Jump to content

BarChart X Axis:Custom Expression-Case statement and NEST columns failing

Kaivalya Deshmukh

Recommended Posts

Hello Everybody!

I'm trying to set X axis conditionally based on a property control (drop down) selection. My code in X axis custom expression goes this way :

case when "${varLocation}"="Cities" then [Region] NEST [City] when "${varLocation}"="States" then [Region] NEST [states] when "${varLocation}"="Region" then [Region]end as [Location]


but this code does not accept NESTing & throws an error saying "The expression is not valid after NEST on line 1..."

I tried using If() also but no luck. Though, I can achieve this using python script, I want to minimize the script usage & limit to only those situations when there is no other option.

Has anybody came across such issue Any help would be much appreciated!


Link to comment
Share on other sites

typically,NESTexpressions need to be surrounded by angle brackets like , however angle brackets are invalid syntax inside of any other statement (like CASE).

I think your best solution will be to change your Property Control from Fixed values to Expressions and put the exact expression there instead.

Link to comment
Share on other sites

Thank you niko for the solution, but unfortunately available columns for Expressions under Property Controls are from only default data table & I've multiple data tables in my dxp (& setting up current data table would not save me as I have to set up multiple property controls for different data table columns on different pages.)


Could there be any workaround for this

Link to comment
Share on other sites

  • 1 year later...
  • 1 year later...

I just bumped into the same problem;

The way I solved it was by replacing the NEST command with the Concatenate function:

case when="${varLocation}"="Cities" then Concatenate([Region], ">>", [City])

when "${varLocation}"="States" then Concatenate([Region], ">>", [states])

when "${varLocation}"="Region" then [Region]

end as [Location]>

I added ">>" to make sure I would end up with unique concatenated values.

(Don't forget to cast to String if you work with numerical fields)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...