winfried Posted October 22, 2010 Share Posted October 22, 2010 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? Link to comment Share on other sites More sharing options...
cbarlow3 Posted October 22, 2010 Share Posted October 22, 2010 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 Link to comment Share on other sites More sharing options...
winfried Posted October 25, 2010 Author Share Posted October 25, 2010 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? Link to comment Share on other sites More sharing options...
cbarlow3 Posted October 25, 2010 Share Posted October 25, 2010 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 Link to comment Share on other sites More sharing options...
mdahlman Posted October 26, 2010 Share Posted October 26, 2010 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 Link to comment Share on other sites More sharing options...
jmurray Posted October 26, 2010 Share Posted October 26, 2010 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. Link to comment Share on other sites More sharing options...
winfried Posted October 27, 2010 Author Share Posted October 27, 2010 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. Link to comment Share on other sites More sharing options...
mdahlman Posted October 27, 2010 Share Posted October 27, 2010 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 Link to comment Share on other sites More sharing options...
winfried Posted October 28, 2010 Author Share Posted October 28, 2010 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. Link to comment Share on other sites More sharing options...
neilelliott Posted March 21, 2011 Share Posted March 21, 2011 Hi MattI'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 ?ThanksNeil Post Edited by neilelliott at 03/21/2011 09:55 Link to comment Share on other sites More sharing options...
cbarlow3 Posted March 21, 2011 Share Posted March 21, 2011 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 NewCatfrom( select share_type, type_count, ROW_NUMBER() OVER (ORDER BY type_count DESC) AS rnfrom(select sh_type.description as share_type, count(sh_type.description) as type_countfrom core.share as share inner join core.sh_type as sh_type on share.type_serial=sh_type.serialwhere share.close_date is nullgroup 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 Link to comment Share on other sites More sharing options...
mdahlman Posted March 21, 2011 Share Posted March 21, 2011 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,MattPost Edited by mdahlman at 03/21/2011 21:26 Link to comment Share on other sites More sharing options...
neilelliott Posted March 22, 2011 Share Posted March 22, 2011 Hi MattThanks for the information. This does the job perfectly along with using a subdataset for the pie chartJust 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 ?RegardsNeil Post Edited by neilelliott at 03/22/2011 10:39Post Edited by neilelliott at 03/22/2011 10:39 Link to comment Share on other sites More sharing options...
mdahlman Posted March 22, 2011 Share Posted March 22, 2011 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 Link to comment Share on other sites More sharing options...
neilelliott Posted March 23, 2011 Share Posted March 23, 2011 Hi MattAs 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now