March 14, 2019 at 10:35 am
Hi,
I've got a requirement to produce a series of reports that are exactly the same thing apart from they would be based on differnet date ranges.
For example, I've got to produce Previous Month, Previous Quater , Previous Year and Current Year to Date but also another where they can enter whatever dates they like.
Now rather than having 5 reports to maintian I want a single report with a drop down list from where the user can select which range they want.
If it were the just the predefined options I would pass the selected option back to the underlying stored procedure and let SQL Server determine the range but it doesn't work well for the custom range.
I thought about having the From and To date parmeters available in the report and pass both the selected option AND the From/To dates into my Stored Procedure but ignore the From/To unless they've passed back the "Custom Range" option.
But there are a couple of things I really don't like about that; first of all the From/To dates are always on show in the parameters bar and secondly I do like to display the values of the parameters set when I render the report.
I know for the second part I could display two different fields dependant on the selected option but if I can just get the drop down list to populate the From/To parameters then it feels like a more straightforward solution.
Ideally I'd like the Selected Option to populate the From and To parameters in the SSRS header.
Failing that I'd like the Selected Option to determine the visibility of the From/To parameters.
Perhaps there's an alternative I've not considered...
Any help really appreciated.
Regards
Giles
March 14, 2019 at 1:38 pm
To do that last, you need to be sure that the custom range selection is higher in the list of parameters than any other one, so that it will be processed ahead of the others. Then you can potentially use that value of that parameter to determine the default values for other parameters, even if you hide them.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2019 at 8:36 am
Thanks Steve,
That works with my intial selection. i.e. when I open the Report, the From and To parameters are greyed out and as soon as I select "Previous Year" or "Previous Month" they popualte correctly.
However, being clumsy, if I select Previous Year, then correct myself and change it to Previous Month the dates remain for the Previous Year.
I even created a Stored Proc to return the relevant date of the option selected and used that to populate the default value of the Parameter, but it had exactly the same result. Running a Trace against that showed the proc was only called the first time.
I need those parameters to "refresh" when the parent parameter is changed, any ideas?
Regards
Giles
March 19, 2019 at 5:15 am
I've been working from an article I've found, it works until I make the parameter Internal (the author does caveat that it might not work with Date Pickers) , I'm still posting it as I'm sure it will be a help to someone in a similar situation.
https://bpmsbi.wordpress.com/2011/04/23/ssrs-cascading-parameters-refresh-solved/
April 3, 2019 at 2:19 pm
I do this with most reports I write. I don't use the date pickers at all. I provided a parameter that is a drop down list of "Date Ranges" to select from. Usually something like: Yesterday, Week To Date, Period To Date, etc.. That returns a data set that includes a "DateStart" and "DateEnd" that is passed to the data set that get's the data for the report. The end user has a nice easy pick instead of the clumsy date pickers. Particularly nice when your fiscal calendar is something like Tuesday to Wednesday.
I also do a pair of parameters where the first is the "Fiscal Option": Days, Weeks, Periods. The second is a listing of those from the choice of the first. This makes it really easy to go grab a day from 8 days ago or a week from the current week on back. Very user friendly.
Neither of these allow the custom selection of a Start Date and End Date but I have rarely had a demand for that and usually just supply a linked report with those parameters exposed for the user.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply