Counting item categories
I'm having difficulty creating a report that will allow me to show total sales for specific categories of our products. Regular summary counts aren't working.
I'll try to explain the scenario. We sell training manuals. We have 115 individual items or SKUs. We have 5 manuals, but each manual is available in multiple languages and multiple formats (print, CD, download, copies). I have set up a series of conditional item categories and subcategories to allow for easy data entry.
Now I need a quarterly report that shows (on one page) how many total manuals were sold, how many were sold in each format, in each language, and in each topic. Using a summary field type, I have been able to use "the count summary" to count item category fields. This essentially counts the lines that have a particular category code. This works great for lines where only one item is purchased. However, the count summary ignores the quantity field. So if someone purchases more than one of a particular item, it is still only counted once. I have not been able to figure out a way to fix this problem. It seems to me each category match field in a line (or record) needs to be multiplied by the qty and then counted/summarized, but I cannot figure out how to do it.
The image I included is the report I've created. It shows 29 total products sold but only seven if you look at it by title. That is because we had an order that had 10 of one title and 11 of another title. The number of resources distributed at the top is a simple summary: total of quantity field. The individual line items below are all summary: count of category item ID fields.