CODEWITHSUNDEEP
sql-serverreporting-services
ceth
ceth

Reputation: 45285

List of strings to attribute list

I have a report which was built on MDX-query:

SELECT {[Measures].[IssueOpened] } ON COLUMNS,
       {(  STRTOSET("[Assigned To].[Id].[Some],[Assigned To].[Id].[Another]") * 
         [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS
FROM (SELECT (STRTOSET(@createdOn) ) ON COLUMNS
     FROM [Reports])

I want to change static string "[Assigned To].[Id].[Some]:[Assigned To].[Id].[Another]" to parameter:

SELECT {[Measures].[IssueOpened] } ON COLUMNS,
       {(  STRTOSET(@assignedTo) * 
         [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS
FROM (SELECT (STRTOSET(@createdOn) ) ON COLUMNS
     FROM [Reports])

I have created parameter, but Available values for this paramater is relation dataset (not MDX dimension). Allow multiple values set to Yes.

How can I convert value of parameter to list of atributes: "[Assigned To].[Id].[Some],[Assigned To].[Id].[Another]"?

Upvotes: 0

Views: 165

Answers (2)

Jason Horner
Jason Horner

Reputation: 3690

If these come from a relational data source I just encode them in the format that MDX is expecting for the parameter value property for example:

Parameter Label: Some
Parameter Value: [Assigned To].[Id].[Some]

Some time this turns out to be easy to create in TSQL Other times you need to do a little hacking with expressions if you need to support dynamic hierarchies. role playing dimensions would be an example. The basic concept is similar though.

Upvotes: 0

Michal Barcik
Michal Barcik

Reputation: 662

One way would be to create CLR stored procedure for analysis services which will do it for you, so it would build the SET for you. You can find some examples on google. (i.e. http://andrewdenhertog.com/analysis-services/clr-stored-procedures-in-sql-server-analysis-services-ssas/)

Upvotes: 1

Related Questions