SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX query parameter passing issue


MDX query parameter passing issue

Author
Message
niha.736
niha.736
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 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
Spiff
Spiff
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 2236
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search