Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier