Indent on report parameter in SSRS (written in MDX)

Posted on 15. April 2016 by Sindre • Posted in SSRS • Tagged MDX, SSRS • Leave a comment

In this post, I will show you how you can make nice indented report parameters in SSRS. This might become handy if you want to display multiple levels in the same parameter list.

Let’s start by writing a simple MDX to list the years and week numbers.

WITH MEMBER [RSValue]
AS
[Date].[Production].CurrentMember.UniqueName

MEMBER [RSCaption]
AS
[Date].[Production].CurrentMember.NAME

SELECT {
	[Measures].[RSValue],
	[Measures].[RSCaption]
} ON 0,
DESCENDANTS(
	[Date].[Production].[Year], 
	[Date].[Production].[Week], 
	SELF_AND_BEFORE
) ON 1
FROM [Adventure Works Internet Sales Model]

Running this query will produce a result like this in SSMS

Continue reading →

Using subqueries in MDX

Posted on 11. September 2015 by Sindre • Posted in MDX • Tagged MDX, subqueries • Leave a comment

Did you know that you can write subqueries in MDX? It might be handy if you have some complex dimension filters.

Lets take a basic sample

SELECT [Measures].[Internet Total Sales] ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
FROM [Adventure Works Internet Sales Model]

And the result will look like this

If you only want to get sales for the year of 2007 it can be achieved with a subquery.

SELECT [Measures].[Internet Total Sales] ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
FROM 
( 
	SELECT [Date].[Calendar Year].&[2007] ON 0
	FROM [Adventure Works Internet Sales Model]
)

Yes I know this can be achieved in simpler ways 🙂 But I had some problems figuring out better samples

What is the difference between “non empty” and “nonempty” in MDX?

Posted on 21. October 2014 by Sindre • Posted in MDX • Tagged Filter, MDX, non empty, nonempty • Leave a comment

In this post I will demonstrate the difference between “non empty” and “nonempty” in MDX. Not everyone knows the difference between these functions.

Lets write a simple MDX

SELECT { 
	[Measures].[Reseller Sales Amount], 
	[Measures].[Discount Amount] 
 } ON COLUMNS, 
 [Geography].[City].[City] ON ROWS 
 FROM [Adventure Works]

And the result will look like this

As you can see there are cities with no sales and no discount. If I add the “NONEMPTY” function I will remove all rows where both measures are NULL.

SELECT { 
	[Measures].[Reseller Sales Amount], 
	[Measures].[Discount Amount] 
 } ON COLUMNS, 
 NON EMPTY [Geography].[City].[City] ON ROWS 
 FROM [Adventure Works]

But to remove all rows with NULL in discount amount I must use the “nonempty”. To make this work I will have to create a new measure that prints NULL if the value are 0 🙂

WITH MEMBER [Measures].[Discount Amount2]
 AS
 IIF([Measures].[Discount Amount] = 0, NULL, [Measures].[Discount Amount])
 
 SELECT { 
	[Measures].[Reseller Sales Amount], 
	[Measures].[Discount Amount2] 
 } ON COLUMNS, 
 NONEMPTY ([Geography].[City].[City], [Measures].[Discount Amount2]) ON ROWS 
 FROM [Adventure Works]

The “NONEMPTY” and “NON EMPTY” can be replaced by a filter. Like this

WITH MEMBER [Measures].[Discount Amount2]
 AS
 IIF([Measures].[Discount Amount] = 0, NULL, [Measures].[Discount Amount])
 
 SELECT { 
	[Measures].[Reseller Sales Amount], 
	[Measures].[Discount Amount2] 
 } ON COLUMNS, 
 FILTER ([Geography].[City].[City], NOT IsEmpty([Measures].[Discount Amount2])) ON ROWS 
 FROM [Adventure Works]

Use “descendants” in MDX

Posted on 25. September 2014 by Sindre • Posted in MDX • Tagged Descendants, MDX • Leave a comment

In the sample today I will show you how to use the “descendants” function in MDX. In this sample I want to count how many days and months I have sold stuff. And then calculate the average sales per month and date.

WITH MEMBER DaysWithSales AS
  Count ( Descendants ( [Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Date] ) )

MEMBER MonthWithSales AS
  Count ( Descendants ( [Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Month] ) )

MEMBER [Avg Sales Per Month] AS
  divide ( [Measures].[Internet Sales Amount], [Measures].[MonthWithSales] )

MEMBER [Avg Sales Per Day] AS
  divide ( [Measures].[Internet Sales Amount], [Measures].[DaysWithSales] )

SELECT NON EMPTY [Ship Date].[Calendar].[Calendar Year].&[2013].Children ON 1,
{ 
   [Measures].[Internet Sales Amount], 
   [Measures].[MonthWithSales], 
   [Measures].[DaysWithSales], 
   [Measures].[Avg Sales Per Month], 
   [Measures].[Avg Sales Per Day] 
} ON 0
FROM [Adventure Works]

And the result will look like this

“Descendants” have lots of different parameters. Please check them out on MSDN 🙂

Use “except” in MDX to remove dimension members

Posted on 13. September 2014 by Sindre • Posted in MDX • Tagged except, MDX, order • Leave a comment

In this post I will explain how the “except” function works in MDX.

I have a basic query that looks like this

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Subcategory].[Subcategory] ON 1
FROM [Adventure Works]

And the output will look like this

But the customer wants me to remove bikes from the resultset. To do this I extend my query to use the “filter” function.

	
SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY Except (
  [Product].[Subcategory].[Subcategory],
  { [Product].[Subcategory].&[2], [Product].[Subcategory].&[1], [Product].[Subcategory].&[3] }
) ON 1
FROM [Adventure Works]

Lets sort the resultset using the order function

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY Order (
  Except (
    [Product].[Subcategory].[Subcategory],
    { [Product].[Subcategory].&[2], [Product].[Subcategory].&[1], [Product].[Subcategory].&[3] }
  ),
  [Measures].[Internet Sales Amount],
  DESC
) ON 1
FROM [Adventure Works]

Nice!!

Return cross product of two or more sets in MDX using “CrossJoin”

Posted on 23. August 2014 by Sindre • Posted in MDX • Tagged CrossJoin, MDX

One of the functions I use almost all the time when I am writing MDX is the crossjoin function. This function returns the cross product of two or more sets in MDX.

Lets look at an example

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

This code will give us sales per year. But what if we want sales per year per city? Lets introduce the crossjoin function!

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Date].[Calendar].[Calendar Year], [Customer].[City].[City])  ON 1
FROM [Adventure Works]¨

And that would gives ut the result we want

How about sales per contry per city per year? Then we will have to nest 2 crossjoins like this.

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Customer].[Country].[Country], CrossJoin([Customer].[City].[City], [Date].[Calendar].[Calendar Year]))  ON 1
FROM [Adventure Works]

Instead of crossjoin you can also use a ‘*’. That is the way I ususally write my code

Here is an example on that

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Customer].[Country].[Country] * [Customer].[City].[City] * [Date].[Calendar].[Calendar Year]  ON 1
FROM [Adventure Works]

Comparable day in MDX using “lag” or “ParallelPeriod”

Posted on 14. August 2014 by Sindre • Posted in MDX • Tagged Aggregate, Comparable days, lag, MDX, ParallelPeriod • Leave a comment

In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.

Running total in MDX

Posted on 12. August 2014 by Sindre • Posted in MDX • Tagged MDX, Running total • 1 Comment

Here is a sample on how you can get running total in MDX. The query below gives sales and running total for january 2011 per date.

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Month].&[2011]&[1].Children ON 1
FROM [Adventure Works]

And the result will look like this:

A query for month level can look like this:

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Calendar Quarter].&[2011]&[1].Children ON 1
FROM [Adventure Works]

This will look like this

Because the parent level of month is quarter you will only get 3 months. In the cubes I make for customers I always have hierarchies for “Year – Month – Date” and then it should not be any problem 🙂

Please note that this sample has to be run against a multidimensional instance of Analysis Services.

Dynamic date filter in MDX using VBA

Posted on 3. August 2014 by Sindre • Posted in MDX • Tagged DYNAMIC DATE, MDX, NOW, StrToMember, VBA • Leave a comment

When I create a new cube I always add a few named sets for getting time periods automatically. These are nice to have if I have scheduled reports that should present data for today, yesterday, last week or maybe current month.

An example could be a named set that returns yesterdays date.

CREATE SET CURRENTCUBE.[Yesterdays date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);

And the named set might be used like this

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

You can of course also use this within a single query like this.

WITH SET [Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]")    -- minus 1 gives yesterdays date

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

Please note that the format of the member must be correct. In my case the Date dimensions members look like this: [Date].[Date].&[2005-01-10T00:00:00]. If you try to run these samples against the sample database you will not get any result. Since the database does not have data for the current period. If you want to test a valid date you can check how many days you have to remove by running this SQL:

select DATEDIFF(day, getdate(), '2006/05/18')

And then you can change the code “now()-1” to “now()-[The number of days the query returned]”

Here are some other useful samples

//Sample date sets.
CREATE SET CURRENTCUBE.[Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now(), "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);       
     
-- Must be used with [This Year]
CREATE SET CURRENTCUBE.[This month] AS 
StrToMember("[Date].[Month].&[" + Format(now()-1, "MM") + "]", CONSTRAINED);        

CREATE SET CURRENTCUBE.[This year] AS 
StrToMember("[Date].[Calendar Year].&[" + Format(now(), "yyyy") + "]", CONSTRAINED);      

-- Can you create a named set for current week? Last week?

Top products within each category in MDX

Posted on 2. August 2014 by Sindre • Posted in MDX • Tagged GENERATE, MDX, TOPCOUNT • Leave a comment

If you want to have top products for each model this is a simple way of doing that in MDX.

SELECT [Measures].[Internet Total Sales] ON COLUMNS,
NON EMPTY Generate (
Order (
{ [Product].[Category].[Model].Members },
[Measures].[Internet Total Sales],
ASC
),
Crossjoin (
[Product].[Category].CurrentMember,
TopCount (
[Product].[Product Name].Children,
2,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

The result will look like this

A blog about Analytics, AWS and Azure

Tag Archives: MDX

Indent on report parameter in SSRS (written in MDX)

Using subqueries in MDX

What is the difference between “non empty” and “nonempty” in MDX?

Use “descendants” in MDX

Use “except” in MDX to remove dimension members

Return cross product of two or more sets in MDX using “CrossJoin”

Comparable day in MDX using “lag” or “ParallelPeriod”

Running total in MDX

Dynamic date filter in MDX using VBA

Top products within each category in MDX