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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Can I do this with Cascading Parameters?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

158 Posts

Posted - 07/11/2014 :  16:22:50  Show Profile  Reply with Quote
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:

  1. Having a separate report that lists all of the funding sources and their associated dates that the user would run before running this report

  2. (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).

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


  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000