So you are ready to create your own web part because the Reporting Services web part does not allow you to pass default parameters and hide the toolbar. Before we start getting into the code, I would like to explain what is happening behind the scenes when your web part gets displayed.

How does the report get displayed after all? To explain this we will have to first look into how the reports are displayed in Reporting Services. Open the above report in Reporting Services using the Report Manager. After entering the parameters your report should open up as follows.

Place your mouse pointer anywhere in the section of the toolbar with the print button on it, right-click on it, and click view source. When looking at view source you will see some JavaScript with variables set to some URLs. If you look at the dmRepUrl and renderingUrl variables you will notice that the report URL is being pointed to where you have actually stored those reports. The URL on my machine displays as follows.

http://computername/ReportServer?%2fSampleReports%
2fEmployee+Sales+Summary&ReportMonth=12&ReportYear=2003&EmpID=20&rs%
3aFormat=HTML4.0&rs%3aCommand=Render&rc%3aArea=Report&rc%
3aLinkTarget=_top&rc%3aJavaScript=True&rc%3aToolbar=True&rc%
3aReplacementRoot=http%3a%2f%2flocalhost%2fReports%2fPages%
2fReport.aspx%3fServerUrl%3d

In my case it points to computername/ReportServer. Reportserver is the place where all my reports are stored. If you look at the string after computername/ReportServer you will notice that after the “?,” you have the “SampleReports/Employee Sales Summary” passed in the querystring. In the above example you will see ReportMonth=12, ReportYear=2003, and EmpID=20 passed in as parameters. So basically, SQL Reporting Services is creating the URL as soon as you set the parameters and when you are ready to render the reports.

Now the question arises: how do you show this report in a web part? That’s where you actually use an IFrame tag to display the report. Now if you switch back to your SharePoint Portal, open the page that displays the Report, and then view the source. Search for the IFrame tag. For the above report you will see it as below.

Source for the IFrame Tag from the SharePoint Page

<IFrame id="fmViewerg_16456605_2905_4a52_98b0_6f9802039e12" 
name="fmViewerg_16456605_2905_4a52_98b0_6f9802039e12" 
src="http://localhost/Reports/Pages/Report.aspx?ItemPath=%
2fSampleReports%2fEmployee+Sales+Summary&amp;
ViewMode=WebPart&amp;StyleSheet=Full" width="100%" height="100%" 
frameborder="0" scrolling="auto"></IFrame>

In the above example the code is actually using the Report Manager’s URL to display this report. You might have also noticed that it does not display the parameters because the report is being rendered inside the IFrame. If you click inside the web part and then do a view source you will find the actual URL similar to the one covered in the Reporting Services URL section above.

I will show you how you could use the actual Reporting Server’s URL to display the report.

Options to Display the Reports

There are actually two different options to display the report by passing default parameters. Based on the above explanation you might have guessed that you could use the page viewer web part. You are right; you can use it.

Page Viewer web part: You can drop a Page Viewer and add the following URL to its Link property.

Report URL to Display the Report

http://localhost/ReportServer?/SampleReports/Employee Sales Summary
&rs:Command=Render&EmpID=20&ReportYear=2003&ReportMonth=12&rc:toolbar=False

The above URL will display the report as follows.

With the above you do expect the user to know the exact syntax to enter it in the Properties Window.

The second option is creating your own web part. The easiest way to create your own web part is to convert the Report Viewer Web Control application provided with SQL Reporting Services. If you have installed SQL Reporting Services with the default options you can find the Report Viewer application under

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer folder.

Follow these steps to create your own Report Viewer web part:

1. Open Visual Studio.NET.
2. Click on File New Project.
3. Under Visual Basic Projects you should see an entry for Web Part Library. If you have not installed Web Part Library templates you can download and install it from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_sp2003_ta/html/sharepoint_webparttemplates.asp. I strongly recommend downloading these templates and using it for creating new web parts.
4. Click on Web Part Library and give it a suitable name. I named mine as APRSReportViewer.
5. Once you create this project, the following files should be added to your Project.
   1. WebPart1.dwp. Rename this file;I renamed it APReportViewer.dwp.
   2. WebPart1.VB. Rename this file too. I renamed it APReportViewer.vb.
   3. AssemblyInfo.vb
   4. Manifest.xml
6. Replace all the instances of WebPart1 with APReportViewer in code.
7. Add new Project to the solution using Add Project -> New Project and selecting Cab Project under Setup and Deployment Projects.
8. On the newly added Cab Project. Right Click and Select Add->Project Output.
9. Select Primary Output and Content Files from the available list of Add Project Output list.
10. Click Ok. Your solution should resemble the one below.
    
11. Open the APReportViewer.vb file.
12. Now we will need four properties for ReportServer, Report Start Path, Report Parameters, and whether to display the Toolbar. The following code creates those properties.

Private _serverURL as string =””
Private _ reportPath as string =””
Private _ showToolbar1 as string = “True”
Private _properties As New Hashtable
<Browsable(True), Category("General Report Parameters"), DefaultValue
(_defaultText), WebPartStorage(Storage.Personal), FriendlyName("ServerURL"),
Description("Server URL Address")> _
Public Property ServerUrl() As String
  Get
    Return Me._serverUrl
  End Get
  Set(ByVal Value As String)
    Me._serverUrl = Value
  End Set
End Property
 
