parameter passed from jsp to SQL query where clause in BIRT reports

Hi

a) I have a parameter in a jsp page in a Struts application.
b) I have a JDBC data source and Data set in BIRT reports.
c) the SQL query is "select Contents from user where Header = ? "
d) I have a ReportParameter called HeaderParameter.
e) I have a dataset Parameter also which is linked to the ReportParameter.

When I run the application, the parameter from the jsp page should be passed to the BIRT report as the value for ? in the where clause.

I have put the parameter in the application like request.getSession.setAttribute("a1", a1);

in the beforeOpen of the dataset, I have added the following:
var session = reportContext.getHttpServletRequest().getSession();
var myid = session.getAttribute("a[1]");

When I run the application, the parameter from the jsp page should automatically be inserted in the sql query and I want the values
to be displayed on the report.

I am not able to insert the value in the query. When I run the application, I get the Parameter page for the ReportParameter, which defeats
the idea of passing the parameter from jsp.

Can somebody help me?

Thanks
neeti

Find more posts tagged with

Comments

bhanley

You should not need the extra work on the scripting tier. You need to bind the report parameter to the Data Set parameter. To do this, open the Data set for editing and look at the "Property Binding" section. Once you bind the parameter to the Data Set, all you need to do is pass the parameter value into the report and everything will be handled for you.

How are you rendering the report? If you are targeting a URL, simply append the parameter name onto the URL, i.e. &parameterName=parameterValue.

Good Luck!

pauljin

Hi you could make use of request object instead of session object.
make use of request.setAttribute() and pass the request object to birt. You could set it either in controller or in jsp it self

In birt get the parameter with reportContext.getHttpServletRequest().getAttribute(<attribute>) method and assign it to the dataset parameter.

It should work in this way

neetira

<blockquote class='ipsBlockquote' data-author="bhanley">You should not need the extra work on the scripting tier. You need to bind the report parameter to the Data Set parameter. To do this, open the Data set for editing and look at the "Property Binding" section. Once you bind the parameter to the Data Set, all you need to do is pass the parameter value into the report and everything will be handled for you. 
 
How are you rendering the report? If you are targeting a URL, simply append the parameter name onto the URL, i.e. &parameterName=parameterValue. 
 
Good Luck!</blockquote>
 
if I use the "Property Binding", then what do I use in the "Query" tab of the Dataset? 
 
I want the query to be "select Contents from Contents_user where Header in (?);" - the parameter is a list of values like 'A1','A2'. 
 
I have a dataset parameter called "param_1" and report parameter called "header parameter". Right now, I am trying to set the "header parameter" by reportContext.getHttpServletRequest().getSession().getAttribute("list"); 
 
What do i write in the "Query" tab and in "Property Binding" of the dataset? 
Also, I am trying to put "list" as a String = 'A1','A2', so that the "header parameter" = 'A1','A2' in order to have the query as "select Contents from Contents_user where Header in ('A1','A2');" 
 
When I run the application, it gives me error that 
" 
org.eclipse.birt.data.engine.odaconsumer.PreparedStatement doSetNull( int ) 
SEVERE: Cannot set a null value to parameter 1. 
Cannot set preparedStatement null value. 
SQL error #1: com.microsoft.sqlserver.jdbc.SQLServerException: There is no metadata. 
 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement null value. 
SQL error #1: com.microsoft.sqlserver.jdbc.SQLServerException: There is no metadata. 
" 
 
Though the value of "list" is 'A1','A2', somehow the application is finding "null". 
 
I have tried with the value of "list" as A1,A2 also, but the same error comes. 
The table and the values for columns Contents and Header with the values A1, A2 are present. 
 
I cant understand how the null value is coming. 
 
thanks 
Neeti

neetira

<blockquote class='ipsBlockquote' data-author="pauljin">Hi you could make use of request object instead of session object. 
make use of request.setAttribute() and pass the request object to birt. You could set it either in controller or in jsp it self 
 
 
In birt get the parameter with reportContext.getHttpServletRequest().getAttribute(<attribute>) method and assign it to the dataset parameter. 
 
 
It should work in this way</blockquote>
 
hey pauljin 
 
In JSP, i assigned the values I am passing to "list" using request.setAttribute("list", parameter); 
 
In BIRT, in initialize function of the report, i put the following, 
 
var session = reportContext.getHttpServletRequest().getSession(); 
 
var myid = session.getAttribute("list"); 
 
reportContext.setParameterValue("Header_Param", myid ); 
 
here, "Header_Param" is the report parameter. 
 
In your post, you said that we can assign to the dataset parameter. 
 
What I did, is create a dataset parameter (param_1) and then put its default value as the report parameter(Header_Param) and then do the above. 
 
I thought if you have a dataset parameter, you have to have a report parameter and put the default value of the dataset parameter as the report parameter. 
 
I tried using the dataset parameter instead of the report parameter. but it didnt help me.

neetira

