Eclipse Community Forums: BIRT » Displaying Query Text in BIRT Report

Home

Home » Archived » BIRT » Displaying Query Text in BIRT Report

Displaying Query Text in BIRT Report [message #719010]

Thu, 25 August 2011 15:14

Eclipse User

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 16:39

Eclipse User

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!

Attachment: displayquery.zip
(Size: 2.81KB, Downloaded 2633 times)

Re: Displaying Query Text in BIRT Report [message #719038 is a reply to message #719029]

Thu, 25 August 2011 17:26

Eclipse User

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]

Mon, 01 July 2013 21:42

Eclipse User

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 11:30

Eclipse User

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 04:40

Eclipse User

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 13:28

Eclipse User

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.

[Updated on: Fri, 20 February 2015 13:30] by Moderator

Re: Displaying Query Text in BIRT Report [message #1626549 is a reply to message #1625926]

Fri, 20 February 2015 21:43

Eclipse User

Many thanks Michael - works a charm. I wrote it up into a full tutorial so that anyone can follow along and easily understand... attached.

Attachment: BIRT display or show the SQL query on the report design.pdf
(Size: 1.66MB, Downloaded 1623 times)

Previous Topic:	Cell colspan change affecting other adjoining cell formatting
Next Topic:	Streaming of large report data

Goto Forum:

-=] Back to Top [=-

Current Time: Fri Mar 28 03:15:39 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter