Click here to Skip to main content
Click here to Skip to main content

Microsoft Reporting Services - Part II

, 28 Jul 2009
Rate this:
Please Sign up or sign in to vote.
This article shows some tips to improve Microsoft Reports like embedded reports, showing stored images, and images from a path, using custom code and custom assemblies, exporting reports, and printing reports without the ReportViewer control.

Introduction

In my previous article about Microsoft Reports, I showed the basic steps of creating a report, defining the data source at runtime, working with parameters, including images, using the expression editor, and how to feed data to a subreport and charts. I also demonstrated some easy ways to customize the ReportViewer.

In this article, I will try to show you some medium/advanced tips, some useful features, and some code, that you can use to improve your reports.

Here’s the list:

Embedded Reports

Microsoft Reports are XML files, with a different extension (*.rdlc), that includes the report definition, images, custom code, etc. Because they are easy to edit (you can use an XML Editor or even Notepad to do that), they can be changed, changing the end result. This has some advantages and some disadvantages.

You can look inside the XML and check for any problems, or you can leave the reports outside the application so the .exe size will not be increased. On the other hand, you don’t ensure the security and protection of the report. These are just a few things that the developer needs to consider.

rpt7.png

By default, when you add a new report to your solution, the Build Action is set to Embedded Resource. If you have used a ReportViewer and you want to choose what report to load at runtime, you can define the report using:

Me.ReportViewer1.LocalReport.ReportEmbeddedResource = "WindowsApplication1.Report1.rdlc"

If you choose to have an external report that is not embedded in your application, you need to change the Build Action property to None, and define the report path using:

Me.ReportViewer1.LocalReport.ReportPath = "..\..\Report1.rdlc"

Showing Stored Images and Images from a Path

Storing images in the database, normally called BLOBs (binary large object) is a usual process when you work with data. There are a few advantages/disadvantages of storing images in the DB or just the path/URL: for one, you can store an image very easily in SQL Server and use it later; however, you cannot say the same for other database engines like Microsoft Access, especially if you want to use it later, because Access stores the image as an Object.

The goal of this article is not to enumerate the advantages and disadvantages of the two methods, but only to demonstrate how to show an image in a report, from both of them.

If the image is stored in the DB, you just have to insert an Image control, from the Toolbox, in the Table.

rpt1.png

Then, in the properties of the control, define the Value as the respective field from the DataTable (don’t forget to define the dataset for the report first). Then, define the Source property as Database and the right MIMEType property. In this example I have chosen Image/png.

If only the path/URL to the image is stored, then you can do the same procedure as above, but you will need to define the Value property with: ="File://" & <field name>. You also need to define the Source as External.

rpt2.png

Finally, either hard coding or using the Properties window of the ReportViewer, configure the LocalReport to allow external images by setting the EnableExternalImages property to True.

Me.ReportViewer1.LocalReport.EnableExternalImages = True

Using Custom Code

Microsoft Reports already have several built-in functions that allow you to customize the information in the report. Using the Expression Editor, you can see the functions in the Common Functions category, and use it to format, modify, etc., the output of the information.

You can also create your own functions and expand the limitations.

By going to the Report properties (menu Report – Report Properties), and choosing the Code tab, you can define your custom code in the available textbox:

rpt3.png

This example will check the country code and define different formats.

Here’s the code from the image above:

Function currencyFormat(ByVal value As Double, _
         ByVal countryCode As String) As String 
    Select Case countryCode 
        Case "PT" 
            Return String.Format("{0:n} €", value) 
        Case "US" 
            Return String.Format("$ {0:n}", value) 
        Case Else 
            Return String.Format("{0:n}", value) 
    End Select 
End Function

If you edit the report file (*.rdlc), you can see that the custom code defined is between the tags <Code> ... </Code>.

rpt4.png

To use this custom code, you can use the Expression Editor, or type directly in the Table cell, Matrix cell, Textbox, etc.

rpt5.png

Here, define “=Code.<function name>” to indicate that you’re using custom code. In this case, for the example, it will indicate a custom code for the first parameter the price, and for the second, the country code.

=Code.currencyFormat(Fields!price.Value,Fields!countryCode.Value)

