Using Dynamically-Generated Sets in the MDX Script

Even more fun: I’ve just found that you can use the technique for dynamically generating sets within a cube’s MDX Script. For example, add the following set declarations to the end of the Adventure Works cube’s MDX Script:

create hidden set myyears as [Date].[Calendar].[Calendar Year].members;

create hidden set mytest as
{topcount(descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Date]), 10,[Measures].[Internet Sales Amount])} as
[Top days for "
+ myyears.current.item(0).item(0).name + "])"

Then connect to the cube with your favourite client tool and, bingo! you see all the sets available to use in your queries:


The only thing to watch out for is that, for some reason, you need to declare the set you pass in to the first parameter of the Generate function separately as I have above, rather than inline.

I can see this as being quite useful – if you need to create a large number of very similar sets on your cube it’s much more manageable than declaring each set individually since you only need to write the set expression once.

3 thoughts on “Using Dynamically-Generated Sets in the MDX Script

  1. Chris,  I would have liked to do something similar with calculated members (dynamically generate).  Still didn\’t find any technique to do that.  Do you happen to know?

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.