Cross tabs and sums in crystal 8.5

[maruja]

Greetings:

I am fairly new to Crystal reporting. (5 months) I am an analyst in a medical facility. I have been asked to write a report showing all patients discharged 'alive' from a facility from january 2004 thru july 2005. I decided to do a cross tab. Unfortunately babies are in one table while all others are in another table. Baby key (data type) is a number. Patient ID (data type) is a string. To get a total of each I did a distinct count. I did get counts for both. Now I am trying to 'sum' the data. My row is discharge year. My column heading is discharge month Jan - Dec. My summarized fields are baby key and pt id. I've tried a formula: if Month({c_cmb_vst_v_20050303.dsch_date}) = 1 then (DistinctCount ({pms_baby_v.baby_key}) + DistinctCount({c_cmb_vst_v_20050303.pt_id})) else 0 to get one figure in each column. I keep getting 0. What is wrong with my formula?? Thanks.

 

[lbass]

I think you should try to convert the datatype of {baby.key} and then use a union all to "merge" the two tables. First, add only the baby table to your report and then create a SQL expression like {%patient}:

{fn Convert(baby.`key`,SQL_VARCHAR)}

Then place this on your report along with any other fields you need (like the date field) from the baby table. Then go to database->show SQL Query. At the end of the query, add "Union All" and then add the corresponding query for the other table. It will look something like:

SELECT
pms_baby_v.`dsch_date`,
{fn Convert(pms_baby_v.`baby_key`,SQL_VARCHAR)},
'Baby' as type
FROM
`pms_baby_v` pms_baby_v
union all
SELECT
c_cmb_vst_v_20050303.`dsch_date`,
c_cmb_vst_v_20050303.`pt_id`,
'Other Patient' as type
FROM
`c_cmb_vst_v_20050303` c_cmb_vst_v_20050303

Note that you must have a corresponding field of the same datatype and in the same order as the first half of your query. I added the type field so that you could distinguish the baby and other records if you need to at some point.

Then you should be able to use an inserted crosstab, where you add {%patient} as a summary field (allow the crosstab to use the default "count" as the summary, and then add discharge date as your row field (select it and go to group options->set it to on change of year. Create a new formula {@month} to use as your column field:

month({pms_baby_v.dsch_date})

While in the crosstab expert, after placing it as your column field->select it->group options->customize group name->use a formula to customize name:

monthname(month({pms_baby_v.dsch_date}))

After exiting the crosstab expert, right click on the summary field->change summary and choose distinctcount. If you make the change while in the crosstab expert, you might get a Dr. Watson error (this is related to using a SQL expression), but this works after exiting the crosstab expert.

Refresh the report to see the new column headings.

-LB