reporting-servicesssasmdxbusiness-intelligencessas-2012

MDX Filter date less than today

I want to filter my query in a data set in a way, where I got dates from beginning of the month until yesterday. First part is easy, I'm passing month from report parameters, so I got values from every day in month, but somehow I have to limit this until yesterday. I tried putting this expression in where clause, but it didn't work at all since I don't have date on rows: FILTER([Date of shipment].[Date], [Date of shipment].[Date] < Format(Now(), "yyyyMMdd"). I know I could filter rows, but important thing is, I don't want Date to be displayed on the rows.

Edit: additionally I can use parameter supplied by main report, which is yesterday's date. But how do I limit date without putting it on rows? Something like this doesn't works: IIF( STRTOSET(@ShipmentDate, CONSTRAINED).Count = 1, STRTOSET(@ShipmentDate, CONSTRAINED), [Shipment Date].[Date] < @ShipmentDate))

Solution

  • You already have something similar to this:

    SELECT 
  {} ON 0
 ,
    [Date].[Calendar].[Date].&[20050101]
  : 
    StrToMember
    ('[Date].[Calendar].[Date].&[20050105]'   //<<hard-coded to illustrate 
     ,constrained
    ) ON 1
FROM [Adventure Works];

    Returns:

    enter image description here

    Most cubes have a multi-level date hierarchy - so you could change your code to something like the so that next year you do not need to change the hard-coded bit:

    SELECT 
  {} ON 0
 ,
    Descendants
    (
      Exists
      (
        [Date].[Calendar].[Calendar Year].MEMBERS
       ,StrToMember
        (@ShipmentDate
         ,constrained
        )
      ).Item(0)
     ,[Date].[Calendar].[Date]
    ).Item(0)
  : 
    StrToMember
    (@ShipmentDate
     ,constrained
    ) ON 1
FROM [Adventure Works];

    If @ShipmentDate is set to '[Date].[Calendar].[Date].&[20060105]' then I get the following:

    enter image description here