Properties and calculation syntax

1. Where to enter settings

1. Open the workbook in Edit mode and select the WSU Glossary Pivot.
2. Use Grammar to place fields in Rows, Columns, and Values. Put any helper measures in Values too.
3. Open Properties → General. Scroll below the standard Oracle properties to find the extension controls, starting with Format:.
4. Enter an example in the property named above it. Leave the field or press Tab to apply it. Compare the result before saving.

The standard Oracle pivot has its own Display as: Percent Of control. Glossary Pivot does not inherit that setting. Its Values: Show as % of Total property is a separate calculation.

2. Syntax at a glance

• Use a semicolon between entries. A trailing semicolon is allowed.
• Use the measure or field’s original name or stable ID. Matching ignores case. Changing a displayed label does not rename the measure for calculations.
• Spaces in a name are allowed. In extension properties, enter Unique Headcount without surrounding quotes. Oracle calculation formulas use their own quoted field references.
• Referenced measures must be in Values. Hide helper measures with Header: Hidden Columns; removing them from Values removes them from the query.
• These text boxes are small configuration languages. Do not enter Excel formulas, JavaScript, or arbitrary SQL in them. Names containing the syntax delimiters need special care; no general quoting/escaping syntax is provided.

3. Properties page reference

The labels below follow the current General panel. Defaults describe a new visualization; a saved workbook can retain different settings.

Oracle also supplies Title, Tooltip, Visibility, theme/style, Size and Position, and Filters controls. Those are host properties. Available controls and whether size fields are editable depend on the canvas layout and OAC version.

4. Number formats

Enter in Format: Per-Measure Override (id:format:decimals; …)

Headcount:number:0;
Rate:percent:1;
Average GPA:number:2;
Amount:currency:2;
Volume:compact:1

Use auto, number, percent, currency, or compact. Decimal places can be auto or 0 through 4. A per-measure setting takes precedence over the table’s Number Format and Decimal Places settings.

Auto preserves OAC’s formatted cell text. Explicit formats use the numeric value. A format changes presentation; it does not choose a denominator or fix an incorrect total.

5. Total calculations

Enter in Totals: Measure Rules (name = sum | avg | distinct | level(A, B) | weighted(W) | ratio(N, D) | none; …)

Amount = sum;
Average Score = avg;
Lowest Score = min;
Highest Score = max;
Rate = ratio(Selected Count, Headcount);
Average GPA = weighted(Graded Credits);
Do Not Total = none

Source totals, including inside ratio

Unique Headcount = level(College Distinct HC, Term Distinct HC);
Share = ratio(level(College Distinct HC, Term Distinct HC), level(Selected Distinct HC))

On a college subtotal, the first line shows the college’s own distinct count. On the grand total it shows the term’s distinct count. The second line divides that same number by the distinct count for the whole selection. Section 7 gives the full workbook setup.

A rate from numerator and denominator

Suppose two disjoint groups contain 10 of 20 and 90 of 100 selected students. Their percentages are 50% and 90%. The combined rate is 100 ÷ 120 = 83.3%. Adding the percentages gives 140%; their simple average gives 70%. Neither is the combined rate.

Total rule, for a measure named Rate

Rate = ratio(Selected Count, Headcount)

Format for a fraction such as 0.8333

Rate:percent:1

If detail Rate values are already on a 0–100 scale, keep the total on that scale:

Rate = ratio(Selected Count, Headcount, 100)

Rate:number:1

The last format displays 83.3 without adding a percent sign. Prefer a 0–1 source rate with Percent format when you want 83.3%.

A weighted average

With GPAs 3.0 and 4.0 and corresponding graded credits 10 and 30, the weighted average is (3.0 × 10 + 4.0 × 30) ÷ 40 = 3.75. The unweighted average would be 3.50.

Average GPA = weighted(Graded Credits)

Graded Credits

Enter the second line in Header: Hidden Columns if the weight should stay out of view. Keep Graded Credits in Values. Its population and grain must match the GPA measure.

Blank cells are skipped. A zero divisor or missing referenced measure produces a blank result. Unlisted measures normally sum. With configured rules, measures whose identity cannot be resolved safely can also have blank totals.

6. Percentage of total

Use this when you want each cell’s share of an additive sum. Create a calculated copy of a count first if the table should show both the count and its percentage.

Enter in Values: Show as % of Total (name[: by field]; …)

Headcount Share

This divides each Headcount Share cell by the sum of that measure’s cells in the same data column. Separate Term columns get separate denominators. This differs from the standard pivot’s Percent Of All Values when several terms are selected.

Share within a row group

Headcount Share: by College

For example, with College then Ethnicity in Rows, each ethnicity becomes a share of its college. College must be a Rows field. The group also includes any Rows fields above College. A Columns-only field is not a valid by grouping for this property.

Measures shown as shares use percentage formatting automatically unless a per-measure override is supplied. An explicit total rule takes precedence over the automatic share total.

7. Overlapping distinct counts

A student with two plans in one college counts once in that college. A student with plans in two colleges counts once in each college and once in the overall population. College counts and percentages can therefore add to more than the overall total and 100%.

Step 1. Add the source totals

