Use Report Builder 2012 to Visualize Your SharePoint Data

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
  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  
  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.   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
  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.


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

For more information about gaining a competitive advantage with digital transformation, contact Red Level today.

Related Posts

Stay Up-To-Date.
Subscribe to The Red Letter

– Red Level's quarterly email featuring the people, ideas and events IT pros need to know.

Modern Workplace Webinar Series

How does Carhartt use technology to foster better communication and collaboration? Join us as we learn from Michigan business leaders – what works, what to avoid, and what's next.
[ read more ]