Transcript Slide 1

Excel Services
•
Displays all or parts of interactive Excel worksheets in the browser
– Excel “publish” feature with optional parameters defined in worksheet
– Expand / collapse Pivot Table, Sorting, Filtering, Parameters
•
•
•
Pass parameters with SharePoint filters to Excel Web Part views
Live spreadsheet “co-authoring” with Excel Web Apps via Office Web Apps
Excel application development improvements from previous versions
– Web Services API allows custom edit, save and collaborative editing applications
– REST exposes Excel content and models via a URL
http://server/_vti_bin/ExcelRest.aspx/..../Ranges(‘Sheet!A1’)
– JavaScript Object Model code with Excel web parts “get/set values”
•
Security can be confusing –
plan carefully
– Trusted File Locations
– Connections
Source: SharePoint 2010 Technical Resource DVD
Excel Services
Control Excel Menus
Control Interactive Features
Excel Services
View, Interact,
Create, Edit
Thin rendering
in browser
Author and Publish
Spreadsheets
Excel
Download a Copy
or a Snapshot,
Open for editing*
Excel
*Editing Spreadsheets in SharePoint
• For editing and viewing changes that do
not change the original worksheet use
Excel Web Access (EWA) Web Part
and set desired Interactivity features
• For concurrent editing of the original
worksheet use Excel Web App via
SharePoint Office Web Apps features
Web Services,
REST API,
JavaScript OM
Custom
applications
Excel Services
Save & Send >
Save to SharePoint
Choose Publish Options to
define worksheet area to show
and define any parameters
Add parameters
Demo: Excel Services
Visio Services
•
Get creative and add reporting “context” with Visio visualization
–
–
–
–
•
•
•
View in browser as Silverlight or image, NO Visio Client or Visio Viewer needed
Refresh data-driven pivot diagrams in the browser
Integrate diagrams into SharePoint applications with web part connectivity
Extend it with Mash-up API, JSOM and other custom development
Save a Visio Web Drawing (.vwd) to SharePoint document library created in
Visio Professional 2010 or Visio Premium 2010 versions
Embed in SharePoint pages using the Visio Web Access web part
Data connectivity and refreshes
– SQL Server, OLE DB data sources, SharePoint Lists and Excel Services
– Allowed data sources saved in Trusted Data Providers
•
Visio Services external data authentication
– Integrated Windows Authentication
– Secure Store Service
– Unattended Service Account
•
Also a new Visio Process Repository
Source: http://blogs.msdn.com/visio/archive/2009/10/30/introducing-visio-services.aspx
Visio Services
Refreshable data-driven
Visio diagrams in SharePoint
provide quick visual context for
performance monitoring
Source: SharePoint 2010 Technical Resource DVD
Visio Services
In Visio 2010 use
Save As Web Drawing
Publish to SharePoint
Source: http://blogs.msdn.com/visio/archive/2009/10/30/introducing-visio-services.aspx
Reporting Services and
Report Builder 3.0
•
Various Levels of Reporting Services integration with SharePoint
–
•
Local Mode
–
–
–
•
New SharePoint capability to render reports without an integrated Report server
Install SSRS 2008 R2 Add-in for SharePoint 2010
Only renders uploaded reports (.rdl files) for SharePoint Lists and Access data sources
Reporting Services Native Mode
–
–
–
–
•
New Local Mode, Native Mode and SharePoint Integrated Mode (preferred)
Storage 100% outside of SharePoint
Separate security and management
Two SharePoint Web parts Report Explorer and Viewer configured to point to external Reporting
Services instance report(s)
Challenging to pass parameters but it can be done with custom ASP.NET
Reporting Services SharePoint Integrated Mode
–
–
–
–
–
Storage in SharePoint and Reporting Server
SharePoint configuration, management, display and document library security
SharePoint document library access to history, subscriptions, Report Builder and more
Much easier for SharePoint development and connecting to other Web parts
BIDS deployment settings need to be configured with full paths
Reporting Services and
Report Builder 3.0
•
•
SSRS Integrated Mode best with SQL Server 2008 R2,
SQL Server 2008 has a patch for limited functionality
Report Builder 3.0 New Features
–
–
–
–
–
–
•
Improved visualizations
–
•
Publish new reports to SharePoint
or edit existing reports
New reusable Report Parts and the Gallery
New shared datasets offer a way to share
a query for multiple reports
SharePoint List data extension
Improved pagination control
Aggregates over aggregates, look up functions
Sparklines, indicators and basic thematic mapping
Rendering Reports to Data Feeds
Source: http://blogs.msdn.com/seanboon/archive/2008/10/10/how-to-build-sparkline-reports-in-sql-server-reporting-services.aspx
and http://prologika.com/CS/blogs/blog/archive/2009/11/23/report-parts.aspx
Reporting Services and
Report Builder 3.0
Add SSRS Content Types to
SharePoint Document Library
Now can manage, edit or
create new SSRS reports
Many other SSRS features
available in Document Library
Report Builder 3.0
Simple to use
Report Wizards
Great data
visualization tools
Report Part Gallery for
reusing report items
Easy drag and drop
report building
Report Viewer Web Part
Embed reports and pass
parameters from
other web parts
Query String (URL) Filter “Year”
web part connected to SSRS
Report “Year” parameter
Demo: Report Viewer and
Report Builder 3.0
Self-Service BI with PowerPivot for Excel
•
•
Mashes-up a wide variety of data sources
Data analysis
–
–
•
PowerPivot requires SQL Server 2008 R2
–
–
•
An Excel 2010 Add-In
Behind the scenes it creates an Analysis Services cube
Vertipaq able to process massive data in seconds
–
–
•
Pivot Tables and Pivot Charts with robust Slicers for filtering
Data Analysis Expressions (DAX) formulas
Note 32-bit version handles less data than 64-bit
Dataset size limited by amount of available memory
IT critical for proper implementation
–
–
–
Plan PowerPivot security and usage policies
Semantic business friendly layer for OLTP sources
Note business users may struggle with trying to combine data that
does not have easy to recognize key relationships
Self-Service BI with PowerPivot for Excel
Excel 2010 Add-In
in Excel Options
PowerPivot tab
Launch PowerPivot
Select data sources
Self-Service BI with PowerPivot for Excel
Select
Data Sources
Import Wizard
Self-Service BI with PowerPivot for Excel
Create
Relationships
between
Data Sources
Self-Service BI with PowerPivot for Excel
Add New
Measures
Analyze Data with
Pivot Tables
and Charts
Select Slicers
Self-Service BI with PowerPivot for Excel
Add new Measure
column using DAX