groups need to show 0 if no data

building reports crystal reports
1

I have one grouping level based on specified order. The report is based on a single table. If there is no data for a particular group within a date range, that group simply does not show on the report. Is there any way to show all groups available with a 0 even if they are null within the date range?

shorne100 (BOB member since 2007-03-14)

2

Try format number.

Sravani.

casino :us: (BOB member since 2006-10-23)

3

hi,

steps

  1. Go to GroupHeader filed and right click.
  2. Click on FormatField.
  3. Select ‘Common’ tab.
  4. Select Display String Formula Editor.
  5. Write the following formula and save it.

if(isnull({GroupField}))
then “0”
else
{GroupField}

i hope it will work.

Regards,
manva04.

manva04 :india: (BOB member since 2006-07-31)

4

hi,

steps

  1. Go to GroupHeader filed and right click.
  2. Click on FormatField.
  3. Select ‘Common’ tab.
  4. Select Display String Formula Editor.
  5. Write the following formula and save it.

if(isnull({GroupField}))
then “0”
else
{GroupField}

i hope it will work.

Regards,
manva04.

manva04 :india: (BOB member since 2006-07-31)

5

I tried the if isnull on the group field and it did not force my group names to show up, either as a group name or as a 0. What I am really trying to do is show the group names if they are null or not null. If they are null, the field name should still show up, along with a 0 in a summary field also.

shorne100 (BOB member since 2007-03-14)

6

The groups with no records in the date range will never appear since they are excluded from the report by your date range selection criteria. No amount of Crystal formulas on the report will help since there is no data for them to work on.

What you need to do is have a separate group lookup table containing just the names of all the possible groups and join it to your main table using an outer join such that all the rows in the group lookup table are returned. Then you put an isnull function on the summary field such that if it is null, then return a zero.

You could derive the lookup table like:

select distinct groupname from maintable

Thus to include all groups on your report the query is

select groupname,isnull(sum(value),0) from
maintable right outer join (select distinct groupname from maintable) lookup on
(maintable.groupname = lookup.groupname)
where
maintable.date between 'X' and 'Y'

meumax :australia: (BOB member since 2006-07-28)

7

I was just wondering if the last reply worked for you? I tried the formula last recommended and it didn’t work.

I’m looking to do the same thing you are. I have a report for caseworkers that show there caseload and the appointments the clients have in a specific date range (parameter). The report is grouped by Caseworker and then grouped by Client. Right now it’s only showing clients who have an appt but I would also like to show clients who don’t have an appointment and since there’s no data in the details for them maybe have it read “Not Scheduled”

Thanks

andrewarbogast (BOB member since 2017-10-17)

8

Wow this is an old thread.

Like I said years ago, you gotta have a table containing the names of all clients which you then join to your appointments table using an outer join so that all the client names are returned even if there are no appointments.

Use the command:

 select distinct client_name from client_table

to get a table containing all client names. Then join this table to your appointments table using an outer join.

meumax :australia: (BOB member since 2006-07-28)