Survey’s entity in Voice of customer provides us with a report to export responses. However, when you export, you see the questions where multiple responses are enabled are exported as multiple columns. Our customer is used to a format where these multiple responses come as one column with values separated by a delimiter. As the survey responses are expected to be lower in number, we ended up creating a custom workflow which will read questionresponse entity and generate csv file in Desired format and attaches the file to survey. One drawback of this approach is to run cleanup jobs to clear these responses to reduce storage usage.
Lets jump into the code and understand how we can do this. Below is the initial method which retrieved survey responses, exports them into CSV file format and then attaches to Annotation entity associated with Survey.
public void ExportSurveys(IOrganizationService service, Guid surveyId, ITracingService trace)
{
trace.Trace($"Retreive SurveyDates for Survey Id {surveyId}");
Entity _surveyData = GetSurveyDetails(service, surveyId, trace);
trace.Trace("ExportSurveyBusinessLogic.ExportSurveys method invoked");
List<Dictionary<string, object>> _responsesCollection =new List<Dictionary<string, object>>();
trace.Trace($"Retreive SurveyResponses for Survey Id {surveyId}");
EntityCollection _surveyResponsesColection = GetSurveyResponses(service, surveyId, trace);
trace.Trace("Loop through each survey response retrieved");
Dictionary<string, string> qGroup = new Dictionary<string, string>();
DataTable dtQuestion = CreateDataTable(service, surveyId, trace,out qGroup);
foreach (Entity _surveyResponse in _surveyResponsesColection.Entities)
{
string surveyRespondant = _surveyResponse.Contains(SurveyResponseConstants.ATTRIBUTESURVEYRESPONDANT) ? _surveyResponse.Attributes[SurveyResponseConstants.ATTRIBUTESURVEYRESPONDANT].ToString() : string.Empty;
DateTime? publishedDate = _surveyData.Contains(SurveyConstants.ATTRIBUTEPUBLISHEDDATE) ? (DateTime)_surveyData.Attributes[SurveyConstants.ATTRIBUTEPUBLISHEDDATE] : (DateTime?)null;
DateTime? closedDate = _surveyData.Contains(SurveyConstants.ATTRIBUTECLOSEDDATE) ? (DateTime)_surveyData.Attributes[SurveyConstants.ATTRIBUTECLOSEDDATE] : (DateTime?)null;
trace.Trace($"Retreive QuestionResponses for Surveyresponse Id {_surveyResponse.Id}");
EntityCollection _questionResponsesCollection = GetQuestionResponses(service, _surveyResponse, trace);
if (_questionResponsesCollection.Entities.Any())
{
trace.Trace("group results by question so you get collection of responses by question.");
var _groupedQuestionResponses = from result in _questionResponsesCollection.Entities
group result by new
{
question = (EntityReference)result.Attributes[QuestionResponsesConstants.ATTRIBUTEQUESTION]
} into groupresult
select groupresult.ToList();
trace.Trace("Loop through each question groud retrieved");
DataRow dr = dtQuestion.NewRow();
dr[DatatTableConstants.DATACOLUMNCLOSEDDATE] = closedDate;
dr[DatatTableConstants.DATACOLUMNPUBLISHEDDATE] = publishedDate;
dr[DatatTableConstants.DATACOLUMNRESPONDANT] = surveyRespondant;
foreach (var _questiongroup in _groupedQuestionResponses)
{
trace.Trace("Call process question for each question group from group results");
ProcessQuestion(service, _questiongroup, ref dr, trace);
// _responsesCollection.Add(ProcessQuestion(service, _questiongroup, dtQuestion, trace));
}
dtQuestion.Rows.Add(dr);
}
}
trace.Trace("Generate CSV from the dictonary created above");
StringBuilder csv =ExportDictionaryToCSV(dtQuestion, trace, qGroup);
trace.Trace("Attach CSV file to Survey");
AttachExcelToSurvey(service,surveyId,csv, trace);
trace.Trace("csv file assoiation complete");
}
Below is the process question method, which is used to read each question-response record and based on the type of the question, process the response in desired format.
private void ProcessQuestion(IOrganizationService service, List<Entity> _questiongroup,ref DataRow dr, ITracingService trace)
{
trace.Trace("ExportSurveyBusinessLogic.ProcessQuestion method invoked");
Dictionary<string, object> _surveyResponse = new Dictionary<string, object>();
if (_questiongroup.Count <= 1)
{
foreach (Entity questionResonse in _questiongroup)
{
Entity _question = GetQuestionType(service, (EntityReference)questionResonse.Attributes[QuestionResponsesConstants.ATTRIBUTEQUESTION], trace);
OptionSetValue _questionType = ((OptionSetValue)_question.Attributes[QuestionConstants.ATTRIBUTEQUESTIONTYPE]);
trace.Trace($"Questiontype retrieved: {_questionType.Value}");
string colunName = _question.Attributes[QuestionConstants.ATTRIBUTEQUESTIONTEXT].ToString() + "_" + ((EntityReference)questionResonse.Attributes[QuestionResponsesConstants.ATTRIBUTEQUESTION]).Id.ToString();
int optionvalue = _questionType.Value;
switch (optionvalue)
{
case (int)QuestionType.DateTime:
dr[colunName] = GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEVALUEASDATE);
break;
case (int)QuestionType.Singlelineoftext:
case (int)QuestionType.Multiplelinesoftext:
case (int)QuestionType.Singleresponseoptionset:
dr[colunName] = GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEANSWER);
break;
case (int)QuestionType.Numeric:
dr[colunName] = GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEVALUEASDECIMAL);
break;
case (int)QuestionType.Rating:
dr[colunName] = GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEVALUEASDECIMAL);
break;
//_questiongroup.Select(p => ((EntityReference)p.Attributes[QuestionResponsesConstants.ATTRIBUTEANSWER]).Name)
}
}
}
else if (_questiongroup.Count > 1)
{
StringBuilder sb = new StringBuilder();
string question = string.Empty;
foreach (Entity questionResonse in _questiongroup)
{
Entity _question = GetQuestionType(service, (EntityReference)questionResonse.Attributes[QuestionResponsesConstants.ATTRIBUTEQUESTION], trace);
OptionSetValue _questionType = ((OptionSetValue)_question.Attributes[QuestionConstants.ATTRIBUTEQUESTIONTYPE]);
trace.Trace($"Questiontype retrieved: {_questionType.Value}");
question = _question.Attributes[QuestionConstants.ATTRIBUTEQUESTIONTEXT].ToString() + "_" + ((EntityReference)questionResonse.Attributes[QuestionResponsesConstants.ATTRIBUTEQUESTION]).Id.ToString();
int optionvalue = _questionType.Value;
switch (optionvalue)
{
case (int)QuestionType.Multipleresponseoptionset:
if (string.IsNullOrEmpty(sb.ToString()))
sb.Append(GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEANSWER));
else
{
sb.Append(";");
sb.Append(GetAttributeValue(questionResonse, QuestionResponsesConstants.ATTRIBUTEANSWER));
}
break;
}
}
dr[question] = sb.ToString();
}
}
Here are few retrieval methods where it is important to note that Order of the questions is added to our query. This helps us in presenting questions in same order as our survey is designed. I am not presenting all retrieval and support methods as they are common to any plugin. Based on your requirement you can retrieve and update CRM.
private EntityCollection GetQuestionResponses(IOrganizationService service, Entity _surveyResponse, ITracingService trace)
{
trace.Trace("ExportSurveyBusinessLogic.GetQuestionResponses method invoked");
QueryExpression query = new QueryExpression()
{
EntityName = QuestionResponsesConstants.ENTITYQUESTIONRESPONSE,
ColumnSet = new ColumnSet(true),
Criteria = new FilterExpression()
{
Conditions =
{
new ConditionExpression(QuestionResponsesConstants.ATTRIBUTESURVEYRESPONSE, ConditionOperator.Equal, _surveyResponse.Id)
}
},
Orders =
{
new OrderExpression(QuestionResponsesConstants.ATTRIBUTEORDERINDEX,OrderType.Ascending)
}
};
return ExecuteQuery(service, query, trace);
}
private EntityCollection GetSurveyResponses(IOrganizationService service, Guid surveyId, ITracingService trace)
{
trace.Trace("ExportSurveyBusinessLogic.GetSurveyResponses method invoked");
QueryExpression query = new QueryExpression()
{
EntityName = SurveyResponseConstants.ENTITYSURVEYRESPONSE,
ColumnSet = new ColumnSet(true),
Criteria = new FilterExpression()
{
Conditions =
{
new ConditionExpression(SurveyResponseConstants.ATTRIBUTESURVEY, ConditionOperator.Equal, surveyId)
}
}
};
return ExecuteQuery(service, query, trace);
}
Finally, exporting to CSV and associating to survey code is here
private void AttachExcelToSurvey(IOrganizationService service, Guid surveyId,StringBuilder stringb, ITracingService trace)
{
trace.Trace("ExportSurveyBusinessLogic.AttachExcelToSurvey method invoked");
byte[] data = Encoding.ASCII.GetBytes(stringb.ToString());
Entity note = new Entity(AnnotationConstants.ENTITYANNOTATION);
note[AnnotationConstants.ATTRIBUTESUBJECT] = OtherConstants.SUBJECTFIELDVALUE;
note[AnnotationConstants.ATTRIBUTEFILENAME] = OtherConstants.FILENAMEFIELDVALUE;
note[AnnotationConstants.ATTRIBUTEDOCUMENTBODY] = Convert.ToBase64String(data);
note[AnnotationConstants.ATTRIBUTEOBJECTID] = new EntityReference(SurveyConstants.ENTITYSURVEY, surveyId);
trace.Trace("Creating annotation entity");
service.Create(note);
}
private StringBuilder ExportDictionaryToCSV(DataTable dtQuestions, ITracingService trace,Dictionary<string,string> qGroup)
{
trace.Trace("ExportSurveyBusinessLogic.ExportDictionaryToCSV method invoked");
StringBuilder csv = new StringBuilder();
IEnumerable<string> columnNames = dtQuestions.Columns.Cast<DataColumn>().
Select(column => column.ColumnName.Contains("_")? RemoveGuid(column.ColumnName.Contains(",")? column.ColumnName.Replace(",","|") : column.ColumnName) : column.ColumnName);
csv.AppendLine(GetQuestionGroup(columnNames, qGroup));
csv.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dtQuestions.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field =>
string.Concat("\"", string.IsNullOrEmpty(field.ToString())?string.Empty: field.ToString().Replace("\"", "\"\""), "\""));
csv.AppendLine(string.Join(",", fields));
}
return csv;
}
Technical Blog 