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.
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 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.
Data Source=[ServerName]\[Instance];Initial Catalog=NW2010DB_Config;Integrated Security=SSPI
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!