How to create Reports on SharePoint Lists

Your SharePoint lists are all set up and they are full of interesting data – but until that data is presented in an aggregated fashion, it will be pretty difficult to make it useful. Your users need to be able to compare, contrast, create ad hoc reports and assess the information. Your high level folks need dashboards, scorecards, KPIs and gauges.

You need reporting options!

• 13 Charting Tools to help build a SharePoint dashboard
• Ever wanted to write ‘SELECT * FROM” a SharePoint List?

Out-of-box Solutions

SharePoint has some reporting options built in:

• Create your own views from the settings page
  • Pros: can be for all users or just for you, allows filtering (including the Me filter, which displays just your items)
  • Cons: can’t join two lists, have to have correct permissions
  • How-to links:
    • http://office.microsoft.com/en-us/sharepoint-server-help/create-modify-or-delete-a-view-HA010377693.aspx 
    • http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-create-a-view.aspx
• Create views using SharePoint Designer
  • Pros: variety of layout options, ability to join two lists
  • Cons: SharePoint Designer learning curve, easy to break things
  • How-to links:
    • https://www.nothingbutsharepoint.com/sites/eusp/Pages/How-to-Link-Two-Lists-and-Create-a-Combined-View-in-SharePoint-2010.aspx
    • http://office.microsoft.com/en-us/sharepoint-designer-help/create-a-custom-list-view-using-sharepoint-designer-HA010379079.aspx

More Flexible

Sometimes you need to be able to visualize the data in ways that aren’t possible in a list view. More flexible options include

• Excel workbook linked to list(s)
  • Pros: familiar interface, powerful calculation and analysis engines
  • Pros: new features like PowerPivot and PowerView allow for data modelling and aesthetically pleasing reporting
  • Pros: PowerPivot Gallery in SharePoint (on-prem only at this time) allow you to publish and share easily
  • How-to links:
    • http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx
    • http://www.powerpivotpro.com/what-is-powerpivot/
    • http://www.youtube.com/watch?v=3xhwOEGjhxw
• Access database linked to list(s)
  • Pros: Access can be used to report on complex data spanning two or more lists
  • Pros: easy to read & write, familiar interface for some users
  • How-to links:
    • http://www.youtube.com/watch?v=KBA-Sz72Spw
    • http://office.microsoft.com/en-us/windows-sharepoint-services-help/export-or-link-a-datasheet-view-to-access-HA001160860.aspx

Most Scalable

Enterprise reporting solutions can help you keep up on your data even as it grows.

• SQL Server Reporting Services
  • Pros: powerful reporting tool, can use lookup functions (2008 R2 or later) or sub-reports to join list data
  • Pros: Report Builder integrated with SharePoint
  • Pros: can be scheduled or subscribed to
  • Cons: requires server install of product
  • How-to links:
    • http://dataqueen.unlimitedviz.com/2012/07/create-a-sharepoint-ssrs-report-library/
    • http://aproblemshared.fusionmanageit.co.uk/2012/08/aggregated-lookups-in-ssrs.html
    • http://msdn.microsoft.com/en-us/library/ee210649(v=sql.105).aspx
• PerformancePoint
  • Pros: reusable KPIs, integration with other Microsoft products (including SSRS reports!)
  • Pros: can consume data from an Excel PowerPivot model
  • Cons: requires SharePoint Enterprise or E3/E4 for Office 365
  • Cons: some features only available for data cubes
  • How-to links:
    • http://msdn.microsoft.com/en-us/office/bb660518.aspx
    • http://www.youtube.com/watch?v=5VOG4fXr3v4
    • http://blog.technock.net/2013/03/sharepoint-2010-performance-point-score.html
    • http://gnetgroup.wordpress.com/2011/11/03/building-a-performancepoint-dashboard-without-a-cube/
    • http://blog.netnerds.net/2012/03/sharepoint-2010-creating-dashboards-and-charts-for-sharepoint-lists-using-performancepoint-and-excel-services/

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

• YouTube Video highlighting the above technique:
  • ttp://www.youtube.com/watch?v=EC2b61W60IU&list=PL581899A129522F53
• Qdabra Toolkit includes detailed how-to instructions and sample templates:
  • http://www.qdabra.com/webstore/p/75/qdabra-webinar-kit-reporting-with-performancepoint