MDX - Named Sets

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services


Named sets allow reuse of sets that you have defined in the cube across multiple queries. You often need to limit the output of your queries by one or multiple members of a given dimension. For example in order to return all product categories with the exception of "bikes" you could create the following set: 

'FILTER ({[product].[category].members},
[product].[category].CurrentMember.Name<>"bikes")'


Alternatively you could use the EXCEPT function to accomplish the same with the following:

EXCEPT({[product].[category].members}, {[product].[category].[bikes]})


You could create named sets that are populated dynamically depending on the data in your warehouse. For example, you could decide that anyone who places 20 or more orders on the internet is a very important customer. The following set will retrieve such customers: 

FILTER({[customer].customer.members},

[measures].[internet order count]> = 20


AND customer.customer.CurrentMember.Name<>'all customers')


MDX is very flexible when navigating hierarchies; the language includes a multitude of date related. However, there is no way to return a current date with pure MDX - you must resort to Visual Basic for Applications (VBA) functions to get the current date and its parts (quarter, year, day and so forth). One common need for named sets is returning data that pertains to current month, current year, current quarter, last year, last quarter and last month. The following examples show how such named sets could be implemented. Note that these calculations require a time dimension that contains numeric month (the hierarchy is called "NumericMonth" in this case); that is, month numbers 1 through 12 instead of January through December.



Current Month:

{StrToMember("[date].[NumericMonth].[" + LTRIM(STR(YEAR(NOW()))) + "].[" +
LTRIM(STR(DATEPART("q", DATE()))) + "].[" + LTRIM(STR(MONTH(NOW()))) + "]")}


Essentially you need to parse various parts of the NOW() function to get the quarter, month and year portions of the current date.



Current Quarter:

{StrToMember("[date].[NumericMonth].[" + LTRIM(STR(YEAR(NOW()))) + "].[" +
LTRIM(STR(DATEPART("q", DATE()))) + "]")}


Current Year:

{StrToMember("[date].[NumericMonth].[" + LTrim(Str(YEAR(NOW() )))  +  "]") }


Last year:

{StrToMember("[Time1].[NumericMonth].[" + LTrim(Str(YEAR(NOW() )-1)) + "]" ) }


Last quarter:

{StrToMember("[Time1].[NumericMonth].[" +

/* if we're currently in 1st quarter then previous quarter is
quarter 4 of last year. Otherwise use current quarter minus one for the current year.*/
IIF(DATEPART("q",DATE())= 1, LTrim(Str(YEAR(NOW() )-1)),
LTrim(Str(YEAR(NOW() ))))
+ "].[" +


IIF(DATEPART("q",DATE())= 1, "4", STR((DATEPART("q",DATE()))))+  "]") }


Last Month:

{StrToMember("[Time1].[NumericMonth].[" +

/* if the current month is January (month = 1) then use December (month = 12)
of last year. Otherwise use current month minus one of current year.*/
IIF(MONTH(NOW())= 1, LTrim(Str(YEAR(NOW() )-1)), LTrim(Str(YEAR(NOW() ))))



+ "].[" +
//we must also switch to quarter 4
IIF(MONTH(NOW())=1, "4", LTRIM(STR((DATEPART("q",DATE())))))+"].[" +


 IIF(MONTH(NOW())= 1, "12", LTRIM(STR((MONTH(NOW())-1))))+  "]") }
Retrieved from "http://sqlserverpedia.com/wiki/MDX_-_Named_Sets"