nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Joining Aggregated Data

David_smav

Hi, I hope someone can help as I have thought through various ways to do it, without any outcome so far.

I have two joined data sets A and B which both contain a column account_id. A and B are each a joined data sets as A contains all sales for an account_id joined with a rev share value from another data source (xls) in order to calculate the revenue. Hence, an account_id can have several sales in data set A.
Data set B contains all cost items of an account_id joined with another data source for the price of the cost.

I now want to bring revenue and costs of an account_id together. Naturally I would aggregate revenue and costs by account_id and then join A and B on account_id, however the dilemma:

aggregation seems to be only possible in data cubes --> data cubes cannot be joined
aggregation is not possible on a joined data set level --> data sets can be joined

I can't do it in the sql statement of the data set, as I need to join a given data set on individual revenue and cost item with another xls data source before.

how to approch this?

thanks a lot
david

Find more posts tagged with

Comments

kclark

Are A and B separate joined datasets or are the joined together in a third dataset? You should be able to join A and B and do all of your aggregation. <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/24297-cross-tab-display/'>Take a look at this post</a> it might help with what you're trying to do also.

Hans_vd

Hi David,

You can not join dataset A and dataset B together because that would lead to a cartesian product, is that it?

If so, I'd rather think of either solving the problem directly in the datasources (have the totals by account id in the Excel) or creating scripted datasources that do the grouping by account id. I don't know much about scripted datasources, not sure if you can process Excel files with it.

Regards
Hans

David_smav

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="111757" data-time="1353521069" data-date="21 November 2012 - 11:04 AM">
Are A and B separate joined datasets or are the joined together in a third dataset? You should be able to join A and B and do all of your aggregation. <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/24297-cross-tab-display/'>Take a look at this post</a> it might help with what you're trying to do also. </blockquote>
 
Hi kclark, yes, A and B are EACH joined data sets already and now I want to join them, but first A and B each have to be aggregated on account_id as both contain various entries for account_ids.

David_smav

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="111760" data-time="1353525140" data-date="21 November 2012 - 12:12 PM">
Hi David, 
 
You can not join dataset A and dataset B together because that would lead to a cartesian product, is that it? 
 
If so, I'd rather think of either solving the problem directly in the datasources (have the totals by account id in the Excel) or creating scripted datasources that do the grouping by account id. I don't know much about scripted datasources, not sure if you can process Excel files with it. 
 
Regards 
Hans </blockquote>
 
Hi Hans, not really, as I want to aggregate sum of revenue and some of costs both on account_id first, and then inner join on account_id. The excel only contains the revenue share and the price per item, there is no account information in there, that is why I A and B are already joined data sets, A with volume of an account (DB sql) with xls (revenue share information) to compute revenue; and B with costs items of an account (DB sql) with xls prices of items to compute costs. Hence, I can't solve the aggegration in the xls as it needs DB information. sorry, the scripted data sources I don't know about. any hints?

Hans_vd

Hi David,

That's what I mean. Since there is no way of grouping the data in the data set itself, the result of joining both data sets together (without the grouping) would lead to a cartesion product.

How about loading the excel files into your database, so that you can do the grouping and aggregations in the queries of data sets A and B? You will then be able to create a joint data to join both data sets together.

Regards
Hans

David_smav

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="111785" data-time="1353577782" data-date="22 November 2012 - 02:49 AM">
Hi David, 
 
That's what I mean. Since there is no way of grouping the data in the data set itself, the result of joining both data sets together (without the grouping) would lead to a cartesion product. 
 
How about loading the excel files into your database, so that you can do the grouping and aggregations in the queries of data sets A and B? You will then be able to create a joint data to join both data sets together. 
 
Regards 
Hans </blockquote>
 
Hi Hans, sure, that is my backup solution, but looking for a way to getting around populating data to the database.

David_smav

anyone?

David_smav

how about reusing the results of my joined data sets A and B my making them accessible as javascript array like in here: http://www.birt-exchange.org/org/devshare/designing-birt-reports/928-report-with-table-and-chart-from-multiple-data-sources-based-on-subqueries/

is that possible? can I put results into an array and then reuse it for a data set sql query or right in a data cube?

Tubal

<blockquote class='ipsBlockquote' data-author="'David_smav'" data-cid="111807" data-time="1353687649" data-date="23 November 2012 - 09:20 AM">
how about reusing the results of my joined data sets A and B my making them accessible as javascript array like in here: <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/928-report-with-table-and-chart-from-multiple-data-sources-based-on-subqueries/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/928-report-with-table-and-chart-from-multiple-data-sources-based-on-subqueries/</a> 
 
is that possible? can I put results into an array and then reuse it for a data set sql query or right in a data cube? </blockquote>
 
This should be possible by storing your data into global variables. I'm not sure this is the most efficient way to do what you're wanting however. 
 
But, to do that, you'd just put something like this in your onFetch of your joint dataset: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setGlobalVariable(row.csf_id,row.color);</pre>
 
What this does is creates a global variable named whatever my csf_id is (you might put your account_id here). 
Then it gives it the value of whatever my color is. (you might put your cost or revenue here). 
 
So if my dataset returns 500 rows, it's going to create 500 different global variables (as long as my csf_id's are unique). 
 
Then to access it, you would just call: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.getGlobalVariable(csf_id);</pre>

David_smav

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="111809" data-time="1353691316" data-date="23 November 2012 - 10:21 AM">
This should be possible by storing your data into global variables. I'm not sure this is the most efficient way to do what you're wanting however. 
 
But, to do that, you'd just put something like this in your onFetch of your joint dataset: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setGlobalVariable(row.csf_id,row.color);</pre>
 
What this does is creates a global variable named whatever my csf_id is (you might put your account_id here). 
Then it gives it the value of whatever my color is. (you might put your cost or revenue here). 
 
So if my dataset returns 500 rows, it's going to create 500 different global variables (as long as my csf_id's are unique). 
 
Then to access it, you would just call: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.getGlobalVariable(csf_id);</pre></blockquote>
 
Hi Tubal, thanks for the tip, makes sense but seems inefficient as you say. I wonder whether I am the only one who would like to reuse the results of a data set for a new data set, or to be able to join cross tab results.

Hans_vd

Hi David,

Yesterday I posted about group functions in data sets in devshare.
The zip file contains a pdf file that describes a situation that comes very close to the one you are dealing with. The group functions in the plugin help solving the problem.

David_smav

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="111959" data-time="1354286126" data-date="30 November 2012 - 07:35 AM">
Hi David, 
 
Yesterday I posted about group functions in data sets in devshare. 
The zip file contains a pdf file that describes a situation that comes very close to the one you are dealing with. The group functions in the plugin help solving the problem. </blockquote>
 
great, grouping would help indeed. do you have a link?

Hans_vd

It is <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1563-birt-group-functions-plugin/'>here</a> 
 
It may not be the most elegant solution, but it'll do the trick.