- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I have spent a considerable time adapting a number of existing SQL-based reports to MDX-based reports in SSRS. However, there is one problem I am having which I can't seem to figure out: I have a time dimension of monthly periods and can build a tuple to represent the value of the "previous" month. I can then build a calculated member to show what the "difference" is (e.g. June = 160 people, May was 100 people therefore Difference is +60 people). However, the old SQL-based report actually shows the following in a table: June 160 people, People In +90, People Out -30, Difference is +60 I need to build calculated measures (members?) to work out the number of distinct people "in" and the number of distinct people "out". I have a Person dimension and a Distinct Count Measure on the People Key on the relevant Fact Table in my Cube. Hope this makes sense - I have a number of books on MDX and none of them contain any examples of this kind of calculation... is it even possible?

ssasmdx
Comment

You are probably looking for the **`LAG`** function and properly build tuple. The `LAG` functions returns member that is specifid number of possition prior the current member. Then if we use `LAG(1)` on `CURRENTMEMBER` of the Date dimension, it will retrun previous member and we can build a tuple of the previous member and appropriate measure. Something like this should work: WITH MEMBER [Measures].[PreviousMonthCount] AS ([Date].[Year-Month-Date].CURRENTMEMBER.LAG(1), [Measures].[DistinctCountMeasure]) MEMBER [Measures].[Difference] AS [Measures].[DistinctCountMeasure] - [Measures].[PreviousMonthCount] SELECT { [Measures].[DistinctCountMeasure], [Measures].[PreviousMonthCount], [Measures].[Difference] } ON COLUMNS, NON EMPTY { [Date].[Year-Month-Date].[Month].MEMBERS } ON ROWS FROM [YourCube] This should return something like this: DistinctCountMeasure PreviousMonthCount Difference January 2007 62 020 (null) 62 020 February 2007 62 122 62 020 102 March 2007 109 189 62 122 47 067 April 2007 66 449 109 189 -42 740 **====** **EDIT** **====** So I've took a look on your problem and created a small cube with two dimensions (Date and Customer) and here is a query which shows composition of the DistinctCount difference. This will provide you exactly results wanted. However didn't tested it on large cubes as don't have currently any available with distinct count, so don't know if the performance will be perfect (It's first draft). WITH MEMBER [Measures].[PrevMonth] AS ([Date].[Year-Month-Date].CURRENTMEMBER.LAG(1), [Measures].[Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Customer].[Customer].[Customer].MEMBERS, IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Customer].[Customer].[Customer].MEMBERS, IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Date].[Year-Month-Date].[Month].MEMBERS,[Customer].[Customer].[Customer].MEMBERS, "Sales") } ON ROWS FROM [Sales] This produces result like this one: Distinct Count PrevMonth Diff Looses Gains 2012 January 2 (null) 2 0 2 2012 February 3 2 1 1 2 2012 March 3 3 0 1 1

Thanks, a very clear example of a classic problem but I've already got this far... I'm looking for the composition of the difference - e.g. how do I identify that the value of my difference column is the result of losing 30 distinct records but gaining 90 new (distinct) ones?

Just posted an update which solves the issue of the difference composition. :-)

Many, Many thanks for this... results produced okay, but because [Customer].[Customer].[Customer].MEMBERS is not a unique dimension I ended up getting non-distinct results: 1. Here's your code adapted to my Data Warehouse: WITH MEMBER [Measures].[PrevMonth] AS ([Dim Snapshot Date].[Financial Year Quarter Month Day].CURRENTMEMBER.LAG(1), [Measures].[RESYSID Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[RESYSID Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Dim Constituent].[Constituent ID].[Constituent ID], IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Dim Constituent].[Constituent ID].[Constituent ID], IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[RESYSID Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Dim Snapshot Date].[Financial Year Quarter Month Day].[FY Month],[Dim Constituent].[Constituent ID].[Constituent ID].MEMBERS, "Dim Constituent") } ON ROWS FROM [Data Warehouse] 2. Here are the results: RESYSID Distinct Count PrevMonth Diff Looses Gains September 47490 (null) 47490 0 54809 October 47632 47490 142 95 277 November 47372 47632 -260 860 555 December 46753 47372 -619 1043 348 January 46954 46753 201 9448 9680 February 46740 46954 -214 600 402 March 46652 46740 -88 482 381 April 46401 46652 -251 621 372 As you can see, because [Dim Constituent].[Constituent ID].[Constituent ID].MEMBERS is not truly unique, I have a problem. This may be because it is a Type 2 SCD, or possibly because there are some strange combinations of application IDs in the early Constituent dimension. I think it may be because of the former. So my solution was I had to create an outrigger dimension to my Constituent Dimension keyed on the "RESYSID" Application Key, that produced a unique list of RESYSIDs, and then refer to that in the SUM instead. I don't know if this is established practice or not but it appears to be a pretty neat trick: Code then became: WITH MEMBER [Measures].[PrevMonth] AS ([Dim Snapshot Date].[Financial Year Quarter Month Day].CURRENTMEMBER.LAG(1), [Measures].[RESYSID Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[RESYSID Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Dim RESYSID].[RESYSID].[RESYSID], IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Dim RESYSID].[RESYSID].[RESYSID], IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[RESYSID Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Dim Snapshot Date].[Financial Year Quarter Month Day].[FY Month],[Dim RESYSID].[RESYSID].[RESYSID].MEMBERS, "Dim Constituent") } ON ROWS FROM [Data Warehouse] Results then became: RESYSID Distinct Count PrevMonth Diff Looses Gains September 47490 (null) 47490 0 47490 October 47632 47490 142 11 153 November 47372 47632 -260 414 154 December 46753 47372 -619 789 170 January 46954 46753 201 19 220 February 46740 46954 -214 384 170 March 46652 46740 -88 318 230 April 46401 46652 -251 411 160 Anyway, many thanks - got there in the end. Turns out to be more accurate than the existing SQL-based solution!

You have posted no information about your schema, so I have only provided an example how you can achieve requested results. It was up to you to adapt it to your solution. :-)

Sorry I think I should have added this as a comment underneath your answer...

**9** People are following this question.

Copyright 2019 Redgate Software.
Privacy Policy