Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize

Tutorial: Creating an Ad Hoc Report Application with Reporting Services

SQL Server 2000
 

Kathrine Lord
Microsoft Corporation

November 2004

Applies to:
   Microsoft .NET framework
   Microsoft SQL Server 2000
   Microsoft Visual Studio.NET 2003
   SQL Server 2000 Reporting Services

Summary: New to .NET programming or Reporting Services? Get step-by-step instruction on creating a .NET application to create and publish reports using the Reporting Services API. (43 printed pages)

Contents

Prerequisites
Create the Solution in VS.NET 2003
Add the Error_Page Form to the Project
Add the RDLGenerator Form to the Project
Add the ManagingReports Form to the Project
Conclusion

Prerequisites

This project requires Microsoft IIS 6.0, Microsoft Visual Studio.NET 2003, Microsoft SQL Server 2000, and SQL Server 2000 Reporting Services to be installed on the developer machine. The edition of SQL Server 2000 and Reporting Services should be identical. For example, if you are using SQL Server 2000 Developer Edition, you will also need Reporting Services Developer Edition.

The SQL Server data sources used for reports do not have to be local nor are they required to be the same edition. The user will need to be able to access remote data sources in order to use them. The Reporting Services databases (ReportServer and ReportServer TempDB) must be local. All SQL Servers referenced either as data sources or to be used by Reporting Services must have either Windows Authentication or Mixed Mode authentication enabled.

Create the Solution in VS.NET 2003

  1. Open Visual Studio .NET 2003 and click New Project on the Start Page.
  2. Choose Visual Basic Projects, ASP.NET Web Application.
  3. Name the project CreateReport and click OK.

    Aa902634.adhocrepsr_figure1(en-us,SQL.80).gif

    Figure 1

  4. Right-click References and select Add Reference from the quick menu.
  5. From the Add Reference modal window click Browse.
  6. Browse to the location of the Report Manager bin folder. In a default installation this is at c:\program files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\Bin.
  7. Select the ReportingServicesWebUserInterface.dll.
  8. On the .NET tab of the Add Reference modal window, scroll down until you find System.Web.Services.dll, and click on it.

    Aa902634.adhocrepsr_figure2(en-us,SQL.80).gif

    Figure 2

  9. Click the Select button to add it to the Selected Components listbox.
  10. Click OK to add these assemblies as References.

Next, take care of the security settings—very important:

  1. Add the following identity tag to the Web.Config file under the <System.Web> opening tag
      <identity impersonate="true" />
    
  2. Using the Internet Information Services manager from the Administrative tools set the CreateReport web security to Windows authentication.
  3. Using the Report Manager interface, enable the My Reports functionality. To do so, go to http://localhost/reports or the name you provided when you installed Reporting Services. Go to Site Settings and check the Enable My Reports... option as shown in the image below.

    Aa902634.adhocrepsr_figure3(en-us,SQL.80).gif

    Figure 3

Add the ReportWizard Form to the Project

  1. Add a new web form (Project, Add Web Form) and name it ReportWizard.aspx.
  2. Right-click ReportWizard.aspx and select Set As Start Page.
  3. Open ReportWizard.aspx in the designer and select the HTML view.

    Just below the opening <body> tag paste the following script (Edit, Paste As HTML), which is used to open the ManageReports.aspx you will create later.

    <script language="javascript">
       function OpenNewWin()
       {
       window.open("ManageReports.aspx", "ManageReports")
       }
    </script>
    
  4. Inside the opening and closing <form></form> tags, paste the following (I'll walk through each of these items later):
    <asp:label id="Label4" style="Z-INDEX: 103; LEFT: 32px; POSITION: 
    absolute; TOP: 616px" runat="server" Height="24px" Width="265px" 
    Visible="False">Choose the output format for your report</asp:label>
    <asp:button id="btnStartOver" style="Z-INDEX: 109; LEFT: 592px; 
    POSITION: absolute; TOP: 616px" runat="server" Text="Start 
    Over"></asp:button>
    <asp:dropdownlist id="formatDropDown" style="Z-INDEX: 102; LEFT: 296px; 
    POSITION: absolute; TOP: 616px" runat="server" Width="199px" 
    Visible="False">
    <asp:ListItem Value="CSV">CSV</asp:ListItem>
    <asp:ListItem Value="EXCEL">EXCEL</asp:ListItem>
    <asp:ListItem Value="HTMLOWC">HTMLOWC</asp:ListItem>
    <asp:ListItem Value="MHTML">MHTML</asp:ListItem>
    <asp:ListItem Value="PDF" Selected="True">PDF</asp:ListItem>
    <asp:ListItem Value="IMAGE">TIFF</asp:ListItem>
    </asp:dropdownlist>
    <asp:button id="Submit" style="Z-INDEX: 101; LEFT: 520px; POSITION: absolute; TOP: 616px" runat="server" Text="Submit"></asp:button>
    <asp:panel id="Panel1" style="Z-INDEX: 104; LEFT: 32px; POSITION: 
    absolute; TOP: 56px" runat="server" Height="82px" Width="784px" BorderColor="Transparent">
    <H1 style="COLOR: navy">Step 1 - Select a Server:</H1>
    <asp:dropdownlist id="ServerList" runat="server" Width="272px" AutoPostBack="True">
    <asp:ListItem Value="Select a SQL Server...">Select a SQL Server...</asp:ListItem>
    <asp:ListItem Value="(local)">Local Server</asp:ListItem>
    <asp:ListItem Value="(local)">Other Server</asp:ListItem>
    </asp:dropdownlist>
    <asp:RequiredFieldValidator id="ServerValidator" runat="server" ErrorMessage="Please select the server where your database resides"
    ControlToValidate="ServerList" InitialValue="Select a SQL Server..." Display="Dynamic"></asp:RequiredFieldValidator>
    </asp:panel>
    <asp:panel id="Panel2" style="Z-INDEX: 105; LEFT: 32px; POSITION: 
    absolute; TOP: 144px" runat="server" Height="80px" Width="784px" 
    Visible="False">
    <H1 style="COLOR: navy">Step 2 - Select a Database:</H1>
    <asp:dropdownlist id=DatabaseList runat="server" Width="272px" 
    AutoPostBack="True" DataValueField="name" DataTextField="name" 
    DataSource="<%# MasterDS1 %>">
    </asp:dropdownlist>
    <asp:RequiredFieldValidator id="DatabaseListValidator" runat="server" ErrorMessage="Please select the database"
    ControlToValidate="DatabaseList" InitialValue="Make a Selection..." Display="Dynamic"></asp:RequiredFieldValidator>
    </asp:panel>
    <asp:panel id="Panel3" style="Z-INDEX: 106; LEFT: 32px; POSITION: absolute; TOP: 224px" runat="server" Height="78px" Width="790px">
    <H1 style="COLOR: navy">Step 3 - Select a Table:</H1>
    <H1 style="COLOR: navy"></H1>
    <asp:dropdownlist id=TableList runat="server" Width="400px" 
    AutoPostBack="True" DataValueField="name" DataTextField="name" 
    DataSource="<%# TablesDS1 %>">
    </asp:dropdownlist>
    <asp:RequiredFieldValidator id="TableListValidator" runat="server" ErrorMessage="Please select a table" ControlToValidate="TableList"
    InitialValue="Make a Selection..." Display="Dynamic"></asp:RequiredFieldValidator>
    <asp:panel id="Panel4" runat="server" Width="782px" Height="248px">
    <H1 style="COLOR: navy">Step 4 - Select the columns:</H1>
    <P dir="ltr" style="MARGIN-RIGHT: 0px"><FONT color="navy">Selected Fields:</FONT>
    <FONT color="navy">Available Fields:</FONT></P>
    <P dir="ltr" style="MARGIN-RIGHT: 0px">&nbsp;
    <asp:listbox id="FieldList" runat="server" Width="240px" Height="118px" SelectionMode="Multiple"></asp:listbox>&nbsp;
    <asp:Button id="Button2" runat="server" Text=">>>" CausesValidation="False"></asp:Button>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button id="Button1" runat="server" Text="<<<" CausesValidation="False"></asp:Button>&nbsp;
    <asp:listbox id="selFieldList" runat="server" Width="240px" Height="120px" SelectionMode="Multiple"></asp:listbox></P>
    <P>
    <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ErrorMessage="You must select at least 1 column"
    ControlToValidate="FieldList" Display="Dynamic"></asp:RequiredFieldValidator></P>
    </asp:panel>
    </asp:panel>
    <asp:textbox id="txtStep" style="Z-INDEX: 107; LEFT: 688px; POSITION: 
    absolute; TOP: 616px" runat="server" Width="32px" 
    Visible="False">1</asp:textbox>
    <asp:label id="Label1" style="Z-INDEX: 108; LEFT: 32px; POSITION: 
    absolute; TOP: 8px" runat="server" Width="544px" BorderColor="White" 
    Font-Italic="True" BackColor="Transparent" Font-Size="22pt" Font-
    Bold="True" ForeColor="ForestGreen">Welcome to the Report Generation 
    Wizard!</asp:label>
    <asp:textbox id="txtTitle" style="Z-INDEX: 110; LEFT: 32px; POSITION: 
    absolute; TOP: 584px" runat="server" Width="552px">Enter Report Title 
    Here...</asp:textbox>
    <input style="Z-INDEX: 111; LEFT: 640px; WIDTH: 136px; POSITION: 
    absolute; TOP: 16px; HEIGHT: 24px" onclick="OpenNewWin()" type="button" 
    value="Manage My Reports...">
    <asp:RequiredFieldValidator id="RequiredFieldValidator5" style="Z-
    INDEX: 112; LEFT: 592px; POSITION: absolute; TOP: 584px" runat="server" 
    Width="208px" ErrorMessage="Please enter a valid report title" 
    ControlToValidate="txtTitle" InitialValue="Enter Report Title Here..." 
    Display="Dynamic"></asp:RequiredFieldValidator>
    
  5. Select the Design view and you should see this:

    Aa902634.adhocrepsr_figure4(en-us,SQL.80).gif

    Figure 4

Understanding the Controls on the ReportWizard Form

The purpose of this page is simply to generate a server name, database, field list, and if the user wants, a title for the report. This is merely meant to be a sample of one method you could use to create these items. I used this method because it is easy to understand; you could choose other ways to come up with these items. For example, you could just give the option of selecting certain descriptions that are tied to views to the user, and leave naming the server and database behind the scenes. You could also use stored procedures (though you'd have to make changes in the GenerateRDL() method to accommodate them). Your .NET developer expertise, an understanding of the Report Definition Language, and the MSSRS web service are all that are required.

The ServerList DropDownList

  1. Right-click the ServerList control and select Properties.
  2. Click on the ellipses next to (Collection) in Items.
  3. The items are as shown below, with both the Local Server and Other Server having the same value of (local). You need to change these values to suit your environment.

    The assumption in this sample is that the databases are all local, but the only requirement is that the ReportingServer database be local. The data sources can be on any SQL Server instance that the developer has authorization to use.

    The ServerList values are used to retrieve a list of databases from the master databases on the specified servers. This example uses integrated security, so the value of these list items must either be the literal name of the SQL Server instance, or (local). It cannot be localhost.

    Aa902634.adhocrepsr_figure5(en-us,SQL.80).gif

    Figure 5

The DatabaseList DropDownList

This control is used to select the database to query in order to provide the values for the TableList control. This list is filled dynamically by querying the sysdatabases table in the master database of the SQL Server instance selected in the ServerList control.

The TableList DropDownList

This control is used to select the table or views that are used to populate the FieldList control. This list is filled dynamically by querying the sysobjects table for the tables and views in the database selected in the DatabaseList control.

The FieldList and SelFieldList DropDownLists

Aa902634.adhocrepsr_figure6(en-us,SQL.80).gif

Figure 6

The FieldList control is populated dynamically with fields from the table or view indicated by the TableList control. You use this control to select the fields to be used for the SQL query. You must select at least one field for the query.

The txtTitle, formatDropDown, Submit, Start Over, and txtStep Controls

Aa902634.adhocrepsr_figure7(en-us,SQL.80).gif

Figure 7

  1. The txtTitle text box is used to allow a user-specified report title to be entered.
  2. The formatDropDown drop-down list allows the user to select the format in which to render the report. The options are PDF (the default), Excel, HTMLOWC (HTML with Office Web Components), TIF (image), CSV, and MHTML (web archive).
  3. The Submit button validates the form data and submits it to the RDLGenerator page. This page does the actual report generation, and will be added to the project later in this tutorial.
  4. The Start Over button deletes the form data and re-starts the wizard at the beginning.
  5. The txtStep text box tracks the step the user is on in the wizard. This field is not visible in the user interface.
There are validations and labels on this page that are not documented. This document is intended for developers who should already be familiar with these types of objects. For additional information on validations and labels use the Visual Studio .NET Help menu.

Add the Data Objects to the ReportWizard Page

Now we will need to create the data adapters, connection, and datasets used by the application.

Add the Data Adapter and Connection to Fill the Database List

  1. Using the Data tab in the Toolbox, select the SQLDataAdapter. Drag and drop it onto the designer.
  2. This starts the Data Adapter Configuration Wizard.

    Aa902634.adhocrepsr_figure8(en-us,SQL.80).gif

    Figure 8

  3. Click Next.
  4. Click on the New Connection button.

    Aa902634.adhocrepsr_figure9(en-us,SQL.80).gif

    Figure 9

  5. Enter (local) or the name of the server you are using as your data source for the report.
  6. Select Use Windows NT Integrated Security.
  7. Select the Master database and click the Test Connection button.
  8. If the test connection succeeds, click OK.

    Aa902634.adhocrepsr_figure10(en-us,SQL.80).gif

    Figure 10

  9. Click Next to proceed to the next step.

    Accept the default Use SQL statements on the Choose a Query Type screen and click Next.

    Aa902634.adhocrepsr_figure11(en-us,SQL.80).gif

    Figure 11

  10. Copy and paste the query below into the Generate the SQL Statements text area.
    SELECT name FROM sysdatabases WHERE (name NOT IN ('Master', 'tempdb', 'msdb', 'Model'))
    

    SQL Illustration 1

    Aa902634.adhocrepsr_figure12(en-us,SQL.80).gif

    Figure 12

  11. Click on the Advanced Options... button.
  12. Deselect the Generate Insert, Update and Delete statements checkbox (these statements are not required) and click OK.

    Aa902634.adhocrepsr_figure13(en-us,SQL.80).gif

    Figure 13

  13. Click Next and then Finish.

    Aa902634.adhocrepsr_figure14(en-us,SQL.80).gif

    Figure 14

  14. Using the Properties dialog, set the name of the SqlDataAdapter1 to MasterSQLAdapter and the SqlConnection1 to MasterSqlConnection1. If you do not rename these objects, you'll have to change the code that you paste later to the names you have for them in your project.

Generate the Dataset

  1. Right-click on the MasterSQLAdapter and choose Generate Dataset.
  2. Name the dataset MasterDS.

Add the Data Adapter to Fill the Tables List

  1. Select SQLDataAdapter from the data tab on the Tools menu to create the TablesDA.
  2. Click Cancel when the Data Adapter Configuration Wizard begins. You will not be using the wizard to complete the properties.
  3. Select the new Data Adapter and click on the Properties view.
  4. Set the following:

    Name=TablesDA1

    Connection=MasterSQLConnection1 (or the name you specified above)

    CommandText= Select name from sysobjects where (xtype IN ('u', 'v')) AND (Status >= 0) order by name

    DeleteCommand=(none)

    InsertCommand=(none)

    UpdateCommand=(none)

    Aa902634.adhocrepsr_figure15(en-us,SQL.80).gif

    Figure 15

Create the Tables Dataset

  1. Right-click on the TablesAdapter1 and select Generate Dataset.
  2. Select New, name it TablesDS and click OK.

    Aa902634.adhocrepsr_figure16(en-us,SQL.80).gif

    Figure 16

Add the Data Adapter to Fill the Fields List

  1. Repeat the steps to create a data adapter without using the wizard as explained in the Add the Data Adapter to Fill the Tables List section.
  2. Set the following:

    Name=GenerateFieldsDA

    CommandText=Select * From sysobjects

    Connection=MasterSqlConnection1

    DeleteCommand=(none)

    InsertCommand=(none)

    UpdateCommand=(none)

    Do not generate a dataset for this adapter.

    Aa902634.adhocrepsr_figure17(en-us,SQL.80).gif

    Figure 17

Add the Code to the ReportWizard Form

  1. Right-click on ReportWizard.aspx page and select View Code.
  2. Copy and paste the code and comments in Code Illustration 1 above the
              Public Class ReportWizard 
    

    to set the Imports statements.

    '=====================================================================
    '  Copyright (C) Microsoft Corporation.  All rights reserved.
    '
    ' This source code is intended only as a supplement to Microsoft
    ' Development Tools and/or on-line documentation.  See these other
    ' materials for detailed information regarding Microsoft code samples.
    '
    ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
    ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
    ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    ' PARTICULAR PURPOSE.
    '=====================================================================*/
    
    Imports System.IO
    Imports System.Collections
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.Xml
    Imports System.Web.Services
    Imports System.Web.UI
    Imports System.Web.UI.Page
    Imports Microsoft.SqlServer.ReportingServices
    

    Code Illustration 1

  3. Paste the code in Code Illustration 2 to declare variables just below the Web Form Designer Generated Code section.
        Private m_connection As SqlConnection
        Private m_connectString As String
        Private m_commandText As String
        Private m_fields As ArrayList
        Private m_ReportName As String
        Private dbms As String = "initial catalog = master"
        Private SQLServer As String = "data source = localhost;"
        Private ConnectionString As String = "integrated security=SSPI;persist security info=False;"
     
    

    Code Illustration 2

  4. Replace the existing comments within the Private Sub Page_Load statement with the code in Code Illustration 3:

    This code is setting environment visibility and focus for the objects on the ReportWizard.aspx page based on what step is listed in the txtStep field covered later.

            ' Track the steps and set visibility/focus
            Select Case txtStep.Text
                Case 1
                    txtStep.Text = 2
                    Me.FindControl("ServerList")
                    Panel2.Visible = False
                    Panel3.Visible = False
                    Panel4.Visible = False
                    selFieldList.Visible = False
                    txtTitle.Visible = False
                    Submit.Visible = False
                Case 2
                    txtStep.Text = 3
                    Panel2.Visible = True
                    Panel3.Visible = False
                    Panel4.Visible = False
                    selFieldList.Visible = False
                    txtTitle.Visible = False
                    Submit.Visible = False
                    Me.FindControl("DatabaseList")
                Case 3
                    txtStep.Text = 4
                    Panel3.Visible = True
                    Panel4.Visible = False
                    selFieldList.Visible = False
                    txtTitle.Visible = False
                    Submit.Visible = False
                    Me.FindControl("SqlChoiceRadioList")
                Case 4
                    txtStep.Text = 5
                    Me.FindControl("TableDirectRadio")
                Case Else
                    Me.FindControl("txtTitle")
            End Select
    End Sub
    

    Code Illustration 3

  5. Next, following Page_load, copy and paste the code from Code Illustration 4 to capture change events in the ServerList.

    Lines 1 and 2 are dynamically changing the connection string that will be used in the connection.

    Line 3 is clearing the DatabaseList in the event there were existing items.

    Within a structured try-catch exception handler, lines 4–7 open the database, fill the dataset, bind the dataset and then inserts a generic list item.

    The MasterSqlConnection1 is closed within the Finally of the try-catch statement on Line 8.

        Private Sub ServerList_SelectedIndexChanged( _
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ServerList.SelectedIndexChanged
            ' Set the data source from ServerList value 
      'Line1 
            SQLServer = "data source = " & ServerList.SelectedValue & ";"
            'Line2 
            MasterSqlConnection1.ConnectionString = ConnectionString & SQLServer & dbms
            'Line3 
            DatabaseList.Items.Clear()
    
            Try
                'Line4     
                MasterSqlConnection1.Open() ' Open connection
                'Line5     
                MasterSQLAdapter.Fill(MasterDS1) 'Fill dataset Master DBMS
                'Line6     
                DatabaseList.DataBind() ' Bind the data set
                'Line7     
                DatabaseList.Items.Insert(0, "Make a Selection...") ' Add generic list item
            Catch ex As Exception
                ExceptionHandler(ex) ' Generic error handler to be added a little later
            Finally
                'Line8            
                MasterSqlConnection1.Close() ' Close connection        
    End Try
        End Sub
    

    Code Illustration4

  6. Copy the code in Code Illustration 5 for the DatabaseList_SelectedIndexChanged. The code is essentially the same as that above with a few additional visibility settings.
        Private Sub DatabaseList_SelectedIndexChanged( _
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles DatabaseList.SelectedIndexChanged
            ' Set visibility and list items
    
            TableList.Items.Clear()
            FieldList.Visible = False
            selFieldList.Visible = False
            dbms = "initial catalog =" & DatabaseList.SelectedValue
            SQLServer = " data source = " & ServerList.SelectedValue & ";"
            Try
                MasterSqlConnection1.ConnectionString = ConnectionString & SQLServer & dbms
                MasterSqlConnection1.Open() 'Open the connection
                TablesAdapter1.Fill(TablesDS1) 'Fill the tables dataset
                TableList.DataBind() 'bind the dataset to the TableList drop down
                TableList.Items.Insert(0, "Make a selection...") 'add a generic list item
                TableList.Visible = True
            Catch ex As Exception
                ExceptionHandler(ex)
            Finally
                MasterSqlConnection1.Close() 'close the connection
            End Try
        End Sub
    

    Code Illustration 5

  7. Copy and paste the following code to enable the capture of TableList selection changes.

    This code prepares a SQL statement and passes it to the GenerateFields() method for processing. It then fills the selFieldList with the array values in m_fields.

        Private Sub TableList_SelectedIndexChanged( _
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles TableList.SelectedIndexChanged
            Try
                ' Set initial state of objects
                FieldList.Items.Clear()
                selFieldList.Items.Clear()
                FieldList.Visible = True
                selFieldList.Visible = True
    
                ' Prepare command and connections
                dbms = "Initial Catalog = " & DatabaseList.SelectedValue
                SQLServer = "data source = " & ServerList.SelectedValue & ";"
                m_commandText = "Select * From [" & TableList.SelectedValue & "]"
                MasterSqlConnection1.ConnectionString = ConnectionString & SQLServer & dbms
                MasterSqlConnection1.Open()
                GenerateFieldsList(m_commandText) 'Pass the command text to generate field list
    
                Dim fieldName As String
                For Each fieldName In m_fields
                    selFieldList.Items.Add(fieldName) 'Fill selFieldList drop down list
                Next fieldName
    
                'set visibility of objects
                txtTitle.Visible = True
                Label4.Visible = True
                formatDropDown.Visible = True
                Submit.Visible = True
                Panel4.Visible = True
            Catch ex As Exception
                ExceptionHandler(ex)
            Finally
                MasterSqlConnection1.Close() 'close the connection
            End Try
        End Sub
    

    Code Illustration 6

  8. Copy and paste the GenerateFieldsList code in Code Illustration 7 just above the End Class statement.

    This code is from the Reporting Services Books Online. It creates a SQL Command and SQLReader. The command is set to the query text that is passed in from Code Illustration 6. The reader is executed to get schema only, since we are just filling a list with field names and do not need any data.

    Public Sub GenerateFieldsList(ByVal m_NewQuery As String)
            'create field list and type arraylists
            Dim command As SqlCommand
            Dim reader As SqlDataReader
    
            ' Executing a query to retrieve a fields list for the report
            command = MasterSqlConnection1.CreateCommand()
            command.CommandText = m_NewQuery
    
            ' Execute and create a reader for the current command
            reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
    
            ' For each field in the resultset, add the name to an array list
            m_fields = New ArrayList
            Dim i As Integer
            For i = 0 To reader.FieldCount - 1
                m_fields.Add(reader.GetName(i))
            Next i
            reader.Close()
        End Sub 'GenerateFieldsList
        
    

    Code Illustration 7

  9. Code Illustrations 8a and 8b are for the two buttons that move field names between the selFieldList and FieldList list boxes. Copy and paste these code samples below the End Sub statement of the GenerateFieldsList method.

    Modifying list box collections while enumerating through them causes an enumeration error (mscorlib). In the try-catch error handler, the enumeration is reset to the beginning of the collection and then moved to the first item to clear the error.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim li As New ListItem
            Try 'Move available fields to chosen fields list
                For Each li In selFieldList.Items
                    If li.Selected Then
                        FieldList.Items.Add(li.Text)
                        FieldList.Items.Item(FieldList.Items.Count - 1).Selected = True
                        selFieldList.Items.Remove(li.Text)
                    End If
                Next li
            Catch ex As Exception
                ' Modifying the field lists causes enumeration to be out of synch
                ' Try to reset enumeration
                Try
                    FieldList.Items.GetEnumerator.Reset()
                    FieldList.Items.GetEnumerator.MoveNext()
                Catch exception As Exception
                    ' ignore further enumeration errors
                End Try
            End Try
        End Sub
    

    Code Illustration 8a

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim li As ListItem
            Try ' User changes mind and removes fields from chosen field list
                For Each li In FieldList.Items
                    If li.Selected Then
                        selFieldList.Items.Add(li.Text)
                        FieldList.Items.Remove(li.Text)
                    End If
                Next li
            Catch ex As Exception
                ' Modifying the field lists causes enumeration to be out of synch
                ' Try to reset enumeration
                Try
                    FieldList.Items.GetEnumerator.Reset()
                    FieldList.Items.GetEnumerator.MoveNext()
                Catch exception As Exception
                    ' ignore further enumeration errors
                End Try
            Finally
                ' Ensure all items in chosen field list are selected so user doesn't
                ' have to reselect all after removing a field
                Dim i As Integer
                For i = 0 To FieldList.Items.Count() - 1
                    FieldList.Items.Item(i).Selected = True
                Next
            End Try
        End Sub
    

    Code Illustration 8b

  10. Copy and paste the code samples from Code Illustrations 9, 10 and 11 below the Button2_Click sub shown above.

    Code Illustration 9 transfers the form values to the RDLGenerator.aspx page. Code Illustration 10 re-starts the Report Wizard, clearing all existing form values. Code Illustration 11 redirects the user to the error_page.aspx page in the event of an error.

    Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
            ' pass values to RDLGenerator page
            Server.Transfer("RDLGenerator.aspx", True)
        End Sub
    

    Code Illustration 9

        Private Sub btnStartOver_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartOver.Click
            ' start over without retaining form values
            Server.Transfer("ReportWizard.aspx", False)
        End Sub
    

    Code Illustration 10

    Private Sub ExceptionHandler(ByVal exception As Exception)
            'generic exception handler
    Response.Redirect("Error_page.aspx?errsource=" & exception.Source & "&errmessage=Please contact the help desk for assistance.")
    
        End Sub
    

    Code Illustration 11

  11. Close ReportWizard.aspx, saving all changes.

Add the Error_Page Form to the Project

This page handles errors with friendly messages. You can use whatever error handling method you are comfortable with.

  1. Select Add New Item from the File menu.

    Aa902634.adhocrepsr_figure18(en-us,SQL.80).gif

    Figure 18

  2. Select Web Form and name it Error_Page.aspx.
  3. From the page properties, select View Code and add Code Illustration 12 to the Page Load sub.

    This code uses Response.Write to display either the errsource or errmessage from the query string. If there is nothing in the query string, it displays a generic error message.

        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            If Request.QueryString("errsource") = "" And Request.QueryString("errmessage") = "" Then
                Response.Write("<CENTER><H2 style='COLOR: Red'>AN ERROR HAS OCCURRED</H2> ")
                Response.Write("<HR style='COLOR: Red'>Please restart the application. If it continues, contact your help desk.<BR>")
                Response.Write("<A href='/CreateReport/'>Create a New Report</A></CENTER>")
            Else
                Response.Write("<CENTER><H2 style='COLOR: Red'>" & Request.QueryString("errsource") & "<H2>")
                Response.Write("<HR style='COLOR: Red'>" & Request.QueryString("errmessage"))
                Response.Write("<P>Contact your help desk for assistance.</P></CENTER>")
            End If
        End Sub
    

    Code Illustration 12

  4. Close Error_Page.aspx and save all changes.

    You can specify an error page in the web.config file and also in the @ Page declarations. For more information search for @ Page declarations in the VS.NET help index.

Add the RDLGenerator Form to the Project

RDLGenerator is the page that does the actual report generation, using the Reporting Services API.

  1. Add a new web form as before, naming it RDLGenerator.aspx.
  2. Switch to Code View and add the code sample from Code Illustration 13 above Public Class RDLGenerator to set Imports statements.
    '=====================================================================
    '  Copyright (C) Microsoft Corporation.  All rights reserved.
    '
    ' This source code is intended only as a supplement to Microsoft
    ' Development Tools and/or on-line documentation.  See these other
    ' materials for detailed information regarding Microsoft code samples.
    '
    ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
    ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
    ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    ' PARTICULAR PURPOSE.
    '=====================================================================*/
    Imports System.Web
    Imports System.Text
    Imports System.Text.RegularExpressions
    Imports System.IO
    Imports System.Data.SqlClient
    Imports System.Xml
    Imports Microsoft.ReportingServices
    Imports Microsoft.SqlServer.ReportingServices
    

    Code Illustration 13

  3. Add the variables in Code Illustration 14 just above the Private Sub Page_Load statement to declare variables you'll be using:
        Private m_query As String ' Query to be used for report
        Private dsName As String ' Name of the data source
        Private m_Fields As ArrayList ' Arraylist of fields
        Private m_Type As ArrayList ' Arraylist of field types
        Private m_ReportName As String ' Report name for referencing
        Private definition As [Byte]() = Nothing ' Report definition used to create report
        Private warnings As Warning() = Nothing ' Warning container to catch warnings
        Private parentFolder = "My Reports" ' Path used to publish reports to..user must have rights to create content
        Private parentPath As String = "/" + parentFolder ' Path from report manager URL to parentFolder
        Private filePath As String = "c:\" ' Used to store RDL files
        Private RS As New ReportingService ' Reporting Service
        Private m_ReportTitle As String ' Title text box for report
        Private FieldList As String
        Private TableName As String
        Private OrderList As String
        Private DatabaseName As String
        Private ServerName As String 'Used to store server name
        Private m_ConnStr As String ' Used for Data Source Definition
    

    Code Illustration 14

  4. Replace the Page_Load sub with Code Illustration 15. See comments inline for explanations.
        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            ' Get form data from ReportWizard.aspx
            ServerName = Request.Form("ServerList")
            DatabaseName = Request.Form("DatabaseList")
            TableName = Request.Form("TableList")
            FieldList = Request.Form("FieldList")
            m_ReportTitle = Request.Form("txtTitle")
            
      Dim format As String = Request.Form("formatDropDown")
    
            ' Set the strReportName and remove illegal characters
            Dim strReportName As String
            If m_ReportTitle = "" Then
                strReportName = Replace(TableName, " ", "")
            Else
                strReportName = Regex.Replace(m_ReportTitle, "[^\w\@-]", "")
            End If
    
            'Create rendering path adding the output format
            Dim myPath As String = http://localhost/reportserver?/ & _
     + parentFolder + "/" & strReportName & _
    + "&rs:Command=render&rs:Format=" + format
    
            'Remove spaces from database name if they exist
            If Not DatabaseName = "" Then
                dsName = Replace(DatabaseName, " ", "") & "Ds"
            Else
                dsName = "MyReportsDs"
            End If
    
            'Create report path for the RDL file
            m_ReportName = "c:\" & strReportName & ".rdl"
    
            'Change connection string to use appropriate server and database
            m_ConnStr = "Data Source =" & ServerName & ";Initial Catalog =" & DatabaseName
            conn.ConnectionString = "Data Source =" & ServerName & ";Initial Catalog =" & DatabaseName
            Try
                Dim x As Integer
                Dim nField() As String = Split(FieldList, ",")
    
                'Create the Order By field list replacing any empty strings
                OrderList = Replace(FieldList, " ", "")
    
                'Add brackets to field list for SQL statement-any spaces will cause error            
    For x = 0 To nField.Length - 1
                    nField(x) = "[" & nField(x) & "] As " & Replace(nField(x), " ", "")
                Next
                ' Join the arraylist to create string
                FieldList = Join(nField, ",")
    
            Catch ex As Exception
                HandleException(ex)
            End Try
    
            'Generate SQL statement adding the FieldList; TableName and OrderList
            m_query = "Select " & FieldList & " from " & "[" & TableName & "] Order By " & OrderList
    
            Try
                'Generate the field list
                GenerateFieldsList(m_query)
                'Generate report
                GenerateRdl(m_ReportName)
                'Publish report
                BeginPublishReport(strReportName)
                'Open report in selected format
                Response.Redirect(myPath)
    
            Catch ex As Exception
                HandleException(ex)
            End Try
        End Sub
    

    Code Illustration 15

  5. Add the code in Code Illustration 16 just above the End Class statement.

    This is essentially the same GenerateFieldsList as used on ReportWizard.aspx, with the addition of an array to contain the field types which are used by the GenerateRDL() method for formatting.

    Public Sub GenerateFieldsList(ByVal m_NewQuery As String)
            'create field list and type arraylists
            Dim command As SqlCommand
            Dim reader As SqlDataReader
            Try
    
                ' Executing a query to retrieve a fields list for the report
                command = conn.CreateCommand()
                command.CommandText = m_NewQuery
                conn.ConnectionString += conn.ConnectionString & ";integrated security= SSPI"
                conn.Open()
    
    
                ' Execute and create a reader for the current command
                reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
                ' For each field in the resultset, add the name to an array list
                ' And add the field type to an array list to set formatting in report
                m_Fields = New ArrayList
                m_Type = New ArrayList
                Dim i As Integer
                For i = 0 To reader.FieldCount - 1
                    m_Fields.Add(reader.GetName(i))
                    m_Type.Add(Replace(reader.GetFieldType(i).ToString, "System.", ""))
                Next i
                reader.Close()
                conn.Close()
            Catch ex As Exception
                ' ignore
            End Try
        End Sub 'GenerateFieldsList
    

    Code Illustration 16

Writing the RDL to a File

  1. Copy and paste the code from Code Illustration 17 below the GenerateFieldsList() end statement.

    Writing out to a file that is saved on disk allows you to import the RDL into a report project in VS.NET and make modifications.

    The Report Definition Language (RDL) is fully documented in the Reporting Services Books Online. It is an XML-based schema. Developers familiar with XML attributes/elements and HTML tags should find the RDL easy to understand. A sample of programmatically generating RDL, Walkthrough – Generating RDL Using the .NET Framework, is in the Reporting Services Books Online. See inline code comments for additional explanations.

        Public Sub GenerateRdl(ByVal m_ReportName As String)
            ' Open a new RDL file stream for writing
            Dim stream As FileStream
            Dim i As Integer
            Try
    
            ' If a file with the same name exists, delete it
                If File.Exists(m_ReportName) Then
                    File.Delete(m_ReportName)
                End If
    
            ' Open the file for writing
                stream = File.OpenWrite(m_ReportName)
    
            ' Create an XML text writer to begin writing to the file
                Dim writer As New XmlTextWriter(stream, Encoding.UTF8)
    
                ' Causes child elements to be indented
                writer.Formatting = Formatting.Indented
    
                ' Report element
                writer.WriteProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
                writer.WriteStartElement("Report")
                writer.WriteAttributeString("xmlns", Nothing, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition")
                writer.WriteElementString("Width", "6in")
    
                ' DataSource element
                writer.WriteStartElement("DataSources")
                writer.WriteStartElement("DataSource")
                writer.WriteAttributeString("Name", Nothing, dsName)
                writer.WriteElementString("DataSourceReference", dsName)
                writer.WriteEndElement() ' ConnectionProperties
                writer.WriteEndElement() ' DataSource
    
                ' DataSet element
                writer.WriteStartElement("DataSets")
                writer.WriteStartElement("DataSet")
                writer.WriteAttributeString("Name", Nothing, "DataSet1")
    
                ' Query element
                writer.WriteStartElement("Query")
                writer.WriteElementString("DataSourceName", dsName)
                writer.WriteElementString("CommandType", "Text")
                writer.WriteElementString("CommandText", m_query)
                writer.WriteElementString("Timeout", "30")
                writer.WriteEndElement() ' Query
                ' Fields elements
                writer.WriteStartElement("Fields")
    
                ' Loop through the fields to add field names
                For i = 0 To m_Fields.Count - 1
                    writer.WriteStartElement("Field")
                    writer.WriteAttributeString("Name", Nothing, m_Fields(i))
                    writer.WriteElementString("DataField", Nothing, m_Fields(i))
                    writer.WriteEndElement() ' Field
                Next 'fieldName
    
                ' End previous elements
                writer.WriteEndElement() ' Fields
                writer.WriteEndElement() ' DataSet
                writer.WriteEndElement() ' DataSets
    
                ' Body element
                writer.WriteStartElement("Body")
                writer.WriteElementString("Height", "5in")
    
    
                ' ReportItems element
                writer.WriteStartElement("ReportItems")
    
                'If report title was specified, create the title text box
                If m_ReportTitle <> "" Then
                    writer.WriteStartElement("Textbox")
                    writer.WriteAttributeString("Name", Nothing, "Title1")
                    writer.WriteStartElement("Style")
                    writer.WriteElementString("FontFamily", "Tahoma")
                    writer.WriteElementString("FontSize", "18pt")
                    writer.WriteElementString("Color", "Navy")
                    writer.WriteElementString("FontWeight", "700")
                    writer.WriteElementString("TextAlign", "Center")
                    writer.WriteEndElement() 'style
                    writer.WriteElementString("Top", "0.125in")
                    writer.WriteElementString("Height", "0.375in")
                    writer.WriteElementString("Width", "6.5in")
                    writer.WriteElementString("CanGrow", "true")
                    writer.WriteElementString("Value", m_ReportTitle)
                    writer.WriteElementString("Left", "0.125in")
                    writer.WriteEndElement() 'Textbox
    
                    ' Table element
                    writer.WriteStartElement("Table")
                    writer.WriteAttributeString("Name", Nothing, "Table1")
                    writer.WriteElementString("DataSetName", "DataSet1")
                    writer.WriteElementString("Top", ".5in")
                    writer.WriteElementString("Left", ".5in")
                    writer.WriteElementString("Height", ".25in")
                Else
                    ' Table element
                    writer.WriteStartElement("Table")
                    writer.WriteAttributeString("Name", Nothing, "Table1")
                    writer.WriteElementString("DataSetName", "DataSet1")
                    writer.WriteElementString("Top", ".125in")
                    writer.WriteElementString("Left", ".125in")
                    writer.WriteElementString("Height", ".25in")
                End If
    
               'determine the width of the table by adding the widths of each column together
                Dim width As Integer = 0
                For i = 0 To m_Fields.Count - 1
                    Select Case m_Type(i)
                        Case "Decimal", "Int16", "Int32", "Int64", "Boolean"
                            width = width + 0.5
                        Case "DateTime"
                            width = width + 0.75
                        Case Else
                            width = width + 1.5
                    End Select
                Next
                writer.WriteElementString("Width", width & "in")
    
                ' Table Columns
                writer.WriteStartElement("TableColumns")
                For i = 0 To m_Fields.Count - 1
                    writer.WriteStartElement("TableColumn")
                    Select Case m_Type(i)
                        Case "Decimal", "Int16", "Int32", "Int64", "Boolean"
                            writer.WriteElementString("Width", ".5in")
                        Case "DateTime"
                            writer.WriteElementString("Width", ".75in")
                        Case Else
                            writer.WriteElementString("Width", "1.5in")
                    End Select
                    writer.WriteEndElement() ' TableColumn
                Next 'fieldName
                writer.WriteEndElement() ' TableColumns
    
                ' Header Row
                writer.WriteStartElement("Header")
                writer.WriteStartElement("TableRows")
                writer.WriteStartElement("TableRow")
                writer.WriteElementString("Height", ".25in")
                writer.WriteStartElement("TableCells")
    
                For i = 0 To m_Fields.Count - 1
                    writer.WriteStartElement("TableCell")
                    writer.WriteStartElement("ReportItems")
    
                    ' Textbox
                    writer.WriteStartElement("Textbox")
                    writer.WriteAttributeString("Name", Nothing, "Header" + m_Fields(i))
    
                    'Set the background color and other header styles
                    writer.WriteStartElement("Style")
                    writer.WriteElementString("BackgroundColor", "DarkBlue")
                    writer.WriteElementString("Color", "White")
    
                    ' Align cell according to datatype
                    Select Case m_Type(i)
                        Case "Decimal", "Int16", "Int32", "Int64"
                            writer.WriteElementString("PaddingRight", "3pt")
                            writer.WriteElementString("TextAlign", "Right")
                        Case Else
                            writer.WriteElementString("TextAlign", "Left")
                    End Select
                    writer.WriteElementString("FontWeight", "700")
                    writer.WriteEndElement() ' End Style
                    writer.WriteElementString("Top", "0in")
                    writer.WriteElementString("Left", "0in")
                    writer.WriteElementString("Height", ".5in")
                    Select Case m_Type(i)
                        Case "Int16", "Int32", "Int64", "Boolean", "Decimal"
                            writer.WriteElementString("Width", ".5in")
                        Case "DateTime"
                            writer.WriteElementString("Width", ".75in")
                        Case Else
                            writer.WriteElementString("Width", "1.5in")
                            writer.WriteElementString("CanGrow", "true")
                            writer.WriteElementString("CanShrink", "true")
                    End Select
                    writer.WriteElementString("Value", m_Fields(i))
                    writer.WriteEndElement() ' End Textbox
                    writer.WriteEndElement() ' End ReportItems
                    writer.WriteEndElement() ' EndTableCell
                Next 'fieldName
    
                writer.WriteEndElement() ' TableCells
                writer.WriteEndElement() ' TableRow
                writer.WriteEndElement() ' TableRows
                writer.WriteElementString("RepeatOnNewPage", "true")
                writer.WriteEndElement() ' Header
                'writer.WriteElementString("KeepTogether", "true")
    
                ' Details Row
                writer.WriteStartElement("Details")
                writer.WriteStartElement("TableRows")
                writer.WriteStartElement("TableRow")
                writer.WriteElementString("Height", ".25in")
                writer.WriteStartElement("TableCells")
    
                ' Loop through the field and type arrays to set formatting & styles
                For i = 0 To m_Fields.Count - 1
                    writer.WriteStartElement("TableCell")
                    writer.WriteStartElement("ReportItems")
    
                    ' Textbox
                    writer.WriteStartElement("Textbox")
                    writer.WriteAttributeString("Name", Nothing, m_Fields(i))
    
                    writer.WriteStartElement("Style")
                    writer.WriteElementString("PaddingLeft", "2pt")
    
                    ' format and align cell according to datatype
                    Select Case m_Type(i)
                        Case "DateTime"
                            writer.WriteElementString("TextAlign", "Left")
                            writer.WriteElementString("Format", "MM/dd/yyyy")
                        Case "Decimal"
                            writer.WriteElementString("PaddingRight", "3pt")
                            writer.WriteElementString("TextAlign", "Right")
                            writer.WriteElementString("Format", "F")
                        Case "Int16", "Int32", "Int64"
                            writer.WriteElementString("PaddingRight", "3pt")
                            writer.WriteElementString("TextAlign", "Right")
                        Case Else
                            writer.WriteElementString("TextAlign", "Left")
                    End Select
    
                    writer.WriteEndElement() ' Style
                    writer.WriteElementString("Top", "0in")
                    writer.WriteElementString("Left", "0in")
                    writer.WriteElementString("Height", ".5in")
    
                    ' Create cell width dependent on datatype
                    Select Case m_Type(i)
                        Case "Int16", "Int32", "Boolean", "Decimal"
                            writer.WriteElementString("Width", ".5in")
                        Case "DateTime"
                            writer.WriteElementString("Width", ".75in")
                        Case Else
                            writer.WriteElementString("Width", "1.5in")
                            writer.WriteElementString("CanGrow", "true")
                            writer.WriteElementString("CanShrink", "true")
                    End Select
    
                    writer.WriteElementString("Value", "=Fields!" + m_Fields(i) + ".Value")
                    'writer.WriteElementString("HideDuplicates", "DataSet1")
                    writer.WriteEndElement() ' Textbox
                    writer.WriteEndElement() ' ReportItems
                    writer.WriteEndElement() ' TableCell
                Next 'fieldName
    
    
                ' End Details element and children   
                writer.WriteEndElement() ' TableCells
                writer.WriteEndElement() ' TableRow
                writer.WriteEndElement() ' TableRows
                writer.WriteEndElement() ' Details
    
                ' End table element and end report definition file
                writer.WriteEndElement() ' Table
                writer.WriteEndElement() ' ReportItems
    
                writer.WriteEndElement() ' Body
    
                ' Set overall report margins
                writer.WriteElementString("TopMargin", "0.25in")
                writer.WriteElementString("BottomMargin", "0.25in")
                writer.WriteElementString("LeftMargin", "0.25in")
                writer.WriteElementString("RightMargin", "0.25in")
                ' End the report
                writer.WriteEndElement() ' Report
    
                ' Flush the writer
                writer.Flush()
            Catch ex As Exception
                HandleException(ex)
            Finally
                'Close the stream
                stream.Close()
            End Try
    
        End Sub 'GenerateRdl
    

    Code Illustration 17

  2. Add the BeginPublishReport() code from Code Illustration 18. This code calls two methods, one for creating the data source and the other for publishing the report that was written in GenerateRDL().
        Public Sub BeginPublishReport(ByVal ReportName As String)
            RS.Credentials = System.Net.CredentialCache.DefaultCredentials
            Try
    
                'Create the shared data source
                CreateDataSource()
    
                'Publish the report
                PublishReport(ReportName)
    
            Catch ex As Exception
                HandleException(ex)
            End Try
        End Sub
    

    Code Illustration 18

Create a Shared Data Source in "My Reports"

  1. Add Code Illustration 19 to create the data source. See the ReportingService.CreateDataSource Method topic for additional information on the definition and properties.
        Public Sub CreateDataSource()
            Dim name As String = dsName
            Dim parent As String = "/" + parentFolder
    
            'Define the data source definition.
            Dim definition As New DataSourceDefinition
            definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
            definition.ConnectString = m_ConnStr
            definition.Enabled = True
            definition.EnabledSpecified = True
            definition.Extension = "SQL"
            definition.ImpersonateUser = True
            definition.ImpersonateUserSpecified = False
            'Use the default prompt string.
            definition.Prompt = Nothing
            definition.WindowsCredentials = True
    
            'Try creating the data source
            Try
                RS.CreateDataSource(name, parent, True, definition, Nothing)
    
            Catch ex As Exception
                HandleException(ex)
            End Try
        End Sub
    

    Code Illustration 19

Publish a Report to "My Reports"

  1. Copy and paste Code Illustration 20 after the End Sub statement of the CreateDataSource method.
        Public Sub PublishReport(ByVal reportName As String)
            Try
                'Create a stream reader and open file
                'Could also create a stream without generating an RDL file 
                Dim reader As StreamReader
                Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
    
                'read the file into the definition
                definition = New [Byte](stream.Length) {}
                stream.Read(definition, 0, CInt(stream.Length))
    
                'close the stream
                stream.Close()
    
            Catch ex As IOException
                HandleException(ex)
            End Try
    
            'Try creating the report using values filled
            Try
                Dim myPropertyValue As String = "Table/View Name: " & TableName & " from " & DatabaseName & " on " & ServerName & " server. "
                myPropertyValue += "  Columns Selected: " & OrderList & "."
                myPropertyValue += " Note: This report uses " & dsName & " shared data source."
                'Set the description of the report to list the server name, table 
                ' and fields used
                Dim newProp As New [Property]
                newProp.Name = "Description"
                newProp.Value = myPropertyValue
                Dim props(0) As [Property]
                props(0) = newProp
    
                'Create the report
                'Properties can be passed as Nothing
                warnings = RS.CreateReport(reportName, parentPath, True, definition, props)
    
                'Write warnings to event log
                If Not (warnings Is Nothing) Then
                    Dim warning As Warning
                    For Each warning In warnings
                        Diagnostics.EventLog.WriteEntry("Report Server", warning.Message, Diagnostics.EventLogEntryType.Warning)
                    Next warning
                End If
    
            Catch ex As Exception
                HandleException(ex)
            End Try
        End Sub
    

    Code Illustration 20

  2. Add the generic error handler as shown in Code Illustration 21.
        Private Sub HandleException(ByVal exception As Exception)
            'Generic exception handler
            Response.Write(exception.Source & "<br>" & exception.Message & "")
        End Sub
    

    Code Illustration 21

  3. Close RDLGenerator.aspx, saving all changes.

    The ReportWizard.aspx and the RDLGenerator.aspx pages are all that are required to dynamically generate connection, query strings, and RDL files; publish the definition and data source to the Report Server; and render them out in the specified output format. The remainder of this document is about creating a page that allows users to delete their reports and data sources from their My Reports folder.

Add the ManagingReports Form to the Project

The purpose of this page is to demonstrate how to iterate through a collection of reports and data sources in the My Reports folder, check for dependencies, and, if none exist, delete multiple selected objects simultaneously.

  1. Add a new web form to the project and name it ManagingReports.aspx.
  2. Open the page in the designer and choose HTML view.
  3. Delete the <form></form> tags.
  4. Copy and Paste as HTML the following html:
    <script>
    function OpenWin()
    {
    window.open("/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&IsDetailsView=False","MyReports")
    }
    </script>
    <form id="Form1" method="post" runat="server">
    <P dir="ltr" style="MARGIN-RIGHT: 0px"><asp:label id="Label3" style="Z-
    INDEX: 104; LEFT: 176px; POSITION: absolute; TOP: 8px" runat="server"
    Height="24px" BackColor="Transparent" Font-Italic="True" 
    BorderColor="White" ForeColor="ForestGreen" Font-Size="22pt" Font-
    Bold="True"
    Width="384px">Your Reports and Data Sources</asp:label><asp:label 
    id="Label2" style="Z-INDEX: 103; LEFT: 408px; POSITION: absolute; TOP: 
    128px" runat="server"
    ForeColor="Navy" Font-Size="Medium" Font-Bold="True" Width="280px">Data 
    Sources You Have Created:</asp:label><asp:checkboxlist 
    id="chkReportList" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; 
    TOP: 160px"
    runat="server" Height="24px" Width="296px" BorderWidth="1pt" 
    BorderStyle="Inset"></asp:checkboxlist><asp:label id="Label1" style="Z-
    INDEX: 102; LEFT: 24px; POSITION: absolute; TOP: 128px" runat="server"
    ForeColor="Navy" Font-Size="Medium" Font-Bold="True" 
    Width="288px">Reports You Have Created:</asp:label><asp:checkboxlist 
    id="chkDSList" style="Z-INDEX: 105; LEFT: 400px; POSITION: absolute; 
    TOP: 160px"
    runat="server" Width="296px" BorderWidth="1pt" 
    BorderStyle="Inset"></asp:checkboxlist><asp:button id="btnDel" 
    style="Z-INDEX: 106; LEFT: 144px; POSITION: absolute; TOP: 64px" 
    runat="server"
    Text="Delete Selected Items"></asp:button></P>
    <HR style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 104px" width="100%" SIZE="1"
    color="#006600">
    <asp:label id="lblWarning" style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 488px"
    runat="server" Height="24px" ForeColor="#C00000" Font-Size="10pt" Font-Bold="True" Width="688px"
    Visible="False">***Reports with subscriptions or data sources with 
    reports using them must be deleted from the My Reports 
    folder</asp:label>
    <INPUT style="Z-INDEX: 108; LEFT: 384px; WIDTH: 208px; POSITION: absolute; TOP: 64px; HEIGHT: 24px"
    type="button" value="View My Reports Folder" onclick="OpenWin()">
    </form>
    

Your page should now look like this:

Aa902634.adhocrepsr_figure19(en-us,SQL.80).gif

Figure 19

Add Code to the ManageReports Page

  1. Right-click and select View Code. Copy and paste the comments and Imports statements from CodeIllustration 22 above the Public Class ManageReports to create the Imports statements and comments.
    '=====================================================================
    '  Copyright (C) Microsoft Corporation.  All rights reserved.
    '
    ' This source code is intended only as a supplement to Microsoft
    ' Development Tools and/or on-line documentation.  See these other
    ' materials for detailed information regarding Microsoft code samples.
    '
    ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
    ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
    ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    ' PARTICULAR PURPOSE.
    '=====================================================================*/
    Imports Microsoft.SqlServer.ReportingServices
    

    Code Illustration 22

  2. Next, copy and paste the variable declarations from Code Illustration 23 just above the Public Sub Page_Load().
        Private rs As New ReportingService ' Create new Reporting Service
        Private _returnedItems() As CatalogItem ' Create catalog item container
        Private parentPath = "/My Reports" ' Path of reports..user must have the right to create and modify content.
    

    Code Illustration 23

  3. Replace the Page_Load method with the code in Code Illustration 24.

    This code checks if the page is a post back and if not, it calls FindMyReports().

        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' Check if page is posted back
            If Not IsPostBack Then ' begin listing reports and data sources
                FindMyReports()
            End If
        End Sub
    

    Code Illustration 24

  4. Copy and paste the FindMyReports() code from Code Illustration 25 just above the End Class.

    For more information, see the ReportingService.FindItems Method topic, as well as the FindRenderSave application in the Reporting Services/Samples/Applications folder. See inline comments for additional explanation.

        Private Sub FindMyReports()
            ' Clear any existing items
            chkReportList.Items.Clear()
            chkDSList.Items.Clear()
    
            ' Create a new proxy to the web service
            rs = New ReportingService
    
            ' Authenticate to the Web service using Windows credentials
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    
            ' Assign the URL of the Web service
            rs.Url = "http://localhost/ReportServer/ReportService.asmx"
    
            ' Create SearchCondition containers
            Dim conditions() As SearchCondition
            Dim condition As New SearchCondition
    
            ' Fill search conditions
            ' Searchable properties are Name, Description, CreatedBy,  
            ' CreationDate,ModifiedBy, and ModifiedDate
            Try
                condition.Condition = ConditionEnum.Contains 'Contains or Equals
                condition.ConditionSpecified = True 'Must be set to true
                condition.Name = "CreatedBy" 'Searching the My Reports folder for all objects
                                             'created by the current user
                condition.Value = System.Security.Principal.WindowsIdentity.GetCurrent.Name
                conditions = New SearchCondition(0) {}
                conditions(0) = condition
    
                ' Get the return catalog items
                _returnedItems = rs.FindItems(parentPath, BooleanOperatorEnum.Or, conditions)
    
                ' Verify something is returned
                If Not (_returnedItems Is Nothing) AndAlso _returnedItems.Length <> 0 Then
                    Dim ci As CatalogItem
                    Dim _ci As CatalogItem
                    Dim su As Subscription
    
                    ' Loop through the report and data source catalog items returned and add 
                    ' them to their corresponding list collections
                    For Each ci In _returnedItems
                        If ci.Type = ItemTypeEnum.Report Then
                            
                    ' If subscriptions exist for this report add asterisks to name
                    For Each su In rs.ListSubscriptions(parentPath & "/" & ci.Name, condition.Value.ToString)
                         ci.Name = ci.Name & "***"
                   ' If a subscription is found, exit the loop. There is no need to continue.
                         Exit For
                    Next
                    ' Fill the chkReportList checkbox group with the report names
                    chkReportList.Items.Add(ci.Name)
    
                    ' if it's not a report, check to see if it's a data source
                    ElseIf ci.Type = ItemTypeEnum.DataSource Then
                            
                    ' If reports depend on this data source add asterisks to name
                    For Each _ci In rs.ListReportsUsingDataSource(parentPath & "/" & ci.Name)
                        ci.Name = ci.Name & "***"
                    ' If a report is found, exit the loop. There is no need to continue.
                        Exit For
                    Next
                    ' Fill the chkDSList checkbox group with the data sources
                    chkDSList.Items.Add(ci.Name)
                End If
                Next ci
                End If
    
            Catch exception As Exception
                HandleException(exception)
            End Try
        End Sub
    

    Code Illustration 25

  5. Copy and paste the code from Code Illustration 26 above the End Class declaration for the btnDel click event.
    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
            rs = New ReportingService
    
            ' Authenticate to the Web service using Windows credentials
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    
            ' Assign the URL of the Web service
            rs.Url = "http://localhost/ReportServer/ReportService.asmx"
    
            Dim li As New ListItem
    
            ' Get the Report List items
            For Each li In chkReportList.Items
            ' loop through each report list item & check if it is selected
                If li.Selected Then
                    ' If report has subscriptions set the warning label to visible
                    ' and DO NOT delete
                    If li.Text.EndsWith("***") Then
                        lblWarning.Visible = True
                    Else ' else delete the report
                        rs.DeleteItem(parentPath & "/" & li.Text)
                    End If ' end text.EndsWith
                End If  ' end li.Selected
            Next  ' list item
            ' Loop through each data source list item & check if it is selected
            For Each li In chkDSList.Items
                If li.Selected Then
                'If the data source has reports depending on it set the warning label
                'to visible
                    If li.Text.EndsWith("***") Then
                        lblWarning.Visible = True
                    Else ' else delete the data source
                        rs.DeleteItem(parentPath & "/" & li.Text)
                    End If
                End If
            Next
            ' Regenerate report and data source lists
            FindMyReports()
        End Sub
    

    Code Illustration 26

  6. Add a generic exception handler of your choosing in a HandleException method. You can copy and paste Code Illustration 27 or create your own.
        Private Sub HandleException(ByVal exception As Exception)
            'Generic exception handler
            Response.Write(exception.Source & "<p>" & exception.Message)
        End Sub
    

    Code Illustration 27

  7. Close the page and save all changes.
  8. Press F5 to build the application and test it.
Occasionally, I would encounter a vbc : Command line error BC2017.
Recompiling seems to take care of it. If you receive this compiling error, refer to: Microsoft Knowledge Base Article - 319976 for additional information.

Conclusion

Using the .NET Framework to program Reporting Services is very straightforward and well-documented, as are the samples that are installed with it. Hopefully this sample application has provided you with supplemental knowledge you can use to move forward in implementing an enterprise reporting environment. The MSSRS team is continually working to improve the developer and user experience and we look forward to your comments and feedback.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft