How to create totals row for grouped columns

Question

If have an rdlc-report with grouped columns within a tablix (table). I want to add a footer row that spans all dynamically created columns and shows a total for all columns together. How can I tell to a cell that it should span all created columns created by the group?

|-------|-------|-------|
| col 1 | col 2 | col 3 |
|-------|-------|-------|
|   Column-Group Total  |
|-----------------------|

Please note, calculating the total is not my problem. I'm only searching for a way to tell report viewer to merge cells that are created automatically through a column-group for a specific row.

Update
Sadly, up to now, I have not found a solution to this. Moreover the same question I also have encountered in a report where I had to add totals of a row-group in a merged column.

|------|-------|
|row 1 |       |
|------| Row-  |
|row 2 | Group |
|------| Total |
|row 3 |       |
|------|-------|

I find this a quite common way of showing totals. Is this not possible in either way or am I missing something obvious?

Update2
Here a screenshot of what I mean:

In the middle there is a group. This creates at runtime n columns. What I want to do is the "spanning category total" to span all dynamically created columns. This means, that the columspan of the cell is n. There is only one cell and in this cell I will show the total of all categories. It's quasi the same as report viewer creates automatically at the top of the group.

Solution

Don't know if you've found an answer to this, yet, but if not...

Usually, totals are expected in the last column for data grouped in columns and the last row for data grouped in rows...

However, depending on the scope, and the level of grouping you have, you might be able to achieve what you want by embedding your tablix into an outer tablix, and then adding a row to the outer tablix that sums the data there.

I have used multiple data regions within rectangles and lists to manipulate all kinds of layouts. You just have to play around with scopes, and possibly adjust your output data (sums/averages by groups in stored procedure) if those scopes just aren't cooperating. Let me know if it solves your problem.

Comments

[HCL]

Thanks for your answer, but it responds not to my question - I have to know, how I can tell the report engine to merge the dynamically created cells for the total-row, so that the total cell spans all the automatically created columns. Something like the ColSpan in HTML for the totals row.

[HCL]

Thanks for your answer. I have tried, but visual studio does not let me merge the cells (Step 3+4). As soon as I select a cell that is part of a group, the merge-command of the editor is disabled. Are you shure that this is possible with BIDS? If yes, there may be the chance to define the merging in the xml, because as far as I know, the rendering engine is the same. In this case, it would only be a limitation of the editor. Can you say for shure that BIDS allows merging of grouped cells? This would be a great help for further searchings...

[Diego]

but what do you want? the sum of col1 + col2 + col3 on all rows? that would be just a matter of changing your expression from =Sum(Fields!col1.Value) to =Sum(col1+col2+col3)

[HCL]

Diego: If I understand your solution right, it is not what I'm looking for. You are adding rows, this means you're trying to solve the problem of the second figure I have drawn. As you see, there are dynamically created rows (throug a group) and then at the end of the line, there is one single cell that spans all rows of the group for showing the total. If you add a rowgroup in a tablix, report viewer creates you such a merged cell at the beginning of the group. I want to have such a merged cell at the end. Maybe you have a further idea on how resolving this? I have added a picture.

[HCL]

No. it's not a question about calculation, this works fine. It's about how to merge dynamically created cells of a group. I have added a picture to my question. Maybe this helps a little to clarify the problem (sorry, I'm not native english speaking, therefore, my post is maybe difficult to interprete).

[Diego]

Humm, I think I get it. How are you generating the dynamic columns? PS: where u from?

[Diego]

HI HCL. You did not tell how you are generating the dynamic columns. I need that info to try help you further

[HCL]

Hello, look at the image which I have added to my post. In the center, there is a column group (The column with the orange bar above it). I have titled the header as "Category". At runtime, this group creates n columns based on the provided data. The goal is, to make the cell "spanning category total" to be a merged cell (only one cell), whereas the "Category"-cell and the "value"-cell not will be merged and therefore will be multiplied n times (n is the amount of cateogries contained in the data).

[HCL]

As an addition to my previous comment: If you look at figure one, there are 3 categories. This means n = 3.

[Diego]

I mean, how does the value on the category creates n dynamic columns?

[HCL]

In the designer I add a column-group. Then I open the column-group-properties and specify a field which is used as the grouping field. At runtime then, the reporting engine creates for every distinct value in this field a new column. If the question is about the data source, I use CLR-objects which are declared as data sources. But this makes for the grouping no difference to plain datasets.

[saluce]

Try adding a screenshot now, you should be able to.