Crystal Reports users have the ability to save reports they created as *.rpt files, just as we do when we build them inside of Visual Studio. The Crystal Reports Developer API allows you to instantiate a ReportDocument and then call its Load method to pull in a saved rpt file. Therefore, it is possible to load a report created with the end-user product into a custom .NET application with the following code.

 Dim rptDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
 rptDoc.Load(“C:\Reports\Report1.rpt”)

The CrystalReportViewer, the control which is embedded into a Windows Form has a ReportSource property which is used at design time to view instantiated reports. By setting the ReportSource property to the newly created ReportDocument, it is then possible to display the user-built report in your custom application.

CrystalReportViewer1.ReportSource = rptDoc

When the report is created and saved, by default, the data is saved along with the report. This may be useful for preserving the state of your data; however, it is more likely that later viewers of the report will want to see current data.

Therefore, the last key part of building this solution is allowing the report to display live data whenever it is viewed. This is done by re-establishing the report’s connection to the database and then refreshing the report.  Rather than using a standard connection string, you will need to pass the key elements of a connection string (server name or IP, database name, integrated security setting) to a SetConnection method within the report’s object model. While you will need to supply the server name (or IP address), the database name and Integrated Security option are stored as metadata in the saved report. The following code shows how to extract that information from the above report and use it to re-connect to the database. Once the connection has been made, call the ReportDocument’s Refresh method to populate the report with the current data.

     With rptDoc.DataSourceConnections(0)
        Dim dbname As String = .DatabaseName
        Dim integrated As Boolean = .IntegratedSecurity
        .SetConnection(“MyServer”, dbname, integrated)
     End With
     rptDoc.Refresh()

The Crystal Viewer utility is made up of two Windows Forms. The first form, shown in Figure 1, lists available reports in a ListBox for the user to select from while the second (ReportViewer) hosts the CrystalViewer control to display the selected report.

Figure 1 - The Viewer Utility displays available reports in a specific path. The user can hide the two settings if they wish.

When the user selects an item from the list, the utility loads the chosen file using the Load method shown above, then establishes the connection to the database as described above using the value stored in the Database Server field. The ReportDocument is then passed to the ReportViewer form which attaches the report to the CrystalViewer using the DataSource property. Lastly, the main form calls the ReportViewer form’s Show method. If parameters are required, Crystal Reports automatically opens its own form for collecting the necessary values and then the report is displayed.  Parameters are discussed in more detail later in this article.

Figure 2 - If the report has parameters, Crystal Reports' "Enter Parameter Values" form will be displayed.

The utility will need to know where to look for saved reports and the name of the database server. These are stored using .NET’s ApplicationSettings and the end-user has the ability to update those values using the settings fields at the bottom of the form.  ApplicationSettings can be created in the Settings page of the project Properties as shown in Figure 3, and then accessed in code. If a user modifies these settings, they will be persisted in the application.

Figure 3 - The Viewer Utility has two application settings, ReportLocation and Server, which can be defined in the Project Properties window.

When the form loads, the ListBox is populated using the following method:

  Private Sub FillReportList()
    Try
      Dim FileList As New ArrayList
      Dim files() As String = Directory.GetFiles(My.Settings.ReportLocation)
      Dim f As FileInfo
      For Each filepath As String In files
        f = New FileInfo(filepath)
        If f.Extension = ".rpt" Then
          FileList.Add(f.Name.Replace(".rpt", ""))
        End If
      Next
      ListBox1.DataSource = FileList
      btnRunReport.Enabled = (FileList.Count > 0)
    Catch ex As DirectoryNotFoundException
      MessageBox.Show("Report Locations folder is invalid. " & _
      "Please edit in Settings.")
    Catch ex As Exception
      MessageBox.Show("Report List cannot be loaded.")
    End Try
   End Sub

The ellipses button to the right of the Report Location on the form opens a FolderBrowserDialog to assist the user in selecting a folder path. Drag a FolderBrowserDialog component onto the form to use this functionality. The following code opens up the FolderBrowserDialog then stores the resulting path to the user setting.  If the user changes the path, then the ListBox is repopulated and the setting is remembered the next time the user runs the application.

    With FolderBrowserDialog1
      .SelectedPath = ReportLocation.Text
      .ShowDialog()
      If .SelectedPath <> ReportLocation.Text Then
        My.Settings.ReportLocation = .SelectedPath
        ReportLocation.Text = .SelectedPath
        FillReportList()
      End If
    End With

The Database Server field is a textbox which the users can type in. The user setting for the database is modified in the same manner as the ReportLocation setting. A more advanced version of the Crystal Viewer utility leverages the System.Data.Sql.SqlDataSourceEnumerator class to assist the end-user in selecting an available SQL Server.

