Storing query result in report parameter

Hi,

I need to get a value using SQL and use this value later in the report as parameter for other queries and various conditions.

For example, I need to call this query somewhere in the early stages of report (initialize, before factory, etc):

select userid from users where ....

and then store this "userid" as parameter for later use.

Any advice?

I am using 2.3.2

Thank you

Find more posts tagged with

Comments

bhanley

Once you have your UserID, you can set a persistent global variable in the scripting layer. This variable will be available within any other event handler you may need: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable(name, value);</pre>
 
You can also directly set the value of the report parameter in a script. this will allow you to bind the value into a query using the data set editor (take care when you do this assignment as your Data Set may have already been built: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>params["param_name"].value = "something";</pre>
 
A last option would be to modify your query in the Data Set's initialize script. There you can check your persistent global variable and use the value to further tweak your query text. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText + " WHERE userid = " + reportContext.getPersistentGlobalVariable("userID");</pre>
 
Any of those approaches should work fine. 
 
Good Luck!!

jserge

Thanks a lot! 
 
How can I ensure that the SQL that returns my userIdValue is executed before any other queries that are used in the report? 
 
Say, I want to run the query first (getting userIdValue), then then do as you advised. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable(name, userIdValue);</pre>
 
Is there any call to execute the SQL from javascrip? 
 
Thanks!

bhanley

You should probably create a Java Event Handler and invoke that via the report's initialize event. Executing SQL from a full-blown Java object will perform much better. 
 
There are examples for creating these handlers on the DevShare. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/634-using-java-to-write-a-birt-event-handler/#description'>Using Java to Write a BIRT Event Handler - Tips & Tricks - BIRT Exchange</a> 
 
Good Luck!