Of all the various SQL Server disciplines with which I’ve worked, I find SSRS to be the messiest and most uninteresting.  The configuration is still clunky – even in 2008 – and developing reports is quite dull.

Enjoy !

Filtering Data

Dataset filters limit the data displayed on the report after retrieval from the source.  This applies to any regions or parts of a report that use that dataset.

http://technet.microsoft.com/en-us/library/dd255287.aspx

Filters can also be added at lower levels -  to data regions, tablix rows, column groups

http://technet.microsoft.com/en-us/library/ee633648.aspx

If users can filter reports using parameters, there may not also be a need to filter the dataset prior to that.

Data Regions - display rows to the user

Table: Static columns, rows expand downwards

Matrix (Crosstab): columns, rows expand; source dictates number of columns

List: Freeform

Chart

Tablix: Detail/grouped data in freeform or grid

Tabular reports: predefined column number

SSRS Installation

SQL Server Catalog: ReportServer + ReportServerTempDB on SQL Server engine

Report Server Service – report rendering, process, delivery, access, scheduling

Web Service/URL access – virtual directories (Reports – used by Report Manager interface , ReportServer – direct access to SSRS Web service)

Native Mode: IIS not required in RS 2008.  HTTP.sys is used. 

Sharepoint integrated mode: No Report Manager

SSRS supports named instances for multiple installs on same computer

Default report manager is http://computername/Reports

Named instances: http://computername/Reports_InstanceName <-- Underscore

Configuration

Authentication to SSRS is controlled inside RsReportServer.CONFIG

http://www.sqlmag.com/article/sql-server-reporting-services-ssrs/understanding-sql-server-reporting-services-authentication-141280

SMTP settings are also in RSReportServer.CONFIG

Enabling Kerberos in SSRS:

http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

Authentication Type = RSWindowsNegotiate in RSReportServer.CONFIG

The ReportServer Database stores all report-related items (Report definitions, schedules, snapshots, keys).

http://msdn.microsoft.com/en-us/library/ms156016(v=sql.105).aspx

If moving the SSRS database, both ReportServer and ReportServerTempDB should be moved.  Subscriptions, cached reports, and snapshots will be preserved.

http://msdn.microsoft.com/en-us/library/ms156421.aspx

RS config manager – create VDs, define accounts, configure DBs for RS, manage encryption settings (.SNK), scale-outs

Encryption Keys can also be managed with RSKeyMgmt.EXE

SSRS Config files (SSRS 2008)

RSReportServer.CONFIG – Authentication, SMTP, everything not listed below

RSSRVPolicy.Config: Policy Settings for the RS Web service

RSMGRPolicy.Config: Policy Settings for Report Manager

Web.Config: ASP.NET settings for RS and RM

ReportingServicesService.EXE.Config: Trace and logging for RS service

RSReportDesigner.Config: Config for R Designer

RSPreviewPolicy.Config – Security policies for server extensions

RSWebApplication.config – not used in SQL Server 2008

Some RS properties can be set via SSMS

Assemblies / Custom Code

<CODE> allows embedded code

Custom assemblies can also be referenced

To reference the same custom function in multiple reports, create a custom assembly to house the code.

By default, assemblies are granted execute inside the report

External resource access must  be modified in rssrpolicy.config

http://msdn.microsoft.com/en-us/library/ms159238(v=sql.105).aspx

Calling external code: <NameSpace>.<Function>(Fields!FieldName.Value)

Function can also be passed static values

<NameSpace>.<Function>(Fields!FieldName.Value, “X”)

Data Sources

Data Source Views abstract the underlying schema, allowing you to add calculated columns or limit access.

http://msdn.microsoft.com/en-us/library/ms170023(v=sql.105).aspx

If a user creating reports only needs access to a subset of tables or views, the data source can contain everything while the DSV limits their access to only what is required.

Shared data sources: Stored in ReportServer DB

Administered in Report Manager à Data Sources

Credential mechanisms:

Supplied by user running report (Manually entered)

Stored securely in Report Server – same credentials used by all users for that report

Windows Integrated

Credentials not required

Built-in Data Connections

http://msdn.microsoft.com/en-us/library/ms156450.aspx

Built-in data extensions include the following data connection types:

Microsoft SQL Server

Microsoft SQL Server Analysis Services – need to use these for retrieving

