Column Selection through Hierarchy Property with Custom Expressions

[Michiel van Schijndel]

Hi,

 

I'm trying to build a Cross-Table Report that allows end users (who will use the web-player)to select their own columns to display in a report. I've asked a few colleagues and learnt this is best done through creating a Text box, and inside the text box create a Hierarchy property. In the Hierarchy property I selected a Multiple Select Listbox and included a few columns from the existing data table. That way users can select those columns directly from the hierarchy property to be displayed (or removed) from the table.

 

In the Cross table axis I have the following axis values:

Horizontal Axis: (Column names)

Vertical Axis :Custom Expression :

Cell Values: Custom Expression: $map("Sum($esc(${HierarchyProperty2})) as [${HierarchyProperty2}]", ", ")

This way the vertical axis refers to the HierarchyProperty that I created and based on the selections made in the HIerarchyProperty, the table will show those columns on the vertical axis. Similar is done for the Cell Values, where I refer to Hierarchyproperty2 which has different columns and where a Sum calculation is made.

it seems to work properly, just how I want it.

However, and this is where the question comes: On the cell values I have several columns which are not standard included in the data table. Those are custom expressions where calculations are made. I wonder if it's possible to use a HierarchyProperty that is referring to those custom expressions rather than the columns directly available in the data table. I do see the option when creating a Hierarchy Property to set the values through Custom Expressions (See attachment), however as soon as I do that, the table stops to work. Perhaps I need to change the formula included on the Cell Values when I use custom expressions instead, but so far I'm only able to get it working by referring to existing columns in the report, but calculations don't seem to work.

Any assistance would be highly appreciated!

[David Boot-Olazabal]

Hi Michiel,

 

Would this be of any help to you https://spotfired.blogspot.com/2014/04/add-columns-dynamically-to-data-table.html

 

Kind regards,

 

David

[Michiel van Schijndel]

Hi David,

 

I'm afraid not.... Within our company I don't have a scripting license to use scripts, but also if I read the examples it looks like it's simply selecting some existing columns from the available columns of the table. That functionality I managed to get working without any script. It just however becomes difficult when I don't want to include existing columns as column selection, but I want to include custom expressions as column selection. It seems like I can't get the formula to work so that I can properly select different custom expressions/calculations in order to be included in the visualization.