Skip to end of metadata
Go to start of metadata

A quite common requirement at the end stages of a project is to create "on-demand" reports on the data within TM. Reports help management and operational users clearly see data that could even be PII data. One benefit of creating reports that are presented within a formspace is that we are not responsible for the delivery of PII data. The results are displayed on screen or within a CSV file and it's up to the end user to safely and securely download and store the file. Reports can be heavily customized to show data from submission objects, data extracts, or form xml. Remember that the form xml will be purged based on data retention settings. Reports can also be quite processor heavy so special care should come with load testing. 

Let's walk though a very simple report that uses the Core API to query submissions. Typically I use the Core API because the Fluent API doesn't have a start and end date as part of the querying criteria. Start and end dates are a standard for most reporting criteria. Most reports are based on submissions and the all DAOs query off of submission date except for the TransactionHistoryDAO which queries off of form start/opened date. 

The following example only shows how to display 1 report but the architecture is setup in a way to easily add more reports. 

Form Development Step-by-step guide

After creating a new blank form with your customer's branding:

  1. Delete all non-essential components, especially the submit and save buttons. We don't want any transactions getting submitted to TM or the customer will incur a fee. 
    1. On the Maguire form I deleted: Wizard Nav Bar, Form Footer, Function Bar, Navigation Second Level, Navigation Chevrons, Job Details Block, and everything within the "Content" Page Controller.
  2. Add a Page named "Reporting" to the Content Page Controller.
  3. Add a Section named "Report Criteria" to your "Reporting" Page.
  4. Add a dropdown named "Report Service Name" to your "Report Criteria" Section. We will only have one report but this is where you can add multiple reports if required. 
    1. Add one option to the dropdown. Make the label "Form Submissions" and the value has to be the name of the Groovy Service that creates the report data. We'll call our service "Report-Form Submissions by Date".

  5. Add a block called "Criteria1" to the "Reporting Criteria" Section.
    1. The reason for the block is to add visibility rules if you have different reports with different criteria.
  6. Add a 2 date fields called "Start Date" and "End Date".
    1. Prefill the "Start Date" and "End Date" with today's date.
    2. Add Validation that they must be Past Dates and Include Today.
    3. Make both fields Mandatory.
    4. Add the following validation rule to "End Date" (optional):

      ValidationRule
      if (Calc.daysBetween(data.startDate, data.endDate) > 7)
               return "Date range cannot exceed 7 days.";
  7. Add a Text Display field beside the dates that states the valid date range. i.e. "Date range cannot exceed 7 days."
  8. Add a dropdown called "Output Format" with 2 options: CSV and HTML to the "Reporting" Section. 
  9. Add a "Run Report" button. 
  10. Add a Block called "OutputBlk" to house all the hidden data and output fields. 
  11. Add a Text Display field with "Output records have been returned." to the "OutputBlk".
  12. Add a "recordsReturned", "rawData", and "outputReturned" Data Fields to the "OutputBlk".
  13. Add a "Download CSV" Button to the "OutputBlk" with the following Visibility rule:

    data.outputreturned === "CSV"

  14. Add a "Show HTML" Button to the "OutputBlk" with the following Visibility rule:

    data.outputreturned === "HTML"

  15. At this point, all the necessary fields have been added. Now it's time to insert the logic to the fields. 
  16. Add the following "Click" script to the "Run Report" button:

    Run Report Click
    var runReport = function()
    {
        data.rawdata = null;
        data.recordsreturned = 0;
        data.outputreturned = data.outputFormat;
    
        Form.showProgress("Your report is being generated...");
    
       var inputData = {
           startDate: data.startDate,
           endDate: data.endDate,
           reportServiceName: data.reportServiceName,
           outputType: data.outputFormat 
       }
       DynamicData.call("GenerateDynamicDataReport", inputData).then(
           function(response) {
               Form.showProgress("");
               console.log(response);
               data.rawdata = response.data;
               data.recordsreturned = response.recordsReturned;
       }).catch(
           function(err) {
               Form.showProgress("");
               console.log(err); 
           }
       );
    }
    
    Form.validate("data.reportCriteria").then(
       function(response) {
           if (response.valid == true) {
               runReport(); 
           } else {
               return;
           }
       }
    ).catch(
        function(err) {
            console.log(err); 
        }
    );
  17. Add the following Click script to the "Download CSV" Button:

    Download CSV Click
    function requiredFeaturesSupported() 
    {
        return ( BlobConstructor() && msSaveOrOpenBlobSupported() );
    }
    function BlobConstructor() 
    {
        return (window.Blob) 
    }
    function msSaveOrOpenBlobSupported() 
    {
        return (window.navigator.msSaveOrOpenBlob) 
    }
    function b64toBlob(b64Data, contentType, sliceSize) 
    {
        contentType = contentType || '';
        sliceSize = sliceSize || 512;
        var byteCharacters = atob(b64Data);
        var byteArrays = [];
        for (var offset = 0; offset < byteCharacters.length; offset += sliceSize) {
            var slice = byteCharacters.slice(offset, offset + sliceSize);
            var byteNumbers = new Array(slice.length);
            for (var i = 0; i < slice.length; i++) {
                byteNumbers[i] = slice.charCodeAt(i);
            }
            var byteArray = new Uint8Array(byteNumbers);
            byteArrays.push(byteArray);
        }
        var blob = new Blob(byteArrays, {type: contentType});
        return blob;
    }
    if (requiredFeaturesSupported()) {
        blobObject = b64toBlob((data.rawdata), "text/csv");
        window.navigator.msSaveOrOpenBlob(blobObject, 'report.csv');
    } else {
        var a = document.body.appendChild(document.createElement("a"));
        a.download = "report.csv";
        a.href = "data:text/csv;charset=utf-8;base64," + data.rawdata;
        a.innerHTML = "download report csv";
        a.hidden = true;
        a.click();
    }
  18. Add the following code to the "Show HTML" Button:

    Show HTML Click
    if (data.rawdata) {
        var win = window.open("", "Report", "toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=yes, resizable=yes, width=780, height=200, top="+(screen.height-400)+", left="+(screen.width-840));
        win.document.body.innerHTML = data.rawdata;
    }

