Various MDX Queries

How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT( NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10 , ( [Measures].[Internet Sales Amount] ) ) ON ROWS
FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] )


How in MDX query can I get top 3 sales years based on order quantity?

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, TopCount([Date].[Calendar].[Calendar Year].Members
,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works]


How do you extract first tuple from the set?

SELECT {{[Date].[Calendar].[Calendar Year].Members}.Item(0)} ON 0
FROM [Adventure Works]


How do you get Last month in the time dimension

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]
WHERE ([Measures].[Sales Amount])


Need a MDX query that returns list of months from start of year up to specified month.

SELECT YTD([Date].[Calendar].[Month].&[2003]&[8]) ON 0
FROM [Sales Summary];


I Need an MDX statement to show the first day of the last month in the cube

SELECT OpeningPeriod([Date].[Calendar].[Date]
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0
FROM [Sales Summary]

How in the report can I order date dimension members in descending order?

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, ORDER(Tail([Date].[Calendar].[Calendar Year].Members, 3)
, [Date].[Calendar].CurrentMember.Member_Key, DESC ) ON 1
FROM [Adventure Works]


I Need an MDX statement to get the last Month loaded into a cube SELECT ClosingPeriod([Date].[Calendar].[Month],

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]


I Need an MDX statement to get the first month of the last year loaded into a cube

SELECT OpeningPeriod([Date].[Calendar].[Month]
, ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0
FROM [Sales Summary]


How do you write MDX query that uses execution date/time as a parameter?

SELECT {[Measures].[Internet Order Count]} ON 0
, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]") } ON 1
FROM [Direct Sales]


Need MDX Query to get latest months and previous years same months data

SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
, ParallelPeriod([Date].[Calendar].[Calendar Year] , 1
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) } ON 0
FROM [Sales Summary]


I need an MDX query to show year level data for all years except the last one, and month level data for the last year.

SELECT {NULL: ClosingPeriod([Date].[Calendar].[Calendar Year]
, [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year]
, [Date].[Calendar].DefaultMember) , [Date].[Calendar].[Month]) } ON 0
FROM [Sales Summary]


How do you extract first member of the tuple?

SELECT {([Date].[Calendar].[Calendar Year].&[2003]
, [Customer].[Customer Geography].[Country].&[Canada]).Item(0) } ON 0
FROM [Adventure Works]


How can I compare members from different dimensions that have the same key values?

SELECT {[Measures].[Internet Order Count]} ON 0
, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children )
, [Ship Date].[Date].CurrentMember.Properties('Key') = [Delivery Date].[Date].Properties('Key') ) ON 1
FROM [Adventure Works]


How can I get attribute key with MDX?

WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON 0, [Product].[Product Categories].Members on 1
FROM [Adventure Works]


How do you compare dimension level name to specific value?

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')
SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary]
WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])


How do I calculate sales for 12 Month to date in MDX?

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12) : ClosingPeriod([Date].[Calendar].[Month]
, [Date].[Calendar].[All Periods])
, [Measures].[Order Count])
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works]


How do I group dimension members dynamically in MDX?

WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'

MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'

MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'

SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS
, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS
FROM [Adventure Works]


How do you write MDX query that returns measure ratio to parent value?

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL , [Measures].[Order Count] / ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) )
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods] } ON 1
FROM [Adventure Works]


MDX query to get sales by product line for specific period plus number of months with non empty sales.
WITH Member [Measures].[Months With Above Zero Sales] AS COUNT( DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount] , ExcludeEmpty )

SELECT {[Measures].[Sales Amount]
, [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works] WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])


How can I get Last (Previous) Year to Date (YTD) values?
WITH MEMBER [Measures].[Current YTD] AS SUM(YTD([Date].[Calendar].CurrentMember)
, [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year] , 1
, [Date].[Calendar].CurrentMember)) ,
[Measures].[Internet Order Quantity] )

SELECT {[Measures].[Current YTD] , [Measures].[Last YTD] } ON 0
FROM [Adventure Works] WHERE ([Date].[Calendar].[Date].[March 22, 2004])

How do you calculate monthly average of a year?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month]), [Measures].[Internet Order Count] )

SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])


How do you calculate monthly average of a year including empty months?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0) ) 

SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])


Scope MDX


Can you use the Filter function in a SCOPE statement?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS, [Date].[Calendar].Properties("Month of Year") = "January") );
Measures.[Amount] = 10;
END SCOPE;

Can you create a SCOPE based on the Member property?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS , [Date].[Calendar].Properties("Month of Year") = "January") );
Measures.[Amount] = 10;
END SCOPE;

How do you specify SCOPE for a Dimension to include all members before a certain member

SCOPE(NULL:[Date].[Calendar].[Calendar Quarter].&[2003]&[3]); This = 100;
END SCOPE;

