Change member name/caption in a Set
hi
i have 2 sets union query. 2 sets when unioned have duplicate member, want plotting on ssrs report. want change duplicate member caption on first set can seperate them on graph.
this have far. first set forecast_periods single member, it's here need change caption/text.
in ssrs dataset returns year , month, ie "2011" "may" (2 seperate columns). want "2011 forecast" "may"
with
set [forecast_periods] as
{
strtomember
("[calendar].[year - month].[month].&[201105]"
,
constrained)
}
set[periods] as
{
strtomember
("[calendar].[year - month].[month].&[201105]"
,
constrained
).siblings
}
set
[all_periods] as union(periods,forecast_periods,all)
i go on use set all_periods on rows axis of query.
any ideas appreciated.
cheers
thanks reply darren. don't have scenario dimension.
is there capability add type of flag or property dynamically sets?
or, how row number across unioned set, pick first or last ro number in report target duplicate member.
i have tried rank fx ranks 2 members same.
cheers
techniques know simulating row number rely on rank , because have duplicate members same rank.
the technique can think of @ moment dynamically adding flag hijack dimension.
so in example below "report" not use promotions dimension, creating 2 calculated measures map member. can use scenario dimension add dynamic "flag"
eg.
with
member [promotion].[promotions].[actual]
[promotion].[promotions].[all promotions]
member [promotion].[promotions].[forecast]
[promotion].[promotions].[all promotions]
set d as
union({[date].[calendar].[month].&[2002]&[2]} * {[promotion].[actual]}
,{[date].[calendar].[month].&[2002]&[2]} * {[promotion].[forecast]}
,all)
select
{[measures].[internet sales amount]} on 0,
d on 1
[adventure works]
this generates result following:
month promotion internet sales amount
february 2002 actual $550,816.69
february 2002 forecast $550,816.69
http://darren.gosbell.com - please mark correct answers
SQL Server > SQL Server Analysis Services
Comments
Post a Comment