Changing response

[Melissa Gurney]

Hello,

Is there a way of going into a call that has been raised and altering the questions in there? (Questions that the analyst has filled in)

I see that you can amend the other details, such as description, asset etc but cannot see a way to change the question responses.

Many thanks,

Melissa

 

[David Hall]

Hi Melissa,

Thanks for the post, I can confirm that it is not currently possible to edit the responses to questions that have been added against a request as these would normally be responses from customers that would be unlikely to change.

Perhaps if you could give an example of when you need to edit such responses we can look to see if there is another way to currently handle it, or put editing questions forward for consideration as a future change.

Kind regards,

Dave.

[Steven Boardman]

HI @Melissa Gurney

Thanks for your question.

The answers held in the Questions section are not editable. The answers are populated from custom questions, on custom forms in Progressive Capture and could have been provided as you say by an Analyst logging a request, or equally by a customer logging a request via the self service portals. 

One option open to you, if you see the need to change the answers to the questions going forward is to utilise the Mapping Fields from customised forms option which is available when configuring your custom forms in Progressive Capture. 

In essence you can map the answers from the questions to the following fields which can be visible in the details section on the request forms:

1. Summary

2. Description 

3. Any custom fields 

This will allow you to map the answers into fields which are then editable in the details section.

* You will need to name the custom fields in the details section on the request forms using the form designer and make these visible.  Obviously if you have different questions for different Services you offer you would need to do the custom field editing at the Service > Request Type level for each service. 

Note - The original answers to the questions are still written to the questions section and remain non editable, so you have the original answer there, and if you need to change or edit these, you can update the fields you have mapped these answers to in the details section.

Details of how to configure and use this is explained on the wiki here, and under the Mapping Fields from customised forms section 

https://wiki.hornbill.com/index.php/Progressive_Capture_Workflow

One added advantage of doing this, means the answers which are written to the Summary, Description or custom fields are then available to you to use as variables when configuring email templates where the answers in the questions section are not. 

I hope this helps and gives you some ideas of what you could do?

Thanks

Steve

[Melissa Gurney]

Hi David,

Thank you for your response. This was a question raised by first line during our testing of Service Manager. As an example, we use a custom form to specify a desk number. The question was asked "What if the customer has entered the incorrect desk number? Can we go back in and change it?"

If this is not possible, I suppose we could add the change into the description of the call.

Thank you,

Melissa

[Melissa Gurney]

Hi @steven boardman

Thank you for that information. That sounds like it will do what we are looking for. I will have a look around and see what I can come up with.

Thank you again for your help.

Melissa

 

[Ralf Peters]

Hi @steven boardman ,

one quick question , i used your info above to map a custom question to the h_custom_a   field   under the  details section   ( date type) ,  asking for a due date  in the progressive capture.

 

i have added the field in the "view details form"  to the design  , changing the field label to "Due Date"

 

that works fine , i can see the date in the question section as well as in the details sections and i can edit the details section field , so far so good ( allthough in IE it is still showing as Custom A , Chrome displays it as Due Date ?? )

I now try to report on both fields  but find that the h_custom_a    field on the  h_itsm_requests table is empty .

Where  is the value from the details section custom field   stored ??

Thanks

 

Ralf

 

 

 

[Steven Boardman]

Hi @Ralf Peters

Thanks for the post

The custom fields in the details section are held in the extended details tables for each request type(Inc, Prob etc) so examples would be:

h_itsm_incidents

h_itsm_servicerequests 

From a reporting perspective if you are using the Reports option through the admin tool, you can create a new report and create the report using the Entity report type, and choose Incident, Service Request etc.

You will then see the available columns for that report.  Include the columns from the request table and the extended details table for the entity you have chosen - if you include the custom fields from the extended details table you will then see your values. 

Alternatively you can use the SQL Schema Designer report type and join the two tables on the request id to get the same result.  

