Formatting a CrossTab

[smil3y]

I have what I thought was a relatively simple job of creating a report that provided summary information from a database.

The two tables in the database are 1 - Injuries and 2 - Classifications. In the injuries table the fields are InjuryID (Autonumber), ClassID, SectionName and DateofInjury. The fields in Classification table are ClassID(Autonumber) and Class (Text).

My .rpt first groups by sectionName (works Fine) and then by Month + Year (works fine).

I then inserted a crosstab that has columns heading of the classification and a Count of the Injuries.DateofInjury. This all works EXCEPT - that when there are no injuries for that month there is no column.
Eg.
ADMIN
Apr 2003 FAI MTI TOTAL
2 1 3

Mar 2003 FAI TOTAL
3 3

I would like to try and get the report in the format:

ADMIN
Apr 2003 FAI MTI LTI RWI TOTAL
2 1 0 0 3

Mar 2003 FAI MTI LTI RWI TOTAL
3 0 0 0 3

Is someone able to help me please.

 

[synapsevampire]

Age old issue, you expect Crystal to fabricate data for period where there isn't any.

Your options would be to create a injuries lookup table as the main table and left outer join it to your data, or create a manual crosstab, group by month/year and use the 3 formula method or Running Totals to handle the summaries.

-k

 

[smil3y]

I am already grouping by month/year. Can you please advise what 3 formula method is. (??)

Many thanks

 

[lbass]

It looks like you are inserting a crosstab in the group (month) header. One option would be to display the crosstab in the report footer, and add the date (grouped by month) as the row field. Then, as long as there was at least one record in the report for each classification (regardless of month) and at least one record for each month (regardless of classification) you would get the desired results.

If you do need the crosstab at the group level, then you have to create a manual crosstab as SV suggested. If you don't have duplicate data, i.e., there is one record appearing per injury date, you could try creating detail level formulas for each classification, e.g.,

//{@FAI}:
if {table.classification} = "FAI" then 1 else 0

//{@MTI}:
if {table.classification} = "MTI" then 1 else 0 //etc.

Then insert sums (not counts) on each formula and drag them to the group header. Then suppress the details section (or suppress the detail-level formulas if you are displaying the details.)

-LB