"There was an error parsing the query.  [Token line number = 2,Token line offset = 13,Token in error = 2011 ] Number:  -2146233087"

Entry Type: Program Issue or Documentation Issue

Product: Sage ERP MAS Intelligence

Application: n/a

Version Reported: 4.40

Status:

This issue is being researched by Engineering.

Subject:

"There was an error parsing the query.  [Token line number = 2,Token line offset = 13,Token in error = 2011 ] Number:  -2146233087" when generating a report with a date type passthrough variable.

Steps to Duplicate:

In ABC company:

1. Open Connector
2. Expand the MAS 90 4.0 ODBC Driver / Sage MAS(Auto Connect)
3. Right click Customer Sales 2-0(MAS) and select Add Expressions
4. In the Select the Expression Type to Add, select the SQL Expression radial button and click OK
5. When prompted for the SQL Expression name enter  PTVDATEEXPRESSION and click OK
6. In the Enter the SQL Text for the Expression, enter @EndDate@ + 7 and click OK
7. Open Report  Manager
8. Create a report using the Customer Sales container
9. In the Choose Column Fields, choose PTVDATEEXPRESSION and click OK
10. Expand the Report
11. Right click on the Parameters and select Add Parameters
12. In the Choose Filter Fields, select EndDate and click OK
13. Leave the Optional Default blank
14. Generate the Report
15. Enter the date 29 July 2011
16. Note the error "There was an error parsing the query.  [Token line number = 2,Token line offset = 13,Token in error = 2011 ] Number:  -2146233087"
17. Note if report is run in SQL Debug, the Query will show that the date has two single quotes:

    {d ''2011-07-29''} which should be {d '2011-07-29'}

Expected Behavior:

Error should not occur .

Possible Workaround:

1.Change the Data Type of the Report Date passthrough variable

2. Change the DateAge expression to the following: we are changing @REPORT_DATE@ to CAST('@REPORT_DATE@' AS DATETIME)

The expression should be something like :

CASE WHEN CAST('@REPORT_DATE@' AS DATETIME)+7 > AR_OpenInvoice.InvoiceDate THEN
AR_OpenInvoice.Balance
ELSE
0
END