Reporting Service MDX problem

Hi all,
I am using Analysis Services cubes as backend to my Reporting Service
reports currently. I have a situation where I select
measures in axis 0
and
a heirarchy members & a level(in another heirarchy) in axis 1.
Now the result set will look like...
heiarchymember1 heirarchymember2 heirarchymember3 level measure
-- -- --
-- --
but my report requires the values of level column above to be column names
and the measure value to be values for those columns. In simple words I want
to transpose a column to rows and measure values should appear as data under
those transposed columns.
I cannot make use of matrix dataregion to do this as I have other
restriction. I can use only table dataregion. Is there a way (say some
function) available in mdx to do this kind of transpose with cube data in
reporting services.
Adventure works query which is good to explain my situation is
SELECT [Measures].[Total Product Cost] ON COLUMNS,
{([Product].[Product Categories].MEMBERS,
[Geography].[Geography].[Country].MEMBERS)} ON ROWS
FROM [Adventure Works]
Pl run the above query from reporting service report builder because if you
run in management studio u will see in different format.
Now when you run the above query in RS you can see Category, SubCategory,
ProductName, Country,Total Product Cost columns. What I want is
Category, SubCategory, ProductName,country1,country2,... as columns and the
relevant Total Product Cost measure values under the country names as data.
If it is sql I can use Case statement to do this kind of transposing. But
here in reporting service MDX we can only use measures no other dimension in
axis 0, which actually leads to this kind of transposing requirements.
Pl let me know what is the best way to accomplish this.
ThanksHi Wei Lu,
Since our reporting requirements are such that we cannot use matrix and we
have to use table with different groups all placing one below the other, I
tried a different solution. I found somewhere that If I make use of OLEDB
provider instead of MS Analysis Services Provider so that I dont have
restriction to put only Measures on columns axis. Now my question is what is
flexibility that I loose if I use oledb provider for analysis services 9.0
than Microsoft Analysis services provider?
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ringt,
> Thank you for your posting!
> From your description, my understanding of this issue is: You want to use
> the MDX in the report and you want to show the heirarchymember in the
> column. If I misunderstood your concern, please feel free to let me know.
> I suggest you use the Matrix control in your report to show the data.
> The attachment is the Report I designed using the Matrix. Please check and
> let me know whether this meet your requirement.
> The datasource is the Adventure Works and the MDX query is the one you
> posted.
> I put the Country in the column group and Total Product Costs in the
> Detail, others in the Row group.
> Please let me know the result so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ringt,
Thank you for the update.
Based on my research, this issue is by design. Although the MDX you want to
use Multi Dimensions in the columns axis could run in the Analysis
Services, the data is extracted as a flattened row-set and can be used by
the report builder only as a row-set. So the Report Builder will not care
about certain things related to OLAP.
I suggest you use the OLEDB for AS 9.0 driver so that you could add Multi
Dimensions in the columns axis.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Ringt,
How are you doing on this issue, does Wei's last reply help clarify the
problem further or have you got further progress on this? If there is still
anything we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.