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; } } }

Combining data of two crosstabs

marcs

Hi guys, 
what seems extremely easy in Excel is not in Birt I guess. I have two datasets based on two different db tables. For both datasets I created each a crosstab that gives me accumulated results for each month. I want now create a third table or crosstab that shows me computed results from the two before mentioned tables. 
Example: 
First crosstab shows me number of sales per platform and month. 
The second crosstab shows me the number of returned item also per platform and month 
in a third table I now want to use these numbers to show the quote of returned items (returned items / soled items * 100) also per platform and month. 
I already tried using a global persistent variable that stores the numbers from the first crosstab and tried to use it in the second to build the quote. This seems already to work, but how do I now build up the third table and fill it with the numbers from the GPV? 
Or maybe I'm thinking much too complicated and there is a much easier solution? 
I appreciate every hint :-) 
Thanks and best regards 
Marc

Find more posts tagged with

Comments

JFreeman

Which version of BIRT are you using?
 
Instead of trying to pull the data from both crosstabs, have you tried created a new cube for the third cross tab and pulling in the needed data from both data sets into the new cube?

marcs

Hi Jesse,
 
thanks for you answere. I tried two different things:
<ol><li>make a joint dataset</li>
<li>join the two datasets into one cube</li>
</ol>But I seem not to make it right. Perhaps I should give an example of the tables:
 
The first table
aDate | aSold | aPlatform
 
Second table
bDate | bReturned | bPlatform
 
I already have 2 crosstabs, one for each table which gives me
<ol><li>Number of sold products per platform and Month/Year</li>
<li>Number of returned products per platform and Month/Year</li>
</ol>What I want to accomplish is:
<ul><li>Proportion of sold products per Month/Year/Platform to returned products per Month/Year/platfrom</li>
</ul>The two numbers are already there because of the two datasets and crosstabs I already have but I can't bring them together ...
 
thanks again for any hint that will lead me to the solution.
 
Best regards
Marc

JFreeman

Can you attach a sample report based on a flat file or classic models with your current configuration that we can run and modify?

marcs

Hi Jesse,
 
thank you very much for trying to help me out here. The real wold data is within a database, so I stripped it all down to 2 datasources based on two flat files.
 
I created a report very similar to what I have in the real world. I hope you can use it.
 
Thanks again and best regards
Marc

JFreeman

Thank you for the sample.
I will take a look and see what I can come up with.
 
Is it possible for you to create a third data set and join the the two table together on the database side through the query?

marcs

the two flat files can be seen as the two database tables. So a join in SQL would be possible...
 
best regards
Markus

JFreeman

If you can join the two tables together on the database side into a single data set, that is going to be the best solution to get all of the needed data into a single crosstab.