SQL Server Reporting Services
Managing Business Intelligence with Reporting Services
Contents
Install Reporting Services via the SQL Server Installation Center.
Report Services provides the infrastructure to support Ad Hoc reporting, the development of Report Models, Managed Reports that can be accessed via a Report Catalogue and the use of Embedded Reports for use by portals or custom applications.
Managed Reports are reports that are deployed to the Report Server by a report administrator for use by one or more report users. Managed Reports are developed using Report Designer, a BIDS project template. BIDS also provides a template for the development of Report Models, referred to as Model Designer
Ad Hoc reports can be developed using Report Builder, a free download from the Microsoft Download Centre, intended to provide an intuitive report authoring environment for business users. Ad Hoc reports can be stored locally and run on demand from a client PC or deployed to the Reporting Services server. Reports built in Report Builder can access Report Models developed by Report Services administrators.
Report files, built with Report Designer or Report Builder, are a type of XML file, in RDL format. RDL files can also be produced using the Report Server API, and reports can be accessed via the API using URL endpoints.
Reporting Lifecycle
Top BottomThe Reporting Lifecycle involves three phases:
- Development of reports
- Report Server management
- Providing users with access to reports
Report server management involves configuring the location, security settings and execution properties of reports. Reports are made available via the Report Server website, 'Report Manager', when the server is run in native mode. The Report Manager website does not require IIS, but uses Http.sys directly to accept requests. The default URL for report manager is: http://servername/Reports
Report Server can also be configured in integrated mode, to publish reports via a separate Sharepoint Server. In integrated mode, RS benefits from version control, workflow management and dashboard integration provided by Sharepoint.
The Reporting Services architecture requires two databases:
- The Report Server database for permanent storage of reports and report models
- The Report Server tempdb used to store session cache information and cached instances of reports
SQL Server 2008 management tools are 32-bit, but are supported on 64-bit Windows via WOW64. Report Server instances cannot run as part of a failover cluster.
RS Management
Top BottomRS Configuration Manager is used to assign service accounts, configure the URLs used by RS, create the RS databases and configure the RS Mail Service.
To switch between native and integrated mode, a new RS database must be created using RS Configuration Manager.
SQL Server Management Studio also provides an environment for configuring the Report Server. SQL Server Configuration Manager can be used to stop/start the services associated with Reporting Services. Some command-line utilities are also available.
The Report Manager website is used to organise and configure reports, control security settings and to manage report models and subscriptions.
Installation Notes
Top BottomA RS installation will require additional storage and processing resources, especially in tempdb for cached reports.
Reporting Services is available as part of SQL Server 2008 Standard edition. Native mode Reporting Services can be installed to SQL Server 2000, 2005 or 2008. Integrated mode requires SQL Server 2008. Enterprise editions provide additional features: infinite click-through; scale-out deployment; and data-driven subscriptions
Create an RS service account before installing RS.
Granting Users Access
Top BottomAccess to the Report Server is granted by assigning Roles to Windows user accounts or groups using the Report Server website. Permissions are inherited from containers, and the top level container is the 'Home' folder.
A number of predefined roles grant users the ability to perform certain tasks. Predefined roles can be viewed in SQL Management Server. First connect to the Report Server instance, and expand 'Security, Roles'. The predefined roles are:
| Role | Description |
|---|---|
| Browser | May view folders, reports and subscribe to reports |
| Content Manager | May manage content in the Report Server. This includes folders, reports and resources |
| My Reports | May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder |
| Publisher | May publish reports and linked reports to the Report Server |
| Report Builder | May view report definitions |
| System Administrator | View and modify system role assignments, system role definitions, system properties, and shared schedules |
| System User | View system properties, shared schedules, and allow use of Report Builder or other clients that execute report definitions |
The roles assigned to user accounts and groups will determine the content they see when the browse to the Report Server website. Custom roles can be added via SQL Server Management Server.
Star-Schema Data Warehouse
Top BottomThe AdventureWorksDW2008 sample database provides a star schema Data Warehouse database. A star schema design denormalises multiple tables in a transactional database into a set of fact tables and dimension tables. A fact table contains numeric columns for data and foreign key columns to dimension tables. A dimension table contains a primary key and attributes for the dimension record.
