Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRSh - Dynamically changing calendar parameters Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:53 PM
Points: 5, Visits: 28
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?

Post #1551124
Posted Sunday, March 16, 2014 11:02 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 697, Visits: 4,471
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).
Post #1551575
Posted Sunday, March 16, 2014 3:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1551600
Posted Monday, March 17, 2014 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:53 PM
Points: 5, Visits: 28
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.

Post #1551648
Posted Monday, March 17, 2014 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:53 PM
Points: 5, Visits: 28
Hi Jeff,

SSRS = SQL Server Reporting Services
Post #1551651
Posted Monday, March 17, 2014 10:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 697, Visits: 4,471
That was tongue in cheek... Jeff does everything in pure SQL.
Post #1551830
Posted Monday, March 17, 2014 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:53 PM
Points: 5, Visits: 28
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.
Post #1551868
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse