_________________________________
_________________________________
_________________________________
_________________________________
Tips and Tricks for
Generating High-Quality _________________________________
Financial Reports in _________________________________
SAP BW 3.x and SAP _________________________________
NetWeaver BI _________________________________
_________________________________
Jeevan K. Ravindran
InfoElixir _________________________________
© 2007 Wellesley Information Services. All rights reserved.
In This Session ...
_________________________________
• Advanced SAP BEx tips, tricks, and features are _________________________________
explained with business case studies/examples _________________________________
• Discover the tool and technique to mass process
_________________________________
workbooks
• Understand methods to make SAP BW reports fiscally _________________________________
compliant during a fiscal year change _________________________________
• Learn techniques to ensure data accuracy by employing _________________________________
reconciliation techniques
_________________________________
_________________________________
_________________________________
1
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks
_________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
2
Five Advanced BEx Tips and Tricks
_________________________________
1. Select between Document, Local, and Group Currency _________________________________
Amounts
_________________________________
• Advanced BEx features – a crash course
_________________________________
2. Calculate with attributes of a characteristic
3. Create exception on a characteristic _________________________________
4. Alert level and process chain _________________________________
5. Customer exit variables and Trial Balance report _________________________________
_________________________________
_________________________________
_________________________________
3
SAP BEx Features List
_________________________________
• The advanced reporting techniques are made possible _________________________________
by employing these BEx features: _________________________________
Replacement path variables
Formula variables
_________________________________
Customer exit variables _________________________________
Virtual characteristics and key figures (not explained here) _________________________________
Visual Basic for Applications (VBA) in BEx workbooks
_________________________________
• Three uses of Replacement Path Variables (RPV)
In the characteristic/hierarchy restriction _________________________________
In the text variables _________________________________
Inside formula variable _________________________________
4
Characteristic/Hierarchy Value Restriction
_________________________________
• RPV populates characteristic value with results from _________________________________
another query
_________________________________
This can be performance intensive
Use “value set” to precalculate values (e.g., Top N _________________________________
vendors) _________________________________
Use BEx broadcasting to precalculate in SAP
NetWeaver® BI _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
5
Characteristic/Hierarchy Value Restriction (cont.)
_________________________________
• Definition steps _________________________________
_________________________________
1 _________________________________
2
3
_________________________________
_________________________________
_________________________________
• Query vs. value set
If current data is required, then use query for replacement _________________________________
path _________________________________
Otherwise, explore the possibility of using “value set”
_________________________________
6
In the Text Variables
_________________________________
• Options to select these values from the connected _________________________________
characteristic _________________________________
1 = Key value, 2 = Description, or 3 = External value
_________________________________
_________________________________
1
_________________________________
2
_________________________________
3
_________________________________
_________________________________
_________________________________
RPV definition steps – For text variable
7
Inside Formula Variable
_________________________________
• Formula variable _________________________________
Formula variable accepts numeric values
_________________________________
A formula variable is created in the Calculated Key Figure
definition screen _________________________________
Formula variable can accept user entered value, replacement _________________________________
path value, or customer exit (ABAP) value
_________________________________
• Replacement path in formula variable
Any characteristic in the query can be used _________________________________
Replace value with one of the following: _________________________________
(1) Key, (2) External characteristic value,
_________________________________
(3) Description, (4) Attribute value, (5) Hierarchy attribute
_________________________________
8
Tip 1: Select Between Doc, Local, and Group Currency
Amounts _________________________________
• How can the report show one currency amount based _________________________________
on user selection?
_________________________________
Show Document vs. Local vs. Group Currency Amount
No separate queries per key figure _________________________________
No separate set of key figures that results in a huge report _________________________________
Company Co
4000
G/L Accoun
100000
YTD Dec 2006
£0.00
YTD Dec 2006
$0.00
Beginning Bala Beginning Bala Jan 2007 Activity
£0.00 $0.00
Jan 2007 Activity Jan YTD
£0.00
Jan YTD
$0.00 _________________________________
4000 100001 £0.00 $0.00 £0.00 $0.00 $37,550.09 £0.00 $37,550.09
4000 100002 £0.00 $0.00 £0.00 $0.00 £19,136.24 £19,136.24 $0.00
4000
4000
100003
100004
£0.00
£4,976.06
$9,747.62
$98,340.95
£0.00
£4,976.06
$9,747.62
$98,340.95 £ (16,935.81)
$ (33,215.63)
$93,035.51
£0.00
£ (11,959.75)
$ (23,468.01)
$191,376.46
_________________________________
4000 100005 £50,202.13 $1,731,711.17 £50,202.13 $1,731,711.17 £47,326.96 $11,448,260.85 £97,529.09 $13,179,972.02
_________________________________
4000 100006 £884,022.24 $0.00 £884,022.24 $0.00 £5,832,742.71 £6,716,764.95 $0.00
4000 100007 £0.00 $6,050.18 £0.00 $6,050.18 $100,408.74 £0.00 $106,458.92
4000 100008 £0.00 $0.00 £0.00 $0.00 $ (34,941.82) £0.00 $ (34,941.82)
4000 100009 £0.00 $985,524.31 £0.00 $985,524.31 $5,143,117.10 £0.00 $6,128,641.41
Amt in Amt in
_________________________________
GBP USD
Two sets of amounts (GBP, USD) for 12
_________________________________
periods makes the report hard to read!
Warning
9
Tip 1: Select Between Doc, Local, and Group Currency
Amounts (cont.) _________________________________
• Solution _________________________________
Prompt for key figure value in the selection screen _________________________________
Use formula variable
_________________________________
_________________________________
_________________________________
Use the above-selected value to pick the appropriate
key figure
_________________________________
Use IF condition in the calculated key figure _________________________________
_________________________________
_________________________________
10
Tip 1: Select Different Currency Amounts – Steps
_________________________________
_________________________________
_________________________________
Step 1: Create a New CKF
_________________________________
_________________________________
_________________________________
Step 4: Variable’s features
Step 2: Create a new Formula Variable (FV) _________________________________
_________________________________
_________________________________
_________________________________
Step 5: Variable’s value type
Step 3: FV Definition 11
Tip 1: Select Different Currency Amounts – Steps (cont.)
_________________________________
• _________________________________
_________________________________
_________________________________
Step 6: Save Formula Var (FV) _________________________________
Step 9: Give a Technical Name
Step 8: Write the IF condition
_________________________________
_________________________________
_________________________________
_________________________________
Step 7: Select FV
_________________________________
Step 10: Use the Calc. Key Fig. in the query
12
Tip 1: Select Between Doc, Local, and Group Currency
Amounts _________________________________
• Output when the Local currency is selected _________________________________
Output in GBP _________________________________
_________________________________
Com pany C o G /L A c co u n YTD Dec 2006 B e g in n ing B a la n ce Ja n 2 0 0 7 A c tivity Ja n Y T D F e b 2 0 0 7 A ctiv ity Feb YTD
4000 10 0 0 0 0 £0 .0 0 £ 0 .0 0 £ 0 .0 0 £ 0.0 0
4000 10 0 0 0 1 £0 .0 0 £ 0 .0 0 £ 0 .0 0 £ 0.0 0
4000 10 0 0 0 2 £0 .0 0 £ 0 .0 0 £ 1 9 ,1 3 6 .2 4 £ 1 9 ,1 3 6 .2 4 £ (1 1 ,7 3 9 .8 6 ) £ 7,3 9 6.3 8
4000 10 0 0 0 3 £0 .0 0 £ 0 .0 0 £ 0 .0 0 £ 0.0 0
4000
4000
10 0 0 0 4
10 0 0 0 5
£4 ,9 7 6.0 6
£ 50 ,2 02 .1 3
£ 4 ,9 7 6 .0 6
£ 5 0 ,2 0 2 .1 3
£ (1 6 ,9 3 5 .8 1 )
£ 4 7 ,3 2 6 .9 6
£ (1 1 ,9 5 9 .7 5 )
£ 9 7 ,5 2 9 .0 9
£ (1 0 8 ,1 7 8 .2 1 )
£ 4 ,4 3 3 ,5 1 1 .3 5
£ (1 20 ,1 3 7.9 6 )
£4 ,5 3 1,0 4 0.4 4 _________________________________
4000 10 0 0 0 6 £ 8 84 ,0 22 .2 4 £ 8 8 4 ,0 2 2 .2 4 £ 5 ,8 3 2 ,7 4 2 .7 1 £ 6 ,7 1 6 ,7 6 4 .9 5 £ (6 ,2 4 5 ,8 6 0 .0 8 ) £ 4 7 0,9 0 4.8 7
4000 10 0 0 0 7 £0 .0 0 £ 0 .0 0 £ 0 .0 0 £ 0.0 0
4000
4000
10 0 0 0 8
10 0 0 0 9
£0 .0 0
£0 .0 0
£ 0 .0 0
£ 0 .0 0
£ 0 .0 0
£ 0 .0 0
£ 0.0 0
£ 0.0 0 _________________________________
• Output when the Group currency is selected _________________________________
_________________________________
Output in USD
_________________________________
Company Cod G/L Account YTD Dec 2006 Beginning Balance Jan 2007 Activity Jan YTD Feb 2007 Activity Feb YTD
4000 100000 $0.00 $0.00 $0.00 $0.00
4000
4000
100001
100002
$0.00
$0.00
$0.00
$0.00
$37,550.09 $37,550.09
$0.00
$ (23,035.80) $14,514.29
$0.00
_________________________________
4000 100003 $9,747.62 $9,747.62 $ (33,215.63) $ (23,468.01) $ (212,284.71) $ (235,752.72)
4000 100004 $98,340.95 $98,340.95 $93,035.51 $191,376.46 $8,700,110.75 $8,891,487.21
4000 100005 $1,731,711.17 $1,731,711.17 $11,448,260.85 $13,179,972.02 $ (12,255,891.85) $924,080.17
4000 100006 $0.00 $0.00 $0.00 $0.00
4000
4000
100007
100008
$6,050.18
$0.00
$6,050.18
$0.00
$100,408.74
$ (34,941.82)
$106,458.92 $ (17,991.09) $88,467.83 13
$ (34,941.82) $34,941.82 $0.00
Five Advanced BEx Tips and Tricks
_________________________________
1. Select between Document, Local, and Group Currency _________________________________
Amounts
_________________________________
• Advanced BEx features – a crash course
2. Calculate with attributes of a characteristic _________________________________
3. Create exception on a characteristic _________________________________
4. Alert level and process chain _________________________________
5. Customer exit variables and Trial Balance report _________________________________
_________________________________
_________________________________
_________________________________
14
Tip 2: Calculate with Attributes of a Characteristic
_________________________________
• How can you include characteristic/attribute in _________________________________
computations?
Classic business case: _________________________________
Compute total price using the attribute “Price” under _________________________________
characteristic “Material”
Financial business case: _________________________________
How much the customer is over the credit limit _________________________________
• Solution _________________________________
Get the customer’s credit limit from customer master data into
a key figure _________________________________
Create a formula variable to get the attribute of _________________________________
0CUSTOMER
Compute the difference between “account balance” and _________________________________
“credit limit”
15
Tip 2: Calculate with Attributes of a Characteristic (cont.)
_________________________________
• Create a formula variable to get credit limit value _________________________________
From 0CUSTOMER’s attribute _________________________________
_________________________________
1
_________________________________
_________________________________
2
_________________________________
_________________________________
Step 1: Create a new Formula Var. (FV)
_________________________________
Step 2: Process FV by
Replacement Path
_________________________________
Step 3: Select Reference
Characteristic 16
Tip 2: Calculate with Attributes of a Characteristic (cont.)
_________________________________
• Select Attribute of 0CUSTOMER _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
Step 4: Select the attribute of the characteristic Step 6: Use the FV in a Calc. Key Fig.
_________________________________
_________________________________
_________________________________
_________________________________
Step 5: Save the Formula Variable Step 7: Use the Cal. Key Figure in query
17
Five Advanced BEx Tips and Tricks
_________________________________
1. Select between Document, Local, and Group Currency _________________________________
Amounts
_________________________________
• Advanced BEx features – a crash course
_________________________________
2. Calculate with attributes of a characteristic
3. Create exception on a characteristic _________________________________
4. Alert level and process chain _________________________________
5. Customer exit variables and Trial Balance report _________________________________
_________________________________
_________________________________
_________________________________
18
Tip 3: Create Exception on a Characteristic
_________________________________
• Exceptions are created only on a key figure _________________________________
• Steps to create an exception on a characteristic _________________________________
Suited for characteristic with mostly numeric values
Step 1: Create a formula variable (refer to previous tips) _________________________________
Use replacement path in the formula variable _________________________________
Select the characteristic
Step 2: Use the formula variable
_________________________________
After you finish, use the formula variable in the calculated _________________________________
key figure
Step 3: Create exception _________________________________
Create exception on the calculated key figure created in _________________________________
Step 2
_________________________________
19
Five Advanced BEx Tips and Tricks
_________________________________
1. Select between Document, Local, and Group Currency _________________________________
Amounts
_________________________________
• Advanced BEx features – a crash course
2. Calculate with attributes of a characteristic _________________________________
3. Create exception on a characteristic _________________________________
4. Alert level and process chain _________________________________
5. Customer exit variables and Trial Balance report _________________________________
_________________________________
_________________________________
_________________________________
20
Tip 4: Alert Level and Process Chain
_________________________________
• Alert level Value 8 or 9 in exception of a query _________________________________
Reporting agent that uses the query will cause the termination
_________________________________
of the process chain
• Business case _________________________________
Stop data load based on the result of a query _________________________________
If the account balance in SAP BW is not same as in SAP R/3 _________________________________
• Solution
_________________________________
Step 1: Create an exception for
the business condition _________________________________
Set the “Alert Level” to Bad 8 _________________________________
or Bad 9
_________________________________
21
Tip 4: Alert Level and Process Chain (cont.)
_________________________________
• Solution (cont.) _________________________________
Step 2: Create reporting agent setting _________________________________
Use the exception
_________________________________
_________________________________
Step 3: Execute process chain _________________________________
If the exception condition is met, then the process chain _________________________________
will not proceed
_________________________________
_________________________________
Details _________________________________
22
Five Advanced BEx Tips and Tricks
_________________________________
1. Select between Document, Local, and Group Currency _________________________________
Amounts
_________________________________
• Advanced BEx features – a crash course
2. Calculate with attributes of a characteristic _________________________________
3. Create exception on a characteristic _________________________________
4. Alert level and process chain _________________________________
5. Customer exit variables and Trial Balance report _________________________________
_________________________________
_________________________________
_________________________________
23
Tip 5: Customer Exit Variables and Trial Balance Report
_________________________________
• Objective: Create YTD Trial Balance report from G/L _________________________________
transactions InfoProvider
_________________________________
Period balance
For balance sheet accounts, it is the sum of all activities _________________________________
since inception _________________________________
For income statement accounts, it is the sum of all
activities since the beginning of a selected year
_________________________________
Challenge _________________________________
Two different criteria to derive the same account balance _________________________________
Solution
_________________________________
Use customer exit variable
_________________________________
24
Tip 5: Customer Exit Variables and Trial Balance
Report (cont.) _________________________________
• Steps _________________________________
1. Accept the “as of” period (0RMA_FIP) in the selection screen
_________________________________
2. Balance in Balance Sheet accounts
Use the indicator – “X” for balance sheet account
_________________________________
In 0FISCPER time restriction, use the offset -9999 in “from _________________________________
value” and offset 0 in “to value” for 0RMA_FIP
_________________________________
Hide this calculated key figure
_________________________________
_________________________________
_________________________________
_________________________________
25
Tip 5: Customer Exit Variables and Trial Balance
Report (cont.) _________________________________
3. Create a customer exit variable – ZFYPJAN
_________________________________
This variable gets “Beginning of Year” (e.g., Jan)
4. Balance in Income Statement (P&L) accounts _________________________________
Use the value “#” (Blank) for indicator balance sheet _________________________________
For 0FISCPER time restriction, use “ZFYPJAN” as “from
value” and 0RMA_FIP as “to value” (Fig 1) _________________________________
Hide this calculated key figure _________________________________
5. Add the two balances to get the Period balance (Fig 2)
_________________________________
Show this value in the output
_________________________________
_________________________________
_________________________________
Fig 1 Fig 2 26
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks _________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
27
Processing Workbooks En Masse
_________________________________
• Scenario _________________________________
Process a workbook for many selections _________________________________
For example, DSO report for 80 countries for different
months _________________________________
Account balance report for 90 company codes _________________________________
• Possible solutions _________________________________
1. Use precalculation server for workbooks
_________________________________
This solution is only available from SAP BW 3.5 onwards
Many variants and broadcaster settings have to be _________________________________
created _________________________________
2. Run manually
_________________________________
Time consuming, cumbersome, and error prone
3. Automation using VBA
28
SAPBExqueries sheet
_________________________________
• When you save a workbook, a repository sheet named _________________________________
“SAPBExqueries” is inserted in the workbook
_________________________________
This “hidden” sheet has a specific structure
SAP can change the structure of this sheet _________________________________
Warning This sheet contains QueryId, variable values, etc. _________________________________
Use macro SAPDebugprint to decipher this sheet’s
_________________________________
contents
A variable’s name is in column FZ, values in GE _________________________________
and GG _________________________________
_________________________________
_________________________________
29
Tool to Automate Workbook Execution
_________________________________
• Objective _________________________________
Accept the selection screen values in an Excel file
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
When you click the “Run Workbook” button, these values will
be passed into the workbook specified in Column C _________________________________
See the “Help” sheet in the Excel file in the CD for more _________________________________
details
_________________________________
30
Prerequisites to Use Workbook Automation Tool
_________________________________
• In the workbook properties, “Save _________________________________
and reuse variable values” must _________________________________
be turned ON
Thus user interaction is eliminated _________________________________
_________________________________
_________________________________
• Before executing the Automation tool (Excel sheet),
_________________________________
start the BEx analyzer
Start All Programs Business Explorer Analyzer _________________________________
_________________________________
_________________________________
31
Workbook Automation Tool: Details
_________________________________
• How does this tool work? _________________________________
The VBA code in the automation tool calls the BEX VBA
“SAPBEXrefresh” function to run the workbook
_________________________________
The variable values are set directly using a VBA statement _________________________________
Alternatively, use “SAPBEXsetVariables” function _________________________________
The tool currently supports only single value and intervals
_________________________________
You can change VBA code to accept select options
_________________________________
_________________________________
_________________________________
_________________________________
32
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks _________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
33
Formatting in BEx Workbook
_________________________________
• Use BEx toolbar’s formatting option _________________________________
If you use Excel’s formatting option, then those cells will be overwritten by
SAP’s standard option the next time you refresh the workbook _________________________________
• The four options in BEx tool bar are listed below _________________________________
_________________________________
_________________________________
_________________________________
Font – Helps you to change the font
Patterns – Here you can change the fill _________________________________
(cell shade) color
Borders – Lets you change the borders for
_________________________________
the selected cells _________________________________
Alignment – Here you can set word wrap,
merge cells, etc.
34
Formatting in BEx Workbook (cont.)
_________________________________
• Create a workbook template for the company/dept. _________________________________
In the workbook
_________________________________
Insert logos and branding elements (colors, font, etc.)
You can also move the results area _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
35
Formatting in SAP NetWeaver BI Workbook
_________________________________
• Convert to Formula mode _________________________________
_________________________________
_________________________________
_________________________________
This option enables you to use Excel formatting capabilities
This formatting will not be overwritten by SAP _________________________________
SAP generates the BExGetData formula in the data cells of _________________________________
the analysis grid
_________________________________
When you navigate in your query results, only the
values for the data are retrieved from the server; _________________________________
standard formatting information is not retrieved
_________________________________
36
Formatting in SAP NetWeaver BI Using Report Designer
_________________________________
• Starting point – Query _________________________________
Easily convert existing query output to Report Designer _________________________________
• Formatting options in Report Designer
_________________________________
Insert customized page header/footer
Insert rows, columns; change height; etc. _________________________________
Apply group level changes _________________________________
Standard formatting options
_________________________________
Font, orientation, border
Cell-level formatting is also possible
_________________________________
Change background color _________________________________
Field wrapping, etc. _________________________________
37
Formatting in SAP NetWeaver BI Using Report
Designer (cont.) _________________________________
• Conditional formatting _________________________________
Allows you to format separately based on characteristic
values
_________________________________
Example: If the vendor is AGILYSYS INC, then show the _________________________________
output in a green color
_________________________________
Use the menu option
Place the cursor on the cell of the characteristic _________________________________
Use menu option: Format Conditional Formatting _________________________________
In the dialog box, select the characteristic value _________________________________
_________________________________
_________________________________
38
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks _________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
39
Alerts and Exceptions – Make the System Do the Job
_________________________________
• Use exceptions to spot anomalies in the report _________________________________
Exceptions are created on a key figure _________________________________
• Create alerts to notify the exception cases
_________________________________
Use a reporting agent
Use BEx Broadcaster _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
40
Steps to Create Exceptions
_________________________________
1. From the tool bar, click on the Exception icon _________________________________
_________________________________
_________________________________
_________________________________
2. In the new pop-up screen, enter a description and _________________________________
select the key figure
_________________________________
_________________________________
_________________________________
_________________________________
41
Steps to Create Exceptions (cont.)
_________________________________
3. Click New _________________________________
_________________________________
4. Enter criteria. (While entering negative values, From _________________________________
value should be greater than To value.)
_________________________________
_________________________________
_________________________________
5. Accept the criteria. Click Transfer.
_________________________________
_________________________________
6. Go to the Cell Restrictions Tab. Click New.
_________________________________
42
Steps to Create Exceptions (cont.)
_________________________________
7. Select cell restriction condition (important step) _________________________________
_________________________________
_________________________________
8. (Optional) Select scope of restriction _________________________________
_________________________________
_________________________________
_________________________________
9. Final step is to click OK
_________________________________
_________________________________
43
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks
_________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
44
Objectives of Data Reconciliation
_________________________________
• Why reconcile? _________________________________
To verify data accuracy in SAP BW
To check data quality in SAP BW
_________________________________
Validate (health check) data model and Data Warehouse _________________________________
(DWH) architecture
Data transformation and aggregation
_________________________________
• How do you reconcile? _________________________________
1. Get the same business data using an alternative source _________________________________
2. Compare that data with the data in “productive" InfoProviders
Create a MultiProvider = (productive InfoProvider + _________________________________
alternate source) _________________________________
Create validation queries on that MultiProvider
_________________________________
45
Data Reconciliation – Alternate Sources of Data
_________________________________
• Use SAP-supplied reconciliation data sources _________________________________
FI/CO: 0CO_OM_CCA_1, 0CO_OM_CCA_9, 0FIGL_6, etc. _________________________________
• Create generic data sources
_________________________________
Compare data from generic data source to “productive” cube
_________________________________
• Compare InfoProvider (IP) contents to PSA
Remote cube based on “export data source” of PSA table _________________________________
Compare this remote cube’s data with productive IP’s data _________________________________
• Use remote cube (Virtual InfoProvider) _________________________________
Create a Virtual InfoProvider (VIP) to read data from SAP R/3
_________________________________
E.g., get data from Special Ledger table or GLT0 table
Compare the VIP’s data to that of productive IP’s data _________________________________
Take-home material contains detailed info on this topic
46
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks
_________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
47
Fiscal Year Change
_________________________________
• Objective _________________________________
Ensure SAP BW reports work with old and new fiscal calendar
_________________________________
• Two approaches: Historical Truth vs. Revisionism
_________________________________
Historical Truth
“As-is” situation _________________________________
Keep the old data as it is _________________________________
Revisionism
_________________________________
“The way to be” situation
Change historical data with the new fiscal year settings _________________________________
_________________________________
_________________________________
48
Riddle on Calendar
_________________________________
Was it born out of chaos from priests’ twist? _________________________________
Was it born out of affair from Emperor’s tryst? _________________________________
A Calendar that bore an Emperor’s name _________________________________
A Calendar, that attained global fame. _________________________________
© Jeevan Kumar Ravindran
_________________________________
Question:
_________________________________
Name the Calendar
_________________________________
Your Fiscal year project does not have to be a riddle or a puzzle.
If you plan in advance, there is no need for any twists or trysts.
_________________________________
_________________________________
49
An Easy Method to Make SAP BW Reports Fiscally
Compliant _________________________________
• In SAP ERP – use the existing fiscal year variant _________________________________
Change the period values
_________________________________
Shorten the transitioning year’s fiscal calendar
New Special Ledger (SL) for reporting (optional) _________________________________
• In SAP BW (SAP NetWeaver BI) _________________________________
Two major areas of change: _________________________________
Presentation – selection screen in new calendar format
_________________________________
Processing – conversion of selected period values
_________________________________
_________________________________
_________________________________
50
Fiscal Year Change – Fiscal to Calendar
_________________________________
• Presentation – selection screen values _________________________________
Before fiscal year change (1 Year = April to March)
_________________________________
Example: In 2005, the fiscal period 005/2005 equals
August/2005 _________________________________
In Year 2005, period 005/2005 was _________________________________
calendar month August
_________________________________
Before Fiscal Year change
After fiscal year change (1 Year = Jan to Dec) _________________________________
New calendar starting from 2006: fiscal month equals _________________________________
calendar month
_________________________________
In new fiscal calendar, the old
period 005/2005 should be
treated as calendar month May _________________________________
After Fiscal Year change
51
Fiscal to Calendar Year Change – Presenting Variables
_________________________________
• Objective: Accept the selection screen variable in the _________________________________
new calendar’s format _________________________________
Instead of using fiscal year variant (FYV) – V3, use K4 in
the dummy restricted key figure _________________________________
This key figure value will be hidden in the query result _________________________________
FYV – “V3” runs April to March until 2005; and beginning
_________________________________
Note in 2006, V3 runs from January to December
_________________________________
Note: This variable (ZFSYRPR) is later
used in the processing step
_________________________________
_________________________________
_________________________________
52
Fiscal to Calendar Year Change – Processing Variables
_________________________________
• Steps to convert Key figures restricted on 0FISCPER _________________________________
Step 1 – (only once)
Define a customer exit variable – ZCALFIS1 _________________________________
This customer exit variable will be based on the _________________________________
variable defined for presentation (ZFSYRPR)
_________________________________
Step 1 – Create a customer-exit variable
_________________________________
Step 2 – (only once)
_________________________________
Write ABAP code to derive the fiscal period value from the
calendar month in the BEx variable user exit – RSR00001 _________________________________
Step 3 – in all queries
_________________________________
Use the variable ZCALFIS1
Use normal fiscal year variant – V3 _________________________________
Step 3 – Change RKFs
53
What We’ll Cover …
_________________________________
• Advanced SAP BEx tips and tricks _________________________________
• Technique to process workbooks en masse _________________________________
• Formatting tips and tricks _________________________________
• Alerts and exceptions – make the system do the job
_________________________________
• Reconciliation options
_________________________________
• Fiscal year change
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
54
Resources
_________________________________
• BW Expert _________________________________
Joerg Boeke, “22 Functions to Automate and Enhance Your _________________________________
BW Reports in Excel,” (BW Expert, March 2004).
• SAP Developer Network _________________________________
Search on How to guide: BW and Excel Features _________________________________
How To… Reconcile Data Between SAP Source Systems and _________________________________
SAP NetWeaver BI
www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/ _________________________________
uuid/7a5ee147-0501-0010-0a9d-f7abcba36b14 _________________________________
_________________________________
_________________________________
55
7 Key Points to Take Home
_________________________________
• By using advanced BEx features, such as Formula _________________________________
variables and Replacement path, users can create
_________________________________
reports with rich features
• BEx variable user exit improves flexibility to report on _________________________________
various time frames _________________________________
• VBA functions in BEx open the door to perform many _________________________________
useful functions with the query and query output
_________________________________
• New formatting options with SAP Netweaver BI in BEx
_________________________________
Analyzer, Report Designer, and Web Application
Designer eliminate the need for third-party reporting _________________________________
applications _________________________________
56
7 Key Points to Take Home (cont.)
_________________________________
• Make the system work for you: Create exceptions on _________________________________
anomalies and automate sending alerts
_________________________________
• Reconciliation of SAP BW data to your source system
_________________________________
ensures data accuracy and instills credibility in SAP
BW users _________________________________
• During fiscal year change, you can make BEx queries _________________________________
work with a new calendar without converting data in all _________________________________
the cubes
_________________________________
_________________________________
_________________________________
57
Your Turn!
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
How to contact me: _________________________________
Jeevan Kumar Ravindran _________________________________
jeevan@infoelixir.com
58
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Wellesley Information Services, 990 Washington Street, Suite 308, Dedham, MA 02026
Copyright © 2007 Wellesley Information Services. All rights reserved.