Just as an aside we are in the future moving towards a single extended table for all requests types (Inc, Problems etc) and as such you may see another table which also holds these values, and this is called h_sm_requests_extended, and you may use this table instead if you prefer as it can be reported on by joining it with the h_itsm_requests table on the request id.

Regardless of the choice you take the data will be available to you to report against. 

Hope this helps?

Steve

[Ralf Peters]

Hi @steven boardman

thanks for the info , i  joined the h_sm_requests_extended and this gives me the data .i need

is there a way to change the headings  on the report , shows as custom 1 ( which i assumed maps to Custom a  ??!! ).

 

Thanks

 

Ralf

 

 

 

[Steven Boardman]

Hi @Ralf Peters

Unfortunately you can't change the report headers in the reporting tool in the admin console.  This is something we are considering as we look to evolve the reporting options and introduce reporting into the user app (analyst interface).

The challenge here is that we provide the ability for you to add custom fields against each service, and each request type - this means you could be using h_custom_a(1) for multiple different purposes depending on how many services you have defined and each with different labels therefore we can't simply use a label to display on the reports when using the custom fields in the h_sm_requests_extended table.

As i mention this is something we are considering with the new reporting options we are looking to introduce into 2017. 

In the meantime one option is to create your report in the reporting tool, and export to CSV where you can change the headers of the columns as you need. 

Alternatively you can create Advanced Analytic widgets to add to dashboards where you have a little more freedom to tweak the display headers. 

Take a data list widget, where you can input SQL and use the AS option in your query to convert the labels to be more presentable 

Here is an example using columns from the h_itsm_requests and h_sm_requests_extended tables and in particular the h_custom_1 column. you may want to limit the results set, or add a condition such as h_fk_servicename='<insert service name>' to limit the results to those request types logged against that service, and thus only values in the h_custom_1 column for requests against that service.  

SELECT h_request_id As Reference, h_itsm_requests.h_summary AS Summary, h_sm_requests_extended.h_custom_1 AS Due_Date from h_sm_requests_extended
inner Join h_itsm_requests on h_request_id=h_pk_reference and h_requesttype='Incident' and (h_status='status.open'  OR h_status='status.new')
Group by h_request_id Desc
Limit 5

Excuse the poor quality demo data

Hope that helps a little

Steve

 

 

[Ralf Peters]

Hi @steven boardman ,

 

thanks for the reply i might give that widget a try .

 

Ralf

 

 

[Ralf Peters]

Hi @steven boardman ,

 

tried the widget , but the allignment seems to be out , i cannot get the  header to line up with the data ??

thats the sql i used  :

SELECT h_request_id As Reference, h_itsm_requests.h_summary AS Summary, h_itsm_requests.h_fk_user_name AS Customer, h_itsm_requests.h_datelogged as Date_Logged,
h_itsm_requests.h_ownername as Owner, h_itsm_requests.h_status as Status,q.h_answer as Due_Date, h_sm_requests_extended.h_custom_1 AS "New_Due_Date"

from h_sm_requests_extended 
inner Join h_itsm_requests on h_request_id=h_pk_reference and (h_status='status.open'  OR h_status='status.new' or h_status='status.onhold') and h_fk_team_name="Data Selection Team"
inner Join h_itsm_questions as q on h_request_id=q.h_entity_ref and q.h_question_id="h_custom_a"

Group by  h_sm_requests_extended.h_custom_1 asc

 

any advise ?

 

Thanks

 

Ralf

 

 

 

[Ralf Peters]

i think the problem is that the owner field is empty , that it does not reserve any space and the formatting seem to be line specific .

[Steven Boardman]

Hi @Ralf Peters

Yep the problem here is the owner field being empty.

You could use the IFNULL operator to display a value if the column is empty.

Select IFNULL(columnreference,"Some Value")  AS displayName

So in your scenario above replace "h_itsm_requests.h_ownername as Owner" with

