Cross Tab Challange 4

There are a couple examples of reporting on data that isn't there on Ken Hamady's newsletter from months 03 and 05 of this year that may be helpful:
I like to force data into the query when I am asked to do this sort of thing. I usually end up with a command object so I can control the SQL. I can't see what your query looks like so you'll most likely have to adjust what I put here to fit your query. Try adding something like this:

union all
select 'Chicago', 'Joe', 0 from dual

I hope you can see all it is doing is adding in missing data to create the Chicago group.

I'm going to paste in a longer explanation that I wrote up for someone else that may be helpful. Note that I would normally use null values where I use the 0 above. Forcing in data is great for making charts look consistent across an entire report. Groups, charts and cross tabs are similar in this fashion. There has to be data to get crystal to report it so we feed it what it needs.

------------------

One method I have used a few times when my users have wanted padded groups is to force the data into the report using SQL. Take for example a report that would be grouped by department and within that department it would shows the employee counts by ethnic group. The user wants to see all of the ethnic groups for each department and not just the ethnic groups represented in each department.

Your original query might look something like this:
select emp_id, ethnic_group, dept_id
from employee

What we need to do is pump all of the ethnic groups into the report for each department. To do this I do a Cartesian product join between the ethnic groups and the departments like this.

select null, ethnic_group, dept_id -- Null emplid so we ignore this record
from (select distinct ethnic_group from employee), -- The ethnic groups needed by the user
(select distinct dept_id from employee) -- Every department that we are grouping on

We leave off any joins so it pumps the report full of all of the missing data with a null value in the employee field. This is the field we are generally doing our counts on and the null values will be ignored or can be suppressed but this will at least force all of the data onto the report to make our users happy. This query will generate the number of ethnic groups times the number of departments records. We don’t need them all but they will not hurt anything because we will be ignoring these records.

The full query looks like this:

select emp_id, ethnic_group, dept_id
from employee
union all
select null, ethnic_group, dept_id
from (select distinct ethnic_group from employee),
(select distinct dept_id from employee)

You could get as creative as you want with the query below the union all to force in only what you want to force in.

Very nice (*)!

-LB

I concur with LBass.

Very nice use of the cross join and the union. Elegant Solution.

Gordon BOCP
Crystalize

I appreciate your kind responses. A "Very nice" from lbass means a lot to me. I'm happy the original poster was pleased as well.

And a nice explanation.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

With Crystal Reports 2008 you can create a "Calculated Member" to insert extra rows and columns in a crosstab. I think they were intended to summarise specific rows or columns but can't see why they couldn't be used to insert a zero value row in there as well.

We covered these in previous issues of our newsletter.


Editor and Publisher of Crystal Clear

Tx Chelseatech,

Yes I was thinking about upgrading the client to CR 2008 since its only a few hundred dollars.

Gordon BOCP
Crystalize