Oracle Reporting 7 - Model Examples

Looping in Model:
select product, country, year, week, inventory, sale, receipts
 from sales_fact
 where country in ('Australia') and product ='Xtend Memory'
 model return updated rows
 partition by (product, country)
 dimension by (year, week)
 measures ( 0 inventory , sale, receipts)
 rules automatic order(
 inventory [2002, week ] =  nvl(inventory [cv(year), cv(week)-1 ] ,0)  - sale[cv(year), cv(week) ] +  + receipts [cv(year), cv(week) ],
 sale [2002, for week from 1 to 53 increment 1] = 0, 
 receipts [ 2002,for week from 1 to 53 increment 1] =0
 )
 order by product, country,year, week;
 
Aggregations in Model: 
 select product, country, year, week, inventory, avg_inventory, max_sale
 from sales_fact
 where country in ('Australia') and product ='Xtend Memory'
 model return updated rows
 partition by (product,country)
 dimension by (year, week)
 measures ( 0 inventory ,0 avg_inventory , 0 max_sale, sale, receipts)
 rules automatic order(
 inventory [year, week ] =  nvl(inventory [cv(year), cv(week)-1 ] ,0)  - sale[cv(year), cv(week) ] +  + receipts [cv(year), cv(week) ],
 avg_inventory [ year,ANY ] = avg (inventory) [ cv(year), week ],
 max_Sale [ year, ANY ] = max( sale) [ cv(year), week ]
 )
 order by product, country,year, week;
Please note the difference between week and cv(week) in the cell reference. In avg(inventory)[cv(year), week], "week" refers to all weeks of cv(year).
 
Using Iteration to pivot:
select year, week,sale, sale_list
 from sales_fact
 where country in ('Australia') and product ='Xtend Memory'
 model return updated rows
 partition by (product, country)
 dimension by (year, week)
 measures ( cast(' ' as varchar2(50) ) sale_list, sale)
 rules iterate (5) (
 sale_list [ year, week ] order by year, week =
 sale [cv(year), CV(week)-ITERATION_NUMBER +2 ] ||
 case when iteration_number=0 then '' else ', ' end ||
 sale_list [cv(year) ,cv(week)]
 )
order by year, week;
Iteration_number starts from 0 to 4. Rule for sale_list is an ordered rule. It accesses cells in the order prescribed by ORDER BY and applies the right side computation.

Presentv and nulls:
select year, week,sale, sale_list
 from sales_fact
 where country in ('Australia') and product ='Xtend Memory'
 model return updated rows
 partition by (product, country)
 dimension by (year, week)
 measures ( cast(' ' as varchar2(120) ) sale_list, sale, 0 tmp)
 rules iterate (5) (
 sale_list [ year, week ] order by year, week =
 presentv( sale [cv(year), CV(week)-iteration_number + 2 ],  
 sale [cv(year), CV(week)-iteration_number + 2 ] || case when iteration_number=0 then '' else ', ' end || sale_list [cv(year) ,cv(week)],
 sale_list [cv(year) ,cv(week)])
 )
 order by year, week;
This query removes redendant commas.

Reference models - lookup table:
select year, week,sale, prod_list_price
 from sales_fact
 where country in ('Australia') and product ='Xtend Memory'
 model return updated rows
 REFERENCE ref_prod on (select prod_name, max(prod_list_price) prod_list_price from products group by prod_name)
 dimension by (prod_name)
 measures (prod_list_price)
 MAIN main_section
 partition by (product,country)
 dimension by (year, week)
 measures( sale, receipts, 0 prod_list_price )
 rules (
  prod_list_price[year,week] order by year, week = ref_prod.prod_list_price [ cv(product) ]
 )
 order by year, week;
Oracle supports multiple reference models.

This example is based on the post: http://www.orafaq.com/node/69
  DEPARTMENT     VARCHAR2 (30), commit;  

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) );
  
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 ( 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); 
commit;

select rep_line_item,department, budget, actual actual, variance
from financial_statement
where department='Retail'
model
return all rows
partition by (department)
dimension by(rep_line_item)
measures (actual actual, budget budget, variance variance)
rules upsert(
  budget['Total Net Income'] = budget['Gross Profit'] + budget['Other Income'],
  actual['Total Net Income'] = actual['Gross Profit'] + actual['Other Income'],
  
  budget['Total Costs'] = budget['Wage Costs'] + budget['Rent Costs'] + budget['Other Costs'],
  actual['Total Costs'] = actual['Wage Costs'] + actual['Rent Costs'] + actual['Other Costs'],
  
  budget['Net Contribution'] = budget['Total Net Income'] - budget['Total Costs'],
  actual['Net Contribution'] = actual['Total Net Income'] - actual['Total Costs'],
  
  budget['Gross Profit % of Net Sales'] = Round(budget['Gross Profit']/budget['Net Sales'], 2),
  actual['Gross Profit % of Net Sales'] = Round(actual['Gross Profit']/actual['Net Sales'],2),
  
  budget['Wages % of Net Sales'] = Round(budget['Wage Costs']/budget['Net Sales'], 2),
  actual['Wages % of Net Sales'] = Round(actual['Wage Costs']/actual['Net Sales'],2),
  
  budget['Total Costs % of Net Sales'] = Round(budget['Total Costs']/budget['Net Sales'], 2),
  actual['Total Costs % of Net Sales'] = Round(actual['Total Costs']/actual['Net Sales'],2),
  
  budget['Net Contrib. % of Net Sales'] = Round(budget['Net Contribution']/budget['Net Sales'], 2),
  actual['Net Contrib. % of Net Sales'] = Round(actual['Net Contribution']/actual['Net Sales'],2),
  
  variance[ANY] = presentnnv(actual[cv(rep_line_item)],actual[cv(rep_line_item)],0) - presentnnv(budget[cv(rep_line_item)],budget[cv(rep_line_item)],0)
);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061262/，如需轉載，請註明出處，否則將追究法律責任。