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

Popular posts from this blog

Motherboard replacement

Remote Desktop App - Error 0x207 or 0x607

Cannot create Full Text Search catalog after upgrading to V12 - Database is not fully started up or it is not in an ONLINE state