on 12-22-2013 12:48 PM
Hi Guys,
I need to Group a Table by two fields : Example first group is on the Product Type and a further Grouping inside is on the Business Partner.
I have been trying to group by only the Product Type first but it doesn't seem to work right.
I have created the control level group in the Context and used Subforms to try and group it. But the Group level field is repeating for every record.
Expected : -
Product Type 1
Row1
Row2
Row3...
Product Type 2
Row1
Row2
Row3..
Current : -
Product Type1
Row1
Product Type1
Row2
Product Type1
Row3
Product Type2
Row1...
Any help is appreciated.
Regards
Piyush Sharma.
Hello Piyush,
The problem lies in the grouping of the records in the table.
Please follow the steps below.
Create 2 tables. 1 Header table and 1 Item table.
1) Header_Table will contain unique product types with field name product_type.
2) Item_Table will contain the rows. In this add another field at the beginning and the field name will be product_type(just to link with Header table) and other fields as required.
In context node, first drag the Header_Table.
Then drag the Item_Table inside the DATA part of Header_Table. This will act as a loop.
Now click on Item_Table table. In Where Conditions button, give the below condition
product_type = Header_Table-product_type.
It acts as a loop as shown below.
Loop at header_table into st_header.
Loop at Item_table into st_item where product_type = st_header-product_type.
.....
.....
Endloop.
Endloop.
DATA sub form
Step 1) In layout editor you will be able to see these 2 tables in data view tab.
Step 2) Don't drag the whole table. Instead, drag only the data part of the Item_Table in the content area sub form in the design view(body page)tab.
Step 3) Select this DATA sub form and in the object palette give content as flowed and Uncheck the "Allow Page breaks within content".
Step 4) In Flow direction, give Western text.
Step 5) In Binding tab, Check the "Repeat sub form for each data item".
Table sub form
Step 1) Click on the parent sub form under which the DATA sub form is available. Select this parent sub form and in the object palette give content as flowed. In Flow direction, give Top to bottom. And check the "Allow Page breaks within content".In place give Following previous and in After give Continue filling Parent.
Step 2) And in Binding section, select the internal table Header_Table. Check the "Repeat sub form for each data item". This binding ensures that for each record of Header_table, only those records of Item_table are grouped for which the product_type is linked.
So the result will be as below.
Product Type 1
Row1
Row2
Row3...
Product Type 2
Row1
Row2
Row3..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Pavan,
Thanks for such a detailed reply..!! Much Appreciated.
I was trying using the Control Level functionality. But i tried your method, it seems to be very close to the solution. I just have one doubt.
I think i got it right till the DATA Subform where i dragged the DATA of the ITEM_TABLE.
Now what part of the HEADER_TABLE should I drag into the Design View?
I tried using the Product_Type Field directly - it gives the desired output but it repeats it many times over. Eg.
ProdType1
Row1
Row2
Row3
ProdType1
Row1
Row2
Row3
ProdType1
Row1
Row2
Row3
ProdType2
Row1
Row2
Row3...
Its repeating it equal to the number of rows in that group.
Hello Piyush,
Don't drag anything from Header_Table.
Now what you do is, wrap the DATA sub form in a parent sub form.
In the object palette give content as flowed. In Flow direction, give Top to bottom. And check the "Allow Page breaks within content". In place give Following previous and in After give Continue filling Parent.
And then bind it with the DATA part of Header_Table(and not directly Header_Table). I will repeat. Bind it only with the DATA part of Header_Table as shown below. Check the "Repeat sub form for each data item"
Works like a charm!
I found my mistake. I was passing the same table for the Header. I had to sort and delete adjacent duplicates from the header table so that it has only unique Product Types.
Also I had to put the Header field from the Header Table in the Parent subform of DATA.
I have now created two sub groups (Product Type - Business Partner - Data) as was needed. Thanks so much for your help!
One last thing:
Do you know how to hide empty rows? This is wat i am getting :
ProdType1
BP1
Row1
Row2
BP2 (empty row)
BP3
ProdType2
BP1(empty row)
BP2
Row1
Row2
BP3
I need to hide the empty rows on the Subgroup - BP. I tried using a simple javascript on the BP Field. doesnt work. This is probably because of the looping.
Hello Piyush,
I had a doubt that there would be duplicate records in header table. Since you told that till then you had followed only till DATA sub form step, I didn't ask you to check. Well, its good that you found out your own mistake.
Regarding the BP group problem, if you want to avoid empty rows at BP level, you can add two fields Product_Type and BP in Header_Table. Then you can add Product_Type, BP and other fields in Item table.
In where condition put the below condition.
product_type = Header_Table-product_type AND BP = Header_Table-BP.
I have put the following conditions in the ITEM_TABLE:
product_type = Header_table-prodtyp.
AND BP = BP_Table-BP.
This is the only way i am getting the correct set of records but with empty BP rows in between.
If i put :
product_type = Header_Table-product_type AND BP = Header_Table-BP.
I get incorrect data.
I tried putting some condition on the BP_table also. Does not seem to work. Trying to figure out some other way.
Had the same problem and debugged the generated FM.
It uses the AT NEW statement, so the field you want to group by must be the first field in the row structure or you have to make sure that the fields left of your group field don't change.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.