Conditional Formatting Question RS 2005

Hi,

Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).

I have tried approaching it in two ways. One was with a case statement as follows:

CASE

WHEN TargetPer < 83 THEN 'Red'

WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'

WHEN TargetPer >= 90 THEN 'Green'

END AS BGColor

TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:

=Fields!BGColor.Value

Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)

The other approach i have used is the following expression to set the background colour

=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))

For this i still have no yellows just reds and greens!

I am using a matrix report that has subgroups on the columns for table!

Any ideas anyone?

Thanks

Marek

You have 2 options to implement CASE logic. Using nested Iif() calls or using the Switch() method. The definition is Switch(<condition>, <value>[,<condition>, <value>],....). So for the first condition that evaluates to true it's corresponding value is returned.

In your case the problem is that you are not applying any aggregation function around your field references. Any groupping naturally means that some aggregation will be applied. If not specified, the default aggregation function is First() rather than Sum(). Also you seem to have Fields!BGColor.Value in your condition which I believe should be Fields!TargetPer.Value.

So the way I see it you have 2 options to re-write your statement:

Using Iif():

=Iif

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Iif

( Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, "Red"

)

)

Using Switch():

=Switch

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, Sum(Fields!TargetPer.Value) < 83 'You could also put True here (CASE ELSE)

, "Red"

)

Concatination problem

Hi all,

I need to get in my stored procedure name of 'result' field as parameter.
I'm trying to implement that using sp_sqlexec but everytime I get error...
Any ideas?
---------------------------
Create procedure MyProc
@.CurClientID varchar(10)
@.CurCounterName varchar(20)

AS

declare @.ExecString varchar(300)

set @.ExecString = 'Select ' + @.CurCounterName +
' FROM Client WHERE ClientID=' + @.CurClientID

exec sp_sqlexec @.ExecStringOriginally posted by yurich
Hi all,

I need to get in my stored procedure name of 'result' field as parameter.
I'm trying to implement that using sp_sqlexec but everytime I get error...
Any ideas?
---------------------------

this should work :

Create procedure MyProc
@.CurClientID varchar(10)
@.CurCounterName varchar(20)

AS

declare @.ExecString varchar(300)

set @.ExecString = 'Select ' + @.CurCounterName +
' FROM Client WHERE ClientID=' + @.CurClientID

exec sp_sqlexec @.ExecString

Create procedure MyProc
@.CurClientID varchar(10)
@.CurCounterName varchar(20)

AS

declare @.ExecString varchar(300)

set @.ExecString = 'Select ' + @.CurCounterName +
' FROM Client WHERE ClientID=' + @.CurClientID

exec (@.ExecString)|||The holy book[SQL Server Books online] says so :

sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application. Removed; no longer available. Remove all references to sp_sqlexec.|||It does not help...|||What is the error you are getting ?|||Syntax error converting the varchar value ...|||Originally posted by yurich
Hi all,

I need to get in my stored procedure name of 'result' field as parameter.
I'm trying to implement that using sp_sqlexec but everytime I get error...
Any ideas?
---------------------------
Create procedure MyProc
@.CurClientID varchar(10),-- just add comma !!!!
@.CurCounterName varchar(20)

AS

declare @.ExecString varchar(300)

set @.ExecString = 'Select ' + @.CurCounterName +
' FROM Client WHERE ClientID=' + @.CurClientID

--exec sp_sqlexec @.ExecString
exec(@.ExecString)|||Snail you beat me to it ,

Yurich ...thats the only error i could find in the code .. rest the code seems fine|||I imagine your missing the quotes...

DECLARE @.SQL varvhar(8000)
SELECT @.SQL = 'Select ' + @.CurCounterName
+ ' FROM Client WHERE ClientID=' + '''' + @.CurClientID + ''''
SELECT @.SQL

exec (@.ExecString)|||Originally posted by Brett Kaiser
I imagine your missing the quotes...

DECLARE @.SQL varvhar(8000)
SELECT @.SQL = 'Select ' + @.CurCounterName
+ ' FROM Client WHERE ClientID=' + '''' + @.CurClientID + ''''
SELECT @.SQL

exec (@.ExecString)


It works! Thanks a lot. Now I need to get out result of this query to local variable:

DECLARE @.SQL varvhar(8000)
DECLARE @.Res varchar(100)
SELECT @.SQL = 'Select [@.Res] ' + @.CurCounterName
+ ' FROM Client WHERE ClientID=' + '''' + @.CurClientID + ''''

exec (@.ExecString)
It works, but after executing @.Res = NULL...|||Originally posted by yurich
It works! Thanks a lot. Now I need to get out result of this query to local variable:

DECLARE @.SQL varvhar(8000)
DECLARE @.Res varchar(100)
SELECT @.SQL = 'Select [@.Res] ' + @.CurCounterName
+ ' FROM Client WHERE ClientID=' + '''' + @.CurClientID + ''''

exec (@.ExecString)
It works, but after executing @.Res = NULL...

You cannot save result to local variable but you could save it to temporary table:

create table #tmp(...)
insert #tmp
exec(...)