Implementing Additional Reporting Features

Objectives

After completing this lesson, you will be able to:

  • Use additional report features

Report Filter or Ranking Override

You may need to display a calculation that includes both the data that the table displays, and the data that is present in the data provider but not currently displayed in the table. The only way to achieve this is to force Web Intelligence to ignore a report filter or ranking that is applied to an object or variable in the data provider.

Use the NoFilter() function to override a report filter or ranking. NoFilter() includes all the values of a variable in the data provider in the calculation, even if the data does not display in the table or block.

The syntax for the NoFilter() function is as follows:

NoFilter(object)

Report Filter or Ranking Override – An Example

See the following video to learn more about the Use of the No Filter() Function.

The RunningSum() Function

The RunningSum() function is one of several running aggregate functions available for calculating data in Web Intelligence.

Other running aggregate functions include:

  • RunningAverage()
  • RunningCount()
  • RunningMax()
  • RunningMin()
  • RunningProduct()

A running sum is a total that is accumulated from value to value across a series of values.

You can use the RunningSum() function to return the running sum of a set of numbers. One feature of running aggregate functions is that a reset can be applied to them within the report. This allows you to start the cumulative total again at a given value (for example, at a break).

The syntax for using the RunningSum() function is:

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

Syntax of RunningSum() Function

The following table describes the components of the syntax of the RunningSum() function

Syntax ComponentDescription
[measure]This refers to any measure object in the data provider
Row|ColThis refers to the calculation direction (optional parameter)
reset_dimensionsThis refers to the list of dimensions used to reset the running sum (optional parameter)

Usage of the RunningSum() Function – 1

You create a report to show State, Year, and Sales revenue with a break by State. You need to add another column to the report to display a running sum.

Use the formula:

=RunningSum([Sales revenue])

If you add a Running Total column to the report, the report displays as shown.

Usage of the RunningSum() Function – 2

If you want the running total to be reset for each state, modify the formula to reset the total each time the table breaks.

Use the following syntax to modify the formula:

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

User Response Functions

The syntax of the UserResponse function is:

string UserResponse([dp;]prompt_string[;Index])

UserResponse Functions Parameters

ParameterDescriptionTypeRequired
dpThe data providerData ProviderNo
prompt_stringThe prompt textStringYes
IndexTells the function to return the database primary keys of the prompt valuesKeywordNo
Note
  • You must enclose the name of the data provider in square brackets.
  • You can use the DataProvider function to provide a reference to a data provider.
  • If you select more than one value in answer to a prompt, the function returns a string consisting of a list of values (or primary keys if the Index operator is specified) separated by semi-colons.

Examples of UserResponse Functions:

  • UserResponse("Which city?") returns Los Angeles, if you entered Los Angeles in the Which City? prompt.
  • UserResponse([Sales Query] ; "Which city?") returns Los Angeles, if you entered Los Angeles in the Which City? prompt in the Sales Query data provider.
  • UserResponse([Sales Query] ; "Which city?" ; Index) returns 23, if you entered Los Angeles in the Which City? prompt in the Sales Query data provider, and the database primary key of Los Angeles is 23.

Implement Additional Reporting Functions

Hide Blocks

You can use the Hide functionality to hide entire blocks of data. You can always hide them, or you can use a formula to hide them conditionally.

Conditionally Hide a Block

Log in to track your progress & complete quizzes