Group Sub Totals Percentages in Cross Tab Summary

[eliner]

Good Morning – I have a report that is 11 columns across. The report is grouped by vendors with group sub totals. We receive a commission from vendors and pass some of that to our clients. My current cross tab is a summary and work fine. We have added two columns to the report: Gross Comm % and Net Comm %. They are working fine going across. I have also been able to get an over group sub total of these columns. I have been unable to get these two group sub totals into my summary.
My cross tab summary looks like this:
Rows:
Commissionable
Gross Comm
Upfrt Disc
Chk Disc
Net Comm

Columns:
Depend upon the number of vendors on the report.

I want to add Gross Comm % and Net Comm % group sub totals to the report.
I am using Crystal 9.

Thanks

 

[lbass]

Please go into the crosstab expert and report what you have in the "Row", "Column", and "Summary" sections. It appears that you are calling summaries "rows". Also, we need to know what type of summary you are using on the summary fields.

I am also unclear what you mean by group subtotals. Can you show a sample of what your report looks like and then a second sample of how you'd like it to look?

-LB

 

[eliner]

Sorry for the delay in response - been out of the office.

Since the report is sorted by vendor and we get totals for each vendor and the report can have anywhere from 1 to 20 vendors on it, I call the vendor totals sub totals since we do have a grand total for the whole report in the report footer.

Cross Tab expert:
Rows: blank
Columns: VenProfile.Name
Summarized Fields:#Base
#Grp Sub Ttl Com Due
#Grp Sub Ttl Rebate
#Grp Sub Ttl Chk Amt
#Grp Sub Ttl Net Comm

I want to add two more:
#Grp Sub Ttl Gross Comm %
# Grp Sub Ttl Net Comm %

Each of this are a formula:
Gross Comm %: {#Grp Sub Ttl Com Due}/{#base}*100
Net Comm %: {#Grp Sub Ttl Net Comm}/{#base}*100

When I go to the cross tab expert and open it up I do not see either of these formulas to move to the summarize area.

Need to know how to get the above two % items into the summary.

Thanks

 

[lbass]

First create a formula {@0}:

whilereadingrecords;
0

Add this twice to the summary area in your crosstab, so that they are the last two summaries. They will serve as holders to display your results.

Then in preview mode, select your summary {#Grp Sub Ttl Com Due}->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar comdue := currentfieldvalue;
false

Then select {#base} right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar base := currentfieldvalue;
false

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

whileprintingrecords;
numbervar comdue;
numbervar base;
totext(comdue%base,2)+"%"//2 for two decimals

Repeat for net comm, using the second 0 summary to display the results. Note that base is already set up, so just need to create the variable for {#Grp Sub Ttl Net Comm}.

If you want to see the same sort of summaries at any total level, you can select the corresponding totals when you select and set up the inner cells with the variables, and the results will be carried out there as well.

If your summary values are of currency datatype, change all variables to currencyvar instead of numbervar.

-LB

 

[eliner]

Thanks for the information and help.

Think there maybe a syntax error as I am getting a Division by Zero error when I run
The report.

Both “comdue” and “base” are highlighted.

Maybe I should rename some formulas.

Gross Comm:
Formula: @commission/@upfront rebate
Commission: database field name: rescardreservation.totalcommission/100
Upfront: If{UDID.UDIDNO}=99
Then
If is Null ({UDIDS.UDID}) or Not Isnumeric ({UDIDS.UDID})
Then 0
Else Tonumber ({UDIDS.UDID})

GrpSubTtl is a running total:
Field to Summarize: @Total Commission
Type: Sum
Evaluate: on change of grp: Grp 4 Rescardreservation.reservationno – A
Reset: On change of Grp: Grp 1 Vendorprolife.name – A

I am presuming that the report is stopping at the first @0. I would presume that the same would happen with the second @0 as similar names are used in it.

The first formula is: {@Total Commission}-{@Check Amt}-{@Upfront Rebate}
Running total is same except use @netcomm. Type, evaluate and change are the same.
Formula for check amount is the same except the udid # is 98 not 99.

At this point all my other summaries are working in the cross tab as most of them are simple ones.
Base = Round ({RESCARDRESERVATION.CALCTOTALBASE},2 ) /100
Fee = Round ({@Base}+{@Upfront Rebate},2 )
I have a running total that uses @fee
Net Comm = {@Total Commission}-{@Check Amt}-{@Upfront Rebate}
And that is also used in a running total.

Thanks again.

 

[lbass]

Just try changing the display formula to:

whileprintingrecords;
numbervar comdue;
numbervar base;
if base > 0 then
totext(comdue%base,2)+"%"//2 for two decimals

-LB

 

[eliner]

Hello again - that solved part of the problem. No longer am I getting the div/0 response.

What is now appearing when I run the report is nothing under the various vendors for the two new rows added. In the total column I do get 0.00 for each of the rows. For some reason It is not picking up the numbers. They are appearing in the body (detail) of the report correctly.

All my field names are in the Group 4 footer. The totals are in the grp 1 footer, does make a difference?

Here is what is in the various x2 areas you said to use:

Gross: whileprintingrecords;
currencyvar comdue:=currentfieldvalue;
false
Net: whileprintingrecords;
currencyvar comdue:=currentfieldvalue;
false
Base: whileprintingrecords;
currencyvar base:=currentfieldvalue;
false
In the format editor I used the first or top suppress X2, not the one just above "dispay string".

In cross tab itself:
Under Col 1 Name
1st @0 (Gross %)
whileprintingrecords;
currencyvar comdue;
currencyvar base;
if base > 0
then
totext(comdue%base,2)+"%"
@nd @0 (Net %)
whileprintingrecords;
currencyvar comdue;
currencyvar base;
If base > 0
then
totext(comdue%base,2)+"%"

I did a copy and paste on the above as a double check on my typing.

Again, Thanks

 

[lbass]

ALL of the formulas were supposed to be entered in the formatting areas of summaries WITHIN the crosstab. None of this depends upon the non-crosstab areas of the report.

-LB

 

[eliner]

Good Morning - thank you for all your assistance with this problem. Working great now. Just what we wanted, again, thanks.