SQL Command Date Parameter

[Former Member]

Hi All,

I need the experts' help!

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).

Hoping this is possible!

SELECT
COALESCE(MAX(CONVERT(VARCHAR,EMP.NAME)), '*UNKNOWN CODER ID') "CODER USER ID"
,PAC.TAR_ID "TAR ID"
,PAC.CODED_DATE "CODED DATE"
,COUNT(PAC.CHARGE_LINE) "LINE COUNT"
,COALESCE(MAX(CONVERT(VARCHAR,PAC.TX_ID)), '*UNKNOWN TX ID') "TX ID"
,COALESCE(MAX(CONVERT(VARCHAR,PAC.OLD_RETRO_ETR_ID)), '*UNKNOWN TX ID') "ETR ID"

FROM
PRE_AR_CHG PAC
LEFT OUTER JOIN
CLARITY_EMP EMP
ON PAC.CODER_USER_ID = EMP.USER_ID

WHERE

PAC.CODED_DATE BETWEEN GETDATE() - 9 AND GETDATE()-2

GROUP BY
PAC.CODED_DATE
,EMP.USER_ID
,PAC.TAR_ID

[DellSC · Accepted solution]

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:

Where
  ('{?Date Type}' = 'Yesterday' and  PAC.CODED_DATE = GetDate() - 1) 
  or
  ('{?Date Type}' = 'Last Week' and PAC.CODED_DATE between 
        GetDate() - DatePart(wd, GetDate()) - 6 and
        GetDate() - DatePart(wd, GetDate()) )
  or
  ('{?Date Type}' = 'Last Month' and PAC.CODED_DATE between
        DateAdd(m, -1, GetDate() - Day(GetDate()) + 1) and
        GetDate() - Day(GetDate()) )
  or
  ('{?Date Type}' = 'Custom' and PAC.CODED_DATE between {?StartDate} and {?EndDate})

If you've never worked with parameters in a command before, please see my blog post here for more information.

-Dell

[Former Member · Accepted solution]

Do I have to make a {?StartDate} and {?EndDate} parameters in the command edit window? Also what Value type would the parameter(s) be?

Thanks so much for your help!

[Former Member · Accepted solution]

I'm getting a failed to retrieve data from database error when I put the where clause above in the command and make a string parameter called Date Type as well, your help is appreciated!