Business objects PLANNING EXCEL ANALYST 5.2 User Manual

BusinessObjects Planning Excel Analyst User Guide

BusinessObjects Planning Excel Analyst 5.2

Copyright © 2007 Business Objects. All rights reserved. Business Objects owns the following U.S. patents, which may cover products that are offered and licensed by Business Objects: 5,555,403; 6,247,008; 6,289,352; 6,490,593; 6,578,027; 6,768,986; 6,772,409; 6,831,668; 6,882,998; 7,139,766; 7,181,435; 7,181,440 and 7,194,465. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Xcelsius, Crystal Decisions, Intelligent Question, Desktop Intelligence, Crystal Enterprise, Crystal Analysis, Web Intelligence, RapidMarts, and BusinessQuery are trademarks or registered trademarks of Business Objects in the United States and/or other countries. All other names mentioned herein may be trademarks of their respective owners.

Third-party contributors

Business Objects products in this release may contain redistributions of software licensed from third-party contributors. Some of these individual components may also be available under alternative licenses. A partial listing of third-party contributors that have requested or permitted acknowledgments, as well as required notices, can be found at:

http://www.businessobjects.com/thirdparty

Chapter 1 Introduction 9

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Conventions used in this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

About BusinessObjects Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Related documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

About this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Chapter 2 Basic Concepts 15

About BusinessObjects Planning Excel Analyst . . . . . . . . . . . . . . . . . . . . 16

Specifying the application and working language . . . . . . . . . . . . . . . . 16

Viewing version information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Business model properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Currency types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Currency settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Inheritance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Chapter 3 Working with BusinessObjects Planning Reports in Excel 25

Accessing and viewing reports in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Logging on to the server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Working with the Log message workbook . . . . . . . . . . . . . . . . . . . . . . 28

Using the BusinessObjects Planning Excel Analyst toolbar . . . . . . . . . . . . 28

Using the Drill-down toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Opening a report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Specifying a missing dimension using the dimension list . . . . . . . . . . 34

Specifying a missing Time Period dimension . . . . . . . . . . . . . . . . . . . 36

Understanding how report values are determined . . . . . . . . . . . . . . . . . . . 37

Drilling down in a report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Changing the Display currency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

BusinessObjects Planning Excel Analyst User Guide 3

Contents

Launching a task from within a report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

Pivoting a report section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40

Editing data in a report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Updating multi-section reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Copying data between Excel and BusinessObjects Planning Analyst . . . .43

Consolidating report data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

Projecting future performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Using predefined projections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50

Defining a projection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Prorating data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54

Proration methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Distribute Evenly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Leaf Based . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Scenario Based . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

Determining the behavior of prorations . . . . . . . . . . . . . . . . . . . . . . . . .56

Prorating using different scenario types . . . . . . . . . . . . . . . . . . . . .56

Prorating Revisable lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57

Prorating lines that are input within a version . . . . . . . . . . . . . . . . .57

Prorating formula lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Using predefined prorations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58

Defining a proration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59

Saving changes to data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

Saving a report as an Excel file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

Logging off of the server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65

Chapter 4 Working with Ad Hoc Reports in Excel 67

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68

Logging on to the server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69

Using the Log message workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . .70

Writing a Log message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Using the Ad hoc toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71

Creating ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71

Creating ad hoc reports using report areas . . . . . . . . . . . . . . . . . . . . . .72

4 BusinessObjects Planning Excel Analyst User Guide

Contents

Specifying report areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Renaming report areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Specifying a business model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Validating business models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Adding rows to ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Adding columns to ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . 83

Specifying section properties using the Dimension Wizard . . . . . 89

Specifying section properties manually . . . . . . . . . . . . . . . . . . . . . 94

Selecting members from the dimension list . . . . . . . . . . . . . . . . . 96

Specifying time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98

Specifying whether to allow data to be uploaded . . . . . . . . . . . . 100

Validating ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

Clearing validation errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

Clearing validation errors using Excel macros . . . . . . . . . . . . . . 105

Editing data in ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Viewing and highlighting dimensions . . . . . . . . . . . . . . . . . . . . . 106

Creating ad hoc reports using functions . . . . . . . . . . . . . . . . . . . . . . 108

Using the BusinessObjects Planning worksheet functions . . . . . 108

Worksheet function error messages . . . . . . . . . . . . . . . . . . . . . . 110

Opening a BusinessObjects Planning report as an ad hoc report . . . . . . 110

Drilling down in an ad hoc report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Modifying and updating ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Opening ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Modifying the contents of a report area . . . . . . . . . . . . . . . . . . . . . . . 116

Editing an identifier in a cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

Editing a row range using the Dimension Wizard . . . . . . . . . . . . 118

Editing a row range manually . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Shrinking a row range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Editing a column range using the Dimension Wizard . . . . . . . . . 123

Editing a column range manually . . . . . . . . . . . . . . . . . . . . . . . . 126

Shrinking a column range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

Deleting dimensions from reports . . . . . . . . . . . . . . . . . . . . . . . . 128

Scaling data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

BusinessObjects Planning Excel Analyst User Guide 5

Contents

Refreshing ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134

Uploading data from ad hoc reports to the database . . . . . . . . . . . . .134

Uploading and refreshing ad hoc reports . . . . . . . . . . . . . . . . . . . . . .135

Uploading and refreshing ad hoc reports using Excel macros . . . . . .136

Copying ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139

Saving ad hoc reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Logging off of the server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .140

Appendix A The BusinessObjects Planning Worksheet Functions 141

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142

IGETVAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143

IPUTVAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

IGETDIMATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

IGETRATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151

IGETMODATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153

IGETSCENATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Attributes for business objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

Appendix B Time Period Tables 161

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .162

Yearly time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163

Yearly spot balance time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . .164

Half yearly time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164

Quarterly time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166

Quarterly spot balance time periods . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Monthly time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .168

Monthly spot balance time periods . . . . . . . . . . . . . . . . . . . . . . . . . . .172

Weekly time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

Daily time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

Determining the current week . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174

Time periods that reference the global current day of the model . . . .175

Time periods that reference the local current day of the scenario . . . 181

Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187

6 BusinessObjects Planning Excel Analyst User Guide

Contents

Using advanced time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Appendix C BusinessObjects Planning Excel Analyst Hot Keys 193

Index 195

BusinessObjects Planning Excel Analyst User Guide 7

Contents

8 BusinessObjects Planning Excel Analyst User Guide

Introduction

chapter

Introduction

Overview

This guide serves two purposes. It describes how to use BusinessObjects Planning Excel Analyst to access, view, and analyze BusinessObjects Planning reports in a Microsoft Excel environment. It also describes how to use BusinessObjects Planning Excel Analyst to create ad hoc reports that query business rules and data in your environment.

This guide assumes that you know how to use the Windows operating system and Microsoft Excel.

This section discusses the following topics:

• “Conventions used in this guide” on page 10

• “About BusinessObjects Planning” on page 10

• “Related documentation” on page 12

• “About this guide” on page 14

Conventions used in this guide

The following table describes the conventions used in this guide.

When you see… It indicates…

Bold text A name of a user interface item that you should select. For

example, “Right-click a report and select Properties.”

Courier text

OLD SMALL CAPS Specific keys you need to press. For example, when you see

Information you need to type into a data entry field. For example, when you see “Type should type each individual letter key to make up the word

AuthorizationServers.

“Press E keyboard.

NTER”, you should press the ENTER key on your

AuthorizationServers”, you

About BusinessObjects Planning

The BusinessObjects Planning product suite provides Web-enabled, vertical industry-targeted enterprise analytics software that helps companies measure, analyze, and predict business performance and profitability. Organizations leverage the suite for real-time business planning and

10 BusinessObjects Planning Excel Analyst User Guide

Introduction

About BusinessObjects Planning

forecasting, accelerating mergers and acquisitions, understanding business performance by customer segment, product, channel and business line, and delivering performance management information across the enterprise.

BusinessObjects Planning is the only suite that is selectively packaged into a series of applications, each one tailored to support a different segment of the user community. Moreover, every user leverages a common information infrastructure. All user applications are driven by the same set of data, business rules, user rights, and report templates, and any changes are automatically synchronized across the enterprise.

The product suite includes the following applications:

BusinessObjects Planning Administrator

BusinessObjects Planning Administrator allows nontechnical users to rapidly and easily configure, deploy, and administer BusinessObjects Planning applications across multiple sites. From a central site—and leveraging intuitive graphical interface, drag-and-drop function, and advanced automation capabilities—users can install and synchronize geographically dispersed sites, assign user access rights, and build and manage multiple business models.

BusinessObjects Planning Analyst Pro

BusinessObjects Planning Analyst Pro is designed for nontechnical users who have sophisticated information requirements. A comprehensive range of formatting features, and drag-and-drop functions allow users to easily create and maintain reports. In addition, users can quickly build, manage, and execute scripts that automate complex tasks such as scheduled report production and distribution.

BusinessObjects Planning Analyst

BusinessObjects Planning Analyst provides secure remote access to realtime report information anywhere, anytime, through a Web browser . Intelligent graphic indicators, drill-down toolbars, built-in annotation capabilities, forecasting tools, and a sophisticated charting interface allow users to easily view, enter, and edit report data.

BusinessObjects Planning Excel Analyst

BusinessObjects Planning Excel Analyst allows users to leverage advanced analytics, superior performance, and automated information synchronization and distribution capabilities, all from within a familiar Microsoft® Excel environment.

BusinessObjects Planning Excel Analyst User Guide 11

Introduction

About this guide

14 BusinessObjects Planning Excel Analyst User Guide

Basic Concepts

chapter

Basic Concepts

About BusinessObjects Planning Excel Analyst

The BusinessObjects Planning Excel Analyst is a Web-enabled application that gives you the ability, based on your user rights, to access, view, and analyze BusinessObjects Planning reports directly inside Microsoft Excel. The information displayed inside Excel is driven by the same core set of business rules and data that define the information produced in other BusinessObjects Planning applications.

Many of the functions you perform on a report in other BusinessObjects Planning applications can be replicated in Excel. For example, you can drill down on dimensions as easily as you would in other applications, and you can enter or edit plans and forecasts directly into the Excel spreadsheet. For more information, see “Working with BusinessObjects Planning Reports in

Excel” on page 25.

Not only can you view existing BusinessObjects Planning reports using the BusinessObjects Planning Excel Analyst, you can also create ad hoc reports that query business rules and data that reside in your BusinessObjects Planning environment. This gives you the flexibility to incorporate BusinessObjects Planning report information into any spreadsheet file that you create in Excel. However, there are some limitations on what you can do to that information once it has been retrieved. For more information, see

“Working with Ad Hoc Reports in Excel” on page 67.

Specifying the application and working language

BusinessObjects Planning content, such as editable fields in the application, report headings and titles, and metadata, appear in the working language of BusinessObjects Planning Excel Analyst. User interface elements, such as menu items and dialog box text, appear in the application language of BusinessObjects Planning Excel Analyst. You can specify which working and application language you want to use for your BusinessObjects Planning Excel Analyst sessions.

Note: You must be logged on to the BusinessObjects Planning server to

change your application or working language. Changes to the application language will take effect the next time you restart

BusinessObjects Planning Excel Analyst, while changes to the working language will take effect the next time you open a report.

16 BusinessObjects Planning Excel Analyst User Guide



To specify the application or working language:

1. From the BusinessObjects Planning menu, select Languages.

2. In the Languages dialog box, select a language from the Application

language or Working language list.

3. Click OK.

Viewing version information

When you are logged on to the server, you can view version information about BusinessObjects Planning Excel Analyst, BusinessObjects Planning Web Gateway, and BusinessObjects Planning Server.