<Browsable(True), Category("General Report Parameters"), DefaultValue
(_defaultText), WebPartStorage(Storage.Personal), FriendlyName
("ReportPath"), Description("ReportPath Property")> _
Public Property ReportPath() As String
  Get
    Return Me._reportPath
  End Get
  Set(ByVal Value As String)
    Me._reportPath = Value
  End Set
End Property
 
<Browsable(True), Category("General Report Parameters"), DefaultValue
(_defaultText), WebPartStorage(Storage.Personal), FriendlyName("Toolbar"), 
Description("Toolbar1 Property")> _
Public Property Toolbar1() As String
  Get
    Return Me._showToolbar1
  End Get
  Set(ByVal Value As String)
    Me._showToolbar1 = Value
  End Set
End Property
 
<Browsable(True), Category("General Report Parameters"), DefaultValue
(_defaultText), WebPartStorage(Storage.Personal), FriendlyName("Report 
Parameters"), Description("Toolbar1 Property")> _
Public Property ReportParameters() As String
  Get
    Return Me._reportParameters
  End Get
  Set(ByVal Value As String)
    Me._reportParameters = Value
  End Set
End Property

Private Sub SetParameter(ByVal name As String, ByVal value As String)
  Try
  'Remove if value is null or empty. Value is null of the property grid value
  'is null or empty. Empty or null removes the property from the Hashtable.
  If value Is Nothing Or value = String.Empty Then
    Me._properties.Remove(name)
  Else
    If Me._properties.ContainsKey(name) Then
      'Change if key exists
      Me._properties(name) = value
    Else
      'Add if key does not exist
      Me._properties.Add(name, value)
    End If
  End If
  'Build a new url string
  Me.BuildUrlString()
  'Catch and handle a more specific exception in a propduction application.
  Catch ex As Exception
    'Sample throws the exception to the client
    Throw ex
  End Try
End Sub 'SetParameter

Private Function EmumProperties(ByVal properties As Hashtable) As String
  Dim paramsString As String = String.Empty
  'Enumerate properties and create report server specific string.
  Dim customPropEnumerator As IDictionaryEnumerator = properties.GetEnumerator()
  While customPropEnumerator.MoveNext()
    paramsString += "&" + CStr(customPropEnumerator.Key) + "=" + _
      CStr(customPropEnumerator.Value)
  End While
  Return paramsString
End Function 'EmumProperties

We need to build another method to create a complete URL for the report.

Method to Build the URL String

Public Function BuildUrlString() As String
  Me._url = Me._serverUrl + "?" + Me._reportPath + "&rs:Command=Render" + _
    Me.EmumProperties(Me._properties)
  Return Me._url
End Function 'BuildUrlString

We also need to create another method to pass the actual report parameters.

Method to Store the Actual Report Parameters to Filter the Report

Private Sub PassReportParameters()
  Dim strParameters(), strParameter As String
  Dim strParamValues(), strParamName, strParamValue As String
  strParameters = Me._reportParameters.Split("|")
  For Each strParameter In strParameters
    strParamValues = strParameter.Split("=")
    Me.SetParameter(strParamValues(0), strParamValues(1))
  Next
End Sub

Finally, we come to the RenderWebPart method. This is the method where you will display the report in IFrame.

Render Method

Protected Overrides Sub RenderWebPart(ByVal output As System.Web.UI.HtmlTextWriter)
  Try
    If Me._serverUrl = String.Empty OrElse Me._reportPath = String.Empty Then
      output.Write("<P style=""font-family: Verdana; font-size: 11px"">")
      output.Write("To render a report, enter the ServerUrl and ReportPath.</P>")
    Else
      'Me._serverUrl = Me._serverUrl & "/Pages/Report.aspx?ItemPath="
      'Create IFrame if the user enters ServerUrl and ReportPath
      If Me._showToolbar1.Trim.Length > 0 Then
        Me.SetParameter("rc:toolbar", Me._showToolbar1)
      Else
        Me.SetParameter("rc:toolbar", "Default")
      End If
      If Me._reportParameters.Trim.Length > 0 Then
        Me.PassReportParameters()
      End If
      output.WriteBeginTag("IFrame")
      output.WriteAttribute("src", Me.BuildUrlString)
      output.WriteAttribute("width", "100%")
      output.WriteAttribute("height", "100%")
      output.WriteAttribute("style", "border: 1 solid #C0C0C0")
      output.WriteAttribute("border", "0")
      output.WriteAttribute("frameborder", "0")
      </font>output.Write(HtmlTextWriter.TagRightChar)
      output.WriteEndTag("IFrame")
      output.WriteLine()
    End If
  Catch ex As Exception
    output.Write("<P style=""font-family: Verdana; font-size: 11px"">")
    output.Write("Error Occurred while rendering the report.</P>")
  End Try
End Sub

Now compile the solution, and install the Cab file on to the server by running the following command on your command prompt.

""C:\Program Files\Common Files\Microsoft Shared\web server extensions\60
\BIN\STSADM.exe" -o addwppack -filename "c:\Cab\APRSWebPartCab.CAB""

Based on the above web part, you could pass the following values:
  ServerURL - http://localhost/ReportServer 
  ReportPath - /SampleReports/Employee Sales Summary
  Toolbar – False – To hide the parameters toolbar
  Report Parameters - ReportMonth=12|ReportYear=2003|EmpID=20
  The Report Parameters are passed with | delimiter
  The report web part will look like the one below.