Crystal Reports 9 on Oracle

The only book that explains how to maximize Crystal Reports in the Oracle environment.Focuses on key Oracle database features and tools as they pertain to creating in-depth reports with Crystal Reports.

Annette Harper, OCP, ACE, MCNE (Canal Winchester, OH) has worked with Crystal Reports with various enterprise databases for many years.

Acknowledgments

xii

Introduction

xv

Chapter 1 Connectivity, Authentication, and Privileges

1

(28)

Making the Connection

2

(18)

Database Administrator Tasks

2

(1)

Installation of Oracle Components

3

(7)

Installation of the Crystal XTREME Sample Data

10

(4)

Installation of the Crystal Data Access Components

14

(1)

Configuration of the Native Oracle OCI

15

(1)

OLE DB

16

(1)

ODBC Connection Setup

16

(4)

Choosing a Connection Method

20

(4)

Crystal Database Options

24

(2)

Advanced Options

24

(2)

User IDs and Schemas

26

Authentication Methods

21

(1)

Database Privileges

21

(7)

Connection Options Used in Book

28

(1)

Chapter 2 Oracle Structures

29

(30)

Schemas

30

(1)

Objects

30

(9)

Tables

30

(2)

Views

32

(1)

Materialized Views

33

(1)

Dimensions

34

(1)

Sequence Generators

34

(1)

Synonyms

35

(1)

Indexes

35

(3)

Primary Keys

38

(1)

Database Links

38

(1)

Relationships

39

(3)

Datatypes

42

(12)

Built-In

42

(7)

User-Defined

49

(2)

Collections

51

(3)

Nulls

54

(1)

Linking on Nulls

55

(1)

Data Dictionary Views

55

(4)

Chapter 3 Oracle SQL

59

(26)

Basic SELECT Statement

60

(1)

SELECT List

61

(1)

FROM Clause

62

(1)

Join Types

62

(8)

Pre-Oracle 9i Joining

65

(2)

9i Joining

67

(3)

Filtering

70

(1)

Ordering

71

(1)

Grouping

71

(1)

Group filtering

72

(1)

Operators

72

(1)

Oracle Comparison Conditions

73

(1)

Aggregation

73

(2)

Working with Dates

75

(2)

Dates in the Selection formula

76

(1)

Date Literals in SQL Expressions

76

(1)

Datelime functions

76

(1)

Working with Strings

77

(1)

Working with Numbers

78

(1)

Other Common functions

79

(2)

NVL

79

(1)

NVL2

79

(1)

COALESCE

79

(1)

DECODE

80

(1)

CASE

80

(1)

GREATEST

80

(1)

LEAST

81

(1)

Pseudo Columns

81

(1)

ROWNUM

81

(1)

ROWID

82

(1)

Subqueries

82

(3)

Chapter 4 Oracle Advanced SELECT Options

85

(36)

HAVING Clause

86

(2)

Set Operations

88

(3)

UNION

88

(1)

UNION ALL

89

(1)

INTERSECT

89

(2)

MINUS

91

(1)

Hierarchical Queries

91

(1)

WITH Clause

92

(1)

Aggregation

93

(3)

ROLLUP

95

(1)

CUBE

95

(1)

Analysis

96

(25)

Ranking functions

96

(6)

Reporting Aggregate functions

102

(2)

Windowing Aggregate Functions

104

(6)

FIRST_VALUE and LAST_VALUE

110

(1)

LAG/LEAD

111

(1)

FIRST/LAST

112

(2)

Linear Regression

114

(2)

Inverse Percentile

116

(2)

Hypothetical Rank and Distribution

118

(1)

WIDTH_BUCKET

119

(2)

Chapter 5 PL/SQL

121

(32)

Overview

122

(1)

PL/SQL Structures

123

(14)

Blocks

123

(4)

Cursors

127

(1)

REF CURSORS (Cursor Variables)

128

(1)

Transaction Control

129

(1)

Subprograms

129

(6)

Packages

135

(1)

SQL in PL/SQL

136

(1)

Native Dynamic SQL

136

(1)

REF CURSOR Stored Procedures

137

(16)

Stored Procedure Requirements

138

(2)

Simple Stored Procedure Example

140

(2)

Privileges for REF CURSOR Stored Procedures

142

(1)

Stored Procedure Parameters and Crystal

143

(3)

Using Temporary Tables

146

(1)

Calling Stored Procedures from SQL Commands

146

(2)

Stored Procedure Myths

148

(1)

Issues with Stored Procedures

149

(4)

Chapter 6 Optimizing: The Crystal Reports Side

153

(52)

