Recently, one of our customers came across an interesting issue which many of us face while working with Reporting Services which fetches the data from Analysis Services cube using MDX. So I thought I will share this in this blog post for the benefit of the community.
We have a following MDX query with Calculated Measures viz against our sample Adventureworks R2 Cube which you can download from here
WITH
MEMBER [Less than 20]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*10
MEMBER [Between 20 and 40]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*20
MEMBER [More than 60]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*40
SELECT NONEMPTY {
[Less than 20],
[Between 20 and 40],
[Greater than 60]
} ON 0,
NONEMPTY{
[Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS*
[Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS*
[Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS*
LASTPERIODS(3,[Date].[Month Name].&[2006]&[8])
}
ON 1
FROM (SELECT [Sales Territory].[Sales Territory Country].&[Australia] ON COLUMNS
FROM [Adventure Works])
We wanted to use the above MDX query to fetch the data and display chart in reporting services as follows
However, if you use the above MDX query AS IS in Reporting Services, you will not get an option to use calculated measures Headings as Category Groups in Chart report item while designing the report using either Report Designer or Report Builder. Since the calculated measures appears as Column Headers and not as data and hence they could not be group by Category Group of Chart Report Item.
So we used the following technique to workaround this
Rewrite the MDX as — (intention is to represent calculated measures as values and not “column names”)
WITH
MEMBER [Less than 20]
AS
([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*10
MEMBER [Between 20 and 40]
AS
([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*20
MEMBER [Greater than 60]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*40
SELECT [Sales Territory].[Sales Territory Country].&[Australia] ON 0,
NONEMPTY {
[Less than 20],
[Between 20 and 40],
[Greater than 60]
}*
{
[Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS*
[Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS*
LASTPERIODS(3,[Date].[Month Name].&[2006]&[8])
}
ON 1
FROM [Adventure Works]
However the Report Builder (OR Report Designer) will not allow you to use the above MDX query AS IS. Since it has a limitation where in Axis 0 should be a Measures axis and it should not have Multiple Level hierarchy.
If you try to use the above MDX query in Report Builder or Report Designer, you will hit the following error.
Query preparation failed. The query cannot be prepared: The query must have atleast one axis.The first axis of the query should not have multiple hierarchies,nor should it reference any dimension other than the Measures dimension
To workaround this,
We can do the following.
1. Create a Stored Proc in a SQL Database
2. Stored Proc is used to query the Cube using above MDX query using a Linked Server/OPENROWSET
3. In the Report Designer/Report Builder, create a Dataset by executing the stored procedure
CREATE PROCEDURE usp_getsalesdata AS
BEGIN
select * from OPENROWSET('MSOLAP','Datasource=PARIKS-MSFT\SQLR2;Initial Catalog=AdventureWorksDW2008R2',
'WITH
MEMBER [<20]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*10
MEMBER [<40 and >20]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*20
MEMBER [>60]
AS ([Measures].[Internet Sales Amount]/[Measures].[Internet Total Product Cost])*40
SELECT [Sales Territory].[Sales Territory Country].&[Australia] ON 0,
NON EMPTY {
[<20],
[<40 and >20],
[>60]
} *{
[Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS*
[Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS*
LASTPERIODS(3,[Date].[Month Name].&[2006]&[8])
}
ON 1
FROM [Adventure Works]'
)
END
Once you perform, we should be able to design chart report as present in the excel.
Parikshit Savjani
Premier Field Engineer, Microsoft