SSRSh - Dynamically changing calendar parameters

Question

SSRSh - Dynamically changing calendar parameters

XWebExpress

SSC Veteran

Points: 225
More actions
March 14, 2014 at 6:39 am

#286719

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?

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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 SSC Guru Points: 1004372 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

XWebExpress SSC Veteran Points: 225 More actions · Answer 3

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 SSC Veteran Points: 225 More actions · Answer 4

XWebExpress

SSC Veteran

Points: 225

March 17, 2014 at 12:41 am

#1697558

Hi Jeff,

SSRS = SQL Server Reporting Services

pietlinden SSC Guru Points: 63503 More actions · Answer 5

That was tongue in cheek... Jeff does everything in pure SQL.

XWebExpress SSC Veteran Points: 225 More actions · Answer 6

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.