Practical .NET

Integrating SQL Server Reporting Services Reports and ASP.NET MVC

There are so many ways to integrate an SSRS report into your page that the biggest problem you'll face is deciding which one to use. Peter runs through all the solutions he can think of, including those cases where you want to download the report as a file.

• By Peter Vogel
• 07/19/2016

Initially, it might seem that adding a SQL Server Reporting Services (SSRS) report to your ASP.NET MVC application is a relatively straightforward thing to do. After all, to call an SSRS report, all you have to do is issue a request to the reporting server's URL, incorporating into the URL's querystring the name of the report, any SSRS options and any parameters that control the output of your report. A typical request might use a URL like this:

http://MySite.com/MyReportingServer?/CustomerSales&rs:Format=PDF&customerId=A123

This particular URL is asking for the CustomerSales and passing a customerId of A123 as a parameter to the report. Because the URL includes the rs:Format=PDF option, the user will be given the opportunity to download the report as a file (other rs:Format options include Word and Excel); omitting the rs:Format option causes the report to be displayed as an HTML page.

Getting the Report with a Button Click
One solution to requesting the report is simply to use this URL in your form tag's action attribute. This example sets the form's action attribute to a URL, which requests the CustomerSales report as an HTML page (and not as a PDF file):

@Using Html.BeginForm(New With {.id="custreport",
  .action="http://MySite.com/MyReportingServer?/CustomerSales& CustomerId=A123"})

Now, when a user clicks a submit button in the form, the report will be requested and displayed in the browser. You don't have to use the Using block or even the BeginForm method to generate the form element (I've talked about how much I like the Using block with BeginForm in a previous tip).

However, there is a wrinkle here: When the user clicks the submit button, the browser is going to gather up all the input elements on the page and send those off to the report. If you have an element on the page that doesn't match a parameter on your report, then your report won't run -- you'll get a message that says you attempted to set a parameter in the report that doesn't exist (on the other hand, it's all right for your report to have a parameter without a corresponding element on the page, as long as you've given that parameter a default value). If there's a way to turn off this behavior in an SSRS report, I haven't found it.

Assuming that my CustomerSales report has a single parameter called customerId (and my Model has a property called CustomerId), then this form will work:

