variable calculation advice needed

[winfried]

 Hello all,

I'm new to iReport and would like to achieve the following task but have problems, how to do it:

I created a datasource, which fetches a time series from a database table. I receive  a list of values, which I want to see in the report. This is easy, I created the fields in the details band and get the values displayed correctly.

At the end of the table, I want to see the average value, which I created as a variable "average" of the java class "java.lang.Double", the calculation "Average", Reset type "Report", increment type "None", variable expression of the value field name from the database table. To display the average value, I created a text field, with text field expression "$V{average}" and evaluation time "Auto". The value is displayed correctly and there are no problems, yet.

Additionally to the value field from the database table, I want to display in the details band the deviation of the current value to the average value. First, I tried to create an additional variable "deviation" with the following variable expression: "Math.abs($V{average}-$F{VALUE})". This should show me the deviation as an absolute value. But unfortunately, the used "average" value is the current average of the line, not the final average value, which I see in the text field, when the report is generated. So, I created a text field instead, with the same expression as the "deviation" variable ("Math.abs($V{average}-$F{VALUE})") and used the evaluation time "Auto". So, I see the correct deviation and also no problem yet.

But now, I want to color the displayed value according to their deviation (like coloring the value green, when the deviation < 5 and red, when deviation > 5). So, I added a conditional style. But when I use the following condition expression "Math.abs($V{average}-$F{VALUE}) > 5", the "average" variable is evaluated for each row and the final value at the end of the table can't be used. 

Is there a way, to create a variable, which is evaluated at the end of the report, without displaying this as a text field in the report?

[cbarlow3]

You say that you created a text field that correctly calculates the deviation, but without creating a variable for it?  I wonder if you could get instead create a variable for the deviation (using the same formula you currently use for  the text field), set the evaluation time to "Auto" as you did for the text field, and then drag that field into the detail instead of the field you have.  The difference?  Now you have a name for the thing you're trying to compare to 5, so I would think it would be easier to enforce conditional coloring...you're expression that you would evaluate would be something like $V{Deviation}>5 instead of an attempted repeat of the calculation you already had to do to display the thing in the first place.

Sorry I can't test it, but I'd be curious if it works.  Oh, and  I never delete anything that works, so maybe you should add the new text field to the detail band and try to get it to have conditional coloring without deleting the deviation field you already have working correctly.  That way you can compare the results and you can always delete the new field if it doesn't work without having to then go back and recreate what you got working so far.

Good luck!

Carl

[winfried]

 Thanks for the suggestion. I was already thinking about this, but the problem is, that I don't know, where I could set the evaluation time for a variable. There is no such setting for variables available. Or do I miss something here?

[cbarlow3]

