Binding multiple datasets to table

Erebel55 · June 6, 2012

Birt 2.6.1

Is there a way to bind multiple datasets to a grid or table? So far I can only figure out how to bind one.

mwilliams · June 6, 2012

You can only bind one dataSet to a table. The ways around this are to either join your two dataSets into one, then bind that dataSet to the table, or to embed elements from the second dataSet into your table and use a filter or dataSet parameter bindings to limit the data for the embedded element.

Erebel55 · June 6, 2012

Thank you

By the way what I was trying to do was add the values of two columns from two separate dataSets but I guess I can use a join dataSet and a computed column.

mwilliams · June 7, 2012

Yeah. You could do that or you could use global variables. If the joint dataSet works without taking too much extra time, great. If not, let me know, maybe we can do something else.

Erebel55 · June 7, 2012

Well the joint dataset idea had to be trashed because i ended up having to add up 3 data items from 3 different datasets. So what I did was create a dataset that unioned the 3 values that I needed and then binded that to the grid. Not a very pretty way..but I am very interested in hearing about global variables.

Thank you.

mwilliams · June 12, 2012

Sorry for the delay. If you just need to add a couple or few values from different dataSets, you can set the value you need from each dataSet to a reportContext.setGlobalVariable("varName","value");, then, recall those global variables where you need them, for your calculation. If you show me some sample data and what you want your final result to be, I'll make you a quick sample.

gkjann · July 13, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="102883" data-time="1339520284" data-date="12 June 2012 - 09:58 AM">
Sorry for the delay. If you just need to add a couple or few values from different dataSets, you can set the value you need from each dataSet to a reportContext.setGlobalVariable("varName","value");, then, recall those global variables where you need them, for your calculation. If you show me some sample data and what you want your final result to be, I'll make you a quick sample. </blockquote>
 
Hi, 
 
I have the following scenario: 
 
I am using a flat file data source and don't have the ability to join the data sets. 
 
Dataset1: Field: Signer_Name - has EMP ID 
Dataset2: Fields: EMP ID, EMP NAME, EMP EMAIL. 
 
I have a need to read the EMP ID (Multiple values) and then display the results for the EMP from the Dataset2. I was able to do this on the report designer using the Nested Tables, but the realtime report takes a lot of time to generate and sometime times out. Is there a way that I can read values from Dataset1 into a Global Variable and then display the matching EMP details from Dataset2 ? 
 
 
Any suggestions will be greatly appreciated.

mwilliams · July 16, 2012

Can you show me a few lines of data from each dataSet and also what you want the output to look like? Also, let me know your BIRT version and I'll make you an example.

gkjann · July 17, 2012

Dataset1: Field: Signer_Name - has EMP ID ( like 5 digit numbers), Sign_Date - Date and time Stamp.

Dataset1:

Signer_Name, Sign_date
11111, 1/1/2012 10:00:00 AM
11112, 1/20/2012 10:00:00 AM
11233, 10/11/2012 11:00:00 AM

Dataset2:

EMP ID, EMP NAME, EMP EMAIL
11111, TOM CRUSE, Tom_Cruse@gmail.com
11233, Mike Tyndal, Mike@gmail.com

The Dataset1 is being derived from one business process (Employee Signature on purchase orders) and the Dataset2 is being derived from a different business process (Full Company Employee Dataset).

Other fields in the Dataset1:

Order_number, Order_desc, Order_date, Vendor_ID.

My expectation is that when the report is run, it picks up the order details and in the signature details (Table), I want to display the EMP_NAME and EMP_EMAIL by looking at the EMP ID from Dataset2 and Corresponding value if available from Signer_Name in dataset1.

Output:

Signer Name, Signer Email, Signature Date
EMP_NAME, EMP_EMAIL, SIGN_DATE
TOM CRUSE, Tom_Cruse@gmail.com, 1/1/2012 10:00:00 AM
Mike Tyndal, Mike@gmail.com, 10/11/2012 11:00:00 AM

I hope I was able to provide all the information that you asked for.

Thank you

mwilliams · July 17, 2012

Here's an example where the name and email are stored in arrays in the onFetch of the customer directory, then accessed in the purchases table by using the id field. Take a look. You can also do the same with persistentGlobalVariables, if you'd like. As I put the name and email in the employee id slot of the array, you can name your global variables after the employee id, so you can easily access the correct values. This design was made in BIRT 3.7. Let me know if you have questions.

gkjann · July 20, 2012

Hi,

THANKS A LOT FOR THE QUICK HELP. I was able to get the code to work with my need. The response time is great.

Thank you,

G

mwilliams · July 20, 2012

You're very welcome! Let us know whenever you have questions!

gopi.inampudi · July 7, 2015

I need help for my scenario.
 
I have two datasets. and in date parameter i am passing the todays date. If the day is saturday, then dataset1 should be execute else dataset2 should be execute. 
 
How should i impliment this logic in report???

mwilliams · July 7, 2015

There are several ways you could handle this. Here are a couple
 
<ul><li>Use two different tables bound to different data sets. Depending on the day, drop the appropriate table from the report in the beforeFactory.</li>
<li>Depending on the changes that you're making with the query, you could use a single data set and change the query in the beforeOpen method of the data set.</li>
</ul>Let me know if either of these sounds like it'd work for you.

gopi.inampudi · July 14, 2015

Thank You Williams.
 
If possible can you please provide me example for the second one?
 
If Business='sales' then dataset1
else dataset2
 
" Depending on the changes that you're making with the query, you could use a single data set and change the query in the beforeOpen method of the data set."

JFreeman · July 14, 2015

What I believe Michael was indicating in point 2 is not using 2 different data sets, it is dynamically modify the query of a single data set.
 
This is where the caveat comes in that Michael indicated about it depending on the changes you are making with the query. If the query changes the data set structure to much, such as different columns with different names/data types, this method may not work for your situation.

SnehaPun · December 7, 2016

Hi , I have used <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=7137' title="Download attachment">NameIDLookupKeyField.rptdesign</a> as a reference and created a report . I have created two dataset one order data set containing createdby and few other details and another userDetails dataset with username and email id and I m trying to fetch email ID from UserDetails for the createdby_ in Order dataset.It is working fine for a single record but with multiple record I m getting the same email ID for all the records.

Binding multiple datasets to table

Comments

Categories