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

Creating a table from your view

     In one of my projects had to create a table and populate it with my view.  You can do this as :

SELECT *
INTO dbo.tbl_tblname
FROM dbo.vw_viewname

Find the port SQL Server is running

 By default the TCP Port for SQL Server is 1433 , and the USD connection is 1434 . If you have a named instance the TCP port is configured dynamically .

You can find out this information by going to the registry and looking up TCP settings.

SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<InstanceNumber>\MSSQLServer\
SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<InstanceName>\MSSQLServer\
SuperSocketNetLib\TCP\

An alternate easier way would be the SQL Server Configeration Manager UI

Go to Windows > Start > SQL Server Program Folder > SQL Server Configuration Manager > SQL Server Network Configurations >  Protocols for your SQL Server > TCP/IP and right click the link.

SQL function to split a multivalue parameter

       In reports with multivalue parameters, the input parameter can be passed as a list to the sql server procedure where we can use a UDF to split it.
For ex if we a have a procedure to return employee names for multiple employee ids then the proc will look something like this

CREATE PROC [dbo].[proc_EmpName_getAll]
@EmpIDKey VARCHAR(1000)
AS
SELECT   EmpIDKey, EmpName FROM dbEmployee
      WHERE EmpIDKey IN (SELECT Item FROM  dbo.Split(@EmpIDKey, ',') AS Split_1)
ORDER BY EmpName

  The  sql function below takes a user defined multi dimensional object and a delimitter character as input parameters and breaks them into individual items. 

CREATE FUNCTION [dbo].[Split]/* This function is used to split up multi-value parameters */(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(50) collate database_default )
AS
BEGIN

     DECLARE @tempItemList NVARCHAR(4000)
     SET @tempItemList = @ItemList

     DECLARE @i INT
     DECLARE @Item NVARCHAR(4000)

     SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
     SET @i = CHARINDEX(@delimiter, @tempItemList)

     WHILE (LEN(@tempItemList) > 0)
     BEGIN
          IF @i = 0
            SET @Item = @tempItemList
         ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
         INSERT INTO @IDTable(Item) VALUES(@Item)

         IF @i = 0
             SET @tempItemList = ''
         ELSE
             SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
             SET @i = CHARINDEX(@delimiter, @tempItemList)
         END
   RETURN
END

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

Conditional Background color for Text box

1.  Background color based on the value of the input parameter
 
        =iif(Fields!TextBox1.Value = Parameters!ParameterName.Label,"LightGrey","DarkGrey")  1.  Background color based on the value of the input parameter

2.   Background color based on a condition

       =iif(Fields!TextBox1.Value > 10,"LightGrey","DarkGrey")

3.   Background color on alternating row

       =iif(RunningValue(Fields!FieldName1.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,"LightGrey","DarkGrey")

 

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

error: 26 - Error Locating Server/Instance Specified

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. "

           Programmers often get this error message when connecting to a SQL Server named instance.  Every time a client connects to the SQL Server named instance, an SSRP UDP packet is sent to the server machine UDP port 1434. This is done to know the configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. 

Without these information, the client does not know how to connect to the server and it fails with the above error message.

Make a checklist of the following

1. Check your server name is correct (typo).

•  Check to see the instance name is correct and and running
•   Check the server DNS (ping the server).

2. Check to ensure  your database engine is configured to accept remote connections.

•  Go to Start  -> All Programs ->  SQL Server 2005 ->  Configuration Tools ->  SQL Server  Surface  Area Configuration     
•  Click on Surface Area Configuration for Services and Connections
•  Select the named instance   -> Database Engine  ->  Remote Connections
•  Click  Enable local and remote connections
•   Restart the server

3.Check the Connection string. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings     (machineName\Named Instance)

4.If firewall is enabled on the server make an exception  for the SQL Server instance and port

•   Go to   Start  -> Run -> Firewall.cpl
•   Click on exceptions tab
•   Add the sqlservr.exe  and the default  port (1433)

5. Check the SQL Browser service is running on the server, if firewall is on make an exception for the SQL Browser.exe   and/or UDP port 1434 .

For more information on A detailed post of How to Troubleshoot Connecting to the SQL Server Database Engine

Dropping all tables in the current database

WHILE EXISTS(SELECT [name] FROM sys.tables WHERE [type] = 'U')
BEGIN
DECLARE @table_name varchar(50)
DECLARE table_cursor CURSOR FOR SELECT [name] FROM sys.tables WHERE [type] = 'U'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC ('DROP TABLE [' + @table_name + ']')
PRINT 'Dropped Table ' + @table_name
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
END

Database fields in Report header / footer

A database field can neither be placed in a report header nor in the footer. This will throw an error "The Value expression for the textbox <fieldname> refers to a field. Fields cannot be used in page headers or footers".

There is however a workaround. Place a hidden textbox in the report body call it tb_hidden and assign to this the database field that you need in the header. Place a textbox in the report header and assign the hidden textbox's value to it
ex: =reportitems!tb_hidden.Value

This however as a drawback, the value will appear only in the first page. If your report runs into many pages then you need to take a different approach.

Pass the value to appear in the header as a parameter from your .aspx page call it database_field. Assign this to the textbox in the header.
ex: =Parameters!database_field.Value

Formatting Text in Reports

I often find requests for help in formatting strings. Just remember you can use your r vb formatting syntax here be it just text, date or numbers. The following are some samples in formatting Strings Formatting a telephone no:

If the database field is numeric then


 =Format(12345678,"(###)##-###") 
 or 
=Format(Fields!phonenumber.value,"(###)##-###")


 If the field is of datatype String then you need to convert it   =Format(Cdbl("12345678"),"(###)##-###"))

