SSRS - Count grouped by Column AND Row in a Tablix

Question

I have an SSRS tablix similar to this:

+----------+-----------+--------+--------+
|  Total   | RowGroup  | Group1 | Group2 |
+----------+-----------+--------+--------+
| Perdiod1 | RowGroup1 | Value  | Value  |
|          | RowGroup2 | Value  | Value  |
| Perdiod2 | RowGroup1 | Value  | Value  |
|          | RowGroup2 | Value  | Value  |
| Perdiod3 | RowGroup1 | Value  | Value  |
|          | RowGroup2 | Value  | Value  |
+----------+-----------+--------+--------+

Now, for each period, I want to calculate the count in each group. When I do:

Count(TableValue.Value, "Perdiod") 

I get the total for the period (for both column groups). When I do

Count(TableValue.Value, "ColumnGroup")

I get the total for all periods. So really, what I need to do is something like this:

Count(TableValue.Value, "TablixRow", "TablixColumn")

which obviously doesn't exist.

So 'Value' should be a total count for a group within a Period (therefore, in the example given above, Value would be repeated twice in each Period (once for each RowGroup))

Is there a way to display the count of all the values within a specified column and row group in an SSRS's tablix?

Answer 1

You'd just need to add the aggregate expression to the matrix detail field without a specified scope, e.g. something like:

=Count(Fields!Value.Value)

Since you don't supply a scope, the aggregate will be calculated in its current scope, which for those value fields will be the particular group/period combination.

Edit after comment/update

Hmm... I'm trying to think of an easy way to do this, but coming up empty handed... I mucked around and could get any combination except the one you want.

Basically you're looking for something like:

=Count(Fields!Value.Value, "ParentRowGroup", "CurrentColumnGroup")

and I can't think of an effective way to do this.

Honestly (and it wouldn't be the first time for SSRS) the path of least resistance here is to add the period count you want to display as an extra column to your DataSet when you generate it, then just display this value unaggregated in the detail of the Matrix.

Annoying, but if you can control the DataSet it's a trivial solution to what is a difficult problem at the report level.