Dynamic Where Clause Using MDX Query

Question

I am new to mdx Query and I want to ask that how we can write the MDX Query with dynamic where clause. For Example i have list of Employee IDs which will pass to procedure as comma seperated values '1,2,3,4'. This list can contain any Employee Ids. In mdx I want some thing like IN clause which we normally use in SQL Queries. I don't want to give static values in MDX Where clause like this {[Member].[Member Key].&[1], [Member].[Member Key].&[2], [Member].[Member Key].&[3], [Member].[Member Key].&[4]}. Can this thing be achieved by dynamic where clause where I can pass any value to storedprocedure parameter?

Thanks

Work on it a little and share some code. I don't know MDX but with regular stored procedures, you would send in your list, parse it into parameters and create a variable. Then write your where clause as a variable @query = 'Select.... where '+@previouslycreatedvariable then run exec(@query) — Missy
No, i am using Ssas. Actually i am optimizing my sql query in cube by using mdx query — Atif

Veselin Davidov Veselin Davidov · Accepted Answer · 2016-08-04T10:00:25

You can create the where clause dynamically in the same way you wrote but with strtomember/strtoset function.

So instead of : {[Member].[Member Key].&[1], [Member].[Member Key].&[2], [Member].[Member Key].&[3], [Member].[Member Key].&[4]}

It will become

strtoset("{[Member].[Member Key].&[1], [Member].[Member Key].&[2], [Member].[Member Key].&[3], [Member].[Member Key].&[4]}",false) //Or true if you want it to have constrained

If it is in SSRS you will make the calculation in parameter and the MDX will become something like strtoset(@Parameter.value,false) and you will use string functions to set correct parameter value. It will be the same concept when using different tools (not SSRS).

The parameters are not hard coded and can be changed easily with string functions. If the passed string to the MDX is a qualified member/set name it will work properly. Now you will just need to use the String functions to change the string

1,2,3,4 to become - {[Member].[Member Key].&[1], [Me...

Dynamic Where Clause Using MDX Query

1 Answers