Microsoft Reporting Services - Part II

• Download source code - 1.88 KB

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
• Showing Stored Images and Images from a Path
• Using Custom Code
• Using Custom Assemblies
• Exporting Reports
• Printing Reports Without the ReportViewer

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.

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.

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.

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:

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

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

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.

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.

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:

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.