Filtering the dashboard filters data based on profile values when we use EFWD for filters

This blog is only relevant in case of version 4.1. In certain cases we will use EFWD mechanism to fetch dashboard filters data for more advanced use cases.

What we want to do is we will be getting the profile values via SSO token and we want that the profile value from token should get assigned to the filter by default.

In order to apply profile security for the dashboard filter, we should follow below steps:

We have created a filter “destination” at the report level and created a dashboard with same filter

1. Create a folder named “efwd_data” and place filters.efwd file
2. The efwd file contains below code :

<EFWD>

<DataSources>

<Connection id="1" type="sql.jdbc.groovy">

//provide DB connection details

<Driver>com.mysql.jdbc.Driver</Driver>

<Url>jdbc:mysql://localhost:3306/sampletraveldata</Url>

<User>root</User> /

<Pass>root</Pass>

<Condition>

<![CDATA[

import net.sf.json.JSONObject;

public JSONObject evalCondition() {

JSONObject responseJson = new JSONObject();

responseJson.put("driver","com.mysql.jdbc.Driver");

responseJson.put("url","jdbc:mysql://localhost:3306/sampletraveldata");

responseJson.put("user","root");

responseJson.put("password","root");

return responseJson;

}

]]>

</Condition>

</Connection>

 

 

</DataSources>

<DataMaps>

 

 

<DataMap id="1" connection="1" type="sql.groovy">

<Name>locationName</Name>

<Query><![CDATA[

 

import com.helicalinsight.adhoc.metadata.GroovyUsersSession;

 

public String evalCondition() {

 

String profileValue = GroovyUsersSession.getValue('${profile[\'destination\']}'); // getting the profile details for the logged-in user

 

String responseJson;

responseJson = "select distinct destination from travel_details where destination in ("+profileValue+") order by destination";  // filtering the filter query data by passing profile values

 

return responseJson;

 

}

 

]]></Query>

</DataMap>

 

 

</DataMaps>

</EFWD>

3. Change your DB details and query as per your details

4. In order to fetch the data from efwd at the dashboard level, open the dashboard in edit mode then click on filter edit
   

5. Disable adhoc data Source and browse efwd file then provide details
   Data map id, column name
   

   Then click on save

6. Created profile “destination” with value “Chennai”

   

7. Now open the dashboard ( destination filter will have only value Chennai -since hiadmin user has only that value)