Crosstab or SubReport to include columns with no matching values

[ahenqas4ed]

Have two tables-First (I) is the rubric for an assessment. It can have rubric names A, B and C. Each name can be defined a Point Value 1, 2, or 3.
Name PointValue
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

Second table (II) would be the point value assigned for a specific person for the rubric name.

So records may look like this and I add a column for year because it is important when creating the report.

Name Rubric Point Year
S1 A 2 2011
S1 B 3 2011
S1 C 2 2011
S2 A 3 2011
S2 B 3 2011
S2 C 2 2011
S3 A 2 2010
S3 B 3 2010
S3 C 3 2010

Want the report to look like this Grouped by Year where the values in the rows are the total count of that point value for that rubric name for that year

2011
1 2 3
A 0 1 1
B 0 0 2
C 0 2 0

2010
1 2 3
A 0 1 0
B 0 0 1
C 0 0 1

It seems this should be simple based on Table I setting up an easy crosstab on I.Name and I.PointValue and selecting from table II all those that match and getting the count. My problem comes from the fact that I need the sort by II.Year that only exists in table II and that some point value columns may have no corresponding entries in table II.
Doing a left or right join, I can get the empty I.Name/I.Point Values, but as soon as I group by II.Year the I.Name/I.Point is no longer in the result set and the tables look like this, because there are not Points = 1 in table II.
2011
2 3
A 1 1
B 0 2
C 2 0

2010
2 3
A 1 0
B 0 1
C 0 1

Have tried crosstabs and experimented with subreports, but am not versed enough there to understand what to pass and how. Any suggestions would be very helpful.

 

[Madawc]

Are 'Point Velues' a table? Or can you get them added? If so, you should get the result you want by starting from them and having a left-outer link to the other data.

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5 or Crystal 2008, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[ahenqas4ed]

Madawc, Thank you! I researched the 'Mock Crosstab' and not sure I understand how to do the column headers as the actual report will have different tables that have different numbers of point values. (they all are not 1, 2, and 3, but may be pretty much anything). Anyway, I do think 'mock crosstabs' will be in my future.

The solution I finally came to was creating a view to contain all the real values I do have. I think there are around 40000 records. Then, using a combination of 4 or 5 fields for comparison, found all of the point values, with reference to year and other values, that do not exist. In my example above this would be Point Value 1 for the group of rubrics A, B, and C as they are . So I would create a dummy value of A 1, B 1, and C 1 each for year 2011 and 2010 with the Name field being blank for each record. I used UNION to add these values to a larger view. The report then has a record for the year, rubric and point value which will keep the column in the report and I can have the count (and other totals) be 0. And now I will go onto improving response time.