Including a Presentation Variable in Custom SQL

Marcus M. Thompson

In an analysis, I can add a filter that refers to a presentation variable, and that filter looks like this:

Customer Name is equal to / is in @{MyCust}

If I want the filter to default to all values, I can refer to the presentation variable like this:

@{MyCust}{%}

Today, I'm creating an analysis using custom SQL. I want to include a where clause that uses @{MyCust}{%}, or its equivalent. When I include such a where clause, this error is thrown:

nQSError: 10058 A general error has occurred.

nQSError: 27002 Near <%>

SQL Issued: call NQSGetLevelDrillability('SELECT "Customer"."Name" saw_0 from "AR Invoices" where "Customer","Name" in (%) ')

Although the % is handled correctly in an "ordinary" analysis, it does not work in custom SQL.

Is there a syntax that I can use to accomplish this same type of filter in custom SQL?

Federico Venturin

Hi @Marcus M. Thompson ,

Replace the filter as follows in your logical SQL and it should work as expected:

"Customer"."Name" in (@{MyCust}['@']{"Customer"."Name"})

You have to use the column that you want to filter itself as the default value instead of % . In this way, you will get "Customer"."Name" = "Customer"."Name" when nothing is selected in the prompt and all customer names will be returned.

['@'] is required to properly handle multiple values for text columns.

Marcus M. Thompson

Thank you, Federico. That works perfectly! I was not familiar with the ['@'].