technoblab

NULLIF()

2012-02-08T12:35:00.000-05:00

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

For example, the expression:

nullif(@variable1,'')

will never return an empty string, it will returneither a NULL value or a string with at least one character present. Also, SQL ignores trailing spaces whencomparing strings, so even if the string isn't empty but it contains allspaces, 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 justNULL 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

2012-02-04T19:01:00.000-05:00

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

2009-05-14T18:48:00.000-04:00

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()

2009-04-07T18:17:00.000-04:00

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

error: 26 - Error Locating Server/Instance Specified

2008-03-14T19:11:00.000-04:00

"Anetwork-related or instance-specific error occurred while establishing aconnection 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 toallow remote connections. "

Programmers often get this error message when connecting to a SQL Server namedinstance. Every time a client connects to the SQL Servernamed 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 nameetc.

Without these information, theclient does not know how to connect to the server and it fails with the aboveerror message.

Make a checklist of the following

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

Check to see the instance name iscorrect and and running
Check the server DNS (ping theserver).

2. Check to ensure your database engine is configured to acceptremote connections.

Goto Start -> All Programs -> SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
Clickon Surface Area Configuration for Services and Connections
Selectthe named instance -> DatabaseEngine -> Remote Connections
Click Enable local and remote connections
Restartthe server

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

4.If firewall is enabled on the server make anexception for the SQL Server instanceand port

Goto Start -> Run -> Firewall.cpl
Clickon exceptions tab
Addthe sqlservr.exe and the default port (1433)

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

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

Dropping all tables in the current database

2008-02-09T07:56:00.000-05:00

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

2007-10-18T03:41:00.000-04:00

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

2007-10-12T05:33:00.001-04:00

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 StringsFormatting 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

2007-09-25T08:23:00.000-04:00

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:

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

2007-09-20T02:55:00.000-04:00

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

2007-08-31T01:01:00.000-04:00

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.