Building a Better Satisfaction Survey: Part 1 – Under the Hood

This is the first part of a two-part offering is a series of modifications to the Altiris Helpdesk Satisfaction Survey. The goal of these customizations is to increase the effectiveness of the survey, and mitigate a potentially detrimental side effect that exists in the out-of-the-box solution. The idea for this customization came from a couple of my recent customers, and I appreciate all of the thought they put into it.

The Satisfaction Survey

Boiling something as nebulous as customer satisfaction down to simple numbers is tricky, to say the least. The default questions do a decent enough job of attempting to separate the various factors that make up overall satisfaction in the context of IT-related problem management, and for that reason I'm going to leave the questions themselves alone. We are, however, going to change how Helpdesk handles the data involved.

Figure 1: The Satisfaction Survey

Currently, when the survey questions are answered, the data is written in the comments section of the incident. The answer to the overall satisfaction question is also written to the "workitem_rating" field, and is later the ONLY thing used for the out-of-the-box reports. The two main problems with this design that I have come across are:

1. Users have no anonymity when responding to the survey
2. We are unable to report on individual performance metrics
3. If using the randomizer, we are unable to determine the actual number of surveys that have been sent

I will address these three points over the next two parts of this article. Ultimately, the goals of the customization are:

• To create more granular data for consumption.
• To [optionally] remove the scores and comments from the incidents themselves so that customers can answer anonymously.
• To provide a method for tracking the number of surveys sent versus the number of surveys completed.
• To provide a sample satisfaction report for use with this customization.

The Customization Objects

This customization consists of the following elements:

• A SQL script to create additional fields in the workitem table
• A custom revision of the SatisfactionSurvey.ascx file
• An entry into the custom.config file

Creating the Custom Fields

I find it easiest to gather all of the custom fields I will require and leverage a single SQL script to create them all at once. Having to go back later isn't the end of the world, but it is a logical starting point for the customization. For more information on how this script was generated, please refer to the Helpdesk Solution Product guide sections on adding Scalar fields. Execute the following SQL commands against the Altiris_Incidents database:

ALTER TABLE workitem ADD satisfaction_overall int NULL,
				 satisfaction_responsiveness int NULL,
				 satisfaction_expertise int NULL,
				 satisfaction_information int NULL,
				 satisfaction_professionalism int NULL,
				 satisfaction_comments varchar(max) NULL,
				 satisfaction_trigger int NULL
GO

EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_overall AS workitem_satisfaction_overall', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_responsiveness AS workitem_satisfaction_responsiveness', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_expertise AS workitem_satisfaction_expertise', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_information AS workitem_satisfaction_information', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_professionalism AS workitem_satisfaction_professionalism', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_comments AS workitem_satisfaction_comments', N''
GO
EXEC sp_addviewdef N'workitem_only_view', N'workitem.satisfaction_trigger AS workitem_satisfaction_trigger', N''
GO
EXEC sp_createHDDview 'workitem_only_view', 'workitem'
GO
EXEC sp_createHDDview 'workitem_detail_view', 'workitem_only_view'
GO

if exists (select * from sysobjects where id =
object_id(N'[dbo].[workitem_current_view]') and OBJECTPROPERTY(id,
N'IsView') = 1)
drop view [dbo].[workitem_current_view]
GO

CREATE VIEW dbo.workitem_current_view
AS
SELECT workitem_detail_view.*
FROM workitem_detail_view
WHERE (workitem_is_last = N'1')
GO

IMPORTANT: After executing the above code against the Altiris_Incidents database, go to the "Incidents Settings" section of your Notification Server console and hit "Apply." This will force a recreation of the HD_workitem views in the Altiris database, which we will need for reporting purposes.

These fields are being created in the "workitem" table, and are, for the most part, directly aligned with the questions asked in the satisfaction survey. We will be modifying the satisfaction survey to write the answers to each of its questions back to the appropriate field rather than [only] to auxdata and the comment field. The "satisfaction_trigger" field will be used to track when surveys are sent out rather than individual answers to survey questions - I will go into more detail on that in Part 2.

Modifying SatisfactionSurvey.ascx

The page that actually builds and runs the Admin Report is called "SatisfactionSurvey.ascx" and is located in the ".\AeXHD\Templates" directory. Copy this file into the ".\AeXHD\custom" folder, and rename the copied file to "customSatisfactionSurvey.ascx." Open your newly renamed file with your text editor of choice; to find a starting point, search for the "Commit" sub-routine but make no changes:

Public Overrides Sub Commit(ByVal ControlID As String, ByVal CurrentPageID As String)

	Dim rating as integer = 0
	Dim nvpairs As New System.Text.StringBuilder(100)
      Dim fmtnvpair As String = ResourceManager.GetString("fmtRatingNameValue", False, True)
      Dim w as WorkItemDataSet = DataStore("WorkItem")
   	Dim dt as Data.DataTable
	Dim dc(1) as Data.DataColumn	
	Dim dr as Data.DataRow
	Dim foundAnswer as boolean = false

The following section is where the majority of our customization is performed. It is here that we are changing the fundamental data flow of the Satisfaction Survey. Each of the following "if-then" statements below represent a question of the satisfaction survey, including the free-form comments entry. I have commented out the line of code beginning with "nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString(:", and prevented the survey from writing information back into the comments section of the incident by doing so. If you still want the information available in the comments section as well, simply uncomment each of those lines by removing the leading apostrophe.

