Implementing Extended Syntax Operators

Objective

After completing this lesson, you will be able to implement extended syntax operators in a Web Intelligence document.

Context Operators In and Where

You set input and output contexts with context operators in the extended syntax.

The In Operator

The In operator specifies dimensions explicitly in a context.

Two tables compare quarterly and yearly sales revenue, highlighting the highest quarterly revenue per year, with formulas for calculating maximum sales revenue by year and quarter.

The example in the Max Quarterly Revenue figure shows a report with year and sales revenue. Your query also has the quarter dimension, but you do not add this to the block. Instead, you add a column to show the highest sales revenue by quarter for each year.

You can find where the values in the Max Quarterly Revenue column come from by comparing this block to one that includes the quarter dimension.

In Context Operator with Additional Dimension

Two tables list quarterly sales revenue for 2024 and 2025, highlighting the maximum value per year with formulas for calculating the yearly maximum.

The Max Quarterly Revenue break footer shows the highest quarterly revenue for each year. For example, Q4 has the highest revenue in 2024.

You use the In operator in the formula for Max Quarterly Revenue:

=Max([Sales revenue] In ([Year] ; [Quarter]))

This formula calculates the highest sales revenue for each year and quarter, then shows this value by year.

The Where Operator

The Where operator restricts the data used to calculate a measure.

A formula retrieves the 2025 Q3 sales revenue value, $3,953,395, from a table displaying Year and Quarter.

For example, the variable Sales Revenue 2025 Q3 calculates the sales revenue for 2025 Q3:

=[Sales revenue] Where ([Year]="2025" And [Quarter]="Q3")

The In operator specifies the dimensions used in the calculation. The Where operator limits the data to find the measure.

Implement Context Operators In and Where

Business Example

Your manager asks you to create a report focusing on sales performance in Florida's key market. They need to see the specific revenue for 2023 and also identify Florida's highest performing year on record. Displaying these key figures will help the sales team set targets for the upcoming year.

ExerciseStart Exercise

Context Operator ForEach

Watch the video to implement calculation context with ForEach.

Implement Context Operator ForEach

Business Example

Your manager asks you to analyze historical sales data to identify the weakest sales quarter for each year. They want to understand if there is a recurring seasonal slump that the marketing team should address. By calculating the minimum quarterly revenue for every year, you can pinpoint these low-performing periods for them.

ExerciseStart Exercise

Context Operator ForAll

Watch the video to implement calculation context with ForAll.

Let's Summarize What You've Learned

Use the In operator to specify dimensions for calculations within a data context.
Apply the Where operator to restrict data used in a calculation based on specific conditions.
The ForEach operator adds specific dimensions to a context in your report formulas.
Use the ForAll context operator to remove dimensions from a context in a report formula.

Implement Context Operator ForAll

Business Example

Your manager asks you to create a sales report showing each quarter's performance. To provide better context, they want the total revenue for the entire year to appear alongside each quarterly amount. This will allow them to instantly see how much each quarter contributed to the final annual sales figure.

ExerciseStart Exercise

Next lesson