Subtracting Values in Crosstab

[jaz237]

Similar to thread767-1210546:

I am using CR XI ver 2 with SQL Server through ODBC and I have a cross tab with the following information:

FY2008 FY2007
Definite 111,111 222,222
Tentative 333,333 444,444

Row = @status
Column = @FY
Summary = sum of command.blockrooms

I need to add a column with the percentage difference in the two years. I attempted to modify the example in the referenced thread but I was unable to do so correctly.

TIA

Julie

 

[lbass]

In this case, I would just create two separate formulas for summary fields, and not use a column in the crosstab:

//{@FY 2007}
if {@FY} = 2007 then {table.amt}

//{@FY 2008}:
if {@FY} = 2008 then {table.amt}

Then add a third summary field {@Diff}:
whilereadingrecords;
0

Go to the customize style tab and choose summary fields->horizontal->add labels.

Add the row field {@status}. Then ini preview mode, select the 2007 summary->right click->format field->suppress->x+2:

whileprintingrecords;
numbervar fy2007 := currentfieldvalue;
false

Then select the 2008 summary->right click->format field->suppress->x+2:

whileprintingrecords;
numbervar fy2008 := currentfieldvalue;
false

Finally, select {@diff}->right click->format field->common tab->display string->x+2 and enter:

whileprintingrecords;
numbervar fy2007;
numbervar fy2008;
totext((fy2008-fy2007)/fy2007,2)+"%"//2 for 2 decimals

You should always specify what version of CR you are using. This solution assumes you have 9.0 or above.

-LB

 

[jaz237]

Thank you LB. I was able to make that work. Just for future reference...when you mention I should specify what version of CR are you referring to something other than CR XI referenced in my original post?

Julie

 

[lbass]

Sorry, I didn't notice that you had mentioned your version.

-LB