When the user selects a report and clicks the Run Report button, the following code is executed to load the report, set the connection and run the report.

  Private Sub btnRunReport_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles btnRunReport.Click
    rptDoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument
    rptDoc.Load(My.Settings.ReportLocation & "\" & _
    ListBox1.SelectedValue.ToString.Trim & ".rpt")
    Try
      With rptDoc.DataSourceConnections(0)
        Dim dbname As String = .DatabaseName
        Dim integrated As Boolean = .IntegratedSecurity
        .SetConnection(My.Settings.Server, dbname, integrated)
      End With
      rptDoc.Refresh()
      Dim frm As New ReportViewer
      frm.SetReport(rptDoc)
      frm.Show()
    Catch ex As CrystalDecisions.CrystalReports.Engine.DataSourceException
      MessageBox.Show("There was a datasource error loading " & _
      "this report. Please check the Server setting.")
    Catch ex As Exception
      MessageBox.Show("There was an error loading this report. " & _
      "The error is coming from the report, not the datasource. " & _
      "Please check with the creator of the report you are trying to view.")
    End Try
  End Sub

The ReportViewer form has a SetReport method which is called in the above code before returning to call the Show method of the ReportViewer form.

Public Sub SetReport(ByVal rptDoc As _
CrystalDecisions.CrystalReports.Engine.ReportDocument)
  CrystalReportViewer1.ReportSource = rptDoc
End Sub

Figure 4 displays a form which was generated at run time using a parameter value entered by the end-user.

Figure 4 - This report was dynamically created using a parameter entered at run-time.

Many reports require parameters, for example, if they are populated with a stored procedure that takes parameters. The ParameterFields property of a loaded report will enable you to discover and set values for the parameters necessary to run the report.

As mentioned previously, by default, a report is saved with its original data in place. When the user re-opens the report, it will show that saved data. Refreshing the report (by clicking the CrystalViewer’s Refresh icon or in code), will force another query of the database and the Crystal Report “Enter Parameter Values” window will open if necessary.  For each parameter that the report requires, the window will display the parameter name, the PromptingText value that was saved with the report , a textbox to enter the value (or a drop down list if there are default values to choose from) and a checkbox for using a Null value for the parameter. If a report collects multiple parameters, the end-user will see one page per parameter with Next and Back buttons, as shown in Figure 5, until they reach the final parameter which then prompts them to Finish.

Figure 5 - If there are more than one parameter required, a separate page will be displayed for each parameter.

If you do not wish the user to see the saved data when first opening the report, you can refresh the report programmatically using the Refresh method. This will cause the Enter Parameter Values screen to appear before the report.

A more advanced approach would be to iterate through the report’s ParameterField collection and dynamically add labels and input controls to single form which can be presented to the end user, rather than using the default parameter form that the report displays. While building a dynamic form is out of scope for this particular article, the code below shows what to do with the values after they have been collected.

Once the user has entered the values in the dynamically built form, store the values in an ArrayList. The ArrayList works well in this scenario because it is able to store a variety of types.  This is important because the parameters will expect properly typed data (Strings, Booleans, dates, etc.) The following code then takes an ArrayList and sets each item in the ArrayList to the value of each ParameterField in the report. The code assumes that you have proper types as well as the correct number of parameters in your ArrayList and that they are in the same order as the parameters. All of these details are available from the ReportDocument object and should be accounted for when building the dynamic form.

The code creates a DiscreteValue whose Value property is set to the value from the ArrayList, then adds this DiscreteValue object to a ParameterValues collection. Then the ParameterValues collection is applied to the particular parameter. The reason the parameter takes a collection of values rather than a single value is because it is possible to have a selection of appropriate values for the end-user to choose from.

  Private Sub UpdateReportParameters(ByVal params As ArrayList)

    Dim discreteVal As New CrystalDecisions.Shared.ParameterDiscreteValue

    Dim currVals As New CrystalDecisions.Shared.ParameterValues

    Dim paramField As _

     CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition

    For iParams As Integer = 0 To _

    rptDoc.DataDefinition.ParameterFields.Count - 1

      paramField = rptDoc.DataDefinition.ParameterFields(iParams)

      discreteVal.Value = params(iParams)

      If discreteVal.Value IsNot Nothing Then

        currVals.Add(discreteVal)

        paramField.ApplyCurrentValues(currVals)

      End If

    Next

  End Sub

If you are setting the parameters using this method, be sure to remove the ReportDocument.Refresh code, otherwise it will set the report’s metadata back to the original values and your new parameter values will be lost.

With a few more tweaks to make your forms look and feel the way your users expect, you can now distribute this utility and allow end users to easily view currently populated reports that were created in the Crystal Reports application or even that you created in the Crystal Report Designer in .NET.