SSRS -Create dynamic MDX dataset and tuning MDX query

How to create a dynamic MDX query
1 create a data set and select query designer

2 add all filter for the query and choose as the parameter ( after choose as the parameter , system will automatically create one parameter in you left hand of parameter folder)

3 click the design mode

4 we can start the modify the MDX query , normally we will keep the rest of after first "From" statement , coz those are the filter statement . we can deleted those highlighted part and add our own code

5 after modifying the query , we can click execute query and the system will automatically generate the columns
6 I will talk about the MDX query tuning , we may often use the crossjoin in our query , sometimes , we use function Crossjoin() , or using * to implement crossjoin ( normally when you use cube browser to drag the columns to data plant , the system will automatically use * to do crossjoin). both of crossjoin() function and * did very bad performance , coz it does not care the turple is null or not , so we should use nonemptycrossjoin() function , this function improved a lot on the performance

7 if you want to do dynamic query , there is another thing we need to pay attention , query format. normally what i did is to line up all code in the notepad, means put all code in one line , because sometimes some line termination activity ,like you click enter and start a new line,  will impact on the expression in the next step

8 click OK we will go to next step ( see the query text , it has been filled by your code )

9 click fx on right side of query and then we enter the expression window

10 next , we need to modify the commandtext , in this part , all the command text should be start with =. all the string should double quote and also we can add the parameter into the command
and then click OK . if you setup expression for query, the query design can not be used any more

you will see the query part was changed to <Exp> and gray out Query Designer

Great , we've done the setup for dynamic MDX query dataset