In a previous blog I talked about the problem of aliasing a row in MDX. The problem is manifested in Reporting Services (SSRS) as datasets need non dynamic names to work well. So here is the promised solution in images.
First here is a look at the fixed fields required as output from the MDX in SSRS
Secondly, notice that I am calling a stored procedure now, not executing MDX in SSRS anymore.
Thirdly, I need to set parameters for the stored procedure (if required).
Fourthly, the code in the stored procedure needs to be built up. I find the easiest is to build up the required bits of the MDX query in smaller substrings, like @Hierarchy1, @Hierarchy2 etc., but ultimately you are building up a full MDX string e.g. @mdxquery which is executed in an OPENQUERY statement on a linked server. This @mdxquery is itself put into a T-SQL string e.g. @sql which is executed (exec @sql). The main point of this exercise is that the resultant query is utilising T-SQL in which you can use aliasing, not to mention simplifying the SSRS queries as all they are now doing is calling SPs. Here is part of the stored procedure, earlier in the code I will have built up the MDX expression itself and put that into @mdxquery. The MDX syntax could be copied and pasted from SSRS itself if you temporarily create a query on a dataset which is connected to the cube you are querying. Also note I am using a BEGIN CATCH statement to catch any queries where no results are returned from the MDX query on the cube to SSRS.
Some other blogs of interest on the same topic you find useful to look through are:
The second one you may need to register for although it is well worth doing so as SQL Server Central has many useful articles and solutions.
One point to note is that when you test your MDX expression and there is a mistake in it, you will often get an unhelpful error – it is imperative that you test your MDX expression (@mdxquery above) on the linked SSAS server before blaming the SP itself. In my experience it is either right and it works or wrong and you end up with an unhelpful error. Next time I will talk about some of the permission settings on the linked server which need to be set properly or else – again – it fails with an unhelpful message.