AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=729&pId=-1
Working with Reporting Services Using Microsoft Sharepoint
page
by Ameet Phadnis
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 47395/ 53

Introduction

There are many instances in which the business process requires executive reports or some company performance reports to show up on intranet sites. Companies do use different portals to build their intranet sites. Microsoft’s SharePoint technology is one such tool. The main task is how we display the reports stored under SQL Reporting Services on the SharePoint site. Microsoft does provide two Web parts with SQL Reporting Services SP 2. In this section I will share information on using the current web parts provided by Microsoft and then explain what exactly happens behind the scenes to display the report. Finally I will explain how you could create your own web part.

The two web parts provided by Microsoft are Report Explorer and Report Viewer.

Report Explorer Web Part

This web part displays all the reports in Report Manager. This allows the user to set the Report Manager property and display all the folders/reports under Report Manager. Next we will cover the properties of this web part.

Report Manager: This property should be set to the Report Manager and not where your Reports are saved. If you just set this property then your screen will look as follows.

 

Start Path: This property allows you to start your explorer view from the folder under which these reports are stored. For this article I am using the default reports provided by SQL Reporting Services. If you set the Start Path your screen will be as follows.

 

View Mode: There are two options.  The first option, List, will display all the Report names and the descriptions. No other details will be provided. The Report Explorer web part will look as follows.

The second option is Detail.  This will display more information about the report in a table format with more data on when the report was last run, etc. The Report Explorer web part will look as follows.

Report Viewer Web Part

This web part is actually used to display the web part. You can either set the report to display in the properties or connect this web part to the Report Explorer web part.

Connecting to the Report Explorer: This is the easiest process. The steps required to accomplish this are:

  1. Go into Modify Shared Web Part for the Report Viewer web part.
  2. Once in Modify Shared Web Part, you can go to Connections -> Get report from and select Report Explorer.
  3. Now you can test the report by clicking on some report in the Report Explorer. For my example, I will click on the Company Sales Report under the sample reports. Following is the snapshot on how the report will display for Company Sales Report.

We'll now cover the properties for this web part.

Report Manager URL: As explained in the Explorer View, this is where you enter the Report Manager’s URL. In my case, I am using http://localhost/Reports.

Report Path: This differs from the Report Explorer web part; it is here you actually specify the Report to be displayed. In my case, I am going to use Employee Sales Summary as I want to show how the parameters window displays. In the snapshot I have highlighted the properties window and the actual Report Viewer web part. When there are parameters to be passed to the report, your report won’t render till you enter the parameters and click the View Report Button.

Toolbar Size: There are three different options for this as follows.  The first option is None, which will remove the toolbar altogether. For reports that do not use Parameters, this option works fine. But for reports that do use parameters, this option will display an error because the report is being rendered without any parameters being passed to it.

Now the question arises: what if I would like to pass default parameters to the screen and not display the Toolbar?  That’s where you will build your own Report Viewer web part as explained later in this article. You will see an error as follows on the above mentioned report if you set the Toolbar size to None.

The second option, Small, will display the parameter window.  The only part that is displayed for the toolbar is the scrolling part that assists the user in scrolling through pages. Your Report Viewer web part will look like the following.

This last option if Full.  This will display all the options including find, exporting the report, refresh, print, and help to create the report. Your screen will appear as below.

Section Summary

In this section I explained how you actually display reports using the Reporting Services Web parts provided by Microsoft. In the next section, I will explain the different options you could use to display the Reports with the default parameters set.

Creating Your Own Reporting Services Web Part

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.

Properties Code    

Private _serverURL as string =””
Private _ reportPath as string =””
Private _ showToolbar1 as string =TruePrivate _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.

Summary

This Article explains how you can display reports in SharePoint Portal details how you could create your own web part if the default web parts provided by SharePoint do not work for you. Going forward you could create Provider web part which would list all Reports under Reporting Services and Provide the Report Name as Cell for Report Viewer eb Part to consume it. Also, you could create your own Parameters web part which could provide a Row to consumer Report Viewer web part and display the Report. In short, there are lots of options that a developer can do to accomplish the displaying of reporting tasks on SharePoint Portal. The above logic does not hold true only for SharePoint Portal but you can use the same logic for ASP.NET Websites.



©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 2:24:34 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search