Tutorial: Creating an Ad Hoc Report Application with Reporting Services

Tutorial: Creating an Ad Hoc Report...

Microsoft SQL 2000 Technical Articles

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)

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

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

Figure 1
Right-click References and select Add Reference from the quick menu.
From the Add Reference modal window click Browse.
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.
Select the ReportingServicesWebUserInterface.dll.
On the .NET tab of the Add Reference modal window, scroll down until you find System.Web.Services.dll, and click on it.

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

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

Add the following identity tag to the Web.Config file under the <System.Web> opening tag
```
  <identity impersonate="true" />
```
Using the Internet Information Services manager from the Administrative tools set the CreateReport web security to Windows authentication.
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.

Figure 3

Add the ReportWizard Form to the Project

Add a new web form (Project, Add Web Form) and name it ReportWizard.aspx.
Right-click ReportWizard.aspx and select Set As Start Page.
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>
```

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>

Select the Design view and you should see this:

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

Right-click the ServerList control and select Properties.
Click on the ellipses next to (Collection) in Items.
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.

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

The txtTitle text box is used to allow a user-specified report title to be entered.
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).
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.
The Start Over button deletes the form data and re-starts the wizard at the beginning.
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

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

Figure 8
Click Next.
Click on the New Connection button.

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

Figure 10
Click Next to proceed to the next step.
Accept the default Use SQL statements on the Choose a Query Type screen and click Next.

Figure 11
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

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

Figure 13
Click Next and then Finish.

Figure 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

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

Add the Data Adapter to Fill the Tables List

Select SQLDataAdapter from the data tab on the Tools menu to create the TablesDA.
Click Cancel when the Data Adapter Configuration Wizard begins. You will not be using the wizard to complete the properties.
Select the new Data Adapter and click on the Properties view.
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)

Figure 15

Create the Tables Dataset

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

Figure 16

Add the Data Adapter to Fill the Fields List

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

Figure 17

Add the Code to the ReportWizard Form

Right-click on ReportWizard.aspx page and select View Code.

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

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

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

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

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

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

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

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

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

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.

Select Add New Item from the File menu.

Figure 18
Select Web Form and name it Error_Page.aspx.

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

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.

Add a new web form as before, naming it RDLGenerator.aspx.

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

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

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

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

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

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"

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"

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

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

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.

Add a new web form to the project and name it ManagingReports.aspx.
Open the page in the designer and choose HTML view.
Delete the <form></form> tags.

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

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

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

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

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

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

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

Close the page and save all changes.
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.

Contents