Use Report Builder 2012 to Visualize Your SharePoint Data

Use Report Builder 2012 to Visualize Your SharePoint Data

2019-07-02T18:46:40+00:00By |App Development, SharePoint|

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 OKQDR2.png
  5. There is a wizard which can be used but I will use the manual method to illustrate more.
  6. Click the option: Blank ReportQDR3.png
  7. Right Click the folder: Data Sources                                                                                 QDR4.png
  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 QDR5.png
  14. Click the side button: Credentials
  15. Select the option: Use current Windows User  QDR6.png
  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           QDR7.png
  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.   QDR8.png
  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                                                                              QDR9.png
  32. Click the button: Next
  33. Select the chart type. I will select Column                                         QDR10.png
  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. QDR11.png
  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. QDR12.png
  39. Next size the chart and report to be snug to each other and fitting your desires.QDR13.png
    • 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.QDR14.png
  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  QDR15.png
  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

About the Author:

Ryan Charnock