nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using parameters - report results are outside the given range selected (time)

summercamp

I am not a SQL expert , and I am an absolute beginner with BIRT so please bear with me.
 
I am trying to write a report where the user can input a given time range, and the result will show data from a postgres database within those times.
 
I'm using parameters for the user input.
 
So when the user runs the report he gives a start time and an end time.
 
But the result includes times which are outside the range, usually before the start time rather than after the end time.
 
When the same query is executed in the DB (with the '?' hardcoded) I get the correct times shown.
 
When I repeat the query in BIRT with the times hardcoded I still get results outside the time range.
 
I think this shows that the problem is with BIRT and not the query.
 
Is there something that needs to be changed in the parameter settings of BIRT so that only results within the time range specified by the user are returned? 
 
thanks in advance
 
summer 
 
PS I am using Eclipse version Neon.2 Release (4.6.2) with Postgres v9.5

Find more posts tagged with

Comments

Clement Wong

Please let us know the SQL statement when you run it from a database client, outside of BIRT that works.
 
Please attach your .rptdesign. Please remove the database URL and credentials, in case there is sensitive information.

summercamp

Hi Clement 
 
Thanks for your reply. Below is the DB code with text changes for security. The parts in bold text are replaced in BIRT with the parameter code '?' 
 
I can't see how to attach files though. I can cut and paste the rpt data if you wish. 

select DB.device.name, 
DB.alarm_node.node_id, 
DB.alarm_node.alarm_time, 
DB.alarm_node.clear_time, 
to_char(DB.alarm_node.alarm_time, 'day'), 
DB.alarm_node.description_key 
from DB.alarm_node,DB.device 
where DB.device.node_id = DB.alarm_node.node_id 
and DB.alarm_node.alarm_time >= â€˜2017-05-01â€™ and DB.alarm_node.alarm_time < â€˜2017-05-03â€™ and ((to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between â€™18:00:00â€™ and '23:59:59' or 
to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between '00:00:00' and â€™06:00:00â€™) 
 or trim(to_char(DB.alarm_node.alarm_time, 'day')) in ('saturday','sunday')) 
and DB.alarm_node.severity = 1 
order by alarm_time

Clement Wong

We'll need the report design to analyze.
 
 
> I can't see how to attach files though.
 
When you reply, click on the "More Reply Options".
 
Then under Attach Files, you'll need to Browse... for the file, and click on "Attach This File".
 
Finally, click on "Add Reply".

summercamp

thanks Clement. File attached.

Clement Wong

Thanks for attaching the .rptdesign. My hunch is that the Data Set parameter substitutions are not being set to the format that you're expecting.
 
Are you able to log the query that is received by the database server?
 
We can also use an alternative method of substituting parameters in the beforeOpen event by replacing element of the SQL select statement with this.queryText.
 
First, in the Data Set's Query, change the ?s to actual (dummy) values:
 
... 
and DB.alarm_node.alarm_time >= '2001-01-01' and DB.alarm_node.alarm_time < '2002-02-02' 
and ((to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between '03:03:03' and '23:59:59' or 
to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between '00:00:00' and '04:04:04') 
or trim(to_char(DB.alarm_node.alarm_time, 'day')) in ('saturday','sunday')) 
...
 
 
Then in the Data Set's beforeOpen event, we will do the substitution of each parameter value. Now, I'm not sure what format you have allowed the end user to enter the date and time. I see the Prompt Text as "enter start date and time (yyyy:mm:dd 24hh:mm:ss)"
<pre class="_prettyXprint _lang-">
// SQL snippet
//
// and DB.alarm_node.alarm_time >= '2001-01-01' and DB.alarm_node.alarm_time < '2002-02-02'
// and ((to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between '03:03:03' and '23:59:59' or
// to_char(DB.alarm_node.alarm_time,'hh24:mi:ss') between '00:00:00' and '04:04:04')
//
//
// Example if 'start date' where entered as 2017-05-18 08:18:26
// and if 'end end' where entered in the same yyyy-MM-dd hh:mm:ss format

this.queryText = this.queryText.replace("2001-01-01", params["start date"].value.split(" ")[0]);
this.queryText = this.queryText.replace("03:03:03", params["start date"].value.split(" ")[1]);

this.queryText = this.queryText.replace("2002-02-02", params["end date"].value.split(" ")[0]);
this.queryText = this.queryText.replace("04:04:04", params["end date"].value.split(" ")[1]);

</pre>
You can log the this.queryText at each step to make sure the formatting/syntax is what the database is expecting. SInce you are using Open Source BIRT, whereas in commercial BIRT, you can write to Eclipse UI's Error Log directly, you'll need to relaunch Eclipse, and open "eclipsec.exe". The debug statement will be written to a separate window.
 
You can add a debug statement like this: java.lang.System.out.println( this.queryText );

summercamp

Hi Clement
 
I will try your solution, thanks for that.
 
In the meantime, your statement :
 
"My hunch is that the Data Set parameter substitutions are not being set to the format that you're expecting."
 
doesn't follow the fact that the results will show data outside the time range even when I remove the parameters and hardcode the dates and times (from my original question):
 
"When I repeat the query in BIRT with the times hardcoded I still get results outside the time range."
 
I will report back on my findings

Clement Wong

Thanks for pointing that out. We can skip the substitution part for now.
 
However, we would still want to see what query the database is receiving as compared to what the SQL statement is being sent.

summercamp

thanks Clement, I will see if I can extract that information for you

summercamp

Please can you let me know whether postgres logs this information somewhere? If it does what would the log be called? If not is there something I can add to the query to generate a log that will show what the database is receiving from BIRT?
 
thanks again
 
summer

Clement Wong

Yes, you'll need to enable logging on the Postgres server side.
 
This was originally asked for Postgres 8.3, and there is a reply that the steps work for Postgres 9.5 (the version that you're using).
<a data-ipb='nomediaparse' href='https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries'>https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries</a>

Siva Rao

Hi i have 2 parameters named as starttiem and endtime.It's datatype is time.
when i want to compare them it shows error as string is passing from the parameters.
how can i convert string to time format(12Hrs format) and compare those two times?
 
Thanks in advance.

Clement Wong

Siva Rao,
 
This is a different topic, and would be good to know more information about your issue.
 
What version of BIRT you are using? Also, is it open source, or commercial?
 
What database are you using?
 
What is your current SQL statement that is being passed to the database server, and what is the syntax of the SQL that you would like it look like?
 
Is there an exact error message that you can share?