使用MS Report Builder连接Snowflake时日期参数报错求助

环境 #

• Microsoft Report Builder 15.0.19914.0
• 数据源：Snowflake（ODBC连接）

原始正常查询（固定日期） #

WITH calls_presented AS (
  SELECT
    "Resource"."resourcename",
    COUNT(DISTINCT "ContactCallDetail"."sessionid") AS presented
  FROM
    "ContactCallDetail"
    JOIN "AgentConnectionDetail"
      ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
      AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
      AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
      AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
    JOIN "Resource"
      ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
  WHERE
    "ContactCallDetail"."startdatetime" BETWEEN '2023-04-09 00:00:00' AND '2023-04-15 23:59:59'
  GROUP BY
    "Resource"."resourcename"
),
calls_handled AS (
  SELECT
    "Resource"."resourcename",
    COUNT(DISTINCT "ContactCallDetail"."sessionid") AS handled
  FROM
    "ContactCallDetail"
    JOIN "AgentConnectionDetail"
      ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
      AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
      AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
      AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
    JOIN "Resource"
      ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
  WHERE
    "ContactCallDetail"."startdatetime" BETWEEN '2023-04-09 00:00:00' AND '2023-04-15 23:59:59'
    AND "ContactCallDetail"."contactdisposition" = 2
    AND "AgentConnectionDetail"."talktime" > 0
  GROUP BY
    "Resource"."resourcename"
),
login_time AS (
  SELECT
    "Resource"."resourcename",
    ROUND(COUNT(DISTINCT CASE WHEN "AgentStateDetail"."eventdatetime" < '2023-04-09 00:00:00' THEN "AgentStateDetail"."eventdatetime" END) / COUNT(*) * 1.0, 2) AS login_percentage
  FROM
    "AgentStateDetail"
    JOIN "Resource"
      ON "AgentStateDetail"."agentid" = "Resource"."resourceid"
      AND "AgentStateDetail"."profileid" = "Resource"."profileid"
  WHERE
    "AgentStateDetail"."eventtype" = 1
  GROUP BY
    "Resource"."resourcename"
)
SELECT
  calls_handled."resourcename" AS AgentName,
  COALESCE(calls_handled.handled, 0) AS CallsHandled,
  COALESCE(calls_presented.presented, 0) AS CallsPresented,
  CASE
    WHEN COALESCE(calls_presented.presented, 0) = 0 THEN 0
    ELSE ROUND(COALESCE(calls_handled.handled, 0) / COALESCE(calls_presented.presented, 0), 2)
  END AS HandleRatio,
  login_time.login_percentage AS LoginBeforeTopHour
FROM
  calls_handled
  FULL JOIN calls_presented
    ON calls_handled."resourcename" = calls_presented."resourcename"
  LEFT JOIN login_time
    ON calls_handled."resourcename" = login_time."resourcename"
ORDER BY
  calls_handled."resourcename"

参数化后的查询（报错版本） #

WITH calls_presented AS (
  SELECT
    "Resource"."resourcename",
    COUNT(DISTINCT "ContactCallDetail"."sessionid") AS presented
  FROM
    "ContactCallDetail"
    JOIN "AgentConnectionDetail"
      ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
      AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
      AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
      AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
    JOIN "Resource"
      ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
  WHERE
    "ContactCallDetail"."startdatetime" BETWEEN @StartDate AND @EndDate
  GROUP BY
    "Resource"."resourcename"
),
calls_handled AS (
  SELECT
    "Resource"."resourcename",
    COUNT(DISTINCT "ContactCallDetail"."sessionid") AS handled
  FROM
    "ContactCallDetail"
    JOIN "AgentConnectionDetail"
      ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
      AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
      AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
      AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
    JOIN "Resource"
      ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
  WHERE
    "ContactCallDetail"."startdatetime" BETWEEN @StartDate AND @EndDate
    AND "ContactCallDetail"."contactdisposition" = 2
    AND "AgentConnectionDetail"."talktime" > 0
  GROUP BY
    "Resource"."resourcename"
),
login_time AS (
  SELECT
    "Resource"."resourcename",
    ROUND(COUNT(DISTINCT CASE WHEN "AgentStateDetail"."eventdatetime" < @StartDate THEN "AgentStateDetail"."eventdatetime" END) / COUNT(*) * 1.0, 2) AS login_percentage
  FROM
    "AgentStateDetail"
    JOIN "Resource"
      ON "AgentStateDetail"."agentid" = "Resource"."resourceid"
      AND "AgentStateDetail"."profileid" = "Resource"."profileid"
  WHERE
    "AgentStateDetail"."eventtype" = 1
  GROUP BY
    "Resource"."resourcename"
)
SELECT
  calls_handled."resourcename" AS AgentName,
  COALESCE(calls_handled.handled, 0) AS CallsHandled,
  COALESCE(calls_presented.presented, 0) AS CallsPresented,
  CASE
    WHEN COALESCE(calls_presented.presented, 0) = 0 THEN 0
    ELSE ROUND(COALESCE(calls_handled.handled, 0) / COALESCE(calls_presented.presented, 0), 2)
  END AS HandleRatio,
  login_time.login_percentage AS LoginBeforeTopHour