IFNULL(h_ownername,"N/A") AS Owner 

This will give you something like this (excuse my data)

Hope that helps 

Steve

[Ralf Peters]

@steven boardman,

thanks again , is the column width fixed ,formatting on this widget is really bad  ,

seems to ignore any spaces in the heading , even if you put  it in quotes and it  removes any upper case other then the first one .

why is it in your example that the "status" is padded out , but the Due_date isn't ?

Thanks

Ralf

 

[Steven Boardman]

HI @Ralf Peters

The formatting is not great i'd agree.  I have asked a colleague to take a look at this at it is not my specific area of expertise, so i will see what they come back with and feedback.

I think i may have mentioned this above, but we are currently working on new reporting functionality for the analyst front end, which will be much more aligned to the Views, Charts and Personal Dashboards which you might be familiar with.  This work is ongoing and will start to become visible into 2017, albeit we will not be doing a big bang with this, but more introducing this through iterative releases but considerations around formatting, layout, ease of configuration are right up there on our agenda, all designed to improve our overall reporting options. 

Thanks

Steve

[Steven Boardman]

@Ralf Peters

Just a quick update here for you.  

Earlier in this post you asked about the ability to change the report column titles if creating a report using the Service Manager SQL Report Creator.  We have added the ability for you to do this in the admin tool, and it is currently going through testing before it is released.  This will allow you to set your own column title display names on the reports and these will be present on both the HTML  and CSV outputs. 

This will allow you to define meaningful display names for the custom fields if reporting on the  extended details table custom columns for example.

Please keep an eye out for the release notes in the admin tool, but i will also post back here once this change is available to you (should be available very shortly).

Steve

 

[Ralf Peters]

Hi   @steven boardman,

that is great, will be looking out for this .

 

Thanks

 

Ralf

 

 

[Steven Boardman]

Hi @Ralf Peters

Just to let you know that the admin console has been updated and the ability to change the display name of the report columns is now live. 

This is available on reports created against entities, and measures initially, with the SQL Schemer Designer to follow. 

When creating reports based on Entities, if you select say Requests this will allow you to report on all the data held in the tables linked to the request entity including the custom fields held in the extended info table, request info, raisedby etc etc without having to use the SQL Schemer Designer to join tables. 

Once this is available with the SQL Schema Designer i'll update the post

Steve

[Ralf Peters]

Hi @steven boardman ,

thanks for the update

i could almost get away with the request entity   but it does not link to the h_itsm_questions table , so i'll wait until the SQl Schema designer has the functionality added .

Thanks

Ralf

 

 

[Steven Boardman]

Hi @Ralf Peters

I'll post back when the SQL Schema Designer reports also support this. 

In the meantime, there is one option open to you, which would allow you to use the entity reports option.

When you are creating your Progressive Capture questions, you can choose to map these to the extended fields of the request entity.   the following wiki article, and the last section Mapping Fields from customised forms describes how to do this. 

https://wiki.hornbill.com/index.php/Progressive_Capture_Designer

The PC questions would still appear in the questions section on the requests, but the answers would also be held in the custom fields on the request forms.  This has a few benefits. 

1. The original answers as provided by the customer or the logging analysts would be held in the questions section, but you would then also be able to change the answers  if you needed too via the Details section on the request form if you map the answers to the custom fields.  meaning you would have the original and the current answers if this is relevant. 

2. You would be able to include the answers to the questions in email templates, because the answers would be in the extended details columns for the request entity

3. You would be able to include the answers in the entity reports and change the column display names because the answers would have been mapped to the extended columns of the request entity. 

So in the example below i am using the Request entity, and including the custom_a and custom_b columns from the extended information table which is where i have mapped my custom questions too from progressive capture custom forms. 

Just a thought ahead of the SQL Schema Designer option being available and also possibly offering you a couple of additional benefits for using answers in email templates (as variables) and if the answers to questions needed the option to be changed during the lifecycle of tickets.