To view version information, from the BusinessObjects Planning menu, select

Help and BusinessObjects Planning Excel Analyst.

Note: If you are not logged on to the server, you will be prompted to do so.

Basic Concepts

Business model properties

A business model presents a hierarchical structure of the business rules that define how core sets of measures for performance and profitability are calculated across your enterprise at a given point in time. Dimensions, rates, and filters define the business rules in a business model. For more information on how to specify which business model to use in an ad hoc report, see “Specifying a business model” on page 74.

Users who are responsible for creating and maintaining business models are also responsible for setting model properties, some of which can affect reports. For example, if CAD is specified as the Default reporting slice for the model, your reports may access CAD from the database even though you have not explicitly set them up to do so.

BusinessObjects Planning Excel Analyst User Guide 17

Basic Concepts

Dimensions

You cannot change model properties unless you are granted modify rights to the model and have access to BusinessObjects Planning Administrator or BusinessObjects Planning Analyst Pro, but you can override properties at more specific reporting levels (report, column, or row).

One of the most powerful features in BusinessObjects Planning is the ability to store information in a number of different dimensions. A dimension is an overall category of information.

To understand dimensions, think of time. Time is broken down into years, months, days, hours, minutes, and seconds. Without these subcategories, time has no meaning. Nonetheless, time provides a way of understanding all the subcategories at once, as one entity.

There are 10 different dimensions, although most large organizations only take advantage of the ones that are most relevant to their understanding of their organization, such as Unit, Line, and Currency. Each dimension is equipped with intelligent capabilities to facilitate consolidation, report construction, and analysis.

Each dimension is sub-divided into members. A member is an individual component within a dimension. A member that is one level up in a hierarchy from another member is the parent. One or more members (children) can belong to a parent, in which case these members are called siblings. As well, children can be parents to other members. The parent value is typically a sum of the values of all its children.

The following table describes each dimension.

18 BusinessObjects Planning Excel Analyst User Guide

Basic Concepts

Currency types

Dimension Description

Unit Organizes information by line of business, subsidiary, geography, business

activity, or legal entity. Units are planning or reporting components to which you assign costs and revenues.

Line Organizes information according to measurement categories, like financial

accounts. Lines also allow you to measure non-financial performance such as customer retention, channel usage, and transaction counts and/or volumes. The Line dimension has built-in functionality, such as time aggregation, as well as an area to build custom formulas similar to those created in a spreadsheet application.

Currency Organizes currencies as either pure or derived. A pure currency is the

money in circulation. Each pure currency references a rate table, which defines the exchange rate for the currency at a particular point in time. A derived currency is a predefined grouping of currencies that is calculated based on pure currencies.

Balance type Organizes data by booked amounts and adjustments that modify those

amounts. Balance types also allow you to distinguish between As at Spot

balances and Average balances in the same line. Customer Organizes data by customer segment. Product Organizes data by product such as deposits, credit cards, or mortgages. Channel Organizes data by channel such as ABMs, telephone banking, and internet

banking. Project Tracks expenditures and revenues generated by business initiatives such

as sales campaigns, acquisitions, and mergers. Transaction Tracks costs by activity or transaction grouping. Fiscal period Describes how you want to store data for each time period. Y ou define fiscal

periods by version (plan, forecast, or actual) and periodicity (monthly,

quarterly, and annually).

Currency types

There are two main categories of currencies: pure and derived. A pure currency is the money in circulation in a country. Each pure currency references a rate table, which defines the exchange rate for the currency at a particular point in time. A derived currency is calculated based on pure currencies. The following table describes the three main derived currencies.

BusinessObjects Planning Excel Analyst User Guide 19

Basic Concepts

Currency settings

Derived currency

Local Currency (LC)

Booked Currency (BK)

Total All Currencies (TT)

Description Source currency Display currency

Retrieves and displays data using a unit’s default reporting currency.

Displays the currency as unconverted.

Displays a specific value by all of the currencies that make it up.

You normally should not use LC as a Source currency, as the report would only retrieve values stored on the currency slice as specified by one unit’s default reporting currency.

If you set the Source currency to Booked, when the report is opened, the user is prompted to specify a currency. You can also set the Source currency to Ask User to prompt users for a currency.

TT is a frequently used setting for the Source currency. For example, if the Source currency is set to TT, you can drill down to see the value by each pure currency that contributes to the total value.

Y ou should use LC as the Display currency for reports that users edit (for example, those used to collect plan and forecast data). Users do not have to change the report’s Display currency to match units.

Booked is a frequently used setting for the Display currency. For example, if the Source currency is CAD and the Display currency is Booked, the values are retrieved from the CAD slice and displayed as Canadian dollars. No conversion takes place.

You should not use TT as a Display currency.

Note: The default reporting currency is part of a unit’s properties. It is

specified as part of the business model creation and maintenance process. You cannot change it from inside a report.

Currency settings

There are two main currency settings that affect reports: the source currency (also referred to as the currency slice) and the display currency (also referred to as the view currency).

The source currency defines what currency is retrieved from the database. When you set the source currency , you can specify a pure currency like CAD or USD or a derived currency like TT or LC. The display currency defines how

20 BusinessObjects Planning Excel Analyst User Guide

Basic Concepts

Currency settings

database values appear in the report. When you enter data in a report, it is interpreted as the currency you specify as the display currency. If you enter data for currencies that do not match the display currency, the data is converted when it is stored in the database. For examples of how these settings affect the data that you enter, see the table on the next page.

The following table describes how these settings determine what value is displayed in a report.

Source currency

CAD USD N/A The report retrieves only the CAD slice from

TT CAD N/A All currency slices are retrieved from the

LC CAD CAD The CAD slice is retrieved from the database

LC USD CAD The CAD slice is retrieved from the

TT LC CAD All currency slices are retrieved from the

CAD BK N/A The CAD slice is retrieved from the database

TT BK CAD All currency slices are retrieved from the

Display currency

Unit’s Default reporting currency

Result

the database, converts it to and displays it as US dollars.

database and are converted to and displayed as Canadian dollars.

and displayed in Canadian dollars. No conversion takes place.

database, converted to and displayed as US dollars.

database and are converted to and displayed as Canadian dollars.

and displayed in Canadian dollars. No conversion takes place.

database. The total of all the currencies is converted to and displayed as Canadian dollars. If you drill down by currency, the individual values for the currencies are displayed as they are stored in the database. No conversion takes place. In this case, the totals and the individual values will not appear to add up.

If the Source currency is TT and the Display currency is BK, the total value may be displayed using the base currency of the model.

BusinessObjects Planning Excel Analyst User Guide 21

Basic Concepts

Currency settings

The following table describes how the data you enter is interpreted and stored in the database.

Note: The default reporting currency is part of a unit’s properties. It is

specified as part of the business model creation and maintenance process and you cannot change it from inside a report.

Source Currency

CAD USD N/A The data you enter is interpreted as US

TT CAD N/A The data you enter is interpreted as Canadian

LC CAD CAD The data you enter is stored in the CAD slice

LC USD CAD The data you enter is interpreted as US

Display Currency

Unit’s Default reporting currency

Result

dollars. When it is stored in the database, it is converted to its CAD equivalent and stored in the CAD slice of the database.

dollars. If TT is defined as CAD+USD+GBP, you can drill down in your report to reveal the CAD, USD, and GBP slices. The data you enter for CAD is stored in the CAD slice as is, but the data you enter for USD is converted to US dollars and stored in the USD slice, and the data you enter for GBP is converted to pounds and stored in the GBP slice.

as Canadian dollars. No conversion is necessary.

dollars. When it is stored in the database, it is converted to its CAD equivalent and stored in the CAD slice in the database.

22 BusinessObjects Planning Excel Analyst User Guide

Basic Concepts

Inheritance

Source Currency

TT LC CAD The data you enter is interpreted as Canadian

CAD BK N/A The data you enter is stored in the CAD slice

TT BK N/A If TT is defined as CAD+USD+GBP, you can

Display Currency

Unit’s Default reporting currency

Result

as Canadian dollars. No conversion is necessary.

drill down in your report to reveal the CAD, USD, and GBP slices. The data you enter for CAD is stored in the CAD slice as Canadian dollars, the data you enter for USD is stored in the USD slice as US dollars, and the data you enter for GBP is stored in the GBP slice as pounds. No conversion takes place.

Inheritance

Inheritance is the sharing of properties from one level of report attributes to another level. For example, properties that you set at the report level may be inherited in the columns and rows of the report.

In BusinessObjects Planning reports, inheritance of report properties operates from the most general to the most specific levels, as the following list indicates:

Model properties, as the most general properties, are used, except where you have defined...

Site default properties, which are used, except where you have defined... User default properties, which are used, except where you have defined... Report properties, which are used, except where you have defined... Column properties, which are used, except where you have defined... Row properties, which are the most specific properties.

BusinessObjects Planning Excel Analyst User Guide 23

Basic Concepts

Inheritance

Model, site, and user properties are specified using BusinessObjects Planning Administrator and BusinessObjects Planning Analyst and are used if you do not specify a property in the ad hoc report you are creating. However, if you want your user default properties to apply to ad hoc reports, your User default report has to be located in your Public folder. If it is in your Private folder, its settings will not be used.

Inheritance applies to report, column, and row properties, as described above. For example, if you set the display currency to CAD in a column and to USD in a row, USD is used for the intersecting cell.

24 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

chapter

Working with BusinessObjects Planni ng Reports in Excel

Accessing and viewing reports in Excel

Using the BusinessObjects Planning Excel Analyst, you can access, view, and analyze BusinessObjects Planning reports in a Microsoft Excel environment.

When you work with BusinessObjects Planning reports in BusinessObjects Planning Excel Analyst you can:

• Access live BusinessObjects Planning reports and open them in

Microsoft Excel

• Drill down into, view, and edit BusinessObjects Planning reports in Excel

• Distribute information to a wider audience by providing Excel

spreadsheets to non-BusinessObjects Planning users

• Select favorites to navigate to reports

• Open multiple BusinessObjects Planning reports in Excel

The BusinessObjects Planning Excel Analyst does not support:

• The creation or revision of annotations. Cell annotations that exist in the

original BusinessObjects Planning report are read-only, and any annotations created within Excel are not included in the BusinessObjects Planning version of the report.

• Charts created using a BusinessObjects Planning application. However,

you can create charts in Excel using BusinessObjects Planning data.

Note: It is not necessary for a BusinessObjects Planning application to be

running when you use the BusinessObjects Planning Excel Analyst.

26 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Logging on to the server

Before you can open a BusinessObjects Planning report within the Excel environment, you are required to log on to a web server. Once you log on to the server, you have access to a hierarchy of favorites that represent live BusinessObjects Planning reports.

Before attempting to log on, you should know:

• The name of the web server onto which you are logging

• Your user name

• Your domain

• Your domain password (the password you use to log on to Windows)

To log on to the server:



1. From the BusinessObjects Planning menu, select Log On.

2. In the Log On dialog box, type your server name in the Server text box.

3. In the User text box, type your user name.

4. In the Domain text box, type your domain. (If you do not know your

domain, contact your administrator.)

5. In the Password text box, type your password.

Logging on to the server

6. To save this log on profile, select Save this profile. Note: If you select Save this profile, all information entered in this

dialog box is remembered, except for your password. Depending on how your web server is configured to authenticate users, you may not require your domain, name, or password to log on to the server.

7. Click OK.

BusinessObjects Planning Excel Analyst User Guide 27

Working with BusinessObjects Planni ng Reports in Excel

Using the BusinessObjects Planning Excel Analyst tool bar

Working with the Log message workbook

