Quick and Dirty Reporting for SharePoint 2013 Applications – Part 1

Good restaurants are hard to come by. The combination of tasty food, atmosphere, price, and service is a mix many owners never master. The same is true in using SharePoint as a platform to develop a department sized application. Microsoft Access, Excel and Lotus notes have traditionally played in this “no-code” space. However, SharePoint offers: tables, forms, a workflow engine, but lacks a little in the reporting area. A simple tool I have been using to resolve this shortcoming is Microsoft SQL Server 2012 Report Builder (you can download it for free here). Report Builder is a free graphics interface tool that visualizes data in table or chart formats. Report Builder can connect to numerous data sources, but what I like about it is that a SharePoint list is one of them. One of the core benefits of this tool is that you don’t need professional developer skills or tools to use this tool. With a quick little install, and a little report writing skills (MS Access / Excel) you can improve your SharePoint applications with stunning charts, graphs, and tables.

I have broken this down into two parts in this blog I will cover the building of the report. Next week I will go through displaying the report.

Part 1: Build the Report

1. Create a default Task List app. (Setting > Add an App > Tasks)
2. Download and install Report Builder. This can be done at https://www.microsoft.com/en-us/download/details.aspx?id=29072
3. In the installation include a URL to your SharePoint site as the report server. The configuration does not matter for this scenario.
4. Open Report builder. If you get a Connection error, ignore it and click OK
5. There is a wizard which can be used but I will use the manual method to illustrate more.
6. Click the option: Blank Report
7. Right Click the folder: Data Sources                                                                                 
8. Give the Dataset a meaningful name. I will name mine TasksDS
9. Select the option: Use a connection embedded in my Report
10. Select the connection type: Microsoft SharePoint List
11. Paste the site address into the connection string box
12. Click the button: Text Connection
13. If you get an error: Check the URL 
14. Click the side button: Credentials
15. Select the option: Use current Windows User  
16. Click the button: OK                               
    • Note: the TaskDS is now listed.
17. Right Click the folder: DataSets, Click the option: New Data Set
18. Enter a meaningful name. The dataset is where you select what list from you site you will like to use as the core data for the report.
19. I will enter the name: Tasks_Set
20. Select the option: Use a dataset embedded in my report
21. Select the data source: TaskDS
22. Click the button: Query Designer
23. Place a check in the list you want to use in your report. I will select the list: TeamTasks
24. Click the button: Run Query to get a preview of the data. This is a good check to make sure you have selected the correct list.
25. Click the button: OK
    • Note: that closes the Query Designer
26. Click the button: OK
    • Note: The Dataset properties closes.   
27. Now we can design the report.
28. Click on the text “Click to add title” and add a title. I will add the title: Team Tasks
29. Click on the Insert menu tab
30. Click the ribbon button: Chart wizard
31. Select the Listed Dataset
32. Click the button: Next
33. Select the chart type. I will select Column                                         
34. Click the button: Next
35. On the next screen you design the layout of your chart. You drag the field you need to the different location. I will drag the following:
    1. Categories: Task_Status
    2. Values: Count(ID)
       1. Note: it will default to sum. Click the drop down arrow to change the function.
36. Click the button: Next
37. Select a style. I am going to choose: Generic
38. Click the button: Finish
    • Note: the wizard closes and your new chart is added.
39. Next size the chart and report to be snug to each other and fitting your desires.
    • Note: you can remove chart elements that you do not need by selected and pressing delete.
    • Note in the image below, I removed the legend, Chart Title and execution time.
40. Click the button: Run
41. This will execute the report and show you what the final product will look like.
42. To make changes click the button: Design.
    • Note: that takes you back to the design surface where you can alter the report more.
43. Once you have finished tweaking the report/chart to the way you like, click the Save button
44. Save the report to your local machine. Remember to chose a meaningful name
45. I will put mine in the Documents folder with the name: TeamTask_Status_Chart.rdl  
46. Close Report builder

Now your report is ready to go. In my next post I will dive into how you can visualize these reports in SharePoint.

Reference:

To learn more about how use Report build builder more Microsoft has a great help and walk through at: Report Builder in SQL Server 2016

To learn how to configure your SharePoint 2013 Farm to enable the use of Report Builder reports, complete steps 1-3 in the following article: Install The First Report Server in SharePoint Mode