Eclipse Community Forums: BIRT » Script: Accessing specific cross-tab cell data

Help

Home

Home » Archived » BIRT » Script: Accessing specific cross-tab cell data

Show: Today's Messages :: Show Polls :: Message Navigator

Script: Accessing specific cross-tab cell data [message #922917]

Tue, 25 September 2012 13:01

Marius Lessiak

Messages: 6
Registered: September 2012

Junior Member

I have a crosstab containing visitors for websites grouped by quartals. It looks like this:

        | 2nd Quarter 2011 | 4th Quarter 2011 | 2nd Quarter 2012 |
        | Visitors         | Visitors         | Visitors         | 
--------|------------------|------------------|------------------|
do1.com | 10000            | 20000            | 40000            |
--------|------------------|------------------|------------------|
do2.com | 100.000          | 60000            | 80000            |
--------|------------------|------------------|------------------|

I'd like to show the development in percent based on the previous Quarter. And the development in percent based on the same quarter in the previous year. It should look like this:

        | 2nd Quarter 2011                   | 4th Quarter 2011                   | 2nd Quarter 2012                   |
        | Visitors | Development | Anual Dev | Visitors | Development | Anual Dev | Visitors | Development | Anual Dev |
--------|----------|-------------|-----------|----------|-------------|-----------|----------|-------------|-----------|
do1.com | 10.000   | n/a         | n/a       | 20000    | +100%       | n/a       | 40000    | +100%       | +300%     |
--------|----------|-------------|-----------|----------|-------------|-----------|----------|-------------|-----------|
do2.com | 100000   | n/a         | n/a       | 60000    | -40%        | n/a       | 80000    | +33%        | -20%      | 
--------|----------|-------------|-----------|----------|-------------|-----------|----------|-------------|-----------|

Since there is no aggregate function (like SUM or COUNT etc.) for that i need to calculate it on my own using script - is that correct? At least that is what i found in the forums: Use script in the oncreate event and calculate/insert the development values.
The script looks like this and works fine:

var prevValue = parseInt(reportContext.getGlobalVariable("prevValue "), 10);
var measureVal = measure["visitors"];
var devPerc = Math.round(((prevValue - measureVal ) / (prevValue)) * 100);
reportContext.setGlobalVariable("prevValue", measureVal);
this.setDisplayValue(devPerc);

So it boils down to manually saving and retrieving the values to global variables. For the anual development it is essentialy the same code but extended with some magic to only save every other time. All in all that works but looks complicated to me.

Looking at the report layout, intuitively i'd like to just access the data in the cell to the left or 2 steps to the left. Is this possible? Something like:

var myPosition = this.getColumnIndex();
var prevValue = this.getRowData()[myPosition - 1];

Is there any convenience function like that? Or do i have to implement that in the report? Or do i need to write my own aggregation function to use in the cross-tab?

Thanks for any answers and/or documentation links.

[Updated on: Tue, 25 September 2012 13:01]

Report message to a moderator

Re: Script: Accessing specific cross-tab cell data [message #923302 is a reply to message #922917]

Tue, 25 September 2012 20:06

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Have you looked at derived measures and relative time periods?
http://www.eclipse.org/birt/phoenix/project/notable4.2.php#jump_9

Do you have some sample data in a csv?

Jason

Report message to a moderator

Re: Script: Accessing specific cross-tab cell data [message #924039 is a reply to message #922917]

Wed, 26 September 2012 12:42

Marius Lessiak

Messages: 6
Registered: September 2012

Junior Member

Thanks a lot Jason, i finally got it: I could not use the "relative time periods" feature because the data in the csv is not imported as Date but as String. So i added a "Computed Column" where i parse the original Date String and insert it as a proper Date Type Date. After that i could add a Year/Quarter Group in the Data Cube.

In the crosstab i could then insert a "relative time period" as shown in the link you posted. With that i could access the data from the previous period and used it in a derived measure where i calculated the development in percent.

Now that looks excellent to me.

Attached is the report plus the example data in case anybody is interested.