Alice Matthews
- Apr 12
- 2 min read

Filter a Report with a Unique List of Attributes

Filtering a report by attributes is a useful feature of Jedox - but getting a dropdown box with a unique list of attributes isn't straightforward. Using the dimension alias will return duplicates which is ugly in a report. In this post I'll go through how to get a unique list of attributes and use them in a combobox to filter a report.

Using the macro editor, we can create a unique list of attributes based on a subset.

To do this we first have to read the attributes.

Using the subset editor to retrieve a list of element attributes would create PALO.SUBSET function like below:

=PALO.SUBSET("localhost/ACME_Data","Product",1,"Color",PALO.HFILTER("All products",0,false,,,,,,,),,,,,PALO.SORT(1,0,,0,,0,1,,))

This can be converted to macro PHP format:

PALO_SUBSET($server,$dim,1,$alias,PALO_HFILTER($HfilterElement,0,FALSE),PALO_SORT(1,0,null,0,null,0,1));

The subset formula returns an array where the first column is the element, second is the attribute and third is the level.

To return a list of the attributes without duplicates we need to take the second column of the subset (column 1), check whether the attribute already exists, if not add it, then return as an array.

We need the inputs for the macro function to be variables so the function can be used for different dimensions.

The full macro is below:

function GetAssociativeArrayFromSubSet($server, $dim, $alias, $HfilterElement, $defaultElement)

{

$SubSetInput = application()->PALO_SUBSET($server,$dim,1,$alias,PALO_HFILTER($HfilterElement,0,FALSE),PALO_SORT(1,0,null,0,null,0,1));

$arrArraySize = count($SubSetInput);

$arrWithoutDuplicates = array();

$arrWithoutDuplicates['empty'] = $defaultElement;

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

{

// SubSet always returns a 3-column-based array

// column 0 = element ; column 1 = attribute ; column 2 = level

$strValue = $SubSetInput[$i][1];

if ( !$strValue=='')

{

if (!$arrWithoutDuplicates[$strValue])

{

$arrWithoutDuplicates[$strValue] = $strValue;

}

return $arrWithoutDuplicates;

We can now use the formula in a combobox to get a unique list of attributes:

Then add a range on the spreadsheet that can be passed into an attribute filter:

Create the attribute filter:

Now when the report is fired up, products can be filtered based on the attribute.

The other thing that can be quite useful is to include a data filter in the macro, so the dropdown list only shows attributes where there's data. To do this you have to adjust the macro code.

New macro is as below:

function GetAssociativeArrayFromSubSetDfilter($server, $dim, $alias, $HfilterElement, $defaultElement,$dcube,$dversion,$dmonth,$daccount,$dmeasure)

{

$SubSetInput = application()->PALO_SUBSET($server,$dim,1,$alias,PALO_HFILTER($HfilterElement,0,FALSE),null,null,null,PALO_DFILTER(PALO_SUBCUBE($dcube,$dversion,$dmonth,null,$daccount,$dmeasure),array("<>","0"),null,null,null,4),PALO_SORT(1,0,null,0,null,0,1));