Need help with building a manual crosstab 1

[Sielah]

I'm trying to put together a simple report for our Help Desk department. I want to build the following table :

1. One row for each IT support team with a final grand total row for all teams.
2. One column for each month in 2008 with a final grand total column for the whole year.
3. Each cell in the table contains the number of incidents that occurred for that team in that month.

I started with an auto crosstab, but this proved to be unsuitable because it does not deal with scenarios where there is no data to report i.e. months with no incidents.

So I'm working on a manual crosstab instead. It seems to be working well, except for the fact that I've had to write an awful lot of manual formulas to keep track of the incident counts.

Here's how I have built the report so far :

1. One group based on team name.
2. In the group header, initialize the incident count for each individual month - one formula.
3. In the detail record, assess the month and increment the corresponding count - one formula.
4. In the group footer, display the incident count for the month followed by a percentage - twenty six separate formulas.

Now I have to implement the logic that will provide the grand total in the final row of the crosstab. At the moment, I can't see any way to do this without writing yet another set of formulas that track across all groups.

So my question is, am I missing something really obvious that would speed the process of implementing the crosstab?

For example, I tried using a summary field on the group that would count the number of distinct incident dates. This doesn't work because the summary can't divide the dates into the individual months.

This is my first manual crosstab, so a bit of a learning process for me. Any advice as to how to make it easier is much appreciated.

 

[lbass]

Yes, you are making this more complex than need be. First create a formula {@null} by opening and saving a new formula without entering anything. Then create one formula per month like this:

if month({table.date}) = 1 then
{table.incidentID} else
tonumber({@null}) //remove the tonumber if ID is a string

Place these in the detail section and insert distinctcounts on them at the group and grand total levels.

-LB

 

[Sielah]

Okay, I see where you are going with this.

I can get everything working except the final stage of adding the distinct count at the group and grand total levels. Is this something I have to create myself, because Crystal won't give me the option of creating a Summary or Running Total on the formula that's used to create the detail record.

 

[lbass]

Then you must have changed my formula. Did you add whileprintingrecords to it? If so, remove it.

-LB

 

[Sielah]

We figured out that there was actually something wrong with the whole report that was preventing me from creating Summary or Running Total fields.

When I recreated the formula in a brand new report, the option to create a Summary on the formulas popped right up.

So I don't know what was wrong with the original report, but I know how to fix it, and I can now see exactly how to create the crosstab.

Thanks for your help!