Excel export: group and sum items in detail band

[dott]

Hi,

I have a list of items. All items have names, quantities and project names. Items can be duplicate in the list. I wanted to group the items by project name, so I used report group, but I also want to sum the items within a project if there are duplicate items.

Excel sheet now:

project name: 123 (in Group header)

Name           Quantity (in Group header)

item1                 15     (in Detail band)

item2                   5     (in Detail band)

item2                   7    (in Detail band)

item3                  11   (in Detail band)

 Excel sheet I prefer:

project name: 123 (in Group header)

Name           Quantity (in Group header)

item1                 15     (in Detail band)

item2                  12     (in Detail band 5+7)

item2                   7    (remove duplicate from Detail band and add them up in one element) 

item3                  11   (in Detail band)

 

I use sql query. I tried to sum up the items in sql, but then I could not group the items by project name, since all items were summed and not only within same project name.

If you need more information, please let me know.

I appreciate your help.

 

[dott]

In sql query "Select item.item_name, project.project_name, sum(inventory.item_quantity) ... From project, item, inventory ... Where (project.projectid = inventory.projectid) and (item.itemid = inventory.itemid) ... Group By project.project_name, item.item_name..."

Adding report group in ireport: (in Report Inspector view) right click on report and select Add Report Group > Select group by: project.project_name > next: add group header and footer.