# 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 2014 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:

```
VIEW_PUB_DIR_V100.sql
VIEW_PUB_DIR_USERS_V100.sql
VIEW_PUB_DIR_GROUPS_V100.sql
VIEW_PUB_DIR_USERS_GROUPS_V100.sql
VIEW_PUB_REQ_DATA_V100.sql
VIEW_PUB_REQ_DATA_V100T.sql
VIEW_PUB_REQ_FILE_V100.sql
VIEW_PUB_REQ_FILE_V100T.sql
VIEW_PUB_REQ_COMMENT_V100T.sql
VIEW_PUB_REQ_ACTION_V100.sql
VIEW_PUB_REQ_ACTION_V100T.sql
VIEW_PUB_REQ_ACTION_DATA_V100.sql
VIEW_PUB_REQ_ACTION_DATA_V100T.sql
VIEW_PUB_REQ_ACTION_FILE_V100.sql
VIEW_PUB_REQ_ACTION_FILE_V100T.sql
CreateReportingDatabase.sql
```

### Installing the SQL views

To install the SQL views:

1. Open SQL Server Management Studio (SSMS) or Azure Data Studio.<br>
2. Connect to your WorkflowGen database (e.g. `WFGEN`).<br>
3. Run each of the `VIEW_PUB_*.sql` files to create the SQL views.

{% hint style="info" %}
If you have existing SQL views in your WorkflowGen database from an earlier version, reinstall them using these steps.
{% endhint %}

## 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.&#x20;

This requires the SQL Server replication service and SSMS. The SQL views must be created as described in the [Installing the SQL views](#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.<br>
2. Set up your WorkflowGen database as a `Publisher` and a `Distributor` to publish all the SQL views (e.g. `VIEW_PUB_*`).<br>
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.

{% hint style="info" %}
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`).
{% endhint %}