Filter report parameter valid values based on parameter dependencies

Question

I'm working on a ASP.net application which allows users to select an SSRS report from a dropdown list, fill in report parameters and then view the report in ReportViewer on the next page. Some of the parameters have valid values, which I get from the RDLC file using code below:

private ValidValue[] GetParameterValidValues(string parameterName, string reportDirectory)
{          
    ServerReport serverReport = new ServerReport();
    string reportServerUrl = Application["ReportServerUrl"] as string;

    serverReport.ReportPath = reportDirectory + lbReports.SelectedItem.Value;
    serverReport.ReportServerUrl = new Uri(reportServerUrl);

    ReportParameterInfo reportParameter = serverReport.GetParameters()[parameterName];
    ValidValue[] validValues = reportParameter.ValidValues.ToArray();

    return validValues;
}

These values are then added to dynamically created dropdown lists on the page.

The problem is that in some cases parameter A can filter valid values of parameter B. This functionality can be observer when selecting parameters in the ReportViewer control.

My question is, how can I implement this functionality in my code?

KiwiSunGoddess KiwiSunGoddess · Accepted Answer · 2015-03-27T00:36:42

You need to populate parameter dependency values each time a value is selected that another param is dependent on. The best solution I have found is using a hash table as per this article: http://www.codeproject.com/Articles/9161/SQL-Reporting-Services-Viewer-Page-Using-SOAP-API

Where you make a call to GetItemParameters, loop through each to check if they have dependencies. each dependency is then added to the hashtable for setting values later...

    private ParameterValue[] _ParamDependenciesValues = new ParameterValue[0];

   protected ItemParameter[] GetReportParameterDependencies()
    {
        ItemParameter[] Parameters = _Rs.GetItemParameters(_ThisReportPath, null, true, _ParamDependenciesValues, null);

        if (Parameters.Length > 0)
        {
            foreach (ItemParameter p in Parameters)
            {
                if (p.Dependencies != null)
                {
                    foreach (var d in p.Dependencies)
                    {
                        if (!_Dependencies.Contains(d))
                        {
                            _Dependencies.Add(d, null);
                        }
                    }
                }
            }
        }
        return Parameters;
    }

Then, each time a parameter value is selected, you will need to set the parameter value in the array and call again the GetItemParameters with dependancy values set

    protected void SetParamValues(ItemParameter[] Params)
    {
        foreach (ItemParameter Rp in Params)
        {
            if (Rp.Dependencies != null)
            {
                foreach (var d in Rp.Dependencies)
                {
                    var MyParamValue = _ParamDependenciesValues.FirstOrDefault(c => c.Name == d);
                    if (MyParamValue == null)
                    {
                        Array.Resize(ref _ParamDependenciesValues, _ParamDependenciesValues.Count() + 1);
                        var MyNewParamValue = new ParameterValue {Name = d};
                        _ParamDependenciesValues[_ParamDependenciesValues.Length - 1] = MyNewParamValue;
                        MyParamValue = _ParamDependenciesValues.FirstOrDefault(c => c.Name == d);
                    }

                    if (_Dependencies.Contains(d))
                    {

                        if (MyParamValue != null && _Dependencies[d] != null)
                            MyParamValue.Value = _Dependencies[d].ToString();
                    }
                }
            }
        }
ItemParameter[] Parameters = _Rs.GetItemParameters(_ThisReportPath, null, true, _ParamDependenciesValues, null);
    }

This needs to be repeated until all parameters have all dependencies resolved...

Filter report parameter valid values based on parameter dependencies

1 Answers