Generate MDX Query in a Strored Procedure...

Howdy Folks...I am back! With some more interesting learning’s on SSRS which is of course the Dessert of my Meal which comes atlast but it’s better than rest.
The Requirement from the client was Pretty Simple …The changes in the Report should not be done in Visual studio and Deployed again..But the Formula showing the data has to be Changed..I was taken aback a bit and Quiet Perplexed… I said Let me think and Came up with a Solution of Using Stored Procedure for Writing MDX Queries! Wow I know you might think what? But I will show you how to do this!

Step 1: Create a Calculated Measure in your Report Say <> which is Dependent on Base Measure <>*1.5 .





So,...What Happens Here is that When You Change the Value in the Stored Procedure the MDX Query Resulted by the Same SP Changes by which You dont need to Get inside the Report Solution to Change it.

Create a Parameter and call it "Query" and Let this Parameter hold the Query Resulted by the SP.




Create a Data Set which Points to this "Query" Parameter as its Source .Like...=Query!Parameter.value



So,This Method can be adopted when You have to create an MDX Query Depending on the Parameter Selected from the User.

By the Way its always a Good Approach to have Your Query Outside the Report as its more Accessible and You Implement Security also on the SP.,,

Will be back soon....Chow..