User manual BUSINESS OBJECTS GENERAL LEDGER RAPID MART XI 3.2 FOR SAP SOLUTIONS BUSINESS GUIDE 12.2.0.0

[. . . ] SAP BusinessObjects General Ledger Rapid Mart XI 3. 2, version for SAP solutions - Business Guide Version 12. 2. 0. 0 October 2009 Copyright © Copyright 2009 SAP AG. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Trademarks 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. [. . . ] That is, a single row in the table represents an entire fiscal year's worth of data, with separate fields for monetary amounts in each fiscal period. GL Plan carries amounts in Transaction (Document) Currency, Local (Company Code) Currency, and Group Currency. In Classic General Ledger, GL Plan is derived from the FI-SL (FI Standard Ledger). A Standard Ledger is a pre-configured ledger delivered in SAP solutions The data model and specific predefined functions for summarization of business requirements are all included. The General Ledger Summary Section (discussed later in this chapter) is a part of the same Standard Ledger definition. In New GL, the GL Plan is derived as a subset of the Ledger Detail (New FI Standard Ledger). Rapid Mart Processing Extracted GL plan data is stored in the GL_PLAN_FACT and the associated dimension tables: Dimensions This section includes the following dimensional attributes, and their associated dimension tables Attribute Business Area Chart of Accounts Dimension Table Name(s) BUSINESS_AREA CHART_OF_ACCOUNTS SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 67 3 Rapid Mart Subject Areas General Ledger Plan Section Attribute Company Code Debit / Credit Indicator Document Category Document Type Financial Version Fiscal Period GL Account & Hierarchy Dimension Table Name(s) COMPANY_CODE DEBIT_CREDIT_INDICATOR DOCUMENT_CATEGORY DOCUMENT_TYPE & DOCUMENT_CATEGORY FINANCIAL_VERSION FISC_PERIOD GL_ACCOUNT_HIER, GL_ACCOUNT_HIER_BAD, GL_ACCOUNT_HIER_HZ, GL_ACCOUNT_HIER_STAGE, GL_ACCOUNT_HIER_VR, GL_ACCOUNT_MASTER CURRENCY FINANCIAL_RECORD_TYPE REFERENCE_PROCEDURE SEGMENT FISL_LEDGER TIME_DIM UNIT_OF_MEASURE Monetary Currency Record Type Reference Procedure Segment (New GL only) Special Ledger (Master Data) Time Dimension Unit of Measure Special Ledger Group (New GL only) LEDGER_GROUP Fact Table Fields In Classic GL, the GL Plan Section extracts data from the SAP solutions table for General Ledger Planning: GLP0. In New GL, the GL Plan is a subset from LEDGER_FACT_HZ for budget amounts. It contains the following measures: GL_PLAN_FACT Measure Column Name AMT_PER_BEGIN_GRP AMT_PER_BEGIN_LOC Description Period Beginning Balance in Group Currency Period Beginning Balance in Local (Company Code) Currency 68 SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide Rapid Mart Subject Areas General Ledger Plan Section 3 GL_PLAN_FACT Measure Column Name AMT_PER_BEGIN_TRX AMT_PER01_GRP AMT_PER16_GRP AMT_PER01_LOC AMT_PER16_LOC AMT_PER01_TRX AMT_PER016_TRX Description Period Beginning Balance in Transaction (Document) Currency Amounts for Periods 1 -16 of a fiscal year in Group Currency Amounts for Periods 1 -16 of a fiscal year in Local (Company Code) Currency Amounts for Periods 1 -16 of a fiscal year in Transaction (Document) Currency SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 69 3 Rapid Mart Subject Areas General Ledger Plan Section Rapid Mart Data The GL Plan section includes budget information. The GL_PLAN_FACT table and associated dimension tables are organized as a star schema as shown below. The associated GL Account Hierarchy is also shown. : GL PLAN You can analyze GL PLan information along several dimensions: · · · · · Accounting Document Category Business Area Chart of Accounts Company Code Monetary Currencies · · · Transaction (Document) Currency Local (Company Code) Currency Group Currency 70 SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide Rapid Mart Subject Areas General Ledger Summary Section 3 · Special Ledger Dimensions · · · · · · · Record Type Document Type Ledger Group (New GL only) Special Ledger ID Version Segment (New GL only) Time · · · · · · · · · Fiscal Periods Time Dimension General Ledger Account & Hierarchy Reference Procedure Units of Measure Which company codes have not entered budgets for the upcoming fiscal year?How many reversing entries (changes) have been made to the budget for each company code?What is the difference in currently active budget versions for selected accounts? Using the GL Plan Section, you can answer questions like: General Ledger Summary Section Overview The General Ledger Summary (GL Summary) section stores summarized financial information (Totals information) for the Finance Module. This is a main data source for analyzing Financial Actuals and can be used to compare Actuals vs. Along with other Special Ledger Summary tables, this is a main source for analyzing and creating financial statements. In horizontal organization each fiscal period is represented by a column. A single row in the table represents an entire fiscal year's worth of data, with separate fields for monetary amounts in each fiscal period. SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 71 3 Rapid Mart Subject Areas General Ledger Summary Section In vertical organization, each fiscal period is represented by a row. There is a separate row for each fiscal period / fiscal year combination. GL Summary carries amounts in Transaction (Document) Currency, Local (Company Code) Currency, and Group Currency. In Classic General Ledger, GL Summary is derived from the FI-SL Standard Ledger. A Standard Ledger is a pre-configured ledger delivered in SAP solutions. The data model and specific predefined functions for summarization of business requirements are all included. The General Ledger Plan Section (discussed earlier in this chapter) is a part of the same Standard Ledger definition. [. . . ] Results are displayed by Business Area. 232 SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide Reports Cost of Sales Summary 4 YTD Balance Trends by Period View YTD Balance Trends by Period for each of the Top X GL Accounts. Results are displayed by Business Area. Report SQL SELECT DISTINCT COMPANY_CODE. CMPNY_CODE_NAME, SUM(case when COST_OF_SALES_SUMMARY_FACT_VR. RECORD_TYPE_ID = '0' then COST_OF_SALES_SUMMARY_FACT_VR. QTY_DR_YTDCOST_OF_SALES_SUMMARY_FACT_VR. QTY_CR_YTD else 0 end), SUM(case when COST_OF_SALES_SUMMARY_FACT_VR. RECORD_TYPE_ID = '0' then (COST_OF_SALES_SUMMARY_FACT_VR. AMT_DR_YTD_LOCCOST_OF_SALES_SUMMARY_FACT_VR. AMT_CR_YTD_LOC) else 0 end), FISC_PERIOD_COMP_CODE. FISC_YEAR, FISC_PERIOD_COMP_CODE. FISC_YEAR_QUARTER, FISC_PERIOD_COMP_CODE. FISC_YEAR_PERIOD, BUSINESS_AREA. BUS_AREA_FULL_NAME, FUNCTIONAL_AREA. FUNCTNL_AREA_FULL_NAME, GL_ACCOUNT_MASTER. GL_ACCT_FULL_NAME, UNITS_OF_MEAS_BASE. UOM_DESCR, UNITS_OF_MEAS_BASE. UOM_CODE FROM COMPANY_CODE, COST_OF_SALES_SUMMARY_FACT_VR, FISC_PERIOD FISC_PERIOD_COMP_CODE, BUSINESS_AREA, SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 233 4 Reports Cost of Sales Summary FUNCTIONAL_AREA, GL_ACCOUNT_MASTER, UNIT_OF_MEASURE UNITS_OF_MEAS_BASE WHERE ( COST_OF_SALES_SUMMARY_FACT_VR. FISC_PERIOD=FISC_PERIOD_CO MP_CODE. FISC_PERIOD and COST_OF_SALES_SUMMARY_FACT_VR. FISC_YEAR=FISC_PERIOD_COMP_CO DE. FISC_YEAR and COST_OF_SALES_SUMMARY_FACT_VR. FISC_YR_VARIANT_ID = FISC_PERIOD_COMP_CODE. FISC_YR_VARIANT_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. CMPNY_CODE_ID = COMPANY_CODE. CMPNY_CODE_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. FUNCTNL_AREA_ID = FUNCTIONAL_AREA. FUNCTNL_AREA_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. GL_ACCT_ID=GL_ACCOUNT_MA STER. GL_ACCT_ID and COST_OF_SALES_SUMMARY_FACT_VR. CHART_OF_ACCT_ID=GL_ACCO UNT_MASTER. CHART_OF_ACCT_ID ) AND ( UNITS_OF_MEAS_BASE. UOM_ID=COST_OF_SALES_SUMMARY_FACT_V R. UOM_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. BUS_AREA_ID=BUSINESS_AREA . BUS_AREA_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. FISC_YEAR = COST_OF_SALES_SUMMARY_FACT_VR. PARTITION_YEAR ) AND ( ( COMPANY_CODE. CMPNY_CODE_NAME = @prompt('Company Code:', 'A', 'Company Code\Company Name', Mono, Free, Persistent, , User:0) ) AND FISC_PERIOD_COMP_CODE. FISC_YEAR = @prompt('Fiscal Year:', 'N', 'Company Code Fiscal Period\Fiscal Year', Mono, Free, Persistent, , User:1) AND GL_ACCOUNT_MASTER. CHART_OF_ACCT_FULL_NAME = @prompt('Chart Of Accounts Full Name:', 'A', 'GL Account\GL Account Chart Of Accounts Full Name', Mono, Free, Persistent, , User:0) AND ( GL_ACCOUNT_MASTER. HIER_ID = 'ACCOUNT' ) AND GL_ACCOUNT_MASTER. GL_ACCT_FULL_NAME In ( SELECT View__1. Column__1 FROM ( SELECT GL_ACCOUNT_MASTER. GL_ACCT_FULL_NAME AS Column__1, 234 SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide Reports Cost of Sales Summary 4 RANK() OVER( ORDER BY SUM(case when COST_OF_SALES_SUMMARY_FACT_VR. RECORD_TYPE_ID = '0' then (COST_OF_SALES_SUMMARY_FACT_VR. AMT_DR_YTD_LOCCOST_OF_SALES_SUMMARY_FACT_VR. AMT_CR_YTD_LOC) else 0 end) DESC ) AS Rk__1 FROM GL_ACCOUNT_MASTER, COST_OF_SALES_SUMMARY_FACT_VR WHERE ( COST_OF_SALES_SUMMARY_FACT_VR. GL_ACCT_ID=GL_ACCOUNT_MA STER. GL_ACCT_ID and COST_OF_SALES_SUMMARY_FACT_VR. CHART_OF_ACCT_ID=GL_ACCO UNT_MASTER. CHART_OF_ACCT_ID ) AND ( COST_OF_SALES_SUMMARY_FACT_VR. FISC_YEAR = COST_OF_SALES_SUMMARY_FACT_VR. PARTITION_YEAR ) GROUP BY GL_ACCOUNT_MASTER. GL_ACCT_FULL_NAME ) View__1 WHERE View__1. Rk__1 <= @prompt('Top x GL Account Full Name (Enter x)', 'N', , Mono, Free, Persistent, , User:2) ) ) GROUP BY COMPANY_CODE. CMPNY_CODE_NAME, FISC_PERIOD_COMP_CODE. FISC_YEAR, FISC_PERIOD_COMP_CODE. FISC_YEAR_QUARTER, FISC_PERIOD_COMP_CODE. FISC_YEAR_PERIOD, BUSINESS_AREA. BUS_AREA_FULL_NAME, FUNCTIONAL_AREA. FUNCTNL_AREA_FULL_NAME, GL_ACCOUNT_MASTER. GL_ACCT_FULL_NAME, UNITS_OF_MEAS_BASE. UOM_DESCR, UNITS_OF_MEAS_BASE. UOM_CODE SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 235 4 Reports Cost of Sales Summary Cost of Sales Rolling 12 Periods vs. Period Comparison View Cost of Sales Rolling 12 periods versus Actual PTD Balances for the selected Fiscal Years. by Company View rolling 12 totals versus actual PTD balances for all companies (with a break by company full name). 236 SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide Reports Cost of Sales Summary 4 by Functional Area View rolling 12 totals versus actual PTD balances for all functional areas, with a section break by Company Code. Calculations · Period Weight = [CS SUM Vr Actual PTD Balance LOC]/[CS SUM Vr Actual Rolling Ttl LOC] Report SQL All report SQL is located in the Rapid Mart installation directory under "\Sample\DDL". Recommended Table Joins To analyze Cost of Sales Summary data you will need to create joins between the fact and dimension tables as follows SAP BusinessObjects General Ledger Rapid Mart XI 3. 2 for SAP solutions Business Guide 237 4 Reports Cost of Sales Summary Note: Additional joins between dimension and hierarchy tables are shown in "Recommended table joins" on page 27 for master reports. [. . . ]