W - Information Builders

Transcript W - Information Builders

Accumulation Techniques for Reporting with WebFOCUS

Kyle Hachey

Workshop Objectives

       Use WebFOCUS to produce report totals  Row and Column Totals Prefix Operators for Counts, Percentages, Minimums, etc.

Use Compute to combine selected columns Produce section totals at break points  Subtotal and Recompute Review report message options Calculate new summary values with RECAP Apply conditions to operations with WHEN Copyright 2007, Information Builders. Slide 2

W - 4

Providing Totals in Reports

WebFOCUS enables you to produce commons types of totals in reports:     Totals for numeric values in

rows

Totals for numeric values in

columns

Totals for

sort breaks

in a report

Grand totals

The Compensation and Bonus Estimates Report The payroll manager from the Human Resources department needs a report that displays compensation and bonus estimates for each region and a grand total.

W - 4

W -13

Performing Numeric Operations

Prefix

operators

are

mathematical functions

added to the fieldname in a verb phrase. that are   Can only be applied to column fields.

Cannot be applied to sort fields (BY or ACROSS).

W -13

Performing Numeric Operations (continued)

  Numeric operations include:  Calculate the

average

(or mean) of the values of a column Determine the

minimum

maximum

value of a column.

Calculate the

percentage

of a

column

column total.

value to the 

Sum

and

count

W -14

Prefix Operators

Prefix Operator

CNT CNT.DST

DST SUM AVE MIN MAX

Function

Count the number of values for a field. Count the unique occurrences of field values in a sort group.

Determines the total number of distinct values in a single pass of a data source.

Add the values of a field. Calculate the average (mean) value for a field.

Identify the lowest value of a field.

Identify the highest value of a field.

Prefix Operators (continued)

Prefix Operator

ASQ ST TOT PCT RPCT PCT.CNT

FST LST

Function

Compute the average sum of the squares for standard deviation in statistical analysis.

Provide the aggregate value for a sort group in section headings and footings.

Provide the aggregate value of a field (used primarily in page headings and footings).

Calculate the percentage of a column value to the column total. Determine the percentage of a column value to the row total.

Calculate the percentage of a COUNT to the total count.

Select the first instance of a field. * Select the last instance of a field. *

W -14

The Total Compensation Estimate Report The payroll manager from the Human Resources department needs a Total Compensation Estimate report that displays the employee count, salary, benefits and combined salary and benefits for each division.

W - 15

Review: Providing Totals in Reports

To add all numeric verb objects for a row total, use: To total selected fields only, use: To generate a grand total at the end of a report, use:

W -25

The Compensation and Bonus Estimates Report The payroll manager from the Human Resources department needs a Compensation and Bonus Estimates report that displays totals at key break points, such as division and department.

W - 26

Review: Section Totals

Type of Function

At specific sortfield only.

At specific sortfield and all sortfields above it.

To Add Numeric Values To Recalculate Computed Fields W - 37

Customized Formatting

W - 38

Customized Formatting

Message Option

Page Heading Page Footing Section Heading (Subheading) Section Footing (Subfooting) Report Heading Report Footing W - 39

Effect Creates a message at the top of each page.

By default, the message appears left justified above first line of text for each page. Creates a message at the end of each page.

By default, the message appears left justified below last line of text for each page.

Options include FOOTING BOTTOM, which displays the footing at the extreme bottom of each page.

Creates a message above a sort group when the sort values change.

By default, the message appears left justified above each section of the sort group. Creates a message below a sort group when the sort values change.

By default, the message appears left justified below each section of the sort group. Creates a one-time message at the top of the first page of the report.

By default, the message appears left justified above first line of text for the report. Creates a one-time message at the bottom of the last page of the report.

By default, the message appears left justified below the last line of text for the report.

The Sales Metrics for All Products Management would like a message in a report that displays the bonus for a Manufacturing Plant, if the plant’s profit exceeds 14 percent.

W - 40

Using Recap

Command Evaluation Point DEFINE

Evaluated as each record that meets the selection criteria is retrieved from the data source.

COMPUTE

Evaluated at the

Internal Table

level after all the data that meets the selection is retrieved and sorted.

RECAP

Evaluated at the

Internal Table

level, after totals are accumulated and formats are applied prior to presentation.

W - 46

Review: Handling Messages

To display a numeric field’s grand total in a heading, footing, subhead or subfoot, use: To use subtotal values in a calculation, use:

field RECAP

To display a RECAP value in a customized text line, use:

RECAP

and

SUBFOOT

To control conditions under which summary lines appear for each sort field value, use:

WHEN

To minimize the width of a report by suppressing a sort field from displaying, use:

W - 64

Workshop: Review

       Use WebFOCUS to produce report totals  Row and Column Totals Count the number of column values with the prefix operator CNT.