These formulas go in Create Calculation → Expression, not in the properties text boxes. The source Unique Headcount measure must already count distinct students. The paths below are the ones the reviewed Census workbook uses for Unique Headcount and Term. For College, use the same field that is in Rows. Drag it from the data panel into the editor instead of typing it; typed names often fail validation.

Full form of two of these, as saved in the Census workbook

AGGREGATE(
  "Census"."New Student Enrolled"."Unique Headcount"
  BY "Census"."New Student Enrolled"."Term"
)

"Census"."New Student Enrolled"."Unique Headcount"
/
AGGREGATE(
  "Census"."New Student Enrolled"."Unique Headcount"
  BY
)

The empty BY list is intentional. It asks the source for the total across the filtered selection, without College or Term grouping. This matches the standard pivot’s Percent Of All Values denominator. Validate it against your own source before reusing it.

Step 2. Bind the fields

To add a measure in the Grammar panel, drop it on the Values heading. Dropping it on an existing measure replaces that measure.

Step 3. Apply the total rules

Enter in Totals: Measure Rules

Unique Headcount = level(College Distinct HC, Term Distinct HC);
College Share of Distinct Selected Headcount = ratio(level(College Distinct HC, Term Distinct HC), level(Selected Distinct HC))

Result for 2013 Fall: the Agri Human & Nat Res Sciences subtotal shows 307 and 7.4%, and the grand total shows 4,163 and 100.0%. With 2013 Fall and 2014 Fall selected, the grand total shows 4,163 / 48.3% and 4,457 / 51.7%, the same as the standard pivot.

College subtotal: count = C,  percentage = C / G
Grand total:      count = D,  percentage = D / G

Step 4. Set display properties

Format: Per-Measure Override

Unique Headcount:number:0;
College Share of Distinct Selected Headcount:percent:1

Header: Display Label

Plan Owner Academic Group Desc:College;
College Share of Distinct Selected Headcount:%

Header: Hidden Columns

College Distinct HC; Term Distinct HC; Selected Distinct HC

Set Grand Total Row: On, Row Subtotals: On, Grand Total Column: Off, and Group Collapse: Off. Leave Values: Show as % of Total blank for these source-calculated measures.

The saved comparison workbook also keeps two older measures hidden, Unique Headcount as Percentage Format and College Share of Distinct Term Headcount. The rules above do not use them.

College rows only, without subtotals

An older setup works when Rows has only College. It needs a helper, College Share of Distinct Term Headcount = Unique Headcount / AGGREGATE(Unique Headcount BY Term), in Values and hidden, and these rules:

Unique Headcount = ratio(Unique Headcount, College Share of Distinct Term Headcount);
College Share of Distinct Selected Headcount = ratio(College Share of Distinct Selected Headcount, College Share of Distinct Term Headcount)

Algebraically these always return the term total (D and D / G), whatever rows they cover. That is right for the grand total, but every subtotal also shows the term total. Do not use them with Row Subtotals; use the level rules above.

If you cannot provide a source denominator

Totals: Measure Rules — suppress an unsupported total

Unique Headcount = distinct

This leaves multi-cell totals blank with an explanation. It prevents a misleading sum; it does not calculate a correct distinct total or repair a percentage denominator.

8. Labels and parameters

Enter in Header: Display Label

Plan Owner Academic Group Desc:College;
Selected Count:@parameter("View By Selector")('Minority');
Selected Percentage:% @parameter("View By Selector")('Minority')

If the parameter is First Gen, the last two headers become First Gen and % First Gen. The default text is used when the parameter has no current value. The parameter and the calculations that respond to it must already exist; this setting only changes the labels.

The original identity remains Selected Percentage. Continue using that original name in formatting and total rules, even when the displayed heading changes. A rename does not change member values or the identity used to retrieve glossary descriptions.

Enter in Header: Hidden Columns

Reporting Sequence; Weight; Denominator

A hidden measure remains available to calculations. A hidden Rows or Columns field still divides the data into groups; hiding it does not combine those groups.

9. Widths and data bars

Enter in Layout: Column Widths

College:260; Headcount:100; Rate:90

Widths are pixels, accepted from 20 through 4000. Named widths override Value Column Width. With wrapping enabled, a column remains wide enough to keep its longest word intact.

Enter in Style: Data Bars

Headcount: #5b7c99;
Rate: #981e32: 100%

The first bar scales to the measure’s largest value in the pivot. The second uses a 100% reference: 1 for fraction-scale values, otherwise 100. The extension infers that scale from the observed range, so values above 1 or negative values can affect the inference. Check the bars against the source units. Bar lengths are capped at a full cell.

Header colors and hover colors use a hex color such as #981e32 or #5e6a71. Text contrast is chosen automatically.

10. Troubleshooting

11. Updates and verification

Keep this file current when the extension changes. Update property names, accepted syntax, defaults, examples, and limitations together. Record the extension version and verification date at the top. Do not label a requested feature as available until it has been implemented and checked.

1. Use the same source fields, filters, and denominator scope in both pivots.
2. Compare every detail row and total, not just a selected percentage.
3. Include overlapping populations and unequal denominators in the checks.
4. Test one term, multiple terms, and a filtered subset. Check zero and missing inputs where applicable.
5. Save, reopen, and verify the intended state. Record untested scopes.