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.

Schema

If you use Microsoft Visual Studio, or another software package that can validate code, you can download and include the DataFeedManifest.xsd schema file in your project.

To use this file in Microsoft Visual Studio, add the schema file to the current solution and click on the XML menu option at the top of the screen and click Validate XML Data.

Intended Audience

This document is intended for BrightWork administrators with XML experience and with experience in working with SQL Server Databases.

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 Drive:\Program Files\BrightWork\12\ExportReportToDb\sample manifests\ DataFeedManifest.xml.

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

• SourceData
• Target Data

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.

WebPart SourceData Node

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

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 inCreate 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 and follow the instructions in the Scheduled Task Wizard to run the CMD file when you want to email
   • Windows 2003: Click Start | Control Panel | Scheduled Tasks | Add Scheduled Taskto open the Task Scheduler.
   • Windows 2008: Click Start | Programs | Administrative Tools | Task Scheduler and click Create Basic Taskto open the Task Scheduler.

Tips

Tip 1 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 Data on the web part menu.
3. Right-click on the page and selectView Source.
4. Search for wpGUID.
   The numbers and characters after wpGUID= (excluding the " at the end) is the web part ID.
   
   wpGUID=6ee19d6f-4ba8-4799-bda5-dc9c87bee158

Tip 2 How to identify the real SharePoint column name

If a column is renamed, SharePoint retains the original column name. The original column name is the one that BrightWork Reporter needs. There are a few ways to identify the real column name.

From the Column Setting Page URL

1. Click Settings | List Settings.
2. Click the Link belonging to the column whose name you want to identify.
3. The column name will be appended to the page URL, for example:
   http://sitecolection/sites/site/list/_layouts/15/FldEditEx.aspx?List=%7B41B7A352%2D7FD4%2D4D34%2D8DE6%2DF542D45A58AD%7D&Field=AssignedTo

From the Column Settings Page

1. Click Settings | List Settings.
2. Click the Link belonging to the column whose name you want to identify.
3. Right-click and select View Source.
4. Do a search for ‘Field=’. Whatever is returned is the field name, for example:
   Field=AssignedTo

From the Business Data Catalog Columns

To find out the underlying SharePoint name of a Business Data Catalog column:

1. Open a view where the column is displayed.
2. Right-click and select View Source.
3. Search for the Displayname of the Business Data Catalog column whose real SharePoint name you want to find out.
   The real name will be displayed before the Displayname, as shown in the below image.