I think variables are automatically calculated/evaluated with each row of a result set (which essentially means once for each detail, assuming you're outputting details).  With a built-in calculation like SUM, that just means that the sum is updated (without being reset to 0) for each row of the result set.  I'm not sure whether a variable will work, since it certainly seems like you'd need to know the average before you could know the deviation, but when I read that you had already figured out a way to display the deviation on each detail band, I figured a variable might just work.

 

Carl

[mdahlman]

If I understand everything correctly, you will need to use a subreport. You need to calculate the average in the main report. Then pass this value to the subreport. Use your current report as the subreport. Once you have the average as a parameter coming into the subreport the rest should be easy.

Regards,
Matt

[jmurray]

You're trying to use summary values before the report has completed filling, so mdahlman is correct.

You will create a subreport to calculate the summary values that you need. Place it in the report header so that it evaluated before the main report begins filling.  Yes, you will be running you query twice but there's no other way to make the summary values available before the report is filled.

[winfried]

Thanks a lot for those suggestions. I now tried to use a subreport, but I don't find the way, how I can pass the variable "average" from the main report to the subreport. I tried to add it to the "parameters" in the subreport properties. I added the variable "average" with "Add" to the list (as "$V{average}" (the function "Copy from Master" only added the parameter "$P{SUBREPORT_DIR}"), but how can I access this parameter in the subreport itself? How can I get this variable and use it in my calculations or add it to a text box? My variable list is empty and I don't know, how I can use my average now. Can someone give me some hints about this? I also didn't find a description in the ireport guide, which did help me further in this subject.

[mdahlman]

Here is a report I created recently. It shows a very similar idea. I was hoping to write up an article explaining it in a bit more detail. I'll do that if I can find the time. For now, you may be able to see the syntax that you're looking for by looking at this example.

Regards,
Matt

[winfried]

Hello, thanks a lot for the demo. I will have a look at it. I already found out, that I have to define a parameter in the subreport and not a variable.

[neilelliott]

Hi Matt

I'm trying to do something similar to this but I want to display the result as a pie chart ie show the top N records as separate 'slices' and group together the remainder in it's own slice.

I see how you've done this with a table in that in the details, you are using the print when expression to tell it to stop printing after a certain point. But have you any tips how to do this in a Pie Chart ?

Thanks

Neil

 

 

Post Edited by neilelliott at 03/21/2011 09:55

[cbarlow3]

I don't know if it will work in whichever flavor of SQL you're using, but I just created a query that seems to do the job:

 

select
  share_type, type_count, rn,
  CASE
    WHEN rn<=5 THEN share_type
    ELSE 'Other'
  END AS NewCat
from
(
select
  share_type, type_count,
  ROW_NUMBER() OVER (ORDER BY type_count DESC) AS rn
from
(
select
  sh_type.description as share_type,
  count(sh_type.description) as type_count
from
  core.share as share inner join
  core.sh_type as sh_type on
  share.type_serial=sh_type.serial
where
  share.close_date is null
group by
  sh_type.description
)
)

 

This gives me results that look like the sample below...as you can see, I could do a pie chart based on the count and the "NEWCAT" instead of the "SHARE_TYPE" field.  Granted, my query only outputs totals by type.  I've lost the detail.  It's certainly possible to have the detail information and the summary information appear on every row, but it would take a little more work.

SHARE_TYPE                     TYPE_COUNT RN NEWCAT
 ------------------------------ ---------- -- ----------------------
 Minimum Balance Share               10570  1 Minimum Balance Share
 Checking                             4871  2 Checking
 Christmas Club                        646  3 Christmas Club
 Money Market Fund                     629  4 Money Market Fund
 Golden Harbor Checking                614  5 Golden Harbor Checking
 Advantage Checking                    330  6 Other
 12 Month Contributory Ira             240  7 Other
 6 Month Certificate                   231  8 Other
 Special Savings                       176  9 Other
 12 Month Roth Ira                      88 10 Other

[mdahlman]

Eric,

This feature is built in to pie charts. I wrote about limiting pie charts in this article. It should do precisely what you need.

Regards,
Matt

Post Edited by mdahlman at 03/21/2011 21:26

[neilelliott]

Hi Matt

Thanks for the information. This does the job perfectly along with using a subdataset for the pie chart

Just one query, and that is is it possible to use a parameter or variable to determine the maximum number of slices to show, so that it can be changed at runtime ?

My requirement is to run a report to show the Top X of something, and then to show a pie chart to show the Top x relative to the rest. ? I've looked at the jrxml and see that the maximum slices is specified as the 'maxCount' attribute... I've tried specifying this using $P{..} etc.. but it doesn't seem to work.

Also, looking at the Charts Pro Pie Chart, is there a similar option to limit the number of slices - there doesn't seem to be ?

Regards

Neil

 

Post Edited by neilelliott at 03/22/2011 10:39

Post Edited by neilelliott at 03/22/2011 10:39

[mdahlman]

Neil,

Setting the maxCount with a parameter (or variable) seems reasonable, but I haven't tried it. If your tests show that it doesn't work, then please log it in the tracker.

I haven't tried this with the flash-based ProCharts. I suppose that they don't have the same option. This also should get logged in the tracker. It would be a good enhancement.

Regards,
Matt

[neilelliott]

Hi Matt

As we have a license of Pro I've raised both enhancements with Jasper support, assuming that these will make it to the tracker.

Thanks again.

Neil