Crosstab - difference between years

[bgamoretti]

Hi guys, I'm new about report studio, so i need help with a crosstab report.

i have to do a report like this:




the year is a parameter (you could chose 1,2,3 ... years, is dynamic)
I've got the columns in blue, but i don't know how to create the columns in green (is the difference between the year 2009 and the other years).

Thanks for your help.

PD. is not dimensional is relational.

[vinu]

Calculate the data for 2009 ,2008 and 2007 separately.
And after that you can calculate from them to achieve u r results by adding calculated data items.

2009=if (year = 2009) then (measure value data item) else (0).
2008=if (year = 2008) then (measure value data item) else (0).
2007=if (year = 2007) then (measure value data item) else (0).
2009Vs2008=2009-2008
2009Vs2007=2009-2007

add the above query items to your crosstab column section.


regards,
Venu

[bgamoretti]

thanks YVGN for reply.
I tried to do that, but i have a problem.
the years are in a parameter from a prompt (you chose from a value prompt multi select the years, for example 2009,2006,2004 in a parameter ?year?). Then I tried to separate these values with substring(?year?;1;4) and it works (i get '2009'), but when I want to get the second year with substring(?year?;7;4) (I think the second year begins in the 7th position because the string of ?year? is '2009; 2006; 2004') it doesn't work.

Could I have other ways to get the values of a parameter without substring?

[bgamoretti]

any idea?  :'(

[imts]

The number of years that the user selects from the list box  -is it fixed ?

Else you cannot use the substring method ; you first need to calculate the length of the parameter and then decide how many years the user has chosen and then calculate how many data items are needed.

-Tarun