Introduction
Microsoft SQL Server 2008 Reporting Services provides a complete, server-based platform designed to support a wide variety of reporting needs enabling organizations to deliver relevant information where needed across the entire enterprise.
Top New Features
• Design reports with any structure by using the new unique layout capabilities of Flexible Report Layout
• Benefit from enhanced performance and scalability to reach all users throughout the enterprise
• Render reports into Microsoft Office Word format
• Integrate Reporting Services with Microsoft Office SharePoint Services for central delivery and management of business insight
• Create reports with richly formatted text
• Display data graphically with enhanced visualization capabilities
This article describes the features like create report , deploying on report server, access report from aspx page and create a chart graph based report. Code snippets are given wherever necessary.
Section 1 – How to create Reports from Business Intelligence Project
Open Microsoft Visual Studio 2008 and create a new project as “Report Server Project”
Figure 1
The solution shows two folder as Shared Data sources and Reports.
Figure 2
Right click on Shared data sources used to create a new data source. This data source connects to the database for data fetch.
Give name to Data source, click on Edit to provide Connection String Details. The extension of data source is .rds
Figure 3
Right click on Reports Folder to add a new report.
The report file will have.rdl as extension
Figure 4
There will be two menus on the left hand side. One is tool box with set of tools that are used in placing controls in the report design screen.
Second one is report data showing parameters,built-in fields(execution time,report header,report footer etc) and images. Go to View -> Report data to see these items. Report will have two tabbed sections as Design and Preview.
Design is the panel where controls (lables,text boxes,tables etc) are placed.
Preview is the section where report can by seen in run mode which appears exactly the same in web page or in report server.
Figure 5
Click on “New” above the reports menu to use data source. On the page select shared data source and select the data source already created.
Figure 6
Right click on data source created and create a data set. The data set is typically a text , stored procedure or table where data is fetched from database to view the results in preview panel.
Figure 7
Open Tool Box and drag and drop the Table control on design page.
From report data menu drag and drop field from dataset onto table cell.
Figure 8
Click on preview panel to view the results as given below. Preview panel will have default features like paging, print , export features.
Figure 9
Section 2 – Report Design Features
To provide color to table header , select each column header cell and right click to open tablix properties.
Select Fill , to chose color to field header. Font , Border , visibility will have their parameters for design.
Figure 10
To allow customized sorting on any column header select interactive sorting and choose the field to sorting.
Figure 11
Report appears as given below
Figure 12
To allow column headers repeat on each page follow the below steps
1) Select advance mode on corner click in column groups section
2) Select “static” row just below the “row groups” section and press F4 to open properties. Set “Report on New Page” to true.
Figure 13
Section 3 – How to deploy Report on Report Server
Open reports server configuration manager from start menu
All programs -> microsoft sqlserver 2008 -> configuration tools -> Reporting Services configuration manager
Check report server is running. Check report manager URL , click on to open the link.
Figure 14
Copy the link and right click on .net solution and view properties.
Paste the link (replace IP address by Hostname) in targetserverURL box.
Right click on solution and click on deploy.
After deploy is successful. Open the report manager link in browser again.
You will observe new solution as “ReportSol” in web page.
Figure 15
Click on Report Sol to see the links for all the reports.
Figure 16
Click on report to view the report
Figure 17
Once all reports are developed , you can copy the .rds , .rdl files in a folder in Report Server system.
Open reportserver URL to create data source and upload each report by upload file link in web page.
Set permissions for users who wish to see the reports on their browser as
Figure 18
Section 4 – Access Reports from a .Net application
Configure report web server url and path in web. Config file. This is to avoid providing values in every aspx page.
<add value="http://172.25.152.95/ReportServer" key="ReportServerUrl" />
<add value="/ReportSol" key="ReportPath" />
Add following references in the solution
Figure 19
Create a new form frmRpt to show report on aspx page. Open markup and add a table with show,reset,close buttons.
Create another table to place rptview control to display report
<table width="100%">
<tr>
<td>
<rsweb:ReportViewer ID="rptview" runat="server" ProcessingMode="Remote" Width="100%">
<ServerReport ReportServerUrl="" />
</rsweb:ReportViewer>
</td>
</tr>
</table>
Open frmRpt source code to add the code to open a report
Add following 2 namespaces in the namespace section on top of the frmRpt.aspx.cs file
using System.Configuration;
using Microsoft.Reporting.WebForms;
Open frmRpt source code to add the code to open a report
Add following 2 namespaces in the namespace section on top of the frmRpt.aspx.cs file
try
{
string strReportPath = string.Empty;
rptview.ShowCredentialPrompts = false;
rptview.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
strReportPath = ConfigurationManager.AppSettings["ReportPath"].ToString() + "/rptBasic";
// Get Report server,Report path from web.config
if (ConfigurationManager.AppSettings["ReportServerUrl"] != null)
{
rptview.ServerReport.ReportServerUrl = new System.Uri(ConfigurationManager.AppSettings["ReportServerUrl"]);
}
if (strReportPath != null)
{
rptview.ServerReport.ReportPath = strReportPath;
}
rptview.ShowParameterPrompts = false;
rptview.ServerReport.Refresh();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
Run the web application and click on Show button to view the report as given below. For better design place the upper controls and below rptview control in <asp:panel>. In the reset code button make the rptview panel visibility false.
Section 5 – Reports with parameters
Create a stored procedure which takes parameters. Example code given below.
CREATE PROCEDURE [dbo].[USP_USER_MONTH_YEAR](
@PFROMYEAR INT = -1,
@PTOYEAR INT = -1,
BEGIN
Procedure code here
END
Add a new report to the solution , create a dataset and select query type as stored procedure.
Figure 21
Preview of report shows as report generation as per values given for parameters
Figure 22
.Net code for parameters passing and results display in reports
Add drop down controls to select month and year in the upper table which has show,reset,close buttons present.
Report code in show report button should be
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
pnlInvoice.Visible = true;
ReportParameter[] parm = new ReportParameter[3];
parm[0] = new ReportParameter("PFROMYEAR", ddlYearFrom.SelectedValue.ToString());
parm[1] = new ReportParameter("PTOYEAR", ddlYearTo.SelectedValue.ToString());
parm[2] = new ReportParameter("PFROMMONTH", ddlMonthFrom.SelectedValue.ToString());
rptview.ShowCredentialPrompts = false;
rptview.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
strReportPath = ConfigurationManager.AppSettings["ReportPath"].ToString() + "/UserReport";
// Get Report server,Report path from web.config
if (ConfigurationManager.AppSettings["ReportServerUrl"] != null)
{
rptview.ServerReport.ReportServerUrl = new System.Uri(ConfigurationManager.AppSettings["ReportServerUrl"]);
}
if (strReportPath != null)
{
rptview.ServerReport.ReportPath = strReportPath;
}
rptview.ServerReport.SetParameters(parm);
rptview.ShowParameterPrompts = false;
rptview.ServerReport.Refresh();
txtStaffName.Text = hdnStaffName.Text;
}
catch (Exception oEx)
{
ProcessUnhandledException(oEx);
}
}
Reports display using Charts
This report shows Unit wise Staff details (top 3 units in descending order of staff count)
Query joins two tables Unit , Staff by Unit ID as related column.
Create a new report as rptgraph in the same solution. Add datasource , select shared data source.
Create a new report as rptgraph in the same solution. Add datasource , select shared data source.
Add new dataset to datasource and give the query that fetches top 3 records of highest staff count in a Unit
Note : take your own example with Emp,Manager tables or any.
Figure 23
Drag and drop chart control on report design place
Figure 24
Drag and drop staff id on to data fields category and unit information on data category area.
Figure 25
Right click on the chart control and select “show data labels”
Figure 26
Run the preview to view the report
Hope you liked this article. Let me know your feedback.
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.