Accessing the Logged In User within a Query-based Input Control

Product/Version: JasperReports Server Professional v3.5
Occassionally, it may be necessary to leverage the logged in user to limit items available from the selection list of a query-based input control. This relates both to single-select and multi-select input controls. As of JasperServer v3.5, accessing this information within the input control query is not possible with the base implementation, however, if you are familiar with the IQueryManipulator interface, you may be able to customize your implementation to allow for this.
The following outlines the potential steps for doing so.

Step 1: Implement a custom Query Manipulator

To start, you will need to implement a custom query manipulator class, compile it, and add it to the server class path. The following is an example of such a query manipulator:

package com.jaspersoft.example.querymodifier;
import java.util.Map;
import org.acegisecurity.context.SecurityContextHolder;
import com.jaspersoft.jasperserver.api.engine.common.service.IQueryManipulator;
import com.jaspersoft.jasperserver.api.metadata.user.domain.impl.client.MetadataUserDetails;
public class SimpleQueryManipulator implements IQueryManipulator
  public final static String ORGANIZATION = "$P{organization}";
  public final static String USERNAME = "$P{username}";
  public String updateQuery(String query, Map parameters) {
    MetadataUserDetails mud = (MetadataUserDetails)SecurityContextHolder
    String username = mud.getUsername();
    String organization = mud.getTenantId();
    query = query.replace(ORGANIZATION, organization);
    query = query.replace(USERNAME, username);
    return query;

Step 2: Inject the Customer Query Manipulator into the Engine Service

Edit WEB-INF/applicationContext.xml in the JasperServer WAR.

1) Create a bean from your class.

<bean class="com.jaspersoft.example.querymodifier.SimpleQueryManipulator" id="simpleQueryManipulator" />

2) Include the new bean into the engineService bean in WEB-INF/applicationContext.xml.

<bean class="${bean.class.engineService}" destroy-method="release" id="engineService">
    <property name="auditContext" ref="${bean.auditContext}" />
    <property name="repositoryService">
      <ref bean="${bean.repositoryService}" />
    <property name="dataSourceServiceFactories">
      <ref bean="dataSourceServiceFactories" />
    <property name="compiledReportsCache">
      <ref bean="${bean.engineService.compiledReportsCache}" />
    <!-- optional property for queryManipulator -->
    <property name="queryManipulator">
      <ref bean="simpleQueryManipulator" />
    <property name="securityContextProvider" ref="${bean.securityContextProvider}" />
    <property name="builtInParameterProviders" ref="builtInParameterProviders" />
    <property name="reportParameterLabelKeyPrefix" value="net.sf.jasperreports.prompt.label." />
    <property name="repositoryContextManager" ref="${bean.repositoryContextManager}" />
    <property name="cacheableCompiledReports" ref="${bean.cacheableCompiledReports}" />
    <property name="reportJarsProtectionDomainProvider" ref="reportsProtectionDomainProvider" />

Step 3: Create a Query-based Input Control

Now, create an input control with a query like the following:

SELECT city FROM example WHERE username='$P{username}' AND organization='$P{organization}'

Step 4: Test

Assuming the following data, you should get the correct data tied to the logged in user based on their username and organization (or whatever other criterion you add to the QueryManipulator implementation) substituted into the query.

city username organization
seattle joe west
san jose joe west
san francisco jane headquarters

For example, logging in as "joe" in organization "west" will only return "seattle" and "san jose" to populate the input control.