Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX query parameter passing issue Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 9:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, Visits: 233
Hi everyone, i have a requirement i.e if i select any value in areport filter for week example 45, then report has to show data upto 44th week only.

for this i tried the following query. here i can only give a fixed value.
if i change 19 value to any number(example 33) it will give me upto 32nd week.

but what i need is , it should be dynamic.
can anybody have any idea on this please help in this.


select
subset({
[Date DIM].[ISO Week Short FI].members }, 0,19)
on rows,
{[Measures].[B2B sales with tax] }on columns
from [POS OLAP Daily Material Sales]



thanks in advance
Niharika
Post #1388018
Posted Monday, November 26, 2012 7:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:47 AM
Points: 83, Visits: 682
Hi Niharika

I'm assuming you are using SSRS when you say a report filter parameter?

My MDX isn't great but what I'd do is create set using a range. So using you example instead of the SUBSET I'd have something like this:

{[Date DIM].[ISO Week Short FI].[1] : STRTOMEMBER(@Date).PrevMember} ON ROWS

Where your date parameter would have the form of a member (e.g. "[Date DIM].[ISO Week Short FI].[19]").

Hope this works for you.


Post #1388610
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse