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.