2

Below, I've attached a sample of my data as well as a sample of the chart output. I want to aggregate the data and include one row with this number; however, SSRS is just showing multiple of the same value.

In the example below, you'll notice there are 8 rows in Unit 1 and 8 rows in Unit 2, a total of 13. How can I get Unit 1 to have one row which shows 8 and one row in Unit 2 to show 5? Currently, the expression is =COUNT(Fields!SubID.Value, "DataSet1").

I've also tried adding in a column full of the number 1 which I could use to Sum on, but that produced the same results.

Originally I was doing this all in SQL; that is, producing the exact output I want in SQL and then charting in SSRS. However, this is no longer a viable solution as the end user would like to be able to drill down into the details of the report. I do imagine, if there is no easy way to do this (which I feel like there has to be), that I could write two queries, having one show the report and the other show the details.

Thanks.

enter image description here

enter image description here

Tom
  • 2,180
  • 7
  • 30
  • 48

1 Answers1

2

The second parameter of your count expression is the problem.

Don't use this:

=COUNT(Fields!SubID.Value, "DataSet1")

Try something like this instead:

=COUNT(Fields!SubID.Value, "UnitGroupName")

The group name should match what is shown in BIDS as the name you've given the grouping, such as under Row Groups.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • Great, thanks! You always have the solution to my SSRS problems :) – Tom Nov 28 '11 at 21:09
  • Thanks... I spend lots of time in SQL and C# as well, but others seem to answer those questions pretty quickly: I end up with the tiny corner that is SSRS. (And thanks for the decently written and illustrated questions.) – Jamie F Nov 28 '11 at 23:45