Search code examples
reporting-servicesssrs-tablix

Combining Cells in Locally Rendered Microsoft Report

I have a basic Tablix in my report that currently renders as follows:

Report in current state

These are steps in a manufacturing process with the clock number of the person who performed them with date performed in the last two columns. However, sometimes steps are combined and performed together. In the example above, steps 10-20 are performed together, and 30-40 are performed together. So I would really like the report to be rendered like this:

Preferred state of report

I do have a column in my data called "StepRange" which in the above example would be "10-20" for the first two rows and "30-40" for the third and fourth rows. So when the value of StepRange is alike, I know those rows are performed together and henceforth the last two columns should be combined. My example shows only two rows being combined at a time, but it could be any number.

How can I make my report look like the second example above instead of the first?

Solution

  • Single Tablix Method

    Rather than literal conditional merging, you can set the border style of a textbox using an expression to achieve a similar effect. There are a few steps, but none of them are particularly involved.

    • Create a parent group for StepRange. Do not add a header or footer, and delete the added column without removing the group.

    • Make sure that your properties panel is visible on the right of your screen. If not, check the "Properties" checkbox under the View ribbon.

    Enabling the property pane.

    • Click on your first detail TextBox and expand the "BorderStyle" property. Set the "Top" property to the following expression:

    Setting BorderStyle.Top to an expression.

    =IIF(RunningValue(Field!Step.Value,CountDistinct,"StepRange")>1, "None", "Solid")

    • Set the Bottom property to "None".

    • Set the expression of the detail TextBox itself.

    Replace FIELDNAME with the appropriate field:

    =IIF(RunningValue(Field!Step.Value,CountDistinct,"StepRange")>1, "", Field!FIELDNAME.Value)

    • Repeat this process for each detail TextBox.

    • You may need to create a dummy row at the bottom with a black top border if you do not have a summation row. (optional)

    The expression only evaluates to "Solid" for the first Step value within each StepRange group, so subsequent rows do not have a top border and appear undivided.

    Nested Tablix Method

    Using a nested Tablix is more straightforward. I have had some issues with them, including some rendering hiccups. But in a report this simple that may not be an issue at all.

    • Set up your main Tablix to group on StepRange.
    • Either clear or add a column to the left for the individual steps.
    • Select "Insert Table" from the toolbar and click on the empty cell.
    • Set the cells to your step and operation fields and delete the extra column.

    The result should look roughly like this:

    Nested Tablix Example

    • By default the inner Tablix will be detail grouped. If your detail rows are more granular than the "Step" field, go to the properties of the "(Detail)" group and add a Group Expression for Step.

    • You can also delete the inner header row if you don't want to see it repeated in the report.

    This results in the employee fields actually being merged and spacing properly. If you don't use an aggregate function on those fields, their value will be that of the first row returned internally. Which is moot if their values are uniform across steps.