When you first attempt to connect to the server from the BusinessObjects Planning Excel Analyst, a Log message workbook is created. This is a page where any error messages, warnings and faults reported by the server are listed, along with the date and time they were generated.

The information displayed in the Log window is for the current session only. Once you close Excel, the information is lost; therefore, you may want to save the information as a text file before ending a session. To save the contents of the Log window, select Save from the File menu in Excel. In the Save as dialog box, specify a name and location for the text file and click Save.

Y ou can also clea r the information in the Log window. You may want to do this to see the exact information a task generates. If you clear the Log window before performing the task, you know that all messages pertain to that task. To clear the contents of the Log window, right-click the window and select BusinessObjects Planning and Clear Log Window from the menu.

Using the BusinessObjects Planning Excel Analyst toolbar

When you start BusinessObjects Planning Excel Analyst with Excel 2003, you will see the BusinessObjects Planning Excel Analyst toolbar on the screen. To access the toolbar with Excel 2007, you must first click on the Add-Ins tab. The toolbar contains some of the operations you will perform during a typical BusinessObjects Planning Excel Analyst session.

Icon Tool Tip Description

Open Report Opens a report.

Edit Edits a report cell-by-cell.

Fast Edit Edits a range of cells.

Consolidate Consolidates the data for scenarios that are in

parent level units, and displays the updated values in the report.

28 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Icon Tool Tip Description

Expand Drills down through a selected row of your

report.

Collapse Collapses a row you have drilled down.

Report Options Changes the view currency.

Environment Hides the grid lines and row and column

headings. If the grid lines and headings are hidden, clicking displays them again.

Zoom In Enlarges the view of your report. The report is

at its maximum view when it is first imported into Excel.

Zoom Out Reduces the view of your report.

Using the Drill-down toolbar

BusinessObjects Planning Help

Note: Additional BusinessObjects Planning Excel Analyst commands are

available using the BusinessObjects Planning Menu and the right-click menu. The BusinessObjects Planning Menu is located in the menu bar at the top of the Excel 2003 interface and under the Add-ins tab in Excel 2007. You can right-click anywhere within the spreadsheet area to produce the right-click menu, which includes BusinessObjects Planning options.

Accesses online help for the BusinessObjects Planning Excel Analyst.

Using the Drill-down toolbar

You use the drill-down toolbar to navigate in a report (for example, to expand rows and columns, or to summarize rows and columns).

BusinessObjects Planning Excel Analyst User Guide 29

Working with BusinessObjects Planni ng Reports in Excel

Using the Drill-down toolbar

Icon Tool Tip Description

Formula If the selected row of a report contains a formula, drill

down to reveal the rows that are used in the formula.

Related Line Drill down on a related line to see its associated line.

For example, you could drill down on an Interest Earned line to see an Asset line, or you could drill down on an Asset line to see tis related Interest Earned lines.

Unit Drill down from high level units to lower level units.

Units are the basic organization framework for business models in BusinessObjects Planning. You can use different unit hierarchies to represent alternative views of your organization, such as line of business and geography.

For example, if your units are organized by geography, drill down on the data for a high level unit like North America to see data for a lower level unit like New York City.

Scenario If the report contains calculated scenario data, drill

down and see the pre-calculated amount.

Line Drill down on Heading and Formula lines to see the

underlying data. For example, you could drill down on Total Revenues and find Net Interest Income, Fees and Commissions, and Other Income.

Currency If the report retrieves a derived currency, such as the

total of all currencies, drill down to see each pure currency that makes up that derived currency.

Balance Type If the report retrieves a derived balance type, such as

Net After Adjustment, drill down to see the break down of data for the booked balance and the adjustment.

Product Drill down on derived products to see the underlying

data.

Project Drill down on derived projects to see the underlying

data.

30 BusinessObjects Planning Excel Analyst User Guide

Icon Tool Tip Description

Channel Drill down on derived channels to see the underlying

data.

Customer Drill down on derived customers to see the underlying

data.

Transaction Drill down on derived transactions to see the

underlying data.

Time Period Drill down on derived time periods to see the

underlying data.

Opening a report

Once you connect to the server, you can select a report from the Open Report dialog box. This dialog box displays the reports that are stored in your Favorites folder in BusinessObjects Planning Administrator and BusinessObjects Planning Analyst Pro.

When the report opens, it displays a Report Summary page. Each report has a default summary page that contains information about that report, including a list of the section names if it is a multi-section report. The section names on the Report Summary page are links to the tabs in which the sections are stored. Tabs cannot be renamed in Excel, but you can change the order of tabs by dragging them to a new position.

Excel places certain restrictions on BusinessObjects Planning reports:

• If there is a BusinessObjects Planning report tab named "Summary," an

extension of "_1" is added to the name to distinguish it from the BusinessObjects Planning Excel Analyst summary tab. For example, the tab will be called "Summary_01."

• Section names are restricted to 31 characters. Section names longer

than 31 characters are truncated and replaced by ellipses (...).

• Section names cannot contain characters such as (,),*,\,?,'. These

characters will be removed from a BusinessObjects Planning report opened in Excel.

Working with BusinessObjects Planning Reports in Excel

Opening a report

BusinessObjects Planning Excel Analyst User Guide 31

Working with BusinessObjects Planni ng Reports in Excel

Opening a report

When you click a section name, or its corresponding tab, the section is displayed and only its data is loaded. Data is not loaded for a section until you access it.

Note: To reopen a report that you have recently opened, from the

BusinessObjects Planning menu, select Open and the report name.

T o open a report:



1. On the BusinessObjects Planning Excel Analyst toolbar, click (Open

Report). You may be prompted to log on if you have not already done so.

2. In the Open Report dialog box, ensure that the Open as an Ad hoc

report check box is cleared.

Note: For information on opening a BusinessObjects Planning report as

an ad hoc report, see “Opening a BusinessObjects Planning report as an

ad hoc report” on page 110.

3. Select the report you want to open. (You may have to expand the

hierarchy to see all available reports.)

32 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Opening a report

4. If the Select Report Dimensions wizard appears, specify members for the

missing dimensions. The wizard indicates how many dimensions need to be specified.

If you do not have any missing dimensions, skip to Step 8.

5. If your missing dimension is a dimension other than Time Period or

Scenario, type the member identifier in the text box provided, and press

AB.

T OR

Click to select a member from a dimension list. See “Specifying a

missing dimension using the dimension list” on page 34 for more

information on the dimension list.

6. If your missing dimension is a Time Period, specify the time period

components in the boxes provided. See “Specifying a missing Time

Period dimension” on page 36 for more information on specifying a

missing time period.

7. If your missing dimension is a Scenario, type the member identifier in the

text box provided, and press T

AB.

BusinessObjects Planning Excel Analyst User Guide 33

Working with BusinessObjects Planni ng Reports in Excel

Opening a report

Click Browse to select a scenario from the list of available scenarios for the report’s Unit dimension. In the Scenario Browser, select a scenario, and click OK.

8. On the Report Summary page, click a report section.

Specifying a missing dimension using the dimension list

When you are opening a report, and are using the Dimension Wizard to select a member for a missing dimension, you can click to view a list of all

members of that dimension. You can then select the member from this dimension list.

34 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Opening a report

From the dimension list, you can select a member by either expanding the dimension tree provided or searching for a member in the dimension tree. To

expand a portion of the dimension tree, click the button located to the left of a displayed member. This displays the children of the member.

When searching for a member in the dimension tree, you can search by member name or member ID. When searching, you can specify any or all of the following limitations:

• The search must match the entire word

• The matched member must start with the text specified in the search

• The matched member must be the same case as the text specified in the

search; for example, “TEXT” only matches “TEXT”, not “text” or “Text”. (This option is only available if you are searching by member name.)

By default, a search matches a member if the search text is contained anywhere in the member name or identifier.

To select a member from the dimension list:



1. In the Dimension Wizard, click to display the dimension list.

2. Select a member from the dimension hierarchy displayed. If the member

you want to select is not displayed, and you know which part of the dimension hierarchy contains the member, click as necessary to

display the member. OR

BusinessObjects Planning Excel Analyst User Guide 35

Working with BusinessObjects Planni ng Reports in Excel

Opening a report

In the Search for dimension member text box, enter some or all of a member name or identifier, and click Find. To repeat the search after finding a member, click Find Next.

3. Click OK to select the member.

Specifying a missing Time Period dimension

When you are opening a report, and are using the Dimension Wizard to select a member for a missing Time Period dimension, you are asked to supply the three components of the time period identifier: a year, a version, and the time period itself. These components are separated by period characters. An example of a valid time period identifier is 2002.A.M3 (which is the 2002 monthly actuals for month 3).

The following table describes the time period identifier components in more detail.

Time Period Identifier Component

Year range Any four-digit year, such as 2003, or one of the

Version One of the following:

Time Period A valid time period. For details on time periods, see

The Dimension Wizard contains boxes for each of these time period identifier components:

Description

following: Current The current year Scenario The scenario’s year

Scenario The scenario’s version Actual Actual data Forecast Forecasted data Plan Planned data

“Time Period Tables” on page 161.

36 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Understanding how report values are determined



To specify a time period:

1. From the Year range list, select a year.

2. From the Version list, select a version.

3. In the Time period text box, type a time period.

OR Click Browse and select a time period from the list that appears.

If necessary, expand the hierarchy to find the time period you want.

Understanding how report values are determined

When looking at the data in a report, it is important that you understand how the values are determined. For example, if a report has a variance row and a variance column, the intersecting cell will contain the value for the variance column. This is determined by predefined precedence rules.

BusinessObjects Planning Excel Analyst User Guide 37

Working with BusinessObjects Planni ng Reports in Excel

Drilling down in a report

The following table describes these rules:

Row type Column type

Text Band & Trend Variance Formula Data

Text The cell is

left blank.

Band & Trend Text Band & Trend

Variance Text Band & Trend Variance (col) Formula Variance Formula Text Band & Trend Variance Formula (col) Formula Data Text Band & Trend Variance Formula Data

Note: For the intersection of Data and Data, the existing precedence rules

apply. If a dimension is explicitly set in the Row template, it will override anything set in the Column Template (or higher). Otherwise, the dimension is inherited.

Text Text Text Text

Band & Trend Band & Tre nd Band & Trend

(col)

Drilling down in a report

One of the key differences between viewing and working with BusinessObjects Planning reports instead of a standard spreadsheet program is the ability to drill down rows by dimensions. This allows you to narrow your focus on selected data, displaying its underlying details.

Whether you can drill down is determined by:

• The dimensions in the business model. If the business model contains

two or three dimensions only, your choices for drilling down are limited. However, if your business model includes several dimensions (for example, unit, line, currency, product, and channel), your choices are considerable.

• The type of data in the report. You can either drill from summary data for

high-level members to more detailed data for lower level members, or you can drill from calculated data to pure data.

When drilling down on a row, you should be aware of the following behavior:

• When you drill down on a calculated row (for example, a variance row),

the rows used in the calculation are displayed. You will get the same result no matter which dimension you drill down. You can then drill down on the rows used in the calculation by a particular dimension to see the details.

38 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Changing the Display currency

• If you drill down on a row by currency, and the resulting currencies have

N/A values, the total of the currencies may be zero.

• Depending on the report settings, if your hierarchy contains calculated

formulas, drilling to leaf may stop at the calculated formula's component members. You could then drill to leaf on the component members to display their lowest-level details.

• When you drill down on a row, the report settings control whether detail

rows that contain only zeroes are displayed.

To drill down through a report:



1. On the Drill-down toolbar, click the butt on for the dimension by which you

want to drill down. For example, to drill down by Line, click (Line).

2. Right-click a row and select BusinessObjects Planning, Expand to, and