All done... not it's time to write the services! Here's what it should look like:

Groovy Script Development Step-by-step guide

The form is designed to call one generic DDS with the name of the report service as a parameter. There are 3 services which sort of represent a typical 3 layer java framework of "Service Layer", "Manager Layer", and "DAO Layer". Only the "Manager Layer" has to change in most situations since the Submission object that gets returned gives you access to everything you'll need. 

  1. GenericDynamicDataReport - This is the main DDS service that the form calls. It's just passes on the parameters to the right Groovy Service. In a perfect situation you would also handle any bubbled exceptions here too.

    GenericDynamicDataReport
    import com.avoka.core.groovy.GroovyLogger as logger
    import com.avoka.fc.core.service.ServiceLocator
    import com.avoka.fc.core.dao.*
    import com.avoka.fc.core.entity.*
    import groovy.json.*
    
    String reportServiceName = request.getParameter('reportServiceName')
    String startDate = request.getParameter('startDate')
    String endDate = request.getParameter('endDate')
    String outputType = request.getParameter('outputType')
    
    def reportService = ServiceLocator.getServiceForName(reportServiceName)
    Map<String,Object> result = reportService.invokeService([
        startDate:startDate,
        endDate:endDate,
        outputType:outputType
    ])
    
    return JsonOutput.toJson(result)
  2. Report-Form Submissions by Date - This is the name of the service that you configured in your Maestro form. It has to match perfectly with the value of the dropdown in "Report Service Name" and the parameters have to match as well. This service assembles the data returned from the next DAO call. This is where you can get really creative and return back submission properties, data extracts, form XML, etc... But this is where you also have to be careful with performance. The more introspecting and filtering you do the slower your report will be. The "filterData" function 

    Report-Form Submissions by Date
    import com.avoka.core.groovy.GroovyLogger as logger
    import com.avoka.fc.core.dao.*;
    import com.avoka.fc.core.entity.*;
    import com.avoka.fc.core.service.ServiceLocator
    import groovy.json.*
    
    DATEFORMAT = 'yyyy-MM-dd'
    
    String formCode = serviceParameters.formCode  // You'll have to add a service parameter or hardcode your formcode here.
    
    String startDate = parameters.startDate
    String endDate = parameters.endDate
    String outputType = parameters.outputType
    String encodeCsv = parameters.encodeCsv ?: 'false'
    
    //logger.info "---------------------------------------------------"
    //logger.info serviceDefinition.getServiceName()
    //parameters.each { logger.info "$it.key: $it.value" }
    //logger.info "---------------------------------------------------"
    
    def reportHeaders = createReportHeaders()
    def reportData = retrieveData(formCode, startDate, endDate)
    reportData = filterData(reportData)
    
    def output = null
    
    if (outputType.toUpperCase() == "CSV") {
        String csvString = createCsvReport(reportHeaders, reportData)
        if (encodeCsv == 'true')
            output = Base64.getEncoder().encodeToString(csvString.getBytes())
        else
            output = csvString
    } else if (outputType.toUpperCase() == "HTML") {
        StringBuilder reportHtmlStr = new StringBuilder();
        reportHtmlStr.append startHtmlBody()
        reportHtmlStr.append createHtmlReport(formCode, reportHeaders, reportData, startDate, endDate)
        reportHtmlStr.append endHtmlBody()
        output = reportHtmlStr.toString()
    }
    
    return ["data":output, "recordsReturned": reportData.size]
    
    
    /*******************
     * HELPER FUNCTIONS
     *******************/
    def createReportHeaders()
    {
        def reportHeaders = ["ApplicationId"    // 1
                            ,"Submit Date"      // 2
                            ,"Submit Time"      // 3
                            ,"Current Status"   // 4
                            ,"Last Updated"     // 5
                            ]
    
        return reportHeaders
    }
    
    
    def retrieveData(formCode, startDate, endDate)
    {
        def getSubmissionDeliveryListService = ServiceLocator.getServiceForName("GetSubmissionDeliveryList")
        def results = getSubmissionDeliveryListService.invokeService(["formCode":formCode, "startDate":startDate, "endDate":endDate])
        return results
    }
    
    
    def filterData(results)
    {
        StringBuilder dataStr = new StringBuilder();
        def dataTable = [][]
    
        def filteredResults = results.sort { it.timeSubmission }.reverse()
    
        filteredResults.eachWithIndex { it, i ->
            dataTable[i] = [
                it.trackingNumber,                      // 1
                it.timeSubmission.format(DATEFORMAT),   // 2
                it.timeSubmission.format(TIMEFORMAT),   // 3
                it.job?.currentStep?.name, // 4
                it.job?.currentStep?.timeCreated.format(DATETIMEFORMAT) // 5
            ]
        }
    
        return dataTable
    }
    
    
    def createHtmlReport(formCode, reportHeaders, dataTable, startDate, endDate)
    {
        StringBuilder dataStr = new StringBuilder();
    
        dataStr.append "<p>Applications submitted for ${formCode} from ${startDate} to ${endDate}</p>"
        dataStr.append "<table>"
    
        dataStr.append "<tr>"
        reportHeaders.each {
            dataStr.append "<th>${it}</th>"
        }
        dataStr.append "</tr>"
    
        dataTable.each{ it1 ->
            dataStr.append "<tr>"
            it1.each {
                dataStr.append "<td>${it ?: ""}</td>"
            }
            dataStr.append "</tr>"
        }
    
        dataStr.append "</table>"
    
        return dataStr.toString()
    }
    
    
    def startHtmlBody()
    {
        StringBuilder dataStr = new StringBuilder();
        dataStr.append "<!DOCTYPE html><html>"
        dataStr.append '''<head>
    		    <style type="text/css">
    		    body {
    		        font-family: Verdana, Arial, Helvetica, sans-serif;
    		        font-size: 10px;
    		    }
    		    table {
    		        border-collapse: collapse;
    		    }
    		
    		    table, th, td {
    		        font-size: 10px;
    		        padding: 3;
    		        border: 1px solid black;
    		    }
    		    td {
    		        color: #898989;
    		        font-size: 10px;
    		        padding: 3;
    		        text-align: right;
    		    }
    		
    		    </style>
    		</head>'''
        dataStr.append "<body>"
        return dataStr.toString()
    }
    
    
    def endHtmlBody()
    {
        return "</table> </body> </html>"
    }
    
    
    def createCsvReport(reportHeaders, dataTable)
    {
        StringBuilder dataStr = new StringBuilder();
    
        reportHeaders.each {
            dataStr.append "\"${it}\","
        }
    
        dataStr.append "\n"
    
        dataTable.each{ it1 ->
            it1.each {
                dataStr.append "\"${it ?: ""}\","
            }
            dataStr.append "\n"
        }
    
        return dataStr.toString()
    }
  3. GetSubmissionDeliveryList - This "DAO Layer" extracts submission objects for a given date range for a specific formcode. I've played with many DAOs and maybe one suits your purpose more than this but what I've found is that many of the DAOs cut off after a certain limit regardless of what you specify. The requirements I've had in many reports goes beyond these limitations so I've found this script/DAO to be the best and surprisingly performs very well. You can also pass a parameter to include Saved transactions.

    GetSubmissionDeliveryList
    import com.avoka.core.groovy.GroovyLogger as logger
    import com.avoka.fc.core.service.ServiceLocator
    import com.avoka.fc.core.dao.*
    import com.avoka.fc.core.entity.*
    import groovy.json.*
     
    String formCode = parameters.formCode
    Date startDate = Date.parse('yyyy-MM-dd', parameters.startDate)
    startDate.set(hourOfDay : 0)
    startDate.set(minute : 0)
    startDate.set(second : 0)
    Date endDate = Date.parse('yyyy-MM-dd', parameters.endDate)
    endDate.set(hourOfDay : 23)
    endDate.set(minute : 59)
    endDate.set(second : 59)
     
    String includeSaved = parameters.includeSaved ?: 'false'
     
    //logger.info "---------------------------------------------------"
    //logger.info serviceDefinition.getServiceName()
    //parameters.each { logger.info "$it.key: $it.value" }
    //logger.info "---------------------------------------------------"
     
    List deliveryStates = ["Not Ready", "Ready", "Sent Email", "In Progress", "Pending", "Completed", "Error", "Undeliverable", "Not Required"]
     
    String clientId = DaoFactory.getClientDao().getClientByCode("digital").getId();
    String deliveryDetailsId = null
    String deliveryMessageLike = null
    int fetchLimit = 10000
     
    SubmissionDao submissionDao = DaoFactory.getSubmissionDao();
    List allResults = []
     
    deliveryStates.each {
        allResults.addAll(submissionDao.getSubmissionDeliveryList(it,
                                                          clientId,
                                                          deliveryDetailsId,
                                                          formCode,
                                                          deliveryMessageLike,
                                                          startDate,
                                                          endDate,
                                                          fetchLimit))
    }
     
    if (includeSaved == 'true') {
        def savedList = submissionDao.getSavedSubmissionList(null, clientId, null, null, startDate, endDate)
        def filteredResults = savedList.findAll{ it.formCode == formCode }
        allResults.addAll(filteredResults)
    }
     
    //logger.info "Total records retrieved - getSubmissionDeliveryList.size:${allResults.size}"
     
    return allResults

Final Look


Compatibility

Since
Deprecated