Solved

SQL Server reporting services (SSRS color code expression)

Posted on 2013-12-04
15
3,002 Views
Last Modified: 2014-01-09
Hi All,

Need your help on below SSRS expression.

Expression:

=Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, "Transparent",
(IsNothing(Fields!Invoice___to_date.Value)="") OR (Fields!Invoice___to_date.Value <=0),"Transparent")

1. Compare the Invoice___to_date column to the Sales_Order___to_date column.
a. If the Invoice value is less, then make background color as a RED.
b. If the Invoice is more, then make background color as a GREEN.
c. If the Invoice is equal, then do not add a color.

2. Only apply color expression to cells where there is an actual value (If cell doesn't contain any value, display cell background color as a transparent or No Color).

The above expression is not giving proper results for point # 1 & 2.

Please help me to correct my mistake in above expression.
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695084
What is the data type of those fields being used?  If not datetime then you may need a conversion for the logical expressions to work:

Switch(
	CDate(Fields!Invoice___to_date.value) < CDate(Fields!Sales_Order___to_date.Value), "Red",
	CDate(Fields!Invoice___to_date.Value) > CDate(Fields!Sales_Order___to_date.Value),"Green",
	CDate(Fields!Invoice___to_date.Value) = CDate(Fields!Sales_Order___to_date.Value), Nothing
)

Open in new window

"Transparent" is not a valid color, you need to use Nothing as shown above.

Also, I removed the following line because it doesn't make much sense (IsNothing returns a boolean, not a string):

(IsNothing(Fields!Invoice___to_date.Value)="") OR (Fields!Invoice___to_date.Value <=0),"Transparent")

Open in new window

I assume it was meant to implement your second condition.  You're probably better using an IIF around the Switch:

IIF(IsNothing(Fields!YourFields.Value), Nothing, 
  Switch(
	CDate(Fields!Invoice___to_date.value) < CDate(Fields!Sales_Order___to_date.Value), "Red",
	CDate(Fields!Invoice___to_date.Value) > CDate(Fields!Sales_Order___to_date.Value),"Green",
	CDate(Fields!Invoice___to_date.Value) = CDate(Fields!Sales_Order___to_date.Value), Nothing
  )
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39695104
Thanks ValentinoV.

Please find the data type details.

[Invoice___to_date] decimal(18,2)
[Sales_Order___to_date] DECIMAL(18,2)

I am new to SSRS technology, Can you please tel me which expression contains logic of point 1 & 2 or let me know which script I have to use (final script) for point 1 & 2 requirement in SSRS report?
0
 

Author Comment

by:sqldba2013
ID: 39695107
Please note that, I have to change Background color in cell not Text color using expression in SSRS reports (MS SQL 2005/2008).
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:sqldba2013
ID: 39695117
The below logic is working fine for point 1.

=Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, "Transparent")

Please let me know how to modify the above expression for point 2.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695216
>> [Invoice___to_date] decimal(18,2)
>> [Sales_Order___to_date] DECIMAL(18,2)

Ow I see, I was under the assumption that these were dates, apparently not.  So my statement using CDate conversion earlier doesn't make any sense...

>> Please let me know how to modify the above expression for point 2.

Assuming your table has a textbox (cell) called TextBox1 and you want to change the background color of that textbox, the following should go into the BackgroundColor property of the textbox:

=IIF(IsNothing(ReportItems!TextBox1.Value), Nothing, 
	Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
		Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
		Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39695405
Hi ValentinoV,

I've tried with below expressions, the output result is correct for Point 1 and for Point 2, the output result is wrong i.e., still it is showing RED background color in cell (these cell doesn't contain any value/data and for these cell(s), we need to set background as a transparent/ no color for these cells )

Please advise on further action item on this issue. I have tried below two options

Option 1:

=IIF(IsNothing(ReportItems!textbox44), Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Option 2:

=IIF(IsNothing(ReportItems!Invoice___to_date.Value), Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695453
What is the name of the textbox?  textbox44 or Invoice___to_date?  In any case, you need to include .Value (it's missing in Option 1)

Let's try this one then (assumes your textbox is called textbox44):

=IIF(Len(ReportItems!textbox44.Value)=0, Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39697454
Hi ValentinoV,

I have tried with above expression & output results are partially correct.

a. If cell contains 0 value (no value in cell): no background color - above expression is working perfect.
b. If cell contains - symbol (minus / hyphen): its showing background color as a RED. We need to set background color as a Nothing/No color where cell contains - symbol.

Please advise me on point b.

Once again thanks a lot for your assistance.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39697521
Is "-" the only character that needs to be taken into account?  And is that coming straight from the dataset or the result of some formatting on the cell?

Anyway, perhaps something like this then:

=IIF(Len(ReportItems!textbox44.Value)=0 or ReportItems!textbox44.Value = "-", Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39697618
Its a result of some formating in cell ('$' #,##0.00;('$' 0.00);'-')

I have changed formating to '$' #,##0.00;('$' 0.00); and applied above expression. Its still showing BG color as a RED & these cell doesn't contain any value/data).

=IIF(Len(ReportItems!Invoice___to_date.Value)=0 or ReportItems!textbox44.Value = "-", Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 

Author Comment

by:sqldba2013
ID: 39697638
I tried with below expression:

=IIF(Len(ReportItems!Invoice___to_date.Value)=0 or ReportItems!Invoice___to_date.Value = "-", Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39697853
Okay, so due to the formatting when the value displayed in the cell is zero you'll get the hyphen instead.

So how about this one then:

=IIF(Len(ReportItems!textbox44.Value)=0 or ReportItems!textbox44.Value = 0, Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

If that doesn't work, let me know what field you're displaying in textbox44...
0
 

Author Comment

by:sqldba2013
ID: 39702760
Thanks ValentinoV.

Its working perfect, You're the awesome.

Increasing points from 250 to 500.
0
 

Author Closing Comment

by:sqldba2013
ID: 39702761
--
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39702993
Cool!  Thanks for your nice comment, and the points ;)

Have a nice weekend!

VV
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question