DBCalc Count(*) shows 1 instead of 0 when no detail in group.

rbuser

Hi,

I created a small, simple report that dumps detail records in groups. I set
up a group in the designer (not in the SQL query) and it does indeed print
detail lines in groups based on "groupname" (my grouping field). Then in
the group footer I placed a DBCalc with its calc type set to Count and its
group set to the 1 group I have on the report (Group[0]). It all seems to
work fine for most groups.

The problem is that whenever a group has no detail records (just a header
and no detail), it prints a total of 1 in the footer. I also have a grand
total DBCalc that also counts these 0 groups as 1 in the grand total field.

Essentially, the report joins a GroupHdr (master) table with a Groups
(detail) table, and a Defect (subdetail) table, and prints defects within
groups. The reason I have to use the GroupHdr table is that I want to see
ALL groups, not just those with defects in them. The GroupHdr table has a
record for each group regardless of whether it has any defects. The Groups
table is only a cross reference of defects and the groups they belong to.

If it helps, the query is as follows:

SELECT GroupHdr.GroupName, Defect.Defect,
Defect.Summary, Defect.Status
FROM "GroupHdr.db" GroupHdr
LEFT OUTER JOIN "Groups.db" Groups ON
(Groups.Product = GroupHdr.Product)
AND (Groups.Release = GroupHdr.Release)
AND (Groups.GroupName = GroupHdr.GroupName)
LEFT OUTER JOIN "Defect.db" Defect ON
(Defect.Product = Groups.Product)
AND (Defect.Release = Groups.Release)
AND (Defect.Program = Groups.Program)
AND (Defect.Defect = Groups.Defect)

Thanks,
-- Vinnie Murdico

digitalmetaphors

The detail band does print, even when there is no detail data for it. The
reason is that there is still master data (1 record), because of the left
outer join. This is the correct behavior. To get the behavior that you
desire, you could use master detail linked queries and set the
SkipWhenNoRecords property to false on the detail datapipeline. This will
cause the master record to print, even when the detail dataset will be
empty. Use subreports connected to the detail data. This will allow you to
show the group header and footer for the master record, but no record for
the detail will print. See the main reports demo in the RBuilder
installation for examples on master detail report layouts.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

> To get the behavior that you

Thanks Jim,

How can I set the SkipWhenNoRecords property on the detail pipeline? I
couldn't find the property setting anywhere in the designer or on the popup
menus.

I can't set it from code because the reports are all custom (created by the
end user), and saved as RTMs and loaded dynamically at runtime for printing
by the end user. There are no reports with code saved in the application,
so I was hoping this property could be set from within the designer. I
checked the Developer's guide by searching on SkipWhenNoRecords, but the
text did not appear to exist in that PDF file.

Thanks,
Vinnie Murdico

digitalmetaphors

It needs to be set on the datapipeline component. If the data is coming
from DADE dataviews, then click on the link between the dataviews and in the
link dialog, there is a radio button selection which describes the
SkipWhenNoRecords behavior.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Hi Jim,

Please ignore my last post. I found one of my problems. But here's what's
still remaining:

1) The report prints the group header for each group, even if it has no
detail. But it does not actually print the subreport which contains
subtotal count for that group's detail records. That is, I have a subtotal
count of records in that group set up as a DBCalc (count(*)) field in the
subreport itself as a group footer.. But that subtotal does not print if no
detail exist in the subreport. I need it to print 0.

2) The "grand total" DBCalc Count(*) field in the Summary section of my main
report prints, but counts the number of header records (group headers),
which makes sense given that the main report's pipeline is the headers
table. Is there a way to get a grand total of all detail records printed
for the entire report, and print that at the end of the report? (again,
this would be an end-user report with no coding involved -- also I only have
RB Pro, so no internal variables or calcs can be defined by the end user).
I would imagine this is a pretty common scenario (master/detail with
subtotal and grand total counts) -- I'm sure I'm just missing one ore two
small things here!

Thanks,
Vinnie Murdico