Grouping like data from different table fields

[BigC72]

I am using CR 8.5 and pulling Diagnosis Code data from a DB2 database. I am pulling all instances of certain diagnosis codes that appear in either Diagnosis Primary, Diagnosis Secondary or Diagnosis Tertiary fields that all reside within one table. I have added those three fields to the details section of the report and used formulas to suppress unwanted codes. Now I would like to be able to group all like codes for a count of the number of instances that code is used throughout. Here is a sample of the data:

Diag Primary Diag Secondary Diag Tertiary
578.1
414.00
433.10
433.10
578.1

And so on, and so on. I can create groups for each field and do a formula to add each and give me a total but I was hoping there is a more straightforward approach to grouping them all together and then doing a running total to give me a total count. Thanks...

 

[synapsevampire]

Ask your dba to create a view akin to:

Create view MyView as
select 'Diag Primary' typ, Diag Primary from table1
union all
select 'Diag Secondary' typ, Diag Secondary from table2
union all
select 'Diag Tertiary' typ, Diag Tertiary from table3

Or you can create a query using table1 and then use the Database->Show SQL Query to add in the Union All lines using the other tables data.

You'll likely additional columns as well, just include them and make sure that the Union selects have the same type and quantity of columns as it builds a single data set from it.

-k