Reader Level:
ARTICLE

SQL Server Reporting Services

Posted by Surya Prakash Articles | SQL Server November 29, 2011
In this article, I will discuss how to work with SSRS(SQL Server Reporting Services).
  • 0
  • 0
  • 5661

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.

csharp1.gif

Step 5 : Click on the Add button & navigate to the path where the Adventure DB is installed & select AdventureWorks_Data.mdf as shown below.

csharp2.gif

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.

csharp3.gif

Develop reports using Business Intelligence

Step 1 : Open VS 2008, New Project, Business Intelligence Project, Report Server Project & name it as "ReportPrj".

csharp4.gif

Step 2 : Now it will show the below screen, click next.

csharp5.gif

Step 3 : Now select New Data Source, Name: Adventure, Type: Microsoft SQL Server & click Edit button.

csharp6.gif

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.

csharp7.gif

Step 5 : Click ok. In connection string text area db connection string would show up. Click Next.

csharp8.gif

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.

csharp9.gif

 Step 8 : Select fields from the available fields area & click Details button, click next button.

csharp10.gif

Step 9 : Now you can select table styles.

csharp11.gif

Step 10 : Now it asks for report name, lets name it FirstReport & click Finish button.

csharp12.gif

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.

csharp13.gif

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.

csharp14.gif

Right click Dataset1 under Adventure, select dataset properties.

csharp15.gif

Change query or other properties as per your need.

csharp16.gif

Step 15 : Finally right click in solution explorer click on solution & select deploy.

 
csharp17.gif

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!

COMMENT USING

Trending up