Grouping to show all available categories

[crogers111]

CRW 10
SQL 2000 DB
OLE DB Connection.

This is a simpler example of what I'm trying to accomplish

I have my report grouped by category and I have a Running Total formula in the group footer to count the records in each category. There are a total of 5 categories in the database but my dataset only uses 3 of the categories. I'm trying to accomplish this within a subreport although I don't think that makes a difference. I need all categories to be returned so I can add some arbitrary, historical data not found in the database.



Current Output in group footer with category heading and running total:

Uno 5
Tres 7
Quattro 2

Desired Output:

Uno 5
Dos 0
Tres 7
Quattro 2
Cinco 0


I thought a LOJ from the category table would accomplish this but no luck. Again, Joe Manager has stated "I need you to add 2 for the DOS category within the report" even though no data uses this category in the database. Is this possible in Crystal ?

 

[lbass]

You can't add the corresponding groups for those categories unless you have another table that uses all categories that you can use as your main table and for grouping purposes, with a left join from that table to the one containing your data.

Other than that, if the two particular missing categories will always be the same, you could insert additional group footer sections _b, and _c in which you place text boxes containing the label "Dos" or "Cinco" with the corresponding manually added data. Then you would conditionally suppress the three sections as follows:

Highlight GF#b->suppress->x+2 and enter:

groupnumber <> 1

Highlight GF#c->suppress->x+2 and enter:

groupnumber <> 3

-LB