Using The SQL MODEL Clause To Generate Financial Statements

Mark Rittman's picture
articles: 

In this article, Mark explains how the SQL MODEL clause can be used to generate rather complex financial statements.

One of our clients that I worked at recently had a rather tricky report that they wanted me to help with. At present, the report was put together using Excel, and looked something like this:

Financial statement

The report consisted of two sections, the top section being the actual, budget and variance figures for a department, and the bottom section being a set of percentages based on the top section. The first line of the report, net sales, was a memorandum item detailing the net sales for the department. The next two lines, gross profit and other income were then added together by a spreadsheet formula to give the next line, total net income. Next, wage costs, rent costs and other costs were totalled to give total costs, with the figure for total costs then being subtracted from total net income to give net contribution. The percentages at the bottom of the report were then calculated using these figures. Finally, variances were calculated, again using an Excel formula, for each of the line items.

As well as producing a report for each department, the client also wanted to produce another copy, this time for the company as a whole and derived from the figures in the department level reports. The figures in the top half of the report would need to be summed, whilst the percentages in the bottom half would need to be averaged.

The client had tried to produce the report using Discoverer and an account hierarchy, but the figures wouldn't add up properly. Fundamentally, you can't (easily) produce this sort of report in a tool like Discoverer because of all the inter-row calculations that are going on - the figures for row 4 are the sum of row 2 and 3, the figures for row 12 are the sum of rows 5 to 7 divided by row 1, and so on. Now they could write some PL/SQL code that would load the data into a PL/SQL collection and then process each figure individually, but a thought that came to mind when we were discussing it was to try out the new SQL MODEL clause.

If you're like me you probably took a look at the MODEL clause when it first came out with Oracle 10g. I worked through the Oracle by Example exercises, read the Open World paper by Tom Kyte, but then mentally filed it away as something that was interesting but probably not all that relevant. This report though sounded like something completely suited to the MODEL clause - it was a spreadsheet calculation, involved inter-row queries, and the ability for the MODEL clause to create custom dimension members meant that we could possibly output report rows for the company as a whole without them being present in the input data.

To try out the idea, we put together a table that would store a row for each line of the report, with the actuals and budget figures loaded into the table prior to processing. The table structure was as follows:

CREATE TABLE FINANCIAL_STATEMENT ( 
  REP_LINE_ID    NUMBER, 
  REP_LINE_ITEM  VARCHAR2 (30), 
  DEPARTMENT     VARCHAR2 (30), 
  ACTUAL         NUMBER (10,2), 
  BUDGET         NUMBER (10,2), 
  VARIANCE       NUMBER (10,2) );


and we then inserted the actuals and budget figures into the table, along with the line items for all the rows of the report:

INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 1, 'Net Sales', 'Retail', 5000, 5500, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 2, 'Gross Profit', 'Retail', 4000, 4750, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 3, 'Other Income', 'Retail', 900, 0, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 4, 'Total Net Income', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 5, 'Wage Costs', 'Retail', 3500, 3400, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 6, 'Rent Costs', 'Retail', 750, 750, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 7, 'Other Costs', 'Retail', 200, 0, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 8, 'Total Costs', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 9, 'Net Contribution', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 1, 'Net Sales', 'Direct', 3000, 2800, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 2, 'Gross Profit', 'Direct', 2250, 2000, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 3, 'Other Income', 'Direct', 0, 0, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 4, 'Total Net Income', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 5, 'Wage Costs', 'Direct', 1000, 1100, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 6, 'Rent Costs', 'Direct', 800, 700, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 7, 'Other Costs', 'Direct', 50, 35, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 8, 'Total Costs', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 9, 'Net Contribution', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 1, 'Net Sales', 'Internet', 2000, 4000, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 2, 'Gross Profit', 'Internet', 1500, 3000, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 3, 'Other Income', 'Internet', 300, 100, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 4, 'Total Net Income', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 5, 'Wage Costs', 'Internet', 1000, 1100, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 6, 'Rent Costs', 'Internet', 800, 500, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 7, 'Other Costs', 'Internet', 100, 0, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 8, 'Total Costs', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 9, 'Net Contribution', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 10, 'Gross Profit % of Net Sales', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 11, 'Wages % of Net Sales', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 12, 'Total Costs % of Net Sales', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 13, 'Net Contrib. % of Net Sales', 'Retail', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 10, 'Gross Profit % of Net Sales', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 11, 'Wages % of Net Sales', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 12, 'Total Costs % of Net Sales', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 13, 'Net Contrib. % of Net Sales', 'Direct', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 10, 'Gross Profit % of Net Sales', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 11, 'Wages % of Net Sales', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 12, 'Total Costs % of Net Sales', 'Internet', NULL, NULL, NULL); 
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE ) 
VALUES ( 13, 'Net Contrib. % of Net Sales', 'Internet', NULL, NULL, NULL); 