Steve

[Ralf Peters]

Hi @steven boardman ,

 

Thanks for the example !

I already implemented your advice and very much like the idea that it preserves the original answer in the question section and any change are reflected in he extended custom section .

My User unfortunately wants to see original and current values on the report L.

 

Happy to wait for the SQL implementation , as it is purely cosmetic  .

Thanks  Ralf

 

 

 

[Keith]

@steven boardman I've been reading this with interest regarding the storage of data in custom fields and reporting against this data. We capture the answers to a specific question in h_custom_a however I have noticed that this is actually stored in h_sm_requests_extended.h_custom_1 rather than in the h_itsm_requests table (in h_custom_a).

I understand that in widgets etc I can join h_itsm_requests with h_sm_requests_extended to report upon data from the request itself and the custom data captured. However, I need to capture this information as a measure and as far as I can see there is no way to do a table join in a measure. Can you advise if there is a way around this. This is absolutely critical to our reporting needs.

Thanks

 

Keith

 

[Steven Boardman]

Hi @Keith

The measures are currently based on a single table, and this is certainly the case for adding Saved Data Columns for future data manipulation in widgets. 

There are options in the Query Where Clause to be able to use a sort of nested query, for example:

h_pk_reference IN(

SELECT

h_request_id

FROM

h_sm_requests_extended

WHERE

h_custom_2 = 'Live'

)

AND h_requesttype = 'Problem'

So this would allow me to only include requests where the h_custom_2 value in the h_sm_requests_extended  = Live and the requests are type Problem in the count, obviously we can extend this to evaluate more of the custom field values, but as i say it is not currently possible to include columns from more than one table in the Saved Data Columns for future use. 

Would you be able to give us some examples of the output you are looking to achieve?

Thanks

Steve

 

[Keith]

Hi @steven boardman 

Thanks for your response. That might be of some help but I really feel that we are unlikely to be able to do what we need to and I have been assured from day one that this would be possible.

Let me fill you in on some background.

Our system is shared across two operating companies which I will refer to as LMS & LBS. Each of the OpCo's have their own IT structures including IT Directors.

As part of every request we as a "question" regarding which OpCo the request relates to (LMS, LBS or Shared) as well as asking for the company code of the person reporting the request (this is not site) i.e 142 equals Milton Keynes. We map the company code question to h_custom_a and the company code to h_custom_b.

Almost all of our reporting needs to segmented by at least OpCo. So for instance in its simplest terms we want to be able to identity the number of open requests by OpCo. However we then need to take this further to establish at each OpCo level the various KPI's (OTD, First time fix etc.) by Site, Region, Categorisation (Request & Closure) etc.

So, it seems to me that the fact that h_custom_* fields are stored in the extended table make this difficult if not impossible. Had the h_custom_a field been stored in h_itsm_requests as we were led to believe the reporting would be so easy.

 

Hopefully I am overlooking something and what we need is not that difficult. However, what I don't quite understand is why the custom fields are not stored under the requests table. What are the h_custom_a fields in the requests table meant to contain if not the answers to the questions we map to h_custom_a?

 

Hope this makes sense.

Regards

 

Keith

 

 

 

[Steven Boardman]

@Ralf Peters

Just for your information, in the latest update to the Admin tool we have added a new option which allows you to give a table an Alias, as a by product this also will now allow you to add column aliases using the SQL designer

1. Use the SQL Designer Option, and choose a table (or tables)

if you highlight a table, you will see an option to add an alias, via a pop up, and then options to change or remove as shown below.

2. Navigate to the Select Column Tab, and add your columns, you will now see the option to add aliases to the columns for SQL Designer Reports as well.

Note there is a small issue, where the column alias button might not immediately show, but if you switch to another tab and back it will appear.  I have reported and it will be fixed shortly, but should not stop you adding the aliases you need.

Steve