data assigned to variable appears on next row

Hello,

I have run through the scripting tutorial and looked at sample files, but not able to figure this one out.

I have data element that displays a variable. If I assign this variable the value of a column, this value appears on the *next* row.

I have

report beforeRender:
myrowcount=1
newACB=0

Detail onCreate: myrowcount++;
newACB = this.getRowData().getColumnValue("NetAmount");

But the report will show something like:

NetAmount myrowcount ACB
100 1 0 < Should be 100 (current value of NetAmount)
200 2 100 < should be 200
300 3 200 < should be 300

The actual newACB calculation will be a bit more complex, and is only recalculated on rows where NetAmount is positive. Otherwise the variable carries over. So I can't see a way to do this simply in the column bindings.

The actual calculation will be something more like:

if (this.getRowData().getColumnValue("NetAmount")> 1){
myrowcount++;
newACB = (this.getRowData().getColumnValue("NetAmount") + newACB * this.getRowData().getColumnValue("PrevShares"))/this.getRowData().getColumnValue("AGShares");
}

My first thought here is that I should put my ACB element as the last in the report, and the script in the onCreate of that cell.

But maybe I'm going about this in the wrong way.

Any suggestions welcome.

Regards,

Boyd

Find more posts tagged with

Comments

mwilliams

Can you do this in a new binding on your table, instead? Also, what is your BIRT version? Can you recreate the issue in a way that I can run it, i.e. use the sample database that comes with BIRT?

BoydKelly

Hey Thank you very much for taking a look at this!

I have zipped up a test project, with a sample report, and data. There is a test.db (sqlite) database file that should be copied to the working directory, and the jdbc sqlite jar file that I am using to access the data source. Or you can rather download from:

http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.7.2/sqlite-jdbc-3.7.2.jar

The calculation (for my own personal taxes...) that I need to do is called Adjusted Cost Base. It's kind of tricky and can't really be done in a spreadsheet.

The formula/logic is as follows:
When there is a purchase of shares, take the currrent ACB per share and multiply that by the number of shares (before the current purchase), then add the Net Amount (shares * price - expenses) of the current transaction. Divide this by the total number of shares (including the current transaction). This will calculate the true average cost of all the shares.

When there is a sale, there is *no* change to this average cost. That makes sense. If the average cost of 100 shares is X per share, the take away any amount and the average cost per share will be unchanged.However when there is a sale, this is when it is important to know the ACB, as it will be used to calculate income tax. (Ouch!)

Since there is a calculation made when there is a purchase, but not a sale, and an amount that needs to be 'remembered', or carried forward, there doesn't seem to be any aggregate function that can handle this type of thing. (as far as I can see...)

So I have made columns/elements named prevACB(bound to variable), newACB(bound to variable), AGShares(running sum of shares owned), and PrevShares(That running sum of shares owned minus the current shares).

Variables prevACB and newACB are initialized to 0 at the start of the report, and reinitialized to 0 in the footer for each group.

In RowDetail/onCreate, I have tried the following as per logic described above:

newACB = (this.getRowData().getColumnValue("NetAmount") + (prevACB * this.getRowData().getColumnValue ("PrevShares")))/this.getRowData().getColumnValue("AGShares");

prevACB = newACB

With the data in the report for the first row this would be (100 + (0 * 0))/10. My newACB should be 10 and then assigned as the prevACB. The following rows should be:

(formula = ACB)

2: (110 + (10 * 10))/20) = 10.5
3: no change to ACB (sold 5 shares)
4: no change to ACB (sold 5 shares)
5: (140 + (10.50 * 10)/20 = 12.25

The problem here is that the prevACB and newACB data elements have the 0 values for the first row and 10 for the second etc. I was able to get some logging going, and the variables are ok as logged, (at least for the first 2 rows), but just not applied to the data elements till the next row is processed. Question of doing this calculation at the right time and place.

I hope the project files are ok for you.

Best regards,

Boyd

mwilliams

So, to run the design from the designer, I just need to put the sqlite driver in my drivers and the design and the db file in my workspace?

Edit: Nevermind. Got it. Taking a look now.

mwilliams

If you use a text box with <value-of> tags to grab the newACB variable or if you use a dynamic text box, it shows up on the line you expect. It's easier to format the <value-of> tag way, cause you can do <value-of format="#,##0.000">.

BoydKelly

Thank you very much for the tip. I actually busted my brain the last few days and found this solution:

I just put this in the expression builder for the ACB calculation:

if (dataSetRow["Shares"]>0){
newACB=BirtMath.divide(BirtMath.add(BirtMath.multiply(prevACB,row["RPShares"]),dataSetRow["NetAmount"]),row["RShares"])
prevACB=newACB

newACB
}
else {
newACB
}

I still had issues that were really baffling. The sqlite database is very loose with data types. So birt was seeing in the Data Set a string type which was really a number. Even though in the report data binding it was set to decimal, my javascript was still seeing a string. When I changed this in the Data Set, it worked.

Anyways, this was my first report. I learned a lot! Thank you.

mwilliams

You're welcome! Good luck! Let us know whenever you have questions!