SSRS 2008 a developer note book

Sagarvt
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4008 red flag
Rating: 4 out of 5  
 1 vote(s)

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.

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Sagarvt
Full Name: vidyasagar Tejomurtula
Member Level: Starter
Member Status: Member
Member Since: 10/9/2012 3:24:33 AM
Country: India

http://www.dotnetfunda.com
.Net Solution Architect. Enthusiastic in open source technologies, exposure to domains as Government,HR,Insurance,Digital Print Media. Interests in Pets,wild life,travelling and photography

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)