Call SSRS Report From ASP.NET

Call SSRS Report From ASP.NET

To call SSRS Report from ASP.NET Web application, go through below steps : 1. First of all open Visual Studio 2008 then go to  New >> Website   2. Then Browse the directory where you want to save New Web Site. 3. Now In your web page add following controls:

• One ScriptManager
• One Button
• One MicrosoftReportViewer

So your web page design will look like following: 4. Now add following code on button’s click event in your .cs file :

?

1 2 3 4 5 6 7 8 9 10 11 12 13

using Microsoft.Reporting.WebForms; try {     MyReportViewer.ProcessingMode = ProcessingMode.Remote;     MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://bhushan-pc:8080/ReportServer");     MyReportViewer.ServerReport.ReportPath = "/StartSSRS/PersonAddressDetails";     MyReportViewer.ServerReport.Refresh(); } catch (Exception ex) {     Response.Write(ex.ToString()); }

5. Now Run your website and click on Get Report Button. It will show following results shown in web page : Congratulations! We successfully called our SSRS report from ASP.NET web application.

SSRS Reporting Services IE Issues

SSRS Prompt for username and password in IE 

This is for IE9 with Windows 7 operating systems
1. Open Internet Explorer (IE9) and from the tools menu, choose Internet Options
2. From the Security Tab, choose Local intranet and click on Sites
3. Click on Advanced button
4. Type in localhost in Add this website text field and click on Add button (Un-tick the box Require server verification (https:) for all sites in this zone)

There are two issues that you will find out when you finish the installation of SQL Server 2012 or SQL Server 2008 R2:
1. When you try to access Reporting Services using your IE browser, it will constantly prompt you to enter username and password.
2. After the successful entry of login details in IE, you will get the following error message:
User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed. 
1. From Start > All Programs > Internet Explorer, Right Click and choose Run as administrator
2, Enter the Reporting Services URL: http://localhost/Reports_SQLExpress
3. Running as administrator will allow you to have access to Site Settings. Click on it
4. You will see the General Site Settings page
5. Click on Security from the left side menu and choose New Role Assignment 6. Add the username or Group that you want to grant permission to and select the appropriate role
7. Now go to Home page and click on Folder Settings. Add the username or Group that you want to grant permission to the main Home page
8. All done now. You can normally open IE and it will work without an AUC error. You can also create Folders within the Home page and restrict access to specific users.

Configure the Unattended Execution Account
1.Start the Reporting Services Configuration tool and connect to the report server instance you want to configure.
2.On the Execution Account page, select Specify an execution account.
3.Type the account and password, retype the password, and then click Apply

Implementing Page Break while exporting bulk data to Excel 2003

1.     Background

The purpose of this article is how to implement Page Break and grouping while exporting bulk data to excel. It provides a solution to resolve the issue with excel 2003 limitations of 65,000 records. SQL Server Reporting services by default export the report into excel 2003 format (.xls). Excel 2003 has worksheet of size 65,536 rows by 256 columns. When we export a report which has more than 65,536 records into excel. It gives the following error:

To resolve this problem, here I am going to share some useful information and my experience  regarding how to resolve the export issue with  excel ‘s 65,536  records limitation while working in SSRS. 

2.     What is Excel 2003’s 65,536 records limitation?    

                Microsoft Excel 2003 has a limitation of 65,536 records in a sheet. As we know that SQL Server Reporting Service is one of the most popular tools for creating the reports. Many      times, a developer has to create reports for bulk data. In this case, when we go to      export the report, it gives error due to limitations of excel. Microsoft Excel is the      most   flexible format to see the report. It supports dynamic reports which have drilldown, drill             through and interactive sort. We can see the report more easily in            excel as compare to other formats. So for enterprise data level, it is necessary to resolve       the issue to get advantages of Microsoft excel with SQL Server Reporting Services.

3.     Step by Step procedure to create a report

                Here I am going to create a simple tabular report with a table that has more than 65,536                records.

a.      While creating dataset for report. Check the count of the table for which we are going to create report

The table has 121710 records.

b.     Dataset for report

                     Select query that retrieve all the records from the table:

c.      Layout design

                I have created a simple report that has three columns – CalendarYear, SalesRegion and                 SalesAmount:

               

d.     Preview the Report.

                Here I run the report for all the records available in “GlobalSales_Fact” table: You can   see the report has total 2767 pages. It has 1, 21, 710 records because we are extracting        all the records from GlobalSales_Fact table.

               

 

e.      Export the Report to Excel (Default format is xls)

               

               

               

f.       While exporting, Reporting Service gives the error:

           

Excel Rendering Extension: Number of rows exceeds the maximum possible rows     per  sheet in this format: Row Requested: 121716, Max Rows:  65536

4.     How to resolve this excel records limitation issue by using Grouping and Page Break in SSRS?

               

v  Create a Row Group: Go to Row Group pane, Right click on the Details èAdd Group è Parent Group.

v  Tablix Group window comes. Here we have to put GroupBy Expression for this new Group.

                       

v  Click on the expression button and put the expression to makes group of each 65,536 record sets.

Expression:  =CInt(Ceiling(RowNumber(Nothing)/65000))

                               

                                Click ok.

v  Row Group has been created. We can see the Group1 in  the Row Group Pane:

v  In the report Layout, you can see Group1 is shown in the Report Layout:

                               

v  Right Click on the Group1 column èDelete Columns

                               

v  Delete Column window comes, Choose Delete column only and click on:

v  Now you can see the Group1 column is deleted but Group1 is still available in the report that groups every 65,500 records:

                               

v  Go to Group1 property by right click on Group1 in Row grouping pane:

v  In the Group property, go to Page Beakè Page Break Optionè Check the box for “Between each instance of a group” and “at the end of group”.

                               

v  Right click on the Tablixè go to Tablix property. Tablix property windows comes: Check “Add Page break after” and in column header, check “Repeat header columns on each page.”

v  After implementing Page Break and Grouping, run the report and export it to excel. Now Report is exported successfully. You can see a Save window for saving the excel report:

v  Save and Open the excel.

v  In the Excel sheet, Data exported into sheet1 and sheet2. Sheet1 has 65,500 records as per the grouping we implemented and sheet2 has 56710 records.

Total records in the table = Total Records in sheet1 + Total records in sheet2

                121710                     = 65,500 + 56,710

v  Output of Sheet1:

v  Output of Sheet2:

               

5.     Conclusion

By using Page Break & appropriate grouping, we can export the bulk data report from SSRS to excel 2003. This is very useful in reporting where we have to deal with bulk data e.g data warehouse.

----------------------------------------------------End of Article---------------------------------------------------