Helical Canned Report, data security, and dynamic filtering


I am working on Helical Canned Reports and I want to integrate the reports with my own application, where the filters can be anything that user wants to use, there is not specific filter which we can set and pass value for. Is there something we can do to implement this scenario? I also want to implement the user security on the report, no one should see someone else’s data. I am using Helical Insight 4.1


Hello Netta,

Yes, we can make this happen, as there is no limit on the filters, like, number of filters, the type of filters, filtering conditions, etc. it will be difficult for the report to prepare it dynamically. However, as your application already knows what is getting filtered and how, we can prepare the where clause within your application which will be the complete clause, with column name(s), the condition(s) and the value(s). This we can use to implement it within our application query. This will be used along with already existing security parameters to implement the row level data security.
Thus there will be 2 parameters coming from your application to our Helical Insight. User info and the where clause piece of SQL query. From your application when a person logsin we can always understand the profile/uername/orgname etc and that can also be dynamically passed in the filter clause

Here is the reference code that you can try to replicate

import com.helicalinsight.adhoc.metadata.GroovyUsersSession;

String userName = GroovyUsersSession.getValue('${user}.name');
String whereValue = GroovyUsersSession.getValue('${profile[\'whereClause\']}');

if (userName.equalsIgnoreCase("'dynamicViewTesting'")) {
	String updatedValue = whereValue.substring( 1, whereValue.length() - 1 );
    return "select `Myschema`.`hcrReport`.`Claimant_Name` as `Claimant_Name`,Claim_Status from ` Myschema`.`hcrReport` where " + updatedValue + " group by `Claimant_Name`,Claim_Status";

	return "select ` Myschema`.`hcrReport`.`Claimant_Name` as `Claimant_Name`,Claim_Status from ` Myschema`.`hcrReport` where  Claim_Status IN ('Open') group by `Claimant_Name`,Claim_Status";

Thank You,
Helical Insight.