Wednesday, March 21, 2012

dynamic mdx

Hello i'm new user,

i have a calculated member

the function LINREGSLOPE juste calculate the slope ( ie: a) of the equation Y= a *X+b

calculated member Name= Gain

LINREGSLOPE([TIME].[Month].&[2006-02-01T00:00:00]:[TIME].[Month].&[2006-06-01T00:00:00],[Mesures].[NumofCustomer],[TIME].[Month], [TIME].[Rank])

i write

the formula do the job veru well. What i want to do now is to

transform the expression to be dynamic with two parameter

I hope this will help.

I seen in this forum that this is possble. how to extract

this information. How can i concat the parametre!month.value with the

string ?

i see idea of solution here but i dont know how to exploit it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443431&SiteID=1

how can i do this please help

thanks

hi,

If I am not mistaken, I think you want to restrict the query based on static value instead of dynamic parameters. Right? If that is the case you can just include a where condition to your query like this:

where [Dimension].[Heirarchy].&[Member1]:[Dimension].[Heirarchy].&[Member2]

Note: Dimesion is your dimension name

Heirarchy is your heirachy name.

Member1 is the start date

Member 2 is the end date

I hope this will help.

Sincerely,

Amde

|||thanks amde,

no it's the opposite from static de dynamique.

if the user change the parametre the query will change too.i want to write my expresion with a parameter.

i want somethink like this:

linregslope([Time].[month].&"param1":[Time].[month].&"param2",[measures].[numberCustomer],[Time].[Month].[MonthNumber]).

if you have others questions feel free to ask me.

visite the link in my first post.

I hope this will help.

any idea is welcome

casagrandi

|||

ok thanks

i found the solution

this isi the code:

WITH

set [RangeMonth] as LastPeriods(STRTOMEMBER (@.FromTIMEMonth,CONSTRAINED),STRTOMEMBER (@.ToTIMEMonth,CONSTRAINED))

MEMBER [Measures].[GAIN PER MONTH] AS 'LINREGSLOPE([RangeMonth],[Measures].[mymeasure],Rank([TIME].[Month], [TIME].[Month].Members))'

,format="percent"

SELECT NON EMPTY {[Measures].[GAIN PER MONTH],[Measures] ..........

sql

No comments:

Post a Comment