Basic of SSRS (MS SQL Server Reporting Services)
-
Basic & Standard
Definition of SSRS (SQL Server Reporting Services).
-
Download Adventure Work
Sample DB to work with reporting services.
-
Develop reports using
Business Intelligence.
Note :
Prior to working on this sample, one
should have installed Reporting Services & configure reporting services & this
article is developed using MS SQL Server 2008, VS 2008 (Business intelligence).
Basic & Standard definition of SSRS(SQL
Server Reporting Service)
- SQL Server Reporting Services (SSRS)
is a server-based report generation software system from Microsoft.
- It can be used to prepare and deliver a
variety of interactive and printed reports. It is administered via a web
interface
- Reports are defined in Report Definition
Language (RDL), an XML markup language.
- Reports can be designed using recent
versions of Microsoft Visual Studio, with the included Business Intelligence
Projects plug-in installed or with the included Report Builder, a simplified
tool that does not offer all the functionality of Visual Studio. Reports
defined by RDL can be generated in a variety of formats[1] including Excel,
PDF, CSV, XML, TIFF (and other image formats[2]), and HTML Web Archive. SQL
Server 2008 SSRS can also prepare reports in Microsoft Word (DOC) formate.
Download Adventure Work Sample DB to work with
reporting services
Step 1 : Download Adventure work DB from below
link.
http://msftdbprodsamples.codeplex.com/releases/view/4004
Step 2 : Install downloaded DB.
Step 3 : Open MS SQL Management Studio &
connect to database
Step 4 : Right click Database options, click the attach option as show below.
Step 5 : Click on the Add button & navigate
to the path where the Adventure DB is installed & select AdventureWorks_Data.mdf as
shown below.
Step 6 : Finally you should be seeing
Adventure DB in the DB list as shown. Note if you have installed Reporting
Services properly you should be seeing ReportServer DB in the DB list as shown
below. In my case I have provided SSRS database names as ReportServer_DB,
ReportServer_DBTempDB while configuring SSRS.
Develop reports using Business
Intelligence
Step 1 : Open VS 2008, New Project,
Business Intelligence Project, Report Server Project & name it as "ReportPrj".
Step 2 : Now it will show the below
screen, click next.
Step 3 : Now select New Data Source,
Name: Adventure, Type: Microsoft SQL Server & click Edit button.
Step 4 : Select Servername, appropriate
Authentication & select database as "AdventureWorks", click Test Connection. If
everything is properly selected Test connection would get succeeded as shown
below.
Step 5 : Click ok. In connection string
text area db connection string would show up. Click Next.
Step 6 : Now enter below query in the
query string text area & click next button.
Select
top 150 *
from Sales.SalesOrderHeader
Step 7 : Select tabular format, click
next.
Step 8 : Select fields from the available
fields area & click Details button, click next button.
Step 9 : Now you can select table
styles.
Step 10 : Now it asks for report name,
lets name it FirstReport & click Finish button.
Step 11 : This will generate report
"FirtReport.rdl" with two tabs in it. The tabs are "Design", "Preview".
Step 12 : To change the color of a
column in a report. Go to design tab, Select Header cell or Row Cell, go to
Properties tab & select appropriate color.
Step 13 : To view the data go to the Preview
tab & look at the data.
Step 14 : To change or modify query.
Select the Design tab, now go to menu items select View, select Report data.
Right click Dataset1 under Adventure, select dataset properties.
Change query or other properties as per
your need.
Step 15 : Finally right click in solution
explorer click on solution & select deploy.
Step 16 : After successful deployment go
to browser & open the reporting service URL. In my case it is
http://localhost/reportserve
Step 17 : Now you should be able to see
the data.
Happy coding.. Hope this helps!