Hello,

We currently use SAP for our financials, and are in the process of assessing the feasibility of recreating a balance sheet using WebFOCUS. Currently, the report is run within SAP using a tool called Report Painter.

The balance sheet has approximately 1100 accounts (and totals) for the rows, and roughly 28 columns of profit centers (and totals) for the columns.

I can see how we could easily use FML to get our accounts formatted correctly, however I am having difficulty with the columns. Each of our columns is made up of groups of profit centers (sets in SAP). I need to have each of the specified groups of profit centers rolled up under a specific column heading. Here is a simplified example

Account Widgets Gizmos Gadgets
------------------------------------------------------------------------------------
10 $50 $60
20 $10 $10 $30
30 $20 $10 $10
40 (Securities) $80 $80 $40
50 $10 $10 $10
60 $40 $30 $20
70 (Cash on Hand) $50 $40 $30
80 (Cash & Equivalents) $130 $120 $70


In this example, Widgets would be made up of a set in SAP called Widgets. This set would contain multiple sets, but would contain profit centers 11357, 15790, 17950, 14320, and 15990 when fully resolved. Similarly, Gizmos and Gadgets would also contain multiple groups of profit centers, but would be displayed as it is above.

Here are my questions:

1. Does anyone have any experience with doing something like this using SAP sets? Can they be read and used easily, or does everything need to be done manually?

2. How would you roll up multiple profit center groups into headings like above? Using FML and some sample data, I was able to create the basic row structure with each profit center across the columns. My initial thinking was that I would have to write 1 fex for each column grouping, and then merge all of the columns together using a separate report. However, I have to believe there is an easier way.

Tim,

Not sure about SAP sets but FML is very adapatble and you can create rows and columns as required -

TABLE FILE CAR
SUM RCOST
ACROSS BODYTYPE AS '' COLUMNS 'CONVERTIBLE' AND 'COUPE' AND 'ROADSTER' AND 'Sporty'
                          AND 'HARDTOP' AND 'SEDAN' AND 'Sedate' AND 'Total'
FOR COUNTRY
'ENGLAND'   LABEL Label1 AS 'England' OVER
RECAP Label1(4) = Label1(1) + Label1(2) + Label1(3); OVER
RECAP Label1(7) = Label1(5) + Label1(6); OVER
RECAP Label1(8) = Label1(4) + Label1(7); OVER
'FRANCE'    LABEL Label2 AS 'France ' OVER
RECAP Label2(4) = Label2(1) + Label2(2) + Label2(3); OVER
RECAP Label2(7) = Label2(5) + Label2(6); OVER
RECAP Label2(8) = Label2(4) + Label2(7); OVER
'ITALY'     LABEL Label3 AS 'Italy  ' OVER
RECAP Label3(4) = Label3(1) + Label3(2) + Label3(3); OVER
RECAP Label3(7) = Label3(5) + Label3(6); OVER
RECAP Label3(8) = Label3(4) + Label3(7); OVER
'W GERMANY' LABEL Label4 AS 'Germany' OVER
RECAP Label4(4) = Label4(1) + Label4(2) + Label4(3); OVER
RECAP Label4(7) = Label4(5) + Label4(6); OVER
RECAP Label4(8) = Label4(4) + Label4(7); OVER
RECAP Label5/D12 = Label1 + Label2 + Label3 + Label4; AS 'Europe' OVER
'JAPAN'     LABEL Label6 AS 'Japan  ' OVER
RECAP Label6(4) = Label6(1) + Label6(2) + Label6(3); OVER
RECAP Label6(7) = Label6(5) + Label6(6); OVER
RECAP Label6(8) = Label6(4) + Label6(7); OVER
RECAP Label7/D12 = Label6;                            AS 'Asia  ' OVER
RECAP Label8/D12 = Label5 + Label7; AS 'Total'
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  ORIENTATION=LANDSCAPE, GRID=OFF, $
ENDSTYLE
END

This gives you additional rows for Europe, Asia and Total but also sums up columns into Sporty, Sedate and Total.

And before someone jumps in and says "you could get column and rows totals using other syntax" - I know, but when you create additional rows and columns you need to be careful on how you create the totals as normal syntax would give incorrect results.

Instead of some of the RECAPS, you could have SET FORMULTIPLE = ON and then used 'ENGLAND' OR 'FRANCE' OR 'ITALY' OR 'W GERMANY' LABEL nnnn AS 'Europe'. It all depends upon your view of readability as to the method you use.

T

