How to Incorporate SSRS Report into MVC 5 Web Application

For traditional ASP.Net Web Form application, you can use ReportViewer control to show your SSRS report. However, for ASP.Net MVC application, you can't use the control in Razor page. Even you can add Web Form into MVC application, these Web Forms are difficult to have similar look and fell as your normal Razor page.

Fortunately, there is a ReportViewer for MVC tool which can help you to resolve this problem.

At first, you need to install the tool from NuGet. Run following command in the Package Manage Console to install the tool:

PM> Install-Package ReportViewerForMvc

Then add reference in your controller to Microsoft.Reporting.WebForms:

using Microsoft.Reporting.WebForms;

Create a new instance of ReportViewer:

ReportViewer reportViewer = new ReportViewer();
reportViewer.ProcessingMode = ProcessingMode.Remote;

reportViewer.ServerReport.ReportPath = "/AdventureWorks 2012/Sales_by_Region";
reportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/ReportServer/");

ViewBag.ReportViewer = reportViewer;

Above is for the situation that you put all your report on your SQL Server. However, every time your application access those reports, which usually required authorization, you would offer user name and password. To avoid this situation, you can put all your report to your local along with your web application. For this purpose, you need to change the report viewer's ProcessingMode.

        public ActionResult Report2()
        {
            SqlConnection connect = new SqlConnection(connectionString);
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            SqlCommand cmd = connect.CreateCommand();
            cmd.CommandText = 
               @"SELECT  DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear, SUM(FactFinance.Amount) AS SubTotalAmount
                 FROM FactFinance INNER JOIN
                     DimDate ON FactFinance.DateKey = DimDate.DateKey INNER JOIN
                     DimDepartmentGroup ON FactFinance.DepartmentGroupKey = DimDepartmentGroup.DepartmentGroupKey INNER JOIN
                     DimOrganization ON FactFinance.OrganizationKey = DimOrganization.OrganizationKey
                 GROUP BY DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear
                 Order by   DimDate.CalendarYear,  DimDate.MonthNumberOfYear";
            dataAdapter.SelectCommand = cmd;
            DataSet dataSet = new DataSet();

            connect.Open();
            dataAdapter.Fill(dataSet);
            connect.Close();
            ReportViewer reportViewer = new ReportViewer()
            {
                SizeToReportContent = true,
                Width = System.Web.UI.WebControls.Unit.Percentage(100),
                Height = System.Web.UI.WebControls.Unit.Percentage(100),
            };
            reportViewer.ProcessingMode = ProcessingMode.Local;

            reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath) + @"Reports\Report2.rdlc";
            reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", dataSet.Tables[0]));

            ViewBag.ReportViewer = reportViewer;

            return View();
        }

Note the report defined file is RDLC file not RDL file. You can just simply rename the file format. RDLC file and RDL file shared same XML Schema, and RDLC file may contains empty values.

Since the RDLC doesn't contain any data source information to increase the flexibility, you need to supply data source within your controller. In addition, you need to use same data set name as your SSRS report, for above code, DataSet1 is used as the SSRS report defined. Because the DataSet1 actually used sql script, so SQLConnection and SQLAdapter are applied here to fill the dataset.

To make the report view area variable for your browser, you need to add parameters when creating the ReportViewer class:

            ReportViewer reportViewer = new ReportViewer()
            {
                SizeToReportContent = true,
                Width = System.Web.UI.WebControls.Unit.Percentage(100),
                Height = System.Web.UI.WebControls.Unit.Percentage(100),
            };

The last is for the view. Add reference at first.

@using ReportViewerForMvc;

Using the building html helper the report in your page.

@Html.ReportViewer(ViewBag.ReportViewer as Microsoft.Reporting.WebForms.ReportViewer)

Resource:
https://reportviewerformvc.codeplex.com/wikipage?title=Getting%20Started
http://stackoverflow.com/questions/1079162/when-to-use-rdlc-over-rdl-reports

SQL Server SSRS Date Format List

Most SSRS Reports required date parameters and need to handling date format conversion. SSRS used VB style function inside their expression, which has Left, Mid, Right, Len, Trim, Ltrim, Rtrim, etc, and & to connect strings.

SSRS supports various and flexible date format which applying similar SQL style format string:

Format Code         Description                                  Example

d

Single Digit Date Format

9

d	Single Digit Date Format	9
dd	Double Digit Date Format	9
ddd or dddd	Full Day Name	Wednesday
M	Single Digit Month Format	2
MM	Double Digit Month Format	2
MMM	Three Character Month Name	Feb
MMMM	Full Month Name	February
yy	Double Digit Year Format	11
yyyy	Four Digit Year Format	2011
H	Single Digit “24 Hour” Hour Format	6
HH	Double Digit “24 Hour” Hour Format	6
m	Single Digit Minute Format	9
mm	Double Digit Minute Format	9
s	Single Digit Second Format	4
ss	Double Digit Second Format	4
tt	ante meridian (AM)/post meridian (PM) part of Time	PM
zzz	Time Zone Offset w.r.t. GMT	+05:30

How to Create Tabbed SSRS Report

You can use SQL Server Report Designer to create Tabs for your SSRS Report. The basic idea is to create one Rectangle at first, and put several TextBox inside the Rectangle. You can treat those TextBox as Tabs, and fill them with your favorite button image as background to let them looked better. The last thing for those TextBox is adding Action property for those TextBox and put your report name to link those tab to your report.

Note if your report goes to the next page, those Tabs would be disappear. To resolve this issue, simply fill the RepeatWith property for the Rectangle with available tablix for each report.

Another issue is when we output our report with pdf or excel format, we don't want to show those tabs, thus we need to hide them. To resolve this issue, add the following expression to the hidden property to each rectangle:

=Globals!RenderFormat.IsInteractive = FALSE

You can also set the Page Name property of each tablix to the name of the tab.

Source:
https://bistuffwithdigven.wordpress.com/2013/01/20/tabbed-ssrs-reports/

How to add ReportViewer Control to Visual Studio 2012 Express

ASP.Net MVC Views in fact are ASP.Net web forms without View State. To incorporate SSRS reports, a web form control named Report Viewer is needed. Since Report Viewer is a web form control, you need to add ASP.Net web form to host the Report Viewer control. You can easily add Web Form in ASP.Net MVC project for this purpose.

Report Viewer is under Visual Studio Toolbox. If you can't find the control, following steps would help you to install it.

First, install the 2012 Report Viewer Runtime, which is a free download on Microsoft's website:
http://www.microsoft.com/en-us/downl....aspx?id=35747

Second, open Visual Studio 2012 Express, go to Tools, and click Choose Toolbox Items.

Third, scroll down the list, you would find 2 ReportViewer control, one is for WebForms,  and the other is for WebForms. Both of them are version 10.0.0.0. You need to click on the Browse button to find the version 11.0.0.0 you just installed.

Navigate to the ReportViewer Runtime you just installed and select Microsoft.ReportViewer.WebForms.dll.

Now Report Viewer control has been added into Toolbox.

Source:
https://www.nuget.org/packages/Microsoft.ReportViewer/
http://stackoverflow.com/questions/6144513/how-can-i-use-a-reportviewer-control-in-an-asp-net-mvc-3-razor-view
http://blogs.msdn.com/b/sajoshi/archive/2010/06/16/asp-net-mvc-handling-ssrs-reports-with-reportviewer-part-ii-deployment-challenges.aspx
http://forums.asp.net/t/1963101.aspx?SSRS+Report+Viewer+in+MVC4
http://dotnetspeak.com/2012/02/using-ssrs-in-asp-net-mvc-application
http://stackoverflow.com/questions/6144513/how-can-i-use-a-reportviewer-control-in-an-asp-net-mvc-3-razor-view
http://stackoverflow.com/questions/15208437/how-can-i-use-a-reportviewer-control-with-razor
https://www.packtpub.com/books/content/mixing-aspnet-webforms-and-aspnet-mvc

https://msdn.microsoft.com/en-us/library/ms251661.aspx

http://www.vbforums.com/showthread.php?717175-How-to-use-Report-Viewer-with-Visual-Studio-2012-Express

http://www.codemag.com/article/1009061http://www.codemag.com/article/1011131

http://blogs.msdn.com/b/sajoshi/archive/2010/06/16/asp-net-mvc-handling-ssrs-reports-with-reportviewer-part-i.aspx

How to build SSRS Drill Down Report using BIDS

A drill down report is very useful for tables containing layered data. The report will be displayed with the top layer at first, and those layer can be expanded later to show more detailed data.

This is the design of the drill down report:

I used AdventureWorksDW2012 as my source DB. You can search Google to get the free database. Following sql script is used to get the dataset:

SELECT  DimOrganization.OrganizationName,
    DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear,
    DimDate.EnglishMonthName, DimDate.MonthNumberOfYear,
    SUM(FactFinance.Amount) AS SubTotalAmount
FROM  FactFinance
    INNER JOIN DimDate ON FactFinance.DateKey = DimDate.DateKey
    INNER JOIN DimDepartmentGroup ON FactFinance.DepartmentGroupKey = DimDepartmentGroup.DepartmentGroupKey
    INNER JOIN DimOrganization ON FactFinance.OrganizationKey = DimOrganization.OrganizationKey
GROUP BY DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName,       DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear

You should add at least 2 columns with one numeric column of SubTotalAmount for the report when you create a tabular report following the report wizard.

