Simple calculation examples

The examples in this section show you how to use basic functions with Discoverer to manipulate and analyze data.

Examples:

• "Example: Calculate the number of rows returned by a query"

• "Example: Calculate a 25% increase in sales"

• "Example: Convert text to upper-case"

Notes

• Examples in this section use a selection of commonly used commands. For a complete list of commands and their full syntax, refer to the Oracle SQL and warehousing guides.

• For more information on how to create calculations, see "Using calculations".

Example: Calculate the number of rows returned by a query

This example calculates the number of rows returned by a query using the Oracle function ROWCOUNT().

Worksheet options	Set to:
Items	Video Analysis Information: Calendar Year, Region, City, Sales SUM
Sort Order	Year, Region, City
Conditions	Department = Video Sale OR Department = Video Rental Calendar Year = 2000 Region = Central
Calculation Name	Rows returned
Calculation	ROWCOUNT

Figure A-1 Worksheet containing the Rows returned calculation

Description of the illustration af_simpb.gif

The worksheet shows the number of rows returned for each city in the central region for the year 2000.

Notes

• ROWCOUNT does not count NULL values. To calculate the number of rows returned by a query, including NULL values, do the following:

  1. first create a temporary item called 'One record', with the formula = '1'

  2. create a calculation called 'Rows returned' to count the occurrences of One record, with the formula = SUM(Video Sales Analysis.One record)

Example: Calculate a 25% increase in sales

This example calculates a 25% increase in sales.

Worksheet options	Set to:
Items	Video Analysis Information: Calendar Year, Region, City, Sales SUM
Sort Order	Year, Region, City
Conditions	Department = Video Sale OR Department = Video Rental Calendar Year = 2000 Region = Central
Calculation Name	25% Increase
Calculation	Sales SUM * 1.25

Figure A-2 Worksheet containing the 25% sales increase calculation.

Description of the illustration af_simpl.gif

The worksheet shows a 25% increase in sales for cities in the central region.

Example: Convert text to upper-case

As well as the extensive range of mathematical functions available in Discoverer, you also have access to a wide range of number and text formatting functions. This example uses a calculation to re-format City text data to upper-case

Worksheet options	Set to:
Items	Video Analysis Information: Calendar Year, Region, City, Sales SUM
Sort Order	Year, Region
Conditions	Department = Video Sale OR Department = Video Rental Calendar Year = 2000 Region = Central
Calculation Name	City(Upper Case)
Calculation	UPPER(City)

Figure A-3 Worksheet containing the City(Upper Case) calculation

Description of the illustration af_simpa.gif

The figure above shows a worksheet containing the city names for the central region converted to upper case.