<blockquote class='ipsBlockquote' data-author="pauljin">Hi you could make use of request object instead of session object. 
make use of request.setAttribute() and pass the request object to birt. You could set it either in controller or in jsp it self 
 
 
In birt get the parameter with reportContext.getHttpServletRequest().getAttribute(<attribute>) method and assign it to the dataset parameter. 
 
 
It should work in this way</blockquote>
 
hi pauljin 
 
how do i pass the request object to birt? 
 
i have a struts application which has report capabilities in MyEclipse. 
I have a jsp called table.jsp. In its action class, TableAction.java, i have put 
request.setAttribute("list", a1); 
 
table.jsp forwards to report.jsp which is the jsp page for the BIRT report. 
 
In the initialize of the BIRT report, i have put 
 
var request = reportContext.getHttpServletRequest() 
var myid = request.getAttribute("list"); 
reportContext.setParameterValue("Header_Param", myid ); 
 
I think I am getting a null value for "Header_Param" as my query does not execute and it keeps giving an error that 'cannot set null value to parameter 1" 
 
can you tell me how to send request object to birt? 
 
neeti

bhanley

You are missing two things based on my understanding. Let me try and summarize.

1) You have a query that needs to accept a dynamic parameter. The place holder for the dynamic value in the query should be a question mark. It appears you have this correct.

2) You need a report parameter for the report to accept the value needed by the query. I think you have this set up correctly as well.

3) You then need to bind the report parameter to the data set's query. If you open up the data set for editing, you will see a "Parameters" group on the left side. When you click that, you will see a parameter already there. This parameter (param_1) is auto-created by BIRT when you add a place holder (?) to a query (I think you mentioned you have seen this entry already). If you look on the data set parameter settings for param_1, there is a drop-down that will allow you to select a report parameter to bind to. This will force the query to use the value supplied as the report parameter inside the query.

Having done all this, you should have closed the loop between the report parameter and the data set.

Good Luck!

neetira

<blockquote class='ipsBlockquote' data-author="bhanley">You are missing two things based on my understanding. Let me try and summarize. 
 
1) You have a query that needs to accept a dynamic parameter. The place holder for the dynamic value in the query should be a question mark. It appears you have this correct. 
 
2) You need a report parameter for the report to accept the value needed by the query. I think you have this set up correctly as well. 
 
3) You then need to bind the report parameter to the data set's query. If you open up the data set for editing, you will see a "Parameters" group on the left side. When you click that, you will see a parameter already there. This parameter (param_1) is auto-created by BIRT when you add a place holder (?) to a query (I think you mentioned you have seen this entry already). If you look on the data set parameter settings for param_1, there is a drop-down that will allow you to select a report parameter to bind to. This will force the query to use the value supplied as the report parameter inside the query. 
 
Having done all this, you should have closed the loop between the report parameter and the data set. 
 
Good Luck!</blockquote>
 
 
Hi Bhanley 
 
I did all the 3 steps which you mentioned. 
 
But when i run the application, it is not taking the parameter which I pass. 
 
I tried the Property binding tab where I wrote the entire query as 
 
this.queryText = "select * from Contents_user where Header in(' " +params["Header_Param"].value+ " ')" ; 
 
and in Query tab of the data set, i have the query as 
select * from Contents_user 
 
and in initialize function of the report, i put 
 
var session = reportContext.getHttpServletRequest().getSession(); 
var myid = session.getAttribute("list"); 
reportContext.setParameterValue("Header_Param", myid); 
 
now when i run the program, it is working. 
 
the only difference i had in both ways, - 
for one, i had a dynamic parameter in the query itself and made a dataset parameter and bound to the report parameter 
 
for 2nd, i had the entire query written in Property binding of the data set 
 
but somehow only the 2nd method is working now. 1st method is not taking the value. 
 
Is it because of the IN clause? if we have an IN clause in the query, is it possible to use only the Property binding method? Why is it not possible to use the Dataset parameter bound to report parameter method?

raghul

I tried the Property binding tab where I wrote the entire query as

this.queryText = "select * from Contents_user where Header in(' " +params["Header_Param"].value+ " ')" ;

and in Query tab of the data set, i have the query as

select * from Contents_user

and in initialize function of the report, i put

var session = reportContext.getHttpServletRequest().getSession();

var myid = session.getAttribute("list");

reportContext.setParameterValue("Header_Param", myid);

the only difference i had in both ways, -

for one, i had a dynamic parameter in the query itself and made a dataset parameter and bound to the report parameter

for 2nd, i had the entire query written in Property binding of the data set

but somehow only the 2nd method is working now. 1st method is not taking the value.

Is it because of the IN clause? if we have an IN clause in the query, is it possible to use only the Property binding method? Why is it not possible to use the Dataset parameter bound to report parameter method?
[/quote]

Can you please elaborate the 1st and the 2nd method?
I have right now created only the report but not any project linking with JSP.
I thought I would try generating report first and then link it with JSP.

Please Help