none
How to set the "default" value for Date Parameters based on another parameter selection. RRS feed

  • Question

  • I need to set the "default" value for the Start Date and End Date parameters based on the selection made for the Time Frame parameter. I need to accomplish this without losing the calendar. 

    Parameters    Values

    Time Frame:  Today, Yesterday, Current Month, Previous Month

    Start Date:    set the "default" date based on the Time Frame selection

    End Date:      set the "default" date based on the Time Frame selection

    I know how to force cascading parameters to refresh which is accomplished by invalidating the previously populated values BUT how do you do that with dates!  Also, keep in mind that I am just trying to set the "default" value. If this cannot be accomplished in SSRS without messing up the calendar, can this be accomplished in Power BI?

    Tuesday, November 19, 2019 8:53 PM

Answers

  • Hi z134,

    The easiest way to accomplish this is to use a pre-generated calendar table.  If your database doesn't have one, google how to create one.  They are very useful for problems like this.

    Here's how to do it without a calendar table.

    1. Create Parameter Timeframe and give it available values:  "Today", "Yesterday", "Current Month", "Previous Month"
    2. Create the following dataset that looks like the following:
    with cte as
    (
    select
    	start_dt = 
    		(
    			case 
    				when @TimeFrame = 'Today' then convert(datetime,convert(date,getdate()))
    				when @TimeFrame = 'Yesterday' then dateadd(day,-1,convert(datetime,convert(date,getdate())) )
    				when @TimeFrame = 'Current Month' then convert(datetime, str(datepart(year, getdate())) + '-' + str(datepart(month,getdate())) + '-01')
    				when @TimeFrame = 'Previous Month' then dateadd(month,-1, convert(datetime, str(datepart(year, getdate())) + '-' + str(datepart(month,getdate())) + '-01'))
    			end
    		)
    )
    select
    	start_dt,
    	end_dt =
    		(
    			case 
    				when @TimeFrame in ('Today','Yesterday')	then dateadd(second,-1,dateadd(day,1,start_dt))
    				when @TimeFrame like ('%Month')				then dateadd(second,-1,dateadd(month,1,start_dt))
    			end
    		)
    from
    	cte;

    1. Go back to your @StartDate and @EndDate parameters and set  their default values to start_dt and end_dt.

    Done!

    I hope this helps.

    --Dan

    Thursday, November 21, 2019 7:59 PM

All replies

  • Hi, 

    Could you please give some logic examples which show us how the "default"s would be set? You could elaborate this a bit.

    You have four choices here in time frame, right?

     


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 20, 2019 5:51 AM
  • Let's say today is 11/20/2019.  Below is a picture of the report parameters.

    When user selects "Today" from the Time Frame parameter, Start Date & End Date should be set to 11/20/2019.

    When user selects "Current Month, Start Date should be set to 11/1/2019 and End Date 11/30/2019.

    When user select "Prior Month", Start Date should be set to 10/1/2019 and End Date 10/31/2019.

    So basically the Start Date and End Date "default value" should cascade from the Time Frame parameter.


    • Edited by zl34 Wednesday, November 20, 2019 7:51 PM
    Wednesday, November 20, 2019 7:49 PM
  • Hi z134,

    The easiest way to accomplish this is to use a pre-generated calendar table.  If your database doesn't have one, google how to create one.  They are very useful for problems like this.

    Here's how to do it without a calendar table.

    1. Create Parameter Timeframe and give it available values:  "Today", "Yesterday", "Current Month", "Previous Month"
    2. Create the following dataset that looks like the following:
    with cte as
    (
    select
    	start_dt = 
    		(
    			case 
    				when @TimeFrame = 'Today' then convert(datetime,convert(date,getdate()))
    				when @TimeFrame = 'Yesterday' then dateadd(day,-1,convert(datetime,convert(date,getdate())) )
    				when @TimeFrame = 'Current Month' then convert(datetime, str(datepart(year, getdate())) + '-' + str(datepart(month,getdate())) + '-01')
    				when @TimeFrame = 'Previous Month' then dateadd(month,-1, convert(datetime, str(datepart(year, getdate())) + '-' + str(datepart(month,getdate())) + '-01'))
    			end
    		)
    )
    select
    	start_dt,
    	end_dt =
    		(
    			case 
    				when @TimeFrame in ('Today','Yesterday')	then dateadd(second,-1,dateadd(day,1,start_dt))
    				when @TimeFrame like ('%Month')				then dateadd(second,-1,dateadd(month,1,start_dt))
    			end
    		)
    from
    	cte;

    1. Go back to your @StartDate and @EndDate parameters and set  their default values to start_dt and end_dt.

    Done!

    I hope this helps.

    --Dan

    Thursday, November 21, 2019 7:59 PM
  • Thank you for all your replies.  This is the correct answer; however, I would lose my calendar.  I did not want to set up a calendar table with prepopulated dates. 

    Monday, November 25, 2019 1:31 PM