properties/extended properties/parameters from SSAS sources

Microsoft SharePoint List

Windows Azure SQL Database

Microsoft SQL Server Parallel Data Warehouse

OLE DB

Oracle

SAP NetWeaver BI

Hyperion Essbase

Teradata

XML

ODBC

Rendering Reports

When exporting to MS-WORD, the width limitation is 22 inches.

http://msdn.microsoft.com/en-us/library/dd283105(v=sql.105).aspx

Resolving Even or odd-numbered pages being blank/rendering errors

For the exam, reduce the width of the report.

In real life, you can also set ConsumeContainerWhiteSpace to TRUE and this may resolve the problem.

There are two reportviewer controls –

http://technet.microsoft.com/en-us/library/aa337090.aspx

Remote Processing Mode: For displaying reports that are hosted on a report server

à Workload handled by server; Scale-out possible

Local Processing Mode: Report is on computer that doesn’t have SSRS

à Report processing handled by control; data processing handled by host application

Visibility settings:

http://sqlserverpedia.com/blog/sql-server-bloggers/visibility-settings-in-ssrs-drill-down-to-showhide-report-data/

Controllable at the group, row, column, text level. Can be “Show”, “Hide”, or based on an expression.

Items that are hidden by their parent in the hierarchy can have visibility set to SHOW on their own properties or as the result of a click.

Rendering Extensions

http://msdn.microsoft.com/en-us/library/ms154606(v=sql.105).aspx

à Transform data into device-specific format

HTML, Excel, Word, CSV or Text, XML, Image, and PDF.

Setting “Visible” = FALSE will prevent rendering with that extension [RSReportServer.CONFIG]

Report Parameters

Dataset parameters are defined at dataset level, affect all data from data source; filter at query time

Report parameters at report level, users can usually modify these

Parameters can be bound to a specific dataset for drop-down selections

Multivalued parameters – must use IN clause in SQL Server and cannot be a SP call; select “allow multiple values” and user can select multiple values

Parameters can be passed through the report URL (&ListID=27)

Internal parameters are only exposed in the report definition and cannot be changed by users.  They must have a default value.

More about report parameters:

http://technet.microsoft.com/en-us/library/dd220464.aspx

Report Subscriptions

Data-driven report subscriptions retrieve dynamic subscription data at run time

http://msdn.microsoft.com/en-us/library/ms159150.aspx

Data-driven subscriptions cannot be executed if the report is using user-defined parameter values.  Expressions such as User!UserID cannot be used and must be removed from the report.

Built-in Functions

IIF syntax: IIF (Expression, Result when True, Result When False)

The MOD function returns the remainder of a division operation.

RowNumber: Current Row Number

ColumnNumber

Switch (like CASE)

Choose (like VLookup)

Clustering/High Availability

SSRS is not cluster aware.   For it to function in a clustered environment, it needs to be installed on all nodes individually.

Scaling out a reporting server instance involves installing it on multiple systems, then pointing those systems to the same reporting database.  There should only be 1 reporting database in virtually all situations – it can be installed on a cluster.

http://technet.microsoft.com/en-us/library/ms156453(v=sql.105).aspx

SSRS is not cluster aware, but the SSRS database can be installed on a cluster.

Report Design

Drillthrough actions allow reports to load other reports.

http://msdn.microsoft.com/en-us/library/ms159847(v=sql.100).aspx

Subreports:

http://msdn.microsoft.com/en-us/library/dd220581(v=sql.105).aspx

Pagination

Page Type – Logical: Excel, HTML

Physical – Image, PDF, Word

None – XML, CSV

 

Physical: set height and width

Logical: calculated at run time based on data

 

Expressions can be used to calculate values for params, queries, filters, properties, groups, etc.

Interactive sorting in SSRS:

http://arcanecode.com/2010/07/15/interactive-sorting-in-sql-server-2008-reporting-services-reports/

Dataset: a simple collection of data used as the source for report items

SSAS datasets: MDX (multidimensional expressions), DMX (Data mining extensions)

Design mode: graphical setup, flattens results to two dimensional columns/rows – MDX

DMX – not automatically flattened unless keyword FLATTENED is used

Report Actions:

à Go To Report

à Go To bookmark

à Go To URL

Bookmarks: internal navigation mechanism

à Bookmark property in design

Backup and Recovery

