Need assistance with creating a real-time analytics report limited to 500 records

[nnaveed]

Hello,

I'm having trouble creating a run-time analytics report that is limited to only 500 records to prevent any issues with the database's performance. The report works fine without the limit clause, but as soon as I add it, I'm unable to generate the report. When I Click Update and then Save Button, a pop-up window briefly appears, and the report is not created.

Could you please provide some guidance on how I can successfully create this report with a limit of 500 records.

Thank you

[Rishi]

@nnaveed limit option should work. Can you send the query that you are trying?

[nnaveed]

SELECT DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID', (select username from users where RA.USERKEY = USERKEY) AS 'USER ID', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) from USERS where RA.USERKEY = USERKEY) AS 'USER NAME', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Request Type') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'REQUEST TYPE', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Start Effective Date') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'EFFECTIVE', (SELECT c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('RoleNames') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'ROLES REQUESTED', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) from USERS where USERKEY = AR.REQUESTOR ) AS 'SUBMITTER NAME', (select username from users where USERKEY = AR.REQUESTOR) AS 'SUBMITTER ID', CONVERT_TZ(AR.requestdate,'+00:00','-4:00') AS 'REQUEST SUBMIT DATE', (SELECT c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Request Initiator') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'REQUEST INITIATOR', (SELECT c.ATTRIBUTE_value FROM request_access_attrs c where c.attribute_name IN ('Training Tracks') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'TRAINING TRACKS', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 1) THEN 'PENDING' ELSE 'Auto-Approved' END AS 'SECURITY APPROVAL', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 1) THEN 'PENDING' ELSE '-' END AS 'SER APPROVAL', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 1) THEN 'PENDING' ELSE '-' END AS 'TRAINING APPROVAL', CASE WHEN AR.STATUS = 3 THEN 'Completed' WHEN AR.STATUS = 6 THEN 'Discontinued' WHEN AR.STATUS = 1 THEN 'Pending' WHEN AR.STATUS = 2 THEN 'In Process' WHEN AR.STATUS = 4 THEN 'Expired' WHEN AR.STATUS = 5 THEN 'Approved' END AS 'REQUEST STATUS', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Site') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'SITE', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('SiteTrainingTeam') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'ENTITY', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('HealthStream Site') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'HEALTHSTREAM SITE', max(APPROVEDATE) as 'REQUEST COMPLETED AT', AR.COMMENTS AS 'FOLLOW UP' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AR.JBPMPROCESSINSTANCEID=AA.JBPMEXECUTIONID AND AR.ENDPOINTASCSV = 'Epic_2021' AND AR.requestdate >= ${dateFrom} AND AR.requestdate <= ${dateTo}
LIMIT 500

GROUP BY SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1)

[mbinsale]

The query cannot have LIMIT in the middle of the section. It should be at the end. Try the below

SELECT DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID', (select username from users where RA.USERKEY = USERKEY) AS 'USER ID', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) from USERS where RA.USERKEY = USERKEY) AS 'USER NAME', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Request Type') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'REQUEST TYPE', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Start Effective Date') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'EFFECTIVE', (SELECT c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('RoleNames') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'ROLES REQUESTED', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) from USERS where USERKEY = AR.REQUESTOR ) AS 'SUBMITTER NAME', (select username from users where USERKEY = AR.REQUESTOR) AS 'SUBMITTER ID', CONVERT_TZ(AR.requestdate,'+00:00','-4:00') AS 'REQUEST SUBMIT DATE', (SELECT c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Request Initiator') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'REQUEST INITIATOR', (SELECT c.ATTRIBUTE_value FROM request_access_attrs c where c.attribute_name IN ('Training Tracks') AND AR.REQUESTKEY = RA.REQUESTKEY AND RA.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY) AS 'TRAINING TRACKS', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'DHeC_Security_Approval' AND aa.status = 1) THEN 'PENDING' ELSE 'Auto-Approved' END AS 'SECURITY APPROVAL', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SERApproval' AND aa.status = 1) THEN 'PENDING' ELSE '-' END AS 'SER APPROVAL', CASE WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 2) THEN 'APPROVED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 3) THEN 'REJECTED' WHEN EXISTS( SELECT 1 FROM ACCESS_APPROVERS aa WHERE aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY AND aa.JBPM_ACTIVITY_NAME = 'SiteTraining' AND aa.status = 1) THEN 'PENDING' ELSE '-' END AS 'TRAINING APPROVAL', CASE WHEN AR.STATUS = 3 THEN 'Completed' WHEN AR.STATUS = 6 THEN 'Discontinued' WHEN AR.STATUS = 1 THEN 'Pending' WHEN AR.STATUS = 2 THEN 'In Process' WHEN AR.STATUS = 4 THEN 'Expired' WHEN AR.STATUS = 5 THEN 'Approved' END AS 'REQUEST STATUS', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('Site') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'SITE', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('SiteTrainingTeam') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'ENTITY', (select c.ATTRIBUTE_value from request_access_attrs c where c.attribute_name IN ('HealthStream Site') and AR.REQUESTKEY = RA.REQUESTKEY and RA.REQUEST_ACCESSKEY=c.REQUEST_ACCESS_KEY) AS 'HEALTHSTREAM SITE', max(APPROVEDATE) as 'REQUEST COMPLETED AT', AR.COMMENTS AS 'FOLLOW UP' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AR.JBPMPROCESSINSTANCEID=AA.JBPMEXECUTIONID AND AR.ENDPOINTASCSV = 'Epic_2021' AND AR.requestdate >= ${dateFrom} AND AR.requestdate <= ${dateTo}
GROUP BY SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) LIMIT 500