Scheduled Database E-mail Reporting with Nintex

Problem

The customer required a Quality Management System (QMS) in order to create and modify documentation of their work processes and safety instructions. They need this system in order to comply with regulations and audits. This system has been designed by Portiva and consists of the following elements:

1 site collection

12 departmental Authoring subsites

12 departmental Published subsites

1 site collection reusable Nintex workflow that requests approval to several managers and, if approved, converts the document to PDF and routes the PDF to the departmental Published site

Muhimbi for Word to PDF conversion

The system has been running for a few weeks but then the customer found out that the out of the box Nintex reporting was not sufficient. Because there are 12 subsites with running workflows it is hard to monitor and administer all running workflows from a centralized view.

Solution

To fill this ‘gap’ I created a solution that automatically sends an email report to a designated group of managers. This reports shows all running workflows and open tasks without having to visit every single subsite.

Because this reporting solution is a Site Workflow it is possible to schedule it to run weekly. And if someone requires to have a report immediately, he can request this by starting the Site Workflow manually (just three clicks).

The workflow

The workflow consist of the following technical aspects:

Dictionary variable

Execute SQL action

Call web service

XML variable

XSL transformation

Workflow constants

I will now elaborate the main points of the workflow.

Phase 1 – Build site dictionary

The first step in the workflow is to build a dictionary variable that contains information of all websites in the site collection (Url, Title and ID). This dictionary will be used later in the workflow. The reason for creating a dictionary is simple: performance. The workflow needs to request data from the dictionary for every result record (probably more than 100 results). Requesting data from the dictionary is much more efficient than calling web services every time we need website metadata (in this case I would have to call the same web service >100 times).

Phase 2 – Execute SQL action

This is the point where the Nintex data will be retrieved. First, I created a connection string to the NW2010DB_Config database and saved it in a workflow constant called NW Connection String.

There are a lot of stored procedures in this database that can be used. For this workflow I used the rptWorkflowTasks stored procedure. This gives me back all running workflow tasks in my site collection.

Phase 3 – Process SQL results

Each result column is stored in a collection variable. In this workflow I loop through each index in the collWorkflowInstanceIDs and store each value of the same index of the other collection variables in a text variable. I need to use the same index because the index actually represents the row number of the SQL output.

Phase 3.1 – Get SharePoint metadata

For each result record the workflow requests the SharePoint metadata using the Query List action. The metadata of the related document will also be displayed in the final report.

Phase 3.2 – Fill XML variable

An XML node will be created for every returned SQL record. This happens in a Build string action. See the picture below for the configuration of this action.

As you can see all records will be transformed to a XML node in the variable textXmlWorkflowLinks.

Phase 3.3 – Fill CSV variable

Not only a HTML report will be sent, but I will also attach a CSV file with the email. The manager only needs to open the email attachment and he or she can begin analyzing the data immediately.

Phase 4 – Transform XML with XSLT

In this step the textXmlWorkflowLinks will be transformed to HTML using XSLT. The output of this action is the textHtmlWorkflowLinks variable that contains the HTML code of the report.

Phase 5 – Send HTML mail

The final step of the workflow sends the HTML report to a specific SharePoint group or the initiator of the workflow.

In the email workflow action I also attach the content of the CSV workflow variable so it will be sent as a CSV file attachment.

The picture below shows the HTML formatted email (also note the attachment QMS-Open-Tasks-572014 832.csv). The managers are now able to track all open tasks in their QMS system from a single email!