1 / 46

Microsoft SQL Server Reporting Services

Microsoft SQL Server Reporting Services. Doug Nelson Chief Architect SynApp north www.synappnorth.com. Discussion Outline. Reporting Services Overview Creating Reports with Reporting Services Managing Reports Reporting Services Web Service Extending Reporting Services.

paul2
Download Presentation

Microsoft SQL Server Reporting Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft SQL Server Reporting Services Doug Nelson Chief Architect SynApp north www.synappnorth.com

  2. DiscussionOutline • Reporting Services Overview • Creating Reports with Reporting Services • Managing Reports • Reporting Services Web Service • Extending Reporting Services

  3. Reporting Services Components Web Browser Report Designer Client Utilities Report Manager http://<server>reports URLAccess SOAP Endpoint Reportservices.amx WMI Web Service (IIS / ASP.Net) http://<server>reportserver Win32 Service Delivery Shared Components Data Retrieval Rendering Security SQL Server Database / SQL Server Agent

  4. Setup Prerequisites Setup UI assumes you are using the default web site and port and integrated security to access the database. If this is not the case, you will need to use command line setup or configure the server after the setup UI completes.

  5. Report Authoring Demo

  6. Management APIs • Web Service/ SOAP API • Full SOAP API Implementation with complex types • Includes WDSL • Add service reference to Visual Studio .NET • Supports SSL and scripting • WMI • Used for managing service configuration • Enumerate instances of Report Server • Works even if the Web Service is not available • No WMI events ( configuration only)

  7. Management Tools • Report Manager • Web-based management application • Built using ASP.net and Web Services • Client Utilities • Script Host – rs.exe • Server Configuration – • Encryption Key Management – • Custom Applications • SQL Server 2005 Management Studio

  8. Role-Based Security Model • Tasks • Sets of low level operations • Item level ( e.g. create report) or system level (manage jobs) • Not customizable • Roles • Sets of tasks • Default roles installed by default (browser, publisher) • Default roles can be customized, new ones created • Groups / Users • Windows NT / Active Directory • Role Assignments • Associates groups / users with roles • Inherited from the parent namespace

  9. Report Properties • Metadata is extracted from report definition at publishing and maintained in the database • Name • Description • Hidden • Report Definition • Parameters • Prompt • Prompt String • Default Names • Data Source Information

  10. Data Source Management • Administrator can set connection type and connection string after publishing • Credential Operations • Prompt for Windows or database credentials • Securely stored Windows or database credentials • Integrated Security (Requires Kerberos delegation) • None (uses report execution account) • Shared Data Sources • Connection and credential information stored as a secured object in the namespace • Single point of management for multiple reports

  11. Managing Report Execution • Execution Sessions • Automatically created for each report execution • Keeps consistency between server round trips (images, paging, exporting) • Session timeout set in server configuration file • Cache Snapshots • On-demand reports can be cached between users • Cache index is based on parameter values • Cache valid for a specified time after execution or cleared on schedule • Limitations – User-specific expressions (User ID, Language), stored credentials

  12. Snapshots and History • Execution Snapshot • Report execution is scheduled, all users get the same data • Single instance of processed report • Limitations: No query parameters or user-specific expressions, stored credentials • History Snapshots • Multiple instances of report snapshots for archiving, auditing purposes • Stored independently of data source, report definition • System and report-specific retention policy

  13. Managing Report Execution • Configure cache and snapshots via Report Manager or SQL Management Studio • Set execution timeouts on a system-wide or per-report basis • Long running reports can be stopped manually • Report execution log enables analysis of server usage • Optionally, executions are logged to Report Server database • Includes report, format, user, start, end, cache hit, size • Setup includes DTS package and sample reports

  14. Scheduling • Management events can be scheduled on the report server • Caching • History • Subscriptions • Schedules are stored in database and integrated with SQL Agent • When triggered, Agent adds entry to the queue • Scheduled events are queued in database and polled by NT Service

  15. Shared Schedules • Managed shared schedules independently of reports, subscriptions or snapshots • Change shared schedule properties • Name • Days, times or frequencies • Start and end dates • Pause and resume shared schedule • Expire a shared schedule • Delete a shared schedule

  16. Subscriptions • Personal or administrator defined • Subscriptions triggered by an event ( schedule, snapshot creation, external) • Delivery extension (e-mail, file share) specifies how report is delivered • E-mail delivery requires an SMTP server • Extensible delivery architecture • Can specify output format (HTML, XLS) • Can deliver links as well as rendered reports • Two types of subscriptions • Standard • Data Driven

  17. Standard Subscriptions • Single report sent to a fixed set of addresses • End user wants to customize their own report delivery • How it works • User creates a standing request to run a report at a specific time and delivered in a certain format • Can be triggered based on a schedule or snapshot generation • Specify report, execution conditions, parameters, rendering format, delivery location, etc.

  18. Data Driven Subscriptions • When to use • Delivery of a report to a dynamic list of destinations with customized content for each destination • How it works • Set up by the adminstrator • Define delivery queue to return list of destinations and parameters • Specify delivery settings and parameter values as a static or field from delivery query • Set to run according to a defined schedule or trigger from a snapshot • Use Null Delivery Provider to deliver reports to cache

  19. Automating Repetitive Tasks • Examples • Duplicate settings between servers • Migrate from test to production environment • Change shared data sources • Cancel running jobs • Automate web service tasks through Report Server Script host (rs.exe) • Visual Studio .Net not required for execution

  20. Scripting Support • VB .Net Code File • Create with text editor or Visual Studio .Net • Generated in SQL Server 2005 Management Studio • Unicode or UTF-8 text file with a .rss file extension • Written using generated Web Service proxy • Scripts must have one function with declaration: • Public Sub Main() • Connection to the report server is made automatically by the script host

  21. Server Configuration Files • Unique per Report Server – not transferable • Exclude from synchronization across web farm • Specific areas of interest • Report Server Database Connection • Report Execution account and password • Extension Configuration ( including e-mail delivery) • Use text editor to change ( file monitoring) • Use rsconfig or WMI provider for encrypted settings • Code Access Security (CAS) for extensions stored in separate file

  22. Logging and Monitoring • Performance Monitoring • Counters for performance • Event Log Integration • Critical events and errors • Trace Events • Traces all server activities, response times, security events

  23. Data Encryption • When data source credentials are encrypted in the Report Server database • Stored with symmetric key tied to instance / machine / service account • Decrypted by both Web Service and NT Service • Shared by all machines in web farm • Update when machine name, instance name or service account changes • Manage keys with rskeymgmt • Extract a copy of the encryption key • Apply stored encryption key • Remove encrypted data on machine

  24. Using Reporting Services with Applications

  25. Reporting Platform Integration • .Net-based solutions (non reporting) with reporting needs • Report Authoring and Publishing Tools • Delivery, Data and Rendering Extensions (Add-ons) • Server Management applications • Portals and Collaboration Applications

  26. Programmatic Interfaces • Customizable XML report definition • Open Schema • Viewing Interfaces • URL Addressability • Web Service / SOAP • Management Interfaces • Web Service SOAP • WMI Interface • Extension Interfaces • Data, Delivery, Rendering and Security

  27. Generating Reports • Application specific report authoring requirements • Reports are defined in Report Definition Language (RDL) • XML format • Use .Net XML classes to generate report definitions • Leverage RDL Schema Description (XSD)

  28. URL Addressability • Embed or link to reports via the Report Server URL • http://[servername]/ReportServer • Supports both GET and POST methods • Can return any output in any format • Optional HTML Viewer for parameter prompting and navigation • Fully localized – based on language in the HTTP header

  29. URL Parameter Directives • Report Parameters • Parameter names must match report definition • http://servername/ReportServer/ReportName?CategoryID=1 • User Credentials (dsu: and dsp:) • Credentials for each report data set https://<>?dsu:DataSet1=MyUserName&dsp:DataSet1=MyPassword Use Secure Protocols!

  30. URL Parameter Directives • Report Server Parameters ( rs: ) • Name/Value pairs specifying server behavior • Same for all Rendering Extensions • http:<>?rs:ClearSession=True • Rendering Device Info ( rc: ) • Name/Value pairs specifying behavior specific to the format being rendered to (i.e., FindString, Section, Zoom, etc.) • Unique per Rendering Extension • http:<>?rc:Section=7&rc.HTMLFragment=true

  31. Web Service Interface • Namespace Management • Scheduling, Subscriptions and Delivery • Report Execution, Report History, Linked Reports • Item Properties, Job Management, Security Management • Report Parameters, Datasource Management

  32. Getting Started • Add a Web Reference to your Visual Studio project • http://[servername]/ReportServer/ReportService.asmx • Proxy classes are generated by Visual Studio • Complex types defined in WSDL • Synchronous and asynchronous support • Update operations may be batched • SOAP Headers • Session ID • Server Info • Batch ID

  33. Web Service Authentication • Basic Authentication • System.Net.NetworkCredentials • ReportServer.RSWebSerive rs = new ReportServer.RSWebService(); • rs.Credentials = new System.Net.NetworkCredential(“user”,”pwd”,”domain”); • Integrated Authenication • System.Net.CredentialCache • rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

  34. Extending Reporting Services • Security Extensions • Delivery Extensions • Rendering Extensions(Only for the very brave) • Data Processing Extensions

  35. Server Extensibility • Extensions provide a way to extend the Reporting Services platform • Managed code runs in server process • Published CLR interfaces • Extension Types • Data Extensions – Communicates to data sources and returns data • Delivery Extensions – Delivers reports over different protocols and to different devices • Rendering Extensions – Renders to specific formats and devices • Security Extensions – Authenticates and authorizes non-Windows users • Extending should be seen as a last resort

  36. Rendering Extensions • Input: A processed report object model • Output: format-specific output stream • IRenderingExtension interface • Render method responsible for primary output stream • RenderStream method responsible for returning ancillary streams (images, etc) • GetRenderingResource may be used to provide non-report specific content • A rendering extensions are very difficult to write and maintain – consider using Xml output instead.

  37. Delivery Extensions • Input: event and specified destinations • Output: delivered reports or notifications • IDeliveryExtension interface • Deliver method delivers an input notification to a destination • ValidateUserData method verifies a set of delivery information is valid • ExtensionSettings property allows delivery extensions to be self describing • Can also provide UI to integrate with Report Manager

  38. Security Extensions • Input: user credentials, security policies • Output: authenticate user credentials and authorize server operations • IAuthentication interface • LogonUser method authenticates the user • GetUserInfo retrieves a user’s unique identity • IAuthorization interface • Provides classes, enumerations, methods for authorizing the user • Allows creating, verifying security descriptors • Only available in Enterprise Edition

  39. Data Extensions • Input: Connection information, query with optional parameter support • Output: DataReader • Subset of the .Net Managed Data Provider Interface (System.Data) • Any .Net managed provider will work • Required: IDbConnection, IDbCommand, IDbDataParameter, IDataReader • Optional: Extended Data Extension interfaces • Generic query designer in Report Designer

  40. Introducing Data Processing Extensions

  41. Data Extension Process Flow • The report server creates a connection object and passes in the connection string and credentials associated with the report. • The command text of the report is used to create a command object. In the process, the data processing extension may include code that parses the command text and creates any parameters for the command. • Once the command object and any parameters are processed, a data reader is generated that returns a result set and enables the report server to associate the report data with the report layout.

  42. SynApp Custom Dataset Extension • These classes implement the necessary interfaces • SynAppConnection : IDbConnectionExtension/IDbConnection • SynAppCommand : IDbCommand • SynAppTransaction : IDbTransaction • SynAppDataParameter : IDataParameter • SynAppDataParameterCollection : IDataParameterCollection • SynAppDataReader : IDataReader Need to set a reference to Microsoft.ReportingServices.Interfaces. This file is located in C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer/bin/Microsoft.ReportingServices.Interfaces.dll

  43. Deploying SynAppDataExtension • Deploying Data Extension to a Report Server • Copy assembly to bin directory of the Report Server • Changes required in the RSReportServer.config file • Find the existing data extensions and add the new extension • <Extension Name="SynAppDS" Type="SynAppDataExtension.SynAppConnection, SynAppDataExtension"/> • Changes are also required to the rssvpolicy.config <CodeGroup class="UnionCodeGroup” version="1" PermissionSetName="FullTrust" Name="SynAppDataExtension" Description="Code group for my data processing extension"> <IMembershipCondition class="UrlMembershipCondition“ version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\SynAppDataExtension.dll" /> </CodeGroup>

  44. Deploying SynAppDataExtension • Deploying Data Extension to the Report Designer • Copy assembly to Report Designer folder • Changes required in the RSReportDesigner.config file • Find the existing data extensions and add the new extension • <Extension Name="SynAppDS" Type="SynAppDataExtension.SynAppConnection, SynAppDataExtension"/> • Changes are also required to the rspreviewpolicy.config <CodeGroup class="UnionCodeGroup” version="1" PermissionSetName="FullTrust" Name="SynAppDataExtension" Description="Code group for my data processing extension"> <IMembershipCondition class="UrlMembershipCondition“ version="1" Url="C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportServer\bin\SynAppDataExtension.dll" /> </CodeGroup>

  45. Additional Resources • Microsoft SQL Reporting Services • www.microsoft.com/sql/reporting • MSDN Reporting Services Developer Center • msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices • Reporting Services Blogs • blogs.msdn.com/tudortr • blogs.sqlxml.org/bryantlikes • odetocode.com • Recommended Reading • Microsoft Reporting Services in Action – Teo Lachev • Microsoft SQL Server 2000 Reporting Services – Brian Larson • Hitchhiker’s Guide to SQL Server 2000 Reporting Services – Peter Blackburn William Vaughn

  46. Questions? • Presenter - Doug Nelson • Company - SynApp north • Web Site - www.synappnorth.com • Email Address - dnelson@synappnorth.com

More Related