Report on PCF answers

[HGrigsby]

Hi

Not sure if I should be starting a new thread  as this one is quite old, but I am trying to do something similar.

We have a translation service and we ask in the progressive capture what language is the translation to and from.

I got some of the way there and can get either of the answers by specifying the question

"Which Language would you like to translate into"  and it gives me a list of those answers.  Or the other way round works, I can get the From Language records

I want to see both what language the translation was from as well as to so we can show the most requested translations etc.

Could someone give me some hints on how to achieve this or what I need to change to make this possible

thanks

Helen

 

 

[Victor]

@HGrigsby I have created a separate thread with your reply... as a rule of thumb, a new thread is required rather than replying to old ones... you can, if you wish, link the original thread (https://community.hornbill.com/topic/10925-progressive-capture-custom-questions-report/)

 

[Victor]

@HGrigsby you still need help with this?

[HGrigsby]

Hi Victor

Thanks for creating my new thread.  Yes please, I'd really appreciate some help

Helen  

[Victor]

@HGrigsby I'll put together a simple report and post it here a bit later on...

[Victor]

@HGrigsby seems that I've hurried when I mentioned the word report...  this can be done with SQL but I'm quite sure you can't have the required SQL statement in a report  

The Statement that will give you what you need is:

SELECT h_entity_ref AS Request, GROUP_CONCAT(h_answer SEPARATOR '-') as LangFromTo
FROM h_itsm_questions
WHERE h_question IN ('<question_ID_Language_From>','<question_ID_Language_To>')
GROUP BY h_entity_ref

The key function here is GROUP_CONCAT which does not exist in reports...

You could run this statement in DB direct then export the result. The information you need is outputted in "LangFromTo" field which will be something like: "Language From" dash "Language To" (e.g: English-Spanish)

[HGrigsby]

@Victor

Thank you for the response and suggestion and apologies for not coming back to you sooner. 

I would have preferred that the results were able to be displayed in a report though.  Is there anything I can change on the PC or BPM to make it work in the future?

I have tried running this but get no records to display message.  The query looks right, I changed the field name to match ours

Lang_To and Lang_From

Any suggestions?  Thanks Helen

 

 

[Dan Munns]

Hi @HGrigsby am I correct in thinking you want a report similar to this but to show questions and answers you need at the same time from a specific PC? 

So your question column would be 'Which Language would you like to translate into' and the answer to that question needs to be in the next column? 

[Dan Munns]

On the chance this is the report you need you can use the attached and change the first filter to match the name of the catalog item the translation requests are logged under. 

I have assumed that the question is exactly "Which Language would you like to translate into" (minus quotes) if it isn't then you can change the third filter to the exact question you are looking for. 

Translation_report.txt

[Victor]

3 hours ago, HGrigsby said:

Is there anything I can change on the PC or BPM to make it work in the future?

@HGrigsby hmm... actually this... this is not a bad idea... !!!  ... and I can't believe I haven't thought of it... 

So yes, you can configure the PCF and map the "language from" and "language to" with custom fields on request table... then you create a very simple report on requests table that displays these two custom fields...

[Victor]

@Dan Munns nothing wrong with the report, however, the challenge was to display two different answers (and questions) on the same line in the report... answers that are stored in two distinct records... the aim was basically to only have a line for each request... the report you presented creates two lines for the two different answers... although not incorrect, not at all, but I don't think is the wanted format

[Dan Munns]

The only option I can think of is to add the required question and answer into custom fields one and two and change the column names in the report. 

Obviously will only work going forward though. 

[Victor]

@Dan Munns indeed  

[Dan Munns]

That will teach me to read posts better I suppose....

[Victor]

It seems we think alike, which is a bit scary because I know (or I like to think I do) what is going on in my head... and that's scary !!!  ... all the voices are scared at times ...

[Dan Munns]

I blame the fact I was on my work phone walking to my car and could only see the latest post. 

Its when the voices are quiet I have to worry to be honest. It usually means the network is down....