CrossTab Report % Calculation

[shelby55]

Hello

I'm using Crystal 8.5 accessing a SQL database.

I have a report with fiscal year and triage level in the rows and I want to show % triage level of total but can't figure out how to do this.

If I only had one row level I would create a field called "triage 1" and it would be:
if triage = 1 then +1 else 0

Then I would create a field called %Triage1 and it would be:
if triage1>0 then triage1*100/total cases

I would put the %Triage1 field in the crosstab and make it a weighted average of total cases and it correctly gives me percent. But I can't get it to work with two layers and I need to show the triage level to correspond with %. Any ideas how to get this to work?

Thanks.

 

[lbass]

Is your summary field a count? If so, in the main report, create a formula like this:

1%distinctcount({table.caseID})

Add this for your percentage of the grand total in the crosstab, using sum as the summary.

-LB

 

[shelby55]

Hi LB

Sorry but I'm not understanding.

The crosstab has fiscal year and then triage level as the two row values. The unique identifier for each case is encounter number which is text. So what is my "table.caseID"?

The problem that I'm having is that the formula isn't showing up to be selected when in the crosstab.

Thanks.

Shelley

 

[lbass]

Okay, I just realized you can't use formulas containing summaries in 8.5 crosstabs, nor is 'display string' available. There is a workaround as follows. First go to the customize style tab and check "row totals on top". Then select the total->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
numbervar curr := currentfieldvalue;
false

I'm assuming your summary is a count of encounter#. Then select an inner cell containing this summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curr := currentfieldvalue;
false

In the main report, create a formula {@0}:

whilereadingrecords;
0

Add this as your second summary field, to act as a holder for the percent. Then select both the total and inner cell for the {@0} summary->right click->format number->check "display currency symbol"->fixed. Then click on "customize". In the number screen->show zero values as:, erase the value so that the box is blank. Then click on the currency symbol tab->enable currency symbol->fixed. Then go to "position" and select -123$. Then go into currency symbol->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar curr;
if tot <> 0 then
totext(curr%tot,1)+"%" //1 for 1 decimal

Basically, this solution uses the currency formula area as a holder for an alternative value display.

-LB