List of strings to attribute list

Question

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]"?

Michal Barcik · Accepted Answer · 2011-12-19 13:10:43Z

1

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/)

answered Dec 19, 2011 at 13:10

Michal Barcik

6624 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ceth Over a year ago

Thanks. Looks like what I want. I'll try to use it.

Jason Horner · Accepted Answer · 2011-12-31 06:52:16Z

0

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.

answered Dec 31, 2011 at 6:52

Jason Horner

3,6803 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

List of strings to attribute list

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related