Limiting DB Query Execution

I have a report with labels and data elements similar to those shown below. Roles AX are obtained from a DataSet query where ROLE in ('A1', 'A2'). Similarly, BX Roles are obtained from a DataSet query where ROLE in ('B1', B2'). To display the A1 Role, I created a layout table with one row and one cell, bound the AX DataSet to the table, and added a BIRT filter to the table (row["ROLE"] equal to "A1"). The same approach was taken to display the remaining roles. 
 
Label 1: Role A1 
Label 2: City, State Zip 
Label 3: Role B1 
Label 4: Phone 
Label 5: Role A2 
Label 6: Phone 
Label 7: Role B2 
... 
 
I would like to limit the number of times my AX and BX queries are executed, and I have read that each DataSet to table binding creates another DB query.

In a BIRT Newsgroup posting, <a class='bbc_url' href='http://dev.eclipse.org/newslists/news.eclipse.birt/msg28290.html'>http://dev.eclipse.org/newslists/news.eclipse.birt/msg28290.html</a>, I learned that BIRT 2.2.2 supports reuse of a query by binding to a previous Report Item. I followed the steps in the Newsgroup posting, but when I bound the table for Role A2 to the Report Item for Role A1, the BIRT filter for A1 was copied to the A2 layout table and was immutable. Does anyone know how to modify a BIRT filter for a layout table that has been bound to a Report Item? Alternatively, is there a different way to reuse my query? 
 
Thanks in advance for any help you can provide. 
 
Jennifer

Find more posts tagged with

Comments

cypherdj

Hi Jennifer,

I'm not quite sure I understand how the underlying data is stored in the database, so my suggestion may not make sense for your problem, but is there a way you could "preload" all the roles into one data set and use a joint data set to associate your labels and roles?

Could you maybe give more detail on your data set(s) query(ies)?

Regards,
Cedric

jlentz

Hi Cedric,

I think I may have overcomplicated the problem by mentioning 2 DataSets. Assume I have one DataSet as below:

select ROLE, NM from PERSON where ROLE in ('Manager', 'Office Assistant', 'Engineer', 'Analyst')

A sample report design layout to illustrate the problem is:

Office Location: New York, NY
Manager: John Smith
Total Employees: 25
Fire Station Distance: 10 miles
Number Exits: 2
Engineer: Richard Knox

John Smith and Richard Knox will both be returned from my query above. I would like to execute the query once and filter the displayed results by Manager for the Manager cell and by Engineer for the Engineer cell. Currently, I have one table per role with each bound to the same DataSet, but with different BIRT filters to display the correct data. I've tried naming the first table and binding the named Report Item to the second table, but the first table's BIRT filter is copied over and is immutable (it's "Manager" when I need it to be "Engineer"). Do you have any suggestions on how I can write my report such that the ROLE query will be executed only once AND I can display my filtered data in various locations throughout the report?

Thanks,
Jennifer