Oracle CRM On Demand Reporting | Oracle Press

Oracle CRM On Demand Reporting

Authors: Michael D. Lairson

Published: September 2008

eISBN: 9780071593052 0071593055 | ISBN: 9780071593045

Contents

Acknowledgments

Introduction

1 Getting Started with Answers On Demand

Report Development Privileges

The Getting Started with Answers Window

Open Existing Analysis

Manage Analyses

Subject Areas

Explore the Prebuilt Reports

Quick Lists

Pipeline Analysis

Sales Effectiveness

Customers

Service

Marketing Effectiveness

2 Planning Your Reports

Planning and Report Validity

Why Do You Need a Report?

How Will You Use This Report?

Physical Use

Cognitive Use

Who Will Use the Report?

What Should the Report Contain?

Analytics Subject Area Versus Reporting Subject Area

Selecting a Subject Area

Determining Data Visibility

Locating Columns for Your Report

3 Formatting and Sorting Report Data

Build and View Analyses

Adding Columns

Formatting Columns

Style Tab

Column Format

Data Format

Conditional Format

Apply Formatting Changes

Sorting Columns

Sorting Multiple Columns

Data Grouping and Default Sorts

4 Filtering Report Data

Filtering Data

Column Filters

Filter Groups

Predefined Filters

Saving Filters

Advanced Filtering

Expressions

Variables

SQL Filters

Reports as Filters

5 Modifying Data with Formulas

Changing Column Formulas

Custom Headers

Analytical Field References

Using Special Columns

Multi-Select Picklist Values

Metric Columns

Date Dimension Columns

Setting Aggregation Rules

Forcing Specific Values

Expressing Literals

Inserting Blank Columns

Changing Column Formulas

Simple Column Formulas

Bins

Functions

Session Functions and Variables

Session Functions

Session Variables

Filter Function

6 Using Column Formulas with Numeric Data

Aggregate Functions

AVG

AVG(DISTINCT)

BOTTOMN

BY

COUNT

COUNT(DISTINCT)

COUNT(*)

MAX

MEDIAN

MIN

NTILE

PERCENTILE

RANK

STDDEV and STDDEV_SAMP

STDDEV_POP

SUM

SUM(DISTINCT)

TOPN

Running and Moving Aggregate Functions

MAVG

MSUM

RSUM

RCOUNT

RMAX

RMIN

Math Functions

Operators

ABS

CEILING

FLOOR

MOD

PI

POWER

RAND

ROUND

SIGN

SQRT

TRUNCATE

Calculus and Trigonometry Functions

ACOS

ASIN

ATAN

ATAN2

COS

COT

DEGREES

EXP

LOG

LOG10

RADIANS

SIN

TAN

Organizing Complex Formulas

7 Using Column Formulas with Date and Time Data

Date/Time Literals

Current Date/Time Functions

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

NOW

Extract Date/Time Functions

DAY

DAY_OF_QUARTER

DAYNAME

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

HOUR

MINUTE

MONTH

MONTH_OF_QUARTER

MONTHNAME

QUARTER

QUARTER_OF_YEAR

SECOND

WEEK

WEEK_OF_QUARTER

WEEK_OF_YEAR

YEAR

Extract Functions Summary

Performing Time Calculations

TIMESTAMPADD

Advanced Timestamp Calculations

Filtering with Timestamp Calculations

TIMESTAMPDIFF

CURRENT_DATE or NOW()?

Useful Samples

Upcoming Dates Report

Current Week Filter

Rolling Date Filter

Calculating Differences in Business Days

8 Using Column Formulas with Text Data

String Functions

ASCII

BIT_LENGTH

CHAR

CHAR_LENGTH or CHARACTER_LENGTH

CONCAT

INSERT

LEFT

LENGTH

LOCATE

LOCATEN

LOWER or LCASE

OCTET_LENGTH

POSITION

REPEAT

REPLACE

RIGHT

SPACE

SUBSTRING

TRIM

UPPER or UCASE

Useful Samples

Build an Email Address

Line Breaks in a Report

Address Labels

Initial Cap Column

Additional Tips

9 Using Column Formulas to Apply Logic and Transform Data

CASE Statements

CASE Switch

CASE If

Nesting CASE Statements

Converting Data with the CAST Function

Converting Numeric Types

Converting Strings

Converting Date/Time Data

Converting a NULL Value with the IFNULL Function

10 Formatting Report Title and Table Views

Formatting the Title View

Format View

Edit View

Formatting the Table View

Format View

Edit View

“LayOUTside” the Box

Applying Extra-Thick Borders

Displaying Totals Only

Applying a Thick Vertical Line in a Table

Displaying Images in the Table Row

Formatting with HTML

11 Adding Charts and Graphs to Reports

Introduction to Chart Views

Using the Chart View

Chart Controls

Basic Chart Types

Area Chart

Horizontal Bar Chart

Bubble Chart

Vertical Bar Chart

Line Chart

Line Bar Combo Chart

Pareto Chart

Pie Chart

Radar Chart

Scatter Chart

Step Chart

Advanced Charts

Gauge View

Funnel Chart

“LayOUTside” the Box

12 Adding Pivot Tables to Reports

Introduction to Pivot Tables

Basic Pivot Tables

Formatting Pivot Table Areas

Formatting Columns in Your Pivot Table

Pivot Charts

Inserting Pivot Charts

Formatting Pivot Charts

“LayOUTside” the Box

Formatting Tips

Inserting Sections Inside a Table

Creating a Section-Only Record Detail Report

Creating a Pivot Chart Dashboard

Troubleshooting

Pivot Table Requirements

Slow Script

Governor Limit Exceeded

Assertion Failure

13 Adding Advanced Views and Creating Custom Dashboards

Informational and Advanced Report Views

Informational Views

Advanced Views

Creating Dashboards

Controlling Dashboard Access

Constructing the Dashboard Filter Prompt

Managing Dashboards

Adjusting Dashboard Layout

Dashboard Design Considerations

“LayOUTside” the Box

Embedded Reports

View Selector and Narrative Views

Scrolling Charts

14 Adding Filter Prompts to Reports

Overview of Filter Prompts

Column Filter Prompts

Image Prompts

15 Combining Reports

Using a Report to Filter Another Report

Combining Reports into a Single Report

Mind Your Data Types

Union Types

Using Multiple Set Operations—Null Reporting

Using a Combined Analysis to Filter Another Report

Displaying Combined Data in a Pivot Table

Working with Result Columns

Filtering Combined Analyses

16 Adding Interactivity to Reports

Changing Default Drill-Down Behavior

Navigating Between Reports

Drilling Down to Record Details

Navigating into Reports

Using Web Link Fields to Access Reports

Creating a Custom Web Applet with a Report

Displaying a Report on a Homepage

Embedding Reports on Web Tabs

Providing External Links

17 Managing Reports and Controlling Access

Report Management

Organizing Reports

Controlling Analytic Data Access

Analytics Performance Guidelines

Performance Guidelines

Choose the Right Subject Area

A Report Is Not Always the Best Solution

The ID Is Key for Detail Reports

Two Reports Are Often Better Than One

The Higher You Go, the More You Can See

The More You Combine, the Lower the Performance

Simplify for Speed

A: Quick Reference

Index