Input control query that uses a parameter

[jjohnson]

Basically I need to vary the input to the SQL call behind an input control.

 

I have an input control that should return a list of salesreps for a branch.

the SQL query is something like EXEC SP_GET_SALESREPS 'BranchID'

 

I'd like to pass in the branch as a parameter at the end of the URL ( http://....&BranchID=1234) and have the SQL query be

EXEC SP_GET_SALESREPS $P{BranchID}

Is anything like this possible?

[anandharaj]

Do you mean to pass the parameter (input control value) to a report?

[jjohnson]

I need to show a list of salesreps in an input control list. This list varies depending on the user that runs it.

 

Therefore to build the list I need to vary the SQL behind it based on who the user is. I can pass the user in (as part of the URL for the report), but I cannot find a way to use that piece of that in the query behind the input control that shows the list of sales reps.

 

Is that more clear?

 

I need to run some SQL behind and input control like: "SELECT ID, NAME FROM SALESREPS WHERE MANAGER = $P{userID}" and I want to pass the userID to the report somehow.

 

Any ideas? It does not have to be $P{...}, please don't get hung up on that.

 

In summary: I just need a SQL query with a variable parameter behind the input control.

 

Thanks

[anandharaj]

Yes, im clear. In a general view, this is not possible, but probably might have some "method" to do this.. perhaps our JS Admin can answer you.

[jjohnson]

Will the JS Admin be around today?

[mdjj]

We did something like this to restrict values shown in a list input control by the logged in user. To do this we had to alter the code in

com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl

in the executeQuery method

        String userName = getLoggedOnUserName();
        String queryStr = query.getSql().replace("$P{LoggedInUsername}",userName);
        query.setSql(queryStr);

 

This will replace any occurrence of the $P{LoggedInUsername} in the input control query with the userName of the logged in user.

The userName is grabbed from the security context but in principal any value could be gotten from the http request and passed in.

Don't know if this helps or whether there's a way it can be done with out altering the code????

[jjohnson]

Thanks for the hint...  I was able to recompile jasper with maven, mount parts of the code with netbeans and debug tomcat with netbeans, so I can step though the code.

Now I have a questions:

In the:

public OrderedMap executeQuery(ExecutionContext context,
            ResourceReference queryReference, String keyColumn, String[] resultColumns,
            ResourceReference defaultDataSourceReference)

 

I do not see where/how I can get to the parameters from on the URL so that I can replace the name pairs in the query?

Can you point me as to where / how can I reference parameters from URL line once I'm inside the executeQuery method?

Thank You.

 

 

 

 

[anandharaj]

Hi mdjj, nice hints.. This is something that our Admin can implement in JS as default

[lucianc]

anandharaj
Wrote:

Hi mdjj, nice hints.. This is something that our Admin can implement in JS as default

Using $P{LoggedInUsername} in input control queries has been implemented (in a different manner) in the development version and will be available in the upcoming release.

Regards,

Lucian

[lucianc]

jjohnson
Wrote:

Can you point me as to where / how can I reference parameters from URL line once I'm inside the executeQuery method?

You can't.  Or, not unless you change other parts of the code as well.

I'm unclear of what you need to do.  If you need the current logged in user, go with mdjj's suggestion.

If you need to access HTTP request parameters, you'll have to look at com.jaspersoft.jasperserver.war.action.ReportParametersAction (the executeQuery method).  There you have access to the request parameters (via the org.springframework.webflow.execution.RequestContext object).  You'll need to gather the data that you need from the request and pass it along to the EngineServiceImpl method.

Regards,

Lucian

[jjohnson]

Hi Lucian

 

I looked at code in the ReportParametersAction method, but I could not find the place where the parameters from URL line are being parsed?  You must be doing this in other parts of jasper, can you point me to that code?

If I did make changes to the createWrappers method in ReportParametersAction, what kind of impact would that have on the rest of the system?  What are my chances of breaking something else?

 

Regards

 

[lucianc]

We read request parameters in many places in the code, check for instance ReportParametersAction.parseRequest(RequestContext, List, boolean).

The chances of breaking something by changing the createWrappers method depend of what changes you plan to make.  This method is called on each report execution, you'll need to make sure that the changes you perform make sense and don't break other reports.

Regards,

Lucian

[jjohnson]

I got it! Thanks.

 

I had to add another method to EngineService and the rest was pretty simple.

It does not look like my implementation will have any potential to break anything.

It will be fun however to upgrade/migrate these changes to the next version of Jasper :-)

 

Thanks again

[mdjj]

Oh by the way I can't really take the credit for the original suggestion as it was Marat's code (plug for JasperSoft Professional Services)!!!!!!

[leeyuiwah]

Hi,

 

I also want my input control queries to take parameters (not just $P{LoggedInUsername} but general parameters.  Most likely these parameters are the results of some previous input controls.  That is, we need some sort of "cascading input control". 

 

That is, selection of input control A will cascade down and affect the query in input control B.  E.g. In A the user selected a state, and in B only the cities in the specified state will be presented to user in the pull-down menu.

 

Based on this thread, I now know how I can change the query executed in input controls (i.e. not in the main report).  However, an open question to me is how we can refresh the GET and submit the result of A so that B can see that result.