SQL Reporting Services 2005

1
SQL Reporting Services 2005
2
Overview

• SSRS includes all the development and management
  pieces necessary to publish end user reports in
• HTML
• PDF
• Excel
• CSV
• Originally released as a SQL 2000 separate add on
  that could be downloaded from the web, all the
  Reporting Server pieces are now bundled in SQL
  2005.

3

• The Reporting Services Architecture
• Reporting Services has a quite complex
  architecture. It includes
• Development tools
• Administration tools
• Report viewers.
• There are a number of ways to get to Reporting
  Services programmatically, including SOAP and WMI
  interfaces.

4

• Report Server is the core engine that drives
  Reporting Services.
• Report Manager is a Web-based administrative
  interface for Reporting Services.
• Decides when the reports will be scheduled to run
• Maintaining the user profiles on the report
  server.
• View or search certain reports.
• Site property configuration and folder management
  in the report server. 
• Report Designer is a developer tool for building
  complex reports.
• Report Builder is a simplified end-user tool for
  building reports.
• The Report Server database stores report
  definitions.
• Reports themselves can make use of data from many
  different data sources.

5
Developers use two GUI tools for creating reports

• Report Designer
• Graphical tool hosted within the Microsoft Visual
  Studio IDE.
• Report Designer provides a tabbed windows for
  Data, Layout, and Preview that allow you to
  design a report interactively. You can add
  datasets to accommodate a new report design idea,
  or adjust report layout based on preview
  results. Also, it provides query builders, an
  Expression editor, and wizards to step you
  through the process of creating a simple report.
• Report Builder
• Provides a simpler user interface for creating ad
  hoc reports, directed primarily at business users
  rather than developers.
• Report Builder requires a developer or
  administrator to set up a data model before end
  users can create reports.

6

• Installation
• The SSRS installation will create two new IIS
  sites,
• Reports
• Report Server
• The Reports physical directory is located at
• C\ Program Files \Microsoft SQL Server
  \MSSQL.3 \Reporting Services \ReportManager.
• The Report Servers directory can be found at
• C\ Program Files \Microsoft SQL Server
  \MSSQL.3 \Reporting Services \ReportServer.
• The Reports virtual directory contains the Report
  Manager Web application. You can access using
  //your server name/Reports

7

• Viewing Reports
• Searching and browsing for specific reports
• Configuring report viewing security
• Creating schedules and subscriptions
• Launching Report Builder for ad hoc reports

8

• The other virtual directory, Report Server, is a
  web service for requesting reports without going
  through the Report Manager GUI. By passing
  arguments in the http query string, reports and
  options can be requested. Browsing without any
  arguments should generate the web page shown
  below.

9
BIDS

• Actual report creation is done in a drag and drop
  application called Business Intelligence
  Development Studio (BIDS). BIDS is located in the
  Windows program group "Microsoft SQL Server
  2005," "SQL Server Business Intelligence
  Development Studio." The Business Intelligence
  Development Studio allows for easy creation of
  data sources and report formatting.
• SQL Server Reporting Services can be used to
  create end user reports in several different
  formats including HTML, PDF, and Excel.

10
BIDS contd..

• The SQL Server Business Intelligence Development
  Studio is a Visual Studio tool for creating
  reports. Reports created on your local machine
  using BIDS can be deployed to any Reporting
  Server.

11
Report Server Project Wizard
12
(No Transcript)
13
(No Transcript)
14
(No Transcript)
15
Create a Reports Project
16
(No Transcript)
17
Add report to solution
18
(No Transcript)
19
Layout Tab Drag and drop report items and
design report format
20
(No Transcript)
21
Expression
22
Expression Examples in Reporting Services

•  

Date Functions - Use Visual Basic functions to
provide date information in your report. The
following expression contains the Today function,
which provides the current date. Today() The
DateAdd function is useful for supplying a range
of dates based on a single parameter.
DateAdd(DateInterval.Month, 6,
Parameters!StartDate.Value) The following
expression contains the Year function, which
displays the year for a particular date.
Year(Fields!OrderDate.Value)
23

• String Functions
• You can use Visual Basic functions to manipulate
  strings in your report.
• You can combine more than one field by using
  concatenation operators and Visual Basic
  constants. The following
• expression returns two fields, each on a separate
  line in the same text box.
• Fields!FirstName.Value vbCrLf
  Fields!LastName.Value
• You can format dates and numbers in a string with
  the Format function. The following expression
  displays values of
• the StartDate and EndDate parameters in long date
  format.  
• Format(Parameters!StartDate.Value, "D") "
  through "   Format(Parameters!EndDate.Value,
  "D") 
• Right(Parameters!User.Value, Len(Parameters!User.
  Value) - InStr(Parameters!User.Value, "\"))

24

• Conversion Functions
• You can use Visual Basic functions to convert
  data types as needed in your report.
• CStr(Parameters!MySelection.Count)

25
_at_Parameters

• After the user selects a set of values, the
  report processing engine constructs the correct
  SQL. The author can also specify a set of default
  values for the parameter. Developers can
  programmatically pass multiple parameter sets via
  URL Access or the Web Services API.

26
Points to note

• Analyze the needs of the users so you know what
  fields they are going to need in their reports. 
• Providing too little information renders the
  reporting service useless, but providing too much
  information could eventually cause performance
  issues as more users access reports.
• Ensure you have the appropriate referential
  integrity in place.  Having a series of reports
  running on a poorly normalized database or poorly
  designed Analysis Services cube will cause
  additional headaches.