MDX Filter date less than today

By : Dodzik
Source: Stackoverflow.com
Question!

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

By : Dodzik


Answers

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

By : whytheq


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

By : Dodzik


C++11 solution:

#include <type_traits>

template<typename From, typename To>
To map(From e) {
    return static_cast<To>(
        static_cast<typename std::underlying_type<To>::type>(
        static_cast<typename std::underlying_type<From>::type>(e)));
}

This casting cascade is very explicit and supports enum classes.

For older C++ versions and for enums without class, static_cast<Enum2>(e) would suffice.

Edit:

With template specialization, you can use map without specifying any types explicitly:

enum class Enum1: int {A, B, C, D};
enum class Enum2: char {A1, B1, C1, D1};

template<typename T>
struct target_enum {
};

template<>
struct target_enum<Enum1> {
    typedef Enum2 type;
};

template<typename From>
typename target_enum<From>::type map(From e) {
    typedef typename target_enum<From>::type To;
    return static_cast<To>(
        static_cast<typename std::underlying_type<To>::type>(
        static_cast<typename std::underlying_type<From>::type>(e)));
}

You can then call map(Enum1::A). Also works with simple, non-class enums.

By : flyx


This video can help you solving your question :)
By: admin