Calculate Case counts minus cancelled cases

[CJP12711]

I am created a report in Crystal 10 to pull a list of case counts for the year. It's using a chart to break down by case type for each month.

The report currently looks like this:
Jan Total
Diagnostic 76 76
Interventional 37 37
Pacers 33 33
Specials 20 20
Total 166 166

The total for each case type is determined by a distinct count of the following formulas:

if {CATH_PROC_V.CASE_TYPE} = "Interventional Cath" then 1
else if {CATH_PROC_V.CASE_TYPE} = "Interventional PV" then 1

CDBl
(sum({@Interventional},{CATH.CATHID}) > 0
and not({CATH_PROC_V.CASE_TYPE} in ['Diagnostic Cath','Diagnostic PV'])
or
(sum({@Interventional},{CATH.CATHID}) = 0)
)

My question is how to modify this report to restrict based on a procedure? For each case there are multiple procedures. If I simply add 'is not one of' witht he cancellation procedure name in the select expert, it then counts all the procedures, rather than the case counts. Is there an easy way to accomplish this? Hopefully I have given you enough information...

Thanks for any help!

 

[CJP12711]

To word another way, I don't want to count any case that has the 'Cancelled' procedure.

 

[MCuthill]

CJP12711,

Assuming report structure like:
Group 1: Case Type (Diag, Intervention, Etc)
- Group 2: Case Number
- - Detail: Procedure

At the details level, create a flag for a Cancelled procedure being found. Such as "IF {Table.Field}="Cancelled" then 1 else 0" (or however it is your cancelled procedures are identified). Then sum this up to the group level.

Then create a RunningTotal Field which looks like:

WhilePrintingRecords;
Shared NumberVar CaseCount;
IF Sum({@AboveFormula},{Table.Case Number})=0 THEN
(
    CaseCount:=CaseCount + 1
)

The above is a very rough idea as to how to proceed, and based on a few assumptions. I am unsure if a RunningTotal can be used in a crosstab or not. "CaseCount" will also likely need a reset formula (at the Group 1 Header level in my assumed report structure).

I hope this helps,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[CJP12711]

Thanks for the response! The report actually groups on location and case ID. There is a cross tab report in the Location header. No grouping or anything for the case type.

Would this apply to a cross tab report also?

 

[MCuthill]

CJP12711,

I *think* the above should work in a crosstab, but sadly do not have Crystal in front of me this afternoon to test for certain. It should only take a moment to create the formula's and give it a try though.

I assume it is the crosstab from the Group Header 1 that is in your orginal post? I beleive the crosstab would need to be moved to Group Footer 1 for the above approach to work - as Variables / Formulas are calculated as one reads a page (Top to bottom, left to right).


You would need 4x formulas for the test.
1) Flag_Cancellation - where a 1 is used to identify a cancelled procedure. (in your details section)
2) VariableReset_CaseCounter - where the number of cases is reset to 0 at the beginning of a location (Group Header 1)
3) RunningTotal_CaseCounter - as outlined above. (Group Header (or Footer) 2)
4) Display_CaseCounter - to display results and/or use in crosstab. This becomes the field summed in the details of the crosstab. (place formula in Group Footer 1)

#1 & #3 I outlined in my previous post.
#2 & #4 are as follows:

#2: VariableReset_CaseCounter

WhilePrintingRecords;
Shared NumberVar CaseCount:=0;

#4: VariableDisplay_CaseCounter

WhilePrintingRecords;
Shared NumberVar CaseCount;

From a logical perspective, the idea of the above approach is to first identify if a procedure is a cancellation then add the number of cancellations up per case. If a case has 0 cancellations, increment the RunningTotal Variable.

I hope this helps, my apologies for being unable to test the above.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Another approach would be to create the following formulas

//{@Cancelled}:
if {table.procedurestatus} = "Cancelled" then 1

//{@Null:
Open a new formula in the field explorer->formula->new and save it without entering anything.

//{@Count}:
if sum({@Cancelled},{table.caseID})= 0 then
{table.caseID} else
tonumber({@Null}) //remove the tonumber if caseID is a string

Then set up the crosstab with "Type" as the row, and "Date" on change of month as the column, and {@Count} using a distinctcount as the summary. Place the crosstab in the location group section. In the customize style tab, you can format it to "suppress blank rows" and "suppress blank columns".

-LB

 

[CJP12711]

Thanks to both of you for your suggestions!

lbass, this is where I was heading also, but I got stuck in how to incorporate the original counts with this as well.

Currently the cross tab has the type as the row, date as the column, and @Diagnostic Counts as the distinct count value.

The logic for @Diagnostic is here:

//{@Interventional}:
if {CATH_PROC_V.CASE_TYPE} = "Interventional Cath" then 1
else if {CATH_PROC_V.CASE_TYPE} = "Interventional PV" then 1

//{@Diagnostic}:
CDBl
(sum({@Interventional},{CATH.CATHID}) > 0
and not({CATH_PROC_V.CASE_TYPE} in ['Diagnostic Cath','Diagnostic PV'])
or
(sum({@Interventional},{CATH.CATHID}) = 0)
)

How would I incorporate these with your @Count formula above?

Thanks for your help - much appreciated!

 

[lbass]

I don't see the logic of what you are doing with these formulas, but you could build them in I guess.

//{@Count}:
if sum({@Cancelled},{CATH.CATHID})= 0 and
(
(
sum({@Interventional},{CATH.CATHID}) > 0 and
not({CATH_PROC_V.CASE_TYPE} in ['Diagnostic Cath','Diagnostic PV'])
) or
sum({@Interventional},{CATH.CATHID}) = 0
) then
{CATH.CATHID} else
tonumber({@Null}) //remove the tonumber if caseID is a string

Note the change in parens.

-LB

 

[CJP12711]

The counts here are basically to determine the type of case. A case can be Diagnostic, Interventional, or Diagnostic and Interventional. If it's both, only the intervention is counted - not the diagnostic.

I'll give this a try - thanks so much for your help!

 

[CJP12711]

What is the @Null?

 

[CJP12711]

Sorry - Missed it from above. I see it...

 

[CJP12711]

Something isn't quite right here... The numbers exploded exponentially! Instead of a few thousand, now it's several million!

 

[lbass]

Please paste the formula you actually used for the count into this thread.

-LB

 

[CJP12711]

Here it is:


//{@Cancelled}
if {CATH_PROC.PROCEDURE_NAME} like '*CANCEL*' then 1

//{@Interventional}
if {CATH_PROC_V.CASE_TYPE} = "Interventional Cath" then 1
else if {CATH_PROC_V.CASE_TYPE} = "Interventional PV" then 1

//{@Count}:
if sum({@Cancelled},{CATH.CATHID})= 0 and
(
(
sum({@Interventional},{CATH.CATHID}) > 0 and
not({CATH_PROC_V.CASE_TYPE} in ['Diagnostic Cath','Diagnostic PV'])
) or
sum({@Interventional},{CATH.CATHID}) = 0
) then
{CATH.CATHID} else
tonumber({@Null})

 

[lbass]

Can you verify that the procedure name would appear in upper case in the database?

-LB

 

[CJP12711]

Yes - I can verify that. It's all caps.

 

[lbass]

Did you insert a distinctcount on the formula (not a sum)?

-LB

 

[CJP12711]

I did do that, and it brought the numbers back down, but they are about 25% lower than expected, based on manual counts...

 

[lbass]

I don't think I have enough information to be able to offer any more help. You need to take a look at the data at the detail level and compare to the summaries to see if you can see why this is going wrong. I would check the basic formulas to see what they are returning at the detail level.

-LB