Dynamic set from dynamic members and parameters creating dynamic queries?

 

hi,

 

i have matrix defined in ssrs see top 20 elements of list per month , many months defined user via parameter history. trying use members:members define range on fly not understand why empty in following test:

 

params: @history set 2 , month parametert set 6

 

the main bulk of mdx is:

 

code snippet

member [measures].[courant] membertostr([time].[month].currentmember)

 

member [measures].[history] "[time].[month].&[" + cstr(val(vba!mid([measures].[courant],18,1))-@history)+ "]"

 

set [setup] members([measures].[history]):members("[time].[month].&[" + [measures].[courant]+"]")

 

 

the select things show wrong

1 -

code snippet

select {[measures].[history],[measures].[courant]} on 0, {[setup]} on 1 [cube]

 

gives me empty line

 

2 -

code snippet

select {[measures].[history],[measures].[courant]} on 0, {[time].[month].[6]} on 1 [cube]

 

 

gives me expecting both, is:

 

month: 6
history: [time].[month].&[ 4 ]
courant: [time].[month].&[ 6 ]

 

this means me [measures].[history] defined reason cannot used in range definition.

 

how should define [measures].[history] can used when define range?

 

thanks in advance!

 

related post: http://forums.microsoft.com/msdn/showpost.aspx?postid=3538276&siteid=1

sorry never posted solution; here is:

my cube has these dimensions:

time – year / month / week / day / hour / twelth of hour (don' t ask)

stored procedures – stored procedure’s type / stored procedure's label

database – server / database name

and following measures:

measures – occurrences / duration / cpu / reads / writes / contention

this report displays top x of stored procedures according user-defined measure , specified period. period defined per users under parameter “grain” can month, week, day, etc. to allow users follow what’s going on period period-n, users can define “history” value. the parameter list allows users choose how many procedures must displayed: first 10, 20 or 50 procedures.
for example, users can see current ranking of 20 (list) slowest procedures according duration (measure) , movement in ranking list last 3 months (history + grain) on specified database (database name).

 

here code (please come if have faster one):

with

member [previous_rank_measure] as ([measures].[rank_measure],strtomember([measures].[courant]).prevmember)

member [measures].[rank_difference] as iif([measures].[previous_rank_measure]=null,null,[measures].[previous_rank_measure]-[measures].[rank_measure])

member [measures].[measure] as

iif(@measure="occurrences",[measures].[v trace facts count],

iif(@measure="duration",[measures].[avg_duration],

iif(@measure="cpu",[measures].[avg_cpu],

iif(@measure="reads",[measures].[avg_reads],

iif(@measure="writes",[measures].[avg_writes],

[measures].[avg_contentionduration])))))

set [setgrain] as

case

when @grain="month" then

lastperiods(strtovalue(@history)+1,strtomember(@month))

when @grain="week" then

lastperiods(strtovalue(@history)+1,strtomember(@week))

when @grain="day" then

lastperiods(strtovalue(@history)+1,strtomember(@day))

when @grain="hour" then

lastperiods(strtovalue(@history)+1,strtomember(@hour))

when @grain="twelfth" then

lastperiods(strtovalue(@history)+1,strtomember(@twelfth))

end

member [measures].[rank_measure] as

iif(@measure="occurrences",[measures].[rank_count],

iif(@measure="duration",[measures].[rank_avg_duration],

iif(@measure="cpu",[measures].[rank_avg_cpu],

iif(@measure="reads",[measures].[rank_avg_reads],

iif(@measure="writes",[measures].[rank_avg_writes],

[measures].[rank_avg_contentionduration])))))

member [measures].[courant] as

case

when @grain="month" then membertostr([time].[month].currentmember)

when @grain="week" then membertostr([time].[week].currentmember)

when @grain="day" then membertostr([time].[day].currentmember)

when @grain="hour" then membertostr([time].[hour].currentmember)

when @grain="twelfth" then membertostr([time].[twelfth].currentmember)

end

set [procedures] as

filter([procedure].[procedure name].[procedure name].members, ([procedure].[procedure name].currentmember))

set [setprocedures] as

case

when @grain="month" then generate([setgrain], {[time].[month].currentmember}*head(order([procedures],[measures].[measure],bdesc),strtovalue(@list, constrained)))

when @grain="week" then generate([setgrain], {[time].[week].currentmember}*head(order([procedures],[measures].[measure],bdesc),strtovalue(@list, constrained)))

when @grain="day" then generate([setgrain], {[time].[day].currentmember}*head(order([procedures],[measures].[measure],bdesc),strtovalue(@list, constrained)))

when @grain="hour" then generate([setgrain], {[time].[hour].currentmember}*head(order([procedures],[measures].[measure],bdesc),strtovalue(@list, constrained)))

when @grain="twelfth" then generate([setgrain], {[time].[twelfth].currentmember}*head(order([procedures],[measures].[measure],bdesc),strtovalue(@list, constrained)))

end

select {

[measures].[measure],

[measures].[rank_measure] ,

[measures].[rank_difference]

} on 0 , [setprocedures]

on rows from (select ( strtoset(@databasename) )

on columns from ( select ( strtoset(@year, constrained) )

on columns from [tracestop])) where

iif (@grain<>"twelfth",

iif (@grain<>"hour",

iif (@grain<>"day",

(

iif( strtoset(@year, constrained).count = 1, strtoset(@year, constrained), [time].[year].currentmember ),

iif( strtoset(@databasename).count = 1, strtoset(@databasename), [database].[database name].currentmember)

),

(

iif( strtoset(@year, constrained).count = 1, strtoset(@year, constrained), [time].[year].currentmember ),

iif( strtoset(@databasename).count = 1, strtoset(@databasename), [database].[database name].currentmember),

iif( strtoset(@month, constrained).count = 1, strtoset(@month, constrained), [time].[month].currentmember)

)

),

(

iif( strtoset(@year, constrained).count = 1, strtoset(@year, constrained), [time].[year].currentmember ),

iif( strtoset(@databasename).count = 1, strtoset(@databasename), [database].[database name].currentmember),

iif( strtoset(@month, constrained).count = 1, strtoset(@month, constrained), [time].[month].currentmember),

iif( strtoset(@day, constrained).count = 1, strtoset(@day, constrained), [time].[day].currentmember)

)

),

(

iif( strtoset(@year, constrained).count = 1, strtoset(@year, constrained), [time].[year].currentmember ),

iif( strtoset(@databasename).count = 1, strtoset(@databasename), [database].[database name].currentmember),

iif( strtoset(@month, constrained).count = 1, strtoset(@month, constrained), [time].[month].currentmember),

iif( strtoset(@day, constrained).count = 1, strtoset(@day, constrained), [time].[day].currentmember),

iif( strtoset(@hour, constrained).count = 1, strtoset(@hour, constrained), [time].[hour].currentmember)

)

)

cell properties value

 

SQL Server  >  SQL Server Analysis Services