US and Worldwide: +1 (866) 660-7555
Results 1 to 2 of 2

Thread: SingleValueQueryFunction in Pentaho Report Designer

  1. #1

    Default SingleValueQueryFunction in Pentaho Report Designer

    Hello,

    Can anyone tell me how to use 'SingleValueQueryFunction' in the pentaho report designer tool verion 2.0 stable?

    I have a static report with columns colA, colB(pymt type), colC(amt) with a group G1.

    In G1's footer, i want to a summary information to display the sum of
    'colC' based on the different distinct values of 'colB'. I found that the 'SingleValueQueryFunction' will work for this scenario.

    But didn't know how to implement that!!!!

    Please help me on this.

    Thanks,
    Senthil.

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

    Default

    create a new SQL-datasource with a name "other-summary". Give it the query

    SELECT sum(colC) FROM table WHERE
    (your full where clause so that you only get the values for that particular group)
    AND colB = ${colA}

    Then in the SingleValueQueryFunction, set the query to "other-summary". Result-Column can be left empty, as the resultset will return only one value. If unset, the first column of the resultset is used.


    However, this will fire a SQL query for each row in your report. As you are querying data from the same database, you may get more speed by using a sub-select to include your second sum in the main resultset.

    SELECT (fields of your main query), (SELECT sum(colC) FROM table WHERE
    (your full where clause so that you only get the values for that particular group)
    AND colB = ${colA})
    FROM table
    WHERE ...

    This, of course, assumes your database is able to handle subselects. If not, then stick with the single-value-query-function.
    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
  •