Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Displaying Query Text in BIRT Report
Displaying Query Text in BIRT Report [message #719010] Thu, 25 August 2011 19:14 Go to next message
mwagley is currently offline mwagleyFriend
Messages: 2
Registered: August 2011
Junior Member
Please bear with me, as I am a newbie Smile

Is there an easy way of displaying the Query Text along side the results in a BIRT report?

For example, I have a Dataset called "A1". For example, I am using the following SQL Query to create the data set:

"select trunc(sydate) from dual"

In the final report, I want to see something like this in a GRID:

Query Text
select trunc(sysdate) from dual

Result
08/25/2011

If anyone can help me with this, I'd really appreciate it!
Re: Displaying Query Text in BIRT Report [message #719029 is a reply to message #719010] Thu, 25 August 2011 20:39 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Take a look at the attached example. One expression shows how to get
the query definition and the other shows a script in the afterOpen to
get the executed query.

Jason

On 8/25/2011 3:14 PM, mwagley wrote:
> Please bear with me, as I am a newbie :)
>
> Is there an easy way of displaying the Query Text along side the results
> in a BIRT report?
> For example, I have a Dataset called "A1". For example, I am using the
> following SQL Query to create the data set:
>
> "select trunc(sydate) from dual"
>
> In the final report, I want to see something like this in a GRID:
>
> Query Text select trunc(sysdate) from dual
> Result
> 08/25/2011
>
> If anyone can help me with this, I'd really appreciate it!
Re: Displaying Query Text in BIRT Report [message #719038 is a reply to message #719029] Thu, 25 August 2011 21:26 Go to previous messageGo to next message
mwagley is currently offline mwagleyFriend
Messages: 2
Registered: August 2011
Junior Member
Awesome help! Thank you so very much! That worked perfectly!
Re: Displaying Query Text in BIRT Report [message #1066291 is a reply to message #719038] Tue, 02 July 2013 01:42 Go to previous messageGo to next message
Beryl Yellow is currently offline Beryl YellowFriend
Messages: 23
Registered: July 2013
Junior Member
Hi Jason,

If I can put an update sentence in the Query, because I need to update the table before I get the result set as below;

update a set a.column1 = ####;
select * from a;
Re: Displaying Query Text in BIRT Report [message #1487178 is a reply to message #719029] Tue, 25 November 2014 16:30 Go to previous messageGo to next message
yoovraj khullar is currently offline yoovraj khullarFriend
Messages: 1
Registered: November 2014
Junior Member
Jason
I am not able to open this rpt attachment.
Can you please see.
I will be thankfull.
Regards
Raj
Re: Displaying Query Text in BIRT Report [message #1625308 is a reply to message #719029] Fri, 20 February 2015 09:40 Go to previous messageGo to next message
Paul Bappoo is currently offline Paul BappooFriend
Messages: 5
Registered: October 2009
Junior Member
This is a very useful technique, however I have a problem. I have a SQL query with 2 parameters and I want to replace the first ? with the first param and the second with the second.

When I use this technique it replaces both question marks. Looking online it appears that the Java replaceFirst should do the trick, but BIRT errors when I try to use that - as if it doesnt recognise the function.

In the error message it says:

Error evaluating Javascript expression. Script engine error: Wrapped java.util.regex.PatternSyntaxException: Dangling meta character '?' near index 0

This refers to JavaScript and when I look online it appears that JavaScript should process only the first occurrence, which of course it is not doing.

So I am wondering if BIRT is processing this as Java or JavaScript and of course, what the ultimate solution to my problem is.




Re: Displaying Query Text in BIRT Report [message #1625926 is a reply to message #1625308] Fri, 20 February 2015 18:28 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

I made a change to the query in the above example to add " and quantityordered > ?", set the value to 25 in the parameters section of the data set editor, and finally, changed the script in the afterOpen to this:

importPackage(Packages.java.lang);
var qry = this.queryText;
var temp = qry.replace("?","--");
temp = temp.split("--");
var newqry = "";
for(i=0; i<temp.length; i++){
	newqry = newqry + temp[i] + this.getInputParameters().values().toArray()[i];
}

reportContext.setGlobalVariable("myquery", newqry);


It worked for me in this simple case. For whatever reason, split wouldn't work for "?", so I had to replace it with "--" beforehand. That's why that is in there.


Michael

Developer Evangelist, Silanis

[Updated on: Fri, 20 February 2015 18:30]

Report message to a moderator

Re: Displaying Query Text in BIRT Report [message #1626549 is a reply to message #1625926] Sat, 21 February 2015 02:43 Go to previous message
Paul Bappoo is currently offline Paul BappooFriend
Messages: 5
Registered: October 2009
Junior Member
Many thanks Michael - works a charm. I wrote it up into a full tutorial so that anyone can follow along and easily understand... attached.
Previous Topic:Cell colspan change affecting other adjoining cell formatting
Next Topic:Streaming of large report data
Goto Forum:
  


Current Time: Tue Sep 01 18:32:57 GMT 2015

Powered by FUDForum. Page generated in 0.02457 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software