SSRS - Using IIF Statement

IIF statement is pretty much easy if it is a straight forward single condition. If it is nested IIF statement (multiple conditions), it might some time takes a little time to understand and use it.
Ex: If there are 4 conditions to check then use only 3 IIF conditions.
This is an example of cell Background Color Expression in SSRS.

=IIF(Fields!DATA1.Value="Lite Turquoise" , "#CCFFFF", (IIF(Fields!DATA1.Value="Ivory","#FFFFCC",
(IIF(Fields!DATA1.Value="Plum" , "#993366", "#9999FF")) )))

Hope this article helps...
Thank you.

SSRS - Color Pallette codes

This pallette, shows the color names and they significant HTML color code/ BGColor.

Note: This above images taken from http://www.mvps.org/dmcritchie/excel/colors.htm

Hope this article help you to use colors/color codes.
Thank you. :)

SQL Server -BETWEEN statement using with DATETIME datatype.

I was using BETWEEN statement with the column that has data type of DATETIME. When debugging or validating data, i found out that the count is mismatch due to the DATETIME.
Ex:

Create table #BETWEENDATETIME (RowNumber INT Identity(1,1), DateTimeTest DateTime)

Insert into #BETWEENDATETIME VALUES ( '2010-07-21 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-21 00:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-20 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-23 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-25 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-26 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-23 16:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-28 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-29 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-29 15:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-30 10:11:50.760')

From the above table:

SELECT * FROM #BETWEENDATETIME WHERE DateTimeTest BETWEEN '2010-07-21' AND '2010-07-29'

It retrieve the following data:

RowNumber DateTimeTest
1 2010-07-21 17:11:50.760
2 2010-07-21 00:11:50.760
4 2010-07-23 17:11:50.760
5 2010-07-25 17:11:50.760
6 2010-07-26 17:11:50.760
7 2010-07-23 16:11:50.760
8 2010-07-28 17:11:50.760

The above retrieved data was invalid.

Solution:

SELECT * FROM #BETWEENDATETIME WHERE DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) BETWEEN '2010-07-21' AND '2010-07-29'

This returns the following data:

RowNumber DateTimeTest
1 2010-07-21 17:11:50.760
2 2010-07-21 00:11:50.760
4 2010-07-23 17:11:50.760
5 2010-07-25 17:11:50.760
6 2010-07-26 17:11:50.760
7 2010-07-23 16:11:50.760
8 2010-07-28 17:11:50.760
9 2010-07-29 17:11:50.760
10 2010-07-29 15:11:50.760

i.e. we are using the DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) condition to round the DATETIME value to DATE.

try this for rounding values:

SELECT GETDATE()
SELECT DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)
SELECT DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0)
SELECT DATEADD(YYYY, DATEDIFF(YYYY, 0, GETDATE()), 0)
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

I hope this article helps you!!!..
Thank you. :)

My First Post.....

Hi everyone!!!... This is my first post. I thought many times to create a blog to share my experiences. May be i was busy or lazy, but i couldn't start until today. Hopefully with full energy i started my blog MSTECHSOLUTIONS2010. I will update my blog with new item with simple to complex problems with solutions.