BrightWork Reporter SQL Server Feed White Paper

Introduction

The BrightWork Reporter SQL Server Feed allows you to use the reporting engine of BrightWork Reporter to extract information from BrightWork and/or SharePoint and feed it to a Microsoft SQL Server Reporting Services database.

The purpose of this is to allow work on large volumes of data in an environment that is built for reporting on volume data. This document assumes that you have a database and a table ready to export to.

Intended Audience

This document is intended for administrators with XML and SQL Server Database experience.

Using the BrightWork Reporter Data Feed

The BrightWork Reporter SQL Server Feed uses ExportReportToDb.exe, a manifest XML file and a .cmd file that can be ran at a specific time by Windows Task Scheduler.

To use the BrightWork Reporter SQL Server Feed you must:

Create the appropriate folder structure.
Create a manifest XML file.
Create a command file to run the manifest XML file. and the ExportReportToDb.exe.
Create a scheduled task to run the command file.

Create Folder Structure

You should create a folder structure to store the BrightWork Reporter SQL Server Feed files that you will be working with.

Note: Files in these folders will not be impacted by upgrading or reinstalling BrightWork Reporter or BrightWork.

To create the folder structure:

Login to your server and navigate to
- SharePoint 2007: Drive:\Program Files\BrightWork\12\ExportReportToDb
- SharePoint 2010: Drive:\Program Files(x86)\BrightWork\14\ExportReportToDb
- SharePoint 2013: Drive:\Program Files(x86)\BrightWork\15\ExportReportToDb
- SharePoint 2016: Drive:\Program Files(x86)\BrightWork\16\ExportReportToDb
- SharePoint 2019: Drive:\Program Files(x86)\BrightWork\16\ExportReportToDb
Create the following 3 subfolders:
- \ExportCommandFiles
- \ExportLogs
- \ExportManifests

Create Manifest XML File

The Manifest XML file is where:

The report to run is defined
The data to be exported and where it is to be exported to is defined

A sample manifest file is included at:

SharePoint 2007: Drive:\Program Files\BrightWork\12\ExportReportToDb\sample manifests\DataFeedManifest.xml
SharePoint 2010: Drive:\Program Files(x86)\BrightWork\14\ExportReportToDb\sample manifests\DataFeedManifest.xml
SharePoint 2013: Drive:\Program Files(x86)\BrightWork\15\ExportReportToDb\sample manifests\DataFeedManifest.xml
SharePoint 2016: Drive:\Program Files(x86)\BrightWork\16\ExportReportToDb\sample manifests\DataFeedManifest.xml
SharePoint 2019: Drive:\Program Files(x86)\BrightWork\16\ExportReportToDb\sample manifests\DataFeedManifest.xml

The Manifest XML file consists of a header, footer and 2 nodes:

SourceData
TargetData

Header and Footer

Every manifest XML file must have the below header and footer.

Header

<?xml version="1.0" encoding="utf-8" ?>
<CubeFeedManifest xmlns="http://schemas.brightwork.com/CubeFeedManifest/v2">

Footer

</CubeFeedManifest>

SourceData Node

The SourceData node is where the report that is to be run is defined. There are 2 options:

webPart
This is where you specify a specific web part on a specific page.
QueryFile
This is where you point the BrightWork Reporter SQL Server Feed at a Report Definition file.

Note: For performance reasons, we recommend using the QueryFile method as opposed to the webPart method. Another advantage of this approach is that the web part does not have to have been actually added to a web part page.

Web Part SourceData Node

Nodes and Attributes	Description
<SourceData type="webPart">	This is where you specify a specific web part on a specific page.
<webPart pageUrl= "http://server/sites/site/Projects/default.aspx"	You must specify the URL of the page the BrightWork Reporter web part is installed on.
webPartId="d13b1737-3ded-464d-8cf8-b7ab0eedcd37" />	You must specify the web part id - please see Identify the web part ID for more information on how to obtain the web part ID.
</SourceData>	This is the closing tag for the SourceData node.

QueryFile SourceData Node

Nodes and Attributes	Description
<SourceData type="QueryFile">	This is where you point the BrightWork Reporter SQL Server Feed at a Report Definition file.
<QueryFile fileSourceType=" "	Specify the gallery type that the Report Definition file is stored in. There are 3 options: fileSourceType="BrightWork" This is the BrightWork Gallery that contains the reports supplied with BrightWork Reporter OR fileSourceType="CustomShared" Specifying CustomShared means that the scheduler will look in the BrightWork Reporter Library (see the BrightWork Reporter Library topic for more information). The library must be in the same site collection as the site specified in the rootHref (see below). OR fileSourceType="CustomSourceHref" Specifying CustomSourceHref means you can use a Report Definition file stored in any document library on the server. You must provide the server relative URL of the document library that the file is stored in the filePath attribute.
filePath=" "	Specify the name and location of the Report Definition file that you want to run. The information you supply here is dependent on the fileSourceType attribute. Sample file paths include: <QueryFile fileSourceType="BrightWork" filePath="OpenWork.xml"/> <QueryFile fileSourceType="BrightWork" filePath="Templates\Projects - Summary.xml"/> <QueryFile fileSourceType="CustomShared" filePath="OpenWorkCopy.xml"/> <QueryFile fileSourceType="CustomSourceHref" filePath="/sites/documentation/Projects/MSF/ Document%20Library/Document.xml" />
scope=" "	Specify the scope of the report you want to run. There are 3 options: scope="SpecifiedWeb" This is the site specified in the rootHref attribute. OR scope="SpecifiedWebAndSubWebs" This is the site, and its subsites, specified in the rootHref attribute. OR scope="QuerySources" This is where the sites(s) are specified in the Report Definition file. To avoid anomalies you should ensure that the users who have access to the rootHref also have access to the sites specified in the QuerySource node in the Report Definition file.
roothref="http://server/sites/site/Projects/mysite" />	Here you specify the site BrightWork Reporter is running in. BrightWork Reporter does not actually have to be added to a site; however, so that it can have a context for generating the report you must specify the URL of a site.
</SourceData>	This is the closing tag for the SourceData node.

