RITM Variable Answer Location

[adamrauh]

For our newhire process, after creation of the new hire ticket (an RITM) we have a job that sends the hiring manager's an email containing various questions about the new hire pertinent to their being setup correctly.

This is done via a task that sends the manager an email, which they then answer:

Which when completed looks like this in the RITM (w have the 'Variable Editor' showing in the RITM form):

My question though is, I want to report off these answers in Tableau (we're using an ETL to transfer over), but I'm not sure the easiest way to get to this data.   I've poked around the question_answer table, and can see some of the data there...but it's a little lacking.

Wondering either, a) if there's a flatter/cleaner area where I can look for this data, or B) what's the best option to flatten the tables then (Database View) so that this can be easily exportable and reportable.

Thx,
A

[shloke04]

Hi,

You can check the Variable Ownership Table(sc_item_option_mtom) table and the Options Table(sc_item_option) to query for the Variable answer provided by the User.

Regards,

Shloke

[shloke04]

Hi,

Did you got a chance to review the solution proposed. If your query is Resolved would you mind marking the answer as correct and close this thread for others.

Regards,

Shloke

[SanjivMeher]

Hi Adam,

Instead of querying each variable, you can store all the variables in a single field. And report that field in tableau.

Because different item will have different variables.

var keys = new Array();

var set = new GlideappVariablePoolQuestionSet();

set.setRequestID(current.sys_id);

set.load();

var vs = set.getFlatQuestions();

var description =''

for (var i=0; i < vs.size(); i++) {

  if(vs.get(i).getLabel() != '' && vs.get(i).getDisplayValue() != '' && vs.get(i).getDisplayValue() != 'false') {

  description = description +vs.get(i).getLabel() + " : " + vs.get(i).getDisplayValue() + "\n";

  }

}

current.u_variable_description = description