Tablix column and row grouping
I need to create a report where i can group on columns and rows like this Region 1Store 22Owner 301---------3-------------------------------------Date | ID | Product | Quanity | Price | Total |.... | 12 | A | 2 | 5 | 10 |.... | 13 | b | 3 | 6 | 18 | owner total: 28 |0wner 302-----------------------------------------------Date | ID | Product | Quanity | Price | Total |.... | 14 | A | 4 | 7 | 28 |.... | 15 | b | 3 | 6 | 18 | owner total: 46 | Store Total: 74 Region Total: 74Region 1Store 23Owner 303---------3-------------------------------------Date | ID | Product | Quanity | Price | Total |.... | 16 | A | 28 | 2 | 56 |.... | 17 | b | 34 | 5 | 170 | owner total: 226 |0wner 304-----------------------------------------------Date | ID | Product | Quanity | Price | Total |.... | 18 | A | 2 | 5 | 10 |.... | 19 | b | 3 | 6 | 18 | owner total: 80 | Store Total: 306 Region Total: 380 I can do it all on row groupin but i want the design to look like that so i guess i will need to create column groups for store, region and owner and group it to my details row group. I just dont know how to that. Can anyone can point me in the right direction.
June 22nd, 2011 12:59pm

You can do this with row groupings. You will group by Region, Store, Owner, Details. Start with just the details group and then add a Parent Group to the Details group for Owner. (right click on Details group and add Parent Group). Be sure to includea header and footer to your Owner group. Delete the new column that was added for the group (now the first column in the report). In the Owner group header row, merge the cells in the row and add the expression ="Owner " + Fields!YourOwnerCode.Value Under the Owner Header row add another row, inside the group. There you can put in your ----------- if you want that. You can merge the cells in that row and add your dotted lines, or you could just edit the border properties of the header row to have a dotted line on the bottom border. Add the owner total into the Owner Group footer. Since you want to have the column headers repeat for each Owner grouping, add a row under the Owner group row, and copy the column headers here. You can remove the column headers from the top of your report. Add a parent group to the Owner Group in the same manner, this time for Store. Include a header and footer row for Store. Remove the new column which was added when you created the group. Add a row under the Store header row if a blank row is required between Store and Owner headers. Add your Store total to the Store footer Add a parent group to the Store Group for Region. Remove the new column which was added when you created the group. Include a header and footer row for Region. Add the Region totals to the Region footer. Ideally you have group the details in the dataset, but if for some reason this was not possible, group the details in the Details Group on multiple items as needed - namely Date, ID and Product. Let me know if you have any questions.Martina White
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2011 4:24pm

Hi kcbread, Thanks for your question and Martina’s reply. According to your description, I understand that you would like to group the records on column. Actually, there was no a good way to achieve the requirement if we only use one tablix, based on my research and experiences, I would give you a workaround: nesting several lists. A list can create more flexible layout, and we can use its group just as the column group. Detail steps, please follow below, Supposing that the Database table Products provides the records for the Dataset Products, the records and structure of the Products just like this, 1. Drag one table from the Toolbox into the design surface, named Tablix1, bind the field of Product to the Tablix1. 2. Drag one list from the Toolbox into the design surface, named Tablix2, set Tablix2’s Datasetname is Products, drag one textbox from the Toolbox into the Tablix2 named Textbox2, bind the field of Owners to Textbox2, drag the Tablix1 into the Tablix2, and the Tablix1 is below the Textbox2. 3. Drag one list from the Toolbox into the design surface, named Tablix3, set Tablix3’s Datasetname is Products, drag one textbox from the Toolbox into the Tablix3 named Textbox3, bind the field of Stores to Textbox3, drag the Tablix2 into the Tablix3, and the Tablix2 is below the Textbox3. 4. Drag one list from the Toolbox into the design surface, named Tablix4, set Tablix4’s Datasetname is Products, drag one textbox from the Toolbox into the Tablix4 named Textbox4, bind the field of Regions to Textbox4, drag the Tablix3 into the Tablix4, and the Tablix3 is below the Textbox4. Please preview the report, the effect just like this, Hope it helps you, if your issue still exists, please feel free to let me know. Thanks, Sharp Wang Best Regards. Sharp Wang
June 28th, 2011 6:30am

I'm not sure why you would go to all that trouble when the functionality exists just by using row groupings as I described above. I do it all the time. Am I missing something?Martina White
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2011 9:54am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics