The Jedox Macro Engine implements PHP to interact with the Jedox Spreadsheet. One of the most used and basic functions is referring to a cell reference in the PHP code.  You can put data into a cell on the spreadsheet, or you can read from that cell.

Download the example spreadsheet here

Reading Cells

// read a cell value:

$value = ActiveSheet()->Range('E7')->value;

return __msgbox($value, 'Returning a Cell Value', 'Info');

// or you can use

return __msgbox(ActiveSheet()->Range('E7')->value, 'Returning a Cell Value', 'Info');

Writing Cells

// Write a value into a cell:

ActiveSheet()->Range('E10')->value = "hello again";

Note, the warning in my previous post. Depending on the complexity of the calculation and the type of cell you are referring to, the calculation timing may be out when referring to cells.

For instance, if you have a =PALO.DATA in the cell that is volatile and you are using a Macro to read the cell value, depending on the order of calculation you can pick up the wrong result. If you are trying to read a value from a cell that contains a PALO.DATA formula, sometimes it is easier to just refer to the cell in the cube using the application-> command in the Macro.

Returning Cube Values

// Get a value from a cube :

$value = application()->palo_data("localhost/Demo","Sales", "All Products", "Europe", "Jan", "2011", "Actual", "Units");

Note here that the formula palo_data, not palo.data. This is because the Palo/Jedox functions implemented in the Macro engine are directly from the Jedox PHP API.  Also, the point/full stop/period is a reserved character in PHP. It is the concatenation character between strings.

Finally, an interesting/semi -advanced concept for you. Sometimes you want to retrieve a cell value that is not necessarily an intersection in the cube, but the sum of certain subsets across one or more dimensions. For example, want to retrieve the Total Unit value for a collection of top 10 Regions. Unfortunately, you cannot refer to a subset as a substitute for an element name in a PALO.DATA formula (yet). But, we can use a macro to achieve the same effect.

Returning a Subset

// Return data cell based on a subset on the Regions Dimension.

// NOTE: The PHP subset is different to the spreadsheet function in 2 ways:

// 1. Replace all . with _ (eg palo.subset becomes palo_subset)

// 2. all blank arguments need to be replaced with NULL (eg PALO_HFILTER("West",FALSE,FALSE,,,,,,) becomes PALO_HFILTER("West",FALSE,FALSE,NULL,NULL,NULL,NULL,NULL,NULL))

$ArraySS = application()->PALO_SUBSET("localhost/Demo","Regions",1,NULL,PALO_HFILTER("West",FALSE,FALSE,2,NULL,NULL,NULL,NULL,NULL),NULL,NULL,NULL,NULL,PALO_SORT(1,0,NULL,0,NULL,0,1));

// Get array size for looping

$ArraySize = count($ArraySS);

for ($i=0; $i<$ArraySize; $i++)

{

$value = application()->PALO_DATA("localhost/Demo","Sales", "All Products", $ArraySS[$i][1], "Jan", "2010" ,"Actual" ,"Units");

$ValueTotal = $ValueTotal + $value;

}

ActiveSheet()->Range('E22')->value = $ValueTotal;