Forcing Group even if there are no values

[kutoose]

I am creating a formula for and using that as Group.
The formula is like this

If DateDiff('d',{Train.BOOKING_DATE},{Train.ARRIVAL_DATE})
in 0 to 7 then "0-7 days "
else if
DateDiff('d',{Train.BOOKING_DATE},{Train.ARRIVAL_DATE})
in 8 to 14 then "8 to 14 days"
else if
DateDiff('d',{Train.BOOKING_DATE},{Train.ARRIVAL_DATE})
in 15 to 30 then "15 to 30 days"
else "greater than 31 days"

I want the report to show all the Groups even if
there are no data in one of the group. For example
if there are no values in 15 - 30 range, then
the data displayed is

0-7 days 10
8 to 14 days 15
Greater than 31 days 8

should be displayed as

0-7 days 10
8 to 14 days 15
15 to 30 days 0
Greater than 31 days 8

How can this be achieved ???

I am using Crystal 10, Oracle DB


kutoose@yahoo.com

 

[rosemaryl]

Dear kutoose,

Unfortunately, reporting on what is not there is difficult. I believe you have two options.

Create a new period table that contains dates, date ranges and so on which you could then link in your report.

Or, you could try this... it will require a subreport. I have done this when a client required a group to be present when there wasn't any mathcing data.

A. In the main report create a SQL Expression that creates the groups ... you will have to base them something you know will always exist in your database. In the database I do consulting on clients are rarely if ever deleted so I use the Clients table. I then create a sql expression with a case statement that states when client id = 'Value' then value I want to print and so on ...

To improve report processing; in the record selection criteria of the main report select only the 4 records you are using in your sql expression.

The expression might look like this:

(Case when Clients."Client ID" = 'AARONT18028'
then '00 to 07 Days'
when Clients."Client ID" = 'AA2647'
then '08 to 14 Days'
when Clients."Client ID" = 'ABOELEK32'
then '15 to 30 Days'
when Clients."Client ID" = 'ADAMSJ39'
then 'Greater than 31 days'
end)

Now, insert a subreport for the group. Create a formula in the subreport that returns the values that match the main report's group values such as this case statement:

Select DateDiff('d',{Train.BOOKING_DATE},{Train.ARRIVAL_DATE})

case 0 to 7 : '00 to 07 Days'
case 8 to 14 : '08 to 14 Days'
case 15 to 30 : '15 to 30 Days'
Default : 'Greater than 31 days'


Link the subreport to the main report via the sql expression you are grouping on in main report and the formula in the subreport.

In the subreport you would insert a formula that counts the records. You could use a shared variable so that you could show the totals on the main report and make the subreport very tiny so it is not seen.

I hope this helps,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Madawc]

A third option is to have a running total for each of the possibilities.

Madawc Williams (East Anglia, UK)