Storing query result in report parameter

[jserge]

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

[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:

reportContext.setPersistentGlobalVariable(name, value);

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:

params["param_name"].value = "something";

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.

this.queryText = this.queryText + " WHERE userid = " + reportContext.getPersistentGlobalVariable("userID");

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.

reportContext.setPersistentGlobalVariable(name, userIdValue);

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.

Using Java to Write a BIRT Event Handler - Tips & Tricks - BIRT Exchange

Good Luck!