one of the following menu options:

• Leaf Level – expands only the lowest level member of the selected

row

• One Level – expands the selected row by one level

Note: You can collapse a row by right-clicking it and selecting

BusinessObjects Planning and Collapse from the menu.

Changing the Display currency

The Display currency defines how database values appear in the report. When you enter data in a report, it is interpreted as the currency you specified as the Display currency. If you enter data for currencies that do not match the Display currency, the data is converted when it is stored in the database.

For example, the Source currency for a report could be set to CAD and the Display currency could be set to USD. In this case, the report retrieves values only from the CAD slice in the database and converts them to, and displays them in, US dollars. If you edit the data for this report, the values you enter are shown in US dollars. When stored in the database, they are converted to Canadian dollars and stored in the CAD slice.

Note: Another name for Display currency is View currency. For more

information on the display currency, see “Currency settings” on page 20.

To change the Display currency:



1. On the BusinessObjects Planning Excel Analyst toolbar , click (Report

Options).

BusinessObjects Planning Excel Analyst User Guide 39

Working with BusinessObjects Planni ng Reports in Excel

Launching a task from within a report

2. In the Report Options dialog box, select the currency in which you want to

view the report. (You may have to expand the hierarchy to see all available currencies.)

3. Click OK.

Launching a task from within a report

You can launch a task (also referred to as a script) from inside a report and see its effect immediately. For this feature to work, a Site Administrator has to set up the task for that report.

To run a task, in an open report, right-click a data cell and select BusinessObjects Planning, Tasks, and the name of the task from the menu.

Note: You cannot run tasks that use dialog boxes in the BusinessObjects

Planning Excel Analyst.

Pivoting a report section

You can pivot a report section. This means that the columns and the rows for a report section are switched.

To pivot a report, the rows or columns cannot be drilled down, the row template must not have page breaks, and the column template cannot contain super-titles.

40 BusinessObjects Planning Excel Analyst User Guide

To pivot a report section, right-click a cell in the report section, and select BusinessObjects Planning and Pivot Section from the menu.

Editing data in a report

When you edit data, you are actually editing scenarios. A scenario is a collection of BusinessObjects Planning data that is stored in a database.

There are two ways in which you can edit data: regular edit mode and fast edit mode. In regular edit mode, each cell’s existing value is updated by the BusinessObjects Planning server before you edit it. In fast edit mode, the cells you edit do not have their values updated by the server as you edit them, which allows you to edit more quickly.

In either mode, the database is not updated until you save the report data. For information on saving report data, see “Saving changes to data” on page 64.

Note: You can only be in edit mode, whether it be regular or fast, in a single

section of a report at one time. You must exit edit mode before switching to a different section of a report. For more information, see “Updating multi-section

reports” on page 42.

To edit data, you must meet the following conditions:

• Data can only be edited in Excel if the equivalent data can be edited in

the BusinessObjects Planning version of the report. In particula r, you can only edit data at the input level.

• Depending on your site's configuration, only one user may be able to edit

a cell at a time. If concurrent editing of scenarios is allowed at your site, multiple users can simultaneously edit the data contained in a scenario.

You can include additional text at the bottom of a report for reference purposes, but any text added in Excel will not appear in the BusinessObjects Planning version of the report. You can also annotate cells, but annotations created in Excel will not appear in the BusinessObjects Planning version of the report. Annotations that are a part of the original BusinessObjects Planning report can be viewed but not edited in Excel.

If the cell is associated with a related line, you can enter the value as either a dollar amount or a rate. To enter a rate value, type a percentage sign before the cell data. Data that is not preceded by a percentage sign defaults to a dollar value. To enter an N/A value, type N/A, na, n/a, or (na) into the report cell.

Unlike entering zero, entering N/A ensures the value of the cell is ignored in calculations. This is critical when performing, in particular, statistical calculations where a ‘0’ value could significantly alter the outcome of the calculation.

Working with BusinessObjects Planning Reports in Excel

Editing data in a report

BusinessObjects Planning Excel Analyst User Guide 41

Working with BusinessObjects Planni ng Reports in Excel

Editing data in a report

The changes you make do not immediately appear in the parent level units of the report or in other reports referencing the same data. You have to consolidate the data to update the data in parent level Units. For information on consolidating data, see “Consolidating report data” on page 43.

Note: The changes you make do not immediately appear in other sections of

the report.

To edit data in regular edit mode:



1. On the BusinessObjects Planning Excel Analyst toolbar, click (Edit).

2. Click inside the cell you want to edit, and begin editing the cell data.

3. Press E

4. Repeat steps 2 and 3 for each cell that you want to edit.

5. To exit regular edit mode, release (Edit).

To edit data in fast edit mode:



1. On the BusinessObjects Planning Excel Analyst toolbar, click (Fast

Edit).

2. Click inside the cell you want to edit, and begin editing the cell data.

Repeat this step for any number of cells. You can use the tab key or the up and down arrows on your keyboard, as well as the mouse, to navigate from cell to cell.

Note: You can cut and paste entire ranges of cells in fast edit mode.

NTER.

3. To exit fast edit mode, click (Fast Edit).

Updating multi-section reports

When using the BusinessObjects Planning Excel Analyst, each section of a multi-section report is updated separately. If you change data in one section, other sections of your report are not automatically updated to reflect this change.

If you want to see your changes to report data migrated to all report sections, you must close the report and reopen it. The only exception to this is if a change affects a report section that you have not yet viewed. In this case, you will see the latest updates when you view the section for the first time, as the BusinessObjects Planning Excel Analyst only retrieves report section data when you first view it.

42 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Copying data between Excel and BusinessObjects Planning Analyst

You can copy and paste a range of cells from Excel to BusinessObjects Planning Analyst and from BusinessObjects Planning Analyst to Excel. If you are copying from BusinessObjects Planning Analyst to the BusinessObjects Planning Excel Analyst, you must start Excel before beginning the copy operation.

Note: To copy from Excel to BusinessObjects Planning Analyst, you must be

in Fast Edit mode in BusinessObjects Planning Analyst before pasting cell data into a report. To copy from BusinessObjects Planning Analyst to the BusinessObjects Planning Excel Analyst, you must be editing data in the BusinessObjects Planning Excel Analyst before pasting cell data into a report.

To copy data from Excel to BusinessObject s Planning Analyst:



1. In the report you want to copy from, select a range of cells.

2. Right-click the cells, and select Copy from the menu.

3. In the report you want to copy to, right-click anywhere in the report and

select Paste from the menu.

Consolidating report data

If you have made changes to the data in a report, you can consolidate it, which updates the data for scenarios that are in parent level units and displays the updated values in the report. The consolidation only affects the units that you have open in the report. Any scenarios that you have edited are checked in before consolidation takes place.

Consolidating may be time-intensive, depending on the units affected. To reduce the consolidation time, you can choose which units and scenarios to consolidate.

The Size field of the consolidation dialog box contains an integer value that provides an estimate of the time required to consolidate the unit and scenario. A Size value of “OK” indicates a scenario that is not a parent-level unit, and cannot be consolidated.

The Size value also indicates whether you have the rights to consolidate a unit and scenario. If you do not have the rights to consolidate a scenario, the Size value is enclosed in parentheses. If you have partial rights to consolidate

BusinessObjects Planning Excel Analyst User Guide 43

Working with BusinessObjects Planni ng Reports in Excel

Consolidating report data

a scenario, the Size value is marked with an asterisk; this normally happens when your scenario is a Compound scenario, and you have rights on only some of the Compound scenario's component scenarios.

T o co nso lid ate data:



1. On the BusinessObjects Planning Excel Analyst toolbar, click

(Consolidate).

2. In the dialog box that appears, click OK to check in any checked-out

scenarios used by the report.

3. In the Warning dialog box, click Details.

4. From the list, select the units and scenarios you want to consolidate.

Note: Scenarios with a size of “OK” cannot be selected because they

are not parent level units.

5. Click OK.

44 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Projecting future performance

In planning and forecast reports, you can define and use projections, which calculate future data values based on past data.

The following example shows a report that calculates values for May, June, July, and August based on values from January, February, March, and April. The projection method used in this example is Run Rates, which calculates the average value from January through April and projects that value forward.

Projecting future performance

Note: For information on specifying projection methods, see “Using

predefined projections” on page 50 and “Defining a projection” on page 50.

The following table describes the projection methods that you can use to calculate future values:

BusinessObjects Planning Excel Analyst User Guide 45

Working with BusinessObjects Planni ng Reports in Excel

Projecting future performance

Projection method

Run Rates Use this method when the source data is relatively constant from period to

Linear Trend Use this method when you expect that the projected data will change by

Growth Trend Use this method when you know that there is, or has been, increasing or

Description

period, and this average run rate is expected to continue into the projection period.

This method projects the average value of historical data. For example, if you select four source time periods, the target time

periods all display the same value, which is an average of the four source time periods.

Source 4 5 4 5

the same absolute amount in each time period, based on the trend in the source range.

This method produces a straight line rather than the increasing or decreasing curve produced using the Growth Trend method.

If the values of the source time periods are not linear, this method smoothes out the trend to produce the straight line for the target time periods.

Source 100 150 250 200

decreasing growth in past periods. This method produces an increasing or decreasing curve rather than the

straight line produced using the Linear Trend method. If the values of the source time periods are increasing over time, this

method produces an upward curve. If the values are declining, this method produces a downward curve.

The values of the source time periods must be positive numbers. Source

100 150 200 250 OR 200 150 150 125

Target

4.5 4.5 4.5 4.5

Target 275 315 355 395

Target

353.55 478.99 648.94 879.19

108.25 94.02 81.65 70.91

46 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Projecting future performance

Projection method

Linear Series Use this method when you expect a fixed amount of growth in each

Growth Series Use this method when you expect that the data will change by the same

Seasonal Use this method when an annual seasonal pattern exists and you want to

Description

period. This method uses an additive growth factor to generate forecasted

values. If you select more than one source time period, the last source time period

is used to calculate the value for the first target time period. The next target time period is calculated as the value of the first target time period plus the additive growth factor, and so on.

For example, if the last source time period you select has a value of 12,000 and you specify the Linear Series growth factor to be equal to 100, the values for four target time periods will be 12,100, 12,200, 12,300, and 12,400.

Source 9,000 12,000

percentage from one period to the next. For each target time period, the value of the previous time period is multiplied by the percentage growth factor to generate the projected value.

If you select more than one source time period, the last source time period is used to calculate a value for the first target time period. The value of the first target time period is then used to calculate the value for the second target time period, and so on.

For example, if the last source time period you select has a value of 12,000 and if you specify the Growth Series growth factor to be equal to

1.1 (a 10% increase), the values for four target time periods will be 13,200, 14,520, 15,972, and 17,569.

Source 9,000 12,000

reflect the same pattern in the projection period, along with some growth. This method applies a growth factor to each source time period to

generate forecasted values. (This method requires that you have equal numbers of source and target time periods.)

For example, if two source time periods have values of 10,000 and 12,000, and you specify the Seasonal growth factor to be equal to 1.1 (10%), the two target time periods will have values of 11,000 and 13,200.

Source 10,000 12,000

Target 12,100 12,200 12,300 12,400

Target 13,200 14,520 15,972 17,569

Target 11,000 13,200

BusinessObjects Planning Excel Analyst User Guide 47

Working with BusinessObjects Planni ng Reports in Excel

Projecting future performance

Projection method

Naive Forecasting Use this method to provide users with a base case for projecting items

Description

that are not expected to change significantly year over year. This method uses the values of the source time periods to generate the

forecasted values. The number of source time periods must be greater than or equal to the number of target time periods.

