I have created several reports using the data and templates found at http://doc.qodbc.com/qodbc/20/reports/sp_report_detail.html with great results.  However, I am having issues trying to get this to work with Budget reporting.  I have copied the example and pasted the following into the add comand report creation area;

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

I keep getting this error;

"Failed to retrieve data from the database.

Details: 42000:[QODBC] Expected lexical element not found: FiscalYear [Database Vendor Code: 11015]"

...and dont know how to resolve.  The default in the example is 2003.  I have tried everything I can think of to replace that, but no luck. 

Can someone point me in the right direction?

There's nothing wrong with the statement:

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

other than may be FiscalYear = 2003 should be: FiscalYear = 2007 or 2008

See: How do I run a Profit & Loss Budget Overview Report? and How do I use QODBC with Crystal Reports? 

For sp_reports or manual SQL statements, you must use “Add Command” and type or paste your SQL statement or sp_report command.

Tom,

Thanks for the help.  I agree, I am 100% trusting of the statement.  Any idea why the error though?  I use this product quite a bit with great success, just not on this specific report. 

Repeat, there is nothing wrong with:-

It runs perfectly in Crystal Reports XI and I get the report to display without any errors:

Tom,

I respect your position and agree with you that there is nothing wrong with the code.  That point is not disputed and I attempted to state as such in my last reply.  However, I do have a real life error on my end, and I have posted this question for help.

It gives me the error code posted no matter what I try.  With the assumption by all of us that the code is NOT the problem, what could it be?  Do you have any feedback as to the references in the error code? 

I am not an expert, but if you (or anyone else) are able to point me in a direction it would help a great deal.   

Tom,

I tried a new report, same issue.  Further, I did the same with several of the sample sp_report templates and found that any report that references budget gets this error.  I have done this dozens of times, but just wanted to verify for you.  I understand that it does work fine for you, and should for me.  I am sure the problem is not with the driver or the report format, but something else and unique to my situation. 

I did some digging and found that in QuickBooks (2003) our budget is setup as Fiscal Year 200X-0X, not Fiscal Year 200X.  Our fiscal is not a calendar year.  I am not sure how this is referenced on the back side, but in QB that is the format.  Is the root of the error in that?  Here is a quick screenshot of an example (this is 2005-2006). 

Again, I understand all of this SHOULD work...but it is not.  Every other thing is working exactly right, and has for quite some time (multiple sp_reports based reports).  This is a bug that has me stumped.  Something is wrong, and there is a solution to be found...just needing some expert advice on where to look. 

Interesting, try:

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 5, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

Sorry for the delay...been messing with this on and off...

Tried what you reccomended, same thing still.  Does the error given provide any direction for me to look into on my side?  Otherwise the only other variable is my "non calendar year" fiscal reporting period.

Same thing.  Tried various options for date, including all reccomened previous to this...