Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

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

edited October 2001 in General
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

Comments

  • edited October 2001
    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


  • edited October 2001
    > 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
  • edited October 2001
    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


  • edited October 2001
    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
This discussion has been closed.