iReport 3.7 Bar Chart from Group Count

[dreis]

I am using iReport 3.7.4.  There must be a way to do this, I'm just not seeing how.

What I want to do is similar to the following (modified to protect the innocent):

create a bar chart of how many widgets people picked in their orders (not which widgets, just how many they ordered).

I query the database for line items (one widget per line), ordered by customer

I create a Report Group by Customer, thereby getting the automatic variable Customer_COUNT (how many widgets each customer ordered).

I create a new variable Widget_COUNT with Calculation=Count, Reset type=Report, Increment type=Group (Customer), Variable Expression = $V{Customer_COUNT}

I create a bar chart with Series expression=$V{Customer_COUNT}, Category expression=$V{Customer_COUNT}, Value expression=$V{Widget_COUNT}

It's close, but sometimes I seem to get how many customers had at least that many widgets, not exactly that many which is what I want ("sometimes" depends on the order of the counts in the database).  I tried all different combinations of calculations, reset types and increment types in the Widget_COUNT variable.  Is there any easy way to do what I'm trying to do?

Thanks for the help.

[dreis]

FYI if anyone is interested...this is how I solved it...

I had to change my query to return the count of widgets [complex query returning count(widget_rows) as NumPicked] instead of the widgets themselves, and "order by" this count in the query so that I could create a Report Group based on the count.  Then it was easy to chart the widget count (NumPicked) against how many people had that count (automatic variable NumPicked_COUNT).

...I'm sure there are other ways to do it...

[bsiddiqui]

Hi,

Your second technique is totally different from the first. It is interesting to see why the first technique didn’t work and the second solved the problem.

What you’re trying to do is to know how many customers bought how many products. Your database contains just line items, meaning one line for one product with a customer name.

The first technique is irrelevant with the problem you are trying to solve, due to the way JasperReports processes database records. JasperReports processes data record-by-record and each record is processed only once. So you have just one chance of extracting and saving whatever information you want to fetch from a record, meaning one data-processing-go.

In the first technique, you ordered-by customers in the query and used JasperReports grouping feature for each customer. So you got the count of products for each customer. This technique may have been useful if you wanted to find how many customers bought a specific-number-of-products. If this were the problem, you could have used JasperReports expressions to check if the number of products bought by a customer matches with your specific-number-of-products. And then you could have maintained a JasperReports variable to count the customers who bought your specific-number-of-products.

But this is not your problem here. You want to find how many customers bought how many products, meaning you want to find all the customer_number and product_number pairs logically present in your line items database. You need one JasperReports’ data-processing-go to find one pair. And one data-processing-go is all that JasperReports offers you. So it is impossible to write a single Jasper report that can find all the pairs for you.

You might have used a subreport in your master report, where the master report asks the subreport to perform one data-processing-go to find the number of customers buying a specific-number-of-products. And the subreport is called every time the master report finds another specific-number-of-products in your line item data.

You second technique worked because you used a complex query to find all the product_numbers present in your line item data. Then it is a trivial task to group the product_numbers and find the number of customers who bought each of the product_numbers.

bsiddiqui

Author, JasperReports 3.6 Development Cookbook

 

Post Edited by bsiddiqui at 09/12/2010 18:48