Using multiple data sets in one report/table

<p>Hi! I have to build a report using data from three different xml data sets. At times, I have to pull data from two or all of them for use in one table/chart (like summing various amounts pulled from each). The data sets have no common columns. I was able to create a joint data set out of two of them, despite there being no common columns. This seems to have worked for my purposes, even though Birt documentation advises against it (joining data sets that have no common columns). But now, for some reason, I am unable to join this joint data set with the third data set. I'm stuck and wondering: </p>
<p> </p>
<p>Am I going about this the wrong way? Is there a better way to do this?</p>
<p> </p>
<p>Thanks!</p>

Find more posts tagged with

Comments

Is there no way that you can create a common column to join on? I don't understand how you possibly join them if they have no relate-able fields. Could you explain this more?

<p>Thanks for your reply, Michael. Honestly, I'm not sure how it worked either, but it did. I needed to sum two amounts from one data set and one from another, so I went to New Joint Data Set, entered the two data sets and chose Full Outer Join. It worked. Interestingly, however, when I try to edit the joint data set, it won't let me. I can't get into it. But it's working correctly in my report. </p>
<p> </p>
<p>About creating a common column. two of the data sets could potentially have a common date column. The other one...not really. Unless I could put a date column in and have the data just repeat for each year....</p>

<p>Also, when I try to create another joint data set using the first joint one and adding the third data set, I don't get an error -- the window just won't let me "finish" out of it. I have to "cancel."</p>

Well, you'll never really be able to make any logical joins on your data unless there's a field that connects them. Date would work if all had a date column.<br><br>Could you maybe show snippets of data from each data set and what you'd like to see as the output or post sample xml files and an explanation of what you want to see from them? I might be able to help more that way.

<p>Thanks, Michael. Yes, I will do that tomorrow!</p>

<p>Hi Michael,</p>
<p> </p>
<p>This is a simplified clip of the xml: </p>
<p> </p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Name</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">Ann Smith</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Name</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:rgb(0,145,147);"><</span>Base_Amount<span style="color:rgb(0,145,147);">></span><span style="color:rgb(0,0,0);">6000</span><span style="color:rgb(0,145,147);"></</span>Base_Amount<span style="color:rgb(0,145,147);">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payments<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Date<span style="color:#009193;">></span><span style="color:#000000;">12-16-2014</span><span style="color:#009193;"></</span>Payment_Date<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Type<span style="color:#009193;">></span><span style="color:#000000;">Payment Type A</span><span style="color:#009193;"></</span>Payment_Type<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Amount<span style="color:#009193;">></span><span style="color:#000000;">1000</span><span style="color:#009193;"></</span>Payment_Amount<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"></</span>Payments<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payments<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Date<span style="color:#009193;">></span><span style="color:#000000;">12-16-2015</span><span style="color:#009193;"></</span>Payment_Date<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Type<span style="color:#009193;">></span><span style="color:#000000;">Payment Type B</span><span style="color:#009193;"></</span>Payment_Type<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Payment_Amount<span style="color:#009193;">></span><span style="color:#000000;">2000</span><span style="color:#009193;"></</span>Payment_Amount<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"></</span>Payments<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donations<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Date<span style="color:#009193;">></span><span style="color:#000000;">05-</span><span style="color:#000000;">Feb</span><span style="color:#000000;">-2014</span><span style="color:#009193;"></</span>Donation_Date<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Type<span style="color:#009193;">></span><span style="color:#000000;">Donation Type A</span><span style="color:#009193;"></</span>Donation_Type<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Amount<span style="color:#009193;">></span><span style="color:#000000;">100</span><span style="color:#009193;"></</span>Donation_Amount<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"></</span>Donations<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donations<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Date<span style="color:#009193;">></span><span style="color:#000000;">05-</span><span style="color:#000000;">Feb</span><span style="color:#000000;">-2015</span><span style="color:#009193;"></</span>Donation_Date<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Type<span style="color:#009193;">></span><span style="color:#000000;">Donation Type B</span><span style="color:#009193;"></</span>Donation_Type<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"><</span>Donation_Amount<span style="color:#009193;">></span><span style="color:#000000;">200</span><span style="color:#009193;"></</span>Donation_Amount<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"><span style="color:#009193;"></</span>Donations<span style="color:#009193;">></span></p>
<p style="font-size:11px;font-family:Monaco;color:rgb(78,145,146);"> </p>
<p style="font-size:11px;font-family:Monaco;">The bar chart needs to be stacked and have 2014, 2015, 2016 and 2017 along the X axis. The Y axis shows amounts. Each year should show 1) a static Base Amount (always 6000) 2) the sum of any Payments for the year and 3) the sum of any Donations for the year. Each year will always show a base amount, even if there are no payments or donations.</p>
<p style="font-size:11px;font-family:Monaco;"> </p>
<p style="font-size:11px;font-family:Monaco;"><span style="color:rgb(0,0,0);">Let me know if you need anything else. Thanks!</span></p>

