Derived measure in crosstab - BIRT 2.2

Hi, 
 
another week, another crosstab question ;-) 
 
I know there have been several improvements for crosstabs in later releases of BIRT, especially in BIRT 2.5, particularly the ability to add derived measures, but unfortunately, I am stuck with BIRT 2.2 on this project. 
 
My dataset is structured as follows: 
|DATECREATED|CLAIMTYPE|STATUS| 
 
Each of these columns is a dimension of my crosstab, and the measure is a count of the number of claims per type (row), per quarter (column lvl1), per status (column lvl2). 
 
So far, nothing fancy. Now, what I'd like to display is a derived measure: closing ratio, calculated as closed*100/new 
 
So I've added a computed column to the dataset, closeRatio, with value 0. This computed column is then added as a measure in the cube. 
 
But when I drop this column in the crosstab, I then get a close ratio column per type, quarter and status. Obviously, what I need is just a column per type and quarter only. 
 
The 1st thought that came to mind is to hide the column, but make it visible in the sub-total per quarter, and override its expression with the calculation described above. But somehow, I think if the measure is hidden in the column, it wont appear in the sub-total. Unless there's a way to do this that I don't know about. 
 
So the 2nd option, which is not very elegant but *should* solve my problem, would be to do a SQL joint as follows: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select DATECREATED, CLAIMTYPE, STATUS
from CLAIMS
joint
select DATECREATED, CLAIMTYPE, 'Ratio'
from CLAIMS
</pre>
 
In this case, I will get a new 'status' named 'Ratio' and I can simply override its expression with my calculation, but the major drawback is that it will double the number of rows returned, incurring a performance hit which I'd rather avoid if possible. 
 
Any ideas? (apart from upgrading to BIRT 2.5 :rolleyes: ) 
 
Rgds, 
Cedric

Find more posts tagged with

Comments

mwilliams

Cedric,

I can't remember if you can in 2.2 or if it'll help in this case, but can you add a 2 column grid to the cell you need the derived measure for and add a data element or dynamic text box to the second cell of the grid to do your calculation? Not sure if this will work, but it might be an idea.

cypherdj

Hi Michael,

actually, I've decided to take a slightly different approach to this.

What I do is, as described in previous post, the SQL joint to create the added column.

At crosstab level, I've introduced 2 additional data bindings:
- newClaims (aggregate, function: count, filter by: measure["STATUS"] == "New", Aggregate on: Group/CaseType, Group2/quarter),
- closedClaims (aggregate, function: count, filter by: measure["STATUS"] == "Closed", Aggregate on: Group/CaseType, Group2/quarter).

Then the expression for the crosstab cell is:
if (measure["STATUS"] == "Ratio"){
if (data["newClaims"] > 0){
data["closedClaims"] * 100 / data["newClaims"]
} else {
0
}
} else {
measure["claimCount"]
}

This seems to do what I'm after, I just need to give a bit of a test to make sure it displays correctly across all different schemas (we use data source property bindings at runtime to point to the appropriate schema),

A bit of a hack to get a derived measure calculated but useful for anyone who cannot upgrade to BIRT 2.3 and use the crosstab derived measure enhancement.

Rgds,
Cedric