Accessing Report sum Variable during column

[2001 JI Open Discussion]

By: Keith Berman - bobbassen

Accessing Report sum Variable during column

2002-07-16 10:03

I am trying to do a calculation using the sum of a column. For each row, I need to calculate the percentage of the total. When a create a variable at Report level which sums the column, I only have the sum at that point, and not the total sum of that column. Is this possible?

 

 

By: Teodor Danciu - teodord

RE: Accessing Report sum Variable during column

2002-07-17 03:00

 

Hi,

 

This is not possible.

At least not in a simple manner.

 

JasperReports makes calculations step by step.

It cannot tell you what the sum will be, hence the

percent, before reaching the last row of data.

When it finds out what the sum is, it cannot go

back updating the percent text fields because it

would have to iterate through all the data again.

 

The only solution would be to calculate the sum

yourself before launching the report, pass it in as

a parameter and let JasperReports deal only will

the percent for each row.

 

I don't know if there are any report generating tools

to support such functionality.

 

Thank you,

Teodor

 

 

 

By: Keith Berman - bobbassen

RE: Accessing Report sum Variable during column

2002-07-17 07:10

Tha'ts what I will do, just wanted to make sure I wasn't overlooking an easier situation.

 

Thanks

 

 

By: Bart Cassady - bartong

RE: Accessing Report sum Variable during colu

2002-07-18 07:27

Actually, most mainstream reporting tools have this functionality, at least as far as I've seen.

 

I think they achieve it by doing multiple passes. I think it could be done in a single "fill" pass if you got creative in the compile. Essentially, the compile can be thought of as the first pass, and the fill is the second. If in the compile you somehow "registered" the need to perform sub-calculations at certain points in the fill to be used later on in the fill, I think you could achieve this.

 

I personally need this functionality from time to time, and your suggested workaround won't handle more complex reports where the percentage (for example) is for a group, not the whole report.

 

Any chance you could add it to your To Do list, Teodor?

 

Bart

 

 

By: Teodor Danciu - teodord

RE: Accessing Report sum Variable during colu

2002-07-18 08:03

 

Hi,

 

I'll give it a serious thought.

It's not simple issue.

 

Re-passing through the data set would have

important performance consequences.

 

Our text fields have now an attribute

called "evaluationTime".

I think we can achieve something using that.

 

Thank you,

Teodor

 

 

 

By: Teodor Danciu - teodord

RE2: Accessing Report sum Variable during col

2002-07-19 04:17

 

Hi,

 

I come back with another suggestion on this issue.

 

I thought about this type of calculation from day

one, when planning to create the library, and didn't

succeed to find a proper solution for it.

 

However, as I previously said, there are other ways

to achieve the same result.

The idea behind it is to help a little the

report-generating engine and to not let everything

on its shoulders.

 

If I would have to create a report with some percent

fields calculated based on group totals, as you

suggested, I would try to help the report engine.

I would try to provide it with the percent value for

each data row, or at least provide it with the group

total for each row (the data set will contain some

redundant data).

 

This is not very hard to do, especially if we are

talking about SQL queries.

If the report data comes from a relational database,

then I could create a more complex SQL query like this:

First, I would try to retrieve with an SQL SELECT

the totals for each group using the SUM function

and the GROUP BY clause. Then, I would perform

a join between this result set and the main query

of the report. This way I would obtain on each row

the total of its group and I would be able to

calculate the percent value on the spot,

in the same SQL query.

 

If the report requires further complexity, then

I could switch to a store procedure where I could

prepare my data before passing it to the report

generating routine, using maybe temporary tables

and so forth.

 

I have another idea about how to handle all this

using scriptlets.

But I think that's enough for the moment.

:)

 

I hope this helps.

Teodor

[xiaoxuep]

Hello,

I need to do the similar thing in my summary report. I noticed this discussion topic is happened many years ago. Just wonder if things changed since then. Is this supported in latest versions?

Thanks

[ernestoo]

I wrote a tutorial on this subject recently, take a look: http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/Creating_A_Percent_Of_Field

 

Hope it helps!

[xiaoxuep]

Thanks for the quick reply. I did try your tutorial, it works when it is in detail fields. But when I added a group (by person name), added a few records for each person. Then the percentages are not right.

I attached my updated percent_of.jrxml. Can you help to take a look at what I have done wrong?