Restrict SQL Query Input Control with Tenant

[chiphi13]

I am currently using the latest version of JasperServer V3.5 and need to be able to pass the tenant ID into the sql query of the input controls in order to restrict the data that gets returned.  Is there a way to pass inject into the sql query dynamic parameters? 

[arno]

Im also looking for this solution.

Im trying to pass the currently logged in user to the underlying sql query for the input control.

I have a report but would like that needs a branch code as a parameter(that i can do) , but this branch code is selected form a drop down list which has underlying sql query which needs a username to determine which Branches are linked to the logged in user.

 

eg the underlying query for the input control looks like this

 

select *

from Branches

where username = $P!{currently loggedInUserName}

 

A selection should be made from the available list of branches based on the current user that is logged in

is there anyway i can pass the currently logged in user to the underlying query for the input control?

It will save me a lot of time (and headaches   :-)  )if this is possible.

[swood]

There is always a LoggedInUser parameter available to reports or input controls run in JasperServer. It is of type com.jaspersoft.jasperserver.api.metadata.user.domain.User. You can get the user name from that.

 

For JasperServer v3.1 and later, there has been a hook added to the EngineServiceImpl so you can inject values into the input control query.

 

Create a class that implements com.jaspersoft.jasperserver.api.engine.common.service.IQueryManipulator, which has a single method,

updateQuery(String queryString, Map parameters)

 

In the logic of your method, you can replace what you want in the query string before it is actually used in a query. In your case, you can get the LoggedInUser parameter out of the parameters Map.

 

In WEB-INF/applicationContext.xml, you will need to change the engineService bean as follows:

 

    <bean id="engineService" class="com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl" destroy-method="release">

    ....

            <property name="queryManipulator">

               <bean class="your.IQueryManipulator.Implementation.Class"/>

            </property>

    ....

   </bean>

 

Sherman

Jaspersoft

[arno]

The LoggedInUser parameter is working fine in my reports.

But my problem is that i should limit a dropdown list(based on the current logged in user) where a branch is selected from and then passed to the report. So this cancels out the LoggedInUser parameter.

 

The second solution u suggested sounds more like what i need.

The problem is just that i dont know exactly what to do.

The way i understand it is that jasper will call this class (i have to define myself) everytime before it will execute a query and replace a specific string in the query(as i defined it in the class) with one of the parameters in the Map object the method in the class receives. This method then returns the altered query string to jasperserver which in turn then executes it.

 

Is there documentation on this  showing me examples?

it would be really appreciated if u can point me in the right direction for examples.

 

Many Regrads

[swood]

Your understanding is correct.

 

I can't give you specific examples, but the style would be to put ${LoggedInUserName} in your query for the input control, like:

 

select department from person where username = '${LoggedInUserName}'

 

In your updateQuery(String queryString, Map parameters) method, you would replace ${LoggedInUserName} with the currently logged in user name, which you can get like:

 

        Authentication auth = SecurityContextHolder.getContext().getAuthentication();
 

        UserDetails details = auth.getPrincipal();

 

        String userName = details.getUsername();

 

 

Sherman

Jaspersoft

[deepthirohini86]

Hi,

       I saw your problem , the same problem i am facing, whether you write the class to solve,.

Kindly suggest me how do you achieve this.

 

Regards,

Rohini A.M

[prakasarao.betha]

Hi Swood,

Accessing the Logged In User within a Query-based Input Control -- is it possible in community 5.0 version.

 

Thanks in advance