Cross tabs for summary level?

[Timbe]

I've developed a report grouped on a distinct patient MRN. I go through all records (at the detail level) for each patient and report whether or not they have ever had a certain diagnosis and/or a certain procedure. This logic all works fine.

The problem I'm having is creating a cross tab (2 x 2 table) for an odds ratio. If they have a and b, if they have a not b, b not a, neither a nor b.
The cross tab only seems to work at the individual record level, in spite of my formulaes and groupings. All the grouping are unavailable.

As a later refinement I will be showing the detail level to the user (if needed) and will be selecting time frames for the diagnoses and procedures to occur. So I need to keep this capacity.

I think canned cross tabs can't do this, any other ideas?

Thanks

 

[kenhamady]

Cross-tabs summarize the details individually. They can't summarize combinations of details.

However, if you link the table to itself using MRN (a many to many join, remember) all records will find all others and then you will have 2 instances of everything in one record. Then you can write a formula like:

If {instance1.Diag1} = "302.10" and
{instance2.Diag1} = "296.25" then "CatA" else
....

You could then cross-tab that formula with a distinct count, which would eliminate all duplicates. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Timbe]

I like your answer, but this is a humongous database with 50 or 60 entries for many individual patients. A self join might kill me, or the data warehouse group.

Is there a way to use running totals in the Cross tab? I've changed the logic so that the last record shows if condition a or b were ever true. Now I'm trying to use the last record for each group(distinct patient) to create the cross tab...

Thanks for any assistance. (I'm using 8.5 by the way)

--Timbe

 

[kenhamady]

You can't use running totals or any group info in cross-tabs.

While my solution is a many to many join, the criteria on the first table should behave like a more common one-to-many. Not all of the records from the first table will make it into the results. The more specific you can be with criteria, the better the SQL will perform. Check the SQL before running it to make sure that your criteria is getting into the WHERE of the SQL. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/