Defaulting Parameters to values from a Dataset

New user to Birt. Apologies if this is a basic facility!
I have a table of data which is date-based. I want to be able to use the latest date in the table as a parameter for a report but only if the input parameter is null.
For example, if the input date parameter is null get the latest date from the table and use that as the parameter, without having to select it.
I have the dataset to retrieve the latest date. Can I use this in the "initialize" script to set the value of the input date parameter if the parameter value is not input?

Any help you can provide on this would be most welcome!

Find more posts tagged with

Comments

mwilliams

Hi AlanC,

So, the latest date in the database isn't the current date? Are you having the user select a range of dates? Or do they just select a specific date?

AlanC

Thanks for the quick response Michael.
Basically, what I am doing is selecting a number of months up to and including the input parameter. If that parameter is not supplied, then I want to retrieve up to the latest month (using the latest month as the parameter value, as this is used in the text of the report).

mwilliams

AlanC,

So, the user is selecting month, not a specific day? So, if you could set the parameter to the current month value if the parameter is left blank, it would work for you?

AlanC

Hi Michael
The problem is that we do not necessarily have data up to the current month. I want to retrieve data for the 12 months up to and including the input date. If that date is not supplied, I want the 12 months up to and including the latest date in the file.

Can this be done in the report "initialize" script?

mwilliams

AlanC,

I haven't done this before. I'd have to look into it. Not sure how you'd grab the latest date before running the query. That'll be the part to figure out. Possibly could use another query. Modifying the dataSet query to use this date, if you can get it, would be done in the beforeOpen script of the dataSet.

rpolunsky

One way is to postprocess your query in the beforeOpen event. If you revise your query so it exposes max(datefield) as a separate column, then you can check in beforeOpen to see whether the date parameter is null.

If it is null, add the where clause to your queryText such that trunc(datefield,yearmonth) >= trunc(maxdate,yearmonth) - 12 months

If it is not null, add the where clause based on the parameter
trunc(datefield,yearmonth) >= trunc(paramdate,yearmonth) - 12 months

The actual SQL will depend on your database. There are probably other approaches that will work as well.

AlanC

Thanks for the input guys.
However, as I mentioned, I'm fairly new to BIRT and am newborn in terms of Java and scripting. Do you have a similar example of how to access the reult of another dataset within the beforeOpen event of the dataset?