Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Analysis Server and Reporting Services (2008)  Can I do this with Cascading Parameters?

Author  Topic  LaurieCox 158 Posts Posted - 2014-07-11 : 16:22:50 I going to try to jerry rig this with Cascading Parameters* but I am not even sure if it will be possible as I have never worked with them before. I am going to try to figure them out whilst applying them to what I am pretty sure is a non-conventional process. So I guess I am asking if what I am going to attempt to do is even possible or if there is some other way to accomplish this or not. Explanation of Problem One of the filters that a user can filter the report on is Eligibility. If they choose to filter on Eligibility there are some additional parameters they must fill in. If they choose not to filter on Eligibility then they should not fill in these additional parameters. If they do choose to filter on eligibility then the first thing they need to do is select a funding source. After they select a Funding Source then they need to select Eligibility Type. But they need to know a couple of dates (Max Negative Action Date and Max Updated Date) associated with the selected Funding Source to help* them determine what Eligibility Type they want to select: Negative Action Missing Eligibility Record Negative Action/Missing Eligibility Record *I cannot write script to determine the Eligibility Type from the dates because some of the information needed to make the decision is in the user's head. They will use the dates along with why they are running the report to determine what to select for Eligibility Type. After selecting an Eligibility Type they need to select Eligibility Month. The max month that they can select will depend on the Eligibility Type they select: If Negative Action or Negative Action/Missing Eligibility Record then Max Negative Action Date for the given Funding Source If Missing Eligibility Record Then Max Update Date for the Given Funding Source So this is how I think the parameters should work <a bunch of filter parameters that they always must select> Filter on Eligibility Drop down with Yes/No values (though I am thinking that maybe I could include Not Applicable in the Funding Source drop down and eliminate this parameter) Funding Source If Filter on Eligibility = Yes: The List of Funding Sources If Filter on Eligibility = No: Then only one item: Not Applicable (The following three parameters all cascade from the Funding Source parameter. I haven't researched it yet but is it even possible to have multiple parameters cascade from one parameter?) Max Negative Action Date (this will be a drop down but will only ever have just one item) If Funding Source = Not Applicable: Not Applicable If Funding Source Selecting: Max Negative Action Date for selected Funding Source Max Update Date (this will be a drop down but will only ever have just one item) If Funding Source = Not Applicable: Not Applicable If Funding Source Selected: Max Update Date for selected Funding Source Eligibility Type Filter If Funding Source = Not Applicable then one item: "Not Applicable" If Funding Source Selected: Then the three Eligibility Type Filters Eligibility Month If Funding Source = Not Applicable then one item: "Not Applicable" If Funding Source selected: Then list of Eligibility Months with the max Month dependent on Funding Source/Eligibility Type Filter (as described above). I am going to start working on this when I can fit it in next week. I would appreciate any feedback on whether what I am trying to do is even possible or any other solutions. Some other solutions I have thought about: Having a separate report that lists all of the funding sources and their associated dates that the user would run before running this report (this was my first choice but from what I can tell is not possible) … having an embedded report (subreport?) that displays the funding sources and their associated dates. The problem is the subreport only displays after the parameters have been entered and they need this information before they select parameters). Including the two dates in the parameter values for Funding Source (so they show up in the drop down. If the cascading parameters don't work this is one I may go to but it looks ugly to me. Laurie