다음을 통해 공유

Datazen Data View Parameter Replacement Functions

  • 아티클

When using Datazen data views you may need to have some dynamic functionality which may not be available in the query language you are writing for.  For those cases Datazen has some pretty neat functions which can help in simplifying your queries.

These are rudimentary Datazen specific functions for parameter replacement:

  • DATEADD(@paramName|TODAY|NOW, y|M|d|H|m|s, interval[, format])
  • EXPANDLIST(@paramName[, separator[, beginQuote[, endQuote]]])
  • ISEMPTY(@paramName[, emptyRepl[, nonEmptyRepl]])
  • REPLACE(@paramName, search, repl)
  • TODAY([format])

The current version of server (3.0.3305) implements two variants of each function: one for DB connections (SQL, SSAS, ODBC, OLEDB) and one for non-DB connections (OData, SharePoint, XML, etc.)

The DB version will perform a query parameter substitution for each value. For example, using EXPANDLIST: given an SQL connection where @MyParam = "Value 1,Value 2,Value 3", EXPANDLIST(@MyParam) is converted to the string "@p1,@p2,@p3" with parameters @p1 = "Value 1", @p2 = "Value 2" and @p3 = "Value 3" passed into the query.

The non-DB version does inline substitution into the query string. For example, using EXPANDLIST: given an OData connection, you would probably do something like EXPANDLIST(@MyParam, " or ", "Key eq '", "'") to achieve the query string ".../Products?$filter=Key eq 'Value 1' or Key eq 'Value 2' or Key eq 'Value 3'”.

When creating parameters the type that you specified when setting up the parameter will represent the data differently:

  • Strings will be cast as strings
  • Numbers will be cast as numbers
  • Dates will be cast as dates

Notes: there are currently some limitations with the functions

  • Keys with commas break since commas are used as delimiters
  • EXPANDLIST can only accept parameters of type string
  • In previous versions of Datazen, all parameter substitution was done inline.  This necessitated the use of quotes to wrap string values (ex. WHERE x = '{{ @Xvalue }}'). While this is still the case for non-DB connections types, it is advised not to do so for DB types. While DB types will automatically unwrap the quotes from around a parameter if the parameter is the only text contained within the quotes, if any other text is present, the query will surely fail.  For example, while the above example evaluates to "WHERE x = @p1" something like "WHERE y = 'P001-{{ @Yvalue }}'" will evaluate to "WHERE y = 'P001-@p1'" which is clearly not proper SQL  parameter syntax.

Real Life Example using SSAS and AdventureWorks - note that you need to double quote on the outside and single quote within the function.

WITH
 MEMBER [Measures].[Total Sales] as  ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount])
  
SELECT
  {
  [Measures].[Total Sales],
  [Measures].[Internet Sales Amount],
  [Measures].[Reseller Sales Amount]
  }
  ON 0,
NON EMPTY
[Date].[Date].[All]
ON 1
FROM [Adventure Works]
WHERE
 IIF({{ @category }} = "",
    STRTOSET("{ [Product].[Category].[All] }"),
STRTOSET("{ {{ ExpandList(@category, ',', '[Product].[Category].&["+', '+"]') }} }")
    )

It produces this output - with

WITH
 MEMBER [Measures].[Total Sales] as  ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount])
  
SELECT
  {
  [Measures].[Total Sales],
  [Measures].[Internet Sales Amount],
  [Measures].[Reseller Sales Amount]
  }
  ON 0,
NON EMPTY
[Date].[Date].[All]
ON 1
FROM [Adventure Works]
WHERE
 IIF(@p1 = "",
    STRTOSET("{ [Product].[Category].[All] }"),
STRTOSET("{ [Product].[Category].&["+@p2+"],[Product].[Category].&["+@p3+"],[Product].[Category].&["+@p4+"],[Product].[Category].&["+@p5+"] }")

This is what the parameters look like - from an SSAS Trace

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
        <Parameter>
          <Name>p1</Name>
          <Value xsi:type="xsd:string">4,1,3,2</Value>
        </Parameter>
        <Parameter>
          <Name>p2</Name>
          <Value xsi:type="xsd:string">4</Value>
        </Parameter>
        <Parameter>
          <Name>p3</Name>
          <Value xsi:type="xsd:string">1</Value>
        </Parameter>
        <Parameter>
          <Name>p4</Name>
          <Value xsi:type="xsd:string">3</Value>
        </Parameter>
        <Parameter>
          <Name>p5</Name>
          <Value xsi:type="xsd:string">2</Value>
        </Parameter>
      </Parameters>