counting orders in a order detail report

[garydr]

I have a standard Order table linked via an order id to an order detail table which is in turn linked via product id to a catalog table. I'm running a report using Order Detail to report the total quantity and dollar value of all products sold during a specified calendar period. That's all working fine. But I can't figure out how to report the number of unique orders there were for each product reported. I can't assume there will be one order detail record for each product in an order. One product could appear more than once in an order since these products can be ordered with different options included. So I need to be able to count the number of unique order id values.

Any help would be greatly appreciated.

thanks in advance.

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/297916/

[garydr]

Fantastic. It works. I just had to add a second sort field to sort on order_id and it worked. I read your explanation of how it works but I don't really get it. I'm sorting on product_id followed by order_id. My cInverseCount is 1/GetSummary(sCountSerialID, Order_id). Then sCountOrders = Total of cInverseCount. sCountOrders appears in my subsummary by product_id area and total grand summary. sCountOrders shows the count I"m looking for.

If you don't mind could you spell it out to me how this works.

Thanks again.

[comment]

I am not sure I can explain it better than I did there, but perhaps an example might help.

Let's say there are 2 sub-groups, one with 4 items, the other with 2. The calculation =

GetSummary ( sCountSerialID ; SubGroup )

would return the following results for each item (in the body of the report)

[big]Group A[/big]

SubGroup A1

• Item 1: [color:red]4

• Item 2: [color:red]4

• Item 3: [color:red]4

• Item 4: [color:red]4

SubGroup A2

• Item 5: [color:red]2

• Item 6: [color:red]2

...

Changing the formula to 1/x (the multiplicative inverse of the count) will result in:

[big]Group A[/big]

SubGroup A1

• Item 1: [color:red]0.25

• Item 2: [color:red]0.25

• Item 3: [color:red]0.25

• Item 4: [color:red]0.25

SubGroup A2

• Item 5: [color:red]0.5

• Item 6: [color:red]0.5

...

Now just work out what the sum of these results is on the sub-group level, the group level, and in total.