dott Posted May 13, 2019 Share Posted May 13, 2019 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. Link to comment Share on other sites More sharing options...
dott Posted May 13, 2019 Author Share Posted May 13, 2019 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. 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