Groups within a Group

[Shiloh917]

Okay, This is what I have. I have a report that has 3 Groups. The 1st group is the location, the 2nd group is the product and the 3rd group is the qty of each product. However I have 10 locations with a variety of the same products on each floor.

For Example:
10N has 11 products and a certain qty of each
3N has 11 products and a certain qty of each
4N has 11 products and a certain qty of each
4S has 11 products and a certain qty of each
4E has 11 products and a certain qty of each
4W has 11 products and a certain qty of each


What I need to do is combine all the "4" floors to make it look like they are one location and one qty for each of those products. This is an example:

Product 10N 3N 5S S4 4E 4W 4S 4N

Bottled 50 60 30 10 200 25 30 25

Glass 75 55 25 80 150 225 40 60

Jugs 55 60 32 28 300 550 60 75


Thus showing Section "4" as having 280 Bottled, 475 Glass, and 985 Jugs. I hope I am explaining this clearly but if you get my jist and can help me please do or if I need to be more clearer let me know.

Thank you!!

 

[Madawc]

For Crystal 8.5, you can do this by creating a formula field to replace the group. I.e.,

If left({product}, 1 = "4" then "Group 4"
else {product}

Group using this formula field.

Madawc Williams
East Anglia, Great Britain

 

[Shiloh917]

I tried this and it is giving me and error that says:

The ) is missing

I tried adding the paren where the cursor flashes and it still won't work. Suggestions?

 

[Madawc]

Sorry, should be

If left({product}, 1) = "4" then "Group 4"
else {product}



Madawc Williams
East Anglia, Great Britain

 

[Shiloh917]

Well that works but like I had it. I need to have all the floors that start with a 4 group together and show as one floor, and show one product and show one qty for the product. This way shows each product and all locations and diff qtys for each location in other words it is showing

Bottled
10N 1
10N 528
4 185
4 200
4 100
Jugs 5S 200
5 250

etc. I just want it to show me the product 1 Time with a Grand Total for everything in Section 10 like in this case for Bottled in Section 10 it should show me 529.

Thanks!

 

[lbass]

After grouping on the formula, you need to insert a summary (sum) on the quantity and then suppress the details, displaying only the group footer (or header) with the group name and the summary. If you wanted a layout like in your earlier post, you would insert a crosstab, use the formula as your column, products as your row, and quantity as the summary field.

-LB