Aggregation and summation

tgiaccone

Here's my problem. I will preface this by saying I'm new to writing BIRT reports.

I'm creating a summary report for purchase orders for a number of customers. Each line item on the purchase order has a product category (A, B, C).

The report is run for the fiscal year for all customers. Each customer has multiple purchase orders that we have fulfilled. Each PO has multiple line items.

I want to calculate summaries for each customer. The summary consists of, the total amount purchased by that customer, and the totals for each product category.

In addition at the end of the report I want to summaries for the company as a whole the totals for each product category.

A simplified example is given below.

Cust-101
PO #1
1 A $100.00
2 B $125.00
PO #2
1 A $100.00
2 C $500.00

Summary Cust 101
Total A $200.00
Total B $125.00
Total C $500.00
Total Purchases: $825.00


Cust-102

PO #1
1 A $70.00
2 D $59.00
PO #2
1 A $120.00
2 E $60.00

Summary Cust 101
Total A $190.00
Total D $59.00
Total E $60.00
Total Purchases: $309.00


I've figured out how to total for the report as a whole and for each customer, but it's not clear to me how to calculate the totals for each product category.

Any help would be appreciated, a pointer to examples, or documentation would be very much appreciated. I've got the BIRT book, "A Field Guide.." which is how I got started on this.. but I'm a bit lost now.


Tony

cypherdj

Hi Tony,

I believe what you're looking for is to use a data cube (use groupings by customer, purchase order and category, and amount as a measure).

Then create a crosstab based on this data cube. Crosstabs support row and column sub totals and grand totals. This should allow you to get the level of summation that you are looking for.

Check out the field guide to reporting for a good example using data cubes and crosstabs.

Regards,
Cedric