Analytics SQL Query Allowing IN instead of = for dynamic attribute

[sjordheim23]

Hello, is it possible to use an "in" instead of "=" in a SQL query where you provide a dynamic attribute for users to populate when running the report?

Essentially trying to get entitlement history for a user (currently), but would like to allow users to input a string of users instead of a single string value.

Analytics query below:

select X.*, r.displayname as 'Assigned from Role' from ( select ar.assignedfromrole, ar.taskkey as 'TASK KEY', u.username as 'USERNAME', case when ifnull(u.statuskey, 0) = 1 then 'ACTIVE' else 'INACTIVE' end 'USER STATUS', case when ar.tasktype=1 then 'ADDACCESS' when ar.tasktype=2 then 'REMOVEACCESS' end 'TASK TYPE', case when ar.status =3 then 'COMPLETE' when ar.status=2 then 'INPROGRESS' when ar.status=1 then 'NEW' when ar.status=4 then 'DISCONTINUE' when ar.status=5 then 'PENDINGCREATE' when ar.status=6 then 'PENDINGPROVSION' when ar.status=9 then 'NOACTIONREQUIRED' else 'FAILED' end 'TASK STATUS', ar.accountname as 'ACCOUNT NAME', e.displayname as 'ENDPOINT NAME', ev.entitlement_value as 'ENTITLEMENT NAME', ar.updatedate as 'PROVISION DATE', ar.TASKDATE as 'TASKDATE' from arstasks ar, users u, entitlement_values ev, endpoints e where ar.userkey=u.userkey and ar.entitlement_valuekey=ev.entitlement_valuekey and ar.endpoint=e.endpointkey and u.username='${username}' )X left join roles r on X.assignedfromrole=r.rolekey order by X.TASKDATE

[rushikeshvartak]

select X.*, r.displayname as 'Assigned from Role' from ( select ar.assignedfromrole, ar.taskkey as 'TASK KEY', u.username as 'USERNAME', case when ifnull(u.statuskey, 0) = 1 then 'ACTIVE' else 'INACTIVE' end 'USER STATUS', case when ar.tasktype=1 then 'ADDACCESS' when ar.tasktype=2 then 'REMOVEACCESS' end 'TASK TYPE', case when ar.status =3 then 'COMPLETE' when ar.status=2 then 'INPROGRESS' when ar.status=1 then 'NEW' when ar.status=4 then 'DISCONTINUE' when ar.status=5 then 'PENDINGCREATE' when ar.status=6 then 'PENDINGPROVSION' when ar.status=9 then 'NOACTIONREQUIRED' else 'FAILED' end 'TASK STATUS', ar.accountname as 'ACCOUNT NAME', e.displayname as 'ENDPOINT NAME', ev.entitlement_value as 'ENTITLEMENT NAME', ar.updatedate as 'PROVISION DATE', ar.TASKDATE as 'TASKDATE' from arstasks ar, users u, entitlement_values ev, endpoints e where ar.userkey=u.userkey and ar.entitlement_valuekey=ev.entitlement_valuekey and ar.endpoint=e.endpointkey and u.username='${Input_Multiple_Usernames.replace(",","','")}' )X left join roles r on X.assignedfromrole=r.rolekey order by X.TASKDATE

[sjordheim23]

It doesn't seem like Analytics likes that .replace function in the variable call. (From what I can tell.) Is there any documentation that you know of where Saviynt mentions supporting multi-input for a variable? I'm not even sure how to go about troubleshooting that with what I've found so far unfortunately.

Edit: This is the error returned:
groovy.lang.MissingPropertyException: No such property: Input_Multiple_Usernames for class: SimpleTemplateScript157 at SimpleTemplateScript157.run(SimpleTemplateScript157.groovy:1) at com.saviynt.ecm.analytics.AnalyticsController$_closure19.doCall(AnalyticsController.groovy:1827) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:155) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

[rushikeshvartak]

Does your report is runtime ?

[sjordheim23]

Building mine currently as a SQL Query. Should it be runtime instead?

[rushikeshvartak]

Yes it should be runtime analytics 

[sjordheim23]

Using Runtime Analytics instead of SQL Query, I get similar errors. Combination of what I mentioned above on not finding that property and:

An Internal Error occurred. Please check supported special characters next to query field in (i) icon and remove the unsupported characters.

[sjordheim23]

Here is a copy of the Runtime report configuration:

[rushikeshvartak]

Create analytics without replace functions and then update

[sjordheim23]

Was able to successfully create a runtime report with the query you originally provided, but was not able to add the replace function into the query and update the report. It continued to reject it with the unsupported special characters in query error.

[rushikeshvartak]

I can replicate issue. 

You can export report and reimport that is working.