commit;

Now, if I selected from the FINANCIAL_STATEMENT table, the output would look like this:

SQL> select * from financial_statement order by department, rep_line_id;

REP_LINE_ID REP_LINE_ITEM                  DEPARTMENT     ACTUAL     BUDGET   VARIANCE
----------- ------------------------------ ---------- ---------- ---------- ----------
          1 Net Sales                      Direct           3000       2800
          2 Gross Profit                   Direct           2250       2000
          3 Other Income                   Direct              0          0
          4 Total Net Income               Direct
          5 Wage Costs                     Direct           1000       1100
          6 Rent Costs                     Direct            800        700
          7 Other Costs                    Direct             50         35
          8 Total Costs                    Direct
          9 Net Contribution               Direct
         10 Gross Profit % of Net Sales    Direct
         11 Wages % of Net Sales           Direct
         12 Total Costs % of Net Sales     Direct
         13 Net Contrib. % of Net Sales    Direct
          1 Net Sales                      Internet         2000       4000
          2 Gross Profit                   Internet         1500       3000
          3 Other Income                   Internet          300        100
          4 Total Net Income               Internet
          5 Wage Costs                     Internet         1000       1100
          6 Rent Costs                     Internet          800        500
          7 Other Costs                    Internet          100          0
          8 Total Costs                    Internet
          9 Net Contribution               Internet
         10 Gross Profit % of Net Sales    Internet
         11 Wages % of Net Sales           Internet
         12 Total Costs % of Net Sales     Internet
         13 Net Contrib. % of Net Sales    Internet
          1 Net Sales                      Retail           5000       5500
          2 Gross Profit                   Retail           4000       4750
          3 Other Income                   Retail            900          0
          4 Total Net Income               Retail
          5 Wage Costs                     Retail           3500       3400
          6 Rent Costs                     Retail            750        750
          7 Other Costs                    Retail            200          0
          8 Total Costs                    Retail
          9 Net Contribution               Retail
         10 Gross Profit % of Net Sales    Retail
         11 Wages % of Net Sales           Retail
         12 Total Costs % of Net Sales     Retail
         13 Net Contrib. % of Net Sales    Retail

39 rows selected.

So what we're looking for the MODEL clause to do is to fill in the actual and budget figures for each of the departments, create additional rows to hold totals and averages for the organisation as a whole, then fill in the variances.

The first step then is to write the base SELECT statement for the query:

create or replace view financial_statement_model as
select rep_line_id, rep_line_item, department, actual, budget, variance
from   financial_statement

Now, we add in the first part of the MODEL clause, that defines the dimensions and measures.

model
dimension by (rep_line_id, department)
measures     (rep_line_item, actual, budget, variance)

The dimensions are the line number for the line item (net sales, other income, wages % of sales) and the departments for which were are going to produce the reports. The measures are the items in the MODEL that we are going to either reference or calculate. You may well at this point be noticing the similarity between an SQL MODEL and an analytic workspace - both have dimensions, both have measures - as what Oracle is going to do when we kick the MODEL clause off is to create a temporary analytic workspace in the background, load our data in, then use the MODEL facility within the OLAP engine to perform our calculations. Quite cool actually and it doesn't require you to have licensed the OLAP Option to use it.