TargetData Node

The Target Data node is for specifying what columns data should be exported from, where the data should be exported to and how it should be exported.

Nodes and Attributes	Description
<TargetData	This is the opening tag for the TargetData node.
connection=" "	Update the connection attribute to specify the SQL Server connection string to be used. Note: If the connection string includes quotation marks (???) they should be replaced with " in the Manifest XML file. Sample connection strings include: Standard Security connection="Server=DBSERVER;Database=DBNAME;User ID=name;Password=password;Trusted_Connection=False" Trusted Connection connection="Data Source=DBSERVER;Initial Catalog=DBNAME;Integrated Security=SSPI" Via an IP Address connection="Data Source=192.168.1.100,1433;Network Library=DBMSSOCN;Initial Catalog=DBNAME;User ID=name;Password=password"
tableName="Table Name"	Update the tableName attribute to the name of the table in your SQL database that you want to map to.
clearTable="true">	Set the clearTable attribute to be true if you want to delete the data stored in the table every time the SQL Server Feed is ran. Note: The default setting is false.
<ColumnMappings>	The ColumnMappings section specifies the mapping between the columns in the BrightWork Reporter Datatable and the Datatable in the SQL server database. Note: Non-field values may also be exported if required. Use the below mappings: Site Name: SiteName List Name: ListName Web URL: WebHref List URL: ListHref List Item URL: ItemHref
<Column sourceName= "SharePointColumnName"	The sourceName attribute should be set to the DataTable column name used in the Report Definition file.
targetName= "TargetSQLServerDBFieldName" />	The targetName attribute should be set to the corresponding table column name in the SQL database.
</ColumnMappings>	This is the closing tag for the ColumnMappings section.
</TargetData>	This is the closing tag for the TargetData node.

Create Command File

The command file is the file you tell the Windows Task Scheduler to run. It tells the BrightWork Reporter SQL Server Feed application (ExportReportToDb.exe) which Manifest XML file(s) to run and where to save the Log file(s).

Note: The examples used in this section presume the folder structure was created as set out in Create Folder Structure.

To create a command file:

Open a text editor and create a file with the following format:
[relative path to ExportReportToDb.exe] -m [relative path to Manifest XML file] > [relative path to the new Log file ]
For example, if you have created the folder structure as described in Create Folder Structure, the line would look something like:
..\bin\ExportReportToDb.exe -m ..\ExportManifests\Manifest.xml > ..\ExportLogs\ExportReport.log
Give the file an appropriate name and a .cmd extension (e.g. openwork.cmd), and save it to
- SharePoint 2007: Drive:\Program Files\BrightWork\12\GenerateReport\ExportCommandFiles
- SharePoint 2010: Drive:\Program Files\BrightWork\14\GenerateReport\ExportCommandFiles
- SharePoint 2013: Drive:\Program Files\BrightWork\15\GenerateReport\ExportCommandFiles
- SharePoint 2016: Drive:\Program Files\BrightWork\16\GenerateReport\ExportCommandFiles
- SharePoint 2019: Drive:\Program Files\BrightWork\16\GenerateReport\ExportCommandFiles

Note: ExportReportToDb.exe will be automatically added to the appropriate location. Do not copy this application into the folder as it will fail.

The manifest XML file, the cmd file and the log file can be called anything.

The log file contains details of who ran the process, when it was run and what emails were sent and not sent.

Running Multiple Reports from a Single Command File

You can have run multiple reports using the one command file, for example:
..\bin\ExportReportToDb.exe -m ..\ExportReportToDb\Report1.xml > ..\ReportLogs\ExportReport1.log
..\bin\ExportReportToDb.exe -m ..\ExportReportToDb\Report2.xml > ..\ReportLogs\ExportReport2.log
..\bin\ExportReportToDb.exe -m ..\ExportReportToDb\Report3.xml > ..\ReportLogs\ExportReport3.log
..\bin\ExportReportToDb.exe -m ..\ExportReportToDb\Report4.xml > ..\ReportLogs\ExportReport4.log

Task Scheduler

Use the Windows Task Scheduler on your server to run the command file at the desired time.

To schedule a new task

Open the Windows Task Scheduler
Select Create Basic Task wizard and follow the steps to schedule the CMD file

Note: You can also run the command file on demand by double-clicking on it.

For more information on the Windows Task Scheduler please consult the Windows Server help.

Tips

Identify the Web Part ID

To identify the web part ID, follow the steps below:

Navigate to the page containing the BrightWork Reporter web part.
Select Email in the Reporter Ribbon.
Right-click on the page and select View Source.
Search for wpGUID.
The numbers and characters after wpGUID= (excluding the " at the end) is the web part ID.
E.g. wpGUID=6ee19d6f-4ba8-4799-bda5-dc9c87bee158

Note: If the report includes Query Parameters (e.g. a My Work report), they will be appended to the end of the web part ID and will appear after the &, - this additional text is not part of the web part ID.

For example:&CurrentUser=21