View Full Version : Need Formula in Total Line

05-31-2006, 01:50 PM
I am very new to Pentaho, checking it out. I am hoping that I can move my organization from a proprietary dashboard environment to an open-source BI like Pentaho.

I tried the Report Design Wizard, 1.1m5, and could not figure out how to specify a formula in the totals line. I have a requirement that needs more than just a sum or average from 1 column. I'm not sure if I'm missing something feature-wise.

I tried BIRT (birt-report-designer-all-in-one-2.1RC3a), and it has a very nice interface and seems to do nice reports with a lot of functionality. BIRT was able to give me the numbers and graphics that I need, but when I tried to publish I get 0007 and 0012 errors which I assume are compatibility issues, since I found a post that indicated that the Pentaho releases are not synchronized with BIRT.

What is the best way for deployment of sophisticated reports, with graphics and formulas? Am I missing a feature in the Report Design Wizard? Do I need a back-level version of BIRT? I haven't tried Jasper yet.

Any help would be appreciated.


05-31-2006, 02:13 PM

You can absolutely do this with JFreeReport, no problem. You are not missing anything feature wise. The Report Design Wizard does not expose any calculations beyond the basics.

What type of calculation are you trying to do? Perhaps I can add it to our set.

With JFreeReport you can create any kind of function you want, either as a BSH (bean shell script) or as an extension (Java) to JFreeReport. With the BSH approach you can embed the calculation into the report at the cost of performance. A new JFreeReport function (Java class) creates a dependency on your report to that external function but the performance is much better.

You can actually upgrade BIRT in the pentaho demo manually, others in the forums have done this with success.

Looking forward to hearing back.

05-31-2006, 02:31 PM
Thanks for the quick reply!!

I have a column of numbers say "Rejected Dollars", and a second column of numbers, "Accepted Dollars." I need to show percentages on each line and then at the bottom a total for each with the proper amount. In the SQL I created a column for the percentage, which is (RD / (RD + AD) * 100) on each row.

If the sum of the values of RD on each row is SRD, and the sum of the AD values on each row is SAD, then the proper number to show at the bottom of the percentage column is the formula that calculates the percentage from the sums (SRD / (SRD + SAD) * 100). A simple sum of the percentages does not work, and an average is not correct either. A representation of the total line values as cells would let you specify a formula overtly for those cells. I guessed that you can do this by editing XML but did not see it in the Report Design Wizard.

Is there a link to a tech tip on how to upgrade the BIRT in the Pentaho-Demo?

THanks for your help.

05-31-2006, 05:57 PM
Hi Mark,

The Report Design Wizard is intended to be a quick-start or a jump-start on designing a report. Please look for an announcement coming soon that will address your concerns with the JFreeReport engine.

06-01-2006, 12:32 PM

As I said yesterday, for custom functions like this you'll need to write them yourself. To answer your other new question from this forum, with the Report Designer I do not believe there is yet a built-in way to create such custom functions.

If you use the Report Design Wizard to create your report, find the ItemSumFunction for your Percentage column and replace it with a fairly simple BSH (BeanShell) script that takes the sum of the Accepted + Rejected and performs the calculation.

<expression class="org.jfree.report.modules.misc.beanshell.BSHExpression" name="Summary_PercentageExpression">
<property name="expression">
Object getValue()
Number acceptedDollars = (Number) dataRow.get("Summary_AcceptedExpression"«»);
Number rejectedDollars = (Number) dataRow.get("Summary_RejectedExpression"«»);
if ( (acceptedDollars == null) || (rejectedDollars == null) || (acceptedDollars .doubleValue() == 0.0) )
return new Double(0.0);

double result = (rejectedDollars.doubleValue() / (rejectedDollars.doubleValue() + acceptedDollars.doubleValue())) * 100;

return ( new Double( result ) );

This should be almost exactly what you need, you'll just have to change your column names to match those in your query, make sure you keep the function name the same as well.

I hope this helps. Might be a good starting point so you can create other functions. It's not very hard to write Java functions either.


Post edited by: mdamour, at: 06/01/2006 16:35

Post edited by: mdamour, at: 06/01/2006 16:39

06-01-2006, 01:14 PM
Thanks, I'll give it a try. I wasn't worried about creating code, but I was worried that if I changed the XML that it would be overwritten if I made any other changes to the report in the Wizard.

The Report Designer looks really nice, and it seems as though little java functions could be easy to write and put the function class file into the jar file, but it would help a lot to see some examples. Is there a place where the source code is posted?

06-01-2006, 01:39 PM
Many functions are built-in to JFreeReport itself and the source code is available from the JFreeReport sourceforge project download. I believe the source is included in the .zip release. Look in the org.jfree.report.functions package (folder) under the source and you'll see them.