This project is read-only.

SharePoint Reporting Wizard

Reporting on data within SharePoint can be a real headache and modifying the SharePoint databases is not supported.

We built the SharePoint Reporting Wizard to make reporting easier by exposing all lists / document libraries within a site as views in a special reporting database. Once the views are created they can be used by reporting services or another tool to create powerful reports.

The tool currently supports SharePoint 2007, 2010 and 2013.

To use the tool you will need:

  1. At least read access to the SharePoint Content Database
  2. Access to a SQL database server & database in which to create the views

Note: the views you generate should not be created in the SharePoint Content Databases!

  1. On the SQL server that hosts SharePoint content database create a new database e.g: SintelSPReportingWizard (this step is optional as the tool can include the SQL code to do this for you)
  2. Run the "SharePoint Reporting Wizard" and configure the various options
  3. Click "Generate Script" button (this will generate the SQL scripts required to create the views and copy them to your clipboard)
  4. Launch Microsoft SQL Server Management Studio and execute the SQL scripts (if you want to create the views in a particular database then make sure to execute the scripts against that database)
  5. Finally now that the views have been created in the database you selected you need to add a login for the purpose of running reports. Create a login and grant it read rights to both the source SharePoint content database and the SintelSPReportingWizard database

Click here to watch the Youtube Video

SPReportingWizard

Example:

The following example shows a script generated for a SharePoint list called Comments

/***********************************************************
Comments
***********************************************************/
IF NOT (OBJECT_ID('dbo.spView_Comments') IS NULL)
BEGIN
DROP VIEW dbo.spView_Comments
END
GO

CREATE VIEW dbo.spView_Comments
AS
SELECT
r0.tp_ID AS ID, r0.tp_Version AS Version, r0.tp_Author AS Author, r0.tp_Editor as Editor, r0.tp_Modified AS Modified, r0.tp_Created AS Created
,r0.nvarchar1 AS [Title]
,r0.ntext1 AS [_ModerationComments]
,r0.nvarchar2 AS [File_x0020_Type]
,r0.int1 AS [Line]
,r0.ntext2 AS [Description]
FROM
WSS_Content_iTracker.dbo.AllUserData r0 WITH (NOLOCK)
WHERE
r0.tp_ListId = 'fe64bf66-aa50-45f3-87b5-1ddb425b27c3' AND r0.tp_IsCurrentVersion = 1 and r0.tp_DeleteTransactionId = 0 and r0.tp_RowOrdinal = 0
GO

 

This application was created by Sintel Business Solutions: www.sintel.ie

Last edited Aug 14, 2015 at 1:57 PM by michalg, version 21