Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Report Designer (Subforms and Pivot tables)

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Default Report Designer (Subforms and Pivot tables)

    I am using Pentaho Report Designer 2.0.0 Build 32195. I am trying to create a report that shows two sub reports side by side. I have the two sub reports created and formatted, I have followed the instruction documents I can find and I get a blank page. So here are my questions:
    1. In the Sub reports that were created using the wizard I am not able to name the query until I am done with the wizard. once I rename the query the report then comes up blank?

    2. How do I get sub reports to work??

    3. The second report I am working on calls for several Pivot or Cross Tab tables how to I achieve this in Pentaho?

    4. Can I put a "Filter" or "Filters" on a displayed table so I can show different sets of data from a single query?

    Thanks in advance for any help
    red

  2. #2
    pstoellberger Guest

    Default

    Two sub reports side by side? Thats not possible (yet) but will be in the next version.
    Same for Crosstab reports, you can do them, sort of, already but its not fully supported yet but will be in the next version.

    Usually you create your main report, then create a new query (datasource) for the subreport, then work on the subreport and et voila .. everything is fine.

    For a better understanding i wouldn't use the wizard and start building reports from scratch so you understand whats happening in the wizard.

    Once you've published your report you can easily add a parameter input prompt for different data selections (queries with "where" parameters). Have a look at the samples / the wiki .. they'll tell you how to achieve that.

    Good luck and have fun!

    -paul

  3. #3
    Join Date
    Mar 2009
    Posts
    2

    Default

    Thanks for the info. Any Idea when that release will be out?

  4. #4
    pstoellberger Guest

    Default

    not sure, maybe mid-april

  5. #5
    Join Date
    Mar 2009
    Posts
    30

    Default Some hints

    Hi Red,

    I'll try to answer your questions pointwise, but my answers are based on the development release, not the stable version:
    1. I agree with Paul; avoid the wizard. When you rename the query after you create the report, you will have to change the value of the query name attribute of the report as well. It's not changed automatically for you.
    2. subreports are pretty straightforward; sort of a link from your main report to a subreport. Each subreports has its own query, details & charts but parameter values can be passed from the main report to the sub report to make it appear as one integrated report
    3. Pivottables are still a work in progress but can be mimiced using some tricks in SQL. The following query will create a data set that looks like a crosstab but isn't. Try it out, maybe it works for you:

    SELECT ProductLine,
    sum( case OrderYear when 2003 then OrderRevenue else 0 end) as Rev2003,
    sum( case OrderYear when 2004 then OrderRevenue else 0 end) as Rev2004,
    sum( case OrderYear when 2005 then OrderRevenue else 0 end) as Rev2005
    from
    (
    SELECT
    `PRODUCTS`.`PRODUCTLINE` as ProductLine,
    year(`ORDERS`.`ORDERDATE`) as OrderYear,
    sum(`ORDERDETAILS`.`PRICEEACH`*`ORDERDETAILS`.`QUANTITYORDERED`) AS OrderRevenue
    FROM
    `ORDERS` INNER JOIN `ORDERDETAILS` ON `ORDERS`.`ORDERNUMBER` = `ORDERDETAILS`.`ORDERNUMBER`
    INNER JOIN `PRODUCTS` ON `ORDERDETAILS`.`PRODUCTCODE` = `PRODUCTS`.`PRODUCTCODE`
    GROUP BY
    year(`ORDERS`.`ORDERDATE`),
    `PRODUCTS`.`PRODUCTLINE`
    ORDER BY
    year(`ORDERS`.`ORDERDATE`),
    `PRODUCTS`.`PRODUCTLINE` ASC
    ) a
    group by ProductLine

    4. That would be a great feature but would require the notion of separate 'tables' in PRD. In PRD, the report is the table and any filtering is done in the query, so you would need sub reports to achieve this kind of output.

    If you want to take a look at the new development release, just download it from http://ci.pentaho.com/view/Reporting/job/PRD/ and give it a try. If you like 2.0, you'll love this one.

    cheers, Jos

  6. #6
    Join Date
    Mar 2009
    Posts
    30

    Default

    Forgot to mention one thing: have you looked at BIRT yet? That can do crosstabs, tables with filtering, multi source, multi query, etc. The BIRT engine is included in the Pentaho platform so deployment is pretty straightforward.

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.