This blog entry addresses a few questions. Recently I was trying to work out a way of aliasing a row in MDX, much like we can do in T-SQL. e.g. select ‘xxx’ as MyColumnX. I could not find a way of replicating this idea in MDX. Note that it is possible to do this on measures using WITH MEMBER statements, but on rows – no chance (if someone has a solution for this in MDX please let us know).
The main reason I had this problem in the first place was that I was trying to create an MDX statement which would produce a Reporting Services dataset. There was to be a generic column ViewBy in which a user could select to view the report by different categories, e.g. by country or company etc. So the MDX had to get the right data from the cube, but still come back with the column heading being ViewBy.
However, I did find a way of doing this using a different technique. That was to execute the MDX in a stored procedure in T-SQL using OPENQUERY and using a final SELECT statement aliasing as normal. Therefore the SSRS report is no longer using the MDX, only executing the SP which now comes back with proper named columns. To build up the MDX query in T-SQL can be tricky, so best is to let SSRS produce the basic MDX for you and then you copy it across.
In the next blog I will give screen shots of these different procedures.