Regarding ES Donut/Bar/Graph Chart

[sumagalla]

Hi Saviynt team,

I am trying to create a dashboard for the following SQL query with dashboard type as ES Donut Chart with the following data, but it's not showing any data.

Query: 

SELECT 'Employees' AS 'label', u.firstname, u.lastname, u.email, u.username FROM users u WHERE u.statuskey = 1 AND u.customproperty51 = 'Employee' GROUP BY u.firstname, u.lastname, u.email, u.username UNION SELECT 'Contractors' AS 'label', u.firstname, u.lastname, u.email, u.username FROM users u WHERE u.statuskey = 1 AND u.customproperty51 = 'Contractor' GROUP BY u.firstname, u.lastname, u.email, u.username;

Dashboard Config

{"analyticsConfig":"1696","fieldToGroup":"LABEL", "filterMap":{"LABEL":["Employees","Contractors"]} }

But it's not giving any data in the Dashboard, showing No data as below

Could you please let me know how to make changes to the query to work for ES chart?

[rushikeshvartak]

Refer Sample Below

• 
• Dashboard Query:
  • {"analyticsConfig":"1588","fieldToGroup":"FilterValue","filterMap":{"FilterValue":["Created in Last 90 days","Terminated in Last 90 days","Previous Users"]}}
•  Chart Properties
  • {"type":"pie","theme":"light","balloonText":"[[label]]<br><span style='font-size:14px'><b>[[data]]<\u002fb> ([[percents]]%)<\u002fspan>","legend":{"align":"center","markerType":"circle"},"balloon":{"fixedPosition":true},"dataProvider":[{"data":5029,"label":"Completed","color":"#FF1417"},{"data":1976,"label":"Expired","color":"#fd8d3c"},{"data":667,"label":"Discontinued","color":"#fdd400"},{"data":112,"label":"Open","color":"#AACC22"}],"titleField":"label","valueField":"data","colorField":"color","radius":"30%","labelText":"[[title]]","innerRadius":"60%"}
• Query for Report
  • Select u2.username as 'UserName',u2.Firstname,u2.Lastname,u2.EmployeeType as 'EmployeeType', u2.email,CASE WHEN u2.Statuskey=1 THEN 'ACTIVE' ELSE 'INACTIVE' END, CASE WHEN DATEDIFF(CURDATE(), u2.customproperty9) <= 90 THEN 'Created in Last 90 days' WHEN DATEDIFF(CURDATE(), u2.customproperty13) <= 90 THEN 'Terminated in Last 90 days' ELSE 'Other Users' END as FilterValue from Users U2

[sumagalla · Accepted solution]

Thank you for the example.

I have tried using the below one, it's working as expected. Just posting it, so that it will be helpful for others.

Analytics Query:

select u.username as 'UserName',u.firstname as 'FirstName',u.lastname as 'LastName',u.employeetype as 'EmployeeType', u.email as 'Email', CASE WHEN u.statuskey=1 and u.customproperty51='Employee' THEN 'Employees' WHEN u.statuskey=1 and u.customproperty51='Contractor' THEN 'Contractors' ELSE 'Not_Contractors_or_Employees' END as FilterValue from users u

Dashboard Query:

{"analyticsConfig":"1665","fieldToGroup":"FilterValue","filterMap":{"FilterValue":["Employees","Contractors"]}}

Chart Properties:

For ES Donut Chart/Simple Bar Chart -> {} -> If we give the empty flower brackets and save it, the data will be automatically populated.

For ES Tile -> Need to give something like below and then save it:

[{"tileType":1,"data":845,"displayName":"Active Accounts","clickable":"yes","chartType":"","objectName":"","className":"dashboard-stat red-intense","category":"","clickType":"analytics"}]

Note: Once we run the analytics report the data will be automatically populated in the tile.

Thank you, can close the ticket.

[rushikeshvartak]

✅👍Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.