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&
ViewMode=WebPart&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:
- Open Visual Studio.NET.
- Click on File New Project.
- 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.
- Click on Web Part Library and give it a suitable name. I named mine as APRSReportViewer.
- Once you create this project, the following files should be added to your Project.
- WebPart1.dwp. Rename this file;I renamed it APReportViewer.dwp.
- WebPart1.VB. Rename this file too. I renamed it APReportViewer.vb.
- AssemblyInfo.vb
- Manifest.xml
- Replace all the instances of WebPart1 with APReportViewer in code.
- Add new Project to the solution using Add Project -> New Project and selecting Cab Project under Setup and Deployment Projects.
- On the newly added Cab Project. Right Click and Select Add->Project Output.
- Select Primary Output and Content Files from the available list of Add Project Output list.
- Click Ok. Your solution should resemble the one below.
- Open the APReportViewer.vb file.
- 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.
Properties Code
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
In the above code we have declared the _Properties parameter to store all the parameter/ properties information and then use it while creating the URL.
Now we define a method to set the parameters or store the parameters information in the properties variable.
Method for Setting Parameters
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
We also need a method to get all the parameters information in the URL.
Method for Creating Server Access Specific String
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.
