Forum Discussion
ERROR [42000] [Cache ODBC][State : 37000][Native Code 12]
Hello, I am currently using the Power BI Report Builder tool to generate a report that involves dynamic parameters. However, I have encountered an error message whenever I attempt to create and implement a parameter in the Power BI Query Editor. The error message is as follows:
FYI: I have successfully created a data source connection using "InterSystems ODBC" Version 2015.01.00.429, and I am able to retrieve data without any issues.
Query: select Payment_History.OCCURENCE_DATE from Payment_History where Payment_History.OCCURENCE_DATE >= @BeginDate
Error Message: ERROR [42000] [Cache ODBC][State : 37000][Native Code 12]
[C:\Program Files\WindowsApps\Microsoft.PowerBIReportBuilder_15.7.1801.0_x64__8w]
[SQLCODE: <-12>:<A term expected, beginning with one of the following: identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, %TRUNCATE, or %UPPER>]
[Location: <Prepare>]
[%msg: < A term expected, beginning with either of: identifier, constant, aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %MVR %SQLSTRING, %SQLUPPER, %STRING, %TR
If you have any suggestions or advice on how to resolve this issue, I would greatly appreciate your help.
Thank you.
2 Replies
Hi Anonymous
If you are using Power BI Report Builder, the error probably comes from the "@BeginDate" part.
Can you first test whether this query can return you the correct data if giving it a specific date value with the correct format required by the data source? For example use '2020-10-12'.
If you are sure which date format the data source requires and your query can work correctly with a specific date, then you may need to convert the paramter to the correct format. For example: =Format(Parameters!BeginDate.Value, "yyyy-MM-dd")
The following links may be helpful:
Power BI gateway and Report Builder support for ODBC data sources - Power BI | Microsoft Learn
Report Builder Parameter Formatting (Datetime to Date) - Microsoft Power BI Community
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
