The Product Order property specifies the order in which groups are evaluated in the cross product for a summary. Product Order also defines the frequency of a summary, formula, or placeholder in a matrix (cross-product) group. That is, the summary, formula, or placeholder has one value for each combination of values of the groups in its Product Order. Product Order is used only for columns owned by cross-product groups. Because a cross product relates multiple groups, the groups in the cross product could be evaluated in any one of many different orders. Therefore, when creating a summary for a cross product, you must use Product Order to specify for Reports Builder which group should be evaluated first, which second, and so on. You must also use Product Order to specify the frequency of a summary, formula, or placeholder within the cross product.
Applies to |
Summary, formula, and placeholder columns |
Values |
A valid group combination. |
Required/Optional |
Required, if the column is a summary or formula column belonging to a matrix (cross-product) group. |
Suppose that you the following matrix report.
Job ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
-------
-----
-------
---------
--------
Year Dept
---- ----
80 10
20
800
800
30
------
800
81 10
2450
5000
7450
20
3000
2975
5975
30
950
2850
5600
9400
------
22825
82 10
1300
1300
20
3000
3000
30
------
4300
83 10
20
1100
1100
30
------
1100
-------
-----
-------
---------
--------
6000
4150
8275
5000
5600
#
Analyst
Pct_Job
Clerk Pct_Job
Manager
Pct_Job
Total
Dept --------
------- ------
------- --------
------- ------
10 1300
31.33%
2450
29.61%
3750
20 6000
100.00% 1900
45.78%
2975
35.95%
10875
30 950
22.89%
2850
34.44%
3800
Total 6000
4150
8275
18425
100%
100%
100%
#
Analyst
Pct_Job Clerk
Pct_Job Manager
Pct_Job Total
Dept --------
------- ------
------- --------
------- ------
10 1300
34.67%
2450
65.33%
3750
100%
20 6000
55.17%
1900
17.47%
2975
27.36%
10875
100%
30 950
25.00%
2850
75.00%
3800
100%
Total 6000
4150
8275
18425
Group G_JOB contains the column JOB, G_DEPT contains DEPTNO, G_YEAR contains YEAR, G_SAL contains SUMSAL, and G_CROSS is the cross product group. (G_CROSS contains all of the summaries for the matrix.)
To get the summary of salaries by job, you create a summary column in G_CROSS with the following settings:
Function |
Reset At |
Product Order |
% of Total |
G_JOB |
G_JOB |
In this case, you want the summary to calculate a sum for each record of G_JOB (that is, each job category) so Product Order is G_JOB. (This summary appears along the bottom of the matrix.)
To get the summary of salaries by year, you create a summary column in G_CROSS with the following settings:
Function |
Reset At |
Product Order |
Sum |
G_YEAR |
G_YEAR |
In this case, you want the summary to calculate a sum for each record of G_YEAR (that is, each year) so Product Order is G_YEAR. (This summary appears along the right edge of the matrix directly underneath a boilerplate line.)
To get the summary of salaries for each department in each year, you create a summary column in G_CROSS with the following settings:
Function |
Reset At |
Product Order |
Sum |
G_DEPT |
G_YEAR, G_DEPT |
In this case, you want the summary to calculate a sum for each department in each year (G_DEPT and G_YEAR). For this summary, G_YEAR has the lowest frequency (that is, it changes only once for each three records of G_DEPT) and G_DEPT has the highest frequency (that is, it changes three times for each record of G_YEAR). As a result, G_YEAR (lowest frequency) appears first and G_DEPT (highest frequency) appears second in Product Order.
Suppose that you have a matrix report that looks something like the one below and you want to add some percentage summaries to it.
Dept Analyst Clerk Manager Total
10 1300
2450
3750
20 6000
1900 2975
10875
30 950
2850
3800
Total 6000
4150
8275
18425
Group G_JOB contains the column JOB, G_DEPT contains DEPTNO, G_SAL contains SUMSAL, and G_CROSS is the cross product group.
To get a summary column that calculates each job category's total salaries per department as a percentage of total salaries for that job in all departments, you would enter the following settings for a summary column in G_CROSS:
Function |
Reset At |
Compute At |
Product Order |
% of Total |
G_DEPT |
G_JOB |
G_JOB, G_DEPT |
In this case, you want the summary to go down the matrix and then across. Since the summary goes down the matrix first, G_JOB has the lowest frequency (it changes only once per three departments) and is the first group in Product Order. Since the summary goes across the matrix after going down, G_DEPT has the highest frequency (it changes three times per one job) and is the second group in the Product Order.
Analyst
Pct_Job
Clerk Pct_Job
Manager
Pct_Job
Total
Dept --------
------- ------
------- --------
------- ------
10 1300
31.33%
2450
29.61%
3750
20 6000
100.00% 1900
45.78%
2975
35.95%
10875
30 950
22.89%
2850
34.44%
3800
Total 6000
4150
8275
18425
100%
100%
100%
To get a summary that calculates each department's total salaries per job category as a percentage of total salaries for that department, you would enter the following settings for a summary column in G_CROSS:
Function |
Reset At |
Compute At |
Product Order |
% of Total |
G_JOB |
G_DEPT |
G_DEPT, G_JOB |
In this case, you want the summary to go across the matrix and then down. Since the summary goes across the matrix first, G_DEPT has the lowest frequency (it changes only once per three jobs) and is the first group in Product Order. Since the summary goes down the matrix only after going across, G_JOB has the highest frequency (it changes three times per one department) and is the second group in the Product Order.
Analyst
Pct_Job Clerk
Pct_Job Manager
Pct_Job Total
Dept --------
------- ------
------- --------
------- ------
10 1300
34.67%
2450
65.33%
3750
100%
20 6000
55.17%
1900
17.47%
2975
27.36%
10875
100%
30 950
25.00%
2850
75.00%
3800
100%
Total 6000
4150
8275
18425
To get a summary that calculates each department's total salaries per job category as a percentage of total salaries in the report, you would enter the following settings for a summary column in G_CROSS:
Function |
Reset At |
Compute At |
Product Order |
% of Total |
G_JOB |
Report |
G_DEPT, G_JOB |
In this case, the summary could go across the matrix and then down, or down the matrix and then across because you are computing this summary for the entire report (for example, Compute At is Report). Product Order could be either G_DEPT, G_JOB (across, down) or G_JOB, G_DEPT (down, across). Note, however, that Reset At must be the group that has the highest frequency: G_DEPT if Product Order is G_JOB, G_DEPT; G_JOB if Product Order is G_DEPT, G_JOB.
Analyst
Pct_Job Clerk
Pct_Job Manager
Pct_Job Total
Dept --------
------- ------
------- --------
------- ------
10 1300
7.06%
2450
13.30%
3750
20 6000
32.56%
1900
10.31%
2975
16.15%
10875
30 950
5.16%
2850
15.47%
3800
Total 6000
4150
8275
18425
100%
Suppose that you have a matrix report that looks something like the one below and you want to add a formula to it.
Dept Analyst Clerk
Manager
President
Salesman
10 1300
2450
5000
20 6000
1900
2975
30 950
2850
5600
Group G_JOB contains the column JOB, G_DEPT contains DEPTNO, G_SAL contains SUMSAL, and G_CROSS is the cross product group.
To add a formula that calculates the sum of after-tax salaries for each department, you first create a summary column (DEPTSUM) in G_CROSS with the following settings:
Function |
Reset At |
Product Order |
% of Total |
G_DEPT |
G_DEPT |
Next, you create a formula (AFTERTAX) in G_CROSS with the following settings:
Formula |
Product Order |
:deptsum * .9 |
G_DEPT |
Because the formula is in G_CROSS, you must give it a Product Order. In this case, the frequency of the formula is the same as the summary column, DEPTSUM. This would calculate the formula as follows:
Dept Analyst Clerk
Manager
President
Salesman
Comp
10 1300
2450
5000
7875
20 6000
1900
2975
9788
30
950
2850
5600
8460
Copyright © 1984, 2005, Oracle. All rights reserved.