Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: record counts in group by

  1. #1
    Join Date
    Jun 2009
    Posts
    17

    Default record counts in group by

    Hello
    I have a simple report in Report Designer that I am having trouble with counting records in Item Band.

    There are only 3 data elements in the report, the query is below. I am grouping the data element that is being counted (casenum) by the other two elements in the report: Types Meta (group) and Types (sub-group). This grouping is in both the query and in Report Designer.

    However, when I preview the report in Report Designer, I am only getting one value displaying for each sub-grouping and the data element that is being counted is displaying the total count for the entire date range in the overall report and not the sub-total for each grouping/sub-grouping.

    Does anyone know what am I doing wrong?

    Paul




    SELECT
    t.TYPES_META,
    t.DESCRIPTION,
    count(c.CASENUM)

    FROM
    CDRC_AUTOMATION.TYPES t,
    CDRC_AUTOMATION.CASES c

    WHERE
    c.FINALDISPDATE between TO_DATE('04/01/2008','mm/dd/yyyy') and TO_DATE('03/31/2009','mm/dd/yyyy')

    GROUP BY
    t.TYPES_META,
    t.DESCRIPTION

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    When you use the SQL-Group-by and SQL-aggregate functions, the database driver does all the aggregation and will not return the various items. So if you want to list all the items for a group in a report, do not aggregate on the database level. Let the reporting engine do that job.

    (Database level aggregation is a good thing if you just want to print the totals and do *not* want to print the details at all.)
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.