(UPDATE 1/7/05: It looks like, although you don't need to license the OLAP Option to use this feature, you need to have it installed. I can't find any reference to the OLAP Option in the context of the MODEL clause within the online docs, and no note that you need to have licensed it to use the MODEL clause, but a reader wrote in last night and let me know that the examples don't work without the OLAP Option installed. To confirm licensing, the best bet is to check with your local Oracle rep.)

(UPDATE 3/8/05: John Haydu, Product Manager within Oracle who handles the SQL Model clause, dropped me a line today to clarify a couple of points in this article. The first clarification is that the Model clause doesn't actually create a temporary analytic workspace, it actually creates it's own in-memory hash tables, and these are not dependent on analytic workspaces. If you have an analytic workspace presented as a table via a table function and view however, MODEL clause is optimized to use the analytic workspace data very efficiently, which is probably where the story has come from.

Secondly, since MODEL does not create analytic workspaces, there is no need for OLAP installation or licensing. The above paragraph (Plus Chris' subsequent comment) mention that it seems necessary to install OLAP. Users performing a DBCA-based installation using default settings will not encounter any problem. However, creating a custom database with DBCA and explicitly deselecting OLAP can raise the issue, and that is a bug. The problem is fixed in the next patch set for 10g Release 1 (10.1.0.5), likely available in the fourth quarter of 2005. The fix is also in 10g Release 2.

Finally, John also pointed out that since MODEL does not depend on the OLAP option, it is available in Standard Edition as well as Enterprise Edition. Thanks John.)

What happens now is that the MODEL clause in the background creates three variables (measures), each dimensioned by rep_line_item and department, which we can then manipulate via our MODEL. The next bit is where we start to add the rules for the model.

rules upsert
(
actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],

Note the RULES UPSERT bit - the RULES part tells the MODEL clause that this is where the rules come in, and the UPSERT bit allows the model to create additional cells in the model - referred to as custom dimension members - which we'll need so that we can store additional entries for the figures for the company as a whole.

The first rules of the model tell Oracle to calculate the figure for line item 4 (total net income) from the sum of lines 2 and 3. Note that we reference actual [4,department] - we need to specify both dimension values to get to our actuals figure, and the "department" bit tells the model to calculate figures for all members for this dimension - we could reference just one department dimension member rather than all of them by specifying actual [4,'Retail'] instead.

The cv(department) bit tells the model to use the "current value" of the dimension as specified on the left hand side of the equation; therefore, when the model is calculating the total net income for "Retail", it uses the gross profit and actual income for "Retail" as the calculation inputs.

actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],

The next two lines, rather than individually specifying other line item dimension members for addition, specify a range of dimension members instead.

actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],

These next two lines are the same as our first two lines, but this time subtract line 8 (total costs) from total net income (line 4).

Now, as we've worked out the totals for the "Retail", "Internet" and "Direct" department dimension members, we want the model to create new cells to hold totals and averages for the entire company - this is where the UPSERT bit comes in. To do this, we use a FOR loop, looping through the values of the rep_line_id dimension, and fill in the values of the rep_line_item measure for a new department dimension member, "All Departments".

rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = 
   rep_line_item[cv(rep_line_id),'Retail'],

Now, we go and fill in the actual and budget figures for line items 1 to 9, for our new "All Departments" department dimension member.

actual[rep_line_id between 1 and 9,'All Departments'] =
   sum(actual)[cv(rep_line_id),department in ('Direct','Retail','Internet')],
budget[rep_line_id between 1 and 9,'All Departments'] = 
   sum(budget)[cv(rep_line_id),department in ('Direct','Retail','Internet')],

Now we calculate the percentages, and trim the results to two decimal places.

actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),

actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),

actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),

actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),

And finally, we calculate the variance for every rep_line_id and department dimension member, including the extra "All Departments" member.

variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(department)]
)

The final SELECT statement, including the MODEL clause, looked like this:

select rep_line_id, rep_line_item, department, actual, budget, variance
from   financial_statement
model
dimension by (rep_line_id, department)
measures  	 (rep_line_item, actual, budget, variance)
rules upsert
(
actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],

actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],

actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],

rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = 
   rep_line_item[cv(rep_line_id),'Retail'],

actual[rep_line_id between 1 and 9,'All Departments'] = 
  sum(actual)[cv(rep_line_id),department in ('Direct','Retail','Internet')],
budget[rep_line_id between 1 and 9,'All Departments'] = 
   sum(budget)[cv(rep_line_id),department in ('Direct','Retail','Internet')],

actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),

actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),

actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),

actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),

variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(department)]
)

Now, when you run it through SQL*Plus, the output looked like this:

SQL> select rep_line_id, rep_line_item, department, actual, budget, variance
     from   financial_statement
     model
     dimension by (rep_line_id, department)
     measures  	 (rep_line_item, actual, budget, variance)
     rules upsert
     (
     actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
     budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],

     actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
     budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],

     actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
     budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],

     rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = 
        rep_line_item[cv(rep_line_id),'Retail'],

     actual[rep_line_id between 1 and 9,'All Departments'] =
       sum(actual)[cv(rep_line_id),department in ('Direct','Retail','Internet')],
     budget[rep_line_id between 1 and 9,'All Departments'] =
       sum(budget)[cv(rep_line_id),department in ('Direct','Retail','Internet')],

     actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
     budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),

     actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
     budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),

     actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
     budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),

     actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
     budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),

     variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(department)]
     )

REP_LINE_ID REP_LINE_ITEM                  DEPARTMENT          ACTUAL     BUDGET   VARIANCE
----------- ------------------------------ --------------- ---------- ---------- ----------
          1 Net Sales                      Retail                5000       5500       -500
          2 Gross Profit                   Retail                4000       4750       -750
          3 Other Income                   Retail                 900          0        900
          4 Total Net Income               Retail                4900       4750        150
          5 Wage Costs                     Retail                3500       3400        100
          6 Rent Costs                     Retail                 750        750          0
          7 Other Costs                    Retail                 200          0        200
          8 Total Costs                    Retail                4450       4150        300
          9 Net Contribution               Retail                 450        600       -150
          1 Net Sales                      Direct                3000       2800        200
          2 Gross Profit                   Direct                2250       2000        250
          3 Other Income                   Direct                   0          0          0
          4 Total Net Income               Direct                2250       2000        250
          5 Wage Costs                     Direct                1000       1100       -100
          6 Rent Costs                     Direct                 800        700        100
          7 Other Costs                    Direct                  50         35         15
          8 Total Costs                    Direct                1850       1835         15
          9 Net Contribution               Direct                 400        165        235
          1 Net Sales                      Internet              2000       4000      -2000
          2 Gross Profit                   Internet              1500       3000      -1500
          3 Other Income                   Internet               300        100        200
          4 Total Net Income               Internet              1800       3100      -1300
          5 Wage Costs                     Internet              1000       1100       -100
          6 Rent Costs                     Internet               800        500        300
          7 Other Costs                    Internet               100          0        100
          8 Total Costs                    Internet              1900       1600        300
          9 Net Contribution               Internet              -100       1500      -1600
         10 Gross Profit % of Net Sales    Retail                  .8        .86       -.06
         11 Wages % of Net Sales           Retail                  .7        .61        .09
         12 Total Costs % of Net Sales     Retail                  .7        .75       -.05
         13 Net Contrib. % of Net Sales    Retail                 .09         .1       -.01
         10 Gross Profit % of Net Sales    Direct                 .75        .71        .04
         11 Wages % of Net Sales           Direct                 .33        .39       -.06
         12 Total Costs % of Net Sales     Direct                 .33        .65       -.32
         13 Net Contrib. % of Net Sales    Direct                 .13        .05        .08
         10 Gross Profit % of Net Sales    Internet               .75        .75          0
         11 Wages % of Net Sales           Internet                .5        .27        .23
         12 Total Costs % of Net Sales     Internet                .5         .4         .1
         13 Net Contrib. % of Net Sales    Internet              -.05        .37       -.42
         13 Net Contrib. % of Net Sales    All Departments        .07        .18       -.11
         12 Total Costs % of Net Sales     All Departments        .55        .61       -.06
         11 Wages % of Net Sales           All Departments        .55        .45         .1
         10 Gross Profit % of Net Sales    All Departments        .77        .79       -.02
          7 Other Costs                    All Departments        350         35        315
          6 Rent Costs                     All Departments       2350       1950        400
          5 Wage Costs                     All Departments       5500       5600       -100
          3 Other Income                   All Departments       1200        100       1100
          2 Gross Profit                   All Departments       7750       9750      -2000
          1 Net Sales                      All Departments      10000      12300      -2300
          8 Total Costs                    All Departments       8200       7585        615
          4 Total Net Income               All Departments       8950       9850       -900
          9 Net Contribution               All Departments        750       2265      -1515

