# 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 WorkflowGen manual 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](/tech/sql-reporting-module.md#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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.workflowgen.com/tech/sql-reporting-module.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.