All posts by ozgur

ASP.NET, Programming, SQL Server, SSRS

How to Render an SSRS Report Asynchronously using the Async\Await Keywords in C# 5.0 with .NET 4.5

The .NET Framework 4.5 introduced a simplified approach to the task-based asynchronous programming model (which was introduced as part of the Task Parallel Library API in .NET 4.0) via the utilization of the two new keywords:  “async” and “await” in C# (Async and Await in VB.NET). For detailed information, see Asynchronous Programming with Async and Await (C# and Visual Basic)  on MSDN.

In this post, I’d like to show you how we could use these new keywords when making asynchronous calls to the SQL Server Reporting Services web service.  For the purpose of simplicity, I created a very simple Windows Forms project in Visual Studio 2012 in which I just have one button which invokes the SSRS web service in the click event handler.

First we need to make sure the target framework for the project is set to .NET 4.5 in Visual Studio 2012 (from the project properties).

Then we add a service reference to the SSRS web service. This part is a little tricky.  In the “Add Service Reference” dialog, after we put in the URL of the ReportExecution2005.asmx file of the Reporting Services into the Address box and locate the service, in the “Services” panel it should list the “ReportExecutionService” and if you expand that, it would show the “ReportExecutionServiceSoap” underneath. Here we select the Soap service. Then, after specifying a namespace for our reference (I just named it as “SSRSWebService”), we click on the “Advanced” button at the bottom which opens the “Service Reference Settings” dialog (as seen in the screenshots). In this latter dialog, we will make sure the “Allow generation of asynchronous operations” is checked and the “Generate task-based operations” option is selected. What this does is that Visual Studio will generate the async methods for the SOAP proxy class by using the Task<> return types.  Then we click “OK” and close the dialog.

HowtorenderSSRSreport_image1 HowtorenderSSRSreport_image2

Here I would like to underline one key part. In the second step we added the reference to the SSRS web service as a “service reference” (just like a WCF service), not as the legacy web reference (which was the old way of adding web service references prior to .NET 3.5 and WCF).  One other thing I want to point out is that here I am using the ReportExecutionServiceSoapClient proxy class, not the ReportExecutionService class which would have been the case had I added the ASMX reference as a legacy web reference. The interfaces exposed by SSRS differ slightly (different method signatures and different members) between when it is added as a WCF service (in this case it is the SoapClient) versus a legacy web reference.  Since my goal is to use the new .NET 4.5 features, I had to create the service reference in the new WCF way.

In my WinForms project I render the report located at the /MyReports/Report1 directory in SSRS as a PDF file. Here is the code for the button’s click event handler: Continue reading How to Render an SSRS Report Asynchronously using the Async\Await Keywords in C# 5.0 with .NET 4.5 →

ASP.NET, Excel

How to Download Files Containing Special or Non-ASCII Characters in the Filename Using ASP.NET

Credit: http://lifehacker.com/5690522/how-to-enable-non+ascii-characters-on-linuxA couple months ago, I worked with file downloads that had filenames with non-ASCII Unicode characters, as well as special characters, such as semi-colons, commas, periods, and others (@, %, +, {, }, [, ], $, #, &, =). Here is an example of a filename that has all of those characters:  日本人 中國的 ~=[]()%+{}@;’#!$_&-  éè  ;∞¥₤€   .txt    

After a couple days of online research,  I finally found a solution that worked across different major browsers like IE8, IE9 and IE10, Firefox 21, Chrome 27, Safari 5 (on Windows). These are the most up-to-date versions as of this writing. Opera mostly works fine as well; however, I noticed that it doesn’t like the space and curly bracket characters.

Without going into detail about character encodings in the HTTP headers, I’d like to mention two major pieces of information you should know that I found regarding this issue:

1. According to the section 2.3 in RFC-2183 (Content-Disposition header field), the “filename” parameter in the Content-Disposition header can only use the US-ASCII characters.

2. However, as specified by RFC-5987 and RFC-2231, the correct encoding can be included right in the header field by using the filename* syntax and then by percent-encoding the non-ASCII characters in the filename. For example, for the filename “test-€.txt,” the content-disposition header would look like the following:  attachment; filename*=utf-8”test-%E2%82%AC.txt. Here is the .NET code that I had for writing the response header:

Response.AddHeader(“Content-Disposition”, “attachment; filename*=utf-8”” + Server.UrlEncode(fileName);

However, when I tried that syntax with my test filename Continue reading How to Download Files Containing Special or Non-ASCII Characters in the Filename Using ASP.NET →

ASP.NET, Excel, OfficeWriter, SQL Server, SSRS

Comparing Excel Export Functionality in SSRS 2012 to OfficeWriter

In the latest version of SQL Server, SQL Server 2012, Reporting Services now supports the Office 2007\2010 XLSX file format (aka Office Open XML file format) in its Excel renderer. In fact that is the default format for the Excel renderer. The old renderer for the legacy Excel 2003 XLS format has been named to “Excel 2003” but it has been deprecated and is not visible in the available export options list by default. For reference see the related section in the MSDN documentation. Although SSRS 2012 has now the ability to export an RDL report into an Excel XLSX workbook, the Excel renderer still has certain limitations. In this blog post, we will discuss some of these limitations and compare them to the OfficeWriter renderer in SSRS.

Charts

Probably one of the major limitations is that charts will be exported as pictures. In the related section in the MSDN documentation it states:

“Charts, sparklines, data bars, maps, gauges, and indicators are exported as pictures. The data they depict, such as the value and member labels for a chart, is not exported with them and is not available in the Excel workbook unless it is included in a column or row in a data region within a report.”

With the OfficeWriter renderer on the other hand, the charts will be native Excel charts with live data. Let’s see the difference in the following screenshots:

“Product Sales Report.xlsx” generated using the SSRS 2012 Excel renderer (note that the chart is a picture)

Continue reading Comparing Excel Export Functionality in SSRS 2012 to OfficeWriter →

OfficeWriter, SSRS

How to display all selected values for an SSRS multi-select parameter in an Excel report

It is very common when designing reports that you may need to define a multi-select report parameter in order to give the users some filter options and generate the report based on the user-selected values. You may also want to display those selected values on the Excel report. This can easily be achieved with OfficeWriter by creating an SSRS formula that uses the required report parameter and inserting that formula into the report template through our OfficeWriter Designer add-in in Excel, as described in the following steps:

  1. Open the RDL file using the Open Report button in our add-in Continue reading How to display all selected values for an SSRS multi-select parameter in an Excel report →