In the lines beginning with "w.current.item("workitem_satisfaction_", we are simply passing the answers from each of the survey questions to the appropriate custom field we created earlier:

	if Not rblOverallExperience.SelectedItem is nothing then 
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblOverallExperience", False, True), rblOverallExperience.SelectedItem.Value)
		w.current.item("workitem_satisfaction_overall") = rblOverallExperience.SelectedItem.Value
		foundAnswer = True
	end if
		
	if Not rblResponsiveness.SelectedItem is nothing then 
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblResponsiveness", False, True), rblResponsiveness.SelectedItem.Value)
		w.current.item("workitem_satisfaction_responsiveness") = rblResponsiveness.SelectedItem.Value
		foundAnswer = True
	end if	
	
	if Not rblTechnicalExpertise.SelectedItem is nothing then 
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblTechnicalExpertise", False, True), rblTechnicalExpertise.SelectedItem.Value)
		w.current.item("workitem_satisfaction_completion_time") = rblTechnicalExpertise.SelectedItem.Value
		foundAnswer = True	
	end if
	
	if Not rblInformation.SelectedItem is nothing then 
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblInformationProvided", False, True), rblInformation.SelectedItem.Value)
		w.current.item("workitem_satisfaction_information") = rblInformation.SelectedItem.Value
		foundAnswer = True	
	end if	
	
	if Not rblPrefessionalism.SelectedItem is nothing then 
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblProfessionalismCourtesy", False, True), rblPrefessionalism.SelectedItem.Value)
		w.current.item("workitem_satisfaction_professionalism") = rblPrefessionalism.SelectedItem.Value
		foundAnswer = True	
	end if
	
	if tbComment.text <> String.Empty then
		'nvpairs.AppendFormat(fmtnvpair, ResourceManager.GetString("sidLblAdditionalComments", False, True), tbComment.text)
		w.current.item("workitem_satisfaction_comments") = tbComment.text
		foundAnswer = True	
	end if

IMPORTANT: In the above code, the word "professionalism" is sometimes spelled correctly and sometimes spelled "prefessionalism" - these mis-spellings are part of the original code, and should be left as-is to avoid potential errors.

The next section checks to see if the survey has already been completed for the incident in question. We are simply changing the auxdata member name from "survey_complete" to "custom_survey_complete" so that we can differentiate between the old and new versions of the survey when reporting:

	
	if Not rblOverallExperience.SelectedItem is nothing then 		
		rating = Ctype(rblOverallExperience.SelectedItem.Value, integer)
		dt = w.AuxData.AllData
		dc(0) = dt.Columns("name")
		dt.PrimaryKey = dc
		dr = dt.rows.find("custom_survey_complete")		
		
		if dr is nothing orelse Not dr("value").tolower = "true" then					
			w.BeginEdit()
			w.Current.workitem_rating = rating
			w.Current.workitem_source = "Rating"
			w.Current.workitem_action = ResourceManager.GetString("sidActionRate")
			
			if foundAnswer orelse rating <> 0 then
				w.Current.workitem_comment = ResourceManager.GetString("fmtIncidentRatingComment", false, True, nvpairs.ToString)

In this final section of code, we are changing the name of the auxdata member to reflect that we are dealing with a custom satisfaction survey just as above. We will leverage this change in our reporting; older incidents that filled out the out-of-the-box satisfaction survey will still show up on the out-of-the-box report. Only incidents that have used this newer version of the survey will show up in the sample reports provided in Part 2:

				'Add flag to AuxData indication that the survey has already been completed
				dt = w.AuxData.AllData
				dr = dt.NewRow()
				dr("member") = "satisfaction_survey"
				dr("name") = "custom_survey_complete"
				dr("value") = "true"
				dt.Rows.Add(dr)		
				w.AuxData.AllData = dt
			else
				w.Current.workitem_comment = ResourceManager.GetString("sidIncidentNoRatingComment", false, True)
			end if			
			w.Commit()
		else
			lblAlreadySubmitted.Visible="true"
		end if
	End if
End Sub

Custom.config

If you're not already using a custom.config file for other customizations, go ahead and create one using an XML editor in the root of you Helpdesk directory. This file is used as a central reference point to the files we've created so far. The contents of this file should contain the following code:

<?xml version="1.0" encoding="utf-8"?>
<custom.configuration>
              <files path="~/custom/">
				  <file id="SatisfactionSurvey" file="customSatisfactionSurvey.ascx"/>
		  </files>
</custom.configuration>

Save your custom.config file and restart IIS.

Conclusion

Success! Your satisfaction survey should now look like this:

Figure 2: Satisfaction Survey post-customization

That's right. It should look exactly the same. How the survey functions, however, is a different story entirely. The score for each individual question now resides in a unique location, easily accessible for reporting purposes. We're no longer limited to "overall" as our only measure of satisfaction or performance. If you chose to prevent the results of the survey from posting to comments, your end users' answers are now, if not completely anonymous, at least obfuscated from the worker who assisted them. Additionally, the workers themselves will no longer have the ability to see individual survey results, instead relying on overall results as a measure over time - far more useful information as a Key Performance Indicator.

Thanks for reading Part 1! Be sure to check back for Part 2 of the Satisfaction Survey customization, where we'll take an in-depth look at consuming the additional data we've created.