Cross Table: Property control in combination with Case when and Nest

[Vincent Verhoef]

Hi all,

I am trying to create a custom expression for a cross table to have see Mutiple vertical columns at once. When I check the custom expression of a situation where you manually add vertical columns, the custom expression is as follows:

<[Column A] NEST [Column B]

I tried to implement this logic within a case when statement using a property control:

<Case

when "${Property1}" = "ColumnA" then [ColumnA]

when "${Property1}" = "ColumnB" then ([ColumnB] NEST [ColumnC])

end as [${Property1}]>

The first line with 'ColumnA' works fine, however when I try to nest column B with C I get an error like: What am I doing wrong, or how should I setup the custom expression to make this work?

Thanks in advance!

[Kirsten Smith (she/her)]

For the nested expression it should be in this format:

<[ColumnB] NEST [ColumnC]>

[Vincent Verhoef]

Thanks for your reaction Kirsten. Your proposed solution however does not work and raises the following error:

Used code:

<Case

when "${Property1}" = "ColumnA" then [ColumnA]

when "${Property1}" = "ColumnB" then (<[ColumnB] NEST [ColumnC]>)

end as [${Property1}]>

What would the correct syntax be?

Thanks in advance

[Kirsten Smith (she/her)]

It turns out that 'NEST' is a top-level function, so it cannot be used in a case() statement. What I would suggest is that you create a property control with 'Set property value through' set to "Expressions". Then you can create all of the different expressions you want in your cross table, and even set the Display name to the desired option.

[Vincent Verhoef]

Hi Kirsten,

Thanks again for your reaction, but again I cannot get this setup to work.

I added the property expression as follows:

Name | Expression

Column A | [Column A]

Column B | <[ColumnB] NEST [ColumnC]>

And on the vertical axis of the cross table I implemented this code to work with the property:

<${ZZZProp2}>

This results in the same errors as I have shown earlier.Am I doing something wrong? Looking forward to your answer.

[Gaia Paolini]

would it work for you as a workaround to embed the expressions directly as values in your drop down list?

E.g. ColumnA corresponds to [ColumnA], ColumnB corresponding to <[ColumnB] NEST [ColumnC]> etc

[Vincent Verhoef]

Hi Gaia, thanks for your answer! But isn't this solution exactly what I described above?

I embedded the expressions directly as the values in the following way:

Display Name | Expression

Column A | [Column A]

Column B | <[ColumnB] NEST [ColumnC]>

Then, within the cross table I used <${Property}> to call the property. The function seems to work for the expression of 'Column A', where the table shows the expected column. The 'Column B' expression results in the following error:

 

[Gaia Paolini]

have you tried removing the <> from ${Property} ? You already have them in the expression.

[Vincent Verhoef]

This works, thanks a lot!