Date Interval Summary when no entries for period

[maurella]

I'm trying to generate a report which shows a summary of events by a time interval. Basically, I selected a group, set it to print for each week, and have it print a count of events in the group footer. So, I get something like this:

Week, total
1,3
2,9
3,13

However, if there are no events for the week interval, I get something like this:

1,3
2,7
4,2 (note week 3 is not printed)
5,6
...

Is there something I could do to make sure the interval gets printed event if there are not events, like this:

1,3
2,7
3,0
4,2
5,6
....

?

thanks.

Mike

 

[synapsevampire]

There are a few ways you might do this, depending upon your Crystal version and the database used.

Not posting basic technical information hinders solutions, please take the time to post the basics.

The best solution is to provide data for all of the weeks required, and the big kid solution is to use a Period table left outer joined to your data.

You might use a formula to check for missing weeks, and insert the missing weeks, as in:

Right click your group footer (assuming this is where you're displaying), and select insert section below.

RIght click the new section and select format section and in the X2 next to suppress place:

week({table.groupfielddate}) = next({table.groupfielddate})-1

Then create a formula to place in the new group footer section of:

whileprintingrecords;
stringvar Output:="";
numbervar counter;
if week({table.groupfielddate}) <> next({table.groupfielddate})-1 then
for counter := week({table.groupfielddate})+1 to next({table.groupfielddate})-1 do(
Output:=Output + totext(counter,0) + ",0" + chr(13)
);
left(Output,loen(output)-1)

Make sure that you right click this formula and select format object->Can Grow.

Or within Crystal you might select all periods in a main report data selection, and then have your data within a subreport and return the values in shared variables, but the above is simpler.

-k