Concatenating a field to a string causing strange filter in drilldown group

[Anonymous]

General details:

I have a drill down group setup to evaluate the name of a product associated with a ticket created by a call center.  Product values are straight forward (Microsoft Outlook, Excel, Access, PowerPoint, etc) but we also have a product value of 'Not Listed' in the event a product is not defined.  When using 'Not Listed' it is required to enter the product name into a free form textbox.

I have a chart showing the number of tickets per product and I want 'Not Listed' to display as 'NL: ' & the value from the free form text field (field in the data source).  I am using the below if statement within the group to determine if I should display the product name or the concatenated 'NL: ' & free form text field.

=if(substringcount(lower(product_name),'not listed'),'NL: ' & u_nl_prod_name,product_name)

So, if the product name is not listed, show 'NL: ' & u_nl_prod_name

      else show just the product name

The concatenate works fine but whenever a product value is selected in the chart for drilldown, a filter is applied to the values in the free form text field causing results to be completely wrong.  This happens if I select any product in the chart, not just 'Not Listed'.

I tried using text() to convert to to a string instead of a field but no luck.

[Anonymous]

Difficult to comment from your notes without seeing an application.

I also find calculated dimensions difficult to manage as selections made in sheet objects can be for only part of the calculated dimensions' formula.

A couple of suggestions. Enclose your formula within square brackets, or turn it into a seperate variable.

[=if(substringcount(lower(product_name),'not listed'),'NL: ' & u_nl_prod_name,product_name)]

No idea if these will work though.

Jonathan