Next, right click on the data part of the above report design area, and choose Add Group, and Parent Group.

A Tablix Group window will popup, choose CalendarYear. Note you need to check "Add group header" to show the Group Name.

After Calendar Year Group column has been added, you can choose Organization Name and SubTotal Amount Column and right click on them to add another Group for English Month Name. Once again you need to check "Add group header" to show the Group Name. This will add one row for the group header so which would be very useful to choose the group during the design phase.

Applying same way you can add Group Organization Name and Department Group Name. You need to check "Add group header" to show the Group Name every time.

Now you need to do hide/show trick. Go to Visual Studio with BIDS menu, choose REPORT, and click View, and check Grouping.

Following Row Groups Info will appear at the bottom of Visual Studio.

Right click on the Group you want to hide, English Month Name, Choose Group Properties. A Group Properties popup window will appear. Choose Visibility on the left pane, and choose Hide. Check "Display can be toggled by this report item:" , and choose Calendar Year.

You can do same for the following 2 groups.

Now the last step is to add Total. Right click on the  Calendar Year Group, choose Add Total, you can add total for all years. Right click on the English Month Name, choose Add Total, you can add Total for one year.

Note, Group would be ordered by name by default, so August would be the first month always. This is not what we want. On the Row Groups Info, choose English Month Name, right click on the Group Properties. Group Properties popup window will appear. Choose Sorting on the left pane. and Choose MonthNumberOfYear column. Then your Month Group will be in the right order.

The final design would be this:


This would be how the report result finally look like.

SSRS Report Is Blank Under Chrome and Safari

Under Chrome and Safari, SSRS report would just show parameter part but not report content, while under IE, report worked fine.

This error happened because Chrome and Safari render overflow:auto in different way than IE.

SSRS HTML is QuirksMode HTML and depends on IE 5.5 bugs. Non-IE browsers don't have the IE QuirksMode and therefore render the HTML correctly

The HTML page produced by SSRS 2008 R2 reports contain a div which has overflow:auto style, and it turns report part invisible
<div id="ctl31_ctl10" style="height:100%;width:100%;overflow:auto;position:relative;">...</div>

The HTML page produced by SSRS 2012 used ct130_ct109 instead.

Solution:

1. Find ReportingServices.js, for SSRS 2008, by default it is in the directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js. For SSRS 2012, by default, it is in the directory  C:\Program Files\Microsoft SQL Server\MSRS11.xxx\Reporting Services\ReportManager\js\ReportingServices.js, while xxx is the database name. 

2. For SSRS 2008, add following js code to above file:

//Fix to allow Chrome to display SSRS Reports
function pageLoad() {    
var element = document.getElementById("ctl31_ctl10");
if (element) 
{
    element.style.overflow = "visible"; 
} }

For SSRS 2012, add following js code to the file:

//Fix to allow Chrome to display SSRS Reports
function pageLoad() { 
    var element = document.getElementById("ctl32_ctl09");
    if (element) 
    {
        element.style.overflow = "visible";         
    } 
}

Note for SQL 2008 R2 SP1, the div name is ct131_ct109 ninstead. So if above solution doesen't work, you can use Chrome "Inspect Element" function to check the javascript source to find the right div name with overflow:auto and made corresponding fix.

Source:
http://stackoverflow.com/questions/5968082/ssrs-2008-r2-ssrs-2012-reports-are-blank-in-safari-and-chrome

How to add Chart to your SSRS report

You can add charts to your SSRS reports.

A typical chart contains 3 parts: Category Groups, Series Groups, and Values. You can treat a chart like a matrix, which has columns, row, and data. Category Groups in the chart is like columns in the matrix. Series Groups in the chart is like rows in the matrix. And the values in the chart is like data in the matrix.

Right click on the white part of the report design page, and choose Chart then a default chart will add to your report. 

Right click on the chart you just added, a Chart Data window will popup so you can choose Category Groups, Series Groups, and Values. 

For example, I need to compare two years sales data month by month. I put following SQL script into the dataset:

SELECT Sum (dbo.FactFinance.Amount) AS SumCount

    ,  dbo.DimDate.MonthNumberOfYear

     , dbo.DimDate.EnglishMonthName

     , dbo.DimDate.CalendarYear

FROM  dbo.FactFinance

    INNER JOIN dbo.DimDate on dbo.FactFinance.DateKey = dbo.DimDate.DateKey

Where dbo.DimDate.CalendarYear = @Year1 OR dbo.DimDate.CalendarYear = @Year2

GROUP BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear,

    dbo.DimDate.EnglishMonthName

ORDER BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear

I set CalendarYear as Category Group, and set EnglishMonthName as Series Group, and SumCount as Value. 