As you can see, the intellisense for this custom code is not available, since the report is not compiled, and it cannot verify the created code. You just have to ensure that the function and parameters are correctly defined.

The end result for this demonstration report, is a list with different formats for different country codes.

rpt6.png

Custom Assemblies

As you can see from the previous section, you can add custom code to Microsoft Reports. This is very helpful if you need to customize something. But, what if you need to do this in several reports? Well, you could use custom assemblies!

To use custom assemblies in your report, you just need to create a Class Library project that will generate a *.dll, and use this file for all the reports you need.

After you create the file, you need to copy that *.dll into your debug\release folder of your application and to the PrivateAssemblies folder (in Visual Studio 2008, it’s normally C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\).

For this example, I have created this small class, which will cut some text if the length is equal or greater than 30.

Public Class rptClass

    Public Function CutText(ByVal txt As String) As String
        If txt.Length >= 30 Then
            Return txt.Substring(0, 26) & " ..."
        Else
            Return txt
        End If
    End Function


    Public Shared Function SharedCutText(ByVal txt As String) As String
        If txt.Length >= 30 Then
            Return txt.Substring(0, 26) & " ..."
        Else
            Return txt
        End If
    End Function

End Class

After this, you need to add a reference in the report to that assembly. Open the report (*.rdlc), and in the menu Report, select the Report Properties option. In the References tab, select the created *.dll.

rpt8.jpg

Note: The assembly name description will be used later in the code.

In this window, you have two grids:

  • References: If you use Shared methods and you will call it directly.
  • Classes: If you don’t use Shared methods, and you do it this way, you need to create a new instance of your class (must be written directly).

After this, you can use it this way in your report:

rpt9.jpg

Note that if you create a new instance of your class, you must define as =Code.<class>.<method name>.

Finally, in the code, you need to indicate that your custom assembly is Trusted Code, using:

Me.ReportViewer1.LocalReport.AddTrustedCodeModuleInCurrentAppDomain( _       
  "ClassLibrary1, Version=1.0.0.1, Culture=neutral, PublicKeyToken=null")

Exporting Reports

Exporting reports can be very useful and allows you to save the report to a file or use it for some automation. The ReportViewer control already has some built-in options to export reports. Although, it is even more useful to do this without the ReportViewer, because this way, you can automate some processes, like sending the file as an email attachment.

When it is used in local mode (LocalReport), there are some different formats that can be used to export: Microsoft Excel, PDF, or Image.

To export a report, you just have to Render() the LocalReport to an array of bytes, and then use a FileStream to write to the end file.

Here’s an example:

