SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameter Startdate, Enddate validation


Parameter Startdate, Enddate validation

Author
Message
sql.pravin
sql.pravin
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 72
Hi all,

I'm working on a ssrs report with parameters startdate and enddate
@startdate : default to 1st of previous month
@enddate : default to last day of previous month

User can change the startdate,enddate

Now when user select
case 1) @startdate < @enddate ( ex: startdate: 10/1/2010, enddate: 10/11/2010 this is fine )
case 2) @startdate > @enddate (ex: startdate: 10/31/2010, enddate: 10/1/2010 this should not happened)

The user should not be able to select values as in case2
or
If he select as in case2, then we should prompt/warn the user (msg -"startdate should not later than enddate")

I tried the following solution:

report->report properties->code

step1)
Function CheckDateParameters(@StartDate as Datetime, @EndDate as Datetime) as Integer
Dim msg as String
msg = ""
If (@StartDate > @EndDate) Then
msg="Start Date should not be later than End Date"
End If
If msg <> "" Then
MsgBox(msg, 16, "Report Validation")
Err.Raise(6,Report) 'Raise an overflow
End If
End Function

step2)
created a hidden parameter "@validate" and set its default to
=CODE.CheckDateParameters(@StartDate.Value,@EndDate.Value)
It worked in BIDS (My system) but failed on QA

Please help me in solving this issue
Thanks in advanceSmile

-pravin
Daniel Bowlin
Daniel Bowlin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14842 Visits: 2629
Make the available values for your @EndDate based on a query that constrains the dates to > @StartDate
only4mithunc
only4mithunc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3784 Visits: 2803
Hi that is an alternative.

I have tried using the code itself , it is working but still Im not satisfied... :-) the code is below. As you told I have used a hidden parameter then from there I called the function.

But problem is because of the Err.Raise(6,Report) the return value is not a string type so that the parameter is not getting the value, ie the validation of the hidden paramter is not satisfied and so the report wont run this is what we need.

But the problem is it will show a message as the validation failed for the parameter... :-(

Can any experts help on this...?



Public Function MyFunction(S as Datetime,E as Datetime) as String
Dim msg as string
msg=""
if(S>E) then
msg = "Start Date Should not be greater than End Date"
end if

If msg <> "" Then
MsgBox(msg, 16, "Report Validation")
Err.Raise(6,Report) 'Raise an overflow
end if
Return msg
End Function



You can see this if you call the function from a text box rather than the hidden parameter, but there the report will run even though it is checking the Start date and End Date...

Thanks & Regards,
MC
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search