Tag Archives: SQL Reporting Services

Introducing ImageAbsolutePath Feature in SqlReportViewer

The recently released service pack includes a number of enhancements to the SqlReportViewer control. One of the enhancements is the addition of ImageAbsolutePath property. This property is introduced for use in scenario involving multiple server environment.

In the initial release, ImageAbsolutePath is inferred from ImageVirtualPath. In this case, the control always assume that the SqlReportViewer and SQL Reporting Service is located on the same server. However, based on customer feedback, scenarios involving multiple server location is common in enterprise application deployment.

For example, the SqlReportViewer service is hosted on ssrs-1 server while the SqlReportingService is located on web-1. The scenario requires user to set both the ImageVirtualPath and ImageAbsolutePath properties, because the inferred absolute path from a remote location will not return a correct result. A code snippet to address such scenario would be:

<Intersoft:SqlReportViewer x:Name="ReportViewer1" 
	ReportProxyHandler="http://web-1/SqlReportViewerHandler.ashx" 
	ReportName="/Northwind Reports/SalesOrderInvoice"
	ReportServer="http://ssrs-1/ReportServer"
	ImageVirtualPath="http://ssrs-1/TempImages/"
	ImageAbsolutePath="C:\inetpub\wwwroot\TempImages" /> 

The above code snippet assumes that the report images will be stored to the C:\inetpub\wwwroot\TempImages folder on the ssrs-1 server and virtually hosted on http://ssrs-1/TempImages. In this case, you need to configure ssrs-1 as the image server by creating the TempImages virtual directory.

If opening up the SQL report server as public web server is not option, simply set the ImageVirtualPath property to the web-1 server. Everything else should remain the same. In this case, you need to configure the IIS in the web-1 server by creating a virtual directory and map it to the network path in the ssrs-1 server.

I hope this post gives you insights on the new ImageAbsolutePath feature and how it can be used along with other properties to achieve advanced deployment scenarios. Any questions or feedback are welcomed.

For the complete list of enhancements and fixes in the latest ClientUI release, please visit our support page.

Regards,
Glenn Layaar

Advertisements

Working with SqlReportViewer Parameter

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

First Look: SQL Report Viewer for Silverlight & WPF

In addition to many new exciting components such as MVVM-ready schedule view for Silverlight & WPF and phenomenal ribbon controls for all .NET platforms, the upcoming WebUI Studio volume release will also ship with two amazingly powerful components: a report viewer for SQL Reporting Services and state-of-the-art flow document viewers. In this blog post, I will focus on the report viewer and share what you can expect for the report viewer.

First of all, if you’re not familiar with SQL Service Reporting Services (further abbreviated as SSRS), I’d suggest you to take a look at the product overview here. There are a number of logical reasons why enterprise companies opted for SSRS instead of Crystal Reports or other reporting solutions. Among the reasons are the pre-bundled license (even for SQL Express edition), strong integration with SQL Server and the highly innovative data presentation format called Tablix. For more information, check out the SSRS resources here.

So now that you’ve heavily invested your report solution on SSRS, one big question that might come across your mind would be: How do I display these SSRS reports right in my apps? If you googled this question, you’d end up getting one or two similar answers. You can either create a proxy ASP.NET website to host your report, or directly targeting the native report server URL in a new browser window – look at the screenshot below.

Classic SQL Report Viewer running in browser

You see, the overall experience is not quite enticing as you need to popup a new browser window. The experience just doesn’t feel right as the reporting interface feels like entirely separated from the main application. This is particularly true for enterprise-class business applications which require seamless integration between their reporting and the main modules.

Many of our clients agree (I hope you, too) that the best and the most ideal solution is to have a report viewer that native to the Silverlight or WPF platform. That sounds to be right – and so it’s exactly what we’re working on, a report viewer that is capable to render the SSRS report exactly as designed which is native to both Silverlight and WPF platforms. Say hello to ClientUI Report Viewer for SSRS 2008.

Seamless Reporting Experiences

ClientUI’s SQL Report Viewer opens up the possibility to create a better, more integrated reporting experiences for Silverlight and WPF apps. This means that you can now display your reports right in your main apps – not on separate browser windows. Here, take a look at the same sales invoice report running seamlessly in a rich Silverlight app.

Introducing ClientUI SQL Report Viewer

Not only you get exact, pixel-perfect results for the display, you also get all the rich experiences of a ClientUI-powered viewer component. Say, the zooming feature, fit to width, printing, expandable/collapsible navigation interface, search, text selection, copy to clipboard and much more.

And if you’re wondering how the report is laid out in the SSRS designer, take a look at the following screenshot. If you compare with the results above, notice that the report viewer renders every SSRS design element precisely, from the position and dimension, to the formatting and styling.

Report Designer

More Than Just Tables

But, wait a minute, how about the support for rich data visualization such as charting and sparklines? Thankfully, it’s well supported through the extensible architecture of our SQL report viewer which enables you to create custom charting renderer. In short, you’ve the freedom to use any third party data visualization that suits you the best.

Too good to be true? Take a look at two of the reference samples below.

Report with sparklines

The above figure shows the SQL Report Viewer in action with sparklines visualization.

Report with charting

The above figure shows the SQL Report Viewer in action with a simple chart.

That’s all for this post. Hopefully my post gives you inspiration on what you can do with SQL report viewer, and how you should design the reporting solution to fit in your rich Silverlight and WPF apps.

As usual, feedback and comments are warmly welcomed. Look out for my next posts on flow document coverage, and possibly release announcement. Stay tuned!

Best,
Jimmy