52 rows selected.

Not bad. The obvious next question is - how can we use this? The model clause doesn't in itself update the table that supplied the data for the model, it just outputs that results just like any other SELECT statement. And, if we want the figures for just one department, say "Retail", we get the aggregated figures for "All Departments" as well, except they're wrong as they only include data for the "Retail" department.

SQL> ed
Wrote file afiedt.buf

  1  select rep_line_id, rep_line_item, department, actual, budget, variance
  2  from   financial_statement
  3  where  department = 'Retail'
  4  model
  5  dimension by (rep_line_id, department)
  6  measures    (rep_line_item, actual, budget, variance)
  7  rules upsert
  8  (
  9  actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
 10  budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],
 11  actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
 12  budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],
 13  actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
 14  budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],
 15  rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = rep_line_item[cv(r
 16  actual[rep_line_id between 1 and 9,'All Departments'] = sum(actual)[cv(rep_line_id),department 
 17  budget[rep_line_id between 1 and 9,'All Departments'] = sum(budget)[cv(rep_line_id),department 
 18  actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
 19  budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),
 20  actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
 21  budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),
 22  actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
 23  budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),
 24  actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
 25  budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),
 26  variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(departm
 27* )
SQL> /

REP_LINE_ID REP_LINE_ITEM                  DEPARTMENT          ACTUAL     BUDGET   VARIANCE
----------- ------------------------------ --------------- ---------- ---------- ----------
          1 Net Sales                      Retail                5000       5500       -500
          2 Gross Profit                   Retail                4000       4750       -750
          3 Other Income                   Retail                 900          0        900
          4 Total Net Income               Retail                4900       4750        150
          5 Wage Costs                     Retail                3500       3400        100
          6 Rent Costs                     Retail                 750        750          0
          7 Other Costs                    Retail                 200          0        200
          8 Total Costs                    Retail                4450       4150        300
          9 Net Contribution               Retail                 450        600       -150
         10 Gross Profit % of Net Sales    Retail                  .8        .86       -.06
         11 Wages % of Net Sales           Retail                  .7        .61        .09
         12 Total Costs % of Net Sales     Retail                  .7        .75       -.05
         13 Net Contrib. % of Net Sales    Retail                 .09         .1       -.01
         13 Net Contrib. % of Net Sales    All Departments        .09         .1       -.01
         12 Total Costs % of Net Sales     All Departments         .7        .75       -.05
         11 Wages % of Net Sales           All Departments         .7        .61        .09
         10 Gross Profit % of Net Sales    All Departments         .8        .86       -.06
          7 Other Costs                    All Departments        200          0        200
          6 Rent Costs                     All Departments        750        750          0
          5 Wage Costs                     All Departments       3500       3400        100
          3 Other Income                   All Departments        900          0        900
          2 Gross Profit                   All Departments       4000       4750       -750
          1 Net Sales                      All Departments       5000       5500       -500
          8 Total Costs                    All Departments       4450       4150        300
          4 Total Net Income               All Departments       4900       4750        150
          9 Net Contribution               All Departments        450        600       -150

26 rows selected.

The first approach I thought of was to enclosed the SELECT statement with the MODEL clause within an inline view, then select from that just the department that I wanted.

