Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Dynamic reports hints

  1. #1
    Join Date
    Oct 2007
    Posts
    235

    Default Dynamic reports hints

    Right. Having got a dynamic report working I thought I would share a few hints with every one, as I took a lot of notes from here its about time I gave some thing back.

    Right by dynamic reports I mean reports that do not have a fixed number of columns, things like cross tab reports.

    If your tying to do a cross tab report read this: http://wiki.pentaho.com/display/COM/...port%2C+Part+1
    and the second part here:
    http://wiki.pentaho.com/display/COM/...port%2C+Part+1

    Following these should get you a reasonable report. Be aware the guides use a simplified version of the report.xml format so what you get out of the report designer will look different, the tags are there just with a lot more options.

    However it lacked a few things for my needs. Totals being the big one for me.

    One thing before we start. If at any point you get errors about invalid XML from the report action, stick a print parameters action in to print out your final report xml into the log. This will help you find the problem.

    Setting up the item sum functions to total up each column is reasonably easy.

    First ignore the advice in the two guides to remove the sum functions when running the report wizard.

    You need two extra templates like the ones for the item band and the header band (See step 10 in part 2 of the linked guides). One will create the group footer entries. The other will create the item Sum Functions themselves. The item Sum XML file should look some thing like the following:
    Code:
    <expression name="Summary_{colName}Expression" class="org.jfree.report.function.ItemSumFunction">
          <properties>
            <property name="field">{colName}</property>
          </properties>
        </expression>
    The group footer entry should look very similar to your item band entry the only difference is you want these to point at the functions you have just templated so you need the following line:
    Code:
    <basic-object name="field">Summary_{colName}Expression</basic-object>
    Two things to watch out for.
    1) you probably want to leave the "grand total:" label in the group footer as it saves creating it later in the JavaScript.
    2) Don't delete all the functions when you take out any totals already in the report template. Row banding and item hiding functions should be left.

    Now we need the .xaction to load these two new files. So create resource entries in the process inputs. Then Create the message Template objects. Then finally we can update the JavaScript entry to use these two fields. Add the resources as inputs to the action, then add some thing like the following in the loop which populates the topband and item band varables:
    Code:
    if ( i >= 2 ) {    // only want the numbers to have totals
           footerBand += replaceParams( footerTemplate, myXpos, colName );
            functions += replaceParams( functionsTemplate, myXpos, colName );
    
        }
    I use the if to ignore the first two columns as they have text info so summing those makes no sense. Make sure that your varable names here are the same as the place holders you put in the template.xml file when you took the group footer and functions out.

    Finally add the footerband and functions variables to the output of the JavaScript and also to the input list of the last message template action which sticks together the report.

    Now in theory your report should have totals.

    Ok cool. What about a total column at the end of your cross tab I hear you cry?

    Ok for this we need a columnsumexpression. Due to our variable number of columns we will need to build this expression in the JavaScript. Now now don't hide behind the sofa, its not that bad.

    Back in our JavaScript function we need change quite a bit.
    1) Add a +1 to the column count function as we are adding an extra column
    Code:
    lineWidth = xPos * (colCount+1);
    2) Start off our columnsumexpression in a new string:
    Code:
    RowSumFunction = "<function class=\"org.jfree.report.function.ColumnSumExpression\" name=\"TotalSum\">\n<properties>\n";
    (I put the \n in to make it easier to read if you print out the XML)
    3)In the loop we need to add an entry for each row we are adding up, so our if statment from eariler becomes:
    Code:
    if ( i >= 2 ) {    // only want the numbers to have totals
           footerBand += replaceParams( footerTemplate, myXpos, colName );
            functions += replaceParams( functionsTemplate, myXpos, colName );
    
        RowSumFunction += "<property name=\"field[" + (i-2) + "]\">" + colName + "</property>\n";
        }
    4) Finish off our row sum function under the loop and add it on to the main function string:
    Code:
    RowSumFunction += "</properties>\n</function>\n";
    functions += RowSumFunction;
    Ok so thats created the expression to add the row up. Now we just need to display it.
    Under the above add the following to stick the new column on the end:
    Code:
    myXpos += xPos;
    topBand += replaceParams( topTemplate, myXpos, "Total");
    itemBand += replaceParams( itemTemplate, myXpos, "TotalSum");
    footerBand += replaceParams( footerTemplate, myXpos, "TotalSum");
    functions += replaceParams( functionsTemplate, myXpos, "TotalSum");
    That should be it for the total column, you should even end up with a total of totals at the bottom of this column.

    So Enjoy. I hope this helps some one out. Any questions I'll keep an eye on this thread so post them below. Any improvements I will welcome. This is my first time writing a guide like this so there are bound to be things that could be worded better. If people are interested I'll put together some screen shots.

    Wil

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

    Default

    Thanks for writing the guide. I've made the thread sticky so that I wont get lost in the other postings over time.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Oct 2007
    Posts
    235

    Default

    No problem. Thanks for moving this to the right section.

    Another small hint to make things look nicer.

    My report template had row bands and background colours on the header and footer rows. But these were only getting set for the first screens width.
    This is really just a case of adding another parameter to the report template and populating it.

    So in the JavaScript section we need the width of the report. (This kind of already exists however its got quotes around it and we need it with out)

    Add this to the JavaScript, some where after it has worked out lineWidth, not in side the loop as that will just slow things down

    Code:
    lineWidthStr = "" + lineWidth;
    Add lineWidthStr to the output of the JavaScript action.

    Now open up your report template xml. look for your coloured boxes. They will have the following tag at the top:
    Code:
    <element type="shape/generic">
    Above these there will be a <band> tag with a bunch of style info that looks like this:
    Code:
    <band>
                <style>
                  <basic-key name="border-top-width">0.0</basic-key>
                  <basic-key name="border-top-style">none</basic-key>
                  <basic-key name="border-top-color">black</basic-key>
                  <basic-key name="border-left-width">0.0</basic-key>
                  <basic-key name="border-left-style">none</basic-key>
                  <basic-key name="border-left-color">black</basic-key>
                  <basic-key name="border-bottom-width">0.0</basic-key>
                  <basic-key name="border-bottom-style">none</basic-key>
                  <basic-key name="border-bottom-color">black</basic-key>
                  <basic-key name="border-right-width">0.0</basic-key>
                  <basic-key name="border-right-style">none</basic-key>
                  <basic-key name="border-right-color">black</basic-key>
                  <basic-key name="border-break-width">0.0</basic-key>
                  <basic-key name="border-break-style">none</basic-key>
                  <basic-key name="border-break-color">black</basic-key>
                  <basic-key name="border-top-right-radius-width">0.0</basic-key>
                  <basic-key name="border-top-left-radius-width">0.0</basic-key>
                  <basic-key name="border-bottom-right-radius-width">0.0</basic-key>
                  <basic-key name="border-bottom-left-radius-width">0.0</basic-key>
                  <basic-key name="border-top-right-radius-height">0.0</basic-key>
                  <basic-key name="border-top-left-radius-height">0.0</basic-key>
                  <basic-key name="border-bottom-right-radius-height">0.0</basic-key>
                  <basic-key name="border-bottom-left-radius-height">0.0</basic-key>
                  <basic-key name="padding-left">0.0</basic-key>
                  <basic-key name="padding-top">0.0</basic-key>
                  <basic-key name="padding-bottom">0.0</basic-key>
                  <basic-key name="padding-right">0.0</basic-key>
                  <basic-key name="x">0.0</basic-key>
                  <basic-key name="y">0.0</basic-key>
                  <basic-key name="min-width">{lineWidthStr}</basic-key>
                  <basic-key name="min-height">18.0</basic-key>
                  <basic-key name="max-width">1000000.0</basic-key>
                  <basic-key name="max-height">2.14748365E9</basic-key>
                </style>
    The line you need to change is the one that has the name="min-width" as you can see I have already inserted the place holder for our width.

    Last of all add lineWidthStr to the input list of the message template action and it should populate these fileds for you.

    There we go nice pretty row bands all the way accross the report.

    Wil

  4. #4

    Default How to add a sub report.

    Hi ,
    Thanks for this wonderful article. I am using the same for one of the reports.

    This is use full to me because there are about 15 to 20 columns number of columns . And I cannot use report designer.

    But i am not able to add a sub report.

    Can you help me to add sub report with a different query.

    I am using this xml file.

    What code must i add and were to add a subreport.

    Thanks
    Sushant
    <?xml version="1.0" encoding="UTF-8"?>

    <report bottommargin="10" topmargin="10" rightmargin="10" leftmargin="10" height="700" width={reportWidth} name="CrossTabReport">
    <parser-config>
    <property name="Report_Title">{reportTitle}</property>
    <property name="pageheader_left">Authored by {author}-Run Date: $(report.date)</property>
    <property name="pageheader_center">$(PageofPages)</property>
    <property name="pageheader_right">Run Date: $(report.date)</property>
    <property name="pagefooter_left">Authored by {author}-Run Date: $(report.date)</property>
    <property name="pagefooter_center">$(PageofPages)</property>
    <property name="pagefooter_right">Run Date: $(report.date)</property>
    <property name="watermark">Confidential</property>
    </parser-config>
    <watermark>
    <message-field height="100%" alignment="left" fontsize="120" fontstyle="bold" fontname="Arial" width="100%" x="0" y="0" vertical-alignment="middle" nullstring="&quot; &quot;" color="#f0EFEE">${watermark}</message-field>
    </watermark>
    <reportheader height="16">
    <message-field height="15" alignment="left" fontsize="12" fontname="Arial" width="100%" x="0" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${Report_Title}</message-field>
    <line color="#5C7485" weight="1" width="100%" x1="0%" x2="100%" y1="16" y2="16"/>
    </reportheader>
    <reportfooter></reportfooter>
    <pageheader fontsize="8" height="16">
    <band name="phb2" x="0" y="0" height="9">
    <message-field alignment="left" height="9" width="33%" x="0%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pageheader_left}</message-field>
    <message-field alignment="left" height="9" width="20%" x="33%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pageheader_center}</message-field>
    <message-field alignment="left" height="9" width="20%" x="66%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pageheader_right}</message-field>
    </band>
    <line color="black" weight="2" width="100%" x1="0%" x2="100%" y1="16" y2="16"/>
    </pageheader>
    <pagefooter fontsize="8" height="9">
    <line color="black" weight="1" width="100%" x1="0%" x2="100%" y1="0" y2="0"/>
    <band name="pfb2" x="0" y="1" height="9">
    <message-field alignment="left" height="9" width="33%" x="0%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pagefooter_left}</message-field>
    <message-field alignment="left" height="9" width="20%" x="33%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pagefooter_center}</message-field>
    <message-field alignment="left" height="9" width="20%" x="66%" y="0" vertical-alignment="middle" nullstring="&quot; &quot;">${pagefooter_right}</message-field>
    </band>
    </pagefooter>
    <functions>
    <property-ref name="report.date"/>
    <function class="org.jfree.report.function.PageOfPagesFunction" name="PageofPages"/>
    <expression class="org.jfree.report.function.HideElementIfDataAvailableExpression" name="DummyGroupNoDataBandElementHideIfNullTrigger">
    <properties>
    <property name="element">DummyGroupNoDataBandElement</property>
    </properties>
    </expression>
    <expression class="org.jfree.report.function.ShowElementIfDataAvailableExpression" name="DummyGroupDataBandElementHideIfNullTrigger">
    <properties>
    <property name="element">DummyGroupDataBandElement</property>
    </properties>
    </expression>
    <expression class="org.jfree.report.function.ShowElementIfDataAvailableExpression" name="dummyGroupFooterHideIfNullTrigger">
    <properties>
    <property name="element">dummyGroupFooter</property>
    </properties>
    </expression>
    </functions>
    <items color="#000000" fontname="Arial" fontsize="9" fontstyle="plain">
    {itemBand}


    <!--
    <string-field name="REGIONElement" nullstring="-" fieldname="REGION" vertical-alignment="middle" alignment="left" width="16.666666666666668%" x="0.0%" y="0" height="11"/>
    <number-field name="CEOElement" nullstring="-" fieldname="CEO" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="16.666666666666668%" y="0" height="11"/>
    <number-field name="CFOElement" nullstring="-" fieldname="CFO" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="33.333333333333336%" y="0" height="11"/>
    <number-field name="AdministrationElement" nullstring="-" fieldname="Administration" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="50.0%" y="0" height="11"/>
    <number-field name="Sexual HarassmentElement" nullstring="-" fieldname="Sexual Harassment" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="66.66666666666667%" y="0" height="11"/>
    <number-field name="Total All PositionsElement" nullstring="-" fieldname="Total All Positions" vertical-alignment="middle" alignment="right" width="16.666666666666657%" x="83.33333333333334%" y="0" height="11"/>
    -->
    </items>
    <groups>
    <group name="dummy">
    <groupheader repeat="true" color="#000000" fontname="Arial" fontsize="12" fontstyle="bold" height="20">
    <band name="DummyGroupNoDataBandElement">
    <label height="11" vertical-alignment="middle" alignment="left" width="100%" x="0%" y="0">No Data Available</label>
    </band>
    <band name="DummyGroupDataBandElement">
    <rectangle color="#dff4ff" draw="false" fill="false" height="18" width="100%" x="0%" y="0"/>
    {topBand}
    <!--
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="left" width="16.666666666666668%" x="0.0%" y="0">REGION</label>
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="16.666666666666668%" y="0">CEO</label>
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="33.333333333333336%" y="0">CFO</label>
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="50.0%" y="0">Administration</label>
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="right" width="16.666666666666668%" x="66.66666666666667%" y="0">Sexual Harassment</label>
    <label color="#000000" fontname="Arial" fontsize="10" fontstyle="plain" height="18" vertical-alignment="middle" alignment="right" width="16.666666666666657%" x="83.33333333333334%" y="0">Total All Positions</label>
    -->
    </band>
    </groupheader>
    <groupfooter name="dummyGroupFooter" color="#000000" fontname="Arial" fontsize="12" fontstyle="bold"/>
    </group>
    </groups>
    </report>

  5. #5
    Join Date
    Oct 2007
    Posts
    235

    Default

    Hi Isushant

    Sorry about the delay getting back to you. You should be able to add the sub report as normal in the report designer before you do the template step. As the subreports can not be side by side you will only get one across the entire band.

    Good luck and let us know how you get on.

    Wil

  6. #6

    Default

    hi
    when i try the example of the crosstab, i got such error
    java.sql.SQLException: Unexpected token: IF in statement [select REGION, sum(if]

    i checked the action file, in the javascript there is a expression
    xTabQry += "sum(if( `" + col_name + "` = '" + columns[i] + "', " + col_value + " , 0)) AS `" + columns[i] +"`,";

    can we write the expression like this? and what dose 'if' here suppose to do?

    Chun

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

    Default

    This is part of an MDX query, so your SQL database does not understand it. You need Mondrian or any other OLAP-Server/Engine to execute MDX queries.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  8. #8

    Default

    Quote Originally Posted by Taqua View Post
    This is part of an MDX query, so your SQL database does not understand it. You need Mondrian or any other OLAP-Server/Engine to execute MDX queries.

    hi
    in fact , it's a sql query, in which the parameters are given in Inputs

    xTabQry = "select " + row_name + ", ";

    for (var i=0; i < columns.length; i++) {
    xTabQry += "sum(if( `" + col_name + "` = '" + columns[i] + "', " + col_value + " , 0)) AS `" + columns[i] +"`,";
    }
    xTabQry += " sum(" + col_value + ") as `Total All Positions` " + row_from ;

    I download this example from the link that wselwood gave http://wiki.pentaho.com/display/COM/...port%2C+Part+1

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

    Default

    then your database does not support the IF function. Ya know, there aint no such thing like an SQL standard in this world.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  10. #10

    Default

    Quote Originally Posted by Taqua View Post
    then your database does not support the IF function. Ya know, there aint no such thing like an SQL standard in this world.
    thx Taqua
    in fact, the user use mysql for the sampledata that suppport 'if'

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 - 2017 Pentaho Corporation. All Rights Reserved.