Crystal 2008 how to add an extra column in crosstab 1

[praveen224]

Hello all,

I have a crosstab report that has formula for columns - which ends up being only two columns which represent the year and customer as rows. The summary field is revenue. I am trying to add a column at the end that would calculate the difference in revenue in dollars and also as a percent. Here is an example:
2008 2009
Cust1 $1000 $2000
Cust2 $1500 $1200

I would like to see:
2008 2009 Diff %
Cust1 $1000 $2000 $1000 150%
Cust2 $1500 $1200 ($300) 75%

Any help is appreciated.

Thanks
Praveen

 

[lbass]

I don't have CR 2008, so am unsure how the calculated column/row feature is used, but I could guide you through a way of doing this that works with earlier versions--if you are interested.

-LB

 

[praveen224]

LB,

Thanks for your help - I would be interested in your solution.

Praveen

 

[lbass]

First remove your column on year, and instead create two conditional formulas and add them as your first two summary fields:

//{@lastyr}:
if year({table.date})=year(currentdate)-1 then
{table.amt}

//{@curryr}:
if year({table.date})=year(currentdate) then
{table.amt}

Then create another formula {@0}:
whilereadingrecords;
0

Add this twice as the third and fourth summary fields. In preview mode, select {@lastyr}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar lastyr := currentfieldvalue;
false

Repeat for {@curryr}, but changing the formula to:
whileprintingrecords;
currencyvar curryr := currentfieldvalue;
false

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

whileprintingrecords;
currencyvar lastyr;
currencyvar curryr;
totext(curryr-lastyr,0,"")

Then repeat for the second {@0} summary but change the formula to:

whileprintingrecords;
currencyvar lastyr;
currencyvar curryr;
if curryr <> 0 then
totext((curryr-lastyr)%curryr)+"%"

Go to the customize style tab and in the summary section select horizontal display and show labels. Adjust the label names as you wish in draft or preview mode.

-LB

 

[praveen224]

That worked great. Thanks LB. Is there any way to actually show the Year as the column Header. Currently it is a Text Object and would like it to show 2008 and 2009 for Last Year and Current Year (based on database date values).

Thanks again.
Praveen

 

[lbass]

You should be able to use 'display string' for that too. Right click on the label->format field->display string:

//thisyear:
totext(year(currentdate),"0000")

//lastyear:
totext(year(currentdate)-1,"0000")

-LB

 

[chelseatech]

It is apowerful and complicated feature. We covered this in a sereies of articles in our newsletter. Download the back issues to see how it works.

But if you columns are just 2008 and 2009, you don't need a crosstab. A couple of conditional formulas and summaries are a lot easier to work with.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

chelseatech is correct and I should have said as much. The only advantage of the inserted crosstab is if you want the automatic grid and of course an inserted crosstab can be placed in a report footer, whereas the manual crosstab technique requires you to use group and detail sections to create it, so if you are already using those sections in another way, and you wanted the summary in a report footer or header, you would have to insert a subreport in order to use a manual crosstab.

-LB