crosstab summary formulas

[Maxie1971]

Hi,

sorry new to the forum but could really do with some help....

I have a crosstab in crystal 9

looks like this -
all B2B B2c Balance
serviceDESC Rate
T1 8 100 50 25 25
T2 8 129 128 2 -255

the data is pulled from a sql server 2000 view called VIDS_RPT_IPSC_ADV_RENT_TOTAL

The balance Colunm is the grand total summary and i have created a formula field using

if (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}) and
({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})) and
(Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.RATE})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.RATE}) and
({VIDS_RPT_IPSC_ADV_RENT_TOTAL.RATE}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.RATE}))
then
Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})-{VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}-Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})
else 0

however as i have tried to show in the example for some reason when the calculation produces a negative result it puts some wierd Number in there.

i.e. 129-128-2=-255

anyone got any idea why this might be happening??

Thanks for your help in advance.

 

[lbass]

Is it possible you have two minuses instead of one between the two first fields?

-LB

 

[Maxie1971]

Re IBASS

nope tried looking into that already... i have had another issue like that and had to change a different formula so it was one of the first things i checked.

But thanks

 

[lbass]

I just realized that your sample data is unclear. In your (manual) crosstab, you are showing data on one row that you think is being added/subtracted and yet in your formula you are referencing previous and next rows. I also can't tell which field corresponds to: {VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}.

So how did you create the crosstab? Are we seeing a group section?

-LB

 

[Maxie1971]

The crosstab is created in crystal reports....

VIDS_RPT_IPSC_ADV_RENT_TOTAL is the Data source.

I have attached an image of the actual crosstab result....

you will see the end column is balance. the two fields in here are calculated fields the count is calculated using the following script -

If {VIDS_RPT_IPSC_ADV_RENT_TOTAL.Type}= 'B2B' then
if (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}) and (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
if ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})) and ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})=next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
(Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})-{VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}-Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}))
else (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})-{VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})
else if ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})) and ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})=next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}-Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}))
else 0
else 0


the value is calculated in the same way but using the appropriate field....

the problem can be seen along the bottom edge of the image where it fails to calculate the balance for count correctly as it calculates that 58 - 0 - 0 = 0 and does the same for the rows below this one as well....

hope this explains my problem in enough detail.

cheers

 

[Maxie1971]

Opps just found out can't have spaces in links to file....

the image is on this post....

 

[lbass]

Are you using CR 2008? In earlier versions you could not use a formula with previous() or next() in a crosstab.

-LB

 

[Maxie1971]

it's crystal reports version 9?

 

[lbass]

I'm confused. If you are inserting a crosstab, you would not be able to use your formula in the crosstab as row, column, or summary field, so where exactly are you using it?

-LB

 

[Maxie1971]

I have created the view in SQL server 2000 and then created the cross tab using crosstab expert.... I have then created two calculated fields that I am then using in the summary field rather than the normal sum / percentage fields.... because of the fact that i need to perform a different type of calculation. rather than summing the columns I need to subtract the second two columns form the first column to provide an effective balance of the columns.

the issue is then that if the cross tab row has a zero in it it is because there is no source data i.e a null or nothing.

As a result of this the calculation doesn't work.

This is the problem i am Having....

 

[lbass]

What is the content of the formulas you are using as summary fields? What summary are you inserting on them? You ordinarily cannot use simple calculations in order to compare summaries in a crosstab.

-LB

 

[Maxie1971]

The formula field uses the following formula

If {VIDS_RPT_IPSC_ADV_RENT_TOTAL.Type}= 'B2B' then
if (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}) and (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})={VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
if ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})) and ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})=next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
(Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})-{VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}-Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}))
else (Previous({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})-{VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count})
else if ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC}=Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.ServiceDESC})) and ({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})=next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Rate})then
({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}-Next({VIDS_RPT_IPSC_ADV_RENT_TOTAL.Record_Count}))
else 0
else 0


this is then simply sumed.... which effectively gives me column 1 - column 2 - column 3....

the problem is tha crosstab puts Zero's into the crosstab where there is no data... however the forumla sees the zero as either null or nothing and as a result doesn't do the calculation.

Thanks