SQL Command Date Parameter

I originally created this script to run a report every Monday for the previous Sun-Sat.

Now the request is that the report can be run for yesterday(Sunday), last week(Sunday-Saturday) and Last Month(previous last full month), and custom date ranges.

This is my original command, I know I need to add parameters in Crystal modify command window but that is where I get lost. Users want to see the above parameter options in a dropdown menu for start and end dates (t-1, t-7, mb-1, me-1, custom dates).

Related questions

3 Answers

This may be possible depending on which version of Crystal you're using, but probably not in the way that your users want it. I don't remember which version gave us optional parameters - it was either 2008 or 2011. However, there's no way to hide the date parameters if custom dates is not selected.

You'll create a "Date Type" parameter that will have your options - Yesterday, Last Week, Last Month, Custom. Create this parameter in the Command editor. Once you have the command saved, you'll be able to edit it in the Field Explorer of the report to add the valid values.

Based on the SQL you posted, I'm going to assume that you're using MS SQL Server - so I'll use that syntax. I'm also going to set this up so that it's not dependent on a report being run on a Monday - it will calculate the last full week and the last full month. Your Where clause will look something like this:

{?Start Date} and {?End Date} are also parameters with a "Date" type that you need to create in the Command Editor.

What is the specific error number that you get?

What type of database are you connecting to? I assumed MS SQL Server, but the syntax will probably be different if it's not. Do you have other filters in your Where clause? If yes, then you'll need to put a pair of parentheses around everything under the where clause above.

For troubleshooting, you can try a couple of things:

1. Move your query to a query tool like SQL Server Management Studio or Toad. Change the syntax on the parameters to that of the database. So, for example, if you're using SQL Server, you'll put something like this at the top of your query:

You'll replace '{?Date Type}' with @DateType, {?Start Date} with @StartDate, etc. Get your query working there will all of the options. Once it's working, remove the declare and set statements that you added above the query and change the parameters back to Crystal's syntax. Don't forget to add the single-quotes around {?Date Type} or else it won't work! Paste this back into the report.

2. In Crystal, comment out from the first 'OR' through the end of the condition so that you're just testing "Yesterday". Make sure that's working correctly. Then add back the first OR and the statement under it and make sure that "Last Week" is working correctly. Then add the last part of the condition back.

Add comment

Thank you so much! The above worked perfectly except for the custom date part, which in the end was not needed by the user.

I ran the command with each parameter separately and only got the error with the custom logic. The error was that the database couldn't connect/find the data. I had the custom parameter, startdate and enddate set up as you stated above.