sql server - Calculating a % rate based on two date dimension in one cube

Question

Set up - SSAS 2012 with OLAP cubes (built by supplier) and MS Report Builder v3. No access to BIDS.

I am building a report which needs to calculate a disposal rate based on data from a single cube. Historically this would have been calculated from two separate tables of data, giving a count by month of new items by date recorded and a count by month of items disposed by month of disposal. This can then be turned to a disposal rate using a lookup or similar.

Blank disposal dates are fine (can take months to dispose of items).

I would like to keep this in a single query so that I can introduce extra dimensions to analyse the data and represent it multiple ways easily. My suspicion is that I need a calculated member but I am not sure where to start with these. Any help would be greatly received - I am trying out a few things and will update this should I solve myself.

Simple formula would be

=(sumif(Items, DateReported="July 2014"))/(sumif(Items, Disposal Date="July 2014"))`

So the following data...

Month Recorded  Month Disposed  No of Items
May-14          May-14          25
May-14          Jun-14          3
May-14          Jul-14          45
Jun-14                          232
Jun-14          Jun-14          40
Jun-14          Jul-14          46

Should produce...

Month         No Recorded  No Disposed  Disposal Rate
01/05/2014    73           25           34%
01/06/2014    48           43           90%
01/07/2014    45           91           202%

My current MDX statement:

SELECT
NON EMPTY { [Measures].[No of Items] } ON COLUMNS,
NON EMPTY 
     { 
       ([Date Reported].[Calendar Months].[Month].ALLMEMBERS 
        * 
        [Disposal Date].[Calendar Months].[Month].ALLMEMBERS ) 
     } ON ROWS 
FROM [Items] 

Answer 1

You can use LinkMember to move a reference to one hierarchy (like [Date Reported].[Calendar Months]) to another one (like [Disposal Date].[Calendar Months]), provided both hierarchies have the exact same structure. Thus, only using [Date Reported] in your query, the calculation can use [Disposal Date]. The query would be like the following:

WITH MEMBER Measures.[Disposed in Date Reported] AS
            (Measures.[No of Items],
             LinkMember([Date Reported].[Calendar Months].CurrentMember, [Disposal Date].[Calendar Months]),
             [Date Reported].[Calendar Months].[All]
            )
     MEMBER Measures.[Disposal Rate] AS
            IIf([Measures].[No of Items] <> 0,
                Measures.[Disposed in Date Reported] / [Measures].[No of Items],
                NULL
               ), FORMAT_STRING = '0%'
SELECT { [Measures].[No of Items], Measures.[Disposed in Date Reported], Measures.[Disposal Rate] }
       ON COLUMNS,
       [Date Reported].[Calendar Months].[Month].ALLMEMBERS
       ON ROWS
FROM [Items]

Possibly, you would want to adapt the column titles in your report. I left that out and used member names that desribe more what they do than what should be shown to users.