<p>I realize that making the date field common among the two data sets would solve one problem. For the data set that has just the base amount and no date, I can see if it's ok to add a date field and repeat the same data for each of the five years. But...is this the easiest/cleanest way -- making these changes and then joining the data sets? Or is there some other solution (like hardcoding a static amount into a bar chart for one series?)?</p>

If you ask me, the person's name would be the logical join field you'd want to have in your different data sets.

<p>That might be possible. Each person accesses his/her own xml file, no one else's. Also, if the date is not common or non-existent, how am I going to use that for the x axis?</p>

What is your BIRT version?

And I didn't realize you'd only have a single person in the XML source. I've got a different idea to get you what you're wanting. Once you tell me your birt version, I'll try to build you a quick example.

<p>Excellent! Yes, there's only one person. Oddly, the only way I found to determine the Birt version in the workbench I'm using is <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Preferences, Report Design->Template. I see "....</span>/plugins/org.eclipse.birt.resources_4.2.2.2..." So I assume it's 4.2.2.2. There's got to be an easier way (not under Help > About)?</p>

One easy way to find out is to look at the XML Source of a report design. You'll see the version in the first few lines. 4.2.2.

<br><br>Help -> About Eclipse should show you, too.

<p>I don't see either of those - probably because of the specific workbench I'm using. The only "about" I have is about the client's workbench, and the xml source doesn't say anything about birt. </p>

Are you able to change the format of your XML source file at all? Having payments and donations how they are makes things somewhat difficult. It would be easier if set up like:<br><br><pre class="_prettyXprint _lang-auto _linenums:0">
<payments>
<payment>
<date></date>
<type></type>
<amount></amount>
</payment>
<payment>
<date></date>
<type></type>
<amount></amount>
</payment>
</payments>
</pre>and same with donations. I've modified the xml you have above to be like this so it's easier to work with. Hopefully this is how yours already is or it can be made to be that way.

Is this kinda what you're looking for?<br><br>

<p>YES, that is exactly what I'm looking for. How did you get the base in there?</p>

Take a look at this report. You'll also need to use my modified XML file.<br><br>In the design, there are two hidden text boxes that force the two XML data sets to run. When these run, there is script in those data sets to fill arrays that I use to populate my scripted data set which is a union of your data in a friendly format for setting up the chart.<br><br>Let me know if you have questions.

<p>Thank you so much. I will have to wait until tomorrow for an in-depth look. Right now, it's giving me the following errors when I run the report: </p>
<p> </p>
<p>Errors in Report</p>
<div> Line 9: The element ODA Data Source("Data Source") has an invalid manifest.</div>
<div> Line 15: The element ODA Data Set("Payments") has an invalid manifest.</div>
<div> Line 182: The element ODA Data Set("Donations") has an invalid manifest.</div>
<div> </div>
<div>I haven't used a scripted data set yet, so it's going to take me a minute to figure out what you did. When I double click Scripted DS under Data Sources, it doesn't open. </div>
<div> </div>
<div>Thanks again!</div>

Are you using BIRT within the Eclipse Juno IDE?

<p>Am I missing a plugin? If I click "ok," it tries to generate the report and I get this:</p>
<p> </p>
<p><span style="color:rgb(255,0,0);font-family:sans-serif;font-size:13px;">The following items have errors:</span></p>
<div style="color:rgb(255,0,0);font-family:sans-serif;font-size:13px;">
<div>Text (id = 10):</div>
<div><span>+ </span>An exception occurred during processing. Please see the following message for details:<br>
Cannot find or process the org.eclipse.datatools.enablement.oda.xml driver's data source extension configuration.<br>
Cannot find the ODA dataSource extension (org.eclipse.datatools.enablement.oda.xml).<br>
Check the workspace log file for any problems with loading the extension bundle and its dependencies.</div>
</div>
<div style="color:rgb(255,0,0);font-family:sans-serif;font-size:13px;">
<div>ReportDesign (id = 1):</div>
<div><span>+ </span>There are errors evaluating script "if(currentRow < myData.length){<br>
row["Name"] = myData[currentRow][0];<br>
row["SeriesGroup"] = myData[currentRow][1];<br>
row["Year"] = myData[currentRow][2];<br>
row["Amount"] = myData[currentRow][3];<br>
currentRow++;<br>
return(true);<br>
}<br>
return(false);":<br>
Fail to execute script in function __bm_FETCH(). Source:<br>

<br>
" + if(currentRow < myData.length){<br>
row["Name"] = myData[currentRow][0];<br>
row["SeriesGroup"] = myData[currentRow][1];<br>
row["Year"] = myData[currentRow][2];<br>
row["Amount"] = myData[currentRow][3];<br>
currentRow++;<br>
return(true);<br>
}<br>
return(false); + "<br>

<br>
A BIRT exception occurred. See next exception for more information.<br>
TypeError: Cannot read property "length" from null (/report/data-sets/script-data-set[@id="13"]/method[@name="fetch"]#1).</div>
<div><span>+ </span>Data Set script method "Fetch" returned null; expected a Boolean value.</div>
</div>
<div style="color:rgb(255,0,0);font-family:sans-serif;font-size:13px;">
<div>Text (id = 11):</div>
<div><span>+ </span>Column binding "Base_Amount" has referred to a data set column "Base_Amount" which does not exist.</div>
<div> </div>
<div> </div>
</div>

<p>Sorry, your post about the <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Eclipse Juno IDE just showed up. I don't know what that is. Sorry - I'm new at this. How would I know that?</span></p>

Where did you download BIRT from?<br><br>Are you using the XML file that I sent?

<p>Hi Michael.</p>
<p> </p>
<p>The client sent me a link for the installation. Yes I'm using your xml file. I created a project specifically for your report.</p>
<p> </p>
<p> </p>
<p>Although I haven't gotten the report to run, I've been looking at the xml. I don't understand everything, but I'm following it high-level. A few questions:</p>
<p> </p>
<p>1. The only place in the report that the name will appear or be used is on the very first line. Why do I need to add it to the other two data sets when it's not used and the year is the common denominator? </p>
<p> </p>
<p>2. This report contains other sections; not just the chart. The primary data set -- the one that has the name and base amount in it -- has other columns that I need for other sections of the report (I gave you a simplified version of that and only included two columns). Can I define that as a normal data set and pull only the base amount from it for use in the unioned data set? Do I have to add the name and base amount to the Donations data sets?</p>
<p> </p>
<p>3. Can I eliminate the hidden text fields if the data sets run in other parts of the report?</p>
<p> </p>
<p>4. I see you changed the xml to have another level for the payments and donations: </p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payments</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"> <</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payment</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"> </</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payment</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"></</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payments</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<div>instead of just <span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payment</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"></</span><span style="color:rgb(78,145,146);font-family:Monaco;font-size:11px;">Payment</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></div>
<p>Is this required for the arrays?</p>
<p> </p>
<p>Sorry I have so many questions. So grateful for your assistance!</p>

<p>Other example reports I have downloaded and run have this: </p>
<p> </p>
<p><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="font-family:Monaco;font-size:11px;color:rgb(78,145,146);">oda-data-source</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">extensionID</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"org.eclipse.birt.report.data.oda.jdbc"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">name</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"Data Source"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">id</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"7"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></p>
<p> </p>
<div>where yours has this: </div>
<div> </div>
<div><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);"><</span><span style="font-family:Monaco;font-size:11px;color:rgb(78,145,146);">oda-data-source</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">extensionID</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"org.eclipse.datatools.enablement.oda.xml"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">name</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"Data Source"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);"> </span><span style="font-family:Monaco;font-size:11px;color:rgb(147,33,146);">id</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,0,0);">=</span><span style="color:rgb(57,51,255);font-family:Monaco;font-size:11px;">"7"</span><span style="font-family:Monaco;font-size:11px;color:rgb(0,145,147);">></span></div>
<div> </div>
<div>Don't know if that means anything.</div>

<p>Hi again. Also...I think I mentioned before that the x axis of the chart needs to go from 2014 to 2018. So for some years there could potentially be only the base and no other payments or donations. I can't tell whether your code works that way or not (I would be able to tell if I could run the report), but I don't see where it forces the years to start/end at 2014/2018. </p>

1. You do not need name. I just added it because it was part of your data.<br>2. You can pull the base however you'd like. This is simply an example of how it could be done with the data you supplied.

You do not need name or base in either of these data sets.<br>3. Yes, as long as the element is BEFORE the chart that will use the scripted data set.<br>4. I mentioned to you above that I did this which was why I attached the XML and mentioned that this might be the reason you're not able to run the report. Adding the extra level may not have been necessary. I didn't even try without. Just seemed like it'd make things easier to have them grouped. It's not needed for the array and you can remove it if everything works fine for you without. You'd just need to change the xpath expressions in the data sets.

<br><br>It's an XML data source, not a JDBC data source, so the source extension id is correct as xml.<br><br>I did not force this to happen. If the years you want are completely static 2014-2018, you could remove the script where I fill the years array and simply define one where years = [2014,2015,2016,2017,2018]<br><br>Hope this answers everything.

I ungrouped the payment and donation sections in this xml file and made some modifications to the script to make the years static from 2014-2018. I made all the necessary changes to the data sets to use the new xml file and removed name from both data sets. I kept base, just because I need it for the chart anyways. You can remove this if you're getting the number from somewhere else.<br><br>Hope this helps.

<p>Thanks for this, Michael! I spent yesterday working on the same changes. I learned a lot. Seeing how you did it will certainly help. I'll let you know!</p>

<p>Hi Michael. Again, thank you so much for your assistance. I've learned a lot. I just have a couple questions about the latest files you sent. First of all, I'm still unable to view your reports. I get the same errors in the second one. Any ideas? It's not imperative that I see them, I guess, because I understand most of what's going on in the code, but it would be helpful to see the output.</p>
<p> </p>
<p>For instance, in the if statement for the base-amount array, it looks like you compare the array year to the current year and set the base to 0 if the year is greater than the current year. Is that correct?</p>
<p> </p>
<p>Also, can you please explain to me what the <span style="color:rgb(2,29,167);font-family:Helvetica;font-size:12px;"><xml-property</span><span style="font-family:Helvetica;font-size:12px;color:rgb(249,151,94);"> name</span><span style="font-family:Helvetica;font-size:12px;color:rgb(255,148,80);">=</span><span style="font-family:Helvetica;font-size:12px;color:rgb(171,69,0);">"queryText"</span><span style="color:rgb(2,29,167);font-family:Helvetica;font-size:12px;">> </span>statements are for?</p>
<p> </p>
<p>Thanks!</p>
<p>Kat</p>