Building an MDX query for SSRS Chart Report

Question

I'm creating a dataset to be used in an SSRS line chart report that goes against a Analysis Services cube using MDX. We use SQL Server 2008 R2 and BIDS in Visual Studio 2008. As an infrequent user of MDX I'm struggling with how to pull the data I need.

The Goal: Create a SSRS line chart with 4 lines: 1. Current year Stewardship numbers (e.g. 2016) 2. Past year Stewardship numbers (2015) 3. Two years past Stewardship numbers (2014)
4. Current year Forecast Estimate numbers (2016)

The Problem: I’ve created a query that gets the correct data but doesn't put in on a single line for building the chart. Here is my query:

with 
member [Measures].[ ImpValueCurrQtr] as 
IIF([Measures].[DIVIDE NUMBER]=0, null, [Measures].[SUMOF IMP VALUE]/[Measures].[DIVIDE NUMBER])

member 
Measures.TrendPastYear as ([Measures].[ImpValueCurrQtr],
ParallelPeriod([DimDate].[BCast Qtr Week Number].[BCast Qtr], 4, [DimDate].[BCast Qtr Week Number].CurrentMember))

member 
Measures.TrendPast2Years as ([Measures].[ImpValueCurrQtr],
ParallelPeriod([DimDate].[BCast Qtr Week Number].[BCast Qtr], 8, [DimDate].[BCast Qtr Week Number].CurrentMember))

SELECT 
NON EMPTY { 
[Measures].[ImpValueCurrQtr],
[Measures].[TrendPastYear],
[Measures].[TrendPast2Years]
} ON COLUMNS, 
NON EMPTY { (
[DimNetwork].[Network].[Network].ALLMEMBERS *
[DimDate].[BCast Qtr Week Number].[BCast Qtr Week Number].ALLMEMBERS *
[DimDemograph].[Demo Name].[Demo Name].ALLMEMBERS *
[DimDelImpDataType].[Data Type Name].[Data Type Name].ALLMEMBERS *
[DimDelImpsSource].[DEL IMPS SOURCE ID].[DEL IMPS SOURCE ID].ALLMEMBERS *
[DimDelImpsSource].[Del Imps Source].[Del Imps Source].ALLMEMBERS *
[DimExecDaypartConformed].[Network Daypart].[Daypart Name].ALLMEMBERS   
) } 
ON ROWS 
FROM ( SELECT ( { 
[DimExecDaypartConformed].[Network Daypart].[Daypart Name].&[YYY]&[TPC] } ) ON COLUMNS 
FROM ( SELECT ({[DimDelImpsSource].[ SOURCE ID].&[1],[DimDelImpsSource].[ SOURCE ID].&[10]}) ON COLUMNS 
FROM ( SELECT ([DimDemograph].[Demo Name].&[A25-54] ) ON COLUMNS
FROM ( SELECT ([DimDelImpDataType].[Data Type Name].&[C3] ) ON COLUMNS 
FROM ( SELECT ([DimDate].[BCast Qtr Week Number].[BCast Qtr].&[Q116] ) ON COLUMNS 
FROM ( SELECT ([DimNetwork].[Network].&[XXX] ) ON COLUMNS 
FROM [TRACCube]))))));

The Network, BCast Qtr, Data Type Name and Demo Name in the sub-selects are parameters to the report that are hardcoded for this illustration. The SOURCE ID and the Daypart Names are hardcoded constant values.

The result has the correct data but in two lines ...

What I need to do is put the numbers for the Forecast Estimates on the same line so the chart can see all 4 data points. Like this ...

If I simply take out the source id and source name the query pulls in all 8 data sources in the cube and the numbers are way off. Can I limit my calculated members to a certain source id similar to a scope statement in the cube? Then I can create a fourth calculate member for the Forecast Estimates.

Thanks for all your help!

whytheq whytheq · Accepted Answer · 2016-04-02T09:47:02

If I understand correctly it should be relatively easy to just move the specific members into the measures:

with 
member [Measures].[ ImpValueCurrQtr] as 
IIF(
 [Measures].[DIVIDE NUMBER]=0
, null
, DIVIDE(
     //>>tuple start>>
     (
        [Measures].[SUMOF IMP VALUE]
       ,[DimDelImpsSource].[Del Imps Source].[Del Imps Source].[Stewardship]
     )
     //>>tuple end>>
    ,[Measures].[DIVIDE NUMBER]
  )
)

You can use the above in your other measures if you wish to limit a measure to just a particular member of a hierarchy.

An alternative which is prettier is to just put the tuples ON COLUMNS:

WITH
...
...
SELECT 
NON EMPTY { 
 (  [Measures].[ImpValueCurrQtr]
   ,[DimDelImpsSource].[Del Imps Source].[Del Imps Source].[Stewardship] )
,(  [Measures].[TrendPastYear]
   ,[DimDelImpsSource].[Del Imps Source].[Del Imps Source].[Stewardship] )
,(  [Measures].[TrendPast2Years]
   ,[DimDelImpsSource].[Del Imps Source].[Del Imps Source].[Stewardship] )
,(  [Measures].[ImpValueCurrQtr]
   ,[DimDelImpsSource].[Del Imps Source].[Del Imps Source].[Forecast] )
} ON COLUMNS, 
NON EMPTY { (
[DimNetwork].[Network].[Network].ALLMEMBERS *
[DimDate].[BCast Qtr Week Number].[BCast Qtr Week Number].ALLMEMBERS *
[DimDemograph].[Demo Name].[Demo Name].ALLMEMBERS *
[DimDelImpDataType].[Data Type Name].[Data Type Name].ALLMEMBERS *
[DimExecDaypartConformed].[Network Daypart].[Daypart Name].ALLMEMBERS   
) } 
ON ROWS 

FROM
...

Building an MDX query for SSRS Chart Report

1 Answers