Adding Formulas in Cross Tab

[CJP12711]

Hello, I'm using CR10. I have a data set like the one below:

case 1
type Diagnostic

Case 2
type Diagnostic
type Interventional

Case 3
type Interventional

I have used the following formula to create a tally for each case type:

if {CATH_PROC_V.CASE_TYPE} = "Interventional Cath" then 1
CDbl(
sum({@Interventional},{CATH.CATHID}) > 0 and
not({CATH_PROC_V.CASE_TYPE} in ['Diagnostic Cath']
) or
sum({@Interventional},{CATH.CATHID}) = 0)

The results I'm looking for, would be a cross tab report similar to this:

Diagnostic Cases 1
Interventional 2

If it is only a diagnostic case, then the tally is 1 for diagnostic. If it is only an interventional case, then the tally is 1 for Interventional. If there is a diagnostic and interventional in the same case, we will ignore the diagnostic, and count the interventional.

When I use the above formula, and look at the details in my report, it is correct. But, when I put it into the cross tab, and sum on the tally, the numbers are very inflated (see below). I'm having a tough time understanding why.

Total
Cardiac - Diagnostic 7
Cardiac - Interventional 27
Total 34


Any help is greatly appreciated!
CJ

 

[lbass]

I think you need to use the maximum type as your row field in the crosstab ("I" being greater than "D"). To do this, you could use a command, as in:

select max(table.`type`) as maxtype, table.`caseID`
from table
group by table.`caseID`

...and link this to the main table on caseID and type, using "enforce both" or setting {command.maxtype} = {table.type} in the record selection formula. Or, better yet, create a command to use as your sole datasource, as in:

select (select max(A.`type`) from table A where
A.`case ID` = table.`case ID`) as maxtype, table.`caseID`, table.`otherfield1`, table.`otherfield2`
from table

You can then use {command.maxtype} as your rowfield in a crosstab.

-LB

 

[CJP12711]

Thanks lbass, I'm not familiar with writing select statements or commands in SQL. I usually write formulas or parameters, although I'm very comfortable with writing SQL and joins. Where do these go in CR?

'Thanks!
CJ

 

[lbass]

Go to database->database expert->your datasource->add command and enter the query there. The syntax/punctuation depends upon your datasource/connectivity, and if you are unsure, try looking at report->show SQL query to see how you should write this.

-LB

 

[CJP12711]

Thanks LB - I'm still working on this command. It hasn't quite worked yet, but I'm still digging around... Stay tuned!

 

[CJP12711]

By using the 'select distinct records only' option, this is now working. Thanks so much for your help!
CJ

 

[CJP12711]

Hi lbass, I have 1 more question about this formula...

All is working well, with the exception of the PV cases. I have the data below:

case 1
type Diagnostic

Case 2
type Diagnostic
type Interventional

Case 3
type Interventional

Case 4
type Diagnostic PV

Case 5
type Interventional PV

Case 6
type Diagnostic PV
type Interventional PV

Formulas:
if {CATH_PROC_V.CASE_TYPE} = "Interventional Cath" then 1


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



The formula for the regular interventional and diagnostic case counts are correct, as are most of the PV counts. There are 8 cases per month (on average) that have both types of PV in the same case - like Case 6 above. When I count, based on the formula above, it double counts those 8 cases, so the count shows up as 16, instead of 8. How can I incorporate this to only count the cases - not the types?


This is what my counts look like now:
Diagnostic - 1
Interventional - 2
PV - 4

In the end, using the examples above, my counts should look like this:

Diagnostic - 1
Interventional - 2
PV - 3

Thanks again for your help!
CJ

 

[lbass]

I'm sorry, but I can't follow what you are doing.

-LB

 

[CJP12711]

Sorry - I'll try to be clearer. I'm wanting to do the same thing with the PV case types as I did with the interventional and diagnostic. I need to count by case - not case type. So, in the case of diagnostic and interventional, if there are both case types in the same case, I only want to count the interventional. IN the end, I want a cross tab report to reflect this:
Jan Feb
Interventional 1 2
Diagnostic 1 1
Specials 2 4

My current cross tab (which is incorrect) is showing this:
Jan Feb
Interventional 1 2
Diagnostic 1 1
Specials 3 5


Rules:
1) If there is a single case with both an Interventional and Diagnostic case type, only count the interventional. Do not count the diagnostic case type. (this is reflecting correctly)
2) If there is a single case with only a diagnostic case type, count the diagnostic case type. (this is reflecting correctly.)
3) If there is a single case with only an interventional case type, count the interventional case type. (this is reflecting correctly)
4) Diagnostic PV and Interventional PV fall under the 'Specials' Category. If a case has multiple case types, we only want to count that case once. Currently, the cross tab report is counting the cases with both case types as 2 cases, instead of 1. (this is not correct)

This is the data:
Case ID Case Type Date (count)
1 Interventional Feb (1)
1 Diagnostic Feb (0)
2 Interventional Feb (1)
3 Diagnostic PV Jan (0)
3 Interventional PV Jan (1)
4 Interventional PV Feb (1)
5 Diagnostic PV Feb (1)
6 Interventional Jan (1)
7 Diagnostic Feb (1)
8 Diagnostic PV Feb (0)
8 Interventional PV Feb (1)
9 Diagnostic PV Feb (1)
10 Interventional PV Jan (1)
11 Diagnostic Jan (1)


Does that make any more sense?

 

[CJP12711]

Hi lbass, did this make sense to you what I'm trying to do?
CJ

 

[lbass]

I can't tell what formulas you are actually using or whether you followed my earlier suggestion. You keep showing formulas without naming them, and also showing nested formulas without displaying the contents, so I'm unclear what you are doing.

-LB