Altered MDX for SSRS cascading report not working using

Category: sql server analysisservices

Question

aivoryuk on Fri, 21 Nov 2014 16:17:03


Hi I have done a drag and drop SSRS report with SSAS as the datasource.

The report has 3 parameters which in the SSRS report they are cascading parameters, teams feeds off departments and fee earners feeds off teams.

Everything works fine.

I have since gone onto design mode to alter the mdx so that the user can enter custom dates, here is the code

SELECT NON EMPTY
 { [Measures].[Churn Ratio], [Measures].[Settlements], [Measures].[Active Actions], [Measures].[Matter Oldest Action Days KPI], [Measures].[Bill No Charge], [Measures].[Chargeable Hours], [Measures].[Files Received], [Measures].[Files Resolved] } ON COLUMNS
 FROM (
   SELECT ( StrToMember (
     "[Date Primary Date].[DDMMYYYY].&[" + Format ( CDate ( @startdate ), "yyyMMdd" ) + "]",
     constrained
   ) : StrToMember (
     "[Date Primary Date].[DDMMYYYY].&[" + Format ( CDate ( @enddate ), "yyyMMdd" ) + "]",
     constrained
   ) ) ON COLUMNS
   FROM (
     SELECT ( StrToSet ( @PeoplePrimaryFeeEarnerFeeEarners, CONSTRAINED ) ) ON COLUMNS
     FROM (
       SELECT ( StrToSet ( @PeoplePrimaryFeeEarnerTeams, CONSTRAINED ) ) ON COLUMNS
       FROM (
         SELECT ( StrToSet ( @PeoplePrimaryFeeEarnerDepartments, CONSTRAINED ) ) ON COLUMNS
         FROM [Prod_Cube]
       )
     )
   )
 )
 WHERE ( IIf (
   StrToSet ( @PeoplePrimaryFeeEarnerDepartments, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeoplePrimaryFeeEarnerDepartments, CONSTRAINED ),
   [People Primary FeeEarner].[Departments].CurrentMember
 ),
 IIf (
   StrToSet ( @PeoplePrimaryFeeEarnerTeams, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeoplePrimaryFeeEarnerTeams, CONSTRAINED ),
   [People Primary FeeEarner].[Teams].CurrentMember
 ),
 IIf (
   StrToSet ( @PeoplePrimaryFeeEarnerFeeEarners, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeoplePrimaryFeeEarnerFeeEarners, CONSTRAINED ),
   [People Primary FeeEarner].[Fee Earners].CurrentMember
 ),
 StrToMember (
   "[Date Primary Date].[DDMMYYYY].&[" + Format ( CDate ( @startdate ), "yyyMMdd" ) + "]",
   constrained
 ) : StrToMember (
   "[Date Primary Date].[DDMMYYYY].&[" + Format ( CDate ( @enddate ), "yyyMMdd" ) + "]",
   constrained
 ) )

 

   

 

However when the report is run the cascading parameters are not updating as they should.

Any ideas would be great.

Regards

Replies

Lakmal_Fonseka on Sun, 23 Nov 2014 23:21:53


Hi,

If the cube which you are working on does not contain any sensitive information send me a backup of your cube including some sample data to chandima.fonseka@outlook.com. Also send me your SSRS report layout too. I will take a look into this issue and let you know further details...

Best regards...

aivoryuk on Mon, 24 Nov 2014 16:04:34


Hi,

If the cube which you are working on does not contain any sensitive information send me a backup of your cube including some sample data to chandima.fonseka@outlook.com. Also send me your SSRS report layout too. I will take a look into this issue and let you know further details...

Best regards...


Chandima Lakmal Fonseka

HI

Unfortunately the cube does contain sensitive data so I would not be able to send it to you.

regards

Alex

aivoryuk on Wed, 07 Jan 2015 13:54:28


I managed to solve this on a test set I was working on.

the main data set query is

