Cascading Parameters MDX

Question

Cascading Parameters MDX

Jules Bonnot

SSCertifiable

Points: 5488
More actions
August 3, 2006 at 4:37 am

#169223

I know that what I need is cascading parmeters but I cant seem to find way of doing it. Below is the MDX code from the second parameters Dataset. I need to filter this dataset by the first paramter which is @p1. I am using rs 2005 and as 2005.

Any Help or pointers greatly appreciated.

WITH MEMBER [Measures].[ParameterCaption] AS '[Broker Contact].[Full Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Broker Contact].[Full Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Broker Contact].[Full Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Broker Contact].[Full Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@p2, CONSTRAINED) ) ON COLUMNS FROM [Any Cube])
www.sql-library.com[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 1

Try making it a dynamic MDX statment typing it like:

="with member [Measures]. bla bla bla... select (strtoset ( '" & Parameters!P2.Value & "' , Constrained )) bla bla bla..."

Be carefult to include the single quotes before and after the parameter so MDX gets a properly quoted value.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

Thanks very much for your help but i am very new to MDX so am not quite there yet.

when i add the = sign at the beginning of the dynamic part in the data set definition i get a syntax error. So i assumed i need to paste it into the the expression box of the data set tab. It appears to accept the syntax but when try to preview the report it says.

Operator '&' is not defined for types 'string' and 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters'

.

Here is the query i have made dynamic as you suggested.

="WITH MEMBER [Measures].[ParameterCaption] AS '[Broker Contact].[Full Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Broker Contact].[Full Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Broker Contact].[Full Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Broker Contact].[Full Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET( '" & Parameters!@P1.Value & "', CONSTRAINED) ) ON COLUMNS FROM [Any Cube]"

Any help greatly appreciated. If you know a good online resource that would also be great.

Thanks again,

Jules.

www.sql-library.com[/url]

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 3

Remove the @ sign from the parameter definition: that is what is confusing the parser.

About the online resource, I don't have any good one. Most of my knowledge comes from "trial and error 1000 times" and lots of help from the guys from SQLCentral .

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 4

Thanks very much for this it seems to compile this code ok but cant run it.

After i select the first parameter( which then would filter the available values for the second based on the data set you suggest.

It tells me that 'cannot set the command text for data set'

'error during processing of command test exprssion for data set ...'

Thanks very much for your help, MDX is a nightmare.

Jules

="WITH MEMBER [Measures].[ParameterCaption] AS '[Broker Contact].[Full Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Broker Contact].[Full Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Broker Contact].[Full Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Broker Contact].[Full Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(" &Parameters!BrokerOrganisationName.Value & ", CONSTRAINED) ) ON COLUMNS FROM [Any Cube])"

www.sql-library.com[/url]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

="WITH MEMBER [Measures].[ParameterCaption] AS '[Broker Contact].[Full Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Broker Contact].[Full Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Broker Contact].[Full Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Broker Contact].[Full Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET('" &Parameters!BrokerOrganisationName.Value & "', CONSTRAINED) ) ON COLUMNS FROM [Any Cube])"

here it is with the single quotes around the parameter name. Still gives this error

cannot set the command text for data set'

'error during processing of command test exprssion for data set ...' when previewing

www.sql-library.com[/url]

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Hey Jules,

I completely overlooked the 'using RS2005' bit.

As far as I can tell it's a *really* convoluted process to get this to go. The key piece is to make sure you use the parameter management tool (in the dataset interface). This auto-creates new datasets for these parameters. You could possibly then modify these datasets (i did it the hard way and created my own, deleted the auto-geenerated ones and then renamed mine to the same name as the auto-gen dataset).

I can shoot you through the project if you want it (just built of the adventure works cube).

Cheers,

Steve.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7

that would be great please send it.

www.sql-library.com[/url]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 8

Hi Steve,

Have you had any luck with that example project.

It would really help me out if you wouldnt mind sending it.

Thanks a lot for your help,

Jules

www.sql-library.com[/url]

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

Hi Jules,

Can you PM me with an email address that I can send it to?

Cheers,

Steve.