Search code examples
ssasmdxolapdimensions

MDX script - mix dimensions with parameter

Good morning
I'm working on a cube that has two date dimensions, with the same structure.. Booking date is a date, when I load every day about 1000 records.
Fix Admin Allowed Date is an other date which are different inside of daily records.

The sample can be found here:

|booking__|fix______|sheet_measure|
|----------------------------------------------------|
|20161207,|20161104,|100
|20161207,|20161109,|150
|20161207,|20161209,|250
|20161208,|20160801,|80,
|20161208,|20161110,|150,
|20161208,|20161208,|250,
|20161209,|20161008,|2,
|20161209,|20161210,|1,
|20161209,|20161211,|20,

I would like to query only that dataset, where the fix admin allowed date is less or equal then the booking date. (in the example: show only: on 20161207 => 20161104, on 20161208 => 20160801, 20161110, 20161208 and on 20161209 => 20161008 )

I'm using this mdx query:

SELECT  NON EMPTY { [Measures].[Sheet Missing] } ON COLUMNS,{[Booking Date].[Date].[Date].ALLMEMBERS} * {LastPeriods(2,StrToMember("[Booking Date].[Calendar Week].&[2016 KW 49]" ) )  } } ON ROWS FROM [ProductionCube] where ([Date - Fix Admin Allowed Date].[Calendar].[Date].&[20140101] : STRTOMEMBER('[Date - Fix Admin Allowed Date].[Calendar].[Date].&['+Format(Now(),'yyyyMMdd')+']') )

I try to use currentmember.properties("Key") as below:

   STRTOMEMBER('[Date - Fix Admin Allowed Date].[Calendar].[Date].&['+ [Booking Date].[Date].currentmember.properties("Key") +']') )

It contains parralel periode, because I would see last 2 weeks in the report.

What do you think? Is it really so complicated?
I am running out of ideas..

Thanks for reading

Solution

  • I'm just wondering if you could create a calculated member using datediff to generate a numerical output, and then filter on that?

    with
member [Measures].[Fix Admin is less than Booking Date] as
datediff("d", <booking date member expression>, <fix date member expression>)
<body of select statement... include filter statement against [Measures].[Fix Admin is less than Booking Date]>

    Apologies that I can't provide a complete solution... let me know if this helps at all, though.