Showing posts with label cells. Show all posts
Showing posts with label cells. Show all posts

Tuesday, March 20, 2012

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

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

Thursday, March 8, 2012

Conditional Navigation Action?

RS 2005.

I cant see a way of putting in a conditional navigation action on a table cell. e.g. some table cells should have a url action, others should not based on the data.

Any ideas?

Try setting the expression in your url to Nothing base on the data.

Ex. =iif(SomeField=0,"www.mywebsite.com",nothing)

Only if somefield as the value of 0 then if opens www.mywebsite.com.

Wednesday, March 7, 2012

Conditional Formatting Problem when Rendering to Excel vs. PDF/HTML

Hello,

I need some quick help. I have conditional formating for color in some of the cells in my reports. When I render the reports to HTML or PDF, colors work great. When I render the report to Excel, all the cells come through in one color. How do I fix this in RS 2000? Or how do make the rendered Excel file just one specific color, like black?

Hi,

There was a similar problem with the hyperlinks used in the reports.

When HTML or I guess .pdf works fine Excel was not working as desired.

Targeting the output format that will be generally rendered by users was my solution then.

Eralper