Using The SQL MODEL Clause To Generate Financial Statements
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:
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.
- Mark Rittman's blog
- Log in to post comments
Comments
Excellent piece of
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.