CODEWITHSUNDEEP
filterdatepickermdxiccube-reporting
Mahamadou D.
Mahamadou D.

Reputation: 65

Use date picker range as a filter in a MDX query

I created a report against the demo Sales cube. It includes a date picker with range (from & to) where the "on selection" event is named date window.
& a pivot table with a MDX query similar to this:

SELECT 
        [Time].[Calendar].[Day].[7 Jan 2005]
      : 
        [Time].[Calendar].[Day].[10 Jan 2005] ON 0
FROM [Sales];

I would like to replace the fixed dates in the query by the from and to in the date picker. How to do that?

Upvotes: 3

Views: 277

Answers (3)

Artem Lopatiy
Artem Lopatiy

Reputation: 938

As it is mentioned above, IcCube Date Picker widget returns valid MDX value for range. Here is demo report with described configuration.

For more details check Date Picker's settings and Pivot Table's mdx settings.

Upvotes: 1

ic3
ic3

Reputation: 7680

In icCube I'd rather use MDX function LookupByKey instead of strToMember. Besides the better typing the compiler can easily guess the hierarchy that is handy in a few scenarios. IMHO, try never using StrToMember. Something like :

 [Calendar].[Day].lookupByKey( StringToDate(@date,"d/M/yyyy") )

To parse strings into dates you've a few functions available ( here and here ).

I think the range filter directly returns the MDX range expression in the event directly (contact us directly if it's not the case).

Upvotes: 2

whytheq
whytheq

Reputation: 35557

In many dialects of mdx if you have a parameter called @aDate and let us assume its current value is 7 Jan 2005 then you can add to mdx via the strToMember function like this:

strToMember('[Time].[Calendar].[Day].[' + @aDate + ']')

Or the strToSet function like this:

strToSet('[Time].[Calendar].[Day].[' + @aDate + ']:[Time].[Calendar].[Day].[' + @aSECONDDate + ']')

Upvotes: 2

Related Questions