Group by in CR

[kyon]

Hi,
I'm using CR Verson 8.0.1.0. I want to group records from a table in MS Access and then give out the summarize quantity. For example: A table in MS Access have records as below,
Item_Code Season Design Col_Code Adjust_Qty
2300MP 3Y ABC BN7 3
2300MS 3Y ABC WE1 4
2300MP 3Y ABC BN7 1
2300MS 3Y ABC BN7 2
2300PT 3Y ABC GD1 2

After I insert a group to the report I made, it is just show as below:
Item_Code Season Design Col_Code Adjust_Qty
2300MP 3Y ABC BN7 3
2300MP 3Y ABC BN7 1

2300MS 3Y ABC WE1 4
2300MS 3Y ABC BN7 2

2300PT 3Y ABC GD1 2

In fact, I want it to display as follow:
Item_Code Season Design Col_Code Adjust_Qty
2300MP 3Y ABC BN7 4
2300MS 3Y ABC WE1 6
2300PT 3Y ABC GD1 2

I found that I can not add GROUP BY statement to the function "Show SQL Query" which in the CR. What I should do for displaying the summarise quantity by Item_Code?
Any suggustion is welcom! Thank you very much!

kyon

 

[lbass]

Right click on {table.Adjust_Qty} and insert a summary (sum) for all groups. You have to decide what col_code you want displayed, but to get the display you have described, it looks like you could just drag the detail fields into the group header and then drag the group summary (which is automatically placed in the group footer) into the group header, too. Then suppress the details by going to format section->details->suppress.

-LB

 

[synapsevampire]

LB nailed the display concern, though you can use either the group header or group footer I think.

To address the Group By issue in the SQL Statement, in order to get Crystal to pass the Group by on to the database in SQL, you would need to insert groups for all fields in the query (as with SQL), and then make sure that the file->Report Options->Perform grouping on Server is enabled.

You would then see the SQL in the Database->Show SQL Query and reap the benefit of Server Side processing.

-k

 

[kyon]

Thank to lbass and synapsevampire

I have tried the sugguestion from synapsevampire and it is exactly what I want. Thank you very much

kyon