How do you specify SCOPE for a Dimension to include all members after a certain member

SCOPE([Date].[Calendar].[Calendar Quarter].&[2003]&[3]: NULL); This = 100;
END SCOPE;

How Can I define SCOPE in MDX for all measures in one measure group?

SCOPE(MeasureGroupMeasures("Sales Orders")); This = ...;
END SCOPE;


How Can I define SCOPE in MDX for all but one measures in one measure group?

SCOPE(MeasureGroupMeasures("Internet Sales") - {[Measures].[Internet Tax Amount]});
This = ...;
END SCOPE;


How Can I define SCOPE in MDX for all measures in multiple measure groups?

SCOPE({MeasureGroupMeasures("Internet Sales"), MeasureGroupMeasures("Internet Orders")
, MeasureGroupMeasures("Internet Customers") }); This = ...;
END SCOPE;

MDX TopSum Function

In Multidimensional Expression, TopSum function will sort the given data in the descending order and then selects the required number of records from the sorted data whose total is at least equal to specified Value.

MDX TopSum Function Syntax

The basic syntax of the MDX TopSum is:

TOPSUM (Set_Expression, Value, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Value: Please provide the Value. TOPSUM function will retrieve the records whose total is at least this Value. 

Numeric_Expression: Any Multidimensional Expression or measure. The TopSum function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the Value.

In this article we will show you, How to write TopSum function to extract the States from Geography Dimension (present in the Adventure Works Cube) whose total is at least equal to the user given value. For this, we are going to use below shown data.

MDX TopSum Function Example

The following query will return all the states present in the Geography Dimension who are contributing Sale of 40000000 of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPSUM (
[Geography].[Geography].[State-Province], 40000000
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, TopSum function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then add the Sales amount of each state until it reaches 40000000.


OUTPUT




If you observe the above screenshot, If you total the Sales amount of above displayed states they approximately equal to 40243296.5. It means greater than what we required.
MDX TopSum Function Example 2


In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPSUM (
[Geography].[Geography].[State-Province], 500000
,[Measures].[Reseller Gross Profit]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected two Measures ([Reseller Sales Amount], [Reseller Gross Profit]) on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, TopSum function will sort the data in the descending order using Measures.[Reseller Gross Profit] and then add the Sales amount of each state until it reaches 500000.


OUTPUT





If you observe the above screenshot, We are getting the top states whose [Reseller Gross Profit] total is 500000.

NOTE: Please use the appropriate Measure as a third argument in the TOPSUM function otherwise, You will end up with wrong results.

MDX TopPercent Function

In Multidimensional Expression, TopPercent function will sort the given data in the descending order and then selects the required number of records from the sorted data whose total is greater than or equal to specified Percentage.

MDX TopPercent Function Syntax

The basic syntax of the MDX TopPercent is:

TOPPERCENT (Set_Expression, Percentage, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Percentage: Please provide the percentage to want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. The TopPercent function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the percentage.

In this article we will show you, How to write TopPercent function to extract the States from Geography Dimension (present in the Adventure Works Cube) who has contributed 60 percent of total sales in Reseller Sales amount. For this, we are going to use below shown data.

MDX TopPercent Function Example

The following query will return all the states present in the Geography Dimension who are contributing 60 Percent of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, TopPercent function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then find the percentage of sales from total sales.


OUTPUT




If you observe the above screenshot, they are not the 60 Percent rows of Total records. If you total the Reseller Sales Amounts present in the above screenshot, it will be greater than or equal to 60 percent of the Total reseller Sales Amount. In short, above mentioned states are contributing 60 Percent Sales of total sales.
MDX Topcount Function Example 2


In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. This is because data is sorted according to [Reseller Sales Amount]


NOTE: Please use the appropriate Measure as a third argument in the TOPPERCENT function otherwise, You will end up with wrong results.

MDX TopCount Function

In Multidimensional Expression, Topcount function will sort the given data in the descending order and then selects the required number of records from the sorted data.

For example, If you want to find the Top 10 performing products or you want to find the top 10 regions with highest sales or to calculate Highest Salary we can use this Topcount function with measure.

Similarities between MDX TOPCOUNT and SQL TOP Clause

MDX TOPCOUNT is similar to SQL TOP Clause.

Like Top clause, Topcount extract the required number of records in the order they initially stored.
To extract Top 10 performing records we have to use Order BY Clause in SQL.
To extract Top 10 performing records we have to add third argument with appropriate Measure or Numeric Value.

MDX TopCount Function Syntax


The basic syntax of the MDX Topcount is:

TOPCOUNT (Set_Expression, Count, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. This is purely optional argument. If we used this argument then TOPCOUNT will sort the data in descending order using this Numeric_Expression and then picks the number of records you asked for otherwise, TOPCOUNT will select the required number of records without sorting.

In this article we will show you, How to write TopCount function to extract Top 7 States from Geography table (present in the Adventure Works Cube) whose Reseller Sales amount is greater than others with example. For this, we are going to use below shown data.

MDX Topcount Function with Two arguments

In this example we will show you, What happen when we miss the third argument in the Topcount function. The following query will return first seven records from the above source in the order they appear above.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
) ON ROWS
FROM [Adventure Works]


In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography table on Rows. Next, Topcount function will select the Top 7 records irrespective of their measure values.


OUTPUT




No one will accept the above result as top 7 performing states around the world.
MDX Topcount Function Example


In this example we will show you, What happen when we add the third argument in the Topcount function. The following query will sort the records in the descending order using [Measures].[Reseller Sales Amount] and then return first seven records from the sorted data. In short, We will get our top 7 performing states around the world.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT

Using Multiple Measures in MDX Topcount Function

In this example we will show you, What happen when we add wrong measure as the third argument in the Topcount function. We are going to use two measures ([Measures].[Reseller Sales Amount], [Measures].[Reseller Gross Profit]) and we assign the [Measures].[Reseller Gross Profit] as sorting argument. The following query will sort the records in the descending order using [Measures].[Reseller Gross Profit] and then return first seven records from the sorted data.


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
,[Measures].[Reseller Gross Profit]
) ON ROWS
FROM [Adventure Works]


OUTPUT





Although we are expecting Top 7 Sales, above query is retrieving Top 7 States Whose Gross Profit is Highest among the others.


NOTE: Please use the appropriate Measure as a third argument in the TOPCOUNT function otherwise, You will end up with wrong results.

MDX Tail Function

In Multidimensional Expression, Tail Function is used to select the required number of records from bottom of the source data.

For example, If you want to find the Bottom 10 performing products or  you want to find the Bottom 10 regions with Lowest sales or to calculate Lowest Salary we can use this Head function along with MDX Order Function.

MDX Tail Function Syntax

MDX Tail Function allows only two arguments. The basic syntax of this function is:

1	TAIL (Set_Expression, Count)

• Set_Expression: Any Multidimensional Expression or attributes on which you want check.
• Count: Please provide, How many number of records you want to retrieve.

In this article we will show you, How to write TAIL Function to extract Bottom 7 States from Geography dimension (present in the Adventure Works Cube) whose Reseller Sales amount is lower than others with example. For this, we are going to use below shown data.

MDX Tail Function Example

In this example we will show you, How to write the Tail function. The following query will return Last seven records from the above source in the order they appear above.

CODE

1 2 3 4 5 6

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, TAIL ( [Geography].[Geography].[State-Province]         ,7        ) ON ROWS FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, Tail function will select the Bottom 7 records irrespective of their measure values.

OUTPUT

No one will accept the above result as Least performing states around the world. They we just Bottom 7 records.

Using Order Function along with MDX Tail Function

In this example we will show you, What happen when we add the MDX Order Function.

CODE

1 2 3 4 5 6

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, ORDER (     TAIL ([Geography].[Geography].[State-Province], 7)--Already Picked Data    ,[Measures].[Reseller Sales Amount], DESC        ) ON ROWS FROM [Adventure Works]

OUTPUT

Analysis:

First Tail function will select the Last 7 records present in the source data using below statement

1	TAIL ([Geography].[Geography].[State-Province], 7)

Next, Order function outside the Tail function will sort the already picked data in the descending order using their Reseller Sales Amount.

1 2 3 4

ORDER (     TAIL ([Geography].[Geography].[State-Province], 7)    ,[Measures].[Reseller Sales Amount], DESC        ) ON ROWS

It means, We are getting Last 7 records from the source data in Descending order

Let us change the above code:

CODE

1 2 3 4 5 6 7 8 9 10

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,   TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS FROM [Adventure Works]

OUTPUT

Analysis

First, Order function will sort the State Province data in the Descending order using their Reseller Sales Amount. We used BDESC to break the hierarchy while sorting the data.

1 2 3 4 5

ORDER (         [Geography].[Geography].[State-Province]        ,[Measures].[Reseller Sales Amount]        ,BDESC       )

Next, Tail function will pick the Last 7 records from the already sorted data.

1 2 3 4 5 6 7 8

TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS

Now, We got our Bottom 7 records or Least performing states.

NOTE: Please use the appropriate Measure as second argument in the ORDER function otherwise, You will end up with wrong results.

Using NON EMPTY in MDX Tail Function

In this example we are going to use Non Empty keyword to remove the NULL values.

CODE

1 2 3 4 5 6 7 8 9 10 11

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, NON EMPTY   TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS FROM [Adventure Works]

OUTPUT

If you observe the above screenshot, MDX Non Empty keyword we used in the above query removed those Null records. Now, it looks Nice and Easy 

Thank You for Visiting Our Blog