Conditional Grouping help

[antmoe3]

I need to create a report with subtotals based upon a certain condition existing. The format is below. The text and sub-total for the warehouse (WH) should ONLY display and calcuate the sub-total if the warehouse exist in that dept. How do I do this?

Dept VEND WH UNITS
17 123 1 2000
17 345 2 5000
17 556 2 2500
17 654 3 1500

WAREHOUSE 1 TOTAL 2000
WAREHOUSE 2 TOTAL 7500
WAREHOUSE 3 TOTAL 1500
DEPT TOTAL 11000

Dept VEND WH UNITS
18 223 1 3000
18 325 2 4000
18 596 2 2500

WAREHOUSE 1 TOTAL 3000
WAREHOUSE 2 TOTAL 6500
DEPT TOTAL 9500

 

[Madawc]

Do you want to show sonmething for departments with a warehouse? If so, so a left-outer from department to warehouse. Group by Department. Do a group, within department, a formula field called soemthing like @Check_Warehouse that is either the warehouse or the lack of one. I.e.

If isnull(WH.code) then "No Warehouse" else "Warehouse " & ToText(WH.code)

Also consider doing a crosstab, department against @Check_Warehouse. This would depend on your Crystal version, 8.5 probably won't allow it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[antmoe3]

The problem is not with printing out the number for the warehouse and the associated text, it is printing one row or two rows or three rows depending upon whether the warehouse exist in a particular dept (dynamic rows). Look at the example again. Can you help?

 

[Madawc]

Group by warehouse. Suppress the detail lines and just show the group header or footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[antmoe3]

Group by warehouse will leave blank lines when the warehouse is not in that particular dept. This is not what I need. Say if I have 10 warehouses within dept 17 then only 3 warehouses in dept 18, I need to see the sub-totals for all 10 warhouses under dept 17 then in the next break see all the sub-total for all 3 warehouses under dept 18. This is what I need. I know is going to be a lot of syntax I must create for this to work but I just need some ideas. Any more ideas?

 

[lbass]

No, this is very simple. Group on department and then on warehouse. Insert a summary on units and then suppress the detail section. Then drag the groupname into the group footer. To eliminate blank sections, go to the section expert->group headers/details/group footers->check "suppress blank sections".

-LB