SSRSh - Dynamically changing calendar parameters

  • 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?

  • 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).

  • 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)

  • 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.

  • Hi Jeff,

    SSRS = SQL Server Reporting Services

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

  • 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.

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

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