Introduction
Report Builder 3.0 is a relatively simple tool used to generate reports and other data visualizations against many different data sources, sql, oracle, an access database. For our purposes, we are using it to connect to a SharePoint list in order to be able to generate meaningful visualizations and reports against that list data. Report builder is good way to generate visualizations that add meaning to reports and SharePoint sites. Here are a few examples of the visualizations:
Setting up the environment, creating a report and displaying within SharePoint are covered in this post.
Install Report Builder 3.0
Report Builder 3.0 is available for free from Microsoft from here. Download and install.
Environment
You will require SharePoint Enterprise 2010 in order to create a report library, with reporting services, and the following feature needs to be enabled:
If you have sufficient permissions, follow the directions following to activate this feature. If not, contact your farm administrator to see if these features/services can be enabled.
Turn on Enterprise Site Collection Features
1. Navigate to the site you are going to create reports on.2. “Site Actions” -> “Site Settings”.
3. “Site Settings” -> “Manage Site Features”.
4. Click “Activate” beside the item “SharePoint Server Enterprise Site features”.
5. “Site Actions” -> “Site Settings”.
6. “Site Collection Administration” - > “Site collection features”.
7. Click “Activate” beside the item “SharePoint Server Enterprise Site features”.
You will also need Reporting Services configured and installed on your farm.
Verify reporting services is enabled
1. Navigate to the site you are going to create reports on.2. “Site Actions” -> “More Options” and choose the “Library” Filter.
3. You should see “Report Library”. If not, contact your SharePoint farm administrator to activate it or follow the below directions on activating if you have permissions to the SharePoint Central Administration Site.
Enable Reporting Services (requires admin access to SharePoint Central Admin)
1. Open the SharePoint 2010 Central Administration Site.2. Choose “General Application Settings”.
3. You should see the category “Reporting Services”. Click “Reporting Services Integration”:
4. Ensure “Activate feature in all existing site collections” is selected:
5. Click “OK”.
6. Exit the Central Administration site, go back to your hosting site, and now confirm that “Form Library” is one of the options available.
Create the Report Library
The first step to creating a SharePoint report is to create the report library in SharePoint. Creating a report library automatically associates the report content type with this library. The report content type is then recognized by SharePoint, and reports are rendered within SharePoint using Sequel Server Reporting Services without any further configuration.
1. Navigate to the SharePoint site that you wish to have the report library on.
2. “Site Actions” -> “More Options”.
3. “Filter By:” -> “Library”.
4. Select “Report Library”:
5. Name your report library and click “Create”.
Create a Report Builder Report
Before you can create a report builder report within SharePoint, you need to have a SharePoint list to report against. We are assuming that this list is already created, and that we will be binding directly to that list with our report. The list in this example is a task list, this list was generated by SharePoint from a Microsoft Project file. The techniques used here to generate a report can be used for any list.
We are creating a “gauge” data visualization, the same techniques can be used for any data visualization (chart, table, etc.).
Create a blank report and add Data Source and Data Set
1. Start Report Builder 3.0.2. Choose “New Report” and click “Blank Report”.
3. Place your mouse pointer over the “Title” section and delete. Place your pointer over the “Execution Time” box and delete:
4. This should leave you with a blank report. In the report data window, right click on “Data Sources” and choose “Add Data Source”.
5. Name your data source with a name that identifies what the data source is.
6. Choose “Use a connection embedded in my report” (if the report is going to be deployed to different environments once completed, you may wish to create an external connection and bind to it here).
7. In “Select connection type:” choose “Microsoft SharePoint List”.
8. In the “Connection String” window, paste in the URL of the SharePoint Site that hosts the list that you are connecting to. Remove any trailing information, so that the link looks like:
http://www.yoursite.com/projects/Form_Project
9. Click “Credentials” in the left window and choose “Use current Windows User”.
10. Click “General” again and then click “Test Connection”. If the URL is correct and you have adequate permissions to the list, you should see:
12. Right click on “Datasets” in the Report data window:
14. By default, “Query” should be selected in the left navigation window. Select “User a dataset embedded in my report”.
15. For the “Data source”, choose the data source you just created.
16. Click the “Query Designer” button.
17. Find your list on the list of lists in the “SharePoint Lists” section, and click the “+” sign beside that list.
18. Put a checkmark in every field you will need to use in your report:
20. Click “OK”.
21. You should now have a Data Set Properties window similar to:
22. Click “OK”.
Create Test Data Table and Project Completed Gauge
Once you have your form data connection and data set created, you can now report on the values returned by the Dataset. You can use any of the Visualization tools for this purpose, our demonstration uses the out of the box “Gauge” visualization to show how close to complete a specific project is (based on the task list).
1. First, we are going to insert temporary “Test” table that simply displays all of the values returned by our dataset. While still on the insert tab, choose “table”. Choose “Table Wizard”.
2. Choose your recently created dataset. Click “Next”.
3. Drag your dataset fields into the “E Values” section. We will not group by columns or rows for simplicity, since this is a testing table, we just want to see the raw data returned by our dataset/Data Source combination. Don’t worry about the “Sum” function it may place around some of your fields.
4. Click “Next”, then “Next” again. Click “Finish”.
5. Drag your table to a clear area of the report for display:
8. Choose “Gauge” from the top menu.
9. Using your cursor, drag a square on the report where you would like the gauge to appear.
10. Next select the Gauge type from the selection screen, we choose the “180 degrees north” selection:
Click OK.
14. You should now have a green indicator bar extending for most of the dial:
Create Gauge Pointer Expression
Now we want a single value that will represent the proportion of the project that has been completed, based on the “Percent Completed”. We are going to approach this by giving an average of percent completes set in the associated table. This weights each task equally, no consideration has been given for tasks that take more or less time.If we want to calculate the average of any group of numbers, we add them together, then divide by the total number of values. We will apply that approach here. First, we want to get a total count of the number of rows (and therefor tasks) associated with this report. Do this by:
1. Click on the pointer on your gauge.
2. Right click and choose “Pointer Properties”.
3. In the “Value” field, click the expression button (fx).
4. Click the “+” by “Miscellaneous” and double click on “RowNumber”. You should now see:
5. Now click the category “Datasets”, and select any of the values (by double clicking):
6. Remove all formatting except for the name of your dataset, so that it now looks like:
7. Now click inside the expression so that your cursor is flashing in front of “RowNumber”.
8. If “Datasets” hasn’t been selected in the “Category” field, select it again.
9. Double click “Sum(ID_Complete)”:
10. Complete the formula by first multiplying by 100, then dividing by the row number total, as follows:
12. You should now be able to click “Run” and preview your form.
Publish and Expose Your Report
1. Save the report you created in your prior steps to the report library. You should be able to click on the report, and view the report in the built in SSRS report viewer:
2. Now navigate to the page you wish to display the gauge on.
3. Click “Site Actions” and choose “Edit Page”.
4. Click “Add a web part” in the appropriate column.
5. Choose “SQL Server Reporting” Category, and choose “SQL Server Reporting Services Report Viewer”:
Click “OK”.
6. The Report Viewer Web Part configuration will come up:
7. Click “Click here to open the tool pane”. This will open the report viewer tool pane:
8. Click the three dots browse button on the tool pane, this will bring up the local sites libraries.
9. Select your reports library, then choose the report you just added:
10. Click “OK”.
11. Click “View” to expand the View menu on the report viewer web part, and remove all checkmarks from the various tools so they won't display with our guage*:
12. Click the “+” sign by the “Appearance” section, title your report viewer. You will probably want to reduce the area used by your report to in the web part as much as possible. This will take tweaking, but use the Height and Width fields to precisely define, as follows:
13. Click “OK”.
14. Tweak the report viewing area by changing height and width until the gauge is correctly positioned with no scrollbars.
*SharePoint will reset default these values to included each time you attempt to modify a different setting, such as width and height. Remember to uncheck these values each time you modify the overall report viewer web part properties.
Next Steps
Here are some further ideas once you are familiar with report builder:
1. Create a report that includes data and gauges for client information:
No comments:
Post a Comment