Or How I Learned to Stop Worrying and Love String Manipulation 😉 .
To kick off this blog with some real content I’m starting small. I recently did some reporting work where I changed the main dataset queries from using a relational database to using a cube. It sped up performance and resolved a lot of calculations that were nasty in T-SQL. However, when I went to implement the new query in the report I ran into a small obstacle. Our parameter lookups were all in T-SQL and needed to stay that way because of some security we had implemented. So I needed to be able to take the parameter value from the database and translate it into a form that the MDX query could handle.
Let’s look at a quick example. I’ll be using the AdventureWorksDW2008R2 Database (which isn’t much different – if at all – from the AdventureWorksDW2008 Database) and I’ll be using the AdventureWorks 2008 Analysis Services project. These are both available online from codeplex.
Generally speaking, our problem isn’t so hard. In our case, the value was an ident that was the same as the member key for the corresponding attribute. Let’s say our report needed ProductCategory as a parameter. The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1]. So we can transform our parameter value to pass into our mdx query like so:
“[Product].[Category].&”+Cstr(Parameters!ProductCategory.Value) + “]”
Simple! But the plot thickens…
I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string. If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values. So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting. Whew!
Here’s the punchline:
Split(“[Product].[Category].&[“+Replace(Join(Parameters!ProductCategory.Value,”],”)+”]”,”,”,”,[Product].[Category].&[“),”,”)
That’s a little bit of a mouthful! What’s happening here?
Join() is a handy little function that takes our array and joins the values into a string. We can delimit the values with a string, in this case “],”, like so:
Join(Parameters!ProductCategory.Value, “],”)
Bikes, Components, and Clothing for our parameter values, our string after using the join function will be “1],2],3”. We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.
Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]”. Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]”
Unfortunately we can’t just pass this into our MDX query. It’s expecting an array object, not a string! But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.
Overall, not a difficult task, but it took me enough time to figure it out that I don’t want to have to re-derive the solution. Now you don’t have to either!
20 Comments
Thanks Erika this was very helpful
Hi Erika, thanks for the nice little trick. I have a question though, how would you call the parameter in the subreport? Inside a STRTOSET?
Thanks again?
I stumbled on this page while trying to learn more about multivalued databases. I don’t think this was exactly what I was looking for but I now know more about MDX queries that I did an hour ago.
Please help!!! How would I o about putting your split function in my mdx? I am having trouble passing multi value params to an mdx query so this is what I have noiw. Its limited to one value..
[Customer].[Customer Number].&[” & Parameters!CustomerNumber.Value(0) & “] } )
PLEASE HELP!!!
Thanks Erika, this blogpost was very helpful.
@John – I’m going to need a bit more info than that. What error are you getting? And where exactly are you using that bit of code? In the mdx query itself? I don’t believe you can call Parameters! in the mdx query dataset, you weed need to pass the value through a query parameter that you use within the dataset.
Hi Erika, thanks for the trick. I have a question, how are you actually using split() function? You have developed split() by your own? and why can we use “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“ directly in MDX?
Thanks,
Prasad
@Prasad
The Split() function is available out of the box in Reporting services. We can’t use the string directly in the mdx because it’s expecting a member. If it was just one member, then we could use the strtomember() function in the mdx query, but the string as it is now, as a concatenated set of values, won’t be recognized as a member.
However, if you were to put curly braces around the string {} then you could possibly use the strtoset() function in the MDX query and pass the whole string without using split(). It’s been a while since I wrote this post, so there may be something I’m forgetting that won’t allow that in this instance, though.
Hi Erika,
I am facing a similar issue but its not related to MDX, rather DAX (in SSAS Tabular model) wherein i have to pass multiple parameter values to the dataset. Can you help me with that please?
Thanks,
Nikhil
Hi Nikhil,
I haven’t worked with the SSAS Tabular Model much lately, so I may not be able to come up with something quickly enough for you, but if I have time to figure it out I will let you know.
Erika,
I am storing MDX code values in a sql server table. So for example, tableA has time_code field with a value [Dim Time].[Time Hierarchy].[Year].&[2001]. I have this value stored in a parameter @Time.
I then have a SSAS report that has also has a parameter @CubeTime which defaults to the value stored in @Time. The SSAS reports runs fine when there is one value, but when there are multiples it fails. So time_code now has a value of [Dim Time].[Time Hierarchy].[Year].&[2001],[Dim Time].Time Hierarchy].[Year].&[2002] The parameter @Time passes this value to @CubeTime that looks right but I get a syntax ‘,’ error message when the report tries to run in my MDX. What am I doing wrong?
This is why Google is making me dumber. Wonderful people like you post your solutions and with a modicum of googling I can avoid using my own brain.
Adapted for passing comma separated values in a text parameter:
=Split(“[Dim CID].[CID].&[” + Replace(Join(Split(Parameters!CID.Value,”,”),”],”)+”]”,”,”,”,[Dim CID].[CID].&[“),”,”)
The only diff is the extra call to Split() on the actual parameter value.
Thanks for posting this.
;D
Mindy, in your MDX, are you making sure to use the strtoset() function on your param? Otherwise, it’s just passing the string value and it won’t recognize that the param is a set. You may also need to make sure you have curly braces {} around your time_code string, so that it reads {[Dim Time].[Time Hierarchy].[Year].&[2001],[Dim Time].Time Hierarchy].[Year].&[2002]}, then you apply the strtoset() function to that value. strtomember() only works on single-valued parameters, you must use strtoset() for multi-valued parameters.
Hi Erika,
Your post is really helpful. It worked out for one of my reports. But now I have a similar issue instead of T-sql query as Data source but a Cube Data source. The Main report contains 4 subreports out of which 2 have the MDX datasets and other 2 have the T-sql stored procedure datasets. The main report has the cube data source. The report runs fine when one value is selected for all the parameters, but when multiple values are selected, it still only displays the first value among the multi-values . I know that the format of the MDX parameter is not the same as T-sql parameter. May I know how could I resolve this issue.
Appreciate your help.
Hi Shalini,
I am assuming you are passing the MDX parameters to an MDX query? Make sure you are using strtoset() instead of strtomember() in your MDX query that is receiving the parameters. That is the first thing I’d check. If you are still getting problems, try running SQL Server Profiler and see what parameters are being passed. Perhaps you have an error when you are linking the report parameter to the query parameter (is it only passing Parameters!MyParameter.Value(0)? That will only pass the first parameter value in the array, not all the parameters). I hope these tips will help you out. Passing MDX parameters to an MDX query should be a straight-forward process, but it is difficult to give specific advice without seeing your actual scenario.
Thank you Erika! You got me out of a tight spot. This was great!!
Hi Erika
Wondering if you have ever done the reverse of this. I have a multivalue MDX parameter that I need to pass to SQL. In SQL, I have a function that splits a comma seperated list to an IN statement. But I need to convert the MDX multivalue parameter into a comma seperated list.
I have a solution that works for single value MDX parameters to SQL. I just need one for multivalue.
Any help is appreciated
Thank you
Maria
Hi Erika,
i am new to ssrs reporting using cube as a source. I have a main report (say Product Column in it) when i click it will go to the subreport but the subreport is not taking parameters it goes to the subreport but parameters are not populated and nothing is shown in the data.
If i run the subreport individually it runs fine.
I have same report on olap which works fine, am i missing anything
Thanks
Hi Maria – it seems you should be able to use the replace and join functions to create the string you want for the sql statement. Simply use the Join() function on the MDX parameter values to get the comma separated list, then use string manipulation to get the keys in the form you need for sql, if the parameter values are returning something like [Dimension].[Attribute].&[Key] you can use Replace() function to get the Key by itself in the list. OR whatever form you need it in. But the join function is what you need to create the list in the first place.
Hi Mahanaz,
it’s really hard to say what could be going on. I would check the configuration of the subreport in the mainreport and see how you have the parameters configured there. Then I would try many other troubleshooting options. But without knowing the exact circumstances of your problem it’s hard to give you better advice. Perhaps you can submit a more detailed write up of your problem on the msdn forums?