Grouping rows for a report

[A Random Dan]

Newbie here, I'll try and explain my problem best I can. I have a table with my products:

Product

- _kp_product_id

- style

- color

- size

- price

- sale_date

I want to genereate a report that shows sales with a row for each distinct product style, color, and size with a qty rather than having a row for each sale. I have been trying a self-join relationship but am still unable to "group" the rows properly. Is there a way to generate this type of table relationship?

[comment]

Instead of a self-join, why don't you simply sort the records by product style, color, and size, then show them in a layout with a sub-summary part for each breakfield and no body part? Use a summary field to count the records.

[A Random Dan]

I don't believe that will accomplish what I'm after. My records are as follows:

ID Style   Color  Price Size

1  Regular Green  12    Big

2  Regular Green  12    Big

3  Regular Yellow 12    Small

4  Regular Yellow 12    Small

5  Regular Yellow 12    Small

6  Regular Yellow 12    Big

7  Premium Red    14    Big

8  Premium Red    14    Big

9  Premium Yellow 14    Big

I want them to appear in a report (or portal) as:

Style   Color  Price Size  Qty

Regular Green  12    Big   2

Regular Yellow 12    Small 3

Regular Yellow 12    Big   1

Regular Red    14    Big   2

Regular Yellow 14    Big   1

Does that clarify my problem? I was under the impression the best way to achieve this is by a self-joining relationship.

[qube99]

Perhaps you could use your sales records instead of your product list? Sub-Summary layouts sure looks like the way to go to me. They'll give you both the number of items sold and the total dollars.

[dansmith65]

I agree with the previous posts; base the report on sales table and use a sub-summary report.

You may need a calculated field that concatenates style , color and qty fields so you can sort by and create a sub-summary part for.

[comment]

I don't believe that will accomplish what I'm after.

It's not a matter of belief:

Report.fp7.zip

I was under the impression the best way to achieve this is by a self-joining relationship.

Summary reports have the advantage of reporting only the found set.

You may need a calculated field that concatenates style , color and qty fields so you can sort by and create a sub-summary part for.

Not really.