Reducing the Number of Records

154

(6)

Grouping on the Server

154

(3)

Using SQL Expressions in Selection formulas

157

(3)

Parameters

160

(1)

Miscellaneous Hints

160

(4)

Limiting Subreport Usage

160

(2)

Connection Type

162

(1)

Use Union All Instead of Union

162

(1)

Avoid Sort in Specified Order

162

(1)

Indexed fields

162

(1)

Avoid linking Stored Procedure Datasets to Other Objects

163

(1)

Large Character fields

163

(1)

SQL Expressions

164

(36)

Operators

166

Functions

114

(83)

Running Totals

197

(3)

SQL Commands

200

(5)

SQL Allowed in SQL Commands

200

(1)

Optimizing SQL Commands

201

(1)

SQL Command Parameters

202

(1)

Schema-Specific Queries

203

(2)

Chapter 7 Optimizing: The Oracle Side

205

(38)

Oracle Optimizers

206

(2)

Rule-Based

206

(1)

Cost-Based

207

(1)

Execution Plans

208

(11)

Displaying Execution Plans

208

(1)

Access Methods

209

(5)

Joins

214

(4)

Operations

218

(1)

Views

218

(1)

Bind Variables

218

(1)

Other

218

(1)

Execution Plan Subreport

219

(4)

Optimizing the Environment

223

(6)

Optimizer Initialization Parameters

223

(2)

Memory Allocation

225

(4)

Optimizing the Execution Plan

229

(14)

Tips

229

(2)

Optimizer Hints

231

(7)

Indexing

238

(3)

Stored Outlines

241

(2)

Chapter 8 Optimizing: Reducing Parses

243

(32)

Description of Parsing

244

(1)

Reducing Hard Parsing

245

(21)

Looking at Oracle's V$SQL Dynamic Performance View

245

(1)

Crystal Parameters and Parsing

246

(1)

Subreport for SQL Statistics

247

(6)

Oracle Bind Variables

253

(1)

SQL Commands and Parsing

254

(1)

Stored Procedures and Parsing

255

(3)

CURSOR_SHARING=FORCE

258

(3)

CURSOR_SHARING=SIMILAR

261

(4)

Choosing a Parse Reduction Method

265

(1)

Caching Cursors

266

(9)

Chapter 9 Other Tips

275

(28)

Materialized Views

276

(10)

Overview

276

(6)

Query Rewrite

282

(3)

Freshness Subreport

285

(1)

External Tables

286

(2)

Configuration

286

(1)

Sample Report

287

(1)

Heterogeneous Services: Generic Connectivity

288

(5)

Configuration

288

(3)

Report Example

291

(1)

Using Synonyms

292

(1)

Oracle Flashback Query

293

(8)

Configuration

294

(1)

SELECT AS OF a Point in the Past

295

(1)

Reporting Using SELECT AS OF

296

(2)

DBMS FLASHBACK

298

(3)

Updating Data via Crystal Reports

301

(2)

Via Stored Procedures

301

(1)

Via SQL Commands

301

(2)

Chapter 10 Data Dictionary Report

303

(18)

Oracle Data Dictionary Views

304

(1)

Report Strategy

304

(2)

Creating the Query

306

(7)

Obtaining All Objects

307

(2)

Object level fields

309

(2)

Detail Level

311

(1)

Final Query

312

(1)

Creating the Report

313

(8)

Preliminary Setup

313

(1)

Create the Report

314

(3)

Page Setup

317

(1)

Add the Parameter

317

(1)

Create formulas

317

(1)

Modify the Group Options

318

(1)

Populate and Format the Sections

318

(3)

Chapter 11 The Crystal Repository

321

(38)

Creating the Repository

328

(9)

Creating the Oracle Schema

328

(1)

Creating the Repository Tables

329

(3)

Creating an ODBC DSN

332

(2)

Populating Repository Sample Data

334

(2)

Configuring Crystal to Use the New Repository

336

(1)

Test the New Repository Tables

336

(1)

Configuring Security for the Repository

337

(17)

Understanding the Repository Data Model

337

(2)

Determining Security levels

339

(3)

Implementing Repository Security

342

(12)

Using the Repository

354

(1)

A Repository Report

355

(4)

Appendix A Common Issues

359

(2)

Crystal Problems

360

(1)

Oracle Errors

361

(3)

Crystal Reports Nuances

364

Appendix B Functions

361

(12)

Reproducing Crystal functions

368

(1)

Other functions

369

(4)

Index

373

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Crystal Reports 9 on Oracle

Summary

Author Biography

Table of Contents

Supplemental Materials