Table of Contents

Open all
Close all
Preface
15
Purpose of This Book
15
How This Book is Organized
15
Overview of the Functions of SAP Analysis for Microsoft Office
17
How to Work with This Book
19
1 Create Your First Workbook
21
1.1 Data Sources as the Basis of Analysis
21
1.2 Inserting Data Sources into a Workbook
23
1.2.1 Inserting Data Sources via the Analysis Tab
23
1.2.2 Inserting Data Sources via the File Tab
29
1.3 Saving Workbooks
30
1.3.1 Selecting Save Settings
31
1.3.2 Removing Data before Saving
34
1.4 Defining a Default Workbook
35
1.5 Opening Saved Workbooks
37
1.6 Adjusting Workbook Name
39
1.7 Deleting a Workbook
40
1.8 Summary
41
2 Navigating in Reports
43
2.1 Working with Current Data
44
2.2 Navigating in Queries
46
2.2.1 Design Panel
46
2.2.2 Analysis View
46
2.2.3 Extending Data Analysis
51
2.2.4 Aggregating Data during Analysis
54
2.2.5 Customizing Data Analysis
55
2.2.6 Working with Background Filters
58
2.2.7 Pause Refresh
72
2.2.8 Undoing Navigation Steps
74
2.2.9 Showing Messages
74
2.2.10 Getting Information about a Data Source
75
2.3 Summary
77
3 Working with Prompts
79
3.1 Getting Started
79
3.2 Prompts Dialog
81
3.2.1 Selecting a Prompt Value
83
3.2.2 Selecting Multiple Values
87
3.2.3 Selecting a Value Range
91
3.2.4 Prompts as Selection Options
92
3.2.5 Working with Hierarchy Variables
93
3.2.6 Working with Formula Variables
95
3.2.7 Texts as Prompts
96
3.3 Using Variants for Prompts
96
3.3.1 Creating a User-Specific Variant
97
3.3.2 Creating a Global Variant
98
3.3.3 Modes for Working with Variants
100
3.3.4 Using a Variant
102
3.3.5 Adjusting a Variant
102
3.3.6 Deleting a Variant
103
3.4 Viewing Selected Values
104
3.5 Summary
106
4 Process Data
107
4.1 Filtering Data
108
4.1.1 Filter Elements
110
4.1.2 Filter Data in Hierarchies
113
4.1.3 Filtering Key Figures
117
4.1.4 Suppressing Zeros
129
4.1.5 Working with Query Conditions
132
4.2 Sorting Data
134
4.2.1 Sorting Elements
135
4.2.2 Sorting Key Figures
137
4.3 Working with Hierarchies
138
4.3.1 Using Characteristics with Hierarchies
138
4.3.2 Navigating in Hierarchies
140
4.3.3 Displaying Characteristics Hierarchically
146
4.4 Customizing the Display of Elements, Key Figures, and Results
152
4.4.1 Customizing the Appearance of Elements
152
4.4.2 Adjusting the Display of Key Figures
154
4.4.3 Customizing the Results Display
159
4.5 Summary
166
5 Organize Data
167
5.1 Calculating New Key Figures
167
5.1.1 Calculation Types
167
5.1.2 Creating Simple Calculations
169
5.1.3 Adding Dynamic Calculations
173
5.1.4 Adding Advanced Calculations
177
5.1.5 Adding Restrictions
182
5.2 Using Comments
184
5.2.1 Properties of Comments
184
5.2.2 Creating Comments
186
5.2.3 Saving Comments
188
5.3 Using the Report-to-Report Interface
190
5.4 Summary
192
6 Working with Planning Applications
193
6.1 Displaying the Planning Tab
194
6.2 Planning Data Manually
196
6.2.1 Changing Existing Data Cells
196
6.2.2 Adding New Data Cells
198
6.3 Working with Planning Functions and Sequences
211
6.3.1 Planning Functions
211
6.3.2 Planning Sequences
218
6.4 Using Change Mode and Display Mode
221
6.4.1 Locking Input-Ready Cells
223
6.4.2 Recalculating Planning Data
225
6.4.3 Undoing Changes to Planning Data
226
6.4.4 Saving Planning Data
227
6.4.5 Setting Work Statuses
227
6.5 Planning-Relevant Settings
229
6.5.1 Determining the Planning System to Save Data
230
6.5.2 Defining Cell Locking Settings
230
6.5.3 Defining the Planning Model
231
6.5.4 Switching Automatically to Change Mode
232
6.5.5 Refreshing Planning Objects
233
6.5.6 Opening a Data Source in Input-Ready Mode
234
6.5.7 Defining the Number of New Lines
234
6.5.8 Determining Rules for the New Line Validation
235
6.6 Summary
235
7 Shape Your Reports
237
7.1 Extending Reports with Local Data
237
7.1.1 Creating a Local Provider
238
7.1.2 Updating Data in a Local Provider
243
7.1.3 Including Local Providers in Reports
245
7.2 Linking Dimensions of Different Reports
248
7.2.1 Linking Dimensions
248
7.2.2 Unlinking Dimensions
250
7.3 Merging Reports
253
7.3.1 Grouping Reports
254
7.3.2 Ungrouping Reports
258
7.4 Important Settings
261
7.4.1 Setting Refresh Behavior
261
7.4.2 Adjusting General Settings
263
7.4.3 Setting Properties of the Data Source
264
7.4.4 Setting the Properties of the Crosstab
267
7.5 Summary
268
8 Format Reports
269
8.1 Adjusting Formatting
269
8.1.1 Working with Conditional Formatting Rules
269
8.1.2 Formatting Cells
279
8.1.3 Adding New Rows and Columns
284
8.1.4 Adding Texts to New Lines
289
8.1.5 Adding Formulas to New Lines
291
8.1.6 More Formatting Options
294
8.2 Managing Design Rules
297
8.3 Working with Styles
298
8.3.1 Creating a Style
299
8.3.2 Applying Styles
302
8.3.3 Exporting Style Sheets
303
8.3.4 Importing Style Sheets
304
8.3.5 Deleting Style Sheets
304
8.4 Extending Workbooks with Diagrams, Info Fields, and Filters
305
8.4.1 Inserting Dynamic Diagrams
305
8.4.2 Inserting Waterfall Diagrams
307
8.4.3 Inserting Info Fields
309
8.4.4 Inserting Filter Components
310
8.5 Summary
312
9 Working with Tools in SAP Analysis for Microsoft Office
313
9.1 Using SAP Analysis for Microsoft Office Tools
313
9.1.1 Converting Cells of a Crosstab into Formulas
313
9.1.2 Creating Web Applications
316
9.1.3 Starting the Query Designer
317
9.2 Working with Views
318
9.2.1 Copying a View
318
9.2.2 Inserting a View
318
9.2.3 Saving a View
318
9.3 Converting a Workbook Created with the SAP BEx Analyzer
320
9.3.1 Converting a Workbook
320
9.3.2 Conversion Settings
323
9.3.3 Conversion Protocol
324
9.4 Protecting Workbooks
325
9.4.1 Activating Protection
325
9.4.2 Removing Protection
326
9.5 Summary
327
10 Working with Formulas
329
10.1 Working with Formula-Optimized Mode
329
10.2 Creating Formulas
331
10.2.1 Basic Formula Creation
331
10.2.2 SAPGetData
333
10.2.3 SAPSelectMember
336
10.2.4 SAPGetUniformScaling
339
10.2.5 SAPGetDimensionDynamicFilter
341
10.2.6 SAPGetDimensionStaticFilter
342
10.2.7 SAPGetDimensionEffectiveFilter
343
10.2.8 SAPGetDimensionInfo
344
10.2.9 SAPGetInfoLabel
346
10.2.10 SAPGetWorkbookInfo
349
10.2.11 SAPGetSourceInfo
350
10.2.12 SAPGetMeasureInfo
352
10.2.13 SAPGetDisplayedMeasures
354
10.2.14 SAPGetMember
354
10.2.15 SAPGetVariable
356
10.2.16 SAPGetMeasureFilter
357
10.2.17 SAPListOfMeasureFilters
358
10.2.18 SAPListOf
360
10.2.19 SAPListOfDimensions
361
10.2.20 SAPListOfDynamicFilters
364
10.2.21 SAPListOfStaticFilters
365
10.2.22 SAPListOfEffectiveFilters
366
10.2.23 SAPListOfMembers
367
10.2.24 SAPListOfMessages
369
10.2.25 SAPListOfVariables
370
10.2.26 SAPSetFilterComponent
372
10.2.27 SAPSetData
375
10.3 Summary
377
11 Using Macros
379
11.1 Creating Macros
380
11.2 Debugging Macros
382
11.2.1 Getting Started
383
11.2.2 Working with Breakpoints
384
11.2.3 Adding a Watch Expression
385
11.3 Syntax Rules for Entering Values
386
11.4 Working with API Methods
388
11.4.1 SAPAddMessage
388
11.4.2 SAPCallMemberSelector
389
11.4.3 SAPSetFilter
395
11.4.4 SAPSetPlanParameter
398
11.4.5 SAPExecutePlanningFunction
400
11.4.6 SAPExecutePlanningSequence
401
11.4.7 SAPGetCellInfo
403
11.4.8 SAPGetProperty
409
11.4.9 SAPLogOff
415
11.4.10 SAPLogon
416
11.4.11 SAPMoveDimension
417
11.4.12 SAPOpenWorkbook
420
11.4.13 SAPSetRefreshBehaviour
423
11.4.14 SAPSetVariable
424
11.4.15 SAPExecuteCommand
425
11.4.16 SAPSuppressMessage
442
11.5 Working with Callbacks
444
11.5.1 Callback Execution
444
11.5.2 AfterRedisplay
445
11.5.3 BeforePlanDataSave
446
11.5.4 BeforePlanDataReset
447
11.5.5 BeforeMessageDisplay
447
11.5.6 BeforeFirstPromptsDisplay
450
11.6 Using Formulas from SAP Analysis for Microsoft Office in Macros
451
11.7 Adjusting Design Rules via Macros
452
11.7.1 SAPSetFormat
452
11.7.2 SAPInsertLine
465
11.7.3 SAPFixLineSize
469
11.7.4 SAPListOfDesignRules
469
11.7.5 SAPDeleteDesignRule
470
11.8 Summary
471
12 Configure SAP Analysis for Microsoft Office
473
12.1 Customizing the User Interface
473
12.1.1 Creating a Profile
474
12.1.2 Customizing Your Profile
476
12.1.3 Setting a Profile as Default
484
12.1.4 Embedding a Profile in a Workbook
485
12.1.5 Setting Up a Company Profile
486
12.1.6 Using Read-Only User Profiles
489
12.2 Options Dialog
490
12.2.1 User Tab
491
12.2.2 Advanced Tab
491
12.2.3 Platform Tab
493
12.2.4 Conversion Tab
495
12.3 Technical Configuration
497
12.3.1 Basics
497
12.3.2 Changing the Technical Configuration
498
12.3.3 Settings for the SAP Analysis for Microsoft Office Add-In
500
12.3.4 Settings for the SAP Analysis for Microsoft Office Plug-In
501
12.4 Summary
502
13 Troubleshooting
503
13.1 Troubleshooting Modes
503
13.1.1 Support Mode
504
13.1.2 Profiling Mode
504
13.1.3 Advanced Mode
506
13.2 Reactivating the SAP Analysis for Microsoft Office Add-In
512
13.3 Summary
513
Appendices
515
A Settings for the SAP Analysis for Microsoft Office Add-in
517
B Settings for the SAP Analysis for Microsoft Office Plug-In
523
C Glossary
547
D The Author
553
Index
555