Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Script: Accessing specific cross-tab cell data
Script: Accessing specific cross-tab cell data [message #922917] Tue, 25 September 2012 09:01 Go to next message
Marius Lessiak is currently offline 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 09: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 16:06 Go to previous messageGo to next message
Jason Weathersby is currently offline 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
Re: Script: Accessing specific cross-tab cell data [message #924039 is a reply to message #922917] Wed, 26 September 2012 08:42 Go to previous messageGo to next message
Marius Lessiak is currently offline 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.
Re: Script: Accessing specific cross-tab cell data [message #924405 is a reply to message #924039] Wed, 26 September 2012 16:28 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Marius

Thanks very much for posting the example.

Jason
Previous Topic:fill dynamic textfield with "..."-dots
Next Topic:Get access of data set from 'beforeDrawDataPoint' event in script window
Goto Forum:
  


Current Time: Thu Aug 28 03:32:16 EDT 2014

Powered by FUDForum. Page generated in 0.01953 seconds