[SOLVED] Problem with date parameters [message #1065779] |
Thu, 27 June 2013 13:06 |
Paolo Aprile Messages: 24 Registered: June 2013 |
Junior Member |
|
|
Hi!
I'm currently trying to represent in my report a list of data obtained from a query that sets a time interval. SO i need to pass 2 dates to my report, a "daystart" end a "dayend".
My query goes like this:
SELECT testuser.username
FROM testuser
WHERE DATE(testuser.reg_time)
BETWEEN ?
AND ?
where testuser.reg_time is a timestamp field.
I know it works because if I input the dates directly, like this:
select testuser.username
from testuser
where date(testuser.reg_time)
BETWEEN '2013/06/03'
AND '2013/06/19'
it gives me exactly what it shoud.
(But only if i use the format YYYY/mm/dd. Others data formats give no results, and I'm thinking this is maybe part of the problem.)
This is the JSP code getting and sending the values:
------------------------------------------------------------
<birt:parameterPage id="birtTest" reportDesign="report/report1.rptdesign"
name="test"
pattern="frameset"
height="600"
width="800"
format="html"
title="test"
isCustom="true"
showTitle="true"
showToolBar="true"
showNavigationBar="true"
>
<table border=1>
<tr id = "data1">
<th> Data d'inizio selezione </th>
<td> <input type="date" name="daystart" id="daystart"> </td>
<br><br>
</tr>
<tr id = "data2">
<th> Data di fine selezione </th>
<td> <input type="date" name="dayend" id="dayend"> </td>
</tr>
<br><br>
<tr>
<td colspan = 2><input type="Submit" value="Richiedi il report"></td>
</tr>
</table>
</birt:parameterPage>
------------------------------------------------------------
The user fills the input fields selecting the data from a javascript app.
The report parameters are set (with the same name as the JSP input) and i linked them with the data set parameters.
I'm sure enough it works to a degree, because there's no occurrence for missing values in the report. So why can't I get my data?
Alas, this portion of the report remains empty, while other dynamic elements are correctly represented. I really don't know where i'm going wrong, and I've already tried a number of things.
Could it depends on the details of the parameters configuration? I tried to set them as date and as string and to convert the timestamps and... well, I tried. I'm open to any suggestion you might have!
Thanks for your patience.
Paolo
[Updated on: Thu, 04 July 2013 10:35] Report message to a moderator
|
|
|
Re: Problem with date parameters [message #1065812 is a reply to message #1065779] |
Thu, 27 June 2013 14:48 |
|
You might try just setting your query in your dataSet to:
SELECT testuser.username
FROM testuser
Then, in your dataSet's beforeOpen script, format the entered parameter value to the format you're wanting. Something like:
importPackage(Packages.java.text);
df = new SimpleDateFormat("yyyy/MM/dd");
ds = df.format(params["daystart"]).toString();
de = df.format(params["dayend"]).toString();
this.queryText = this.queryText + " WHERE DATE(testuser.reg_time)BETWEEN '" + ds + "' AND '" + de + "'";
This way, you control the format that is passed through the query.
Michael
Developer Evangelist, Silanis
|
|
|
|
|
|
Re: Problem with date parameters [message #1067073 is a reply to message #1066777] |
Fri, 05 July 2013 16:53 |
|
No problem. If you want to set up a simple report with the sample database showing exactly what you tried, I can take a look and we can try to get it solved for you in the report.
Michael
Developer Evangelist, Silanis
|
|
|
Powered by
FUDForum. Page generated in 0.02834 seconds