SQL> ed
Wrote file afiedt.buf

  1  select * from
  2  (
  3  select rep_line_id, rep_line_item, department, actual, budget, variance
  4  from   financial_statement
  5  model
  6  dimension by (rep_line_id, department)
  7  measures    (rep_line_item, actual, budget, variance)
  8  rules upsert
  9  (
 10  actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
 11  budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],
 12  actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
 13  budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],
 14  actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
 15  budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],
 16  rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = rep_line_item[cv(r
 17  actual[rep_line_id between 1 and 9,'All Departments'] = sum(actual)[cv(rep_line_id),department 
 18  budget[rep_line_id between 1 and 9,'All Departments'] = sum(budget)[cv(rep_line_id),department 
 19  actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
 20  budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),
 21  actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
 22  budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),
 23  actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
 24  budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),
 25  actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
 26  budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),
 27  variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(departm
 28  )
 29  )
 30* where department = 'Retail'
SQL> /
from   financial_statement
       *
ERROR at line 4:
ORA-00600: internal error code, arguments: [kkqsp_add_level.1], [], [], [], [], [], [], []

Now that doesn't look good.

(UPDATE 3/8/05: Another clarification from John Haydu. "This bug has also been fixed in 10.1.0.5 and 10g Rel. 2.")

How about creating a view based on the SELECT statement, then selecting from that?

create or replace view financial_statement_view as
select rep_line_id, rep_line_item, department, actual, budget, variance
     from   financial_statement
     model
     dimension by (rep_line_id, department)
     measures  	 (rep_line_item, actual, budget, variance)
     rules upsert
     (
     actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
     budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],

     actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
     budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],

     actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
     budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],

     rep_line_item [for rep_line_id from 1 to 13 increment 1,'All Departments'] = 
        rep_line_item[cv(rep_line_id),'Retail'],

     actual[rep_line_id between 1 and 9,'All Departments'] =
       sum(actual)[cv(rep_line_id),department in ('Direct','Retail','Internet')],
     budget[rep_line_id between 1 and 9,'All Departments'] =
        sum(budget)[cv(rep_line_id),department in ('Direct','Retail','Internet')],

     actual [10,department] = trunc(actual[2,cv(department)] / actual[1,cv(department)],2),
     budget [10,department] = trunc(budget[2,cv(department)] / budget[1,cv(department)],2),

     actual [11,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
     budget [11,department] = trunc(budget[5,cv(department)] / budget[1,cv(department)],2),

     actual [12,department] = trunc(actual[5,cv(department)] / actual[1,cv(department)],2),
     budget [12,department] = trunc(budget[8,cv(department)] / budget[1,cv(department)],2),

     actual [13,department] = trunc(actual[9,cv(department)] / actual[1,cv(department)],2),
     budget [13,department] = trunc(budget[9,cv(department)] / budget[1,cv(department)],2),

     variance [ANY,ANY] = actual[cv(rep_line_id),cv(department)] - budget[cv(rep_line_id),cv(department)]
     )

Now that looks better:

SQL> select * from financial_statement_view o
  2  order by department, rep_line_id;

