Math-functions in crosstabs

hello all of you,<br />
<br />
concerning my old topic <a class='bbc_url' href='http://www.birt-exchange.org/forum/designing-birt-reports/16044-using-crosstab-summary-field-without-function.html#post51201'>http://www.birt-exchange.org/forum/designing-birt-reports/16044-using-crosstab-summary-field-without-function.html#post51201</a> I've got a new issue. First, again lets have a look at the table structure:<br />
<br />
table 'pilot'<br />
<br />

<br />
|id|name|...|<br />

<br />
<br />
table 'type'<br />
<br />

<br />
|id|name|...|<br />

<br />
<br />
table 'call'<br />
<br />

<br />
|id|value|pilot.id|type.id|<br />

<br />
<br />
The result should look like:<br />
<br />

<br />
|--crosstab--|type#1.name|type#2.name|type#3.name|...|<br />

<br />
|pilot.name#1|call.value#11|call.value#12|call.value#13|...|<br />

<br />
|pilot.name#2|call.value#21|call.value#22|call.value#23|...|<br />

<br />
|pilot.name#3|call.value#31|call.value#32|call.value#33|...|<br />

<br />
<br />
What I want to do is the following:<br />
<br />
I want to "create" a new collumn which contains the following:<br />
<br />
type#4.name = (type#1.name)/(type#3.name) (of cource, I cannot divide strings).<br />
<br />
The result should show call.value#4x = (call.value#1x)/(call.value#3x)<br />
<br />
How can I create such a function? Computed Collums won't work.<br />
<br />
Thanks!

Find more posts tagged with

Comments

Hi mgrohmann,

So, you're only trying to make this one column?

hi there,

I'm tryin a create a few collumns which are defined by math operations (e.g. collumn 1 + collumn 2 / collumn 3).

So is there an easy solution for it? I don't wanna create a new collumn in the DB, just want to have a "virtual" collumn inside the crosstab.

Thanks again!

mgrohmann,

One way that you could do this would be to add the same columns from your dataSet to your dataCube measures more than once until you have enough "extra" values to create the computed columns you want. Then, you add all of the measures to your crosstab, but delete the aggregation elements from the "extra" columns. You can then drop data elements into these cells and do whatever computation on the other values you want. This should work. Hope it helps. Let me know if you have questions.

hey michael,

unfortunately I couldn't get it working, may be I missunderstood sth:

I should clone my existing measures or should a clone an existing group in my data cube?

May be some screenshots can help to explain my problem more exactly:

clipboard01.jpg shows my datacube
clipboard02.jpg shows the links
clipboard03.jpg shows the crosstab

So I just want to compute some of the collumns of the data set "typ", could you explain it a little bit more detailed for me? Thanks!

mgrohmann,

Sorry, I misunderstood your setup. I was thinking you had different measures. My suggestion was to duplicate your measures within the dataCube, but this will probably only work right with static measure columns.

If you have 'n' columns in your crosstab, how many "computed columns" will you have? You could possibly use the derived measures feature, but they wouldn't be all at the end of your crosstab. Let me know.

hey michael,

in this example, I filter the group "typ" to 3 elements, so I've got 3 columns. Now I want to divide two of them.

The result should look like the following:

|--crosstab--|type#1|type#2|type#3|type#2/3|

|pilot.name#1|value#11|value#12|value#13|(value#12/value#13)|

|pilot.name#2|value#21|value#22|value#23|(value#22/value#23)|

|pilot.name#3|value#31|value#32|value#33|(value#32/value#33)|

Does this explanation help?

Thanks!

mgrohmann,

I understood after looking at the report design. I was just confusing myself before looking at it.

What I'm saying is that if you're wanting several columns after the original columns, I don't think you'll be able to do it. If you have 4 columns and you want to compute 1/2, 2/3, and 3/4, you could use the derived measure column, but the computed column would come after the measure column for each column dimension. You'll have to store the values in PersistentGlobalVariables, most likely, any way you go about this. I was just trying to get an idea of what caused you to need another computed column. What's the criteria to need a computed column? This way, I can try to come up with something that will work for you.

Let me know.

hey there,

thanks for the input. I tried to understand, but I'm a bit confused:

Where and how do I use PersistentGlobalVariables, you described?

The major problem, I think, is to get the value from another column in one columns measure.

In pseudo-code, the expression would look like:

if(dimension["Typ"]["id"] == 18) {

value = measure["value"] / #at this place, I need the the measure["value"] of dimension["Typ"]["id"] = 23 as divisor

value

}

