In some scenarios, it is necessary to use a database passthrough function (ApplySimple, ApplyComparison, ApplyAgg) to achieve specific query syntax that is not natively generated by the Strategy SQL Generation Engine. Sometimes the syntax must include double-quote marks, particularly for compound metrics defined in an MDX cube that are based on custom MDX syntax.
String constants are delimited by double-quote marks; therefore, it is not valid to write double-quotes as is into a string.
The expression editor in Strategy Developer provides an escape sequence to support double-quote marks in a string constant. The user should enter two double-quote marks in succession, as shown.
ApplySimple("cast(#1 as varchar(3)) + ': ""' + #0 + '""' ", , )
The purpose of this expression (for SQL Server) is to display items with both the ID and description in the same string, quoting the description:
1: "100 Places to Go While Still Young at Heart"
When SQL is generated, wherever two double-quotes appear in a row in the string, one double-quote is written into the query.
select a11.ITEM_ID ITEM_ID,
a11.ITEM_NAME ITEM_NAME0,
cast(a11.ITEM_ID as varchar(3)) + ': "' + a11.ITEM_NAME + '"' CustCol_9
from LU_ITEM a11
Importance for custom MDX metrics
In MDX queries, user-defined metric calculations place the MDX expression inside single-quote delimiters. Some MDX functions refer to objects in the cube using strings. For instance, the Properties function accesses a "member property" for the given hierarchy member (attribute element). The property name must be quoted within the expression string, calling for double quotes (since single quotes are already reserved to enclose the entire expression).
For instance, an MDX expression to filter a metric on a particular region by name would look like this:
'Sum(Filter({ ...members }, .CurrentMember.Properties("MEMBER_NAME") = "Northeast"), .)'
The double-quote escape sequence is required to enter this expression into a custom MDX metric.
ApplySimple("Sum(Filter({ ...members }, .CurrentMember.Properties(""MEMBER_NAME"") = ""#1""), #0)", Revenue, ?)
Answering the prompt with Northeast generates the following MDX query, and the expected results:
with set as '{...members}'
