Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How can we correctly order by columns containing numeric data in KPI in Control Center?

RikuN
Regular Contributor
Regular Contributor

Hi

We have KPI which reports slowest approvers, and tells how long time it has taken for them on average to approve request. 

We have two issues:

When we open KPI, rows are not sorted like we have told in our Analytics sql. Is KPI always sorted by first columns, not column we have sorted it in Analytics sql?

When we sort by our column having in minutes the approval time, column is sorted as text, not as number, so it sorts wrongly. How to sort it as numbers in Control Center KPI?

Example screenshot, where we have clicked column header "Average approval time minutes" to sort it:

RikuN_0-1697629499053.png

 

Regards

Riku

13 REPLIES 13

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @RikuN,

Can you please share your analytics query which being used here?

Thanks.

If you find the above response useful, Kindly Mark it as "Accept As Solution".

RikuN
Regular Contributor
Regular Contributor

Hi

 

Here:

SELECT u.username AS 'APPROVER ID', u.firstname AS 'FIRST NAME', u.lastname AS 'LAST NAME', cast((((SUM(TIMESTAMPDIFF(SECOND, submitdate, approvedate)) / COUNT(username))/60)/60) as DECIMAL) AS 'Average Approval Time Minutes' FROM ARS_REQUESTS ar, ACCESS_APPROVERS aa, users u, (SELECT r.REQUEST_ACCESSKEY, r.ACCESSKEY, r.ACCESSTYPE, r.ENDDATE, CASE WHEN r.PARENTREQUEST IS NULL THEN r.REQUEST_ACCESSKEY ELSE r.PARENTREQUEST END 'PARENTREQUEST', r.REQUESTKEY, r.REQUESTTYPE, r.STARTDATE, r.USERKEY, r.STATUS FROM REQUEST_ACCESS r) ra, request_access_attrs raa, endpoints en WHERE ar.REQUESTKEY = ra.REQUESTKEY AND raa.request_access_key = ra.PARENTREQUEST AND raa.attribute_value = en.endpointkey AND raa.attribute_name = 'ENDPOINT' AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND ar.status NOT IN (4 , 6) AND u.userkey = aa.approveby GROUP BY username ORDER BY 4 desc LIMIT 10;

 

Regards

Riku

Order by cast((((SUM(TIMESTAMPDIFF(SECOND, submitdate, approvedate)) / COUNT(username))/60)/60) as DECIMAL) desc


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Hi

 

That is technically same what we had.

And also end result is same as i just tested it. So it didn't helped.

Our version is 5.5SP3.18.2

 

Regards

Riku

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @RikuN,

Thanks for sharing the query,

For the KPI, it will sorted on the first coloumn only.
For your convienience ,would request you to change the column order so that you it get sorted on the first coloumn.

Thanks
If you find the above response useful, Kindly Mark it as "Accept As Solution".

RikuN
Regular Contributor
Regular Contributor

Hi

 

Changing my number column to first doesn't help at all.

It is still sorted as text ascending. And i need it to be sorted as numbers descending.

Here is how it looks after moving column to be first column, not good:

RikuN_0-1698229307475.png

 

 

Regards

Riku

Add leading zeros 


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Hi

 

Thanks for this workaround. It technically works but doesn't look too nice for endusers.

Regards

Riku

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @RikuN ,

we are checking it with product team, if this is expected behaviour or not.

Thanks

If you find the above response useful, Kindly Mark it as "Accept As Solution".

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @RikuN,

As confirmed with the product team, this is the expected product behavior.

Thanks.

If you find the above response useful, Kindly Mark it as "Accept As Solution".

RikuN
Regular Contributor
Regular Contributor

Hi

 

There is no business reason for this not to work correctly with numbers, so from my point of view this is bug (not expected behaviour). This might be how product now behaves, but not what is expected from customers point of view.

 

Regards

Riku

 

Dave
Community Manager
Community Manager

@RikuN - Feel welcome to submit this on the Ideas Portal so Product Management can review it: https://ideas.saviynt.com/

RikuN
Regular Contributor
Regular Contributor