cancel
Showing results for 
Search instead for 
Did you mean: 

Parameter for Last Week, Last Month & Last Year from the current Date

Former Member
0 Kudos
2,191

I have a crystal report that has a StartDate & EndDate parameters from the stored procedure that I am calling. Now i got a new requirement saying that my user wants a drop-down parameter in which they need to be able to select last week (Sunday to Saturday), last month, last year instead of manually selecting start date and end date parameters? Can anyone please explain me how to do this?

(FYI, I am using Crystal 2008)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Thanks Abhilash for the idea to write the logic in Stored procedure.

I wrote the code in SP, & it worked successfully.

Here is the code that I used:

If @DateRange='Yesterday'

BEGIN

SET @StartDate = DATEADD(dd, datediff(dd, 0, GETDATE()) - 1, 0)

SET @EndDate = DATEADD(S, -1, datediff(dd, 0, GETDATE()))

END

ELSE

If @DateRange='Last Week'

BEGIN

SET @StartDate = DATEADD(wk,DATEDIFF(wk,7,GETDATE()),-1)

SET @EndDate = DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5)

END

ELSE

If @DateRange='Last Month'

BEGIN

SET @StartDate = DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

SET @EndDate = DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

END

ELSE

If @DateRange='Last Quarter'

BEGIN

SET @StartDate = dateadd(qq, datediff(qq, 0, GETDATE()) - 1, 0)

SET @EndDate = DateAdd(s, -1, dateadd(qq, DateDiff(qq, 0, GETDATE()), 0))

END

ELSE

If @DateRange='Last Year'

BEGIN

SET @StartDate = DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

SET @EndDate = DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

END

ELSE

If @DateRange='Campaign'

BEGIN

SET @StartDate = (Select [CampaignStartDate] FROM [DataMart].[Dim].[Campaign] WHERE [CampaignSK]=@CampaignSK);

SET @EndDate = (Select [CampaignEndDate] FROM [DataMart].[Dim].[Campaign] WHERE [CampaignSK]=@CampaignSK);

END

ELSE

If @DateRange='Custom'

BEGIN

SET @StartDate = @StartDate

SET @EndDate = @EndDate

end

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Naveen,

You'll need to handle the logic in the Stored Proc's where clause.

If you're using oracle, I have something like this in the Stored Proc's where clause for our reports:

Where {Date_Field} Between

Decode('{?Relative Date}',

'Yesterday', sysdate - 1,

'Last Week', Tunc(sysdate, 'IW')-7)


AND


Decode('{?Relative Date}',

'Yesterday', sysdate,

'Last Week', Tunc(sysdate, 'IW'))


where 'Relative Date' is a prompt in the Stored Proc and 'yesterday'. 'last week' are the 'static values' in the prompt.


-Abhilash

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Here is an input for the requirement you have but check whether it's feasible for you.

We have similar requirement, but not on Stored Procedure though, we use Command Object SQL query.

So, we have couple of Database functions which accept the user selected dates along with an additional parameter called Frequency.

Frequency parameter in Crystal will have the values like Last Week, Last Month, Last 3 Months etc.

So, based on the Frequency selected, we return the date range from the 2 functions I mentioned above which will override the user selected date range parameter.

So, check whether you are allowed to create DB functions and then modify the stored proc to call those functions instead of the user selected date range.

Hope this helps.

Thanks,

Raghavendra

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

To add more to previous post, if you just need to have options of Last Week, Last Month and Last Year only, you can try with record selection date range by using Crystal inbuilt functions like LastFullWeek, LastFullMonth and LastYearYTD.

Thanks,

Raghavendra