Calulating % change between 2 columns of an inserted crosstab total 1

[Gus02]

Hello,

Data sample :
[(Year).PreviousValue = (Year - 1).Value]:

Key Year Value PreviousValue
001 2009 10 9
002 2009 20 19
001 2010 11 10
002 2010 22 20

Inserted crosstab (Sum), I would like to display the % change in the total :
2009 2010
Total PreviousValue 28 30
Total Value 30 33

% change ?% ?%

Is it possible to display the % change, in the inserted crosstab, on a total row (and not the sum of the % change of each row)?

Thank you for your help.

 

[lbass]

What version of CR are you using (you should always specify this)? And will you always be evaluating only two years? It might be easier just to use a manual crosstab. What is the current crosstab setup (row, column, summary)--I'm wondering if it is as simple as you show.

-LB

 

[Gus02]

Thank you for your interest lbass.

I'm using CR10 and crosstab setup is :
- Column = Year
- Rows = none
- Summarized Fields = Sum of PreviousValue, Sum of Value

Unfortunately, the report is more complexe than explained
- data selection does not filter by year, so there may be a different number of years according to the historical in the database on which the report is run. I foresee the possibility of displaying up to 10 years (limit related to the width of the page).
- In addition, this report is a summary dashboard and there are about 20 inserted crosstab of this type ... it was so easy to use inserted crosstab !

If this is not possible (manual crosstab is too heavy), I will see to group the data into the database.

 

[lbass]

Okay, then what you need to do is create a formula and add it as your third summary:

whilereadingrecords;
0

This will act as a holder for the percentage. Suppress this summary and label for the inner cells, and drag the border vertically to minimize the summary height.

Then right click on the previousvalue summary->format field->suppress->x+2 and enter:

whileprintgrecords;
numbervar prev := currentfieldvalue;
false

Then right click on the value summary->format field->suppress->x+2 and enter:

whileprintgrecords;
numbervar curr := currentfieldvalue;
false

Then right click on the {@0} summary->format field->DISPLAY STRING->x+2 and enter:

whileprintgrecords;
numbervar prev;
numbervar curr;
if prev = 0 then
"--" else
totext((curr-prev)%prev,1)+"%" //1 for one decimal

-LB

 

[Gus02]

It's wonderful, it works! and the first time.
How can you think of stuff like this ?

Big thank you
I'll keep this thing carefully for fear of not remind me ...