# Counting the number of rows that have a value in a specific column [Answered]RSS

## 2 replies

Last post Jun 23, 2011 02:21 PM by MSBoston

• MSBoston

Member

27 Points

49 Posts

### Counting the number of rows that have a value in a specific column

Hello,

I'm having a small problem with Report Builder and trying to get it to display the right count.  So here's a little overview of my issue.

I'm creating a report about certain awards being given out to people.  These awards are rated as 5 (Outstanding) being the highest and 1 (Unsatisfactory) being the lowest.  For the actual report each row is a subtotal of a specific specialty (HR / Accounting / etc.) with the columns being "Rating O", "Rating E", "Rating FS", "Rating MS", "Rating U" for the awards rating 5-1.  When you expand the rows they correctly show each person within that service and what award they received.

My issue comes from trying to get counts of each award for each specialty (Example: HR has 4 people with "Outstanding (5)" rating thus the number that should show up in column "Rating O" should be 4 and when you expand the HR row you'll see the 4 people with the "Outstanding (5)" rating.

Instead, what is happening is that the count in each row for the subtotal becomes the total count of all of the awards within each specialty.  So for example, there are four 5's, three 4's, two 3's, one 2 and zero 1's.  In each row, which should be counting the total number of each separate award I'm getting a "10" instead of "4" for Rating O, "3" for Rating E, "2" for Rating FS, etc.

My expression to show a "1" when a person has that award and shows "" if a person does not have that award. (This example deals with Rating 5 = Rating Outstanding)

`=IIF(Fields!Rating.Value = "5", "1", "")`

My expression to show the total count of the specific awards for each specialty. (This example deals with Rating 4 = Rating Excellent)

`=Count(IIF(Fields!Rating.Value="4", 1, 0)`
• You-Hu Fu

Contributor

6670 Points

794 Posts

### Re: Counting the number of rows that have a value in a specific column

Jun 23, 2011 01:58 AM|You-Hu Fu|LINK

Hi MSBoston,

From your information, you want to show different content when you extend or contract the "+" sign, to this requirement, I think you can make use of InScope function to distinct extend and contract status. Just like below sample:

=iif(inscope("SubCategory"),Sum(Fields!OrderQuantity.Value),Count(IIF(Fields!SubCategory.Value="Locks", 1,0)))

Please mark the replies as answers if they help or unmark if not.
Feedback to us
Microsoft One Code Framework
• MSBoston

Member

27 Points

49 Posts

### Re: Counting the number of rows that have a value in a specific column

I basically solved my own problem by "hacking" my way to a solution.  I completely changed the way that I looked at this problem.  Instead I went with a fairly round-a-bout way of solving this.

I created 4 datasets:

1 for the main query

1 to get the total number of people per service

1 to get the total number of ratings per service (rating = 5, 4, 3, 2, 1)

1 to get the total number of awards per service (rating = 5 or 4)

I then used these different datasets (queries) with the Lookup function in Report Builder to link each dataset (on Service Name) which pulls the total awards, people, ratings to the main table.

For example to find the total ratings percentage (all people rated 5,4,3,2 or 1 in a service / total people):

`=Count(Fields!FullName.Value) / Lookup(Fields!ServiceName.Value, Fields!ServiceName.Value, Fields!TotalPeople.Value, "TotalPeople")`

This was a much easier solution to implement.