@Using Html.BeginForm(New With {.id="custreport",
  .action="http://MySite.com/MyReportingServer?/CustomerSales")
  @Html.EditorFor(Function(m) m.CustomerId)
End Using

This design also has the advantage of letting the user enter the customerId into the input element generated by the EditorFor method, rather than hardcoding it into the action attribute.

It's critical that the name of the input element match the name of the report parameter. In this example, I've assumed that's the case. If, however, the name of the property on the Model doesn't match the name of your report parameter, then you'll need to switch from the EditorFor method so that you can set the name attribute on your element, as this code does:

@Html.TextBoxFor(Function(m) m.CustID, New With {.name = "customerId"})

However, regardless of what names you use, the following form won't work because it includes a second input element without a corresponding parameter in the report:

@Using Html.BeginForm(New With {.id="custreport",
  .action="http://MySite.com/MyReportingServer?/CustomerSales& CustomerId=A123"})
  @Html.EditorFor(Function(m) m.customerId)
  @Html.EditorFor(Function(m) m.customerName)
End Using

The problem is worse than I've indicated here because I've omitted the submit button in these examples. That submit button is itself an input element that will be sent to the report server along with its value (the button's caption) and will need a corresponding parameter.

Getting the Report with a HyperLink
An alternative is to use an anchor tag to request the report. With an anchor tag, the only data sent to the report server is the data in the URL in the anchor tag's href attribute. This gives you complete control over what parameters you will (and won't) send and what names you assign to them.

The following code embeds the customerId parameter in the anchor tag's href attribute when the HTML is generated in the View on the Server (I've assumed that the customerId value is held in a property called CustID on the View's Model):

<a href="http://MySite.com/MyReportingServer?/CustomerSales&[email protected]">Get Customer Report</a>

Taking Control of the Process
These solutions won't handle a number of common scenarios, though. Other scenarios you might want to address include: What if you want to use an anchor tag, but the customerId can be changed after the user sees the page? What if, for consistency with your other pages, you want the user to click a button to request the report, but you have elements on the page that don't correspond to report parameters? There's also a variation on that last case: What if your page has multiple buttons, only one of which is requesting the report?

I'll start by making two assumptions: (1) that the parameter values don't change after the user sees the page and (2) the user either requests the report by clicking the only submit button on the page or clicking on an anchor tag.

Based on these assumptions, the simplest solution is to funnel your report request through an Action method in a Controller. This solution gives you complete control over what parameters you pass to the report and what names you use.

If the user is going to click a submit button to request the report, the first step in implementing this solution is to set the form's action attribute to request your report Action method in the View that requests the report. This example assumes that the code that requests the report is in an Action method called CustomerSalesRequest in a Controller called ReportController:

@Using Html.BeginForm(New With {.id="custreport",
                                .action='@Url.Action("CustomerSalesRequest","Report", 
                                                      New With {}, Request.Url.Scheme)'})
   @Html.EditorFor(Function(m) m.CustID)

Now you can populate your form with whatever additional input elements you want -- you'll pick out the data to send to the report in your Action method. In my example, I've only added a CustID element that holds the value for the customerId report parameter (with this solution I also don't have to worry about matching the element name to the report parameter's name).

If you wanted to provide the user with a hyperlink to click on to request the report, your View would contain an ActionLink like the following to request your Action method (I've added the report parameter [held in a property on the Model called CustID] to the anchor tag with the name CustID to demonstrate that I don't have to match the parameter's name here, either):

@Html.ActionLink("Get Report", "CustomerSalesRequest", New With { 
.Controller = "Report",
.CustID = @Model.CustID})

Both of these solutions would work equally well with the option to download the report as a file, but I'll look at displaying the report as an HTML page first. For this, the Action method just needs to assemble the report's URL from the data sent up from the browser and redirect the browser to that URL. While I could use any names I wanted for the elements in the View, the URL will need to use the name of the report's parameter. A typical Action method that passes a parameter called customerId with data from the browser called CustID would look like this:

Public Function CustomerSalesRequest(CustID As String) As ActionResult
  Dim url As String
  url = "http://MySite.com/MyReportingServer?/CustomerSales&customerId=" & CustID
  Return Redirect(url)
End Function

It's possible that the user will need to log in to the reporting server to get the report. However, because the Redirect method effectively turns the problem of fetching the report over to the browser to solve, if the user does have to log in, then the browser will just pop up a dialog box for the user to enter their name and password on the reporting server.

To download the report as a file is just slightly more complicated. In your Action method, you need to submit the request to the reporting server, catch the returned file, and then use the File method to download the file to the user. However, because the reporting server is accessed from your Web server, you're going to have to handle logging into the reporting server in your Action method.

The simplest set of objects for doing all this that I've found are the NeworkCredential object (for setting the username and password to access the reporting server) and the WebClient object (to actually make the request and retrieve the result). A typical action method looks like this:

Public Function CustomerSalesRequest(CustId As String) As ActionResult
  Dim url As string 
  url = "http://MySite.com/MyReportingServer?/CustomerSales&rs:Format=PDF&customerId=" + CustId

  Dim nwc As New NetworkCredential("username", "password")
  Dim wc As New WebClient
  wc.Credentials = nwc

  Return File(wc.DownloadData(url), "application/pdf")
End Function

Supporting Changing Data
It's probably more realistic to assume that the user may change the data on the page that's used to request the report. If you're using the solution where the user clicks the form's submit button to request the report, then I have good news: You've already solved this problem. The browser will send the latest data on the form back to the server for processing when the user clicks the submit button.

If you're using an anchor tag, however, you'll need to rewrite the URL in the anchor tag's href attribute each time the data changes. The first step in doing that is to assign a value to the anchor tag's id attribute and omit the URL in its href attribute. As a result, this is all you need for your anchor tag:

<a href="" id="requestReport">Get Report</a>

The next step is to wire up a JavaScript function to the change event of the input element's that holds the parameter data. In that function, you can grab the anchor tag and build the href attribute using the value from the element that fired the change event. Typical JavaScript/jQuery code to do that would look like this:

$("#CustID").change(function() {
               $("requestReport").attr("href",
                 '@Url.Action("CustomerSalesRequest","Report")' + $(this).val();}
);

Now, when the user clicks on the anchor tag, the href attribute will have been sent to a URL that includes the latest data. Of course, if the user hasn't changed the data, the anchor tag won't yet have a URL in its href attribute and will do nothing.

A Double-Duty Page
All of these solutions, however, assume that there's a single button on the page that requests the report. It's not hard to imagine a page that has two buttons: one to save the data on the page and one to generate a report based on the data on the page.

If both buttons are submit buttons, then you have two options: One is to always have the form postback to the same Controller/Action method and then detect which button the user clicked. I've discussed that option in a previous tip. Alternatively, you can have two Action methods: one to handle the report and one to handle saving the data (probably a better choice from a design point of view). To implement that solution, you'll need to create a custom ActionMethodSelector, which I discussed in an earlier column.

Alternatively, depending on which button the user clicks, you can rewrite the form's action attribute to call the right Action method. With this solution, the button that causes the form to postback doesn't have to be a submit button -- you could use any "clickable" element. A button like this would work fine:

<input type="button" id="requestReport" value="Get Sales Report"/>

In the click event function for that button, you'll need to rewrite the action attribute on your form and then call the form's submit method. The code to do that while incorporating the current value from the CustID element into the URL would look like this:

$("#requestReport").click(function (event) {
            var action;
    	     action = "@Url.Action("CustomerSalesRequest","Report", new {}, Request.Url.Scheme)";
            action = action.concat($('#CustID').val());   
            $("#custReport").attr("action", action).submit();            
        });

That covers all of the scenarios for accessing an SSRS report -- at least, all the ones that I can think of. If you can come up with another scenario, get in touch! I can do a follow-up column. Or you can just include your solution in the comments section. Whatever.