3

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?

tomsky
  • 535
  • 4
  • 11
  • 28

2 Answers2

2

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • I could do that but actually my requirements are different, I've just realized this thanks to your answer and edited my question. So basically, each value should represent a total for the group and Period (therefore, the value should be repeated twice in the example given above). – tomsky May 07 '13 at 14:14
  • Added my thoughts, please see above. – Ian Preston May 07 '13 at 16:29
  • Thanks for your reply Ian. Going to the stored procedure and changing it as the only option to solve it was exactly what I feared of, since the Period group is calculated in the report definition as the user is allowed to choose the period then want to group by. Also, we'd like to avoid any kind of grouping in the stored procedure as it is used by multiple reports. – tomsky May 08 '13 at 06:37
0
=CountRows()
=CountRows("GroupByInitial")
=CountRows("GroupByInitial",Recursive)
Auric
  • 1
  • 1