Can MDX be used to create a SSRS report ? 1

[Recce]

Good day,

I was wondering if it is possible to use the MDX query that gets generated in the back of the Pivot Table in Excel to create a SSRS report ?

In other words,I would like to know if I take the MDX query can it be used in something like a DrillThrough action to automatically generate a Reporting Services report which can be used to view the detail within my SSAS Cube.

It would be very usefull for the user to see all the detail behind the Cube which he sliced and diced.

If anyone know more about this,please could you tell me how and where. Thank you.

"We know nothing but, that what we know is not the truth..." - Me

 

[RiverGuy]

In your SSAS project, in your cube, there is a tab called "Actions." One of the types of actions is the "Reporting Action." What you have described is what reporting actions are commonly used for.

 

[Recce]

Hi RiverGuy,

Thanks for the reply. Yes, you are right, I was looking at that however,that Report action requires me to put my Server name in,which I have and which works and the,it asks for a report path. Now,doesn't that mean that you have to already created this report by yourself before the time ?

I was hoping that the MDX, which is generated at the back would be able to create this report with all the filters and measures which the user has in his Cube.

Please let me know if maybe I'm hoping for a bit too much here ?

"We know nothing but, that what we know is not the truth..." - Me

 

[Recce]

Hi RiverGuy,

Thanks for the reply. Yes, you are right, I was looking at that however,that Report action requires me to put my Server name in,which I have and which works and the,it asks for a report path. Now,doesn't that mean that you have to already have created this report by yourself before the time ?

I was hoping that the MDX, which is generated at the back would be able to create this report with all the filters and measures which the user has in his Cube.

Please let me know if maybe I'm hoping for a bit too much here ?

"We know nothing but, that what we know is not the truth..." - Me

 

[RiverGuy]

I understand what you are asking now. I don't think there is any way to do what you are asking. I've created the reports and then passed parameters to them. It is a fairly painful process when there are a lot of dimensions.

 

[Recce]

Ok,I also understand what you did in order to make it work for you. Thanks for explaining.

I just have another question. What does that type of parameter look like that you use ? Where can I get an example ?

The thing is,I plan on doing what you did because, fortunately I only have 6 dimensions.

"We know nothing but, that what we know is not the truth..." - Me

 

[RiverGuy]

Here's an example:

Parameter Name: Fiscal_Month_Name
Parameter Value: [Date].[Fiscal Month].CurrentMember.Name

In that example, I am running my report against the relational database to get all of the details. So in my SSRS report, I have a parameter called @Fiscal_Month_Name. This parameter gets set from the [Date].[Fiscal Month] member. My SQL query joins to my date dimension with the selected Fiscal Month name.

 

[Recce]

Yes,thank you very much for this RiverGuy, this is what I was looking for, I think I can use this for now to go on with the report...

"We know nothing but, that what we know is not the truth..." - Me