How to create totals row for grouped columns

8
votes

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:

Spanning Category Total

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.

3
reporting-servicesssrs-2008rdlcrdlreport-viewer2010
The dynamic columns, is this some sort of pivot (column head of Fiscal Year, and data value of dollars spent in that fiscal year for example)? – Pulsehead
@Pulsehead: Right, there are reports with pivotted years, as you mentioned. E.g. col1 = 2005, col2=2006,col3=2007 and the column group total should show the total of col1-col3. In other reports, there are other grouped columns such as office locations, month names etc. The amount of columns is not fixed, it is depending on the data to be visualized. – HCL

3 Answers

1
votes

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.

0
votes

As I know, rdlc are report files you edit on Report Wizard or visual studio as opposed as rdl that are report files being developed on BIDS.

I don't know if this will work on rdlc files because I only use BIDS, but I think its worth the try:

  1. on the row groups tab (botton left) right click on your group and select add total -> after, it will add a total row.
  2. temporarily copy the sum cell (the total) to some other place on the report (because the next step would erase it if you dont)
  3. using SHIFT, select cell by cell on the row you want to merge (don't select the row itself)
  4. right click it and select merge cells
  5. paste the sum cell you copied away on step 2

Result (I hope you can see):

enter image description here

EDIT:

answering your question, yes I'm sure it can be done because I did it several times and also you saw on the print screen. I'm pasting the XML for my row, I think the secret is on the <ColSpan>8</ColSpan> tag. I do have 8 columns on my report.

<TablixRow>
<Height>0.25in</Height>
<TablixCells>
  <TablixCell>
    <CellContents>
      <Textbox Name="textbox18">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>=Sum(Fields!myField.Value)</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>9pt</FontSize>
                  <Format>'$'#,0.00;('$'#,0.00)</Format>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Right</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox16</rd:DefaultName>
        <Style>
          <Border>
            <Color>LightGrey</Color>
            <Style>Solid</Style>
          </Border>
          <BackgroundColor>White</BackgroundColor>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <ColSpan>8</ColSpan>
      <rd:Selected>true</rd:Selected>
    </CellContents>
  </TablixCell>
  <TablixCell />
  <TablixCell />
  <TablixCell />
  <TablixCell />
  <TablixCell />
  <TablixCell />
  <TablixCell />
</TablixCells>
</TablixRow>
-1
votes

Since it looks like you have a dynamic column group, you will probably put into the columns:

Fields!ColHeading.Value |Total
Fields!DataValue.Value   | =SUM(Fields!DataValue.Value)

If you prefer the graphical interface, right click on the total's value field and click on Expression. In the popup, expand the Common Functions and click on Aggregate and in the Item window, double click on Sum. Type in or click the data field and you will get something like this:Filled out Expression Window.

Hit Ok, and you are good to go!