So the question is, how can i access the measure-value from another column (in my example column-id 23.

In my opinion, thats what it's all about...

Thanks again!

mgrohmann,

In the onCreate script of the measure element in the crosstab, you can assign the value of the measure to a PGV named after the row and column names with something like below:

reportContext.setPersistentGlobalVariable(dimension["Typ"]["id"], this.getValue()); Then, in a totals column, derived measure column, or a report item below the crosstab, you can recall these values to do calculations. Hopefully this helps explain a little better. Let me know if you have questions.

Hey,<br />
<br />
great, the mapping works, but not in the correct way:<br />
<br />
I inserted your scriptin the onCreate-script:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable(dimension["Typ"]["id"], this.getValue());</pre>
<br />
and addet the code to my measure-field (just mapping the measure-value of column 24 to the measure-value of column 18):<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var value = measure["wert"]

if(dimension["Typ"]["id"] == 18) {

value = reportContext.getPersistentGlobalVariable(24);
value

}

else{

value

}
</pre>
<br />
The preview in eclipse shows the data just on row "too late" (see screenshot "globalpersistantvariable_previewer.jpg") and the web viewer only displays on of the values (126, see screenshot "globalpersistantvariable_web-viewer.jpg").<br />
<br />
I think there is some kind of issue with the other date-dimension, do you have an idea, how to respect the second dimension?<br />
<br />
Thanks again, your posts are a big help!

mgrohmann,

Trying to get a previous column to display a later column's value won't work correctly. If you reversed your logic to display 18's in 24's place, it'd work correctly, I think.

As for having a second dimension, you could name the PGV's in respect to both dimensions, this would just create more variables.

Absolutely GREAT. Had to format it a little bit with parseFloat(); but now it works.

If you're in Germany one day, be sure to get invited by me to more than one beer

THANKS!

ok, there are still things to manage

How can i suppress the columns I used for the calculation? I don't want them to be shown in the crosstab, but I need them for the calculation...

Hiding is not "enough", cause this just clears the cell/column, but do not hid the column itself...

any ideas?

mgrohmann,

Excellent! Glad to hear it's working!

Unfortunately, this is always an issue with crosstabs when trying to hide an element. The best solution I've found to date is to play with the borders of the cells to make them appear to not be there.

And, I may take you up on those beers if I'm ever in Germany..lol.

<br />
<br />
as promised: more issues to discuss:<br />
<br />
- if I set a fixed width for the cell, I can set the cell-content's width to 0, but it has no effect.<br />
- if I set the cell-content's width to 0 (while setting the cell width to auto), my computed column values which refers to other columns won't work ("no values available for computing") <br />
<br />
I suggest the following:<br />
<br />
- writing a function which checks for computed columns and call this function in every cell-content script<br />
<br />
- writing a function which checks for column ids to hide<br />
<br />
<br />
could you give me a hint, how to manage it?<br />
<br />
see the actual code for the measure-field without functions<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>

var value = measure["wert"]

if((dimension["Typ"]["id"]["unit"] == "seconds") && (measure["wert"] !=null)) {

value = Math.floor(measure["wert"]/3600) + ":" + Math.floor((measure["wert"]%3600)/60) + ":" + ((measure["wert"]%60).toString())
value

}

if(dimension["Typ"]["id"] == 30) {

value = (measure["wert"] + parseFloat(reportContext.getPersistentGlobalVariable(28)) + parseFloat(reportContext.getPersistentGlobalVariable(26)) + parseFloat(reportContext.getPersistentGlobalVariable(24)))/(parseFloat(reportContext.getPersistentGlobalVariable(18)));

if(value.toString() == 'NaN'){

value ="no values available for computing"
value
}

else{
value = (value*100).toFixed(2) + "%"
value

}
}

if(dimension["Typ"]["id"] == 50) {

var typ50 = measure["wert"]
var typ48 = parseFloat(reportContext.getPersistentGlobalVariable(48))
var typ46 = parseFloat(reportContext.getPersistentGlobalVariable(46))
var typ17 = parseFloat(reportContext.getPersistentGlobalVariable(17))
var typ3 = parseFloat(reportContext.getPersistentGlobalVariable(3))
var typ2 = parseFloat(reportContext.getPersistentGlobalVariable(2))
var typ1 = parseFloat(reportContext.getPersistentGlobalVariable(1))

value = typ1 + typ2 - typ17 - typ46 + typ3 - typ48 - typ50

if(value.toString() == 'NaN'){

value ="no values available for computing"
value
}

else{
value

}
}

else{

value

}

</pre>
<br />
and the code for the column's header<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
var value = dimension["Typ"]["id"]["name"]

if (dimension["Typ"]["id"] == 30){
value = "Test-Header#0"
value

}

if(dimension["Typ"]["id"] == 18){

value = "Test-Header#1"
value
}

if(dimension["Typ"]["id"] == 50){

value = "Test-Header#2"
value
}

else{

value

}
</pre>

ok, I've found the enemy:

Our DB-design does not save 0 values, so the db-entries are reduced to entries with another than the 0-value.

But this causes a problem for BIRT:

If there is no result for a pilot/typ-combination, BIRT does not interprete that as a "0", it thinks, that there is a data binding error and throws an exception.

How could I catch this exception with a function?

mgrohmann,

What value does it store other than 0? Do you have any idea?

Ok, I've to tell a little bit more about the ETL-process:

We are reading the values from many xls-files into the db.

For storage-reasons, we don't write datasets for empty cells from the xls-file into the mysql. So it's possible, that type x pilot on date = 2009-10-09 has the value 10.0, but for type x pilot on date = 2009-10-10 does no dataset exists.

This is very unhandy for the BIRT-crosstabs, 'cause the crosstabs does not accept absent datasets.

The only thing I have to do is to catch the exception while BIRT is executing the report an set the value of the missing dataset to "0".

Is this possible with a script inside "onCreate"?

nobody ever catched a BIRT-exception?

ok, I've created a little function, called "checkIfNull":<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>function checkIfNull(value){

if ((parseFloat(value).toString() == "null")||(parseFloat(value).toString() == "NaN")){

return 0.0;

}
else{

return value;
}

}</pre>
<br />
so this one does the trick for the non-existing datasets.<br />
<br />
<br />
Now I'm only facing one major problem on my way to heaven

