Applying Miscellaneous Functions

Objective

After completing this lesson, you will be able to use the NoFilter function to override report filters, and the RunningSum function to create cumulative totals in a Web Intelligence document.

Report Filter or Ranking Override

You might need to show a calculation that uses both the data shown in the table and data from the query that does not appear in the table. To do this, you need to make Web Intelligence ignore any report filter or ranking applied to an object or variable. Use the NoFilter function to override a report filter or ranking.

Watch the video to use the NoFilter function.

Show the Running Sum of a Measure

You create a report that shows state, year, and sales revenue, with a break by state. You want to add another column to show a running total.

You can use the RunningSum function to return the running sum of a set of numbers.

The syntax for the RunningSum function is:

Number RunningSum([measure];[Row|Col];[(reset_dimensions)])

The parts of the RunningSum function are:

[measure] any measure object in the data source.
Row|Col the direction for the calculation (optional).
reset_dimensions the list of dimensions used to reset the running total (optional).

To show a running sum, use the following syntax :

=RunningSum([Sales revenue])

If you want to reset the running sum for each state, change the formula so the total resets at each state break.

Use the following syntax to modify the formula:

=RunningSum([Sales revenue] ; ([State]))

For more information about this topic, please read SAP BusinessObjects Web Intelligence User's Guide

Let's Summarize What You've Learned

Use the NoFilter() function to delete report filters or rankings and include all data in calculations.
Apply NoFilter() to display totals for all data, even if some values are not shown in the report.
Use the RunningSum() function to calculate cumulative totals across a series of values in a report.
Reset running totals by specifying dimensions, such as restarting the total for each state.

Implement Additional Reporting Functions

Business Example

Your manager asks you to assess the combined sales revenue from California, Colorado, and Texas to plan regional marketing strategies. Additionally, they request the total sales revenue from all states to gain a comprehensive view of overall performance. Lastly, they want to understand the aggregated sales from other states to evaluate their significance.

ExerciseStart Exercise

Next lesson