SAP BusinessObjects Financial Consolidation Excel Link (Web) User Guide
■ SAP BusinessObjects Financial Consolidation 10.0
2010-12-08
Copyright
© 2010 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Business ByDesign, and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business
Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web
Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well
as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the
United States and in other countries. Business Objects is an SAP company.All other product and
service names mentioned are the trademarks of their respective companies. Data contained in this
document serves informational purposes only. National product specifications may vary.These materials
are subject to change without notice. These materials are provided by SAP AG and its affiliated
companies ("SAP Group") for informational purposes only, without representation or warranty of any
kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The
only warranties for SAP Group products and services are those that are set forth in the express
warranty statements accompanying such products and services, if any. Nothing herein should be
construed as constituting an additional warranty.
2010-12-08
Contents
Logging on to an SAP BusinessObjects Financial Consolidation server................................5Chapter 1
1.1
1.2
2.1
2.1.1
2.2
2.2.1
2.3
2.3.1
3.1
3.1.1
3.2
3.2.1
3.2.2
3.2.3
3.2.4
3.2.5
3.2.6
3.2.7
3.2.8
3.3
3.3.1
3.3.2
3.3.3
3.3.4
3.4
3.5
3.6
Logging on...............................................................................................................................5
Logging off...............................................................................................................................5
Installing the application ........................................................................................................7Chapter 2
Installing the application on a temporary basis..........................................................................7
To create a temporary connection to the application ...............................................................7
Installing the application on a permanent basis.........................................................................7
To create a permanent connection to the application in Excel...................................................8
Disabling the application ..........................................................................................................9
To disable the application ........................................................................................................9
Working with consolidation data in Microsoft Excel.............................................................11Chapter 3
Excel schedules for retrieving data and entering amounts.......................................................11
Data sources for financial consolidation.................................................................................12
Creating a schedule...............................................................................................................12
Defining the table structure in a worksheet in Excel................................................................12
To insert dimension members in the Insert Dimension Members wizard.................................13
To insert an elementary dimension member in an Excel worksheet.........................................15
To insert a filter into a dimension............................................................................................15
To format a dimension with a characteristic............................................................................15
To insert data into the data matrix..........................................................................................16
Example: Creating an Excel schedule to retrieve data from packages.....................................17
Formula functions in Excel cells..............................................................................................18
Entering data in a package via Excel.......................................................................................23
Schedule dimensions required for entering or exporting data.................................................24
To enter data in a package via Excel.......................................................................................24
Multi-user mode when updating package information from Excel............................................25
Exclusive mode for package data entry in Excel .....................................................................25
To export package data from Excel ........................................................................................26
Displaying financial data in charts...........................................................................................26
Analyzing data in schedules using Excel.................................................................................26
2010-12-083
Contents
3.6.1
3.6.2
3.6.3
3.6.4
3.6.5
3.6.6
Index 33
Updating data in schedules....................................................................................................26
To update amounts in a range of cells in a schedule...............................................................27
To update amounts in a schedule worksheet..........................................................................27
To update amounts in a schedule workbook...........................................................................27
Retrieving data using the GetCtData function........................................................................27
Retrieving the display language using the GetCtLabel function...............................................28
More Information...................................................................................................................31Appendix A
2010-12-084
Logging on to an SAP BusinessObjects Financial Consolidation server
Logging on to an SAP BusinessObjects Financial
Consolidation server
1.1 Logging on
To access all of the functions in the Financial Consolidation menu in Excel, you must connect to the
application database.
Connect to the database by selecting Financial Consolidation > Connect in the Excel menu bar.
This dialog box contains two fields.
• The Data source groupbox is used to enter the URL.
• In the Authentication groupbox, specify the user connection.
Note:
Information about the connection status is available in the status bar. This indicates Connected to
[Name of data source] – [Finance user].
1.2 Logging off
Selecting Financial Consolidation > Disconnect will break the link between the application database
and the Excel workbook.
You will no longer be able to import or export data.
2010-12-085
Logging on to an SAP BusinessObjects Financial Consolidation server
2010-12-086
Installing the application
Installing the application
To find out more about installing the application, see the
Installation Guide
.
SAP BusinessObjects Financial Consolidation
2.1 Installing the application on a temporary basis
Installing SAP BusinessObjects Financial Consolidation Excel Link (Web) on a temporary basis means
that a link is established between SAP BusinessObjects Financial Consolidation and Excel only as long
as you are working in Excel. Once you exit Excel, the menu will be uninstalled.
2.1.1 To create a temporary connection to the application
A temporary connection to SAP BusinessObjects Financial Consolidation Excel Link (Web) lasts only
as long as you are working in Excel. Once you exit Excel, the connection is closed.
1.
Start Excel.
2.
Select File > Open.
The "Open" dialog box appears.
3.
In the Finance Consolidation installation folder, select the CtExcelLinksWeb.xll macro.
4.
Click Open.
The Financial Consolidation menu appears.
Note:
All Excel functions are still available when you are connected to the Web version of SAP BusinessObjects
Financial Consolidation.
2.2 Installing the application on a permanent basis
2010-12-087
Installing the application
You can install the application on a permanent basis. To do so, use the Add-Ins dialog box in Excel.
This dialog box is used to select the macro needed to install the application on a permanent basis. You
can select this macro to enable the application or deactivate it to disable it temporarily.
Tip:
To get the most out of the dynamic analysis function, we recommend that you create an ODBC data
source on a client workstation to connect to the SAP BusinessObjects Financial Consolidation database.
If the name of the ODBC data source on the client workstation is not identical to that on the application
server, the client workstation must select a data source each time a dynamic analysis is required. To
avoid this, you should ensure that both the ODBC and application server data sources have the same
name. For Oracle databases, you should also configure the same resolution for the Oracle service
name on the client and application server.
2.2.1 To create a permanent connection to the application in Excel
Tip:
If you are using the dynamic analysis function, we recommend that you create an ODBC data source
on a client workstation to connect to the Finance database. If the name of the ODBC data source on
the client workstation is not identical to that on the application server, the client workstation must select
a data source each time a dynamic analysis is required. To avoid this, ensure that both the ODBC and
application server data sources have the same name. For Oracle databases, you should also configure
the same resolution for the Oracle service name on the client and application server.
1.
Start Excel.
2.
Select Tools > Add-Ins.
The "Add-Ins" dialog box appears.
This dialog box is used to select the macro needed to install SAP BusinessObjects Financial
Consolidation Excel Link (Web) on a permanent basis.
Note:
While the add-in is being installed, the Edit > Cancel command is only available once.
3.
Click Browse.
The "Browse" dialog box appears.
4.
In the SAP BusinessObjects Financial Consolidation Excel Link (Web) installation folder, select the
CtExcelLinksWeb.xll macro.
You can select this macro to enable SAP BusinessObjects Financial Consolidation Excel Link (Web)
or deselect it to disable it temporarily.
5.
In the "Add-Ins" dialog box, activate Financial Consolidation and click OK.
The Financial Consolidation menu appears.
2010-12-088
Installing the application
Note:
All Excel functions are still available when you are connected to the Web version of SAP BusinessObjects
Financial Consolidation.
2.3 Disabling the application
You can disable the application even when it is installed on a permanent basis.
Note:
If the application was installed on a temporary basis, it will be disabled when you exit Excel.
2.3.1 To disable the application
1.
Start Excel.
2.
Select Tools > Add-Ins.
The "Add-Ins" dialog box appears.
3.
Deactivate Financial Consolidation and click OK.
The Financial Consolidation menu disappears.
Note:
To enable the application again, you should activate Finance Consolidation in the "Add-Ins" dialog
box.
2010-12-089
Installing the application
2010-12-0810
Working with consolidation data in Microsoft Excel
Working with consolidation data in Microsoft Excel
You use Excel Link to integrate data in and retrieve data from SAP BusinessObjects Financial
Consolidation when working in Excel.
You access SAP BusinessObjects Financial Consolidation in Excel to:
• enter package data, including amounts, and comments
• retrieve all of the data
• retrieve only the codes and descriptions of elementary values, filters, or characteristics
To use Excel Link, be an expert with the following Excel functions:
• the syntax used to refer to cells in which consolidation data is displayed
• formulas
• the charts used to illustrate data
• worksheets
• PivotTables
3.1 Excel schedules for retrieving data and entering amounts
You use the schedule in Excel to:
• Retrieve data by querying SAP BusinessObjects Financial Consolidation and storing the information
retrieved in the schedule.
• Enter package amounts.
Amounts can be entered directly in a package or exported to the package from an Excel schedule
that already contains amounts. Sites can enter data in the packages even though SAP
BusinessObjects Financial Consolidation is not installed at the sites.
To create the Excel schedule:
• Define the structure of the table.
• Select the SAP BusinessObjects Financial Consolidation dimension values to initialize the axes.
• Iinsert data entry or retrieval functions.
2010-12-0811
Working with consolidation data in Microsoft Excel
Note:
The Excel schedule queries the database directly. You can also update the schedule against SAP
BusinessObjects Financial Consolidation at any time.
3.1.1 Data sources for financial consolidation
Data sources structure the data tables so that amounts can be identified using dimensions. Data sources
can include:
• Amounts
• Conversion rates
• Tax rates
• Investment rates
• Scope rates
• Intercompany reconciliations
The contents of the conversion and tax rate data sources are taken from the Rates module in the
Operation domain, and are the only data sources that can be changed in the Dimension Builder module
of the Setup domain.
3.2 Creating a schedule
The following method is recommended to create a schedule:
1.
Define the structure of the table before creating the schedule in Excel.
2.
In Excel, insert dimension values and add other information by using the "Insert Dimension Values"
wizard.
3.
Insert data in the data matrix using the "Define Table" wizard.
4.
Format the table and edit the data retrieved, for example update the data, total amounts, or insert
charts.
3.2.1 Defining the table structure in a worksheet in Excel
Define the structure of the table before creating a schedule in Excel to enter or retrieve data.
2010-12-0812
Working with consolidation data in Microsoft Excel
Tip:
We recommend that you display the Excel worksheet in the R1C1 reference style to make it easier to
refer to cells.
A table usually comprises four sections:
Note:
Use the "Insert Dimension Values" wizard in Excel to define the schedule header, header column and
header row. Use the "Define Table" wizard in Excel to generate the data matrix.
The schedule header, header column, and header row contain:
• initialization formulas
• display language like code and descriptions
For example, when you initialize the Account dimension with the Sales values in the schedule header,
the same values are assigned to all of the data.
Note:
To format a table in a worksheet, consult the Microsoft Excel online help.
3.2.2 To insert dimension members in the Insert Dimension Members wizard
You can insert dimension members using the "Insert Dimension Members" wizard.
1.
Select the data source so that only the dimensions relevant to the selected data source appear in
the list of dimensions.
• For a data entry schedule, select Package data and specify the members for the Audit ID,
Account, Flow, and Period dimensions. The Category, Reporting Unit, Data Entry Period, and
Currency dimensions are automatically defined when you open a package.
2010-12-0813
Working with consolidation data in Microsoft Excel
• For a schedule retrieving conversion rates, select Conversion rates. Only the dimensions relevant
to the selected data source appear in the list of dimensions: Period, Exchange Rate Version and
Type, Currency, and Reporting Unit.
2.
Select the dimension.
3.
Specify the dimension member by clicking the arrow under Selection.
Tip:
For example, you have selected the Account dimension for the row axis. For the first row, you can
then select A20100 Start-up Costs as the only member for the Account dimension. The member is
assigned to all of the amounts displayed in the row.
4.
Initialize dimensions by assigning members to them in one of the following ways:
• One member only
A value is identified by a code and a short and long description. If you select this method, you
assign one elementary value only to the dimension. For example, reporting unit U01 is an
elementary value in the Reporting Unit dimension.
• Several members
Several elementary members are assigned to the dimension.
• Filter
A filter is identified by a code, a short and long description. When you use a filter, you assign a
set of members to a dimension. For example, the FIL01 filter groups the reporting units doing
business in a given activity.
• Several members
Select Several members to assign the elementary members to the dimension. The method can
be applied to all of the dimensions in the package, preconsolidated, and consolidated data
sources, except for the following: Category, Data Entry Period, Period, Consolidation Currency,
Variant, and Scope.
The method can also be applied to the following dimensions in the Intercompany reconciliations
data source: Buyer, Seller, Reconciliation rule and Reconciliation block.
• No Member
This option initializes the dimension using members that have not been defined. It can only be
applied to non-compulsory dimensions.
• A characteristic belonging to a dimension.
If you initialize the dimension with a filter, you can aggregate the amounts retrieved in the schedule
by activating Aggregated.
If you initialize the dimension with several members, you can insert them in as many rows or columns
as required by clicking the relevant option in the "Insert members" section.
5.
Specify the display of the code or short or long description of the dimension members by activating
the relevant boxes in the "Display language" section.
2010-12-0814
Working with consolidation data in Microsoft Excel
The dimension members are inserted in rows or columns depending on the previous option selected.
Repeat the previous steps for each dimension you want to insert.
3.2.3 To insert an elementary dimension member in an Excel worksheet
1.
In Excel, select the cell in the worksheet in which you want to insert a dimension member.
2.
Select Financial Consolidation > Define Schedule > Insert Dimension Members.
The "Insert Dimension Members" wizard appears.
3.
Select the data source.
4.
Select One member only.
5.
Select the required member.
6.
Specify the row or column in which to insert the member.
7.
Select the display language.
8.
Click Insert.
3.2.4 To insert a filter into a dimension
1.
In Excel, select the cell in the worksheet in which you want to insert a dimension member.
2.
Select Financial Consolidation > Define Schedule > Insert Dimension Members.
The "Insert Dimension Members" wizard appears.
3.
Select the data source.
4.
Select the Filter icon.
5.
Select the required member.
6.
To have the amounts totaled, activate Aggregated.
7.
Specify the row or column in which to insert the member.
8.
Select the display language.
9.
Click Insert.
3.2.5 To format a dimension with a characteristic
2010-12-0815
Working with consolidation data in Microsoft Excel
Characteristics group together different members taken by the same dimension. Each character is
linked to a reference table and can have one or more characteristics. The first time you connect to
Financial Consolidation, it precustomizes the characteristics required for the application to run correctly.
If the same dimension has more than one characteristic, arrange these characteristics into a hierarchy.
This arrangement ensures that the characteristics defined for the same dimension are organized in
logical order. For example the Reporting Unit dimension can be arranged whereby the Sector is a direct
characteristic of the dimension, and the Activity a sub-character of Sector.
If the dimension member is linked to one analysis only, the member is treated as a characteristic. For
example:
• If the reporting units have one activity, the activity is treated as a characteristic, and each reporting
unit is assigned only one activity.
• If the reporting units have several activities, the activity is treated as a dimension. For each reporting
unit, the information is broken down among the different activities.
To format a dimension with a characteristic:
1.
In Excel, select the cell in the worksheet in which you want to insert a dimension member.
2.
Select Financial Consolidation > Define Schedule > Insert Dimension Members.
The "Insert Dimension Members" wizard appears.
3.
Select a data source.
4.
Select a dimension.
5.
Select one or more dimension members.
6.
Activate Aggregated for the amounts to be totaled.
7.
Specify the location in a row or column in which to insert the member.
8.
Select the display language.
9.
Click Insert.
3.2.6 To insert data into the data matrix
1.
Select Financial Consolidation > Define Schedule > Define Table.
The "Define Table" wizard appears.
2.
In the Excel worksheet, select the range of cells containing the dimension values for the schedule
axis.
3.
In the Dimensions section of the "Define Table" wizard, click the schedule axis area definition button
to assign values to the three schedule axes according to the table structure defined.
4.
Repeat the previous steps for the row and column axes.
5.
In the Data section, select the data source.
For a data entry schedule, select Package data – Package amounts as the data source.
6.
Activate Data entry or Data retrieval option as the objective, and click Insert.
2010-12-0816
Working with consolidation data in Microsoft Excel
3.2.7 Example: Creating an Excel schedule to retrieve data from packages
In the following example, you will retrieve the reporting unit sales analyzed by partner and in several
flows.
1.
Open Excel and select Financial Consolidation > Connect.
The "Start the Application" dialog box appears.
2.
Select the required options for connecting to SAP BusinessObjects Financial Consolidation.
3.
Click OK.
4.
Select Financial Consolidation > Define Schedule > Insert Dimension Members.
The "Insert Dimension Members" wizard appears.
5.
In the Excel worksheet, select the R1C1 cell.
6.
In the wizard, select Package data as the data source.
7.
Select the Category dimension from the list of available dimensions, then select One member only
and the category containing the data.
8.
In the "Insert members" section, activate In n rows and in the "Display language" section, activate
Long description.
9.
Click Insert.
10.
In the Excel worksheet, select R2C1.
11.
In the wizard, select the Data Entry Period dimension, One member only, and 2001.04.
12.
Click Insert.
13.
In the Excel worksheet, select R3C1.
14.
In the wizard, select the Reporting Unit dimension, One member only, and U001.
15.
Click Insert.
16.
In the Excel worksheet, select R4C1.
17.
In the wizard, select the Account dimension, One member only, and YR111.
18.
Click Insert.
19.
In the Excel worksheet, select R9C1.
20.
In the wizard, select the Partner 1 dimension, Several members, and U001, U002, U003, U004
and U005.
21.
Click Insert.
22.
In the Excel worksheet, select R7C3.
23.
In the wizard, select the Flow dimension, Several members, and F00, F20, F30 and F99.
24.
In the "Insert members" section, activate In n columns.
25.
In the "Display language" section, activate Long description.
26.
Click Insert, then Close.
27.
Select Financial Consolidation > Define Schedule > Define Table.
2010-12-0817
Working with consolidation data in Microsoft Excel
The "Define Table" wizard appears.
28.
In the Excel worksheet, select the range of cells containing the Category, Data Entry Period,
Reporting Unit, and Account dimensions.
Note:
Only select the cells containing these dimensions, not the cells containing the display language.
29.
In the wizard, click the button next to the "Of the schedule" text box in the "Dimensions" section.
The range of cells containing the schedule axis dimensions is inserted.
30.
In the Excel worksheet, select the range of cells containing the "Partner 1" dimension.
31.
In the wizard, click the button to insert the range of cells in the row axis.
32.
Repeat the same steps to insert the range of cells containing the Flow dimension in the column
axis.
33.
In the wizard, select Package data – Package amounts in the "Data" section.
34.
Activate Data retrieval and click Insert.
The each cell in the worksheet in which the amounts should appear displays a zero.
35.
Click Close.
36.
Select Financial Consolidation > Autonomous Mode > Import > In Sheet.
The package amounts for the U001 reporting unit is imported into the Excel worksheet.
If you want to retrieve amounts for another reporting unit, for example U002, replace RU=U001 by
RU=U002, then select Financial Consolidation > Autonomous Mode > Import > In Sheet to update
the data for U002.
You can hide the columns or rows containing the dimension codes, merge cells containing long
descriptions, and format the table. You can also add a title.
3.2.8 Formula functions in Excel cells
Once you have initialized the axes in the Excel schedule with the required dimension values, you can:
• Enter amounts in Excel and integrate them in a package.
• Retrieve amounts from SAP BusinessObjects Financial Consolidation.
You perform the tasks using data entry or retrieval functions expressed in the data matrix cells.
Tip:
Create formulas by selecting Insert > Function and using the "Formula Palette".
2010-12-0818
Working with consolidation data in Microsoft Excel
3.2.8.1 Data entry function syntax for GetCtData and PutCtData
2010-12-0819
Working with consolidation data in Microsoft Excel
Tip:
Once you have inserted a GetCtData or PutCtData function into a cell, you can copy and paste the
same formula into other cells in the data matrix. It is not necessary to express the function for each cell.
However, ensure that the function arguments contain valid references.
PutCtData
("Data source";
"Property";
"Dimension1=[dim1]";
"Dimension2=[dim2]";
"Cell1";...;
"Last existing value")
DescriptionSyntax
A data entry function. It is not necessary to enter the
last existing value when expressing the function as it
is added automatically. For example:
PutCtData
("PKAMOUNT";
"AMOUNT";"ACCOUNT=CA01";
"ENTITY=U01";"B12";"…";
"#3.14159")
A data retrieval function. It is not necessary to enter
the Last existing value when expressing the function
as it is added automatically. For example:
GetCtData
("Data source";
"Property";
"initialization";
"initialization";
"Cell1";"...";
"#Last existing value")
GetCtData
("Property";
"Cellaxes":"Cellaxes";
"...")
GetCtData("Property";
"initialization";"...")
GetCtData("PKAMOUNT";
"AMOUNT";"ACCOUNT=CA01";
"ENTITY=U01";"B12";"…";
"#3.14159")
GetCtData("PKAMOUNT";
"AMOUNT";"ACCOUNT=CA01";
"ENTITY sumTOUS";"B12"
;"…";"#3.14159")
GetCtData("PKAMOUNT";
"AMOUNT";"ACCOUNT=CA01";
"ENTITY sum (COUNTRY=FR)";
"B12";"…";"#3.14159")
GetCtData("PKAMOUNT";
"AMOUNT";"ACCOUNT=CA01";
"FORMFORMULE45;
B12";"…";"#3.14159")
The arguments identify data in a unique way by using
values taken for each dimension. The arguments can
refer to cells initialized in the schedule, row, and column
axes. For example:
GetCtData("PK-AMOUNT";
"AMOUNT";L2C3:L3C3;...)
The arguments identify data in a unique way by using
values taken for each dimension. The arguments can
refer to cell contents. For example:
GetCtData("PK-AMOUNT";
"AMOUNT"; "ACCOUNT=CA01";...)
GetCtData("Property";
"initialization";"...")
2010-12-0820
Working with consolidation data in Microsoft Excel
DescriptionSyntax
In the Excel Link data retrieval (Get) cells, you can:
• Refer to a cell in a data retrieval formula. For exam-
ple:
GetCtData("PK-AMOUNT";"AMOUNT";
$A$3:$A$6;$A12:$A12;
C$8:C$8;$F$6)
where F6 is a cell containing an amount.
• Use an Excel formula which uses data retrieval
formulas. For example:
IF(GetCtData("PK-AMOUNT";"AMOUNT";
$A$3:$A$6;$A10:$A10;C$8:C$8);
GetCtData("PK-AMOUNT";"AMOUNT";
$A$3:$A$6;$A11:$A11;C$8:C$8))
If data retrieval formula 1 is inferior to formula 2,
formula 2 is displayed. If not, formula 1 is displayed.
In the Excel Link data entry (Put) cells, you can:
• Refer to a cell in a data entry formula. For example:
PutCtData("PK-AMOUNT";"AMOUNT";
$A$1:$A$4;$B9:$B9;C$7:C$7;$F$12)
where F12 is a cell containing an amount.
PutCtData("Property";
"initialization";"...")
• Use an Excel formula which uses data entry formu-
las. For example:
SUM(PutCtData("PK-AMOUNT";"AMOUNT";
$A$1:$A$4;$B8:$B8;C$7:$C7); PutCtData("PK-
AMOUNT";"AMOUNT";
$A$1:$A$4;$B9:$B9;C$7:$C7))
The result of the Excel formula is the sum of the
two cells.
3.2.8.2 References contained in the GetCtData and PutCtData functions
In the GetCtData and PutCtData functions, arguments can refer to cells initialized with dimension
values in the:
• schedule axis, which must be absolute for functions copied and pasted in the data matrix.
2010-12-0821
Working with consolidation data in Microsoft Excel
• row axis, which must be absolute for functions copied and pasted in the same row but relative for
the in columns.
• column axis, which must be absolute for functions copied and pasted in the same column but relative
for the in rows.
Depending on the axis, cell references contained in functions can therefore be:
• absolute for arguments referring to cells initialized with dimension values in the schedule axis.
For example, in the R1C1 reference style, R3C4 is an absolute reference to a cell at which row three
and column four intersect.
• mixed for arguments referring to cells initialized with dimension values in the row and column axes.
For example in the R1C1 reference style, mixed references can be as follows:
• absolute row reference, relative column reference: R1C[-2] refers to a cell located in row one,
and two columns to the left of the current cell.
• relative row reference, absolute column reference: R[3]C4 refers to a cell located three rows
below the current cell and in column four.
In the R1C1 reference style, all of the cell references made using the Formula Palette are relative.
Relative references are references to different cells depending on the position of the formula.
The following example illustrates the various reference types contained in a formula and the result when
you copy and paste it.
The PutCtData function (R2C3:R3C3;R3C;RC2) is expressed in R6C6 and then copied and pasted
in R8C7.
2010-12-0822
Working with consolidation data in Microsoft Excel
3.3 Entering data in a package via Excel
Enter data in a package via Excel in the following ways:
• use an Excel worksheet while connected to SAP BusinessObjects Financial Consolidation
Using Excel as a starting point, activate the connection to SAP BusinessObjects Financial
Consolidation and enter the amounts in Excel. The amounts are directly integrated into the package.
You can apply the same Category Builder formulas from SAP BusinessObjects Financial Consolidation
to schedules in Excel.
Note:
Several users can work on a given package at the same time.
• export data to SAP BusinessObjects Financial Consolidation
Enter data in Excel without connecting to SAP BusinessObjects Financial Consolidation, then activate
the connection and export the amounts to the relevant package.
Caution:
When you connect and open a package to enter data via Excel, all of the amounts in the schedule
in Excel are replaced the database amounts.
Related Topics
• Multi-user mode when updating package information from Excel
2010-12-0823
Working with consolidation data in Microsoft Excel
3.3.1 Schedule dimensions required for entering or exporting data
Dimensions store, process, and retrieve data. A dimension is an analysis axis used to identify an amount
in SAP BusinessObjects Financial Consolidation and contains values. Dimensions are identified by a
code containing a maximum of 12 characters.For example, AC represents Account and FL represents
flow. The code is primarily used in formulas for initializing cells and retrieving data, so that the formula
syntax is short.
You can make adjustments to amounts in the schedule only if authorized in the category scenario.
In a data entry or export schedule, the compulsory dimensions are as follows:
• Account (AC)
• Flow (FL)
• Audit ID (AU)
• Period (PE)
• corresponding analysis dimensions if necessary
The optional dimensions are:
• Category (CA)
• Data Entry Period (DP)
• Reporting Unit (RU)
• Currency (CU)
If the optional dimensions are not specified in the schedule, then by default, they are identical in the
current package.
If cells in Excel do not match the dimension values in the current package, for example Category, Data
Entry Period, Reporting Unit, and Currency, they are regarded as external data.
Update the dimension amounts by selecting Financial Consolidation > Data Entry Connected >
Refresh Data.
3.3.2 To enter data in a package via Excel
1.
In Excel, select Financial Consolidation > Data Entry Connected > Open Package.
2.
Select the package you want to open.
Note:
When you open the package, a message appears that warns you that the data in Excel will be
replaced by the data from SAP BusinessObjects Financial Consolidation.
3.
Enter data in the Excel worksheet and apply Category Builder formulas.
2010-12-0824
Working with consolidation data in Microsoft Excel
4.
Save the data in SAP BusinessObjects Financial Consolidation and close the package.
The data in SAP BusinessObjects Financial Consolidation is changed only when you save the data
in the package.
When you save the package, and you can see the data that has been entered and saved by the
other concurrently connected users, the data is synchronized.
Note:
Simply refreshing the data only enables you to see the data that you are in the process of entering.
Tip:
If an amount for the Account/Flow pair does not exist in the database, then the cell in the Excel schedule
is empty. If the value of the amount is zero in the database, then a zero appears in the Excel cell.
3.3.3 Multi-user mode when updating package information from Excel
Several users can work on a given package at the same time if the Authorize simultaneous data
entry for several users in the package option is activated in SAP BusinessObjects Financial
Consolidation, in the Reporting tab of the General Options dialog box.
If two users enter different amounts for the same indicator, the amount entered and saved by the last
user is taken into account.
3.3.3.1 To monitor the users connected to "SAP BusinessObjects Financial
Consolidation" in Excel
• Select Financial Consolidation > Data Entry Connected > Users.
The dialog box displays all of the users connected.
Tip:
Use the icon in the top right of the dialog box to send a standard message to the users you selected
in the list.
3.3.4 Exclusive mode for package data entry in Excel
2010-12-0825
Working with consolidation data in Microsoft Excel
When you open a package after changes have been made to the category scenario, a message appears
that enables you to open and work on the the package in mono-user, exclusive mode. While you are
in exclusive mode, other users will not be able to open the package.
Note:
In SAP BusinessObjects Financial Consolidation, you can also connect in exclusive mode to perform
other operations.
3.4 To export package data from Excel
1.
In Excel, select the data you want to export, and click Financial Consolidation > Autonomous
mode > Export.
2.
Select and open the package you want.
The data from the Excel worksheet is exported to the package.
3.
Save and close the package.
3.5 Displaying financial data in charts
You can retrieve data from SAP BusinessObjects Financial Consolidation and then use charts to illustrate
the data retrieved. Excel charts are updated automatically when data in the table changes. You can
create charts using the chart wizard.
3.6 Analyzing data in schedules using Excel
SAP BusinessObjects Finance Excel Link imports data from SAP BusinessObjects Financial
Consolidation and integrates the data into the Excel worksheet.
Because SAP BusinessObjects Financial Consolidation and the Excel schedule do not interact
automatically, you have to update and export amounts manually. If you change amounts in the database,
they are not updated automatically in the Excel schedule.
3.6.1 Updating data in schedules
2010-12-0826
Working with consolidation data in Microsoft Excel
You can update amounts in a range of cells, a worksheet, or a workbook.
If an error occurs while you are updating amounts, an error message appears in the relevant cell. If the
amount does not exist in SAP BusinessObjects Financial Consolidation, then the cell in Excel is empty.
If the value of the amount is zero, then a zero appears in the Excel cell.
3.6.2 To update amounts in a range of cells in a schedule
1.
In Excel, select the cells containing the amounts you want to update.
2.
Select Financial Consolidation > Autonomous Mode > Import > In Cell Range.
The data in the range of cells you selected is updated.
3.6.3 To update amounts in a schedule worksheet
1.
In Excel, select the worksheet containing the amounts you want to update.
2.
Select Financial Consolidation > Autonomous Mode > Import > In Sheet.
The worksheet is updated.
3.6.4 To update amounts in a schedule workbook
1.
In Excel, select the workbook containing the amounts you want to update.
2.
Select Financial Consolidation > Autonomous Mode > Import > In Workbook.
Each worksheet in the workbook is updated.
3.6.5 Retrieving data using the GetCtData function
2010-12-0827
Working with consolidation data in Microsoft Excel
You can retrieve amounts from SAP BusinessObjects Financial Consolidation using the GetCtData
data retrieval function. The amounts retrieved from the database are displayed in the GetCtData
syntax as follows:
= GetCtData (Argument 1;Argument 2
;Argument 3;...;Data)
To perform budget estimates or forecasts, enter new amounts in the cells. When you select Financial
Consolidation > Autonomous Mode > Import > In Sheet, amounts from SAP BusinessObjects
Financial Consolidation replace what you entered.
Note:
When you enter amounts using the GetCtData function, the existing amounts in the database do not
change. You can however, save the amounts you entered in the schedule in Excel.
An SAP BusinessObjects Financial Consolidation formula that uses a specific indicator without any
amount displays a zero when data is imported to Excel, when data is exported from Excel, and when
you enter data while being connected to the database. You can create Excel worksheets with formulas
that include the cells containing SAP BusinessObjects Financial Consolidation formulas.
3.6.6 Retrieving the display language using the GetCtLabel function
When you want to display the dimension code and descriptions, you do not need to know the syntax.
All you need to do is activate one or more of the following:
• Code
• Long Description
• Short Description
However, when you use the Filter or Characteristic methods to initialize the dimension and you want a
description of the selected filter or characteristic shown, you use the GetCtLabel function to enter syntax
to show this information. You also use the function if you want to show the characteristic of a dimension.
You can retrieve the display language:
• Activating Code, Short description and Long description in the "Insert Dimension Values" wizard.
2010-12-0828
Working with consolidation data in Microsoft Excel
• Open the Excel wizard used for inserting Excel functions, and use one of the following syntax
examples:
DescriptionSyntax
=GetCtLabel(A1;
"VALUE(<DIM>)!NAME")
=GetCtLabel(A1;
"VALUE(<DIM>)!SDESC")
=GetCtLabel(A1;
"VALUE(<DIM>)!LDESC")
=GetCtLabel(A1;
"VALUE(<DIM>)!
<CHARACT>!NAME")
=GetCtLabel(A1;
"VALUE(<DIM>)!
<CHARACT>!SDESC")
Retrieves the code of the dimension member stored in a
cell. In the sample syntax A1 is used.
Retrieves the short description of the dimension's member
stored in a cell. In the sample syntax cell A1 is used.
Retrieves the long description of the dimension's member
stored in a cell. In the sample syntax A1 is used.
Retrieves the code of a characteristic or subcharacteristic
linked to a dimension member.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the short description of a characteristic or subcharacteristic linked to a dimension member.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
=GetCtLabel(A1;
"VALUE(<DIM>)!
<CHARACT>!LDESC")
=GetCtLabel
("ENV(CURUSER)!
NAME")
=GetCtLabel
("ENV(CURUSER)!
LDESC")
Retrieves the long description of a characteristic or subcharacteristic linked to a dimension member.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the code of the connected user.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
2010-12-0829
Working with consolidation data in Microsoft Excel
=GetCtLabel(A1;
"VALUE((<DIM>)!
CREATIONDT[SDATE]")
=GetCtLabel(A1;
"DIM((<DIM>)!LDESC")
DescriptionSyntax
Retrieves the long description of the connected user.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the reference member creation date in short
format stored in a cell. In the sample syntax cell A1 is used.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the dimension's long description. In the sample
syntax cell A1 is used.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
=GetCtLabel(A1;
"CHARACTINIT((<DIM>;
<CHARACT>)!NAME")
=GetCtLabel(A1;
"CHARACTINIT((<DIM>;
<CHARACT>)!SDESC")
=GetCtLabel(A1;
"CHARACTINIT((<DIM>;
<CHARACT>)!LDESC")
Retrieves the code of the dimension's characteristic. In
the sample syntax cell A1 is used.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the short description of the dimension's characteristic. In the sample syntax cell A1 is used.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
Retrieves the long description of the dimension's characteristic. In the sample syntax cell A1 is used.
Note:
The syntax is not provided by the wizard, and must be
entered directly into the cell.
2010-12-0830
More Information
More Information
LocationInformation Resource
SAP BusinessObjects product information
SAP Help Portal
SAP Service Marketplace
http://www.sap.com
Navigate to http://help.sap.com/businessobjects and on the "SAP BusinessObjects Overview" side panel click All Products.
You can access the most up-to-date documentation covering all SAP
BusinessObjects products and their deployment at the SAP Help Portal.
You can download PDF versions or installable HTML libraries.
Certain guides are stored on the SAP Service Marketplace and are not
available from the SAP Help Portal. These guides are listed on the Help
Portal accompanied by a link to the SAP Service Marketplace. Customers
with a maintenance agreement have an authorized user ID to access
this site. To obtain an ID, contact your customer support representative.
http://service.sap.com/bosap-support > Documentation
• Installation guides: https://service.sap.com/bosap-instguides
• Release notes: http://service.sap.com/releasenotes
The SAP Service Marketplace stores certain installation guides, upgrade
and migration guides, deployment guides, release notes and Supported
Platforms documents. Customers with a maintenance agreement have
an authorized user ID to access this site. Contact your customer support
representative to obtain an ID. If you are redirected to the SAP Service
Marketplace from the SAP Help Portal, use the menu in the navigation
pane on the left to locate the category containing the documentation you
want to access.
Docupedia
Developer resources
https://cw.sdn.sap.com/cw/community/docupedia
Docupedia provides additional documentation resources, a collaborative
authoring environment, and an interactive feedback channel.
https://boc.sdn.sap.com/
https://www.sdn.sap.com/irj/sdn/businessobjects-sdklibrary
2010-12-0831
More Information
LocationInformation Resource
SAP BusinessObjects articles on
the SAP Community Network
Notes
Forums on the SAP Community
Network
Training
Online customer support
https://www.sdn.sap.com/irj/boc/businessobjects-articles
These articles were formerly known as technical papers.
https://service.sap.com/notes
These notes were formerly known as Knowledge Base articles.
https://www.sdn.sap.com/irj/scn/forums
http://www.sap.com/services/education
From traditional classroom learning to targeted e-learning seminars, we
can offer a training package to suit your learning needs and preferred
learning style.
http://service.sap.com/bosap-support
The SAP Support Portal contains information about Customer Support
programs and services. It also has links to a wide range of technical information and downloads. Customers with a maintenance agreement
have an authorized user ID to access this site. To obtain an ID, contact
your customer support representative.
Consulting
http://www.sap.com/services/bysubject/businessobjectsconsulting
Consultants can accompany you from the initial analysis stage to the
delivery of your deployment project. Expertise is available in topics such
as relational and multidimensional databases, connectivity, database
design tools, and customized embedding technology.
2010-12-0832
Index
D
data
analyzing in Excel worksheets 26
in schedules 27
inserting into the Excel data matrix
16
dimensions
configuring in an Excel worksheet
15
filtering in an Excel worksheet 15
for data entry and export in Excel
24
inserting elementary members in
an Excel worksheet 15
inserting into an Excel worksheet
13
E
Excel data matrix
inserting data 16
Excel Link
using to work with data 11
Excel schedules
for financial consolidation 11
Excel worksheet
adding a dimension filter 15
configuring a dimension member
15
Excel worksheets
defining the table structure 12
Excel worksheets
dimensions for data entry and
export 24
entering data in packages 24
exclusive mode 25
exporting package data 26
functions 18
inserting a dimension member 15
inserting dimensions 13
multi-user mode 25
retrieving consolidation data 17
using to enter data in a package
23
exclusive mode
in Excel worksheets 25
(continued)
F
functions
GetCtData 19, 21, 27
GetCtLabel 28
in Excel worksheets 18
PutCtData 19, 21
G
GetCtData
Excel cell references 21
syntax in Excel data 19
using to retrieve data in Excel 27
GetCtLabel
using to retrieve display language
28
P
packages
entering data via Excel 23, 24
exporting data from Excel 26
sending data to an Excel worksheet
17
working in multi-user mode in Excel
25
PutCtData
Excel cell references 21
syntax in Excel data entry 19
S
schedules
data 27
retrieving data using GetCtData 27
retrieving data using GetCtLabel
28
updating amounts in cells 27
updating workbook amounts 27
updating worksheet amounts 27
T
tables
defining for the Excel worksheet 12
U
users
connected in Excel 25
2010-12-0833
Index
2010-12-0834
Loading...