I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:
Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..
How to create with either Tabular or Matrix report type?
Thanks,
Marcus
You can either:
1. Use the RS 2005 SSAS provider but change the report format to request measures on columns.
2. Bypass the RS 2005 SSAS provider and use the MS OLE DB 9.0 Provider for Analysis Services to request the measures on rows and Time on columns. However, in this case you can't use the matrix region and your table columns will be fixed. So, you have to create 12 columns (assuming 12 months on columns) and hide the ones that are not returned.
|||Thank you very much Teo,I had to chose alternative 2 and created a report with measures in rows and tabular structure. Now I got stuck in some details. I need cascading parameters following the whitepaper " Integrating Analysis Services with Reporting Services" for SQL 2000. But I'm working with SQL2005. I easily establish a parameter for the highest parameter level (here ProductCategory in Adventure Works)
="WITH MEMBER [Product].[Category].[Prod] AS
'[Product].[Category].[" + Parameters!ProductCategory.Value + "]'
SELECT
{ [Measures].[Internet Sales-Discount Amount],[Measures].[Internet Sales-Extended Amount] } ON ROWS ,
{[Date].[Calendar Time].[Month].[January 2003]} ON COLUMNS
FROM [Adventure Works UDM]
WHERE [Product].[Category].[Prod] "
but a link to this existing parameter in a cascading parameter yields an empty list and the report is not executable ([Product Categories] is a natural hierachy):
= "WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{DESCENDANTS({" + Parameters!ProductCategory.Value + " },
[Product].[Product Categories].[Subcategory])} ON ROWS
FROM
[Adventure Works UDM]"
Are cascading parameters with OLE DB 9.0 and SQL 2005 possible, can you provide an example?
Thanks a lot,
Marcus
