How to filter measure multiple times in cube

2
votes

I need to product a report from my cube that looks something like the following. enter image description here (dummy data)

Where it lists sales and gross profit for today, this week, the period and year to date across the products category.

My cube is setup as follows A date dimensionenter image description here

And the cube itself enter image description here

Currently I have not implemented the product category pieces.

I'm struggling with how to write an MDX query that can return the sales/gross profit for a single day and then week and so on.

I can return it by itself like so 

SELECT {[Measures].[Gross Profit],[Measures].[Price]} ON COLUMNS
From [Cube]
WHERE [Date].[Date Key].[2015-04-22];

and so on for the other various types (week etc), but I'm unsure as how to apply the where filter to the columnn itself rather than the overall query, or if this is even the correct way to do it and I should be making multiple MDX calls that I then compose in my app that will use this.

Can anyone give me a pointer in the right direction here?

EDIT: Seems to mostly work using the approach @Akshay Rane described however I cannot get one of my measures to work

MEMBER [This Week] as 
 (PeriodsToDate([Date].[Fiscal Week Date].[Fiscal Week],StrToMember('[Date].[Fiscal Week Date].[Date Key].&[' + '20140401' + ']'))
,[Measures].[Merchandise Gross Profit])

Gives me an error:

The  function expects a string or numeric expression for the  argument. A tuple set expression was used.

Any pointers here?

2
ssasmdxdata-warehousecube

2 Answers

2
votes

You will have to create separate Calculated Members for each time interval you want to aggregate the data upon.

This can be done in [Adventure Works] cube as follows.

WITH 

MEMBER [Today] as 
    ([Measures].[Internet Sales Amount], [Date].[Date].CURRENTMEMBER)

MEMBER [Last Week] as 
    AGGREGATE   ( 
                    { [Date].[Date].CURRENTMEMBER.lag(6) : [Date].[Date].CURRENTMEMBER }
                    , [Measures].[Internet Sales Amount] 
                )

SELECT 
        { [Today], [Last Week] } ON 0,

        { ([Product].[Product Categories].[Category], [Date].[Date].[Date]) } ON 1

FROM 

(   
    /*FILTERING ON SPECIFIC DATE USING SUBCUBE*/
    SELECT [Date].[Date].&[20070715] ON 0 
    FROM [Adventure Works]
)
0
votes

If you can take the different levels of date from the same user hierarchy then something like this is possible:

SELECT 
    {
      [Date].[Calendar].[Month].&[2006]&[7]
     ,[Date].[Calendar].[Date].&[20050220]
    }
  * 
    {
      [Measures].[Order Quantity]
     ,[Measures].[Internet Sales Amount]
    } ON COLUMNS
 ,{[Product].[Category].Children} ON ROWS
FROM [Adventure Works];

The above produces results like this:

enter image description here