kheaps Posted November 20, 2013 Share Posted November 20, 2013 Is it possible to create a field that calculates based off the sums of two other fields (i.e., after aggregation)?I have retail prices and discounted prices, and want to create a weighted average discount, which is the sum of all discounted prices over the sum of all retail prices. This is a fundamentally different # than taking the discount rate on a row by row basis and then averaging those numbers. I can figure out how to do the latter (unweighted) calculation in domain designer, and how to calculate something simple like dividing the sum of discounted prices by the total for the column in the adhoc view creator, but not to do a division of two fields post aggregation. Example data:ITEM IDRETAILDSCNTEDDSCNT PCT1108.202105.5032020.00410095.05510095.056104.60TOTAL250227.092 weighted (this row discount/retail).233 unweighted (average of column) Link to comment Share on other sites More sharing options...
Solution bobtins Posted November 21, 2013 Solution Share Posted November 21, 2013 Thanks, this is a good example of something we should be able to support in the product. We are implementing an expansion of the calculated fields capabilities in Ad Hoc for next release, and one thing I would like to enhance is the way we handle summary calculations. Currently, we just allow the user to specify a function, but for cases like this, the user should be allowed to have full control over how to calculate the summary.A few releases back, we did make a change so that the sum of a calculated field "a/b" would be "sum(a)/sum(b)". The formula that you want for the summary is actually "1 - sum(discount)/sum(retail)".I put your sample data into a database and tried it out. What I found was that the summary for "discount/retail" comes out as "sum(discount)/sum(retail)", but not summary for "1 - discount/retail" is really "sum(1 - discount/retail)", which is not correct.Thanks again for bringing this up. Often we are trying to strike a balance between ease of use and power in designing the user interface. In cases like this, users have a good idea of what to do, and we should figure out how to get out of the way and let them do it! Link to comment Share on other sites More sharing options...
kheaps Posted November 26, 2013 Author Share Posted November 26, 2013 Interesting... did not realize that it differentiated and did something else for a/b than for 1-(a/b). Using that info, I was able to get around this by creating an intermediary calculated field for retail - discounted (call it dscntdol), then divide dscntdol by retail. I then get the answer I seek, calculated at the last possible moment. Link to comment Share on other sites More sharing options...
christophe.suzzoni Posted May 6, 2014 Share Posted May 6, 2014 Hello, I've got the same issue, i want to create a measure in domain designer which is SUM(fieldA)/SUM(fieldB) in aggregation view.I don't find find the solution. I use Jasperserver pro 4.5. Is it possible or this feature is available only for next release ? Thanks for your help. Link to comment Share on other sites More sharing options...
kheaps Posted May 6, 2014 Author Share Posted May 6, 2014 per @bobtins above, I can verify that simply sum(a/b) is translated to sum(a) / sum(b), at least in version 5.5 - not sure if that is true in 4.5, but you might want to try it. Link to comment Share on other sites More sharing options...
christophe.suzzoni Posted May 6, 2014 Share Posted May 6, 2014 If I understand well, I create a new calculated field which is equals to a/b. In display tab I use this field like a measure with SUM aggregate function ? So, if it's good the query send to MySQL will be SUM(a)/SUM(b) instead of SUM(a/b) ? Is it clear ? I'll check it quickly. Link to comment Share on other sites More sharing options...
kheaps Posted May 6, 2014 Author Share Posted May 6, 2014 That is how I understand it. It might process the sum on the java side, though. If you turn on the button that allows you to see the query being sent, you could confirm. Link to comment Share on other sites More sharing options...
christophe.suzzoni Posted May 6, 2014 Share Posted May 6, 2014 Thanks for your reply. I'll check it. But what is the button ? Where is it ? I don't know it. Link to comment Share on other sites More sharing options...
kheaps Posted May 6, 2014 Author Share Posted May 6, 2014 There is a button in the ad hoc editor that can be enabled (it is disabled by default) (you must be the super user to enable it) that allows you to view the query that is being sent to the database. In the JasperReports Server 5.5 Admin Guide it's described in section 7.8 Configuring Ad Hoc, 7.8.1 Ad Hoc Query Settings. Not sure where it is in the 4.5 guide, but I'd image it is similar. 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