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


SSRSh - Dynamically changing calendar parameters


SSRSh - Dynamically changing calendar parameters

Author
Message
XWebExpress
XWebExpress
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 29
Suppose that we have a report with two date parameters: StartDate and EndDate selectable by calendar-picker not through a drop-down values list.
But I need to implement a condition for these two parameters:

1. If I set StartDate>EndDate, automatically StartDate must changed to EndDate value (in the StartDate textbox)
2. If I set EndDate<StartDate, automatically EndDate must changed to StartDate value (in the EndDate textbox)

In other words, StartDate<=EndDate no matter where I setting-up these values, from StartDate textbox or EndDate textbox.
How can I implement this?
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4799 Visits: 13138
Interesting question. The only way I can think of doing it is to declare the parameter as text and then use a value list and then filter that. I'd be interested in a better answer (because this one is obviously not ideal).
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85080 Visits: 41074
I pretty much don't even know how to spell "SSRS". With that though in mind, here's how I'd do it in T-SQL. I would imagine that it wouldn't be much more difficult to do in SSRS.

--===== Here are the two parameters + a working variable.
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@Swap DATETIME
;
--===== This simulates passing the parameters
SELECT @StartDate = GETDATE()
,@EndDate = GETDATE() +10 --<<Change this number to test
;
--===== This will put the parameters in the correct order
-- if they're out of order. This is the code I'm talking about.
SELECT @Swap = @StartDate
,@StartDate = @EndDate
,@EndDate = @Swap
WHERE @StartDate > @EndDate
;
--===== This just verifies the result.
SELECT @StartDate, @EndDate
;



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
XWebExpress
XWebExpress
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 29
Hi Pietlinden,

I found a compromise solution for my problem. The StartDate and EndDate parameters must be selectable by a calendar-picker. In this case I cannot compute them (in Avaliable Values box) because the SSRS put them in a drop-down list and I do not want this. I decide that in case of StartDate>EndDate the report must run but only on EndDate value (meaning between EndDate and EndDate) and I must show an error message to the User in the parameters bar, before the report runs. So, except the StartDate and EndDate parameters I need another two parameters: XDateStart (as Date) that keep the forumla: IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value) both in Default and Avaliable values section, parameter I can provide to the DataSet, and XMessage (as Text) parmeter: IIF(StartDate>EndDate,"StartDate>EndDate, Report runs only for EndDate = "+CDATE(FormatDateTime(Parameters!EndDate.Value,FormatDateShort)),"none") that will be automatically updated at every change of the StartDate value, before the report runs. In this manner, the user see the message in parameters bar. I think is a resonable solution.
XWebExpress
XWebExpress
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 29
Hi Jeff,

SSRS = SQL Server Reporting Services
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4799 Visits: 13138
That was tongue in cheek... Jeff does everything in pure SQL.
XWebExpress
XWebExpress
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 29
I apologize if I offended you Jeff, I'm really sorry. That happens when you have too many years in SQL (me over 20, even I'm a newbie on this forum), you feel the need to enlighten anything. But even at this age in SQL issues and problems unsolved exists.

Best Regards.
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