Post reply

Input Variables

  • January 13, 2009 at 1:50 pm

    #126604

    Hi,

    I'm trying to figure out how to "conditionally" require a couple of input parameters. Based on the @timeframe the user inputs, the default start & end dates will be populated. The problem comes in if we want a userdefined timeframe so that they can enter any dates they want to.

    Here's some sample code:

    ALTER PROC [dbo].[spRS_dateDefaults_test]

    @timeframe as varchar(15)

    as

    if @timeframe = 'userdefined'

    ?????????

    end

    else

    if @timeframe = 'YTD'

    select

    'DefaultStartDate' = (select cast( '01/01/'+ cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4)) as datetime)),

    'DefaultEndDate' =

    case when cast(DatePart(mm,getdate()) as int) = 1 then

    dateadd(yy,1,dateadd(dd,-1,cast(cast(DatePart(mm,getdate()) as varchar(2))

    + '/01/'+ cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4))as datetime)))

    else

    dateadd(dd,-1,cast(cast(DatePart(mm,getdate()) as varchar(2))

    + '/01/' + cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4))as datetime))

    end

    if @timeframe = 'monthly'

    select 'DefaultStartDate' = (select cast(cast(DatePart(mm,dateadd(mm,-1,getdate())) as varchar(2))

    + '/01/' + cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4)) as datetime)),

    'DefaultEndDate' = case when cast(DatePart(mm,getdate()) as int) = 1 then

    dateadd(yy,1,dateadd(dd,-1,cast(cast(DatePart(mm,getdate()) as varchar(2))

    + '/01/' + cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4))as datetime)))

    else

    dateadd(dd,-1,cast(cast(DatePart(mm,getdate()) as varchar(2))

    + '/01/' + cast(DatePart(yy,dateadd(mm,-1,getdate())) as varchar(4))as datetime))

    end

    if @timeframe = 'weekly'

    select

    'DefaultStartDate' = DateAdd(dd,-6+-DatePart(weekday,getdate()),convert(varchar(11),getdate(),101)),

    'DefaultEndDate' = DateAdd(dd,-DatePart(weekday,getdate()),convert(varchar(11),getdate(),101))

    I have a second stored procedure that I tried calling which has the input variables of @start and @end. I'm having little luck with that.

    Can someone please help me figure out what belongs in the ????????? area?

    Thanks in advance,

    Michele

  • January 13, 2009 at 2:08 pm

    #926235

    You could theoretically have 2 additional params like @start, @end of type datetime that are defaulted to NULL.

    Then you could do something like

    if @timeframe = 'userdefined'

    SELECT DefaultstartTime = @start, DefaultEndtime = @end

    end

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • January 13, 2009 at 2:12 pm

    #926239

    Also, it seems like your date calculations are starting to get rather complex.

    For what you are trying to do there are some much more efficient ways...

    Perhaps the following may shed some light on some less complicated, easier to maintain and more flexible date manipulations...

    http://www.databasejournal.com/features/mssql/article.php/3076421/Examples-of-how-to-Calculate-Different-SQL-Server-Dates.htm

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • January 14, 2009 at 5:29 am

    #926483

    Thanks, but this won't work for me. I'm using the stored procedure to populate the dates in Reporting Services & having the NULLs doesn't allow the user to select anything.

  • January 14, 2009 at 5:30 am

    #926484

    Thanks for the link to the cleaner, easier date computations. I inherited the code & since it works, I was happy to leave it alone. 🙂

  • January 14, 2009 at 8:02 am

    #926578

    So If I'm understanding all of this correctly here's your scenario...

    You have a report, you want to give the user the ability to choose a parameter @timeframe. This Parameter can be Monthly, Weekly or UserDefined.

    If the Param is UserDefined you want to prompt for 2 additional date values...

    Correct?

    The only way I see this working correctly for you is as such...

    Create 3 Parameters, Timeframe, StartDate and EndDate in your report. You'll also need to make certain that Timeframe has a higher precedence than Start and End.

    Then create a new Dataset called getDates

    have it execute your stored procedure as is...

    In the Start and End Parameters... Do Not allow a Null Value... Populate (Default) them with the results of the getDates Dataset.

    this should pre-populate them If monthly,Weekly etc is selected, and they will be NULL is UserDefined is Selected. This will then force your users to modify them to appropriate values before they can run the report...

    Will that work for you?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • January 14, 2009 at 9:21 am

    #926675

    Yes, you've summarized my issue precisely. The problem with the current methodology is that in order for the dates to update when you select a different timeframe, I need to put the default dates into the 'Available Values' as well as the 'Default Values'. When this happens, every timeframe but the one for "userdefined" works fine.

    I think I know what I need to do. I need to set up the stored procedures so that they work as cascading parameters. The trick will be that there aren't any tables to pull from. If I figure it out, I'll post it.

    Thanks again.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply