Using One DataSet To Filter Another DataSet or Table?

Hello,
 
 I'm a neophyte in the BIRT world, and I was wondering if I could get some input on a technique...
 
 Recently, due to a software upgrade, a particular application that I develop for and administer began having problems with particular Crystal based reports that we have been using for a couple years. This problem has been identified as a bug, and will be addressed in a future update. In the meantime, though, I was sort of left hanging. This application supports two reporting engines: Crystal and BIRT, with BIRT being the preferred solution for future development. Therefore, I decided to tackle the job of rewriting these reports in BIRT.
 
 Luckily, I'm familiar with Eclipse in general, JavaScript and Java. It took two days, but I was able to rebuild this report in BIRT. It's one of our more complex ones, in that it has fifteen separate datasets. The layout however is fairly basic. Once I got the hang of creating datasets, and passing parameters for use in the WHERE clauses, the rest of the process fell in line quickly. I was also able to increase the performance of this report from about ten minutes to under one minute, by refactoring the SQL queries. All in all, this might have been a blessing in disguise.
 
 There's two flavors of this report. The first accepts a record ID via a report parameter, which is passed in by the application. It prompts the user for two other parameters, a start date and end date used as parameters in most of the datasets as part of the WHERE clause. This version works great.
 
 The second flavor accepts a record ID via a report parameter, and then in the first dataset (bound to the first display table in the report, to guarantee execution order), it retrieves two datetime values, one from a table column, and the other being SYSDATE. The goal is to use these two values as filters in the remaining datasets, to restrict displayed data to a particular time range.
 
 I tried adding code to the onFetch event for the first dataset, and then set a persistent global variable to the value of the start date and sysdate fields returned by the dataset. Then, I added code to the onPrepare events for each of the display tables, and dynamically added filters to the tables at runtime based on these two persistent global variables. I thought this would work, but after playing with this idea, I cannot seem to get it to. The tables keep returning no records when I dynamically filter them based on the two variables. Although, I know the filtering code is working, because I can hard code the variables to specific values, and the filtering works. So, the problem seems to be related to retrieving the values from the first dataset, and setting the global variables.
 
 I can think of two ways to handle this:
 
 1) Filter the data at the presentation layer level, which is what I attempted to do. If you say that this is a reasonable method, I will continue to pursue this course, and will try to figure out what the issue is.
 
 2) Filter the data at the dataset level, which is what I'd prefer, since it would probably be more efficient due to returning less data to be further processed. I cannot quite figure out how to do this, though.
 
 I'll keep plugging away at this, and hopefully I'll figure it out... But, I'd really like some input from people 'in the know' to see which of these ideas is actually workable, or if neither is a good idea, how this should be handled. If anyone knows of an example report demonstrating such concepts, I'd love to see it.
 
 I guess if I generalize my question, it would go like this: "How can one take a value returned by one dataset, and use this to filter other datasets or tables?"
 
 Thanks in advance for any advice, pointers, or suggestions.
 
Brad

Find more posts tagged with

Comments

pricher

Hi Brad,
 
Attached is an example that shows how you can store a column value from one query and use it as a filter in another query.
 
Here are the steps:
 
1. Report Variable "vCustomerNumber" is defined in Data Explorer.
2. In the onFetch method of First Data Set, the customer number of the query is stored in the report variable;
3. In the beforeOpen method of Second Data Set, the where clause of the query is modified by replacing the dummy value with the actual customer number.
 
Hope this helps,
 
P.

bradstockdale

Hello,
 
 This sounds exactly like what I need. It also sounds close to something I tried yesterday...
 
 The more I thought about my problem, the more I got to wondering if the date format was wrong.
 
 One of the fields is stored in unix epoch time, in milliseconds, so I am converting that to a Date object. The other value is being created by calling Oracle's sysdate, so I'm assuming it is valid, but maybe I'll convert that to a Date object as well, just to make sure the format is correct.
 
 Thank you very much for the example. I'll take a look at it right now.
 
Brad

bradstockdale