:<br />
<br />
The trick with the reportContext.setPersistentGlobalVariable(dimension["Typ"]["id"], this.getValue()); and give my measure-field the following function:<br />
<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>if(dimension["Typ"]["id"] == 3){

value1 = parseFloat(reportContext.getPersistentGlobalVariable(1));
value2 = parseFloat(reportContext.getPersistentGlobalVariable(2));
value3 = value1 + value2;
}</pre>
<br />
In the BIRT previewer, everything is fine, but in the web viewer and PDF, it won't work.<br />
<br />
this is how it look in the previewer:<br />
<br />
<br />
| typ#1 | typ#2 | typ#3 = typ#1+typ#2 | ..<br />
10.10.2009 | 1 | 2 | 1+2=3 | ...<br />
10.10.2009 | 2 | 2 | 2+2=4 | ...<br />
10.10.2009 | 5 | 2 | 5+2=7 | ...<br />
<br />
In the web viewer of pdf, the system first aggregates the complete column 1, then the complete column 2, then 3...<br />
<br />
So the value for parseFloat(reportContext.getPersistentGlobalVariable(1)); is 5 and for column2, it's 2 for every following column:<br />
<br />
| typ#1 | typ#2 | typ#3 = typ#1+typ#2 | ..<br />
10.10.2009 | 1 | 2 | 5+2=7 | ...<br />
10.10.2009 | 2 | 2 | 5+2=7 | ...<br />
10.10.2009 | 5 | 2 | 5+2=7 | ...<br />
<br />
<br />
How can I force BIRT to calculate the values BY ROW first, not by column?<br />
<br />
edit: alternatively, I would add a row-number to the name of the PersistentGlobalVariable, but how can I read the row-number from an IDataItemInstance-Element?

ok, the runningcount should do the trick for the dataset, but that's not what I wanted:

I need something like:

for every unique date {

row_number +=1;

}

and put that into my persistantGlobalVariable. If this works, I should be able to access the globalvariable with

reportContext.getPersistentGlobalVariable("valueOf(column-id)+"/"+valueOf(row_number));

I'm looking forward, to find a solution, thanks to your posts

ok, it's the web viewer, this component totally crashes all the used functions, but after a deployment in pentaho, the report works.

Thanks

mgrohmann,

Sorry for the delayed response, I was out of town at the SpringOne show all last week. Glad you found a way to get it to work!

I really asked myself, why the previewer has such issues, even though the integrated pentaho-version does not have this problems.

Next step is the horrible PDF-output, I'm going to deploy a new thread