passing data to parent from sub, accounting for NULL rows

[Malekish]

Working on a series of reports and I ran into a bit of a problem.

CR XI, pulling from Informix, DB2 and Oracle DBs

I am pulling data from 4 sources, each within it's own subreport, and compiling numbers to show combined scores. A row in my databases is created each time there is activity on a given day. If there was no activity, either because the project was closed or there happened to be no data captured, then a row is not created.

Samples of data
Location A

Row_Date   Abandoned   Answered   Acceptable
4/1/09     5           10          9
4/2/09     2           15         11
4/4/09     3           11          8

Location B

Row_Date   Abandoned   Answered   Acceptable
4/1/09     9           23         18
4/3/09     7           29         21
4/4/09     6           19         17

You'll notice that not every unique date is represented in the tables.

In my main report, I want to display the following items

Row_Date   sum(Abandoned)   Sum(Answered)   Sum(Acceptable)
4/1/09     XXX              YYY             ZZZ
4/2/09     XXX              YYY             ZZZ

Where XXX, YYY and ZZZ are the sum of those numbers for each individual day.

First question: How do I account for a missing row? In my example of Location A, I want to represent 4/3/09 as existing but having 0s for each value. (or at least have my parent report understand that a missing value should be considered all Zeros)

Second question: What is the best way to pass those numbers to the parent report? An array with [date,aband,answered,acceptable] elements? Or just 3 arrays with [date,aband], [date,answered] and [date,acceptable)

The old report is just passing the 3 data fields (aband, answered & acceptable) in an array for each but it's not including the date tied to the data. This is producing false values because the 3rd element in the array isn't necessarily the 3rd day of the month.


Current output from old report has a row that supposedly represents each date but I know it is not correctly tying the numbers to a date. (Element 2 in location A may equal April 2 but Element 1 in location B may equal April 2 because no activity on April 1)

Aban                  Answered            Acceptable
sum element 1         sum element 1       etc, etc.
for each location,    for each location,
Abandoned[] array     Answered[] array

Thanks for your time, any insight is much appreciated!

 

[Madawc]

I think you'll need a mock-crosstab. That's 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.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Malekish]

Hrm, never tried making a Crosstab but I'll look it up and see if that does what I need.

Thanks for the suggestion, will post my progress!