Oops. Looks like I'll need to get a newer version of BIRT to take a look at the report. I'm stuck using an ancient version due to the application requirements (2.6.2). Hopefully I can mimick this functionality in this old of a version. It has the same events, so I should be able to shoehorn it in.

bradstockdale

Okay, I took a bit of a different approach here... But I seem to be on the right track now... There were a multitude of problems.
 
First, as you suggested, I used the onFetch event to set the start datetime value = to the appropriate column.
In the same event, instead of retrieving sysdate, I set the other variable = new Date(). For some reason, when I was using sysdate, it would display properly, but when filtering on the value, it would go back to 1969. Didn't spend too much time diagnosing it; just moved on to an easier fix.
 
Then, in the beforeOpen even handler, I added code to parse the datetime values in the two variables. Broke them down into their component values, and then built a string representing the datetime. Format used was YYYY-MM-DD HH24:mi:SS. Did this for both variables.
 
Then, I took these variables, and used the setInputParameterValue method to set the query parameters to the formatted datetime strings.
 
By some miracle, this is working for three out of fifteen of the tables that I've added the JS code to. I'll continue to modify the sections until everything is scripted similarly.
 
Thank you for your input on this. It gave me enough clues to figure out the above described solution.

lockdavid

BIRT 4.3.2 user here.
 
I am trying to do something similar to the above but I'm noticing that in the example rptdesign that pricher uploaded, the first dataset has the customer number as a fixed value, "141".
 
I am creating a report that accepts an excel spreadsheet as the first data source/data set. This spreadsheet will have many different student numbers in it. 
 
The report then takes the student numbers from the first data set, goes to a second data source (the central university database), and creates a second data set with information about each student.
 
When I adapt the example rptdesign to my report, I only get information about the last student in the spreadsheet. It's as if it can only read in one row from the first dataset as input for the second, and I need it to read in ALL rows from the first as input parameters for the second.
 
Hope this makes sense - thanks!

pricher

Hi,
 
If you need to join heterogeneous data sets, you can always use the Joint Data Set feature in BIRT. The attached example has two data sets coming from Classic Models, Customers and Orders. The Joined Data Set joins the two previous data sets on CustomerNumber.
 
This should suffice for your case.
 
regards,
 
P.

lockdavid

Hi P. - thanks so much for the fast reply. I don't think a joint dataset will work though. Dataset 1 is a list of student numbers, and dataset 2 is produced by a query with student number as an input parameter. I need to pull the student numbers out of the first dataset and plug them into the query that produces dataset 2...
 
I think I need to take the values in dataset 1 that look like this:
 
123456789
897654321
456789123
 
and store them somehow as a comma-separated list:
 
123456789,897654321,456789123
 
then drop that list into the second query. 
 
select * from where student_num in (?)
 
And dataset 1, the list of student numbers, won't always be the same length.
 
It's beyond my self-taught capabilities to know if this is even possible, let alone to make it happen. If this makes sense, and if you're able to help, I'd be grateful!

actuser9

Hi Pierre,
 
My request is some what close to the sample report provided but I am not able to get it work. I have two databases with a common field. I need to pass the ALL the values(data type is string) of one column in a data set to second data set. This is to reduce the performance hit on the second data set to the database. There are millions on records in the second data set, by passing values from first data set the performance can be made better. ALL the column values needs to be selected by default.
 
Can you please provide help on this?
 
Thanks, 
UY

gangadhar15

Any one done the above request , please share the report. I am stucked for a month.
 
Hi P. - thanks so much for the fast reply. I don't think a joint dataset will work though. Dataset 1 is a list of student numbers, and dataset 2 is produced by a query with student number as an input parameter. I need to pull the student numbers out of the first dataset and plug them into the query that produces dataset 2...
 
I think I need to take the values in dataset 1 that look like this:
 
123456789
897654321
456789123
 
and store them somehow as a comma-separated list:
 
123456789,897654321,456789123
 
then drop that list into the second query. 
 
select * from where student_num in (?)
 
And dataset 1, the list of student numbers, won't always be the same length.
 
It's beyond my self-taught capabilities to know if this is even possible, let alone to make it happen. If this makes sense, and if you're able to help, I'd be grateful!