Public Class ReportUtils 

    Enum rptFormat 
        Excel 
        PDF 
        Image 
    End Enum 

   '''<summary>
   ''' Exports a LocalReport 
   '''</summary> 
   '''<param name="report">LocalReport</param> 
   '''<param name="output">Format</param> 
   '''<param name="filePath">File path</param> 
   '''<remarks></remarks> 
   Public Sub Export(ByVal report As LocalReport, _
          ByVal output As rptFormat, ByVal filePath As String) 

        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 = report.Render(output.ToString, Nothing, _ 
                              mimeType, encoding, extension, streamids, warnings) 

        Using fs As New IO.FileStream(filePath, IO.FileMode.Create) 
            fs.Write(bytes, 0, bytes.Length) 
            fs.Close() 
        End Using 
        bytes = Nothing 

    End Sub 

End Class

Then, you can use the above class like this, opening the file in the end of the process:

Dim rpt As New LocalReport 
rpt.ReportPath = Application.StartupPath & "\..\..\rptProducts.rdlc" 

' --------------------------------------------------------- 
' Define report DataSource, Parameters, etc.
' --------------------------------------------------------- 

Dim fileName As String = "c:\test.pdf" 

Dim clsReport As New ReportUtils 
clsReport.Export(rpt, ReportUtils.rptFormat.PDF, fileName) 

Process.Start(fileName)

Printing Reports Without the ReportViewer

Microsoft Reports are normally associated with the ReportViewer, and normally, when one is used the other is used as well. It’s true that this control has a lot of nice features like preview, export, print, etc., but it’s not always necessary to preview the report before printing it!

The LocalReport doesn’t have a built-in option that allows you to print the report directly, without the ReportViewer. To print the report, you need to Render() the report to a Stream, and then, using a PrintDocument(), draw the Stream for each correct page, from the list of Streams, when the PrintDocument is printing.

Here is a class that can do this:

Imports System.IO 
Imports System.Data 
Imports System.Text 
Imports System.Drawing.Imaging 
Imports System.Drawing.Printing 
Imports System.Collections.Generic 
Imports Microsoft.Reporting.WinForms 


Public Class ReportUtils 
    Implements IDisposable 

    Private currentPageIndex As Integer 
    Private tmpFileName As String = String.Empty 
    Private streamList As List(Of Stream) 

    Enum Orientation 
        Landscape 
        Portrait 
    End Enum 

    ''' <summary> 
    ''' Add the Stream to the list 
    ''' </summary> 
    Private Function CreateStream(ByVal name As String, _ 
                                  ByVal fileNameExtension As String, _ 
                                  ByVal encoding As Encoding, _ 
                                  ByVal mimeType As String, _ 
                                  ByVal willSeek As Boolean) As Stream 

        tmpFileName = My.Computer.FileSystem.GetTempFileName() 

        Dim s As New FileStream(tmpFileName, FileMode.Create) 
        streamList.Add(s) 
        Return s 

    End Function 


    ''' <summary> 
    ''' Exports the file to the list of Streams 
    ''' </summary> 
    Private Sub ExportToStream(ByVal report As LocalReport, _ 
                              ByVal Orientation AsOrientation) 

        Dim deviceInfo As New StringBuilder 
        With deviceInfo  
            .Append("<deviceinfo>") 
            .Append(" <outputformat>EMF</outputformat>") 

            If Orientation = ReportUtils.Orientation.Portrait Then 
                .Append(" <pagewidth>8.5in</pagewidth>") 
                .Append(" <pageheight>11.5in</pageheight>") 
            Else 
                .Append(" <pagewidth>11.5in</pagewidth>") 
                .Append(" <pageheight>8.5in</pageheight>") 
            End If 

            .Append(" <margintop>0.3in</margintop>") 
            .Append(" <marginleft>0.3in</marginleft>") 
            .Append(" <marginright>0.3in</marginright>") 
            .Append(" <marginbottom>0.3in</marginbottom>") 
            .Append("</deviceinfo>") 
        End With  

       Dim warnings() As Warning = Nothing 
       report.Render("Image", deviceInfo.ToString, _ 
                        AddressOf CreateStream, warnings) 

        For Each s As Stream In streamList 
            s.Position = 0 
        Next 
       deviceInfo = Nothing 

    End Sub 


   '''<summary> 
   ''' When the PrintDocument is printing, draw the right page from the list 
   '''</summary> 
   Private Sub PrintPage(ByVal sender As Object, _ 
                                                  ByVal ev As PrintPageEventArgs) 

        Using pageImage As New Metafile(streamList(currentPageIndex)) 
            currentPageIndex += 1 

            ev.Graphics.DrawImage(pageImage, ev.PageBounds) 
            ev.HasMorePages = (currentPageIndex < streamList.Count) 
        End Using 

    End Sub 


   '''<summary> 
   ''' Prints the report without preview 
   '''</summary> 
   '''<param name="report">Report Name </param> 
   Public Sub Print(ByVal report As LocalReport, ByVal Orientation As Orientation) 

        streamList = New List(Of Stream) 

        ' Exports the file to a list of Streams 
       Call ExportToStream(report, Orientation) 

        If streamList IsNot Nothing AndAlso streamList.Count > 0 Then 

           ' Start the printing process 
           Using printDoc As New PrintDocument() 

                If Not printDoc.PrinterSettings.IsValid Then 
                    Dim msg As String= "Printer is not available or is not valid!" 
                   Throw New ArgumentException(msg) 
                End If 

                AddHandler printDoc.PrintPage, AddressOf PrintPage 
                If Orientation = ReportUtils.Orientation.Portrait Then 
                   printDoc.DefaultPageSettings.Landscape = False 
                Else 
                   printDoc.DefaultPageSettings.Landscape = True 
                End If 

               printDoc.Print() 
            End Using 

        End If 

    End Sub 

   '''<summary> 
   ''' Prints the report without preview
   '''</summary> 
   '''<param name="report">Report Name</param> 
   Public Sub Print(ByVal report As LocalReport) 
        Print(report, Orientation.Portrait) 
    End Sub 


    Public Overloads Sub Dispose() Implements IDisposable.Dispose 
        Try 

            If streamList IsNot Nothing Then 
                For Each s As Stream In streamList 
                    s.Close() 
                Next 
               streamList.Clear() 
                streamList = Nothing 
            End If  

            If tmpFileName <> String.Empty AndAlso _ 
                              IO.File.Exists(tmpFileName) Then 
               IO.File.Delete(tmpFileName)  
            End If 
            tmpFileName = String.Empty 

        Catch ex As Exception : End Try 
    End Sub 