To be backed up: ReportServer database, ReportServerTempDB, Encryption keys (via SSRS configuration manager)

Restore process:

1)      Install SSRS

2)      If necessary, restore SSRS DBs

3)      Restore encryption keys to enable access to security/data sources

Report Execution and scheduling

Reports that have a user-entered parameter can still be run without interaction by enabling a default value for the parameter, including using an expression.

Scheduling Reports: SQL agent must be on; credentials must be stored for report – ideally a unique account with Read-access to ReportServer, msdb, report objects

Shared schedules can be used by multiple reports; created in SSMS or Report Manager

Running reports on-demand: Pull delivery; Scheduled: push delivery

Subscriptions: automate delivery – File share, SMTP email delivery, NULL delivery channel (cache-only)

Report Performance

Report Caching: SQL Server can store cached data in ReportServerTEMPDB.

http://msdn.microsoft.com/en-us/library/ms155927.aspx

ReportServerTEMPDB also stores session execution and work tables.

Cache can expire via specified time limit or by a shared schedule.

Also expires if data source is changed.

Caches are parameter-based; same report with different parameters is a different cache.  You can have as many combinations of cached instances as there are combinations of parameters in a report.

Report snapshots

Pre-scheduled reports, persistent

Snapshots do not allow parameter changes – always use defaults.   Filters can be used lower down the hierarchy at the report level.

http://msdn.microsoft.com/en-us/library/aa256337(v=sql.80).aspx

SSRS Report Security

SSRS Security Model – Securables and Roles (Item and System-Level)

Item-Level: report object permissions

System: Wide-scale functionality

SSRS 2008 roles are managed via SSMS.

Role assignment is done through the report manager web interface.

SSRS Roles: http://odetocode.com/articles/215.aspx

From:

http://mscerts.programming4.us/sql_server/sql%20server%202008%20reporting%20services%20%20%20management%20and%20security.aspx

Built-in Roles and Permissions

SSRS comes with a set of built-in roles. Each role is a collection of permissions, normally used in tandem to enable a functional scenario. Following are some of the built-in roles:

·         Browser— This role is a collection of read-only permissions that is useful for navigating the Reporting Services namespace and viewing reports and resources.

·         Content Manager— This role is similar to an administrator on the part of the Report Server where it is granted. A person who has the Content Manager role can view and change any reports, folders, data sources, and resources and can read and set security settings in the folders where he or she has that permission.

·         Publisher— This role is useful for report authors who need to create and change reports, folders, and data sources in a specified folder.

·         Report Builder— This role can be used for granting permissions needed for editing Report Builder reports.

·         My Reports— This security role is normally given to each user in his or her own My Reports folder. It gives each user of the Report Server his or her own place to publish documents on the server.

By default, roles are devoid of users except for Content Manager, which has BUILTIN\Administrators assigned to the Home folder (Root)

Tasks that are available inside roles:

Consume reports

Create linked reports

Manage all subscriptions

Manage data sources

Manage folders

Manage individual subscriptions (user-specific)

Manage models

Manage report history

Manage reports

Manage resources

Set security for individual items

View data sources

View folders

View models

View reports

View resources

System-level roles are outside the normal report object hierarchy and are managed with SSMS

Default Roles:

System Administrator – manage roles and security, sever properties, schedules, job, and execute reports

System User

System-level tasks

Execute Report Definitions

Generate Events

Manage jobs

Manage report server properties

Manage report server security

Manage roles

Manage Shared Schedules

View Report Server properties

View shared schedules

Linked Reports: reference and existing report with pre-defined configurations; dependent on original report

My Reports can be used to junction Linked Reports and limited access

Report Deployment

Report deployment properties:

OverWriteDataSources: Y = replace shared data sources

TargetDataSourceFolder: location of shared data source

TargetReportFolder: Location reports will be deployed to

TargetServerURL (Default: http://localhost/ReportServer)

Native mode uses port 80 by default EXCEPT on XPSP2 32-BIT (8080)

StartItem: which item to display in the preview pane

Embedding reports in code: The Report Server web service exposes objects

2 Endpoints: ReportService2005 to manage objects (Native mode)

ReportService2006: manage objects (Sharepoint mode)

Also includes ReportService endpoint for backwards compatibility

Command-line utilities

RSConfig.EXE: DB connection properties

RSKeyMgmt.EXE

RS.EXE: deployment scripting