Download Report

How to Use the Report Signage Parameter in the
EPM Add-In on BW InfoProviders
by Peter Jones, BI Solution Architect, MI6 Solutions
This document is for your personal use only. Reproduction or distribution in any form is strictly prohibited without the permission
of the publisher, Wellesley Information Services. For information about SAP Experts visit www.SAPexperts.com.
This article is found at SAPexperts.com.
SAP Experts is an independent, online knowledgebase packed with proven, step-by-step instruction, best practices, case studies, tools, and tips from the
world’s top experts in SAP technology. SAP professionals around the world who deploy, manage, support, configure, and customize SAP solutions rely on
SAP Experts as a trusted learning tool throughout the year. A 12-month subscription to SAPexperts.com helps you advance your skills and do your job
better while you avoid costly mistakes and get more out of your SAP technology. Subscribe, or get more information, at SAPexperts.com.
Hone your ability to view financial data in an SAP system by using the report signage parameter
in the SAP Enterprise Performance Management add-in.
If you review the reporting components
that are available in the SAP BI space, you
see that most toolsets enable you to display
data, specifically financial data, by using
two approaches. You can use data that is
stored in tables in the system (InfoProviders in SAP NetWeaver Business Warehouse
[SAP NetWeaver BW]), and therefore see
the information based on the way it is
posted, or you can use an approach that
switches the information to a more business user-friendly view of the data.
I call this business user-friendly view the
ability to see the data with a report
signage view. Looking at financial information you see that the format of the data
>> Note
I use the term InfoCube, but I am
referencing all tables, including
DataStore Objects (DSOs) and other
new SAP NetWeaver BW 7.3 InfoProviders, such as the Semantic
Partition Objects (SPOs).
stored in the InfoCube definitely makes
sense for the financial person, but it is a
bit more challenging for the regular users
or for printing and more statutory reporting requirements.
The reason for this is that financial information is stored with signs that are a bit
different from the types of signs that users
are accustomed to seeing. What I mean is
that information that is based on revenue or
income has a negative sign applied, and the
expenses are stored as a positive sign. If the
base data stored in the InfoProvider is used
for reporting, it would mean that the business user would be seeing a negative sign
on Net Income, and this would identify that
the company is profitable rather than a positive. To help align the view of financial data
with the more user-friendly approach of
having Net Income be displayed as a positive if a company is profitable, SAP has
developed multiple approaches to allowing
the business user to switch from one view
to another in a report process.
For example, if you look at the BEx
reporting process, you see that there are
a number of options to overcome this
issue. If you want, you can create a calculated key figure (CKF), which is a
formula that is created and used against
the data. You can use these CKFs to
create a basic multiplication by a –1 or
+1 to accommodate the need to see the
data in a report format. Another
approach is to identify a specific cell in
a static report and use the cell editor to
create a formula that adjusts that cell’s
information for the reporting view. You
can also use the parameter reverse sign
in the properties of the key figures or
CKFs to accommodate this requirement.
If push comes to shove, you can always
use a BEx workbook and insert some
small macro to accommodate this
requirement as well.
>> Note
A key figure is a value that’s stored
in the InfoProvider (table). A CKF is
generated at the time the query is
executed.
©2014 SAP Experts • Reprinted with permission from SAP Experts •
Reproduction or distribution in any form is strictly prohibited without the permission of the publisher, Wellesley Information Services.
The SAP Enterprise
Performance Management
(EPM) Add-In
little bit of back-end work on the SAP
NetWeaver BW side. Remember that this
parameter is integrated with the reverse
signage property found on the Hierarchy
tab used on the InfoObjects in SAP
NetWeaver BW (Figure 2). You might see
The EPM add-in can be used against standard SAP NetWeaver BW InfoProviders
— specifically InfoCubes (virtual or
basic) and MultiProviders — by using a
local connection via the EPM connection
screen. An additional parameter has been
added to the EPM add-in. You can access
this parameter by following menu path
Options > Sheet Options > Formatting
(Figure 1).
This parameter in the EPM add-in Sheet
Options provides the business user with a
reverse sign property to influence the view
of the data in the report. Therefore, when
the EPM add-in is used against the SAP
NetWeaver BW InfoProviders, you can
offer the reverse signage to the business
users so that they can decide if they want
to see the data based on the raw data view
as posted into the cube, or if they want to
see the business user view of the data
based on a report signage approach.
Figure 1 The Use Reverse Sign Property parameter in the EPM Sheet Options for
turning the reverse sign property on or off
Remember that this is based on a link to
the SAP NetWeaver BW InfoCubes,
which in turn are based on a local connection from the EPM add-in to the SAP
NetWeaver BW InfoProviders. As anyone
who uses SAP Business Planning and
Consolidation (SAP BPC) knows, the
ability to see the data from a SAP BPC
connection using the EPM add-in is inherent in the connection between the SAP
BPC MultiProvider and the EPM add-in.
Thus, the view of the data using the SAP
BPC connection — Web services — is
normally showing as the reporting view.
For those who are interested in using the
EPM add-in against SAP NetWeaver BW
InfoProviders, this additional parameter
offers a valuable enhancement for the business user and allows the use of the different
features offered by the EPM add-in against
core SAP NetWeaver BW data.
To set up this feature you need to do a
Figure 2 The Reverse +/– sign for nodes parameter on the Hierarchy tab of the InfoObject
©2014 SAP Experts • Reprinted with permission from SAP Experts •
Reproduction or distribution in any form is strictly prohibited without the permission of the publisher, Wellesley Information Services.
www.SAPexperts.com
this parameter turned on for the general
ledger (G/L) account hierarchies if you are
addressing financial data.
You also need to turn on this reverse sign
on the nodes and base values in the hierarchy that is required. Normally, if you are
going to use this feature, you usually turn
on the property for all values, nodes, and
base level functions (Figure 3) and in this
way, make sure that the display of the data
is consistent throughout the hierarchy.
There may be times where turning on the
parameter for only the node level values
can be of use — for example, if the information at the base member level needs to
be shown in the natural signage, but at the
text node level I want to use the reporting
view. This way you can offer a summary
level report (node level) that is familiar to
the business users but for those who drill
down to the lower level values, I want to
still show the natural sign for analysis
purposes.
Figure 3 Reverse +/– signs activated in the hierarchy (Financial With Signage)
After this step is complete you use the
parameter on the EPM add-in. One of the
differences between what is in the EPM
add-in and BEx, for example, is that the
EPM feature is accessible to the business
user, whereas the BEx feature needs to be
turned on in the Query Designer or by creating a CKF.
To illustrate this point consider the set of
data shown in Figure 4. This is straightforward dataset containing only four
records, but it enables you to see the differences and results. In this case you have
two accounts — 0030110000 and
0030311000 — that represent Revenue
and Cost of Goods Sold (COGS), respectively. As you can see from the posted
data, the raw view of the revenue is a negative, and the COGS is a positive.
After reviewing the data from the BW
side, you can review the results from the
EPM add-in report shown in Figure 5. As
you can see, without a hierarchy in the
Figure 4 Posted data in the YLEGAL InfoCube
Figure 5 EPM add-in report showing the G/L accounts with values
©2014 SAP Experts • Reprinted with permission from SAP Experts •
Reproduction or distribution in any form is strictly prohibited without the permission of the publisher, Wellesley Information Services.
report, the view of the information shows
that the raw or posted view is shown.
Therefore, the revenue (account
0030110000) shows as negative, and
COGS (account 0030311000) shows as
positive.
Now, if you switch the view from a list
of G/L accounts to a hierarchy of G/L
accounts, you see the impact of the
reverse signage property. To illustrate this
I’ve configured both a hierarchy with the
signage turned on (refer back to Figure 3)
and one that doesn’t have the signage
turned on as shown in Figure 6.
In the first report (Figure 7) you see
the use of the Reverse Sign Property
(Figure 8) and the hierarchy with
signage turned on.
The results of this feature shows that the
revenue values appear as positives, and
the expenses (in this case the COGS)
appear as negatives. This is a more comfortable view for business users in that
they can see that if the results show an
increase, that they are positive in nature,
rather than seeing a growth value as a
negative.
If you take the other view of the information and use the hierarchy that doesn’t
have the signage turned on and you turn
off the reverse sign property in the User
Options, you see that the results look very
similar to those of the view of the basic
raw data. In Figure 9 you see the results
of this combination.
An additional item you need to be aware
of in this case is that this reverse sign
option is purely mathematical in nature.
For example, with the report signage
available using the SAP BPC Web services connection to BPC MultiProviders/
InfoCubes, this uses the additional
feature of the property of Account Type
(SAP BPC-specific property) on each of
the G/L accounts and therefore incorporates an accounting view of the data.
Therefore, if the G/L account has an
Account Type of INC, then the reverse
sign uses this information to generate a
report with the data associated with an
INC account as a positive and the same
approach with all of the other Account
Types of AST, LEQ, and EXP.
This helps with the accounts that are contrary to the others and that require an
Figure 6 Account hierarchy without the signage parameter turned on (Financial WO Signage)
Figure 7 Hierarchy with Reverse Sign On and Reverse Sign Property turned on in the EPM add-in
©2014 SAP Experts • Reprinted with permission from SAP Experts •
Reproduction or distribution in any form is strictly prohibited without the permission of the publisher, Wellesley Information Services.
www.SAPexperts.com
account concept attached to them to get
the correct format. With the signage property used for SAP NetWeaver BW-specific
InfoProviders, you can, if you want,
remove all connections to anything
accounting-wise. By that I mean you can
Figure 8 Turned on Use Reverse Sign Property
Figure 9 Hierarchy without signage activated and without the use of the Reverse Sign
Property in the EPM add-in
ignore the Account Type, Balance Sheet
indicator, or Income/Expense indicator.
The reverse signage still works and
takes any negative data and displays it
as positive and vice versa for the others.
It doesn’t rely on accounting principles
and therefore may have to be tweaked
for specific accounts in the hierarchy. In
general this parameter can help with
most of the G/L accounts (99 percent)
and being able to display them as
expected by the normal business user.
The other situations in which this
reporting signage may not work would
be based on other contra-accounts in
which the signage should, in fact, be
reverse of what a simple mathematical
calculation would make it. You will also
have to make sure that the reverse
signage works as expected at the node
levels and basically the accounts found
under a specific hierarchy node are consistent in their roll up (i.e., all accounts
being positive or negative under a node
and therefore the signage at the hierarchy node level is correct). Overall, this
parameter definitely helps reduce the
time spent getting the data ready for
display to the stakeholders and formatted appropriately for the statutory
reports required at every end-of-month
process. n
Peter Jones is a business intelligence solution architect in the areas of CO, EC, SAP NetWeaver, BI, BusinessObjects, Planning and Consolidation (BPC), and SEM for MI6 Solutions. He is certified in all areas listed and a subject material expert for SEM, CO, BI, and
Planning and Consolidation. He has worked in these areas for SAP and MI6 Solutions during the last 14 years. He is currently involved in
projects incorporating SAP NetWeaver BW 7.3 and BPC 10.0 and EPM Excel Add-In 10.0. He has consulted in all areas above for the
last seven years, was an SAP instructor for seven years, and has written several books about SAP NetWeaver BW and FI/CO.
You may contact the author at [email protected].
©2014 SAP Experts • Reprinted with permission from SAP Experts •
Reproduction or distribution in any form is strictly prohibited without the permission of the publisher, Wellesley Information Services.