A script-enabled browser is required for this page to function properly.

Product Order property

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. 

Restrictions

Examples

Example 1:  Single 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.

Example 2:  Multiple group

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%

Example 3:  Formula

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

See also

About matrix reports