SQL Reporting Module

Overview
The WorkflowGen SQL reporting module uses SQL views to gather important information about your users, requests, and actions, allowing you to create custom data reports.
Technical requirements
WorkflowGen version 7.12.0 or later
SQL Server 2012 or later
Installation
Setup files
The SQL reporting module setup files are located in the \Databases\MsSQLServer\Reporting folder in the manual WorkflowGen installation pack:
1
VIEW_PUB_DIR_V100.sql
2
VIEW_PUB_DIR_USERS_V100.sql
3
VIEW_PUB_DIR_GROUPS_V100.sql
4
VIEW_PUB_DIR_USERS_GROUPS_V100.sql
5
VIEW_PUB_REQ_DATA_V100.sql
6
VIEW_PUB_REQ_DATA_V100T.sql
7
VIEW_PUB_REQ_FILE_V100.sql
8
VIEW_PUB_REQ_FILE_V100T.sql
9
VIEW_PUB_REQ_COMMENT_V100T.sql
10
VIEW_PUB_REQ_ACTION_V100.sql
11
VIEW_PUB_REQ_ACTION_V100T.sql
12
VIEW_PUB_REQ_ACTION_DATA_V100.sql
13
VIEW_PUB_REQ_ACTION_DATA_V100T.sql
14
VIEW_PUB_REQ_ACTION_FILE_V100.sql
15
VIEW_PUB_REQ_ACTION_FILE_V100T.sql
16
CreateReportingDatabase.sql
Copied!
Installing the SQL views
To install the SQL views:
1.
Open SQL Server Management Studio (SSMS) or Azure Data Studio.

2.
Connect to your WorkflowGen database (e.g. WFGEN).

3.
Run each of the VIEW_PUB_*.sql files to create the SQL views.
If you have existing SQL views in your WorkflowGen database from an earlier version, reinstall them using these steps.
SQL reporting database and replication (optional)
For better performance without overloading the main WorkflowGen database, you can create a dedicated SQL reporting database and replicate the SQL views data to their specific tables. 
This requires the SQL Server replication service and SSMS. The SQL views must be created as described in the Installing the SQL views section above.
Setup procedure overview
1.
In SSMS, run CreateReportingDatabase.sql to create the SQL reporting database on another SQL Server database instance.

2.
Set up your WorkflowGen database as a Publisher and a Distributor to publish all the SQL views (e.g. VIEW_PUB_*).

3.
Set up the dedicated SQL reporting database as a Subscriber to retrieve the data from the Publisher in real-time data replication. For each of the SQL views, configure the respective table to receive the data from the view. For example:
The DIR_V100 table gets data from the VIEW_PUB_DIR_V100 view
The REQ_FILE_V100 table gets data from the VIEW_PUB_REQ_FILE_V100 view
The dedicated SQL reporting database should now be ready to use for custom data reports.
If you have an existing SQL reporting database from an earlier version, update the affected tables and columns accordingly, then reconfigure the data replication service (Publisher, Distributor, and Subscriber).

Synchronizing a Directory in Command Line

Azure Integration

Last modified 1yr ago

Copy link

Contents

Overview

Technical requirements

Installation

Setup files

Installing the SQL views

SQL reporting database and replication (optional)

Setup procedure overview