Is there a way to show a ratio in Crystal reports? 2

[jschill2628]

Is there a way to show a ratio in Crystal reports? I don’t want a percentage I want the actual numbers i.e. 1/100 not 0.01 or 1%. I assume there is a formula, but I can’t think how that would go.

For example I have:
a formula such as the following: {near miss}

if {IRSCODE.CODE}in ["NME1", "NME2", "NME3"] then "Near Miss" else
if {IRSCODE.CODE}in ["PSE1", "PSE2", "PSE3", "PSE4"] then "Precursor" else
if {IRSCODE.CODE}in ["SSE1", "SSE2", "SSE3", "SSE4", "SSE5"] then "Serious Event" else
"Not Applicable"

I would like to get two different ratios. the first being “Serious event/ near miss”, and the second one being “Serious event/ Precursor”. and as you can see from above the field that I am pulling from is a coded field.

309 serious events, 20,923 Precursor Events, And 5,916 Near Miss Events
I want to show 309/20,923 and 309/5716.
Is there a way to show this in a cross tab or in the details section?

I have tried the following, but these are not working, I am missing something, and I am just to close to this to see what I am missing.

totext(if {IRSCODE.CODE}in ["SSE1", "SSE2", "SSE3", "SSE4", "SSE5"] then "Serious Event" ,0,"")
+"/"+ totext((if {IRSCODE.CODE}in ["NME1", "NME2", "NME3"] then "Near Miss"),0,"")


mid(towords({@Near Miss}), instr(towords({@Near Miss}), "and")+4)


Thanks for all your help!

 

[IanWaterman]

@near miss does not return a number !

Create 3 formuale like
@near miss count
if {IRSCODE.CODE}in ["NME1", "NME2", "NME3"] then 1 else 0
Repeat for Precursor and serious event

In Report footer
@serious to Precursor
totext(sum(@near miss count,0,""))&"/"&totext(sum(@precursor count,0,""))

Ian

 

[hilfy]

I would create running totals - one for each of the items you're trying to count. For example, the "Near Miss" running total would be set up something like this:

Field to Summarize: Some key field in your data
Summary: Count
Evaluate on: Formula
{IRSCODE.CODE}in ["NME1", "NME2", "NME3"]
Reset: If you're counting these items inside a group, you'll want them to reset on change of the group. If this is for the whole report, you'll set this to "Never".

There are then two ways that you can display the data you're looking for:

1. Add a text block to the group footer or report footer. Drag the Serious Event running total into the text box, type "/" after it, then drag the Near Miss or Precursor running total into the text box after the "/". Select each of the running totals in the text box and format them to have no decimal places.

2. Create a formula for each of the ratios and place the formulas in the group footer or report footer. It will look something like this:

ToText({#Serious Event}, "", 0) + "/" + ToText({#Near Miss}, "", 0)

NOTE: Running totals are calculated during the last pass of the report, so they don't have a "final" value until after the group or report data has all been processed. That's why in your case they need to be used in a group or report footer section.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[jschill2628]

@ IanWaterman
When I try your formula, I get an error:
{near Miss count}:
if {IRSCODE.CODE}in ["NME1", "NME2", "NME3"] then 1 else 0

{precursor count}:
if {IRSCODE.CODE}in ["PSE1", "PSE2", "PSE3", "PSE4"] then 1 else 0

{{precursor count}:}:
if {IRSCODE.CODE}in ["SSE1", "SSE2", "SSE3", "SSE4", "SSE5"] then 1 else 0

{serious to near miss}:
totext(sum({@Serious Event Count},0,""))&"/"&totext(sum({@near miss count},0,""))
error: a field is required here, and it highlights the 0


@ hilfy
when I try your formula I get the following error
{serious to Precursor}:
ToText(Sum ({@Serious Event Count}), "", 0) + "/" + ToText(Sum ({@near miss count}), "", 0)
error: bad number format string

Can either of you help me twiek this.

Thank you!

 

[IanWaterman]

Parenthesis problem

{serious to near miss}:
totext(sum({@Serious Event Count}[highlight #EF2929])[/highlight],0,"")&"/"&totext(sum({@near miss count}),0,"")

Ian

 

[jschill2628]

It works!!

totext(sum({@Serious Event Count}),0,"") +" / "+ totext(sum({@near miss count}),0,"")

totext(sum({@Serious Event Count}),0,"") +" / "+ totext(sum({@precursor count}),0,"")

THANK YOU!!!