In one of the projects we are working on, we created a number of Reporting Services and integrated them in SharePoint 2007. This report displays SharePoint data in a printable, exportable report on a SharePoint page. We started of with using a report deployed to our Reporting Services server. In this proof of concept stage, we looked at using the Enesys RS Data Extension. A pretty clever datasource for SharePoint. It did not exactly meet our requirements, so we had to look at another solution. One of my colleagues pointed me to the ReportViewer controls in Visual Studio. This includes an ASP.NET control that is able to display reports in a web page. In this article I will describe how I created a web part that displays an aggregation of SharePoint tasks in a report. For the end user the report looks like this:
The user has a dropdown box in the web part that left him/her select a task status. After clicking the button, the reporting web part queries the site collection for all Task list items with that status and puts them in the report.
A big pro that we got by using this control is that we now bypass the Report Server. The report itself is deployed as a resource in the assembly. Therefore we do not need to deploy the report and its permissions. And we bypass the double hop. Nice. By using our Reporting Services server, we would go from SharePoint to the Report Server to get the report. The report server would need to connect back to the SharePoint server to get the data. By using the ReportViewer, we bypass this, because everything happens on the server. As you can see from the screenshot above, this article is not about my skills to build a nice report. I just want to show you how to display a report in SharePoint that is using SharePoint data. At the end of this post you will find a link to a ZIP file containing all sources used in this article.
Step 1 – Setup
In this article I am using Visual Studio 2008, the .NET Framework 3.5 SP1 (this also needs to be available on the SharePoint servers). After creating a new Visual Studio project, (Class library) I targeted the .NET framework to 3.5. Next thing to do is add a reference to the reporting assembly. This control is available in the dll Microsoft.ReportViewer.WebForms.dll, that can be found in folder ‘C:\Program Files\Microsoft Visual Studio 9.0\ReportViewer’. Your solution installer also needs to make the assembly available on the SharePoint server(s). The the reportview is not yet available on the SharePoint servers, you need to deploy these assemblies:
- Microsoft.ReportViewer.WebForms.dll
- Microsoft.ReportViewer.Common.dll
- Microsoft.ReportViewer.ProcessingObjectModel.dll
In the web.config of your SharePoint web application(s) you will need to make some changes. First thing to do is to register the HttpHandler. Add this snippet in the HttpHandlers section:
1: <add verb="*"
2: path="Reserved.ReportViewerWebControl.axd"
3: type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
In the appSettings section, you need to remove this element:
1: <add key="ReportViewerMessages"
2: value="Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
If you do not remove the element, your reporting web part will display this error message at runtime: “The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found”.
Now your Visual Studio project and your SharePoint are ready to get started.
Step 2 – Create the business objects
The report will use an ObjectDataSource as the datasource. In this step we will create the businessobjects to be used as the datasource. Visual Studio can help you pretty good if you want to connect to SQL Server to get the data for your report. Unfortunately, Visual Studio does not know about SharePoint, and therefore you will have to do the coding around the datasource yourself. First object to create is the Task object. This object is very simple:
1: public class Task
2: { 3: public Task()
4: { 5: }
6:
7: public String Title { get; set; } 8: public String Status { get; set; } 9: public String Priority { get; set; } 10: public String AssignedTo { get; set; } 11: public DateTime DueDate { get; set; } 12:
13: public int DueDays
14: { 15: get
16: { 17: return DueDate.Subtract(DateTime.Now).Days;
18: }
19: }
20: }
The TaskList object is an object that holds a number of Task objects and is the object that will be used as ObjectDataSource. Therefore this class is decorated with the DataObjectAttribute attribute (from the System.ComponentModel namespace). This class has a parameterless constructor and a public method that returns a List populated with Task items.
1: [DataObjectAttribute(true)]
2: public class TaskList
3: { 4: public TaskList()
5: { 6: }
7:
8: [DataObjectMethod(DataObjectMethodType.Select)]
9: public List<Task> LoadByStatus(String status)
10: { 11: List<Task> taskList = new List<Task>();
12: SPSiteDataQuery taskQuery = new SPSiteDataQuery();
13: taskQuery.Lists = "<Lists ServerTemplate=\"107\" />";
14: taskQuery.RowLimit = 10000;
15: String query = String.Format(@"<Where><Eq><FieldRef Name='Status' /><Value Type='Text'>{0}</Value></Eq></Where>", status); 16: taskQuery.Query = query;
17: taskQuery.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"AssignedTo\" /><FieldRef Name=\"Priority\" /><FieldRef Name=\"DueDate\" Type=\"DateTime\"/>";
18: taskQuery.Webs = "<Webs Scope=\"SiteCollection\" />"; ;
19: DataTable projectsTable = SPContext.Current.Web.GetSiteData(taskQuery);
20: foreach (DataRow row in projectsTable.Rows)
21: { 22: Task task = new Task();
23: task.Title = GetFieldValue(row, "Title");
24: task.Priority = GetFieldValue(row, "Priority");
25: task.AssignedTo = GetFieldValueUser(row, "AssignedTo");
26: task.DueDate = GetFieldValueDateTime(row, "DueDate");
27: taskList.Add(task);
28: }
29: return taskList;
30: }
31: }
The function that is called by the ObjectDataSource is LoadByStatus. This takes a status string as a parameter and returns a List of Task items. The class also contains a number of helper methods to get the data from the DataTable. They are irrelevant for this blog post, but they can be found in the source code that you will find in the ZIP file at the bottom of this article.
Step 3 – Create the report
Now that we have our business objects ready, we can create the report. In your Visual Studio project, click “Add new item” and select the “Report” from the Reporting page:
Select the rdlc file you just added in the Solution Explorer and set the Build Action to Embedded Resource.
In the Datasources windows, click the Add New Data Source button:
In the next dialog, select Object as the Data Source Type:
The next step in the wizard asks you to which object you with to bind. Select the Task object, because that is the object we want to use in our report:
In the Report menu of Visual Studio, select Data Sources.
In the dialog select the datasource object you just added, and click Add to report.
Add a table to the report and drag one of the fields to the detail row:
From this point on, it is business as usual (if you are used to building reports in Reporting Services).
Step 4 – Create the reporting web part
Last thing to do it to create a new web part that will render the report. The snippet below shows the part of the CreateChildControls method of the task reporting web part that created the objects we need. Full code (that also creates the dropdownlist and the button) can be found in the ZIP file at the bottom of this post.
1: _reportViewer = new ReportViewer();
2: _reportViewer.Width = new Unit(100, UnitType.Percentage);
3: _reportViewer.Height = new Unit(100, UnitType.Percentage);
4: _reportViewer.Visible = false;
5: _reportViewer.LocalReport.ReportEmbeddedResource = "TST.SharePoint2007.Reporting.TaskReport.rdlc";
6: Controls.Add(_reportViewer);
7:
8: _datasourceTaken = new ObjectDataSource();
9: _datasourceTaken.ID = "datasourceTasks";
10: _datasourceTaken.SelectMethod = "LoadByStatus";
11: _datasourceTaken.SelectParameters.Add(new Parameter("status", System.Data.DbType.String)); 12: _datasourceTaken.TypeName = "TST.SharePoint2007.Reporting.TaskList, TST.SharePoint2007.Reporting, Version=1.0.0.0, Culture=neutral, PublicKeyToken=503edd7b21a430b3";
13: _datasourceTaken.Selecting += new ObjectDataSourceSelectingEventHandler(datasourceTasks_Selecting);
14: Controls.Add(_datasourceTaken);
After creating the ReportView control, the reference to the report file needs to be set. Our report is an embedded resource in the assembly. Therefore we seet the ReportEmbeddedResource property of the LocalReport (in line 5). The full name to load the report is the name of the assembly (TST.SharePoint2007.Reporting) followed by the name of the report (TaskReport.rdlc).
After creating the viewer control, the ObjectDataSource is created. The TypeName property in line 12 is the reference to the business object we created in step 2. The SelectMethod and SelectParameters need to match a public function decorated with the DataObjectMethod attribute in that classe. In my case that is the function LoadByStatus, which has a parameter called status of type String (see step 2). The snippet below shows the implementation of the Selecting event.
1: private void datasourceTasks_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
2: { 3: e.InputParameters["status"] = _getStatus.SelectedValue;
4: }
In this event we get the value of the status dropdownlist (see the first screenshot) and pass the value to the inputparameter of the datasource. Last thing to do it to implement the event handler for the button. When a user clicks the “Generate Report” button, this method is called:
1: private void search_Click(object sender, EventArgs e)
2: { 3: ReportDataSource dataSource = new ReportDataSource("TST_SharePoint2007_Reporting_Task", _datasourceTaken); 4: _reportViewer.LocalReport.DataSources.Clear();
5: _reportViewer.LocalReport.DataSources.Add(dataSource);
6: _reportViewer.Visible = true;
7: _reportViewer.LocalReport.Refresh();
8: }
9:
In this method, a new ReportDataSource object is instantiated and added to the report. Please note that the first parameter of the constructor, the name parameter, needs to match the name of the datasource in the report. This is the name of the data source you selected in step 3 in the Report Data Sources dialog:
The second parameter of the ReportDataSource constructor is a reference to the ObjectDataSource we created in CreateChildControls.
After building and deploying your web part, you should now be able to run the report.
Conclusion
The .NET ReportViewer control is a powerful control to use if you need to do reporting on SharePoint data in SharePoint. It saves you from managing a report server. You don’t need to deploy the report, because it it part of the assembly and you do not have to worry about the double hop issue. Another option would be to run Reporting Services in integrated mode with SharePoint. In our case, that was not an option. We had very specific requirements for the data retrieval process and therefore we moved in this direction.
Full source code for this sample can be found in this ZIP file.