Scripted table-column

Hi all,

in the attached image is a table displayed that I need for a chart with 2 series. The first 4 columns "A-D" I got from a mysql-database. The columns E and F are allready generated from the column D with Birt-functions and not the problem. The values are grouped by the "Week of year". On the x-axis of the chart also are the weeks of year. The first series is the "running sum of testcases" from column F.

My problem is how to generate a computed column like column G. There are only testcases listed when they are first touched. In this point it is not interesting, if testcases are used a second time. So the rest of the cells are filled with null. I guess that I need this computed column to generate the last column, which should be the second series of the chart.
The last column is the running sum of the values from column H and the column H is the count of testcase from column G per week.
Someone has an idea for a script generating this computed column and solving my problem? Or another solution for the second series? Hope the discription is understandable.

Thanks for your help

Find more posts tagged with

Comments

mwilliams

Hi doublehawk,

What version of BIRT are you using? So, you have all the columns through G so far, but you're trying to compute H and I? Is this correct?

doublehawk

Hi Michael,

thanks for your reply. Iam using the version 2.5.1. Up to now I have the columns A-F. So first of all I need to generate G. My idea would be to add a computed column to the dataset and whenever a testcasenumber from column D is used the first time I write this number into the computed column. If the testcase is used before I write null in the computed column. Later I can aggregate the columns H and I in the table-binding with the birt-functions COUNT and RUNNINGSUM, thats not a problem I guess, if I have G. I need the javascript for the dataset to work off the following points:

1. new Array()
2. get row.testcases-Nr.
3. check if testcase is in Array
if false -> save testcase in Array and in computed column
if true -> write null in computed column

I allready tried some scripting but it didnt worked.

beforeOpen

cnt=0;
var myarray = new Array();

onFetch

var parmcount = myarray.length;

while(cnt<parmcount)
{
if(row["ORDERNUMBER"] == myarray[cnt])
{
row["computedcolumn"] = row["ORDERNUMBER"];
myarray.push(row["ORDERNUMBER"]);
}

cnt++;
return true;

}
return false;

The query:
SELECT
CLASSICMODELS.ORDERS.ORDERNUMBER,
CLASSICMODELS.ORDERS.ORDERDATE
FROM
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS
WHERE CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER

Do I need a global Array for this?

mwilliams

doublehawk,

From the looks of column G, you could just set up a global variable called temp or something that keeps track of the last value from column D. If the new value in column D is not the same as your temp variable, you would display the value in G and set the temp variable to the new value. If they were the same, you'd put a null value in column G and leave the temp variable alone. Make sense? Let me know.

doublehawk

Hi Michael,

your suggestion has the problem that one testcase from D could appear in G more than one-time I think, because you recall just the last value. The testcases are not done in a strict ascending order. In the example in the week 50 of 2008 I used the testcase with the number 1000 the first time. In the week 3 of 2009 I used this testcase again but it should not appear in G. Between this two rows with the same testcasenumber are lots of other rows so that in the temp variable is maybe any other testcasenumbers. And this would involve that the second testcase gets into column G, what is not in my mind. So Iam sure that I need an array-variable and I fill it with the testcasenumbers if they appear the first time. If a testcase turns up again in any later row I can recognize it and it wont be written into the array and do not appear to G again.
(I would not need G to get column I if there were a table-aggregation available like a RunningCountDistinct for all testcases of the table with intermediate results in the week-group-footer.)
Hope this will clear our misunderstanding or tell me if I understood you wrong.

Thanks for your anwsers

mwilliams

doublehawk,

From the image above, what I said would work. I was under the assumption that the table would be ordered by testcase number and would not appear again later on. But what you're saying is that 1000 could appear later in the table and you don't want it listed again in column G? Let me know.

doublehawk

Hi Michael, 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">But what you're saying is that 1000 could appear later in the table and you don't want it listed again in column G?</blockquote>
Thats correct! 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">I was under the assumption that the table would be ordered by testcase number and would not appear again later on.</blockquote>
The first order is the recordingdate! In the table the values will be grouped by week. Because of this I thought it is obviously that they are first sorted by date. 
 
Greetings from Germany

mwilliams

doublehawk,

I noticed that they were sorted by week, but if you look at the screenshot, you can see how I assumed that the testcase number would not reappear as they are all in order in the displayed data.

Now that I understand the new way the data will actually appear, in your computed column, you could create a new persistentGlobalVariable for each testcase number as you encounter it and increment it each time you came across that case. This would handle the times encountered and if a testcase had been passed already as there would be a PGV named after that test case. If the value of the PGV is null, you haven't made it yet and you could put the value in the computed column. If it's 1 or higher, you've already encountered that testcase and can output null for that column. Or, you could do an array that adds a testcase value to the array if the value is not currently in the array and output the testcase and put a null in column G if it is in the array.

Hope this helps.

doublehawk

I found your first suggestion with the PVG a good idea but I have problems with the implementation, because Iam not so experianced with javascript.
I tried to put the following script in various variants into the expression builder of the computed column and the onFetch of the table, but it has errors.

if(reportContext.getPersistentGlobalVariable(this.getRowData().getExpressionValue("row[ORDERNUMBER]"))=="x")
{
row["computedcolumn"] = null;
}
else{
reportContext.setPersistentGlobalVariable(this.getRowData().getExpressionValue("row[ORDERNUMBER]"), "x");
row["computedcolumn"] = row["ORDERNUMBER"];
}

Can you help me with the script pls.

Thanks!

doublehawk

Problem solved!!!

With the following script in the onFetch of the dataset it was possible to generate G:

if(reportContext.getPersistentGlobalVariable(row["testcasenumber"])=="1")
{
row["computedcolumn"] = null;
}
else{
reportContext.setPersistentGlobalVariable(row["testcasenumber"], "1");
row["computedcolumn"] = row["ORDERNUMBER"];
}

Thanks for your anwsers and ideas

mwilliams

doublehawk,

I'd be glad to help with the script, but it looks like you got it now!

Always glad to help. Let us know whenever you have questions!