Working with SqlReportViewer Parameter

Leave a reply

The latest ClientUI release comes up with a host of new amazing controls that have been awaited by many developers. One of the new controls is SqlReportViewer, which is used to display SQL Reporting Service reports in Silverlight or WPF applications.

In this blog post, I will share how to work with the report parameter features in SqlReportViewer, e.g. fill the parameter with default value, hide some parameters, and use custom validation to validate the inserted parameter value.

Consider this scenario, we have a holiday request form that a user needs to fill. The report by default requires these parameters to be filled:

  • Company Name
  • Username
  • First Name
  • Last Name
  • Start Date
  • End Date
  • Holiday Name

In this scenario, the Silverlight application will retrieve the Company Name, Username, First Name, and Last Name based on the user login information. Since these information are already predefined, SqlReportViewer has the capabilities to hide and automatically fill these parameters value.

As shown in the above image, Company Name, Username, First Name, and Last Name by default has been set programmatically. Company Name and Username is hidden as well, since we would like the value not to be modified by the user.

In order to achieve this behavior, you need to bind the ReportParameters property to a ObservableCollection<ISqlParameterMetadata> using TwoWay mode.

<Intersoft:SqlReportViewer ReportParameters="{Binding ReportParameters, Mode=TwoWay}"
x:Name="ReportViewer1" />

The default value and Visible property is modified during ObservableCollection<ISqlParameterMetadata> collection changed event, see the code snippet below.

void _reportParameters_CollectionChanged(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
    if (e.NewItems != null)
    {
        foreach (object paramObj in e.NewItems)
        {
            SqlReportParameterMetadata sqlParamObj = paramObj as SqlReportParameterMetadata;
            if (sqlParamObj != null)
            {
                switch(sqlParamObj.Name)
                {
                    case "CompanyName":
                        sqlParamObj.Value = this._companyName;
                        sqlParamObj.Visible = Visibility.Collapsed;
                        break;
                    case "Username":
                        sqlParamObj.Value = this._userName;
                        sqlParamObj.Visible = Visibility.Collapsed;
                        break;
                    case "FirstName":
                        sqlParamObj.Value = this._firstName;
                        break;
                    case "LastName":
                        sqlParamObj.Value = this._lastName;
                        break;
                }
            }
        }
    }

    OnPropertyChanged("ReportParameters");
}

public ObservableCollection<ISqlParameterMetadata> ReportParameters
{
    set
    {
        if (_reportParameters != value)
        {
            _reportParameters = value;
            _reportParameters.CollectionChanged += new System.Collections.Specialized.
                NotifyCollectionChangedEventHandler(_reportParameters_CollectionChanged);
            OnPropertyChanged("ReportParameters");
        }
    }
    get
    {
        return _reportParameters;
    }
}

For simplicity, the sample is using a static value to retrieve the default parameter value.

this._companyName = "Intersoft Solutions";
this._userName = "jdoe@intersoft.com";
this._firstName = "John";
this._lastName = "Doe";

A custom validation which does not allow StartDate earlier than EndDate has also been provided.

This behavior is achieved by using a custom SubmitParameterCommand and binding IsReportParameterError property to a boolean property with TwoWay mode. Setting IsReportParameter property to False aborts the report execution to the reporting services.

<Intersoft:SqlReportViewer Intersoft:DockPanel.IsFillElement="True" x:Name="ReportViewer1"
    IsReportParameterError="{Binding IsReportParameterError, Mode=TwoWay}"
    SubmitParameterCommand="{Binding SubmitParameterCommand}" />

During command execution, the code below will validate if the EndDate is later than StartDate. It will also set the IsReportParameter to False if the validation fail.

public ReportSampleViewModel()
{
    _submitParameterCommand = new DelegateCommand(ExecuteSubmitParameter, CanSubmitParameter);
}

public DelegateCommand SubmitParameterCommand
{
    get { return _submitParameterCommand; }
}

private bool CanSubmitParameter(object parameter)
{
    return true;
}

private void ExecuteSubmitParameter(object parameter)
{
    ObservableCollection<ISqlParameterMetadata> sqlParams = parameter as ObservableCollection<ISqlParameterMetadata>;

    if (sqlParams != null)
    {
        ISqlParameterMetadata sqlStartParam = sqlParams.Where(x => x.Name == "StartDate").FirstOrDefault();
        ISqlParameterMetadata sqlEndParam = sqlParams.Where(x => x.Name == "EndDate").FirstOrDefault();

        if (sqlStartParam != null && sqlStartParam.Value != null &&
            sqlEndParam != null && sqlEndParam.Value != null)
        {

            DateTime startDate = (DateTime)sqlStartParam.Value;
            DateTime endDate = (DateTime)sqlEndParam.Value;

            if (endDate.CompareTo(startDate) > 0)
            {
                this.IsReportParameterError = false;
                sqlStartParam.ClearError("Value");
            }
            else
            {
                this.IsReportParameterError = true;
                sqlStartParam.SetError("Value", "Start date must be earlier than End date");
            }
        }
    }
}

Here is the link to the sample project and the link to the RDL used in this sample.

Conclusion

In this post, I have discussed the report parameter feature in SqlReportViewer. The feature allows you to insert default parameters value, hide parameters, and use custom validation to validate the parameter. If you would like to learn more about SqlReportViewer, please see SqlReportViewer Overview.

As an extra note, hiding parameter feature is an enhancement in the latest SqlReportViewer build and requires the latest ClientUI hotfix.

SqlReportViewer is available in the latest 2011 R2 release which you can download here. If you have questions or feedback regarding SqlReportViewer or other ClientUI controls, feel free to post them to our community forum.

Regards,
Glenn Layaar

Leave a comment