SQL SERVER REPORTING SERVICE WITH ASP.NET MVC ~ Mondanta

SQL SERVER REPORTING SERVICE WITH ASP.NET MVC

SQL Sever Reporting Service (SSRS) has a nice set of features and one of them is URL Accesswhich allows you to render reports directly to the response stream.

URL access of the report server in SQL Server Reporting Services (SSRS) enables you to send commands to a report server through a URL request.

A simple url which will return a report with no parameters would look like the following:

http://DOMAIN/ReportServer/Pages/ReportViewer.aspx?%2fXReports%2fNAME&rs:format=FORMAT&PARAMETERS

Let's examine each bold url property in the previous url example.

DOMAIN

IP address or domain of your report server.

NAME

The name of your report without the extension.
Obviously Sharepoint has specific needs so you need to include the *.rdl extension.

For SharePoint integrated mode, it is the fully qualified URL of the item in the SharePoint library, including the item extension.

FORMAT

The format in which to render a report.
There are several formats you can use, including: HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, and NULL, but don't expect great results if your report isn't neatly made for the specified format you would like to generate. Read more about the exports formats here.

Using PDF will mostly give you best results because it's a Hard page-break renderer extensionwhich means it will maintain the report layout and formatting.

PARAMETERS

To pass parameters you simply include the parameter name, an equal sign, and the value of the parameter. You don't need to prefix parameters like the URL Access Parameter which are used to configure the look and feel of your reports.

http://DOMAIN/ReportServer/Pages/ReportViewer.aspx?%2fXReports%2fNAME&rs:format=FORMAT&dateFrom={0}&dateTo={1}&id={2}

NOTE

To pass a null value for a parameter, use parameter:isnull=true.
Beware the Prompt User property because if it's not selected in Report Manager you won't be able to pass a value for that report parameter within a URL.

By now you probably imagine how simple would be to write a simple MVC action which will generate report and show it to the user as *.pdf.

public async Task<FileStreamResult> GenerateReport()

{

    CredentialCache credentialCache = new CredentialCache();

    credentialCache.Add(new Uri("http://domainORipaddress"), "NTLM", new NetworkCredential(

        ConfigurationManager.AppSettings["username"],

        ConfigurationManager.AppSettings["password"]

    ));

    Stream report = null;

    using (var httpClient = new HttpClient(new HttpClientHandler { Credentials = credentialCache }))

    {

        httpClient.Timeout = TimeSpan.FromMilliseconds(Timeout.Infinite);

        report = await httpClient.GetStreamAsync("reportUrl");

    }

    var contentDisposition = new ContentDisposition

    {

        FileName = "Report.pdf",

        Inline = false

    };

    Response.AppendHeader("Content-Disposition", contentDisposition.ToString());

    return File(report, "application/pdf");    

    //or use

    //Response.AppendHeader("Content-Disposition", String.Format("attachment;filename=\"{0}\"", reportName));

    //return File(reportPath, MediaTypeNames.Application.Pdf);

}

view raw GenerateReport.cs hosted with ❤ by GitHub

You can use FileContentResult, FilePathResult, or FileStreamResult.

The code above is just a simple example but you can just as easily add more functionality: automatic task that generates your reports, saves them to disk with custom filenames, logs everything in your database etc.

string baseDirectory = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();

string userDirectory = Path.Combine(BaseDirectory, "userGuid");

if (Directory.Exists(userDirectory) == false)

{

    Directory.CreateDirectory(userDirectory);

}

string reportFullName = "reportName";

string reportFullNameSanitized = String.Join(" ", reportFullName.Split(Path.GetInvalidFileNameChars(), StringSplitOptions.RemoveEmptyEntries)).TrimEnd('.');

string reportFullPath = Path.Combine(userDirectory, reportFullNameSanitized);

using (var fileStream = File.Create(reportFullPath))

{

    report.CopyTo(fileStream);

}

Mondanta

Thursday, May 7, 2015