Input Variables

Question

Post reply

Input Variables

msoutherland

SSC-Addicted

Points: 479
More actions
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

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

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]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

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]

msoutherland SSC-Addicted Points: 479 More actions · Answer 3

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.

msoutherland SSC-Addicted Points: 479 More actions · Answer 4

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

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]

msoutherland SSC-Addicted Points: 479 More actions · Answer 6

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.