For example, if four source time periods have values of 100, 150, 200, and 250 respectively, the four target time periods will have values of 100, 150, 200, and 250 respectively.

Source 100 150 200 250

Target 100 150 200 250

48 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Projecting future performance

Projection method

Moving Average Smoothes out past data by averaging the last several periods and

Single Exponential Smoothing (SES)

Description

projecting that value forward. The number of source time periods you choose determines the number of

time periods used to calculate each projected value. For example, if you choose four source time periods, each projected value is an average of the previous four time periods.

For example, suppose that you have four source time periods with values of 100, 150, 200, and 250 respectively. Your first target time period will have a value of 175, which is the average of the source time periods. The next target time period will have a value of 193.75, which is the average of the last three source time periods and the first target time period. Subsequent target time periods will be the average of the last four time periods calculated.

Source 100 150 200 250

Projects future values by calculating a weighted average. The smoothing value determines how the weighting occurs.

The smoothing value must be between 0 and 1. If you specify 0, 100% of the weighting is applied to the value for the last time period. If you specify 1, 100% of the weighting is applied to the value for the first time period. A smoothing value closer to 1 places more emphasis on values for older time periods, and a smoothing value closer to 0 places more emphasis on values for more recent time periods. This allows you to specify whether the projected values should be more sensitive to recent changes in the data or more sensitive to older data values.

For example, suppose the source time periods are 500, 600, 700, and 1,000. If you specify a smoothing value of 0.2, the more recent source time periods will have a greater weighting than the earlier values. As a result, the projected value will be greater than the non-weighted average of the source time periods because the last two source time periods have larger values. However, if you specify a smoothing value of 0.8, the older source time periods have greater weighting. As a result, the projected value is less because the values of the older time periods are smaller.

Smoothing value = 0.2 Source 500, 600, 700, 1,000 Smoothing value = 0.8 Source 500, 600, 700, 1,000

Target 175 193.75 204.69 205.86

Target 935, 935, 935, 935

Target 645, 645, 645, 645

BusinessObjects Planning Excel Analyst User Guide 49

Working with BusinessObjects Planni ng Reports in Excel

Projecting future performance

Using predefined projections

Y ou can project values in a BusinessObjects Planning report only if the report creator enables projections for the section of the report that you are viewing. When projections are enabled, the report creator may have defined one or more projection methods for use in the report.

To project values using a predefined projection method, select one or more rows of the report, right-click a selected row, and select BusinessObjects Planning, Projection and the name of the projection from the menu.

If you cannot select Projection, projections have not been enabled for your report.

Projection is only possible if the report has not been pivoted. The report creator may also have allowed you to define your own projection

method for the report. For more information on defining your own projection method, see “Defining a projection” on page 50. For information on how to enable projections for a report, see the Reporting Guide.

Defining a projection

You can define projections for your report if the report creator has granted permission to do so. When you define a projection, you are specifying the time periods on which to base the projection, the projection method to use, and the time periods in which to project values.

For best results:

50 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Projecting future performance

• Make sure the column template contains time periods

• Use as many source time periods in your historical data set as possible

And remember that:

• You can hide the columns containing the source time periods to make the

report easier to read

• The data in the report has to be editable

T o define a projection:



1. Select one or more rows of the report.

2. Right-click a selected row, and select BusinessObjects Planning,

Projection, and Define Projection from the menu.

Note: If Define Projection is not available, you may not have

permission to define projections for this report. See the Reporting Guide for more information on projection permissions.

BusinessObjects Planning Excel Analyst User Guide 51

Working with BusinessObjects Planni ng Reports in Excel

Projecting future performance

3. In the Projection Input Parameters dialog box, select the time periods on

which you want to base the projection. (The list contains all of the time periods in the column template.)

4. Specify whether you want to apply the projection to selected items only or

to all items in the section. (This setting determines whether the projection is applied to the rows you have selected or to the entire section.)

5. Click Next.

6. In the Projections Methods dialog box, select a projection option from the

Method list. (For a description of the methods, see “Projecting future

performance” on page 45.)

52 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Projecting future performance

7. If the projection method you select requires parameters, specify them in

the Projection parameters text boxes.

8. Click Next.

9. In the Projection Output Parameters dialog box, select the time periods to

which you want to project future values.

Note: After you have specified the default projection settings, if you

delete the columns you have specified as the source or target, you will not be able to repeat the projection.

BusinessObjects Planning Excel Analyst User Guide 53

Working with BusinessObjects Planni ng Reports in Excel

Prorating data

10. To specify an alternative balance type in which to store the future values,

click the Write incremental values to selected balancetype option, then choose a balance type from the list. (You may want to select an alternative balance type if the output time periods you select contain actual data. In this way, you ensure that you do not overwrite actuals.)

To select a balance type from this dimension list, follow the steps described in “Specifying a missing dimension using the dimension list” on

page 34.

11. Click Project Now to perform the projection.

Prorating data

In planning and forecast reports, you can enter or adjust data at a summary level and then prorate those values down to a detailed level.

When you are working with prorations, keep the following points in mind:

• The proration feature is enabled at the section level. If you want to

prorate data, prorations must be enabled in each section you want to use.

• To use prorations, the section must contain time periods in the column

template.

• If you perform a proration on units, you will not see the results until you

consolidate the data.

54 BusinessObjects Planning Excel Analyst User Guide

Proration methods

You can choose how you want to prorate data using three proration methods: Distribute Evenly, Leaf Based, and Scenario Based. The values distributed to each member of the target scenario depend on the proration method you choose.

Distribute Evenly

When using the Distribute Evenly proration method, a value, which you define, is divided evenly from the target data cells at the parent level, then distributed down to the leaf-level members.

In the following example, 8000 was entered as the value to prorate over Total Income for October 2002. Using Distribute Evenly proration, 8000 is distributed evenly to each child member of Total Income. Since there are two child members of Total Income, each receives a value of 4000. The value of 4000 is then distributed evenly to each child member of License Fee and Support.

Working with BusinessObjects Planning Reports in Excel

Prorating data

Leaf Based

When using Leaf Based proration, a value, which you define, is divided among the target data cells at the leaf level using the same ratio as the target scenario. The values at the leaf level are then rolled up to the parent level.

In the following example, 6000 was entered as the value to prorate over Total Income for October 2002. Using Leaf Based proration, 6000 is distributed to the leaf-level members. Since there are six leaf-level members, each receives a value of 1000. These values are then rolled up to the parent level to total

6000.

BusinessObjects Planning Excel Analyst User Guide 55

Working with BusinessObjects Planni ng Reports in Excel

Prorating data

Scenario Based

When using the Scenario Based proration method, the value of the source data cell is divided among the target data cells using the same ratio as another scenario.

In the following example, Investments Securities Held to Maturity is the parent line of the other lines in the report. The prorated value of 1,000,000 is divided among the child lines for each month in 2003. In this example, the prorated value is divided based on the monthly results from 2003.

Determining the behavior of prorations

When prorating data, the results of your proration depend on several factors, including the type of scenario associated with the report and the type of lines contained in the report.

Prorating using different scenario types

The behavior of your proration depends on the type of scenario associated with the report, as described in the following table.

56 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Prorating data

Scenario type Proration behavior across units

Editable The proration is applied across the target time periods and down to the

leaf-level members. When you consolidate, the values in the leaf-level members are rolled up to their parents to give the total for each target time period.

Rollup The proration is applied to the unit dimension members specified in the

consolidation formula for the scenario, provided the report scenario for the leaf is Editable. Normally, these will be the leaf-level descendants of the unit.

Adjustments entered using an adjustment rollup balance type are cleared. The Rollup scenario consolidates the values of the Editable

scenarios that are below it in the unit hierarchy. Calculated Proration cannot be performed. Compound The source time period for the proration (specified in the Proration

Input Parameters dialog box) indicates the scenario on which the

proration is to be performed. If this scenario is an Editable or a Rollup

scenario, the proration is performed as previously described. If this

scenario is a Calculated scenario, the proration is not performed.

Prorating Revisable lines

When prorating revisable lines, the results of the proration may differ depending on the proration method you choose and the type of data cells you are prorating. The following rules apply:

• When prorating revisable lines using Scenario Based proration, if the

source data cell is revised, the target data cell will be treated as a revised cell, and the value is not prorated to the children of that member. If the source data cell is not revised and the target data cell is revised, the target data cell is treated as not revised and the target data cell and its children are prorated.

• When prorating revisable lines using Leaf Based or Distribute Evenly

proration, a revisable target data cell is always treated as not revised and the value is prorated to the children members.

For information on Revisable lines, see the Administrator’s Guide.

Prorating lines that are input within a version

When prorating lines that are input within a scenario version, such as Actual, Forecast, or Plan, the results of the proration may differ depending on the proration method you choose and the type of data cells you are prorating. The following rules apply:

BusinessObjects Planning Excel Analyst User Guide 57

Working with BusinessObjects Planni ng Reports in Excel

Prorating data

• When prorating lines that are input within a version using any proration

method, if the target data cell is input, the prorated value is not distributed to the child members of the target data cell.

• When prorating lines that are input within a version using scenario based

proration, if the source data cell is input and the target is a formula, leaf based proration is always used to prorate values to the child members of the target data cell.

For information on Input Within lines, see the Administrator’s Guide.

Prorating formula lines

Y ou can prorate formula lines that contain other formula lines, in put lines, and rates. The formula lines you prorate can use addition, subtraction, multiplication, and division; however, they can contain one operator only. For example, you can prorate a formula line that uses multiplication only, but you cannot prorate a formula line that contains both multiplication and division. If you want to prorate more complex formulas, you can do so by referencing other formula lines within the formula of the line that you are prorating.

Using predefined prorations

You can prorate in a BusinessObjects Planning report only if the report creator enables prorations for the section of the report that you are viewing. When prorations are enabled, the report creator has defined one or more proration methods for use in the report.

To prorate values using a predefined proration method, select one or more rows of the report, right-click a selected row, and select BusinessObjects Planning, Proration and the name of the proration from the menu.

58 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Prorating data

Proration is only possible if the report has not been pivoted. The report creator may also have allowed you to define your own proration

method for the report. For more information on defining your own proration method, see “Defining a proration” on page 59. For information on how to enable prorations for a report, see the Reporting Guide.

Defining a proration

You can define prorations for your report if the report creator has granted permission to do so.

When you define a proration, you are specifying the source time period, the proration method to use, the value to prorate, and the target time periods for the prorated data. For example, if you expect deposits to increase by 10% across the board, you can define a proration that increases a current source value by 10% and prorates this increased value on all children of the source value. Report users can then use this proration instead of having to define their own.

The following is a list of rules that apply to the value you choose to prorate:

• If you do not specify a value to prorate (you leave it blank), the existing

source value is prorated.

BusinessObjects Planning Excel Analyst User Guide 59

Working with BusinessObjects Planni ng Reports in Excel

Prorating data

• You can enter the value to prorate as a negative amount by typing =-

before the value. For example, if the value you want to prorate is 1,000,000, type

=-1000000.

• You can also specify that you want the existing source value to increase

or decrease by an absolute amount or a percent. For example, if the existing source value is 100,000, and you want to prorate 150,000, type

+50000. If the existing source value is 100,000 and you want to prorate a

value that is 10% less, type

-10%. In this case, a value of 90,000 is prorated.

When prorating data, you select a source and target time period. A source time period can be:

• A derived time period (for example, a full year p lan) in the same scenario

as the target time periods. You would do this if you know the full-year plan amount and want to distribute this to each month.

• A derived time period (for example, full year actual) in a different scenario

than the target time periods. You would do this if you wanted to increase a plan by a certain percentage over last year’s actual data.

