sharepoint and ssrs brian caauwe – senior sharepoint consultant megan caauwe – bi associate...
TRANSCRIPT
SharePoint and SSRS
Brian Caauwe – Senior SharePoint ConsultantMegan Caauwe – BI Associate Consultant
April 14, 2012
Better Together
Session Agenda
• SSRS Basics• SSRS SharePoint Integrated• Architecture• Development
• Megan Caauwe– Business Intelligence Consultant• Email: [email protected]• Twitter: @megmation
• Brian Caauwe– SharePoint Consultant & Speaker• Email: [email protected]• Twitter: @bcaauwe• Blog: http://blog.avtex.com/author/bcaauwe
Who are we?
Minnesota SharePoint User Group
• 2nd Wednesday of the Month• 9:00 – 11:30 AM
• SharePoint resources and links• Meeting Schedule• Past User Group Presentations• This Presentation
• Next Meeting – 5/9• TBD
www.sharepointmn.com
Quick Poll• SharePoint Version
– 2007 – WSS, MOSS– 2010 – SPF, Server, FAST
• SQL Version– 2005– 2008– 2008 R2– 2012
• Currently using Reporting Services– Native– Integrated
• Work Roles– SharePoint Administrator– SharePoint Developer– Business User– Other
SSRS Basics
ReportingTables, Charts and Maps… OH MY
SSRS Basics• Create professional, standardized reports for the enterprise• Web-based reporting portal• Allow for a certain level of interactivity in the report
– User-controlled Parameters– Expand/collapse rows and columns– Embedded hyperlinks– Interactive sorting
• Different visualizations– Table/Matrix (aka Tablix)– Chart, Data Bar, Sparkline– Gauge/Indicator– Map
General Overview
SSRS Basics• SQL (On Premise, Azure)• Data Warehouse• Multidimensional database• SharePoint Lists• XML• SAP• Hyperion• Oracle• Teradata• DB2• Generic (OLEDB, ODBC)
Supported Data Sources
SSRS Basics
• Export Options– Word, Excel, PDF, TIFF, HTML, XML, Data Feed
• Print• Subscriptions– Data driven (Enterprise Only)– User-scheduled
• Parameters• Self-Service Reporting
End User Functions
SSRS BasicsSample Report Portal
DEMO – SSRS Native
SSRS Integrated
SSRS IntegratedBetter Together
SSRS Integrated
• Shared navigation, look, and feel• Extend report metadata using SharePoint document
management model• Manage deployment and approval through SharePoint
workflows• Shared permissions/authentication• Deliver report subscriptions to a SharePoint library• Embed reports in a site using a Report Viewer web part• Access to extended Reporting Features
– Specifically PerformancePoint and Power View
Benefits
SSRS Integrated
• Unsupported SSRS Features– Custom security extensions– Report Manager portal in browser– Linked Reports– My Reports / My Subscriptions– Batching Methods
• Requires SQL to be installed ON SharePoint• Claims authentication “fun”
Downfalls
SSRS Integrated
• Same as Native EXCEPT• Report Server Mode – SharePoint Integrated• SSRS account needs rights to content databases
Configuration – Report Server
SSRS Integrated
• General Application SettingsConfiguration – Central Administration
SSRS Integrated
• Reporting Services Integration– Report Server URL– Authentication Mode– Credentials– Features
Configuration – Central Administration
SSRS Integrated• Site Collection Feature
• Content Types
• Web Part
Configuration – Site Collection
SSRS IntegratedSample Report Library
DEMO – SSRS Integrated
Architecture
ArchitectureSlightly Confusing…
Architecture• SQL 2005– Service Pack 2– Only works with SharePoint 2007
• SQL 2008– RTM+ works with SharePoint 2007– Service Pack 1 + CU #8 works with SharePoint 2010
• Needs 2008 R2 Add-In
• SQL 2008 R2– Works with SharePoint 2007 & 2010
• SQL 2012– Works with SharePoint 2010
• Deployed as a Service Application
Application Dependencies
Architecture
• SQL installation ON SharePoint server– Same SharePoint product installation– Does NOT need to be on ALL farm servers
• Authentication– Windows Authentication• NTLM• Kerberos
– SQL Authentication• DNS for report server URL
Architecture Dependencies
Architecture• SQL Enterprise (or SQL 2012 BI Edition)
– Scaled out deployments– Data driven subscriptions– PowerPivot for SharePoint– Power View
• SharePoint Enterprise– Excel Services– PerformancePoint– Power View
• SQL Virtualization– License the host or individual virtual machine– http://
download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Guide_Apr2012.pdf
Licensing Dependencies
Development
Differences in SQL VersionsSQL 2005 SQL 2008 SQL 2008 R2 SQL 2012
Render for MS Word No DOC DOC DOCXRender for MS Excel CSV CSV & XLS CSV & XLS CSV, XLS &
XLSX
Visual Studio Compatibility 2005 2008 2008 2010Report Builder Version 1.0 2.0 3.0 (best) 3.0Shared Data Sources Yes Yes Yes Yes
SQL Azure Data Source Type No No Yes Yes
SharePoint List Data Source Type No No Yes Yes
Shared Data Sets No No Yes YesIncludes Mapping Component No No Yes YesIncludes Gauge, Data Bar, Sparkline, and Indicator Components
No No Yes Yes
Visual Studio vs. Report Builder• Report Builder can be accessed from SharePoint
– Launches from the server instead of needing a full install• Report Builder is geared toward the Business Power-User
– Microsoft Office Ribbon Bar for many settings– Many built-in wizards for quickly starting reports and adding different
types of content• Business Intelligence Development Studio is an expansion on
Visual Studio which comes with SQL– Adds new project templates: SSAS, SSIS, SSRS, etc.
• Allows developers to leverage full Project/Solution management capabilities– Enables TFS integration– Allows grouping/nesting of related content: multiple reports in a
project, multiple projects in a solution, etc.
Other SharePoint Visualization Tools
• Dashboard tool with a very SharePoint-centric look and feel
• Capable of easydrill-throughinteractionswhen used incombinationwith a datacube
PerformancePoint
Other SharePoint Visualization Tools
• Build out tables and charts in a very familiar Microsoft Office interface
• Embed the resultsinto SharePoint forthe masses to see
Excel Services
Other SharePoint Visualization Tools
• Microsoft’s latest and greatest• Self-service reporting tool (after a “little”
setup and configuration, of course)• Business-user
data analysistool whichdoesn’trequire SQLknowledge
Power View
DEMO – Development
References• Brian’s Blog: http://blog.avtex.com/author/bcaauwe• Misc. Microsoft MSDN & TechNet
– Licensing Guide: http://download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Guide_Apr2012.pdf
– Features Supported by Reporting Services in SharePoint Integrated Mode: http://technet.microsoft.com/en-us/library/bb326290.aspx
– Overview of Reporting Services and SharePoint Technology Integration: http://technet.microsoft.com/en-us/library/bb326358.aspx
– Features Supported by the Editions of SQL Server: http://msdn.microsoft.com/en-us/library/cc645993.aspx
– SSRS Native URL Access Parameters: http://msdn.microsoft.com/en-us/library/ms152835(v=sql.105).aspx
Q & A
