percentage in a cross tab

[jschill2628]

I want to put a percentage in a cross tab.

What I have is the count of incidents, then below it I have the count of open incidents. I would like to take (1-((Count({IRSCLAIM.CLAIM}))/{@cout of open})), to get the percentage of open incidents. I would also like to put this in a cross tab. Am I doing something wrong, well I know I am because I am not getting the results I want, but is there a better way to do this to get the end result with a percentage of open incidents in the cross tab with the other information? Thank you. I hope I explained this well enough. I am using crystal reports 2008 that I will published on business object

 

[lbass]

I think you have the formula backwards.

What is the content of {@count of open}?

What fields are you using for the row and column in the crosstab?

In what section are you placing the crosstab?

-LB

 

[jschill2628]

You are correct; I believe I wrote this wrong. Let me show you an example:
Count of Incidents: 336
Sum of @count of open: 3
Percentage that I want to get: 99% (essentially the percentage of closed incidents)
When I do it in excel its (1-(Sum of @count of open / Count of Incidents))

For the Cross tab:
- In the rows I have the facility name
- In the columns I have the create date (the date the incident was created)
- In the Summarized Fields I have Count of Claims, and beneath that Sum of @count of Open

Formulas:
- {@count of Open} = if {@Closed} = "Closed" then 0 else 1
- {@Closed} = if {IRSCLAIMSA1.SA151} = "3" and {IRSCLAIMSA1.SA152} = "3" then "Closed" else "Not Closed"

 

[lbass]

Create a formula {@0} to act as a holder:

whilereadingrecords;
0

Add this as your third summary. In preview mode, select the count of claims summary in an inner cell ->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
false

Then select the count of {@open}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar open := currentfieldvalue;
false

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

whileprintingrecords;
numbervar tot;
numbervar open;
totext(100-(open%tot),2)+% //2 for two decimals

-LB

 

[chelseatech]

If you are running Crystal 2008 you can use the new Grid functions in a formula to create an embedded summary. I've covered this in a previous issue of Crystal Clear, but do that example next week in my blog at

http://www.crystalkiwi.com/blog.htm

Editor and Publisher of Crystal Clear

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

 

[jschill2628]

@ LB

what if the formula is coming up with an error message: "Division by zero"

what can I do then. In this case the hospital has closed out all incidents and have none remaining, so it should be returning it with 100%.

 

[lbass]

That wouldn't cause a division by zero issue. Only if there were 0 claims would there be a division by zero error.

whileprintingrecords;
numbervar tot;
numbervar open;
if tot <> 0 then
totext(100-(open%tot),2)+% //2 for two decimals

-LB