I am developing a ssrs-report with an underlying ssas-cube. In this report the user is able to select a date range (date-from and date-until; for example 01/01/2018 - 12/31/2018). Depending on this selected date range the sales data is displayed.

Next to the sales data of the selected range the data of the previous period is shown as well.

I have build a calculated measure in the ssas-cube. This measure works well if i use it in excel for example. Is there a way to use this calculated measure in the mdx-query for a dataset in the report? Or do i have to calculate the sales for the previous period manually in the statement?

I have built a statement where i manually set the date ranges depending on the user input. This already works.

WITH Member [Measures].[actSales] AS SUM(
[Dim Date].[Date].&[2018-01-01T00:00:00] : 
[Dim Date].[Date].&[2018-01-31T00:00:00], [Measures].[Sales])
Member [Measures].[preSales] AS SUM(
[Dim Date].[Date].&[2017-01-01T00:00:00] : 
[Dim Date].[Date].&[2017-12-31T00:00:00], [Measures].[Sales])
Member [Measures].[Diff] AS ([Measures].[actSales] - [Measures].[preSales]) 

Select NON EMPTY {[Measures].[actSales],[Measures].[preSales], 
[Measures].[Diff]} ON COLUMNS , NON EMPTY 
{([Dim Company].[Company Name].[Company Name])} ON ROWS
FROM [Cube]

However i am not using the calculated measure from the ssas cube already. Is there a way to benefit from the pre-aggregated measure?

Because i am using several mdx-queries in ssrs and there are up to 6 million datarows involved i would like to consider performance issues.

I would appreciate some tips for improving the existing mdx-query.

Thanks in advance

0 Answers