Using multiple data sets in one report/table

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: 
 
Am I going about this the wrong way? Is there a better way to do this?
 
Thanks!

Find more posts tagged with

Comments

mwilliams

Can you try downloading a fresh copy of the all in one birt designer from the following link and using the XML and design I supplied in a new report project? <a data-ipb='nomediaparse' href='http://archive.eclipse.org/birt/downloads/build.php?build=R-R1-4_2_2-201302161152'>http://archive.eclipse.org/birt/downloads/build.php?build=R-R1-4_2_2-201302161152</a> As for the if statement, yes, I'm making the base 6000 for the years up to the current year. After that, I set that value to 0 so that nothing shows for the future years except for a blank space (see below).

KatCos

I only see that download for Windows and Linux. I have a Macbook Pro. Also, I'm running the client's development workbench with eclipse/birt integrated (I'm sure that's why your files aren't working). I'm afraid the installation of the all-in-one would conflict with that. Not sure I could run them separately.

mwilliams

Try this download location: <a data-ipb='nomediaparse' href='http://www.eclipse.org/downloads/packages/eclipse-ide-java-and-report-developers/junosr2'>http://www.eclipse.org/downloads/packages/eclipse-ide-java-and-report-developers/junosr2</a> You should be able to use this separately just to be able to run the report and see where all the script is and how it's put together to help you reproduce it in your client's version.

KatCos

That worked, and it helped a lot. Thanks. I'm also able to look at the data source and data set definitions now. The way you added Base to the Donations data set was by column mapping when you defined the data sets? I promise I'm almost done asking you questions on this!

KatCos

Hi Michael, 
The way the client set things up, I can't add columns to data sets like you did with the Base Amount. I can only define the primary and secondary data sets as they're defined in the xml. I can create a unioned data set and populate it with the info for the graph, but there are items, such as the base amount, that I need to use elsewhere in the report (for instance, I need to sum it with the donation amounts). I mentioned at some point in this thread that I did an outer join on the two data sets even though there isn't a common column, which seems to have worked, but it's sort of "sketchy." What I'd like to do instead is populate some variables and have them available throughout the report. I tried to do this by adding:
 
reportContext.setPersistentGlobalVariable("base",base);
reportContext.setPersistentGlobalVariable("commission",commission);
 
to the beforeClose method for the appropriate data set, but it's giving me errors (see below). Apparently I'm not using the setPersistentGlobalVariable statement correctly. What am I doing wrong? Can I do what I'm trying to do? 
 
Thanks for your help, again and again! Here are the errors:
ReportDesign (id = 1):
<div style="color:rgb(255,0,0);font-family:sans-serif;font-size:13px;">+ There are errors evaluating script " 
 for(i=0,statementyear=2014;i<5;i++,statementyear++){ 
myData[myData.length] = ["Salary",statementyear,base]; 
} 
 reportContext.setPersistentGlobalVariable("myData",myData); 
 reportContext.setPersistentGlobalVariable("base",base); 
 reportContext.setPersistentGlobalVariable("commission",commission); 
": 
Fail to execute script in function __bm_beforeClose(). Source:

" + 
 for(i=0,statementyear=2014;i<5;i++,statementyear++){ 
myData[myData.length] = ["Salary",statementyear,base]; 
} 
 reportContext.setPersistentGlobalVariable("myData",myData); 
 reportContext.setPersistentGlobalVariable("base",base); 
 reportContext.setPersistentGlobalVariable("commission",commission); 
+ "

A BIRT exception occurred. See next exception for more information. 
Can't find method org.eclipse.birt.report.engine.script.internal.ReportContextImpl.setPersistentGlobalVariable(string,number). (/report/data-sets/oda-data-set[@id="9"]/method[@name="beforeClose"]#6).</div>

KatCos

It looks like I can use report variables......
 vars["base"] = row["Base_Pay"];
 vars["commission"] = row["Commission"];
 
But they seem to be accumulating for each iteration through the data set?

KatCos

I figured that out, and I got the whole report working. I am so grateful for your help. Thank you. What a fantastic resource you are.
Kat

mwilliams

Sorry for the delay. I was in Boston at Big Data TechCon at the beginning of the week. Glad you got it all working and I'm very happy to help. Let us know whenever you have questions.