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?
14 Answers:
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????
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
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
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
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
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
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.
