QlikSense Writeback demo, Approvals Workflow for dimensional values
One Directional Write-back demo: This scenario allows users to add notes/comments in the form of chat window to individual transactions within the app. This is a one way write-back where an external web page is collecting notes data from users within the sense application and writes the notes in an SQL(access) database. Userid, current selections & selected dimensional value is communicated to the external web page using URL parameters and these data values are written to SQL database along with the user entered notes.
Bi-directional Write-back demo: This demo takes the first part (adding user notes) of writeback demo one step further by adding a workflow approval process. Approval decisions & notes are captured from users are first written to an external database and then used to move records up & down the approval steps within the sense app by feeding this data back in to the data model . In this scenario, users can route individual transactions through a 3 step workflow approval process. (Analyst -> Review -> Coordinator). An external custom web page allows qliksense data transactions to be submitted, approved or rejected based on their current location within the workflow process. Individual records(OrderIDs) will move up & down the approval process based on user decisions until they are finalized. Qliksense app stores the current workflow steps & the approval decisions within the data model where historical notes and previous decisions are fetched from the external database and displayed within the app as users select different transactions. Once a decision is made for a given record, web page will write these user inputs in to an Access database and will remove all input controls so no one can make additional decisions/notes for that record until the app data is refreshed via reload where record's approval decision value stored within the sense app is synchronized with what is stored in the external sql database.
Demo setup consists of a sample QlikSense App using a MediaBox extension to display an intermediate aspx .net page for capturing user inputs in terms of comments and approval decisions as well as displaying them. Setup also includes IISExpress that can be triggered to run in the background to host the .net aspx page.
Click on the image to see the animated demo of Part #1
Video of demo part #2
Below Visual shows the inner workings of this demo.
Current selections and selected transaction id is transmitted using a dynamic URL padded with parameters.
MediaBox extension displays the dynamic URL which renders the custom web page. This pages displays previous decisions and notes for a given transaction and it also allows users to make approval decisions using buttons.
Once a decision is made to either submit, approve or reject a transaction, the input is interpreted by custom business logic built in to the custom page to move the record up or down the workflow process by assigning it a new workflow step. This data is immediately written to an access database as part of the write-back process. New data is also used to refresh the page to display the new messages for the current transaction.
Finally Qliksense task or manual reload button(extension) is used to reload the qliksense app which in turn refreshes the data model using the newly written data from the external access database. Once the reload is done, SetAnalysis filters are used to simulate moving records from one approval queue to another.
Unzip the file and place Qlik_WriteBack folder directly under your C:\ drive so 1-Start_Writeback.bat file is in the following path "C:\Qlik_WriteBack\1-Start_Writeback.bat"
Deploy the following two extensions using the attached zip files (Skip this step if you already have these extensions😞
MediaBox.zip for MediaBox extension
mhamano-Qlik-Sense-Reload-Button-344eb7b.zip for the reload button extension. (This extension allows you to trigger a reload within the QlikSense UI. An alternate solution for enterprise version is to create a reload task and trigger it from QMC either manually or automatically at short intervals)
Run "C:\Qlik_WriteBack\1-Start_Writeback.bat" file to start the IISExpress that hosts the aspx page.
Open the WriteBack_Workflow_Sample.qvf app file either in QS desktop or via enterprise edition. ( If using enterprise version, access the hub via HTTP. IISExpress is hosting the page on HTTP and most browsers will not allow an HTTP page to displayed within a page running on HTTPS )
To show Bi-Directional write-back: Open Approval-Combo sheet and hold CTRL + Click on different ORDERID values in analyst table to enter comments and submit them for approval. Once you submit a few records, click on reload button or triiger a QS task to reload the app which should move those records to corresponding approval steps based on your decisions. A reload is necessary after one or multiple approval actions to visually see the transactions move within the qliksense app.
To show one-directional write-back: Open the details sheet and hold CTRL + Click on different ORDERID values to see if any notes attached. Once a single ORDERID is selected, you add notes for that order by entering text on the right and clicking on SEND button. User name & Date stamp is added to each note. User name is Me for Desktop version but will use the actual QS username if you are using the enterprise edition.
Resetting the demo: Once you go through the motions and finish the demo, you can reset the entire setup by performing the next steps:
Navigate to Reset Data sheet and click on CLEAR DATA button. (Nothing visually will happen but all records will be deleted in the access database)
Trigger a reload using the Reload Extension or a Task.
All notes and approval decisions are written to an ACCESS database using a MsJet OLEDB connection.
Database is located: C:\Qlik_WriteBack\App_Data\Qlik_WriteBack.mdb
One-directional sample notes from Details sheet are stored in COMMENTS table. Table includes notes plus another column that captures the users current selections when a note was posted.
Multi-direction workflow sample notes and decisions are stored in Approvals table along with user's current selections.
To add this user comment feature in to your existing applications:
Find a sheet with some visuals where you can select unique dimension values such as InvoiceNo, Category or ProductName.
Place a MediaBox extension on to that page.
Set the following parameters for the MediaBox. Replace the [DimensionName] with column name that you would like to associate the user comments with.