1 / 42

INTRODUCTION TO SQL SERVER REPORTING SEVICES

Delve into the world of SQL Server Reporting Services for robust business intelligence. Explore SSRS features such as Tablix, Sparkline Charts, Bullet Gauges, Shared Components, Reports in SharePoint, and more. Learn to build interactive reports, visualize spatial data, and deliver personalized reports efficiently.

jmead
Download Presentation

INTRODUCTION TO SQL SERVER REPORTING SEVICES

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. INTRODUCTION TO SQL SERVER REPORTING SEVICES Shane Risk Business Intelligence Consultant

  2. Introduction to SSRS

  3. Integrated End-to-End BI Offering DELIVERY COLLABORATION CONTENT MANAGEMENT SharePoint Server SEARCH Reports Dashboards Excel Workbooks Analytic Views Scorecards Plans END USER TOOLS AND PERFORMANCE MANAGEMENT APPS Excel PerformancePoint Server BI PLATFORM SQL Server Reporting Services SQL Server Analysis Services SQL Server DBMS SQL Server Integration Services

  4. The Microsoft BI Platform SQL Server 2008

  5. Introduction to SSRS

  6. Flexible Report Layout • What is Tablix? • The best of Table merged with the best of Matrix • Allows for fixed and dynamic columns and rows • Enables arbitrary nesting on each axis • Enables multiple parallel row/column members at each level • Introduces optional omission of row/column headers • Innovative layout capabilities • Support for richly formatted text • Innovative grouping capabilities Web Report Embed Reporting

  7. Tabl e + Matr ix

  8. Rich Visualizations • Chart Types Web Report Embed Reporting

  9. Rich Visualizations • Gauge Types Web Report Embed Reporting

  10. Inline Charts • Greatly enhances report impact relative to a table of values • All reports and gauges can be embedded in tables • Allows for interesting layouts that were previously unsupported • Charts are rendered to the browser as images, be sure to test client performance prior to rolling out to users

  11. Sparkline Chart • Compact and concise view of data ideal for dashboards • Provides meaningful historical context • Allows understand time series comparisons across entities • Link to other reports for more detail

  12. Bullet Gauge • Preferred over gauges and thermometers • Easier to read • Take less space • More accurate • Similar to a bar chart, but offers more information

  13. Report Development Demo 1 • Tablix • Inline Charts

  14. Maps • Enhance reports with spatial data visualizations • Incorporate Bing Map layers to give maps a professional look • Supports SQL Server spatial data type • Supports ESRI Shapefile format • TIGER/Line Shapefiles are preloaded from the U.S. Census Bureau

  15. Report Builder 3.0 • Most recent version of the Report Builder tool • Same code base as BIDS report designer • Additional functionality allows you to utilize Shared Datasets and Shared Report Components not present in BIDS • Allows end users to design their own reports • Thin client accessible through Report Manager (Native) or SharePoint (Integrated)

  16. Shared Components • Shared Datasets • Share datasets across reports for simplified maintenance and improved performance • Can be cached independently from any reports • Can be reused by end users building reports in Report Builder 3.0 • Shared Report Parts • Share report components between reports • Build a report visualization once and use it in many reports • Can be reused by end users building reports in Report Builder 3.0

  17. Report Development Demo 2 • Report Builder 3.0 • Shared Report Parts • Shared Datasets

  18. Introduction to SSRS

  19. Deliver Personalized Reports • Delivery to the location users want • Pull Model for retrieval of reports when needed • Push Model for scheduling automatic delivery • Embed reports for contextual business insight • Interactive Reports • Interactive navigation paths • Parameterization for optimal relevancy • Ad-hoc report customizations • Rendering in the format users want • Rendering to Microsoft Office • Rendering in SharePoint • Rendering HTML, PDF, CSV, XML, and Image XML

  20. My Reports • Personal folder where report publishers can deploy their own reports • Useful for creating one-off reports from existing reports for one time use • Can be used as a poor man’s development environment • Develop and QA reports deployed to My Reports prior to public release • System Administrators can view reports in user’s My Report folders

  21. My Subscriptions • Users can subscribe to reports through the Report Manager interface • Report subscriptions can be viewed and maintained on the My Subscriptions page • Allows users to easily manage report delivery without administrative assistance

  22. Data Driven Subscriptions • Table driven report delivery • Report parameters are specified via table columns • Each row in the table is a separate report instance • Reports can be delivered via email, to a file share, or to a SharePoint list

  23. SharePoint Portal – Key Benefits • Single place to View, Manage, & Deliver reports with-in SharePoint UI • Same site access for all business documents, including reports, report models, and shared data sources • Deep level of database and security integration • Easy integration with other information delivery tools & technologies

  24. Viewing Reports in SharePoint • ReportViewer web part • Full page or Dashboard view • Connects with Filter web parts • Connects with Reports Library, Doc. Library web parts • URL Access Parameters • Toolbar, HeaderArea, DocMapAreaWidth, AsyncRender • PerformancePoint Dashboard Viewer • Embed Reporting Services reports within a PerformancePoint dashboard

  25. Report Delivery Demo • My Reports • My Subscriptions

  26. Introduction to SSRS

  27. Reporting Services Security • Users are granted role based access to specific folders • There are a number of predefined roles that come out of the box • New report roles can be created through SQL Server Management Studio • Roles: Browser, Publisher, My Reports, Content Manager, System User, System Administrator

  28. Data Source Administration • Manage data sources through the report manager interface • Configure reports to use a proxy account or Windows Integrated Security • View subscriptions that rely on the data source • View dependent reports

  29. Report Caching • A report optimization technique that caches report data • Set a report to keep a temporary copy of the report when executed or refresh the cache on a schedule • Report caches can be expired after a number of minutes or according to a schedule • Caching requires the use of stored data source report credentials

  30. Report Snapshots • Used to retain point in time reports • Report snapshots can be configured to run on a specified schedule • Snapshots store report data in the Report Server database • Render at runtime using stored snapshot data • Requires stored report credentials

  31. Linked Reports • Reports can show up in multiple folders but are only deployed to one • Simplifies report maintenance because report updates only need to be applied to a single report • Linked reports can have their own default parameters, allowing linked reports to display their own discrete data Linked Report Linked Report Linked Report Report

  32. Manage, Deliver & Publish • Manage Report Items in SharePoint • Report parameters, Data sources, Processing Options, Snapshots etc. • Data Sources, Dependent Items • Model Item Security • Report Delivery from / to SharePoint • Report subscriptions & Shared Schedules • Publish to SharePoint • Report Builder, Report Designer, Model Designer

  33. SSRS Features NOT Supported in SharePoint Integrated Mode • Custom SSRS Security Extensions • Report Manager Administration of Reports • Linked Reports • My Reports • My Subscriptions • SSRS Batching Methods

  34. Report Server Administration Demo • Roles • Data Source Administration • Report Snapshots • Report Caching • Linked Reports

  35. INTRODUCTION To SSRS

  36. Azure Reports • Cloud based access to Reporting Services • Alleviate the overhead of hosting your own SSRS servers • Provide per user access to reports via the internet

  37. Azure Reports – Supported Features http://www.microsoft.com/en-us/sqlazure/reporting.aspx

  38. Project Crescent • Silverlight Ad Hoc Reporting tool coming in SQL “Denali” • Enhanced self service BI offering for PowerPivot • Supports drag and drop report building for the end user • Features highly interactive visualizations and animations • Allows visualizations to be embedded in PowerPoint presentations for powerful real time presentations • http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx

  39. INTRODUCTION To SSRS

  40. Conclusion • Reporting services is • a powerful enterprise reporting platform • capable of meeting your report delivery needs • simple to administer • constantly evolving to become even more powerful

  41. Additional Resources • Books • Microsoft SQL Server 2008 Reporting Services by Brian Larson • Applied Microsoft SQL Server 2008 Reporting Services by TeoLachev • Web Sites • http://msdn.microsoft.com/en-us/sqlserver/cc511478.aspx • Blogs • Reporting Services Team Blog - http://blogs.msdn.com/b/sqlrsteamblog/ • Robert Bruckner - http://blogs.msdn.com/b/robertbruckner/

  42. Questions? Shane Risk Business Intelligence Consultant BlueGranitesrisk@blue-granite.com Phone: 734 478 5905 www.blue-granite.com http://www.linkedin.com/in/shanerisk http://twitter.com/srisk

More Related