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.