Excel Add-in for Analysis Services
Franco Perduca
Factory Software
[email protected]
Excel Add-in for Analysis Services
Microsoft Office Excel add-in
Free-form e Semi-Structured reporting
Free-form supporta connessioni a fonti diversi
nello stesso report
Supporta






2
Microsoft Excel 2003 / Excel XP
Microsoft Analysis Services 2000 / 2005
Free-Form Reports
Sales Cube
Mktg Campaign Cube
Vantaggi
•
•
•
•
Diversi cubi OLAP
Dati OLAP e Excel
Insert di righe e colonne
Funzioni native di excel (formatting and
sorting
• Member properties
• Analysis Services’ Actions, drill through,
write back e server side formatting
3
Formule Excel
Limiti
• Use di membri expliciti
• No pivoting
• Navigazione limitata
Structured Reports
Geography
Order Type
Customer
Vantaggi
• Espressioni dinamiche su assi
• Drill Up, Drill Down, Expand, Collapse,
Focus e Eliminate, Pivoting
• Analysis Services’ Actions, drill through,
write back e server side formatting
• Refresh vede nuovi memberi
• Espressioni custom per filtri
4
Limiti
•
•
•
•
•
Reports da fonti singoli
No Insert row or column
No dati esterni
No member properties
No sort members
Building a Report Overview
Selezionare report layout (structured only)
Scegliere data connection
Selezionare la dimension, (gerarchia se
presente) e display type (Member Tree o
Dimension Level)
Selezionare membri o misure e eventuali opzioni
Popolare Foglio
1.
2.
3.
4.
5.
5
Structured Report Layout
Initial layout
Additional elements
6
Working with Data Connections
Office Data Connection .odc files
Gestione Data Connection dal Build Report Data Panel
Connessione verso cubi o local cube
Connessione http: o TCP/IP
personalizzazione connection string
Salvati in current user’s Application Data\ORSLN folder
Gestione centralizzata tramite Manage Connection







7
Explicit Member Selection
Selezionare membri da includere e Selection Set
come Member(s) Only
Membri sugli assi per free-form non è dinamica
Page Segment solo un Membro



8
Dynamic Member Selection
Dal Member Tree usare Define Selection Set As


Creare un set usando le funzioni dal combo
Dimension Level


Posso scegliere livelli anzichè membri
Structured Reports – Nuovi membri compresi
nel set sono aggiunti quando faccio refresh
Free-Form Reports – Set viene convertito in
explicit members (no nuovi membri)


9
Custom Set Expressions (Structured
Only)
Where Measure












is equal tois top percent
is top sum
is bottom count
is bottom percent
is bottom sum
is less than
is less than or equal to
is greater than
is greater than or equal to
is between
is top count
Show MDX

10
Searching

Su cosa applico filtro (Scope)



Options



11
Selected Member/Levels – Searches Member and
Descendants or Entire Level
Entire Hierarchy
Starting With
Contains
Exact Match
Navigating a Structured Report

Drilling Down and Drilling Up
Drill Up
Drill Down
12
Navigating a Structured Report
Expanding and Collapsing
Expand
Collapse

13
Navigating a Structured Report

Pivoting a Report
Pivot
14
Navigating a Structured Report

Isolating and Eliminating

Data isolation


Data elimination

15
Vedo solo celle che voglio
Tolgo quello che non mi
interessa
Navigating a Free-Form Report


Isolating and Eliminating
Expanding Only and Collapsing Only

Global Option Settings


16
Enable Expand Only
Show Custom Data
Navigating a Free-Form Report

Sorting uguale a Excel nativo




Sort sulle righe
Sort sui dati

17
Sort sui valori delle celle
Is undoable
Bisogna avere almeno una non-measure dimension
sulle colonne
Navigating a Free-Form Report

Excel Calculations



Formule con dati esterni a OLAP
Gestisce a refresh
Può essere copiato per estendere calcolo ad altre
celle

Opzioni da settare


18
Expand Only and Collapse Only – Global Option
When Expanding Show Custom Data – Global Option
Domande?
19
© 2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
Scarica

Excel Add-in for Analysis Services