CODEWITHSUNDEEP
reporting-servicesssasmdxbusiness-intelligencessas-2012
Dodzik
Dodzik

Reputation: 370

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))

Upvotes: 1

Views: 2073

Answers (2)

whytheq
whytheq

Reputation: 35557

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

Upvotes: 2

Dodzik
Dodzik

Reputation: 370

Solution: Since I had Month passed through parameter dates were limited to current month. This allowed me to do this:

[Shipment date].[Datw].&[20160101] : STRTOMEMBER(@ShipmentDate, constrained)

Way I did this is ugly, but it works(it may need mainteance, to change date to 20170101 in next year and so on).

Upvotes: 0

Related Questions