SELECT NON EMPTY { [Measures].[FACT DEFENCE COUNT] } ON COLUMNS,
 NON EMPTY
 {
   ( [Date Matter Opened].[DDMMYYYY].[DDMMYYYY].AllMembers )
 } ON ROWS
 FROM (
   SELECT ( StrToSet ( @PeopleMatterFeeEarnerFeeEarners, CONSTRAINED ) ) ON COLUMNS
   FROM (
     SELECT ( StrToSet ( @PeopleMatterFeeEarnerTeams, CONSTRAINED ) ) ON COLUMNS
     FROM (
       SELECT ( StrToSet ( @PeopleMatterFeeEarnerDepartments, CONSTRAINED ) ) ON COLUMNS
       FROM (
         SELECT ( StrToMember (
           "[Date Matter Opened].[DDMMYYYY].&[" + Format ( CDate ( @startdate ), "yyyMMdd" ) + "]",
           constrained
         ) : StrToMember (
           "[Date Matter Opened].[DDMMYYYY].&[" + Format ( CDate ( @enddate ), "yyyMMdd" ) + "]",
           constrained
         ) ) ON COLUMNS
         FROM [Prod_Cube]
       )
     )
   )
 )
 WHERE ( IIf (
   StrToSet ( @PeopleMatterFeeEarnerDepartments, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeopleMatterFeeEarnerDepartments, CONSTRAINED ),
   [People Matter Fee Earner].[Departments].CurrentMember
 ),
 IIf (
   StrToSet ( @PeopleMatterFeeEarnerTeams, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeopleMatterFeeEarnerTeams, CONSTRAINED ),
   [People Matter Fee Earner].[Teams].CurrentMember
 ),
 IIf (
   StrToSet ( @PeopleMatterFeeEarnerFeeEarners, CONSTRAINED ) .Count = 1,
   StrToSet ( @PeopleMatterFeeEarnerFeeEarners, CONSTRAINED ),
   [People Matter Fee Earner].[Fee Earners].CurrentMember
 ) )

In SSRS there are then 3 hidden datasets, Departments Teams and FeeEarners

I had to alter these data sets for it to function.

Departments code is

WITH MEMBER [Measures].[ParameterCaption] AS
   [People Matter Fee Earner].[Departments].CurrentMember .MEMBER_CAPTION
 MEMBER [Measures].[ParameterValue] AS
   [People Matter Fee Earner].[Departments].CurrentMember.UniqueName
 MEMBER [Measures].[ParameterLevel] AS
   [People Matter Fee Earner].[Departments].CurrentMember .Level .Ordinal
 SELECT
 { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
 [People Matter Fee Earner].[Departments].AllMembers ON ROWS
 FROM (
   SELECT StrToMember (
     "[Date Matter Opened].[DDMMYYYY].&[" + Format ( CDate ( @startdate ), "yyyMMdd" ) + "]",
     constrained
   ) : StrToMember (
     "[Date Matter Opened].[DDMMYYYY].&[" + Format ( CDate ( @enddate ), "yyyMMdd" ) + "]",
     constrained
   ) ON COLUMNS
   FROM [Prod_Cube]
 )
 

Teams is

WITH MEMBER [Measures].[ParameterCaption] AS
   [People Matter Fee Earner].[Teams].CurrentMember .MEMBER_CAPTION
 MEMBER [Measures].[ParameterValue] AS
   [People Matter Fee Earner].[Teams].CurrentMember.UniqueName
 MEMBER [Measures].[ParameterLevel] AS
   [People Matter Fee Earner].[Teams].CurrentMember .Level .Ordinal
 SELECT
 { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
 NonEmpty ( [People Matter Fee Earner].[Teams].AllMembers, [Measures].[FACT DEFENCE COUNT] ) ON ROWS
 FROM (
   SELECT ( StrToSet ( @PeopleMatterFeeEarnerDepartments, CONSTRAINED ) ) ON COLUMNS
   FROM [Prod_Cube]
 )

The important part with the above is adding the non empty function. You also have to declare the parmater in the dataset.

Fee earner code is

WITH MEMBER [Measures].[ParameterCaption] AS
   [People Matter Fee Earner].[Fee Earners].CurrentMember .MEMBER_CAPTION
 MEMBER [Measures].[ParameterValue] AS
   [People Matter Fee Earner].[Fee Earners].CurrentMember.UniqueName
 MEMBER [Measures].[ParameterLevel] AS
   [People Matter Fee Earner].[Fee Earners].CurrentMember .Level .Ordinal
 SELECT
 { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
 NonEmpty ( [People Matter Fee Earner].[Fee Earners].AllMembers, [Measures].[FACT DEFENCE COUNT] ) ON ROWS
 FROM (
   SELECT ( StrToSet ( @PeopleMatterFeeEarnerTeams, CONSTRAINED ) ) ON COLUMNS
   FROM [Prod_Cube]
 )

You then set up the cascading parameters in the normal way.