Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Issue with parameter on the WorkSpace Reportbuilder SSRS

Hi, I have created a report based on ODBC, it was working fine (on desktop and on the workspace) with filters implemented in DAX Query.

When I tried to make it more user-friendly, adding parameters, it is also working on the Desktop but when it is loaded to the Workspace, after completing the parameters, an error appears as below.

Do you know what is the reason? 

 

SSRS

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    A same error code ,user said:

    "I had a similar issue.  I traced the failure to a set of beginning and end date parameters that were set as DateTime in the SSRS report.  Apparently, the gateway server did not recognize this datatype, likely due to a missing library.    Changing the parameter data type to text and performing the date conversion in the Oracle filter clause fixed the problem: (....Where myDate between TO_DATE(:begindate,'MM/DD/YYYY') AND TO_DATE(:enddate,'MM/DD/YYYY')."

     

    Check if you have the problem for the same reason.

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, I am not sure how to do that,

      After validating the query, such a window appears:

      Regardless of whether I leave it as it is or if I change something, a message appears:

       

      "ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "?,'YYYY-MM-DD') AND TO_DATE(:?,'YYYY-MM-" (10713)"

       

      this is my query:

      SELECT
      PUB.cntl."cntl-nr",
      PUB.clgr."empl-code-1",
      PUB.clgr."empl-code-2",
      PUB.stom."arti-cd-base",
      PUB.stom."arti-cd-sfx",
      PUB.stom."adfc-cd-diffco",
      PUB.stom."dcmf-nr",
      PUB.stom."stom-type-stck",
      CONVERT('VARCHAR(10)',PUB.stom."stom-dt-tran",103) as "stom-dt-tran",
      PUB.stom."stom-nr",
      PUB.stom."stom-time-mut",
      PUB.stom."stom-qty-mut",
      PUB.stom."stom-am-mut",
      PUB.stom."stom-type-tran",
      PUB.stom."stom-id-user",
      PUB.stom."stom-ind-proc",
      PUB.adfc."busi-id"

      FROM
      PUB.stom
      INNER JOIN PUB.cntl ON PUB.stom."stom-id-tran" = PUB.cntl."cntl-nr"
      INNER JOIN PUB.clgr ON PUB.stom."stom-id-tran" = PUB.clgr."cntl-nr"
      INNER JOIN PUB.adfc ON PUB.stom."adfc-cd-diffco" = PUB.adfc."adfc-cd-diffco"
      AND PUB.stom."arti-cd-sfx" = PUB.adfc."arti-cd-sfx"
      AND PUB.stom."arti-cd-base" = PUB.adfc."arti-cd-base"

      WHERE
      PUB.stom."stom-type-stck"='FIN-QTY'

      AND PUB.stom."stom-dt-tran" BETWEEN TO_DATE(:?,'YYYY-MM-DD')
      AND TO_DATE(:?,'YYYY-MM-DD') -- not sure if it is ok

      --AND PUB.stom."stom-dt-tran" BETWEEN (?) AND (?) -- that was previously and works on Desktop only
      AND PUB.stom."stom-type-tran"='CNTL'

       

      Could you please advice how to do that?

       

      2more screenshots 

      DataSet properties:

      Parameter properties after change from date/time to Text