REP_LINE_ID REP_LINE_ITEM                  DEPARTMENT          ACTUAL     BUDGET   VARIANCE
----------- ------------------------------ --------------- ---------- ---------- ----------
          1 Net Sales                      All Departments      10000      12300      -2300
          2 Gross Profit                   All Departments       7750       9750      -2000
          3 Other Income                   All Departments       1200        100       1100
          4 Total Net Income               All Departments       8950       9850       -900
          5 Wage Costs                     All Departments       5500       5600       -100
          6 Rent Costs                     All Departments       2350       1950        400
          7 Other Costs                    All Departments        350         35        315
          8 Total Costs                    All Departments       8200       7585        615
          9 Net Contribution               All Departments        750       2265      -1515
         10 Gross Profit % of Net Sales    All Departments        .77        .79       -.02
         11 Wages % of Net Sales           All Departments        .55        .45         .1
         12 Total Costs % of Net Sales     All Departments        .55        .61       -.06
         13 Net Contrib. % of Net Sales    All Departments        .07        .18       -.11
          1 Net Sales                      Direct                3000       2800        200
          2 Gross Profit                   Direct                2250       2000        250
          3 Other Income                   Direct                   0          0          0
          4 Total Net Income               Direct                2250       2000        250
          5 Wage Costs                     Direct                1000       1100       -100
          6 Rent Costs                     Direct                 800        700        100
          7 Other Costs                    Direct                  50         35         15
          8 Total Costs                    Direct                1850       1835         15
          9 Net Contribution               Direct                 400        165        235
         10 Gross Profit % of Net Sales    Direct                 .75        .71        .04
         11 Wages % of Net Sales           Direct                 .33        .39       -.06
         12 Total Costs % of Net Sales     Direct                 .33        .65       -.32
         13 Net Contrib. % of Net Sales    Direct                 .13        .05        .08
          1 Net Sales                      Internet              2000       4000      -2000
          2 Gross Profit                   Internet              1500       3000      -1500
          3 Other Income                   Internet               300        100        200
          4 Total Net Income               Internet              1800       3100      -1300
          5 Wage Costs                     Internet              1000       1100       -100
          6 Rent Costs                     Internet               800        500        300
          7 Other Costs                    Internet               100          0        100
          8 Total Costs                    Internet              1900       1600        300
          9 Net Contribution               Internet              -100       1500      -1600
         10 Gross Profit % of Net Sales    Internet               .75        .75          0
         11 Wages % of Net Sales           Internet                .5        .27        .23
         12 Total Costs % of Net Sales     Internet                .5         .4         .1
         13 Net Contrib. % of Net Sales    Internet              -.05        .37       -.42
          1 Net Sales                      Retail                5000       5500       -500
          2 Gross Profit                   Retail                4000       4750       -750
          3 Other Income                   Retail                 900          0        900
          4 Total Net Income               Retail                4900       4750        150
          5 Wage Costs                     Retail                3500       3400        100
          6 Rent Costs                     Retail                 750        750          0
          7 Other Costs                    Retail                 200          0        200
          8 Total Costs                    Retail                4450       4150        300
          9 Net Contribution               Retail                 450        600       -150
         10 Gross Profit % of Net Sales    Retail                  .8        .86       -.06
         11 Wages % of Net Sales           Retail                  .7        .61        .09
         12 Total Costs % of Net Sales     Retail                  .7        .75       -.05
         13 Net Contrib. % of Net Sales    Retail                 .09         .1       -.01

52 rows selected.

SQL> 

and just to make sure:

SQL> select * from financial_statement_view where department = 'All Departments'
  2  order by department, rep_line_id;

REP_LINE_ID REP_LINE_ITEM                  DEPARTMENT          ACTUAL     BUDGET   VARIANCE
----------- ------------------------------ --------------- ---------- ---------- ----------
          1 Net Sales                      All Departments      10000      12300      -2300
          2 Gross Profit                   All Departments       7750       9750      -2000
          3 Other Income                   All Departments       1200        100       1100
          4 Total Net Income               All Departments       8950       9850       -900
          5 Wage Costs                     All Departments       5500       5600       -100
          6 Rent Costs                     All Departments       2350       1950        400
          7 Other Costs                    All Departments        350         35        315
          8 Total Costs                    All Departments       8200       7585        615
          9 Net Contribution               All Departments        750       2265      -1515
         10 Gross Profit % of Net Sales    All Departments        .77        .79       -.02
         11 Wages % of Net Sales           All Departments        .55        .45         .1
         12 Total Costs % of Net Sales     All Departments        .55        .61       -.06
         13 Net Contrib. % of Net Sales    All Departments        .07        .18       -.11

13 rows selected.

WoOoOot! So there you go, a useful application of the SQL MODEL clause. What's more, you can now import this view into Discoverer, build a worksheet based on the view, and have department as a page item, allowing you to see the financial statement for any department (including "All Departments" within the organisation.

For more details on the SQL MODEL clause, take a look at this previous blog posting and the online documentation.

Comments

Excellent piece of information. I knew about the SQL Model Clause and understood from the docs, but didn't know in which cases this would be used. Thanks so much.