70-448 (070-448) Exam prep, Part 2/3 - SSRS
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
SMTP settings are also in RSReportServer.CONFIG
Enabling Kerberos in SSRS:
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
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:
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:
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