Formatting Date and Time

Examples of applying a format string to date/time values are shown in the following table.

 Format                         Output
 Format(Now,"D")        Friday, October 12, 2007
 Format(Now,"d")         10/12/2007
 Format(Now,"T")         6:37:23 AM  
 Format(Now,"t")          6:37 AM
 Format(Now,"F")         Friday, October 12, 2007 6:37:23 AM
 Format(Now,"f")          Friday, October 12, 2007 6:37 AM
 Format(Now,"g")         10/12/2007 6:37 AM
 Format(Now,"m")        October 12
 Format(Now,"y")         October, 2007

Customize the reportviewer toolbar

Customize the ReportViewer ToolBar
You can customize the existing toolbar, however it is not possible to extend it.

You can hide the default reportviewer toolbar using the code

ReportViewer1.ShowToolBar = false

Customize your Toolbar
You can write your own Find Button as:

Dim found As Integer = Me.ReportViewer1.Find(txtFind.Text, 1)
If found > 0 Then
btnFindNext.Enabled = True
Else
MessageBox.Show("String not found", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
btnFindNext.Enabled = False
End If
Return True

Your own export to pdf button:
refer my earlier article "render rdlc as pdf"

Your own export to excel button:
Follow the steps as mentioned in my earlier article "render rdlc as pdf"
and replace the code in Samples.aspx with the one given below :

Protected Sub Page_SaveStateComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.SaveStateComplete
Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

Dim FolderLocation As String = "D:\SampleProjects\"
'First delete existing file
Dim filepath As String = FolderLocation & "Employee.xls"
File.Delete(filepath)
'Then create new excel file
bytes = ReportViewer1.LocalReport.Render("Excel", Nothing, mimeType, encoding, extension, streamids, warnings)
Dim fs As New FileStream(FolderLocation & "Employee.xls", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()
'Set the appropriate ContentType.
Response.ContentType = "application/vnd.ms-excel"
'Write the file directly to the HTTP output stream.
Response.WriteFile(filepath)
Response.End()
End Sub
End Class

To customize the reportviewer Messages
You can implement the IReportViewerMessages interface to provide custom localization of the ReportViewer control user interface. This implementation can be passed to the ReportViewer control by adding a custom application setting to the the web.config file using the key “ReportViewerMessages”.

For example:

<appsettings>
<add value="MyClass, MyAssembly" key="ReportViewerMessages">
</appsettings>

The following code is an example of a class that implements the IReportViewerMessages interface.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.Reporting.WebForms

Namespace MySample
Public Class MyReportViewerCustomMessages
Implements Microsoft.Reporting.WebForms.IReportViewerMessages
#Region "IReportViewerMessages Members"

Public ReadOnly Property BackButtonToolTip() As String
Get
Return ("Add your custom text here.")
End Get
End Property

Public ReadOnly Property ChangeCredentialsText() As String
Get
Return ("Add your custom text here.")
End Get
End Property

Public ReadOnly Property ChangeCredentialsToolTip() As String
Get
Return ("Add your custom text here.")
End Get
End Property

Public ReadOnly Property CurrentPageTextBoxToolTip() As String
Get
Return ("Add your custom text here.")
End Get
End Property
#End Region
End Class
End Namespace

Note: All messages are not implemented here. If you do not specify custom messages then the default is taken.

Adding Images to your report

Adding Images to your Report

There are 3 ways in which you can add images to your report
1. Embeded
2. External
3. Database

Embeding Images

Open your report. Drag and drop an image icon.
Click on the image and press F4, the properties window pops up
Under the group data you'll find source choose embeded.
To embed images to your report choose report property on the menu (Click on the report if you can't see this),
Click on Report Images and choose the images you want to embed.
Now, In the value property of your image choose the image you want to display.

Embedded images are ok for small logo files, but for huge bmp files external images work better.

External Images

Set the source as external and set the value for external files as the virtual path to the image folder. ex: http://servername/imagefoldername/imagename

Also enable external images in your aspx page

ReportViewer1.LocalReport.EnableExternalImages = True

Database Images

Set the source as Database and set the value for external files as the fieldname
In the textbox write the foll expression.
=Convert.ToBase64String(Fields!Image.Value)

Placing a database image in your header
Since database fields are not accessible from the header, place a hidden textbox in your report call it tb_images and set the value, as mentioned above for database images. Now place an image icon in the header and set its value to the hidden image textbox i.e
=ReportItems!tb_Image.Value

Dynamically Change Image
In certain case you may have to dynamicaaly place a header image based on the department.
The code can be placed in a code window. Select Report -> Report Properties -> Code (tab) in the VS.NET designer.

Function ShowHeaderImage(value as Object) As String

Dim strURL as String = "http:///images/"
Dim strImg as String
Select Case value
Case Nothing
strImg = "heading1.jpg"
Case 1
strImg = "heading2.jpg"
Case 2
strImg = "heading3.jpg"
End Select
Return strImg
End Function

Place an image icon in the report body and set its source as external. In the value property
enter the following
=Code.ShowHeaderImage(Fields!.Value)

render rdlc as pdf

This article shows how to generate reports using the ASP.NET 2.0 Reportviewer server control using LocalReports with parameterized table adapters. I am using ASP.NET 2.0, Visual Studio 2005, and SQL Server 2005

The difference between Local Reports and Server Reports is that in Server Reports the client makes a report request to the server. The server generates the report and then sends it to the client. While this is more secure, it lowers performance due to transfer time from server to client.
In LocalReports, reports are generated at the client end and does not connect to the "SQL Server Reporting Services Server" .
Using the AdventureWorks database, this example will get the parameters for the table adapters as a queryString from the requesting aspx page.

You can download AdventureWorks database from

http://www.microsoft.com/downloads/thankyou.aspx?familyId=487c9c23-2356-436e-94a8-2bfb66f0abdc&displayLang=en

1. Open a new Website call it SampleReport.

2. Right click on solution in the Solution explorer. Go to Add new item and choose DataSet. Call it dsReport.xsd.

3. Open the dataset . Right click and choose Add -> TableAdapter. Open AdventureWorks DataBase and choose table Employee. Select all fields and append the "where" clause. (ex where employeeid = @employeeid)

4. Right click on solution in the Solution explorer. Go to Add new item and choose Report. Call it Report.rdlc

5. Design the report to display the EmployeeDetails. Choose Data -> show data sources from the Report Menu. Drag and drop the fields to be displayed on your designer.

6. Add new aspx page call it SampleReport.aspx. Drag and drop a ReportViewer from the toolbar . Now bind the rdlc to the reportviewer by bringing up the smart tag of the ReportViewer control and selecting "Report.rdlc" in the "Choose Report" dropdown list.

7. Now Select "Choose Data Sources" and choose an ObjectDataSource.


8. Configure the ODS to take the input parameter as a querystring. (Need to pass the parameter as a queryString in order to render as pdf)

9. Give the queryString name the same as you would use in your default.aspx page.

10. Open your default.aspx page and call the SampleReport.aspx. Add the following codeSnippet.. Note this is only an example. The default.aspx page can have a data grid listing all emplyee details. The user can click on an id and pass multiple queryString values to your SampleReport.aspx which in turn can have multiple datasets and/or table adapters.

Imports System.IO
Imports Microsoft.Reporting.WebForms
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Response.Redirect("SampleReport.aspx?employeeid=1")
End Sub
End Class

11. Add the foll code in your SampleReport.aspx.

Imports System.IO
Imports System.data
Imports Microsoft.Reporting.WebForms
Partial Class SampleReport
Inherits System.Web.UI.Page
Protected Sub Page_SaveStateComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.SaveStateComplete
Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

Dim FolderLocation As String = "D:\SampleProjects\"
'First delete existing file
Dim filepath As String = FolderLocation & "Employee.PDF"
File.Delete(filepath)
'Then create new pdf file
bytes = ReportViewer1.LocalReport.Render("PDF", Nothing, mimeType, encoding, extension, streamids, warnings)
Dim fs As New FileStream(FolderLocation & "Employee.PDF", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()
'Set the appropriate ContentType.
Response.ContentType = "Application/pdf"
'Write the file directly to the HTTP output stream.
Response.WriteFile(filepath)
Response.End()
End Sub
End Class

12. Build and Run your WebSite.