• An input time period (for example, January plan) that is the same as the

target time period. You would do this if you wanted to adjust the January plan by $100,000.

The target time period receives a partial amount of the total prorated value. The amount the target receives is determined by the proration method. Selecting a derived time period distributes a partial amount to each of its leaf time periods. For example, if you select a full-year time period, all of the months for that year receive data.

You can prorate from:

• A non-derived time period to a non-derived time period

• A non-derived time period to a derived time period

• A derived time period to a derived time period, if the source and the target

have the same frequency. For example, you can prorate from Q1 to Q2, from Q1 to M1, M2, and M3, and from M? to M+6.

Suppose, for example, that you want to set up a report that allows users to plan monthly expenses for 2005 based on actual expenses from 2004. The users want to see prorated values for their branch and any units below it. To set up this report, you need a column template with the total for 2004 actuals and a column for each month in the 2005 plan. You would then enable prorations and specify the following default settings:

• Source time period: 2004 full year actuals

60 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Prorating data

• Method: Leaf Based (or use Scenario Based and specify the 2004

monthly actuals scenario)

• Value: Leave this blank to prorate the value in the source time period

• Target time periods: January-December 2005

T o define a proration:



1. If you are performing a scenario-based proration on all children including

units, consolidate the report before prorating. This ensures that your proration is performed using the correct data.

2. Select one or more rows of the report.

3. Right-click a selected row, and select BusinessObjects Planning,

Proration, and Define Proration from the menu.

Note: If Define Proration is not available, you may not have permission

to define prorations for this report. See the Reporting Guide for more information on proration permissions.

4. In the Proration Input Parameters dialog box, select the time period to

use as the source.

5. Specify whether you want to apply the proration to selected rows only or

to all rows in the section.

BusinessObjects Planning Excel Analyst User Guide 61

Working with BusinessObjects Planni ng Reports in Excel

Prorating data

6. Specify the items on which you want to perform the proration.

7. Click Next.

8. In the Prorations Methods dialog box, select a proration option from the

Method list.

9. Specify the value to prorate. (The value you specify is entered as the

value for the source time period. If you leave this blank, the existing source value is prorated. This only works for one row at a time.)

10. Click Next.

62 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

Prorating data

11. In the Proration Output Parameters dialog box, select the target time

periods.

12. To specify an alternative balance type in which to store the prorated

values, click the Write incremental values to selected balancetype option, then choose a balance type from the list.

Note: If the output time periods you select contain actual data, you may

want to select an alternative balance type. In this way, you ensure that you do not overwrite actuals.

BusinessObjects Planning Excel Analyst User Guide 63

Working with BusinessObjects Planni ng Reports in Excel

Saving changes to data

To select a balance type from this dimension list, follow the steps described in “Specifying a missing dimension using the dimension list” on

page 34.

13. Click Prorate Now to perform the proration. Note: If you delete the columns you have specified as the source or

target, you will not be able to repeat the proration.

Saving changes to data

If you have modified the data in a report, you can save it. Your choices are to:

• Save and check in scenarios, which copies the data to the database

• Save to a working copy (save and leave the scenarios checked out)

• Abandon all changes (undo the checked-out scenarios)

T o sa ve ch ange s to data:



1. From the BusinessObjects Planning menu, select Save and Changes to

Scenarios.

2. In the Save Changes to Scenarios dialog box, select an option, and click

OK.

Saving a report as an Excel file

You can save a BusinessObjects Planning report as an Excel spreadsheet (.xls file), which is useful when you need to distribute reports to nonBusinessObjects Planning users.

To save a report as an Excel file:



1. On the Excel toolbar, click (Save).

64 BusinessObjects Planning Excel Analyst User Guide

Working with BusinessObjects Planning Reports in Excel

2. In the Save As dialog box, specify a name and location for the

spreadsheet and click Save.

Logging off of the server

You can log off to end a report session. Logging off of the application assists in the conservation of server resources, improving overall performance of the BusinessObjects Planning Excel Analyst.

To log off of the server, from the BusinessObjects Planning menu, select Log Off.

BusinessObjects Planning Excel Analyst User Guide 65

Working with BusinessObjects Planni ng Reports in Excel

Logging off of the server

66 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

chapter

Working with Ad Hoc Reports in Excel

Overview

Ad hoc reporting is an extension of the BusinessObjects Planning Excel Analyst, and becomes available when you install the BusinessObjects Planning Excel Analyst. Some functionality is shared between the BusinessObjects Planning Excel Analyst and ad hoc reporting. For example, ad hoc reporting is Web-enabled, it accesses the BusinessObjects Planning Server through your Internet or Intranet, and you do not have to run a BusinessObjects Planning application to use it.

Ad hoc reporting is unique in that it allows you to create reports that query business rules and data in your BusinessObjects Planning environment.

Primarily, ad hoc reporting allows you to:

• Use report areas or functions to create one or more reports that query

business rules and data from your BusinessObjects Planning environment

• Retrieve data from the database

• Upload the data you enter to the database

Ad hoc reporting also allows you to:

• Open a copy of a BusinessObjects Planning report as an ad hoc report

• Create and edit reports offline

• Drill down from summary information to more detailed data

• Format reports using Microsoft Excel functionality and take advantage of

all other Excel tools (for example, charting)

• E-mail reports to another user

• S pecify the display state of data in an ad hoc report by specifying whether

the data is a dollar amount or a rate. If you are specifying the display state of the data, type either a % for rate or a $ for a dollar amount. If a row is set to rate and a column to dollar , the setting for the row is used for the intersecting cell.

Note: For information on how to use Microsoft Excel functionality, see the

online help that comes with that application. Ad hoc reporting does not allow you to modify existing BusinessObjects

Planning reports. You can perform this task using BusinessObjects Planning reports. For more information, see “Working with BusinessObjects Planning

Reports in Excel” on page 25.

68 BusinessObjects Planning Excel Analyst User Guide

Logging on to the server

You can create ad hoc reports while working online or offline. Some tasks, such as validating a business model, require that you be logged on to the server. However, if you try to perform these tasks while working offline, you are automatically prompted to log on.

Before attempting to log on, you should know:

• The name of the web server onto which you are logging

• Your user name

• Your domain

• Your domain password (the password you use to log on to Windows)

To log on to the server:



1. From the BusinessObjects Planning menu, select Log On.

2. In the Log On dialog box, type your server name in the Server text box. Note: All information entered in this dialog box is remembered, except

for your password. Depending on how your web server is configured to authenticate users, you might not require your domain, name, or password to log on to the server.

3. In the User text box, type your user name.

4. In the Domain text box, type your domain. (If you do not know your

domain, contact your administrator.)

5. In the Password text box, type your password.

Working with Ad Hoc Reports in Excel

Logging on to the server

6. To save this log on profile, select Save this profile.

7. Click OK.

BusinessObjects Planning Excel Analyst User Guide 69

Working with Ad Hoc Reports in Excel

Logging on to the server

Using the Log message workbook

Writing a Log message

From within an Excel macro, you can use the CLogMessage macro to write messages to the Log message workbook. This allows you to warn users of unusual conditions.

When you call CLogMessage, you must pass the message as a parameter. For example:

CLogMessage "Missing data in report!"



T o crea te a ma cro tha t writes a log me ssa ge:

1. Create a macro using the Excel macro creation functionality. (Refer to the

Microsoft Excel help for information on how to create macros in Excel.)

2. From the Tools menu, select Macro and Macros.

3. From the Macro window, select your macro from the list provided, and

click Edit.

4. In the Microsoft Visual Basic window, select Tools and References.

5. Select the BusinessobjectsPlanningExcelAnalyst check box to link

your current code with the BusinessObjects Planning project.

6. Click OK.

7. In the Code window, which contains the code for your macro, add the

CLogMessage macro. This macro can be in either of two formats:

CLogMessage "Missing data in report!"

70 BusinessObjects Planning Excel Analyst User Guide

Application.Run "CLogMessage", "Missing data in report!"

If you use the second format, you do not need to follow steps 4, 5, and 6 above, as this format is independent of any project dependencies.

8. In the Microsoft Visual Basic window, from the File menu, select Close

and Return to Microsoft Excel.

9. Save your Microsoft Excel file.

Using the Ad hoc toolbar

When you start the BusinessObjects Planning Excel Analyst, you will see the Ad hot toolbar on the screen. It contains some of the operations you will perform when using ad hoc reporting.

Icon Tool Tip Description

Validate Report

Validates the report to ensure that the report design is correct and that the member identifiers are valid. Checking this button will not validate the business model.

Working with Ad Hoc Reports in Excel

Using the Ad hoc toolbar

Refresh Ad Hoc Report

Upload Data Validates the report and sends data to the database.

Upload and Refresh

V alidates the report, retrieves data from the database, and displays it in the report.

Validates the report, sends data to the database, retrieves the updated data, and displays it in the report. This is useful for updating planning lines, and then downloading the updated totals or ratios, all in one step.

Creating ad hoc reports

There are two ways to create ad hoc reports using the BusinessObjects Planning Excel Analyst:

• You can define report areas and download report data into them (see

“Creating ad hoc reports using report areas” on page 72)

• You can specify BusinessObjects Planning functions in cells that retrieve

report information from the database (see “Creating ad hoc reports using

functions” on page 108)

BusinessObjects Planning Excel Analyst User Guide 71

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Note: You can specify a BusinessObjects Planning function in a cell

contained in a report data area. In this case, the value of the function overrides the value that would otherwise be displayed in the cell.

When you create ad hoc reports, you do not need to have BusinessObjects Planning Administrator or BusinessObjects Planning Analyst Pro installed. However, you do have to understand how your organization’s business rules are set up in your BusinessObjects Planning environment. You also need to understand the basic BusinessObjects Planning report building rules. For example, if you are creating a planning report in which you want users to be able to enter data, all the members you specify for the report must be at the lowest level in their respective hierarchies or they must be revisable lines. However, if you specify a revisable line in the report, there are implications when uploading data to the database.

Note: If you do not specify a dimension at the row, column, or report level,

the report inherits defaults from the user, site, or model level. These defaults are specified using BusinessObjects Planning Administrator or BusinessObjects Planning Analyst Pro.

Creating ad hoc reports using report areas

In the BusinessObjects Planning Excel Analyst, you can create a report by specifying a report area, which is a block of cells on a worksheet. You then build your report by specifying a business model, columns, rows, and report properties within this report area.

You can create multiple reports in a single worksheet by specifying a report area for each report, provided the maximum number of data cells in the worksheet does not exceed the cell upload or cell download limit imposed by your site. (Contact your system administrator for more details on these limits.)

At a minimum, to create a report, you must:

• Specify a report area

• Specify a business model (Do this before specifying columns, rows, or

report properties.)

• Specify columns

• Specify rows

• Specify report properties, such as the dimensions not specified in the

columns and rows (See “Specifying section properties using the

Dimension Wizard” on page 89.)

You also have the option to:

72 BusinessObjects Planning Excel Analyst User Guide

• Specify general rates and currency exchange rates (Although you can

apply rates to rows, columns, and the entire report, it makes most sense to add them to rows.)

• Specify the display currency (See “Currency settings” on page 20.)

• Specify the state of the data (You can specify whether data is a dollar

amount or a rate. By default, data is interpreted as dollar amounts and therefore if you want it displayed as a rate, you must specify the display state.)

• S pecify whethe r to allow data to be uploaded (See “S pecifying whether to

allow data to be uploaded” on page 100.)

If you do not specify a dimension at the row, column, or report level, the report inherits defaults from the user, site, or model level. These defaults are specified using BusinessObjects Planning Administrator or BusinessObjects Planning Analyst Pro.

Specifying report areas

