May 19, 2009 at 7:03 am
Hi, is it possible to use the lag function or is there a different function that would allow me to see a cross section of data from a 'selected month' in a dimension and to 'lag back' for instance 2,3,4,5 months as a range and show a month over month comparision of data rather than just going back a value of one month at a time?
Thanks in advance for your help,
Marcus.
May 19, 2009 at 8:00 am
You could use the periods to date function which will return all members within a period up to the member specified
or try the : notation to specify a range
[member].lag(4):[member].currentmember
May 19, 2009 at 8:05 am
Hi samuel, I've tried 'lag' but it only allows for one value, I'd actually like to pull data from a range of months. I'll look into the periods to date function. Thanks for your reply.
May 19, 2009 at 8:07 am
Marcus,
I think you misunderstood
In MDX you can specify a range as
([Time].[Years].[2009].[Feburary]:[Time].[Years].[2009].[May])
This will give a set with all members:
[Time].[Years].[2009].[Feburary]
[Time].[Years].[2009].[March]
[Time].[Years].[2009].[April]
[Time].[Years].[2009].[May]
So to do this range dynamically you can use
([Time].[Years].[2009].[May].lag(4) : [Time].[Years].[2009].[May])
or in an expression
([Time].lag(4) : [Time].currentmember)
May 19, 2009 at 8:19 am
Thank you Samuel, that does make more sense.
.mf
May 19, 2009 at 8:39 am
Good stuff
Don't forget - that when using a calculated member then the currentmember could be year, quarter, month or day
So if you use the currentmember on the time hierarchy but list the years instead of months then the lag(4) will go 4 years back as it stays in the same level of the hierarchy as the currentmember
May 19, 2009 at 9:05 am
Samuel,
Having trouble pulling the first selected parameter into this calculation… if we use lastperiods in place of lag… how can we get the first selected month from list of parameters into this equation… would be same problem for lag…
Sum(Lastperiods(3,head(strtoset(@DimDateTimeCalendarMonthNo))))
May 19, 2009 at 9:08 am
Whats this variable @DimDateTimeCalendarMonthNo from?
May 19, 2009 at 9:23 am
It's a reporting services parameter to select months, the user can select multiple months.
May 19, 2009 at 9:32 am
Assuming that the variable is being converted to a set then HEAD is the right function to use
I don't think that your variable is being converted though, try this syntax instead (you'll need to prefix the query with an equals (=) and wrap the whole thing in double quotes ("):
="....
Sum(Lastperiods(3,head(STRTOSET ( '{" & Join(Parameters!DimDateTimeCalendarMonthNo.Value,",") & "}', CONSTRAINED))))
....."
May 19, 2009 at 9:42 am
Hi Samuel,
I am working with Marcus on this... are you suggesting we place this code in as an MDX member in SSRS? the "=" is not recognized.
thanks for your help
Jason
May 19, 2009 at 9:49 am
at the moment you have an MDX query in a data source
something like
with member [measures].[mymeasure] as
Sum(Lastperiods(3,head(strtoset(@DimDateTimeCalendarMonthNo))))
select
[measures].[mymeasure] on 0,
[dimension] on 1
from [mycube]
This will need to become
="with member [measures].[mymeasure] as
Sum(Lastperiods(3,head(STRTOSET ( '{" & Join(Parameters!DimDateTimeCalendarMonthNo.Value,",") & "}', CONSTRAINED))))
select
[measures].[mymeasure] on 0,
[dimension] on 1
from [mycube]"
This will allow reporting services to parse out the multi value parameter and build the query for you
May 19, 2009 at 11:34 am
works like a charm!
thanks for your help 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply