Birt Scripted Query Question

I would like my birt report to select between two different queries, when a user selects an ID and a different one when a user selects a range of dates. I understand I have to do this in JavaScript but how do I set the report up to ready to receive the data?

Any help suggestions or examples would be very helpful.

Thanks

Find more posts tagged with

Comments

bhanley

You can use the beforeOpen scripting event on the Data Set to modify the query. From this event you can look at the input parameters (in your case a date or an ID) and act according to which one is populated.

The key line of Java Script that you need for each condition is this.queryText = "Contents of Query";

The pseudocode is like this:

if(validate ID Parameter (i.e. reportContext.getParameterValue("pParameterName")){
this.queryText = "Some Query";
}
else if(validate Date Parameter (i.e. reportContext.getParameterValue("pParameterName")){
this.queryText = "Some Other Query";
}
else{
// Catch any others
this.queryText = "last Query";
}

One final tip. Be sure to assign a default query to your Data Set so it will compile and test well in Preview mode. The text you use in the befroeOpen even will override anything set for a query when you create the Data Set initially.

Hope this helps.

--Brian

timofei

Overall I like the design of the report (visually) now, do I need to have the script build my design or can I keep the current view? Or better yet, do I have to change the data Set Columns when the script determines which query to run?

bhanley

If the two queries return the same set of columns you are fine, you can simply leave the existing report in place and swap out the SELECT clause as I described above. If the queries return different sets of data, that is another issue entirely. What you would likely want to do then is build out two data sets and bind a data set to a corresponding table. Hide each table by default, and reveal the appropriate table based on the user's input. This way you are not trying to fit both data sets into a single destination when the requirements for each may be different.

timofei

thank you for all your help. I am having a strange issue I had what I thought every set up, but I forgot one field. I added the missing field to both the script and the query and now it is saying one by one a field is missing. I know the fields are there but here is the message: Column binding "CROSS_COMPUTING" has referred to a data set column "CROSS_COMPUTING" which does not exist. Do you have any idea why this error is occurring?

Thanks Again