Jump to content
Alisha

Widget help!

Recommended Posts

Hello,

I have two questions please.

1. Is there a way to display the Status in a widget with just 'Open', 'Closed', etc. rather than 'status.open' and 'status.closed'?
2. I have created a widget to show the most recent calls logged by analysts today. I have used h_createdby, but I would also like to use the Handle, which I understand is stored in h_name in the h_sys_accounts table. How would I do the join to display the Handle please?

Any guidance would be much appreciated.

Many thanks,
Alisha

Share this post


Link to post
Share on other sites

Don't worry about this, I have managed to use the nested REPLACE function to get what we need for both, but it was a bit tricky with all the brackets! :)

However, if there is a cleaner way of doing this, it would be good to know!

 

Share this post


Link to post
Share on other sites

Hi @Alisha

I think the answer to this might be dependant on the type of widget you are creating here. 

If you could post some screenshots (ensuring to obfuscate any sensitive customer information), then we may be able to advise further, as we have recently introduced some Look Up functionality but it's only available in certain areas. 

Kind Regards

Bob

Share this post


Link to post
Share on other sites

Hi @Bob Dickinson,

I used the following REPLACE function.
 

REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'


I'm now having difficulties with using h_fk_priorityname. This is my SQL query. When I replace 'High' with 'Critical', it works. But it doesn't work for just the 'High' priority. It also works with the 'Low' priority, but not the 'Medium' priority.
 

SELECT h_pk_reference, h_summary, h_fk_team_name, DATE_FORMAT(h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'
FROM h_itsm_requests 
WHERE h_fk_servicename ="IT Services"
AND h_requesttype = "Incident"
AND h_fk_priorityname = "Critical" 
AND h_status IN ("status.new", "status.onhold", "status.open") 
ORDER BY h_pk_reference DESC


Please could you tell me what I'm doing wrong?

Many thanks,
Alisha

Share this post


Link to post
Share on other sites

Hi @Alisha

If the query runs and works with Critical and Low then I don't see why Medium and High wouldn't work. 

Perhaps the best thing to do to get a clear view of this is run the request WITHOUT the h_fk_priorityname in the WHERE CLAUSE, and include h_fk_priorityname in the SELECT part of the statement - so it will return ALL of the requests logged today, regardless of the priority. Then from the results, so can scan through and see if:
a) There actually are any Medium or High tickets in the result set
b) Check to see what the spelling/format is of the Medium/High ones to make sure the right thing is being added into the where clause perhaps?

So it would be this:

SELECT h_pk_reference, h_fk_priorityname, h_summary, h_fk_team_name, DATE_FORMAT(h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'
FROM h_itsm_requests 
WHERE h_fk_servicename ="IT Services"
AND h_requesttype = "Incident"
AND h_status IN ("status.new", "status.onhold", "status.open") 
ORDER BY h_pk_reference DESC

It might help diagnose the issue

Let me know what results you get

Kind regards

Bob

  • Thanks 1

Share this post


Link to post
Share on other sites

Hi @Bob Dickinson,

I used that query but it gives me an error straight away: "Error in SQL query, see log for more details". I don't understand why!

Many thanks,
Alisha

Share this post


Link to post
Share on other sites

Hi @Alisha

Could you send me a screenshot if possible so I cabn establish exactly where this is happening (Feel free to direct message this to me if it contains anything you don't want to be publically visible)

Thanks

Bob

  • Like 1

Share this post


Link to post
Share on other sites

Hi @Alisha

Thanks for the details. I have been able to replicate this now - quite simply, we have a 100 result (row) limit on a List Of Data Widget. This is to prevent a huge amount of server load and potentially cause performance issues on the rest of the platform. Also, the list of Data on a Dashboard will potentially become very small in text size if it needs to be condensed to fit within your widget. 

To establish this, I had a look at the logs on your instance and noticed this error when the widget was being created:

The maximum number of rows allowed (100) has been exceeded, query aborted

Perhpas we can make this a little clearer to an end user, so I'll feed that back. But we are unlikely to expand the number of rows returned because of the potential of the performance issues mentioned above. 
But this explains why some criteria (e.g. Critical, Low priorities) work - because they return fewer than 100 results, whereas High and Medium have more than 100 so fail. 

I guess the next step is to consider why you need to show 100+ results in this way on a Dashboard widget (rather than a standard Report) - and if there is a way of potentially reducing this down to a more manageble amount which would work better on a List of Data widget. 

I hope this helps, let me know if you have any more questions around this

Kind Regards

Bob

 

  • Thanks 1

Share this post


Link to post
Share on other sites

Hi @Bob Dickinson,

Thank you for the above explanation. We have now limited our results to 10.

I'm now having difficulty with another query and was wondering if you could help please?

I am trying to remove results from a Count widget that have the sub-status 'With Third Party'. I have tried different operators such as
!=
<>
NOT LIKE
NOT IN
NOT CONTAINS

I have tried using h_sub_status and h_sub_status_id, but all return a result of 0.

Is there anything else that I can try please?

Many thanks,
Alisha

Share this post


Link to post
Share on other sites

HI @Alisha

I think this might be because when you use a Not Equals operator, it also removes any values that are NULL. So if you don't use any other substatuses in this result set (i.e. every substatus is either "With Third Party" or Empty) - then the filter you've used above will result in 0 results. 

Try this - and see if it gives you a count closer to what you would be expecting:

 WHERE (h_sub_status != 'With Third Party' OR h_sub_status IS NULL)

Let me know if that works

Kind Regards

Bob

  • Like 1

Share this post


Link to post
Share on other sites

Hi @Bob Dickinson,

I'm creating a chart widget and I do now need to show the analysts' names underneath the columns. At the moment I'm using h_createdby, but that only shows their User ID. Is there a way to do this please?

Many thanks,
Alisha

Share this post


Link to post
Share on other sites

Hi @Alisha

When you are creating your widget, if you click on the "Chart Settings" button, you should see an option called Label Display Lookup. 

Because h_createdby is the User ID, you need to select the option: "User Name from Id"

This should then map all of the user IDs to their real names and make it look a lot nicer. 


Administration___Create_New_Widget.jpg

 

Hope this helps

Bob

  • Thanks 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...