FROM
  calls_handled
  FULL JOIN calls_presented
    ON calls_handled."resourcename" = calls_presented."resourcename"
  LEFT JOIN login_time
    ON calls_handled."resourcename" = login_time."resourcename"
ORDER BY
  calls_handled."resourcename"

报错信息 #

An error occurred while executing the query.
ERROR [42000] SQL compilation error:
syntax error line 15 at position 48 unexpected '@StartDate'
syntax error line 15 at position 63 unexpected '@EndDate'
syntax error line 33 at position 48 unexpected '@StartDate'
syntax error line 33 at position 63 unexpected '@EndDate'
syntax error line 42 at position 72 unexpected '@StartDate'

已尝试操作 #

• 在Report Builder中设置@StartDate和@EndDate为DateTime数据类型
• 为参数添加格式表达式：=FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate)、=FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate)，问题未解决

解决方法 #

核心原因 #

Snowflake ODBC驱动遵循ODBC标准参数占位符规则，使用?而非@参数名，直接用@StartDate会被Snowflake识别为无效语法。

步骤 #

1. 修改SQL查询的参数占位符
   将所有@StartDate替换为?，所有@EndDate替换为?，注意参数顺序要和后续映射的Report Builder参数顺序一致。修改后的SQL如下：

   WITH calls_presented AS (
     SELECT
       "Resource"."resourcename",
       COUNT(DISTINCT "ContactCallDetail"."sessionid") AS presented
     FROM
       "ContactCallDetail"
       JOIN "AgentConnectionDetail"
         ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
         AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
         AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
         AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
       JOIN "Resource"
         ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
     WHERE
       "ContactCallDetail"."startdatetime" BETWEEN ? AND ?
     GROUP BY
       "Resource"."resourcename"
   ),
   calls_handled AS (
     SELECT
       "Resource"."resourcename",
       COUNT(DISTINCT "ContactCallDetail"."sessionid") AS handled
     FROM
       "ContactCallDetail"
       JOIN "AgentConnectionDetail"
         ON "ContactCallDetail"."sessionid" = "AgentConnectionDetail"."sessionid"
         AND "ContactCallDetail"."sessionseqnum" = "AgentConnectionDetail"."sessionseqnum"
         AND "ContactCallDetail"."profileid" = "AgentConnectionDetail"."profileid"
         AND "ContactCallDetail"."nodeid" = "AgentConnectionDetail"."nodeid"
       JOIN "Resource"
         ON "AgentConnectionDetail"."resourceid" = "Resource"."resourceid"
     WHERE
       "ContactCallDetail"."startdatetime" BETWEEN ? AND ?
       AND "ContactCallDetail"."contactdisposition" = 2
       AND "AgentConnectionDetail"."talktime" > 0
     GROUP BY
       "Resource"."resourcename"
   ),
   login_time AS (
     SELECT
       "Resource"."resourcename",
       ROUND(COUNT(DISTINCT CASE WHEN "AgentStateDetail"."eventdatetime" < ? THEN "AgentStateDetail"."eventdatetime" END) / COUNT(*) * 1.0, 2) AS login_percentage
     FROM
       "AgentStateDetail"
       JOIN "Resource"
         ON "AgentStateDetail"."agentid" = "Resource"."resourceid"
         AND "AgentStateDetail"."profileid" = "Resource"."profileid"
     WHERE
       "AgentStateDetail"."eventtype" = 1
     GROUP BY
       "Resource"."resourcename"
   )
   SELECT
     calls_handled."resourcename" AS AgentName,
     COALESCE(calls_handled.handled, 0) AS CallsHandled,
     COALESCE(calls_presented.presented, 0) AS CallsPresented,
     CASE
       WHEN COALESCE(calls_presented.presented, 0) = 0 THEN 0
       ELSE ROUND(COALESCE(calls_handled.handled, 0) / COALESCE(calls_presented.presented, 0), 2)
     END AS HandleRatio,
     login_time.login_percentage AS LoginBeforeTopHour
   FROM
     calls_handled
     FULL JOIN calls_presented
       ON calls_handled."resourcename" = calls_presented."resourcename"
     LEFT JOIN login_time
       ON calls_handled."resourcename" = login_time."resourcename"
   ORDER BY
     calls_handled."resourcename"
   

2. 映射Report Builder参数到ODBC占位符

   • 打开数据集属性，切换到「参数」选项卡
   • 按SQL中?出现的顺序添加参数映射：
     1. 第1个? → 选择StartDate参数
     2. 第2个? → 选择EndDate参数
     3. 第3个? → 选择StartDate参数
     4. 第4个? → 选择EndDate参数
     5. 第5个? → 选择StartDate参数
   • 确保每个参数的「数据类型」设置为DateTime，无需额外格式化，ODBC会自动处理类型转换。

3. 验证驱动版本
   确保使用的Snowflake ODBC驱动是最新稳定版，旧版本可能存在参数传递兼容性问题。

内容的提问来源于stack exchange，提问作者Joanne Angelia