Calculations Using Data Elements From Multiple Report Tables

I am developing a multi-table report. The first table is created using one dataset, and the second table is created using a second data set. The SQL for each table returns data rows for a requested Year. Visibility of the individual data rows is hidden. Data bindings define aggregations for each of the data cells in each table footer. Each table has 13 columns, one column for each month in the year and a year total.

I would like to subtract the values in the footer of the first table from the values in the footer in the second table and display the results of the calculations, but I can't figure out how to access both sets of data bindings required for the creating the expressions for the calculations.

Documentation indicates that a table can be bound to one and only one dataset or report item. Is there some other report item that allows references to data bindings of multiple tables/report items or does this report need to be architected differently to generate the results that I am looking for?

Also, once I have this additional set of calculations, I would like to be able to create three charts displaying the totals from both table footers and the resulting calculations using those table footer totals. Using BIRT I've created charts using raw data from an individual dataset, so I've been able to define values for the Y-axis and X-axis using individual data elements from the dataset. Is there a way to define a Y-axis series for each of these sets of calculated data values? Is there a way to define a X-axis series for the corresponding month names and year total?

Find more posts tagged with

Comments

cypherdj

Is there any way you could use a joint data set to amalgamate your 2 data sets into 1 and apply calculations that way to generate your 3rd table?

jjfeigal

I created one dataset containing all of the data and was able to do all of the calculations in one table on the report. This was not how I wanted to present the data, but it accomplished the task.

I still can't figure out how to chart my data values in the table footer, though. Any suggestions?

cypherdj

<blockquote class='ipsBlockquote' data-author="jjfeigal">I created one dataset containing all of the data and was able to do all of the calculations in one table on the report. This was not how I wanted to present the data, but it accomplished the task. 
 
I still can't figure out how to chart my data values in the table footer, though. Any suggestions?</blockquote>
 
What version of BIRT are you using? Do you use the eclipse plugin? 
 
Could you possibly attach your report design and let us know what type of chart you would like to create? 
 
Cheers, 
Cedric

jjfeigal

I am using BIRT 2.2.2. I don't understand the question regarding an "eclipse plugin" so I am probably not using one unless you get it by default.

I am trying to create a bar chart or a line chart where the X-axis is Month (e.g., 01, 02, 03, ... 12) and the Y-axis is associated with the values from a table footer. Each value in the footer corresponds to an Aggregation data binding, so there is not one single data binding that can be assigned to the Y-axis.

Attached is the rptdesign for a version of my report where I have created two tables, and I would like to create a separate chart for the values in each table's footer.

Also, if you have ideas about creating the third table using calculations with the values from the first two tables I would be interested. It appears that a report item (e.g., a table) does not have access to the data bindings of more than one table. I have created another version of the report using only one dataset and one table so that I can perform the calculations, but that report design is more complicated/detailed than need be for this charting question.

cypherdj

Hi, I'll try to have a look later on today,

ciao,
Cedric

skroslak

sign me up on this one. I'm interested if there is any way to refer to computed fields from multiple tables (each connected to it's own data set) and use those values elsewhere. Assume I don't want to use another SQL query or scripting, is there an elegant way to do it?

semime

sign me up on this one aswell.

I'm interested if there is any way to refer to computed fields from multiple tables (each connected to it's own data set) and use those values elsewhere.

martinvr

I am tyring to use computed aggregates from a grouping in a table inside a chart that is in the table footer. I only have one data source. everything I try results in a blank chart.

clearstone

It seems that charts can only be based on table detail rows, not aggregates in group footers. If there is any way to do this, I would be very interested.

Jim

mwilliams

Hi Jim,

You can do aggregations in the chart. If you go to the "select data" tab and click on the group and sorting button next to the Category (X) Series, you can do grouping and choose an aggregation in there. This should allow you to do standard aggregations on the chart.

jashaffner

I too need a way to create a chart or even a table accessing the results from few other tables to compare.

j

ebirkenes

I don't know if you tried this, but can you use reportContext.setPersistentGlobalVariable("name", this.getValue()) on onCreate on the aggregations to store the values and then use them in the third table?
You can then access the variables using this.setDisplayValue(reportContext.getPersistentGlobalVariable("name")) on either onCreate or onRender. (Sometimes onCreate doesn't work and you have to use onRender.)

Thats what I usually do when I need to access values across different tables.
I don't know how to create charts from them though, I've never done that.

Nitin83

<blockquote class='ipsBlockquote' data-author="'ebirkenes'" data-cid="58984" data-time="1263290554" data-date="12 January 2010 - 03:02 AM">
I don't know if you tried this, but can you use reportContext.setPersistentGlobalVariable("name", this.getValue()) on onCreate on the aggregations to store the values and then use them in the third table? 
 
You can then access the variables using this.setDisplayValue(reportContext.getPersistentGlobalVariable("name")) on either onCreate or onRender. (Sometimes onCreate doesn't work and you have to use onRender.) 
 
 
Thats what I usually do when I need to access values across different tables. 
 
I don't know how to create charts from them though, I've never done that. </blockquote>
 
Can you please share the exact steps for achieving this? It would be really helpful if you can also share a report structure where you have utilized this. 
 
Thanks, 
Nitin

mwilliams

Hi Nitin,

Can you explain more about what you're trying to do?