How to choose between datasets before start of report?

Hi all,
i have two almost same data sets. Each of them have same names of columns. Only difference between them is, that first data set shows data old max 90 days and second dataset shows data older than 90 days.

My goal is: When i will start report it will offer me which data set to use. If it shows table with data older than 90 or less.

Is there a way how to do it?

I have to use 2.2.1 version of birt.

Find more posts tagged with

Comments

Tubal

If 2.2.1 has visibility built into the tables, you could just have both tables in your report, and hide the one you don't want to see with a report parameter.

I'm not sure if 2.2.1 has this option.

BlackSheep

Thank you for your answer.

I will use this option like last solution. Unfortunately i have to create report that will consume as less as possible of computing capacity. Your solution have to compute two tables and it means double load.

Tubal

Is your dataset based on a query? You can also rewrite your query based on report parameters.

BlackSheep

Yes, each dataset is one query which approuch directly to database and select and compute what i want. I discovered BIRT before few months. Already posted several reports but still newbie

How you mean to rewrite query based on report parameters?

johnw

On the Data Soure, there is a Property Binding for the JDBC URL. You can set it using a simple if statement to select which one to use.

Tubal

<blockquote class='ipsBlockquote' data-author="'BlackSheep'" data-cid="97131" data-time="1330715119" data-date="02 March 2012 - 12:05 PM">
Yes, each dataset is one query which approuch directly to database and select and compute what i want. I discovered BIRT before few months. Already posted several reports but still newbie

How you mean to rewrite query based on report parameters? </blockquote>
 
The first thing would be to set up your report parameter, so when your report starts, your user would select if they wanted the old data or new data. You could do this with a list box, or a boolean checkbox. 
 
If your dataset is a query, you probably have something like: 
 
SELECT * 
FROM table1 
WHERE mydate < current_date - 90 
 
and 
 
SELECT * 
FROM table1 
WHERE mydate >= current_date - 90 
 
So the only thing we need to change is your WHERE statement. 
 
So you could do something along the lines of (this is PostgreSQL, your sql flavor might be different): 
 
SELECT * 
FROM table1 
WHERE (CASE WHEN ? = 'OLD' THEN mydate <= current_date - 90 ELSE mydate > current_date - 90 END) 
 
The '?' in the query is your parameter. You set this in the parameters tab of your dataset. In my example, you would tell your report parameter to set the value to "OLD" if the user wanted the old data. If they didn't select old, it would send the new data. 
 
Obviously with this way, you would only need one dataset. The query would just select different data depending on the parameter.

BlackSheep

Tubal:

I realy like your idea. But even in past i had problem with creating parameters directly in query. The symbol ? doesnt work. What i read in tutorials, it should automatically create parameter in parameters. Do nothing in my BIRT and cause error after closing of window.

Is it possible that problem is in old version of BIRT. The version is one of things i cant change.

johnw

If it doesn't automatically create the parameter, then you can go to the parameters tab. The ? is just a placeholder. Data Set parameters get mapped to ? in the order they are recieved. So if your query has 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
where
a = ? AND
b = ? AND
c = ?
</pre>
 
In the data set editor, under the parameters tab, you would need 3 parameters. Normally the editor will automatically add those, but if it doesn't just add them manually. Just make sure the data types are correct. From there you can even link them to report parameters.

BlackSheep

Hi, so i tried some things but still didnt have result. My query is.

SELECT totalt.total, totalt.totalact

FROM (SELECT COUNT (t0.serial_number) AS total,
SUM (t0.is_activated) AS totalact

FROM rpt_view1 t0
inner JOIN rpt_view2 t00 on t0.serial_number = t00.serial_number
WHERE ( CASE WHEN ? = 'OLD' THEN (t00.days_skipped - 400) < 90 ELSE (t00.days_skipped - 400) > 90 END ) ) totalt

If i click on output column it gives me error message missing keywords. Then i create Parameter (Edit Data Set) with string data type and map it on simple report parameter (list box). After all this i always get error missing keyword.