x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 119 subscribers
  • Views 3769 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
963
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
mcolwell

SSRS Date Parameters

SOLVED
Posted By

I am attempting to build a report for SSRS using Visual Studio. 

I have a query that is returning results, and the report is functioning perfectly.  However, the end user decided they wanted a date range selector on this report.  I have done this hundreds of times before with standard SQL databases/reports, but never before when accessing a Sage database.

Here is my issue.  When I put the date parameters into the query, it throws an error message.  Here is the line in the query that is causing me the issue:

AND SO_SalesOrderDetail.PromiseDate between {d @beginDate} and {d @endDate}

And here is the error message it gives:

Error[37000][ProvideX][ODBC Driver]Expected lexical element not found: <identifier>.

This query functions perfectly if I hard code a static date into it, but the user wants to be able to select a date range of their own.  Any ideas on how to fix this issue?

  • 0

    I would suggest doing the filter in VisualStudio using LINQ. It will be much faster as it will not have to re-query the database each time the user changes the date range.

  • 0 in reply to dlech

    Thanks for the response, but that's not quite what I was asking for.  The report will get run once or perhaps twice a day, so re-querying and speed isn't really going to be an issue.  

  • 0 in reply to mcolwell

    Even if you are not after speed, this is a way to make it work without wasting time figuring out the quirks of the ODBC driver.

    Just a shot it the dark though, could it be that your variables don't include the single quotes required around the date?

  • 0 in reply to dlech

    I tried about every variant I could think of to get the syntax to work, but I still get the same error.  If I put in no markings at all, I get an "Invalid Column Name @beginDate" error.  

    Is there anyone else who has been able to make this work?

  • 0 in reply to mcolwell
    verified answer

    It seems like it wants #'s around the date.  I tried to go back and find my examples/documentation but I couldn't so I'm just going from memory, but I know I've seen that message and think I resolved it and with syntax variations.  Wish I could provide more definite help but maybe that will keep you going in the right direction.  

    EDIT: I was thinking of something else above (I generally use Crystal Reports when creating Mas reports).  Don't include the parameter name in your Query at all.  Instead, right click your DataSet go into the properties and create a filter based on your parameter. That should include the parameters at the top of your report and the users can change them to their hearts content.  If it's a lot of data the report may be slow to load unless you set a default value to the parameter, maybe try =Today

  • 0 in reply to stuart_1

    Removing the parameter from the query, and instead putting a filter on the dataset resolved my issue.  Thanks!

*Community Hub is the new name for Sage City