Errors when using start and end dates as parameters in MS Report Builder via Snowflake DB

Question

I'm using Microsoft Report Builder version 15.0.19914.0 with Snowflake Dataset via ODBC connection. This is my working query which works perfectly with MS Report Builder

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"

Now once I switch the start and end date to datetime type parameters as in the below query

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"

I get the following error :

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'

I did try setting up expressions for the parameters like =FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate) and =FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate) as well. Since I thought this issue might be due to a date time format mismatch with MS Report Builder and Snowflake. Unfortunately that was not the solution. The @StartDate and @EndDate parameters are setup with DateTime data type in MS Report Builder.

Answer 1

ODBC expects ? for parameters:

WHERE "ContactCallDetail"."startdatetime" BETWEEN @StartDate AND @EndDate

should be:

WHERE "ContactCallDetail"."startdatetime" BETWEEN ? AND ?