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:

  1. Create the appropriate folder structure.
  2. Create a manifest XML file.
  3. Create a command file to run the manifest XML file. and the ExportReportToDb.exe.
  4. 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.

To create the folder structure:

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

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.
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 &quot; 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.

<ColumnMappings>

The ColumnMappings section specifies the mapping between the columns in the BrightWork Reporter Datatable and the Datatable in the SQL server database.

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

To create a command file:

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

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

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

Tips

Identify the Web Part ID

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

  1. Navigate to the page containing the BrightWork Reporter web part.
  2. Select Email in the Reporter Ribbon.
  3. Right-click on the page and select View Source.
  4. 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

Was this article useful?

Back to Top