End Class

Then, you can use it this way:

Imports Microsoft.Reporting.WinForms 

Public Class frmMain 

   '''<summary> 
   ''' Prints the report 
   '''</summary> 
   Private Sub btnPrint_Click(ByVal sender As System.Object, _ 
                              ByVal e As System.EventArgs) _
                              Handles btnPrint.Click 

        Try 

            Dim rpt As New LocalReport 
            rpt.ReportPath = Application.StartupPath & "\..\..\rptProducts.rdlc" 

           ' --------------------------------------------------------- 
            ' Define report DataSource, Parameters, etc
            ' --------------------------------------------------------- 

            Using cls As New ReportUtils 
                cls.Print(rpt) 
            End Using 

        Catch ex As Exception 
            MessageBox.Show(ex.Message, My.Application.Info.Title, _  
                                    MessageBoxButtons.OK, MessageBoxIcon.Error) 
        End Try 

    End Sub 

End Class

Thanks to...

Thanks to Gary Lima (aka VB Rocks), Microsoft Visual Basic MVP, for all the support and help!

Conclusion

And that’s all for now!

I hope that this article helps you to improve your reports, and to realize that Microsoft Reports is a great reporting tool.

History

  • 28 July, 2009: Initial post.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

jpaulino
Software Developer
Portugal Portugal
Jorge Paulino
Microsoft Visual Basic MVP
Portuguese Software Developer
VB.NET, ASP.NET, VBA, SQL
http://vbtuga.blogspot.com/

http://twitter.com/vbtuga

Comments and Discussions

 
Questionxml error when trying to print through code PinmemberJon Boucher18-Nov-13 9:32 
QuestionError from Code PinmemberMember 99605429-Apr-13 7:22 
AnswerRe: Error from Code Pinmemberjpaulino11-Apr-13 12:01 
GeneralRe: Error from Code PinmemberMember 996054213-May-13 8:52 
GeneralRe: Error from Code PinmemberMember 996054214-May-13 1:51 
Suggestionexternal images Pinmembervb_Lover26-Jan-13 17:43 
QuestionError While Exporting the report to pdf Pinmembermadhukars6-Sep-12 7:08 
Hi
While Exporting the locally created report i am getting a error as follows
{"An error occurred during local report processing."}
 
Please let me know
 
Madhukar
 
madhukar@live.com
QuestionPrinter is not available or is not valid! Error PinmemberMember 883689616-Apr-12 18:46 
GeneralThanks a Lot Pinmemberaaa200917-Mar-12 7:20 
GeneralLink for Part I PinmemberVenkataSirish27-May-11 3:08 
GeneralRe: Link for Part I Pinmemberjpaulino27-May-11 3:11 
QuestionStored image = PDF PinmemberGert Hauan28-Dec-09 22:43 
GeneralGrrrreat! PinmemberMark Pryce-Maher27-Aug-09 0:48 
GeneralRe: Grrrreat! Pinmemberjpaulino27-Aug-09 0:52 
GeneralThank you very much. Pinmemberjohziegler12-Aug-09 23:19 
GeneralRe: Thank you very much. Pinmemberjpaulino14-Aug-09 9:37 
GeneralNice article! PinmemberElizabeth Connolly4-Aug-09 5:03 
GeneralRe: Nice article! Pinmemberjpaulino4-Aug-09 11:46 
GeneralRe: Nice article! Pinmemberricardo_c323-Jul-12 11:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 29 Jul 2009
Article Copyright 2009 by jpaulino
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid