updating data in dataset based on algorithm

Here is what I'd like to do:

1) Take the results of a dataset in a BIRT report (currently it's of type jdbc-oracle sql statement).

2) Take all of the data and create a table-like java object with it.

3) Run algorithms (written in java) across the java object created in step 2, which will transform and change the results of the dataset.

4) Display the data in the report after the algorithms have been run.

Is this possible and if so, any advised methods of doing so?

Thanks in advance,
AC

Find more posts tagged with

Comments

mwilliams

Could you call your algorithms on the data as it comes in in the onFetch and then store the transformed values into an object of some sort? Then, you can create a scripted dataSet that then iterates through this object creating the rows and use this dataSet in your report. If you didn't want to transform the data line by line as it's fetched, you could store the values into your object in the onFetch, then run your computations in the scripted dataSet. Pretty much the same overall process each way. There's also always the computed column way within your dataSet if that worked for you.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80758" data-time="1311882058" data-date="28 July 2011 - 12:40 PM">
Could you call your algorithms on the data as it comes in in the onFetch and then store the transformed values into an object of some sort? Then, you can create a scripted dataSet that then iterates through this object creating the rows and use this dataSet in your report. If you didn't want to transform the data line by line as it's fetched, you could store the values into your object in the onFetch, then run your computations in the scripted dataSet. Pretty much the same overall process each way. There's also always the computed column way within your dataSet if that worked for you. </blockquote>
 
Thanks again for the response. 
 
I believe based on the requirements of this project, that we'd go for the option "store the values into your object in the onFetch, then run your computations in the scripted dataSet". Does there exist any examples of this on devShare or anywhere else, where dataset1 is the sample Classic Models database, then dataset2 is scripted and is derived from the object created in the onFetch in dataset1? If so that'd be great to start with! Let me know. If not I'll have to try and put together something to work with and post follow-ups here. 
 
--A.C.

mwilliams

I've got an example somewhere. Not sure if it made it into the devShare or not. What is your BIRT version? If I can't find it, I'll make one in your version.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80762" data-time="1311885808" data-date="28 July 2011 - 01:43 PM">
I've got an example somewhere. Not sure if it made it into the devShare or not. What is your BIRT version? If I can't find it, I'll make one in your version. </blockquote>
 
That's great to hear! We're running BIRT 2.6.1 (version that's bundled with Actuate v 11). Thanks again, look forward to seeing it.

mwilliams

Take a look at this. I have a SQL dataSet that grabs all columns from the customer dataSet. Then, I fill an array with values from the two columns that I want from that dataSet and use this in the scripted dataSet. A text box is bound to the SQL dataSet so that it's forced to run to fill the array for the scripted dataSet. Let me know if you have questions!

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80767" data-time="1311887790" data-date="28 July 2011 - 02:16 PM">
Take a look at this. I have a SQL dataSet that grabs all columns from the customer dataSet. Then, I fill an array with values from the two columns that I want from that dataSet and use this in the scripted dataSet. A text box is bound to the SQL dataSet so that it's forced to run to fill the array for the scripted dataSet. Let me know if you have questions! </blockquote>
 
This is just what I was asking for, thanks again! I was able to do some simple evaluations to the first dataset (numeric field less than, greater than, equal to) in my test and change the data displayed in the report based on it. This is a very good start. I may follow up again with another question, at least if it applies to BIRT - I'll be getting into some more complex algorithms in my next test.

mwilliams

Great. Glad to help!

chavez_ant

This method has been working for me for the time being, but I thought I'd check if you had any ideas for my next request.

Attached is a new version of the example provided. In the new version, some script is run on the scripted dataset, under 'fetch', which adjusts the value of a column in the scripted dataset, based on a global variable (if value higher than variable then null, else display the value divided by 2).

I'd like to take the results of the first dataset, move it to a java object much like in the original example. I'd like to then take that object and run some java code outside of the report, from a java class, to transform the data (similar to the example attached, where the computed column based on global variable is generated). Then move the data back into the report via scripted dataset.

I'm currently taking a look at doing this myself, but any advice or examples anyone can provide for this would be of great help. Many thanks in advance.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'chavez_ant'" data-cid="81303" data-time="1312840655" data-date="08 August 2011 - 02:57 PM">
This method has been working for me for the time being, but I thought I'd check if you had any ideas for my next request. 
 
Attached is a new version of the example provided. In the new version, some script is run on the scripted dataset, under 'fetch', which adjusts the value of a column in the scripted dataset, based on a global variable (if value higher than variable then null, else display the value divided by 2). 
 
I'd like to take the results of the first dataset, move it to a java object much like in the original example. I'd like to then take that object and run some java code outside of the report, from a java class, to transform the data (similar to the example attached, where the computed column based on global variable is generated). Then move the data back into the report via scripted dataset. 
 
I'm currently taking a look at doing this myself, but any advice or examples anyone can provide for this would be of great help. Many thanks in advance. </blockquote>
 
 
I did some research, and am thinking what we want to do for these purposes is create an java class for the Scripted Data Set, that will do the same functions as the 'fetch' section in the attached file. Then assign that event handler to the scripted data set. 
 
Am I on the right track? 
 
I'm a newbie to java so an example of a java class that does the same functions as the 'fetch' section of the scripted data set of the last report example I uploaded, or any tips or suggestions on how to do this would be extra helpful. I'll keep trying to put something together in the meantime. Thanks again!

mwilliams

You should also be able to import your java class and run your functions from your external java file within the fetch method.

chavez_ant

I've got a version of this working, using this method: Dataset is run via sql. Then a NativeArray is generated with the sql data (using the same method as the example mwilliams sent) and the array is assigned to a global variable. Then a java event handler is run across the scripted data set which converts the NativeArray to an ArrayList and does the calculations needed. I did this with quite a bit of help from coworkers too. Thanks for your assistance with getting me this far!!

I have one more question about this. To prevent me from having to convert from NativeArray to ArrayList in the event handler, can we, in the SQL dataset, generate a java ArrayList directly? I know we can add to the dataset's script something like this to get started:

importPackage(Packages.java.util.*);
globalvarList = new java.util.ArrayList();

but the rest I'm not sure. Any more tips would be great. Once I get the example finalized, I'll plan to 'devshare' it and post the link here.

Thx again!

mwilliams

What questions do you have about using java.util.ArrayList? Once you've defined the array, you should be able to just start adding elements to it. Let me know.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="81774" data-time="1314038381" data-date="22 August 2011 - 11:39 AM">
What questions do you have about using java.util.ArrayList? Once you've defined the array, you should be able to just start adding elements to it. Let me know. </blockquote>
 
Thanks, should have been more specific. 
 
Below is the current script being used across the first dataset, below, that you helped with. I'd like to figure out how to change this script to get dataArray to be set in the global variable as an Array List instead of NativeArray. It'd be great to get some help with this, and in the meantime I'll keep researching and trying new methods as well. I took a shot at it and haven't been successful yet. 
 
In the beforeOpen: 
 
dataArray = []; 
currentRow = 0; 
 
In the onFetch: 
 
dataArray[currentRow] = []; 
dataArray[currentRow][0] = row["CUSTOMERNAME"]; 
dataArray[currentRow][1] = row["CUSTOMERNUMBER"]; 
currentRow++; 
 
in the beforeClose: 
 
reportContext.setGlobalVariable("dataArray",dataArray);

mwilliams

Here's the report I attached before, only I add a second table that uses the java.util.ArrayList. Hope this helps.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="81778" data-time="1314045314" data-date="22 August 2011 - 01:35 PM">
Here's the report I attached before, only I add a second table that uses the java.util.ArrayList. Hope this helps. </blockquote>
 
The solution seems quite simple now that I see it in action. Thanks so much for saving me many more attempts at this!! I'll let the community know once I've finalized the example of my version of this type of report, that has the java class event handler running across the scripted dataset. I'll post the link to it here and I'll make sure to credit you.

mwilliams

Sounds great! No need to credit me! Good luck!

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="81796" data-time="1314106277" data-date="23 August 2011 - 06:31 AM">
Sounds great! No need to credit me! Good luck!

</blockquote>
 
Thanks again! I started on setting up my new version, but found that the Array List generated differs from what I was expecting. What I'd like to generate is an array of arrays, a 2d array, with an array for each row of data. So the array list would have data like this, much like the NativeArray did: 
 
Name1, 101][Name2, 105][Name3, 203 
 
What would even be better is this. I've recently found that we'll have a need to add a Hashmap value to the array list as well. Let's say the hashmap key will be the Name column in this example. 
 
I'll plan to attempt to converting the example to this format but I thought I'd chime in and see if you have any additional tips or could share another example of this. 
 
All the best, 
A.C.

mwilliams

If you don't want to do a hashmap, you could probably do an array of arrays, like we did with the other example. You'd just have to create a new array in each loop of the onFetch script to add to your main array.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="81826" data-time="1314135780" data-date="23 August 2011 - 02:43 PM">
If you don't want to do a hashmap, you could probably do an array of arrays, like we did with the other example. You'd just have to create a new array in each loop of the onFetch script to add to your main array. </blockquote>
 
I got this up and running and posted on devShare, link below, if you're interested. Thanks again!! 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1407-use-java-event-handler-to-code-across-the-output-of-a-sql-dataset-using-a-scripted-dataset/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/1407-use-java-event-handler-to-code-across-the-output-of-a-sql-dataset-using-a-scripted-dataset/</a>

mwilliams

You're welcome. Thanks for posting your solution! Let us know whenever you have questions!