NULLIF()

NULLIF() returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned.  Seems a little odd and not very useful, but it is a great way of ensuring that empty strings are always returned as NULLS.  

For example, the expression:

nullif(@variable1,'')

will never return an empty string, it will return either a NULL value or a string with at least one character present.  Also,  SQL ignores trailing spaces when comparing strings, so even if the string isn't empty but it contains all spaces, it will still return NULL.

select nullif('     ','') 
 
----
NULL

NULLIF() can be a very useful function to employ.  Consider it when you need to replace default values other than just NULL when using ISNULL() or COALESCE() expressions.

http://msdn.microsoft.com/en-us/library/aa276840(v=sql.80).aspx

Avoid empty reports in a reporting services data driven subscription

        Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data.  Data-driven subscriptions are intended for the following kinds of scenarios:

 Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example, distribute a monthly report to all employees.

Distributing reports to a specific group of recipients based on predefined criteria. For example, send a sales performance report to the top ten sales managers in an organization.

This is an excellent feature except that there is no clean way to stop empty emails from being sent, as in the case when your query returns an empty dataset. 

There are many hacks that you can try

First, When configuring a data driven subscription, you must provide a query which returns subscriber data. Most of the time this query simply returns rows from a table which lists your data driven subscription users and their preferences around delivery and parameter values for the report in question. Each row of data returned equals one report we'll deliver as part of the subscription. Just modify this query so that it also filters the result based on whether or not the report itself will return rows. For example:

SELECT * from SubscriptionTable

WHERE EXISTS(SELECT Field1 FROM DataSourceTable WHERE Field2 Between DateAdd(dd,-2,GetDate()) and GetDate())

If you provide this query to the wizard, it will only return subscribers for whom when there is data you wish to report on. (records in the DataSourceTable table that have a date within the last 2 days)

        In Server Management Studio under the list of jobs, you will find the subscription you created with its GUID.  The first step in the job is an EXEC command that will run the SSRS subscription. Edit this job and add a step ahead of the SSRS step.  This step does a SELECT 1/(SELECT COUNT(*) FROM MyDataSet).  If this step fails (because there is no data in the dataset the job exits reporting success, and if it succeeds, the SSRS subscription is run.

Use a RAISEERROR statement in your SQL script or procedure. In the case of an error the report will not be rendered.

IF NOT EXISTS ( SELECT * FROM MyDataTable)

RAISEERROR('no records found....)

ELSE

SELECT * FROM MyDataTable

SSRS - Custom code embedded in your report

In Reporting Services you will often need  to manipulate or dynamically format the  report data .  The built in  expressions  can do a lot of these, but most often you might want to format data or calculate values for which there are no expression readily available. This is where the built in custom code comes.

In one of my reports I had to calculate the Next month, having the current month and year as my input parameters.

You write your custom code in the report properties code window. Go to the report layout Select                                         Report ->  Report Properties  -> Code

        Public Function getNextDate(ByVal month As String, ByVal year As String) As String

Try

Dim dateNow As DateTime

dateNow   =month+ "-01-" + year

Dim nextMonth As DateTime

nextMonth  = dateNow  .AddMonths(1)

Return nextMonth

Catch ex As exception

Return ex.message

End Try

End Function

Now in the textbox where you want the next month displayed call the custom code 

="Next Month is  "+ code. getNextDate (Parameters!Month.Value,Parameters!Year.Value)

ISNULL()

The ISNULL  function in SQL Server is used to substitute alternate value if the one being checked is “NULL”. 
Take the following example.

DECLARE @var1 VARCHAR(50) 
DECLARE @var2 VARCHAR(5) 

SET @var1 = 'Hello'
SET @var2= NULL

SELECT ISNULL(@var2,@var1)

Your output is Hello

Now try substituting @var1 with a bigger sentence

SET @var1 = 'Hello World Its great to be here'

Now your output is just Hello


This is because the ISNULL funnction type casts var2 to var1
Try the following, it will resolve the issue

SELECT ISNULL(CONVERT(VARCHAR(50), @var2),@var1)

http://msdn.microsoft.com/en-us/library/aa933210(v=sql.80).aspx

Multiple lines in a text box

Sometimes we might need to present data in a text box in multiple lines. The newline character used for this purpose is VbCrLf

ex
="Hello" & VbCrLf & "World"

will be
Hello
World