Thanks Tony.

What syntax would you recommend if I only want to show the "Sporty", "Sedate", and "Total" columns? (while still maintaining the same values)

Tim,

I'd be inclined to do a define to get these values -
DEFINE FILE CAR
Sporty/D20 = IF BODY_TYPE IN ('CONVERTIBLE','COUPE','ROADSTER') THEN RCOST ELSE 0;
... etc.

T

Tony,

How would you then integrate that into your previous code? If it went into the Across statement, wouldn't it just display the RCOST values as the column headings? I would want to Sum the RCOST of the various body types in the DEFINE, and then have those values listed for each country as it applies. Please excuse the excessive questions, I am a bit of a newbie to FML. (Although I have read the IBI documentation on it.)

Tim

after the define of the several "type"_rcost you can use them in the FML
If you use the GUI it will help you define the computations

Tim,

My sincere apologies, code follows. Basically the request becomes easier -

DEFINE FILE CAR
  Sporty/D20 = IF BODYTYPE IN ('CONVERTIBLE','COUPE','ROADSTER') THEN RCOST ELSE 0;
  Sedate/D20 = IF BODYTYPE IN ('HARDTOP','SEDAN') THEN RCOST ELSE 0;
END
TABLE FILE CAR
SUM Sporty
    Sedate
    RCOST AS 'Total'
-*ACROSS BODYTYPE AS '' COLUMNS 'CONVERTIBLE' AND 'COUPE' AND 'ROADSTER' AND 'Sporty'
-*                          AND 'HARDTOP' AND 'SEDAN' AND 'Sedate' AND 'Total'
FOR COUNTRY
'ENGLAND'   LABEL Label1 AS 'England' OVER
'FRANCE'    LABEL Label2 AS 'France ' OVER
'ITALY'     LABEL Label3 AS 'Italy  ' OVER
'W GERMANY' LABEL Label4 AS 'Germany' OVER
RECAP Label5/D12 = Label1 + Label2 + Label3 + Label4; AS 'Europe' OVER
'JAPAN'     LABEL Label6 AS 'Japan  ' OVER
RECAP Label7/D12 = Label6;                            AS 'Asia  ' OVER
RECAP Label8/D12 = Label5 + Label7; AS 'Total'
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  ORIENTATION=LANDSCAPE, GRID=OFF, $
ENDSTYLE
END

T

Tony,

Thank you! This is perfect.

Tim,


What I think you really want to do to create your Balance Sheet Report (and any other of the standard Financial Reports) is to use FML Painter with Hierarchy reporting.

This will eliminate updating the report for changes to Account #, Profit Centers, Cost Centers, Cost Elements etc. on a regular basis. Once the hierarchy is built, it will display in the Right Hand frame of report painter in the same format you see when you look at the hierarchy trees in SAPGUI. To build the report you simply select a node, and drag it to the line you want on the report.

For instance..If you are using Account Groups and there is an account group 40 defined for SECURITIES. Within that Acct Grp 40 which has 2 child nodes, who in turn at the lowest level have accounts.

+ 40 Securities
+ 41 Long Term Securities
41000 Bonds
+ 43 Short Term Securities
43111 stocks
43222 Certif Deposit

In your report you could the 40 node w/children and with that one selection you would get all the children under it down to the lowest account level

Balance
40 Securities 500.00
41 Long Term Securities 200.00
41000 Bonds 200.00
43 Short Term Securities 300.00
43111 stocks 100.00
43222 Certif Deposit 100.00


or you could specify the down 1 level with captions only ..then your report would only show the 2 Acct Group nodes by descriptions..not codes.


Balance
Securities 500.00
Long Term Securities 200.00
Short Term Securities 300.00


Once you build the hierarchies, each time you run the report, you run an update of the hierarchy code first and your report will pull any new accounts, etc. Say that Account 41200 Money Market Account was added to Account Group 41. When you run the update, it gets that data from the SET Tables and automatically pulls the value into your report. Its VERY COOL !!!

You will need to build the parent/child relationships into a file from the SETNODE, SETLEAF,SETHEADERT (plus text tables like SKAT,CEPCT,CSKU for descriptions depending on what hierarchy categories you define.

The upfront work to build the hierarchy file reduces ongoing maintenance to the Balance Sheet Report to near nothing.. With traditional FML coding, you will need to change the report on a regular basis to add/delete/change the accounts to the line.

You might want to check out the FML Painter Documentation, it explains how to create the Hierarchy files although it is not SAP specific.