Crosstab object

[WhiteKnight2K]

Hello,

I'll first describe the situation then I'll ask the question so its makes sense what I am asking.

Currently I have a database that stores Species of a tree as well as Location Class. Right now there are 3 Species and 6 Location Classes, however this could change because it is user controlled (i.e. new entries can be created).

In one of the reports I am developing, I need to show a summary of each tree Species within each Location Class for each Municipality (there are about 200 municipalities in the database)

In the summary i would like to show all of the species/ location class combinations even if there are no trees that fit the specific combination. It sort of would look like so:

Summary for Municipality1:

Species
Location Class American Elm Siberian Elm Japan Elm
BLVD
ORN
WILD
RIVERBANK

So i would like the summary to look like that. The Tree table has a field for Species and a field for Location Class among other things.

I thought that a Crosstab would be able to do this for me. I set the Crosstab up with Row->Location Class and Column->Species and a Running Total as the field to sum (the running total was set to count each tree and set to reset on Change of Municipality).

However, when the report is run, the crosstab on shows those combinations for which there is a record. For example, it will show something like this:

Species
Location Class American Elm
BLVD 10
WILD 5

But it will not show the other combinations. I realize the other combination do not have a record that fits but I would still like to show this in the summary.

Any ideas how to set this up?

Thanks!

 

[synapsevampire]

Thankfully Crystal doesn't fabricate data, that could get pretty confusing.

Since you have the potential for extensive combinations, I would suggest that this be table driven, so that a left outer join from some master table(s) list of Species, Location Class and Municipality could produce everything, even though some of it is wrong (doesn't exist).

There are other ways you might cheat this, but we'd need real technical information rather then descripritions of it:

Crystal version
Database/connectivity used
Example data (tables/columns, sample data)

-k

 

[WhiteKnight2K]

Thanks for the reply. I dont mean i want CR to show erroneous data, I just want it to show 0 for the combinations that do not have a result. I would like it to show something like this:

Species
Location Class American Elm Siberian Elm Japan Elm
BLVD 1 0 0
ORN 5 0 0
WILD 0 0 0
RIVERBANK 0 0 0

instead of showing this for example:

Species
Location Class American Elm
BLVD 10
WILD 5

Do you see how the tables report different data. I want to be able to show all the combinations regardless of whether the result is zero or not. THe crosstab just shows the result of non-zero entries.

Btw, I'm using CR 9.0, Microsoft Access database

 

[acessn]

Have you tried selecting: File->Report Options->"Convert Null Values to default"? That did the trick for me...

Regards Bjørn

 

[lbass]

I think your best best is a manual crosstab, where you group on municipality and then on location class. Then create a formula for each tree species:

//{@American Elm}:
if {tree.species} = "American Elm" then 1

//{@Siberian Elm}:
if {tree.species} = "Siberian Elm" then 1

//{@Japan Elm}:
if {tree.species} = "Japan Elm" then 1

Place these formulas in the detail section and then right click on each and insert a summary (SUM, not count) at the group level. Then suppress the detail section.

The only maintenance involved is that you would have to add a new formula for each new species added.

This will not, however, provide any results for municipalities/location classes that have no trees, i.e., no groups would appear for these. To include those, you would have to have a table that included all municipalities/location classes and use a left join from that table to the trees table.

If you don't have a table like that, you could use a different kind of manual crosstab where you place running totals in the report footer that are specific to a municipality, location class, and species. You would use the formula option in the evaluation section and add a formula like:

{table.municipality} = "Concord" and
{table.locationclass} = "Blvd" and
{table.species} = "American Elm"

You set the reset to "never". Then you would label rows and columns with text boxes. This would be onerous, with the number of rows you will have, and probably not feasible.

-LB