CrossTab or Not 1

[smil3y]

I have spent the last week trying to use a crosstab to present data that I need in a particular format. Eventually I found that a manual crosstab may be best option.

I have three tables - Injuries, Sections and Classification.
Injuries has fields - InjID, Date, SectionID and ClassID.
Section has fields - SectionID and Section
Classification has fields - ClassID and Classification.

I think I need to use a manual crosstab but seek advice please. My data needs to be presented as follows:
Classes
Month MTI FAI LTI Total 6MMA
Jul 2002 0 1 2 3 ?
Aug 2002 1 0 1 2 ?
Sep 2002 2 1 0 3 ?
etc
Dec 2003 1 3 1 4 ?


The classes come from Classes table and there could be several classes depending on users requirements.

Months need to be displayed consecutively (from graph later) Total is just adding class occurences and the calculation for 6MMA is a simple formula using the Total number.

Can this be done using a manual crosstab. If not what option do I have?

 

[lbass]

Yes, you can, and in one of your previous posts thread767-758219 I explained how to do a manual crosstab. (There are also a couple of FAQs on how to do this.) The only difference is now you want to add a calculated column. If you need help with that, please provide the formula you are using and/or explain what the formula should do.

If you tried the manual crosstab and didn't get the results you wanted, please provide a sample of the results and a sample of what the results should have been.

-LB

 

[smil3y]

Yes I was inserting crosstab in group header. Tried your suggestion from previous thread but did not

get very different results from when I placed the crosstab in the Group Header.

My problem is that I need to group by Section at the top level. However, not all sections may be used

in the injuries table. What I did was load the injuries, Sections and Classification Tables (with

relationships), then load the Sections table again as an Alias (Section_1). I grouped on Section_1

which gives me groups for every Section.
First step achieved - but trying to include a crosstab after that gave very strange result especially

with dates.

Not sure whether I can do this or not, but I do need to group by every section even if data does not

exist for every month. There is the possibility that more than one injury per date could happen. ie

there could be many accidents in the same section on the same day - they might all be the same class

or different classes.

My crosstab had columns - Classification.Class, Rows - Injuries.date (grouping by Injuries.date, but

using a formula to give group name in format MMM yyyy). If I do not do initial group by Section_1 then

result is close but I only get listing in crosstab if date and class entries exist in the injuries

table. Hence I suppose I need manual crosstab.

There are currently 4 sections in Sections Table: Admin, Electrical. Open pit and Engineering
There are currently 3 classifications in Classifications Table: MTI, RWI LTI
There are several entries in Injuries Table the result was

ADMIN

MTI RWI Total
Mar 2003 2 1 3
Apr 2003 0 2 2

Electrical

LTI RWI Total
Mar 2003 0 1 1
Apr 2003 1 1 2

Open Pit

LTI RWI MTI Total
Mar 2003 0 1 1 2
Apr 2003 1 1 2 4

Engineering needs to list table with 0 in all fields - it listed nothing.

ADMIN and Electrical need to include 0 entries for LTI and MTI respectively.


Hope my explanation is OK.

First can I do a legal grouping using the alias Section_1 table - does it just go in Group header?

Second - how do I get all classes displayed across the top of the table. I tried using a second alias

(Classification_1 without relationships) but that would not work - crystal gave an odbc error.

An example of my preferred table layout for each section is:

SECTION NAME

Month Classes
MTI LTI RWI etc Totals Formula
Jan 2002 0 2 1 3
Feb 2002 1 1 1 3
Mar 2002 0 0 0 0
etc
Dec 2002



but maybe I will have to make Months across top and classes down - but how do I do it?
The totals total all classes for the month. The formula multiplies the total by another figure but I

can worry about that after I get the table started.

This is my first experience with crosstabs so really need help. Can I send tables?

 

[lbass]

The problem is that you want to display data that doesn't necessarily exist. As I said in the other thread, you can get the classification columns if you have at least one date per month, but I don't think you necessarily have all months represented. You can do this by creating formulas like:

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

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

If there were one date in January 2003, then you would have a group for that month and the above formulas could be summed, and you would have a result for each class.

Alternatively, you could get all months if you had at least one record per classification by grouping on classification instead of date and creating formulas like:

//{@Jan 2004}:
if month({table.date}) = 1 then 1 else 0

//{@Feb 2004}:
if month({table.date}) = 2 then 1 else 0

Then you would insert sums on these at the group (classification) level.

One option would be to create a table with all dates for the report period and use that to group on.

However, you also already have the problem with Sections. I'm guessing that adding the alias table worked because you are bringing many more records into the report, so that there is a better chance that each section is populated with data. But the problem you are seeing with results is because your data is duplicating. If you lay out your fields in the detail section, I think you will see that for each Section_1 record, records from your original table are repeating. This means you would have to use running totals to get the correct values--even if you solved the date problem. Still doable, but more complicated.

Finally, you added in parentheses something about plans for a graph based on monthly data. Graphing running totals presents its own special difficulties.

You might want to consider a suggestion I made in another thread and insert a crosstab in the report footer (why not try this as a test?) and use two rows: section and date (set to print on change of month), and classification as the column, with a count of Injury ID as the summary field. In the report footer, you won't lose as many rows/columns as in the groups, since occurrence only once in the report as a whole will result in a corresponding column/row. For the calculated field, you might be able to add a second crosstab just for that, but you still haven't shared what that calculation is.

-LB

 

[smil3y]

Thanks LB - much food for thought. Seems so easy on paper. I will try your crosstab in the footer and see how that goes.
I think maybe the best option however would be to create a table (as you said) use my code to populate the table with the required information and then use a crosstab.