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


update parameter changes as soon as another parameter


update parameter changes as soon as another parameter

Author
Message
jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
I have a parameter in vai get today's date and have another parameter that will get the date one year ago. So far so good. But I would like to click on the calendar for today's date and moved to a date yesterday for example another parameter also would change automatically?

Parameter today = CDate (Format (DateAdd ("d", -1, now ()), "MM / dd / yyyy 1:00"))
parameter year ago = CDate (Format (DateAdd ("d", -365, now ()), "MM / dd / yyyy 1:00"))
TaylorMade
TaylorMade
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 212
I am going to have to assume that the second parameter is hidden, otherwise this would appear to be near impossible (or extremely complicated), since whatever logic you use will be based on the value in the 1st parameter.

For the 2nd parm, set your default value based on the other parm's value, like so:
=DateAdd(DateInterval.Day,-365,Parameters!StartDate.Value)

That's it. The other way this could be accomplished would be to use a global variable (the value of the 1st parm again), but this method for the scenario you described is much easier.
jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
yes the other field is to stay hidden.
I put the following code = CDate (Format (DateAdd ("d", -364, Parameters! DataA.Value) "MM / dd / yyyy 1:00"))
But when I change the parameter DataA the above code does not change anything. gives the feeling that no updates
TaylorMade
TaylorMade
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 212
You do need to refresh the report after changing a parameter. To prove whether it is working or not, temporarily display the parameter fields in the body of the report until it works as desired. Good luck.
jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
But is there any command to refresh the Paramenters. Or is just to say f5 (refresh)?
TaylorMade
TaylorMade
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 212
amadeu_j, I appologize, but I just tried this and this creates a "forward depenency" error message (I thought that I have done this in the past, again, sorry for misleading you).

The best solution is to alter your SQL, basically the same way you attempted to adjust the 2nd parameter. Something like:

WHERE columName BETWEEN @Parm1Date and DATE_ADD(year, -1, @Parm1Date)

since all your really need is to work off of one date.
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