Export Smart Assessment Responses to Excel/PDF

[ujjwalababa]

Hello All,

I would like to know if there is currently any capability to export Smart Assessment responses to Excel or PDF format.

Our end users have expressed a need to download and retain assessment responses for reporting, review, and audit purposes. Having an export option would significantly improve the user experience and help users manage assessment records more efficiently.

Has anyone implemented a similar solution or are there any plans to support this functionality?

Thank you in advance for your insights.

Thank you

[Hemanth M]

Hi @ujjwalababa ,

There is currently no OOB capability to export responses to Excel or PDF directly, why don't use versioning for any changes in the smart assessment template and you don't have to worry about review/audit trails - it will be there unless you delete.

[prerna_sh]

Hi @ujjwalababa ,

There is currently no OOB functionality to directly export Smart Assessment responses to Excel or PDF.

We have implemented a similar solution for the TPRM External Assessment. In our implementation, we refer to the Smart Assessment Question Instance (sn_smart_asmt_question_instance) table to retrieve the assessment questions and their corresponding responses. We then generate an Excel file with this information and attach it to the External Assessment record.

For the TPRM implementation, we use the following M2M table to establish the relationship between the Smart Assessment and External Assessment:

sn_vdr_risk_asmt_m2m_tpra_sae_template

You can follow a similar approach for Smart Assessment exports by referring to sn_smart_asmt_question_instance for the questions/responses and identifying the appropriate linking/M2M table based on your specific use case. From there, you can generate the Excel/PDF. 

If required, I can provide you the excel generation Script what we have used.

-------------------------------------------------------------------------------------------------------------------------------------------
If my response solves your query, please mark it as helpful by selecting accept as Solution and Helpful. Let me know if anything else is required.
Thanks,
Prerna

[ujjwalababa]

Hello @prerna_sh ,

Thank you for the detailed explanation, that's really helpful context!

Would you be able to share the full implementation including the code you used for the TPRM External Assessment export? Specifically, it would be great to see,

How you query the sn_smart_asmt_question_instance table
How you handled the M2M table relationship
The Excel generation script you mentioned

Having the actual code as a reference would make it much easier for us to adapt the solution for our use case.

Thanks again for your help!

[prerna_sh · Accepted solution]

Hi @ujjwalababa ,

You can create a Business Rule on the Smart Assessment Instance (sn_smart_asmt_instance) table. Configure it as an After Update with a condition such as State changes to Complete, meaning the Business Rule will execute when the Smart Assessment has been completed.

You can also add an additional condition specific to your assessment if required. Otherwise, you can proceed with just the State changes to Complete condition.

 and use the Created Script Include in the BR, you'll get the Excel on your assessment.

// 1. Get External Assessment from M2M

var extM2M = new GlideRecord('sn_vdr_risk_asmt_m2m_tpra_sae_template');
extM2M.addQuery('sae_questionnaire_instance', smartAssessmentInstance);
extM2M.query();

if (!extM2M.next()) {
    return;
}
var externalAssessmentSysId = extM2M.getValue('vendor_risk_assessment');


// 2. Get Question Instances

var questionGR = new GlideRecord('sn_smart_asmt_question_instance');
questionGR.addQuery('assessment_instance', smartAssessmentInstance);
questionGR.orderBy('order');
questionGR.query();


// 3. Start Excel XML

var excel = '';
excel += '<?xml version="1.0"?>';
excel += '<?mso-application progid="Excel.Sheet"?>';
excel += '<Workbook ' +  'xmlns="urn:schemas-microsoft-com:office:spreadsheet" ' + 'xmlns:o="urn:schemas-microsoft-com:office:office" ' + 'xmlns:x="urn:schemas-microsoft-com:office:excel" ' + 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
excel += '<Worksheet ss:Name="Assessment Responses">';
excel += '<Table>';


// 4. Header
excel += '<Row>';
excel += '<Cell><Data ss:Type="String">Question</Data></Cell>';
excel += '<Cell><Data ss:Type="String">Response</Data></Cell>';
excel += '<Cell><Data ss:Type="String">Justification</Data></Cell>';
excel += '</Row>';


// 5. Process Questions

while (questionGR.next()) {
    var questionRecord = questionGR.getElement('assessment_question').getRefRecord();
    if (!questionRecord.isValidRecord()) {
        continue;
    }

    // Get Question
    var question = questionGR.getDisplayValue('assessment_question');
    // Get Selected Response
    var response = questionGR.getDisplayValue('selected_response_options');
    }

    // Add Question + Response 
    excel += '<Row>';
    excel += '<Cell><Data ss:Type="String">' + escapeXML(question) + '</Data></Cell>';
    excel += '<Cell><Data ss:Type="String">' + escapeXML(response) + '</Data></Cell>';
    excel += '</Row>';
}


// 6. Close Excel XML
excel += '</Table>';
excel += '</Worksheet>';
excel += '</Workbook>';


// 7. Get External Assessment
var assessmentGR = new GlideRecord('sn_vdr_risk_asmt_assessment');
assessmentGR.addQuery('sys_id', externalAssessmentSysId);
assessmentGR.query();
if (!assessmentGR.next()) {
    return;
}


// 8. Create Attachment

var attachment = new GlideSysAttachment();

var fileName = 'Assessment responses(' + extM2M.getDisplayValue('sae_questionnaire_template') + ')-' +assessmentGR.getValue('number') + '.xls';
var attachmentSysId = attachment.write( assessmentGR, fileName, 'application/vnd.ms-excel', excel);
return attachmentSysId;


// 9. XML Escape Function

function escapeXML(value) {
    if (value === null || value === undefined) {
        return '';
    }

    value = String(value);
    value = value.replace(/&/g, '&amp;');
    value = value.replace(/</g, '&lt;');
    value = value.replace(/>/g, '&gt;');
    value = value.replace(/"/g, '&quot;');
    value = value.replace(/'/g, '&apos;');

    return value;
}

Also, you can put conditions for the responses you got as per your requirement.

-------------------------------------------------------------------------------------------------------------------------------------------
If my response solves your query, please mark it as helpful by selecting accept as Solution and Helpful. Let me know if anything else is required.
Thanks,
Prerna