You can create an ad hoc report on a worksheet by specifying a report area. Within this report area, you then build your report by specifying a business model, columns, rows, and section properties.

To specify a report area for an ad hoc report:



1. Click the cell you want as one corner of the report area, hold down the

HIFT key, and click the cell that you want as the opposite corner of the

S report area.

2. Right-click a cell and select BusinessObjects Planning and Create New

Report Area from the menu.

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

BusinessObjects Planning Excel Analyst User Guide 73

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Renaming report areas

For each report area you create, BusinessObjects Planning assigns the name INEACONTAINER_REPORTx, where x is a number beginning with 0 and incrementing with each report area you create. For example, BusinessObjects Planning defines the first report area you create as INEACONTAINER_REPOR T0, the second as INEACONT AINER_REPORT1, and so forth. If the name of a BusinessObjects Planning report area contains double digits, such as when you create more than 10 report areas in a single BusinessObjects Planning report, BusinessObjects Planning ignores the last digit in the name and the report does not refresh properly. To ensure that the report refreshes correctly, you should replace the last two characters in the report area name with a single character. For example, when you specify the eleventh report area, it will be assigned the name INEACONTAINER_REPORT10, which you can rename to INEACONTAINER_REPORTA.

T o rename a report area:



1. From the Insert menu, select Name and Define.

2. Select the name you want to change from the Names in Workbook list.

3. Type a new name in the Names in Workbook text box and click Add.

4. To delete the original name, select it from the Names in Workbook list

and click Delete.

Specifying a business model

If you are using report areas to create a report, you need to specify which business model to use with each ad hoc report you create. The dimensions that appear in the Ad hoc menus change depending on your choice of business model.

The dimensions for each model that you specify are saved in the registry. If you specify a new business model that is not part of the registry, you are prompted to log on to the server to retrieve the rules for the new model. Once you do so, the settings for that model are also saved to the registry.

When you create a report offline using a model that you have previously used, the dimensions that appear in the Ad hoc menus are based on the model settings saved in the registry . Therefore, you may occasionally want to log on to the server and validate the models to ensure that they accurately reflect what is in the BusinessObjects Planning environment, for example, to ensure that no additional dimensions have been added since you specified the business model or you last validated it. For more information on validating a business model, see “Validating business models” on page 76.

74 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports



To specify a business model for ad hoc reports:

1. Right-click a cell, and select BusinessObjects Planning, Business

Model, and Select from the menu.

2. In the Select Business Model dialog box, select a business model from

the list provided.

Note: You can only select a business model if an identifier has been

defined for it. Business models for which identifiers have been defined are displayed in blue.

3. Click OK.

BusinessObjects Planning Excel Analyst User Guide 75

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Validating business models

If you are using report areas to create a report, you would validate a business model if you are reusing an old report and want to ensure that the model it accurately reflects what is in the BusinessObjects Planning environment. For example, you would validate a business model to ensure that no additional dimensions have been added to it since the last time you validated.

To validate a business model:



1. Right-click the cell you specified for the business model and select

BusinessObjects Planning, Business Model, and Validate from the menu.

2. If the Log On dialog box appears, log on to the server. (This dialog box

only appears if you were working offline.) See “Logging on to the server”

on page 69 for details on how to log on to the server.

Adding rows to ad hoc reports

If you are using report areas to create ad hoc reports, you must define rows for each ad hoc report you create. To define a row, select its range, associate it with dimensions, display currencies, display states, or rates, and then specify individual members for each cell in the range.

When you specify the row range, it can span only one Excel column. If you select cells that span multiple Excel columns, you will not be able to associate the range with a dimension.

You can nest rows in the report. For example, you can define lines and balance types in the rows. To do this, first define a range in one column, associate this range with the line dimension, and specify a line for each cell in

76 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

that range. Then, define a second range in a second column, associate this range with the balance type dimension, and specify a balance type for each cell in that range.

Note: Even though you must use member identifiers when specifying rows,

you can add additional rows that use member names and then hide those rows that contain the identifiers. Hidden information still applies to the report. (For information on how to hide a row, see the Microsoft Excel help.)

If you are using nested rows in a report, both ranges must be of the same length. You cannot have one range that spans 5 cells and a second range that spans 4 cells. If either of the ranges contains a blank cell, that row is considered a text row, and no values are loaded into that row.

To add rows to an ad hoc report:



1. Specify a business model on which to base the report. (If the model you

specify is not valid, dimensions will not appear in the Row menu when you perform Step 3. For info rmation on how to specify a business model, see “Specifying a business model” on page 74.)

2. Click the cell you want as the first row in the report, hold down the S

key, and click the cell that you want as the last row in the report. (The second cell you select must be in the same column as the first cell you selected.)

3. Right-click one of the selected cells, and select BusinessObjects

Planning, Row, and an item from the menu. (For example, if you want lines in the rows, select Line from the Row menu.)

HIFT

BusinessObjects Planning Excel Analyst User Guide 77

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Note: If you want to create a row range while not logged on to the server ,

or you want to create a row range of display states or scenarios, see

“Editing a row range manually” on page 121.

78 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Once you have selected your range, the Dimension Wizard automatically appears if you are logged on to the BusinessObjects Planning server, and provided you have not selected Scenario or Display State. For all dimensions except the Time Period dimension, the Dimension Wizard appears as follows:

BusinessObjects Planning Excel Analyst User Guide 79

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

For the Time Period dimension, the Dimension Wizard appears as follows:

4. If you are selecting time periods, select and enter time period

components in the boxes provided. See “Specifying time periods” on

page 98 for more details on selecting time period components.

In the text box to the left of the button, type all or part of a member identifier. If necessary, press T identifier in the text box.

80 BusinessObjects Planning Excel Analyst User Guide

AB to display the complete member

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Click to select a member or member hierarchy from the dimension list. See “Selecting members from the dimension list” on page 96 for more details on selecting members from the dimension list.

Note: If you are selecting a member of the Currency dimension, do not

select either Pure Currencies or Derived Currencies, as these are folders, not members. To select a currency, expand either or both of the Pure Currencies and Derived Currencies folders.

5. If you did not use the dimension list to select the member, click

to add the selected member to the Members selected list. (If you used the dimension list, the member has already been added.)

For each selected member, the Members selected list displays the member’s identifier and name, but the Dimension Wizard uses only the member identifier as a row header. See Step 9 for information on how to use the Dimension Wizard to display member names in your report.

6. Repeat steps 4 and 5 to add additional members to your row range. If you

want to leave a row blank, click Blank.

7. To move a member up or down in your row range, select the member

from the Members selected list, and click Up to move the member up or click Dn to move the member down.

8. To remove a member from the row range, select the member from the

Members selected list and click .

BusinessObjects Planning Excel Analyst User Guide 81

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

9. To specify member labels for your row range, select the Display Member

Name check box and click Select column. When the Label position

dialog box appears, go to your report, click a cell in the column in which the labels are to appear, then click OK in the Label Position dialog box. The label text box now specifies the column in which labels are to appear.

The labels are positioned to line up with your row range. For example:

If your row range is rows 7-11 of your spreadsheet, and you have selected column B for your labels, your labels appear in cells B7 through B11.

Note: If a member identifier in your row range consists of one or two

alphabetic characters followed by one or more digits (such as AA15), the BusinessObjects Planning Excel Analyst may interpret this as a cell location, not a member identifier. To keep this from happening, enclose the identifier in quotes (for example, “AA15”).

82 BusinessObjects Planning Excel Analyst User Guide

10. Click OK. This displays the range starting at the first cell you selected.

If you specified labels for your row range, they are displayed in the column specified in the label text box. Each label appears in the same column as its corresponding member.

11. To nest rows in the report, repeat the above steps. (If you nest rows in a

report, and you leave a cell in one of the ranges blank, the entire row becomes a text row. The row remains a text row even if another range defines data for it.)

If you forget which cells you defined as a row range, you can use View Dimensions to display the range. For more information on View Dimensions, see “Viewing and highlighting dimensions” on page 106.

Adding columns to ad hoc reports

If you are using report areas to create ad hoc reports, you must define columns for each ad hoc report you create. To define a column, select its range, associate it with dimensions, display currencies, display states or rates, and then specify individual members for each cell in the range.

When you specify the column range, it can span only one Excel row. If you select cells that span multiple Excel rows, you will not be able to associate the range with a dimension.

Note: Even though you must use member identifiers when specifying

columns, you can add additional columns that use member names and then hide those columns that contain the identifiers. Hidden information still applies to the report. (For information on how to hide a column, see the Microsoft Excel help.)

You can nest columns in the report. For example, you can define units and currencies in the columns. To do this, first define a range in one row, associate this range with units, and specify a unit for each cell in that range. Then, define a range in a second row, associate this range with the currency dimension, and specify a currency for each cell in that range.

If you are using nested columns in a report, both ranges must be of the same length. You cannot have one range that spans 5 cells and a second range that spans 4 cells. If either of the ranges contains a blank cell, that column is considered a text column, and no values are loaded into that column.

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

To add columns to an ad hoc report:



1. Specify a business model on which to base the report. (If the model you

specify is not valid, dimensions will not appear in the Column menu when you perform Step 3. For info rmation on how to specify a business model, see “Specifying a business model” on page 74.)

BusinessObjects Planning Excel Analyst User Guide 83

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

2. Click the cell you want as the first column in the report, hold down the

HIFT key , and click the cell that you want as the last column in the report.

S (The second cell you select must be in the same row as the first cell you selected.)

3. Right-click one of the selected cells, and select BusinessObjects

Planning, Column, and an item from the menu. (For example, if you want units in the columns, select Unit from the Column menu.)

Note: If you want to create a column range while not logged on to the

server, or you want to create a column range of display states or scenarios, see “Editing a column range manually” on page 126.

84 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

BusinessObjects Planning Excel Analyst User Guide 85

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

For the Time Period dimension, the Dimension Wizard appears as follows:

4. If you are selecting time periods, select and enter time period

components in the boxes provided. See “Specifying time periods” on

page 98 for more details on selecting time period components.

In the text box to the left of the button, type all or part of a member identifier. If necessary, press T identifier in the text box.

86 BusinessObjects Planning Excel Analyst User Guide

AB to display the complete member

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Click to select a member or member hierarchy from the dimension list. See “Selecting members from the dimension list” on page 96 for more details on selecting members from the dimension list.

Note: If you are selecting a member of the Currency dimension, do not

select either Pure Currencies or Derived Currencies, as these are folders, not members. To select a currency, expand either or both of the Pure Currencies and Derived Currencies folders.

5. If you did not use the dimension list to select the member, click

to add the selected member to the Members selected list. (If you used the dimension list, the member has already been added.)

For each selected member, the Members selected list displays the member’s identifier and label, but the Dimension Wizard uses only the member identifier as a column header. See Step 10 for information on how to use the Dimension Wizard to display member labels in your report.

6. Repeat steps 4 and 5 to add additional members to your column range. If

you want to leave a column blank, click Blank.

7. To move a member up or down in your column range, select the member

from the Members selected list, and click Up to move the member up or click Dn to move the member down.

BusinessObjects Planning Excel Analyst User Guide 87

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

8. To remove a member from the column range, select the member from the

Members selected list and click .

9. To specify member labels for your column range, select the Display

Member Name check box and click Select row . When the Label position

dialog box appears, go to your report, click a cell in the row in which the labels are to appear, then click OK in the Label Position dialog box. The label text box now specifies the row in which labels are to appear.

The labels are positioned to line up with your column range. For example:

If your column range is columns D-I of your spreadsheet, and you have selected row 3 for your labels, your labels appear in cells D3 through I3.

Note: If a member identifier in your column range consists of one or two

10. Click OK. This displays the range starting at the location specified in the

Dimension position text box.

88 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

If you specified labels for your column range, they are displayed in the row indicated in the Label text box. Each label appears in the same column as its corresponding member.

11. To nest columns in the report, repeat the above steps. (If you nest

columns in a report, and you leave a cell in one of the ranges blank, the entire column becomes a text column. The column remains a text column even if another range defines data for it.)

If you forget which cells you defined as a column range, you can use View Dimensions to display the range. For more information on View Dimensions, see “Viewing and highlighting dimensions” on page 106.

Specifying section properties using the Dimension Wizard

At the section properties level, you can specify dimensions, display currencies, display states, and rates.

When you set a section property , it applies to the entire report unless it is also specified in a column or row.

As a best practice, the cells you select to define section properties should appear vertically in the report. In this way , you can create a table that displays the identifiers next to their names. For example:

Creating ad hoc reports

If you are specifying a dimension (such as Balance Type) as a section property, and you are logged on to the server, the Dimension Wizard automatically appears to help you create the section property. If you are specifying a scenario or display state, or you are not logged on to the server, you must specify the section property manually.

For information on specifying a section property manually, see “Specifying

section properties manually” on page 94. For information on how to log on to

the BusinessObjects Planning server, see “Logging on to the server” on

page 69.

To specify section properties using the Dimension Wizard:



1. Specify a business model on which to base the report. (If the model you

specify is not valid, dimensions will not appear in the Section menu when you perform Step 3. For info rmation on how to specify a business model, see “Specifying a business model” on page 74.)

BusinessObjects Planning Excel Analyst User Guide 89

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

2. Right-click a single cell and select BusinessObjects Planning, Section,

and an item from the menu. (For example, to specify the display currency for the report, select Display Currency from the Section menu.)

90 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Once you have selected your cell, the Dimension Wizard automatically appears if you are logged on to the BusinessObjects Planning server, and provided you have not selected Scenario or Display State. For all dimensions except the Time Period dimension, the Dimension Wizard appears as follows:

BusinessObjects Planning Excel Analyst User Guide 91

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

For the Time Period dimension, the Dimension Wizard appears as follows:

3. If you are selecting a time period, select and enter time period

components in the boxes provided. See “Specifying time periods” on

page 98 for more details on selecting time period components.

In the text box to the left of the button, type all or part of a member identifier. If necessary, press T identifier in the text box.

92 BusinessObjects Planning Excel Analyst User Guide

AB to display the complete member

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Click to select a member from the dimension list. See “Selecting

members from the dimension list” on page 96 for more details on

selecting a member from the dimension list.

Note: If you are selecting a member of the Currency dimension, do not

select either Pure Currencies or Derived Currencies, as these are folders, not members. To select a currency, expand either or both of the Pure Currencies and Derived Currencies folders.

4. If you did not use the dimension list to select the member, click

to add the selected member to the Members selected list. (If you used the dimension list, the member has already been added.)

BusinessObjects Planning Excel Analyst User Guide 93

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

5. To specify a member label for your section property, select the Display

Member Name check box and click Select position. When the Label

position dialog box appears, go to your report, click the cell in which the label is to appear, then click OK in the Label Position dialog box. The label text box now specifies the cell in which the label is to appear.

6. Click OK when you have completed adding your section property.

7. Repeat the above steps for each section property that you want to set.

If you forget which cell you defined as a section property, you can use View Dimensions to display the property. For more information on View Dimensions, see “Viewing and highlighting dimensions” on page 106.

Specifying section properties manually

If you are specifying a scenario or display state, or you are not logged on to the server, you must specify the section property manually. In other cases, a Dimension Wizard automatically appears to help you create your section property.

Note: For more information on specifying a section property using the

Dimension Wizard, see “Specifying section properties using the Dimension

Wizard” on page 89.

94 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

If a member identifier that you are editing consists of one or two alphabetic characters followed by one or more digits (such as AA15), the BusinessObjects Planning Excel Analyst may interpret this as a cell location, not a member identifier. To keep this from happening, enclose the identifier in quotes (for example, “AA15”).

If a member identifier begins with a zero (0), Excel may treat the zero as a leading zero and remove it. To prevent this, type a ' (single-quote) before typing your identifier, or change the cell to a text cell by right-clicking the cell, selecting Format Cells, and selecting Text from the Category list.

To specify section properties manually:



1. Specify a business model on which to base the report. (If the model you

specify is not valid, dimensions will not appear in the Section menu when you perform Step 3. For info rmation on how to specify a business model, see “Specifying a business model” on page 74.)

2. Right-click a single cell and select BusinessObjects Planning, Section,

and an item from the menu. (For example, to specify one Display state for the report, select Display State from the Section menu.)

You cannot select more than one cell.

BusinessObjects Planning Excel Analyst User Guide 95

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

3. Click the cell you selected in Step 2 and type a member identifier for the

dimension or rate you are adding. OR If you are specifying the display state of the data, type either a % for rate

or a $ for a dollar amount.

4. Repeat the above steps for each section property that you want to set.

Selecting members from the dimension list

When you are using the Dimension Wizard to select members for a row range or a column range, or a member for a section property , you can click to

view a list of all members of that dimension. Y ou can then select a member or member hierarchy from this dimension list. Also, a dimension list appears when you specify a balance type for a projection or proration, or when you open a report that contains missing dimensions.

96 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

From the dimension list, you can select members by either expanding the dimension tree provided or searching for a member in the dimension tree. To

expand a portion of the dimension tree, click the button located to the left of a displayed member. This displays the children of the member.

When searching for a member in the dimension tree, you can search by member name or member ID. When searching, you can specify any or all of the following limitations:

• The search must match the entire word

• The matched member must start with the text specified in the search

• The matched member must be the same case as the text specified in the

search; for example, “TEXT” only matches “TEXT”, not “text” or “Text”. (This option is only available if you are searching by member name.)

By default, a search matches a member if the search text is contained anywhere in the member name or identifier.

When you use the dimension list to select a member from the dimension tree, the selected member is at the top of a dimension hierarchy, and the hierarchy has been expanded, the member’s children are transferred to the Members selected list along with the selected member. For example:

BusinessObjects Planning Excel Analyst User Guide 97

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Here, if you select CM00 and click OK, all of its children are also transferred. If you want to transfer a member but not its hierarchy, click , located to the

left of the member. This hides the hierarchy, and ensures that only the member itself is transferred.

For more details on using the Dimension Wizard to specify a row range, column range, or section property, see one of the following:

• “Adding rows to ad hoc reports” on page 76

• “Adding columns to ad hoc reports” on page 83

• “Specifying section properties using the Dimension Wizard” on page 89

T o se lect m e mb ers fro m the dim e nsion list:



1. In the Dimension Wizard, click to display the dimension list.

2. Select a member from the dimension hierarchy displayed. If the member

you want to select is not displayed, and you know which part of the dimension hierarchy contains the member, click as necessary to

display the member. OR In the Search for dimension member text box, enter some or all of a

member name or identifier, and click Find. To repeat the search after finding a member, click Find Next.

3. Click OK to select the member or member hierarchy.

Specifying time periods

You can use the Dimension Wizard to specify one or more time period identifiers in a section property, row, or column of your report. A time period identifier consists of three components: a year, a version, and the time period itself. These components are separated by a period (.). An examp le of a valid time period identifier is 2002.A.M3 (which is the 2002 monthly actuals for month 3).

The following table describes the time period identifier components in more detail.

98 BusinessObjects Planning Excel Analyst User Guide

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

Time Period Identifier Component

Year Any four-digit year, such as 2003, or one of the

Version One of the following:

Time Period A valid time period. For details on time periods, see

The Dimension Wizard contains boxes for each of these time period identifier components:

Description

following: Current The current year Scenario The scenario’s year

Scenario The scenario’s version Actual Actual data Forecast Forecasted data Plan Planned data

“Time Period Tables” on page 161.

For more details on using the Dimension Wizard to specify a row range, column range, or section property, see one of the following:

• “Adding rows to ad hoc reports” on page76

BusinessObjects Planning Excel Analyst User Guide 99

Working with Ad Hoc Reports in Excel

Creating ad hoc reports

• “Adding columns to ad hoc reports” on page 83

• “Specifying section properties using the Dimension Wizard” on page 89

T o sp ec ify a time p erio d:



1. From the Year list, select a year.

2. From the Version list, select a version.

3. In the Time Period text box, type a time period.

OR Click Browse, and select a time period from the list that appears.

Specifying whether to allow data to be uploaded

By default, you cannot upload the data you enter in an ad hoc report area to the database. This is a safety feature to protect against accidentally overwriting values in the database.

Note: Cells containing calls to the IPUTVAL function always have their

values uploaded, even if you have not specified any upload flags. You can allow uploading of data to the database in the following ways:

• You can allow uploading of an entire report by defining a single Upload

flag. This upload is restricted to input level members only . For details, see

“Uploading an entire report” on page 100.

• You can indicate that particular rows of your report are to be uploaded.

For details, see “Uploading specified report rows” on page 101.

• You can indicate that particular columns of your report are to be

uploaded. For details, see “Uploading specified report columns” on

page 102.

• You can indicate that particular cells of your report are to be uploaded.

For details, see “Uploading individual report cells” on page 104.

Y ou must use a row or column Upload flag to upload a revisable line. A reportlevel Upload flag has no effect on revisable lines.

If you want to upload data from a report that contains non-editable lines, use row or column Upload flags to avoid error messages.

Uploading an entire report

To allow uploading of an entire report, you can define a single Upload flag for the report. This upload is restricted to input level members only.

Note: If you want to upload revisable lines, you must specify individual

Upload flags for each of the rows or columns that contain the revisable lines. For more information, see “Uploading specified report rows” on page 101 and

“Uploading specified report columns” on page 102.

100 BusinessObjects Planning Excel Analyst User Guide

Business objects PLANNING EXCEL ANALYST 5.2 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Introduction

Overview

Conventions used in this guide

About BusinessObjects Planning

Related documentation

About this guide

Basic Concepts

About BusinessObjects Planning Excel Analyst

Specifying the application and working language

Viewing version information

Business model properties

Dimensions

Currency types

Currency settings

Inheritance

Working with BusinessObjects Planning Reports in Excel

Accessing and viewing reports in Excel

Logging on to the server

Using the BusinessObjects Planning Excel Analyst tool bar

Working with the Log message workbook

Using the Drill-down toolbar

Opening a report

Specifying a missing dimension using the dimension list

Specifying a missing Time Period dimension

Understanding how report values are determined

Drilling down in a report

Changing the Display currency

Launching a task from within a report

Pivoting a report section

Editing data in a report

Updating multi-section reports

Copying data between Excel and BusinessObjects Planning Analyst

Consolidating report data

Projecting future performance

Using predefined projections

Defining a projection

Prorating data

Proration methods

Distribute Evenly

Leaf Based

Scenario Based

Determining the behavior of prorations

Prorating using different scenario types

Prorating Revisable lines

Prorating lines that are input within a version

Prorating formula lines

Using predefined prorations

Defining a proration

Saving changes to data

Saving a report as an Excel file

Logging off of the server

Working with Ad Hoc Reports in Excel

Overview

Logging on to the server

Using the Log message workbook

Writing a Log message

Using the Ad hoc toolbar

Creating ad hoc reports

Creating ad hoc reports using report areas

Specifying report areas

Renaming report areas

Specifying a business model

Validating business models

Adding rows to ad hoc reports

Adding columns to ad hoc reports

Specifying section properties using the Dimension Wizard

Specifying section properties manually

Selecting members from the dimension list

Specifying time periods

Specifying whether to allow data to be uploaded