Also I need to order Series Group by MonthNumberOfYear, so I right click on the EnglishMonthName under Series Group, and choose MonthNumberOfYear as following.

The report is done. The source DB is from Microsoft AdventureWorksDW2012. The result would be like this.

Source:

https://msdn.microsoft.com/en-us/library/dd239351.aspx

Deploy your SSRS reports to Report Server through Report Manager

Deploying your SSRS reports using Report Manager has various difference than using BIDS/SSDT. Its admin centered not developer centered. With BIDS, you can set data source directory and report directory within project properties. Using Report Manager, however, you need to create those directories by yourself.

• First, Go to Report Manager URL using Reporting Services Configuration Manager. e.g. http://SERVERNAME/reports.

• Second, Click the New Folder button, following screen will popup.

• Third, Fill the Name textbox, e.g. Report Manager Deployment Example and click OK. The Report Manager now displays the new folder. 

• Fourth, Click on the folder Report Manager Deployment Example to enter the folder, and then click on the above New Data Source button to create and configure your data source as follows. 

The following are the main points about creating a data source:

1. You have to enter the connection string; there is no helper dialog
2. The Connect using option selected is Windows integrated security; this means the report executes using the Windows credentials of the caller; the caller must have at least read permission on the database
3. The Credential stored securely in the report server option along with checking Use as Windows credentials when connecting to the data source is a good choice when you don't want to give the users running the report read permission on the database; rather you specify User name as DOMAIN\USERNAME and give just that account read permission on the database

• Fifth, After clicking OK to finish creating your data source, click the Upload File button to deploy a report. Fill in the dialog as shown below by navigating to the report that you want to deploy. I navigated to the folder where our BIDS project was saved and picked the file ReportDesignerExample.rdl (reports are stored in XML files with a .RDL extension): 

• After clicking OK to deploy the report, you can click on the report in the Report Manager to run it.  

Resouce:
http://www.sqlcircuit.com/2012/11/ssrs-different-ways-of-deploying-reports.html
https://www.mssqltips.com/sqlservertutorial/247/report-manager/

How to resolve SSRS Report URL Access Denied Error

During the process of report deployment, you want to publish your finished reports and thus you go to SSRS Configuration Manager, and choose Web Service URL on the left pane, and then click on the URL link on the right side. A new web browser page would popup and your report hub main page will appear. Everything worked just as expect. However, If you copy the URL link of your report main page and close the web page, and open a new web page, paste the copied URL link. Guess what would happen? You would suffer SSRS Report Service ERROR like this: "The permissions granted to user 'your domain\your user name' are insufficient for performing this operation. (rsAccessDenied)".

Why this happen? The first time worked fine because the windows account used by SSRS Configuration Manager was used to access the report main page. However, if you close the web page, and opened a new one to access the same report main page, the underline windows account, which is your own  account, will be used instead, and this user doesn't have any access right to the report. so the above error will surely happen.

How to resolve this common error?

Open SSRS Configuration Manager, and choose Report Manager URL on the left pane, click on the URL link on the right side and a new web page would popup to show your Report Manager URL.

Click on the Folder Setting on above buttons.

Click on the New Role Assignment, and enter yourdoman/yourname showed on above error, and check all available roles.

Now your account has been assigned enough roles to run the reports.

Deploy your SSRS reports to Report Server through BIDS/Report Builder/SQL Server Data Tools

After you finish your SSRS report, you need to deploy it to let other users to use it. You can use the built-in deployment features in SQL Server Data Tools (SSDT) to publish the reports to a report server.

Before you can publish a report, you must have permission to do so. Permission is determined through role-based security that is defined by your report server administrator. Publishing operations are typically granted through the Publisher role.

SQL Server Data Tools (SSDT) provides project configurations for managing report publication. The configuration specifies the location of the report server, the version of SQL Server Reporting Services installed on the report server, whether the data sources published to the report server are overwritten and so forth.

Right click on Solution of your project name upon Solution Explorer, and choose Properties, following window will popup.

By default, SQL Server Data Tools (SSDT) provides three project configurations: DebugLocal, Debug, and Release. The default configuration is DebugLocal. You typically use the DebugLocal configuration to view reports in a local preview window, the Debug configuration to publish reports to a test server, and the Release configuration to publish reports to a production server.

Right click on your project name upon Solution Explorer, and choose Properties, following window will popup.

Go to the report server URL to check whether Report server URL is working fine or not.

Right click on your project name upon Solution Explorer, choose Deploy,

To verify whether report deployed successfully or not, go to Report server URL click on the My Report folder link and run the report.

Source:
https://msdn.microsoft.com/en-ca/library/ms159270(v=sql.120).aspx
http://www.sqlcircuit.com/2012/11/ssrs-different-ways-of-deploying-reports.html