Crystal Report XI Release 2 User Guide Topics

Contents
Introduction to SAP Crystal Reports 2011............................................................................21 Chapter 1
About Crystal Reports...........................................................................................................21 1.1
About the Crystal Reports documentation..............................................................................21 1.2
Locate information quickly......................................................................................................22 1.2.1
Sample Reports.....................................................................................................................22 1.2.2
Quick Start............................................................................................................................23 Chapter 2
Learning how to use Crystal Reports.....................................................................................23 2.1
Sample data - Xtreme.mdb.....................................................................................................23 2.1.1
Report Creation Wizards........................................................................................................24 2.2
Standard................................................................................................................................24 2.2.1
Cross-Tab..............................................................................................................................24 2.2.2
Mailing Label..........................................................................................................................25 2.2.3
OLAP.....................................................................................................................................25 2.2.4
Quick start for new users.......................................................................................................25 2.3
Before you begin....................................................................................................................26 2.3.1
Creating the report.................................................................................................................26 2.3.2
Record Selection...................................................................................................................37 2.3.3
Grouping and sorting..............................................................................................................41 2.3.4
Completing the report............................................................................................................45 2.3.5
Quick start for advanced users...............................................................................................46 2.4
Choosing a report type and data source.................................................................................46 2.4.1
Working with report elements on the Design tab....................................................................47 2.4.2
Using other reporting features................................................................................................48 2.4.3
Report Design Concepts.......................................................................................................51 Chapter 3
Basic report design................................................................................................................51 3.1
Deciding on the content of the report.....................................................................................51 3.2
Stating the purpose................................................................................................................51 3.2.1
Determining the layout of the report ......................................................................................52 3.2.2
Finding the data......................................................................................................................53 3.2.3
Manipulating the data.............................................................................................................54 3.2.4
2012-03-14 3
Determining printing area characteristics................................................................................55 3.2.5
Developing a prototype on paper...........................................................................................57 3.3
To design a paper prototype...................................................................................................57 3.3.1
Introduction to Reporting......................................................................................................59 Chapter 4
Report creation options..........................................................................................................59 4.1
Choosing data sources and database fields...........................................................................60 4.2
The Database Expert..............................................................................................................60 4.2.1
The Field Explorer...................................................................................................................62 4.2.2
About the report design environment.....................................................................................64 4.3
Design tab.............................................................................................................................64 4.3.1
Preview tab............................................................................................................................68 4.3.2
HTML Preview tab..................................................................................................................72 4.3.3
Creating a new report............................................................................................................73 4.4
Selecting the data source.......................................................................................................73 4.4.1
Adding tables.........................................................................................................................74 4.4.2
Linking multiple tables............................................................................................................75 4.4.3
Placing data on the report......................................................................................................76 4.4.4
BLOB (Binary Large Object) fields.........................................................................................83 4.4.5
Formatting data......................................................................................................................83 4.4.6
Record selection....................................................................................................................84 4.4.7
Grouping, sorting, and summarizing data................................................................................84 4.4.8
Using the drill-down option on summarized data.....................................................................84 4.4.9
Using the zoom feature..........................................................................................................85 4.4.10
Inserting page headers and footers........................................................................................85 4.4.11
Adding a title page to the report.............................................................................................86 4.4.12
Adding summary information to the report..............................................................................87 4.4.13
Exploring reports and working with multiple reports................................................................88 4.5
The Report Explorer...............................................................................................................88 4.5.1
The Workbench......................................................................................................................90 4.5.2
The Dependency Checker......................................................................................................91 4.5.3
Opening and docking explorers..............................................................................................93 4.5.4
Beyond basic reports.............................................................................................................94 4.6
SAP BusinessObjects Enterprise Repository........................................................................95 Chapter 5
What is the SAP BusinessObjects Enterprise Repository?.....................................................95 5.1
Work flow..............................................................................................................................95 5.1.1
Accessing the SAP BusinessObjects Enterprise Repository..................................................96 5.2
To open the SAP BusinessObjects Enterprise Repository......................................................97 5.2.1
Repository Explorer toolbar....................................................................................................97 5.3
2012-03-14 4
Contents
Adding subfolders and subcategories to the repository..........................................................98 5.4
To add a new subfolder or subcategory..................................................................................99 5.4.1
Adding items to the repository...............................................................................................99 5.5
Adding a text object or bitmap image.....................................................................................99 5.5.1
Adding a text object or bitmap image - another method........................................................100 5.5.2
Adding a custom function.....................................................................................................100 5.5.3
Adding a command...............................................................................................................101 5.5.4
Using repository objects in reports ......................................................................................101 5.6
Adding a text object or a bitmap image to a report...............................................................102 5.6.1
Adding a custom function to a report....................................................................................102 5.6.2
Adding a command to a report.............................................................................................103 5.6.3
Adding a list of values to a parameter...................................................................................103 5.6.4
Modifying objects in the repository.......................................................................................103 5.7
To modify a repository object...............................................................................................104 5.7.1
Updating connected repository objects in reports................................................................104 5.8
To set the update option......................................................................................................105 5.8.1
Deleting items from the repository.......................................................................................105 5.9
To delete an object from the repository................................................................................105 5.9.1
Using Undo in the repository................................................................................................106 5.10
Designing Optimized Web Reports.....................................................................................107 Chapter 6
Overview..............................................................................................................................107 6.1
Key strategies for optimizing web reports............................................................................108 6.1.1
Scale with SAP BusinessObjects Business Intelligence platform .........................................108 6.2
Evaluation times for date functions in SAP BusinessObjects Business Intelligence platform .109 6.2.1
Making the right design choices...........................................................................................110 6.3
Using faster report formats..................................................................................................110 6.3.1
Choosing between live and saved data................................................................................111 6.3.2
Designing summary reports.................................................................................................112 6.3.3
Using subreports carefully....................................................................................................113 6.3.4
Using other design elements effectively...............................................................................115 6.3.5
Designing reports to maximize data sharing.........................................................................115 6.3.6
Streamlining your reporting environment..............................................................................116 6.4
Selecting the fastest database and connection....................................................................116 6.4.1
Using table indexes..............................................................................................................116 6.4.2
Improving table-linking choices.............................................................................................117 6.4.3
Using thread-safe database drivers......................................................................................117 6.4.4
Using stored procedures for faster processing.....................................................................118 6.4.5
Using enhanced record selection formulas ..........................................................................119 6.5
Pushing down record selection—an example.......................................................................119 6.5.1
Record selection performance tips.......................................................................................120 6.5.2
2012-03-14 5
Contents
Strategies for writing efficient record selection formulas......................................................121 6.5.3
Incorporating parameter fields into record selection formulas...............................................124 6.5.4
Using SQL expressions where appropriate..........................................................................125 6.5.5
Improving grouping, sorting, and totaling..............................................................................125 6.6
Performing grouping on server.............................................................................................126 6.6.1
Benefits of grouping on server—an example........................................................................126 6.6.2
Using SQL Expressions for groups, sorts, and totals...........................................................127 6.6.3
Using SQL Expressions for Case Logic................................................................................128 6.6.4
Inserting summary and running total fields where possible....................................................128 6.6.5
Record Selection.................................................................................................................129 Chapter 7
Selecting records.................................................................................................................129 7.1
Options for selecting records...............................................................................................129 7.1.1
Determining which field(s) to use.........................................................................................129 7.1.2
Using the Select Expert........................................................................................................130 7.1.3
Using formulas.....................................................................................................................132 7.1.4
Interaction of the Select Expert and the Formula Editor........................................................132 7.1.5
Saved data selection formulas..............................................................................................134 7.1.6
Using formula templates.......................................................................................................134 7.2
Record selection formula templates.....................................................................................134 7.2.1
Pushing down record selection to the database server.........................................................137 7.3
Troubleshooting record selection formulas...........................................................................137 7.4
To troubleshoot record selection formulas...........................................................................137 7.4.1
Correcting selections that do not generate data...................................................................139 7.4.2
Sorting, Grouping, and Totaling..........................................................................................141 Chapter 8
Sorting data.........................................................................................................................141 8.1
Understanding sort options..................................................................................................141 8.1.1
Sorting single and multiple fields..........................................................................................143 8.1.2
Sort controls........................................................................................................................145 8.1.3
Grouping data......................................................................................................................147 8.2
Grouping data......................................................................................................................147 8.2.1
Creating custom groups.......................................................................................................149 8.2.2
Sorting groups conditionally.................................................................................................150 8.2.3
Sorting records within groups..............................................................................................151 8.2.4
Group selection...................................................................................................................152 8.2.5
Grouping data in intervals.....................................................................................................156 8.2.6
Grouping by the first letter of a company name....................................................................158 8.2.7
Grouping data hierarchically.................................................................................................159 8.2.8
Editing groups......................................................................................................................165 8.2.9
2012-03-14 6
Contents
Summarizing grouped data...................................................................................................166 8.3
To summarize grouped data.................................................................................................166 8.3.1
Ordering groups by summarized values................................................................................167 8.3.2
Selecting top or bottom N groups or percentages................................................................168 8.3.3
Selecting top or bottom groups or percentages conditionally...............................................170 8.3.4
Subtotaling...........................................................................................................................171 8.4
Subtotaling data...................................................................................................................171 8.4.1
Extending prices and subtotaling the extensions...................................................................172 8.4.2
Percentages.........................................................................................................................173 8.5
Calculating a percentage......................................................................................................174 8.5.1
Group headers.....................................................................................................................175 8.6
Creating group headers........................................................................................................175 8.6.1
Suppressing group headers.................................................................................................179 8.6.2
Drilling-down on group headers............................................................................................179 8.6.3
Running Totals....................................................................................................................181 Chapter 9
Understanding running totals................................................................................................181 9.1
How running totals work.......................................................................................................181 9.1.1
Creating running totals.........................................................................................................182 9.2
Creating running totals in a list.............................................................................................182 9.2.1
Creating running totals for a group.......................................................................................184 9.2.2
Creating conditional running totals.......................................................................................185 9.2.3
Creating running totals in a one-to-many linking relationship.................................................187 9.2.4
Creating running totals using a formula.................................................................................188 9.3
To create running totals using a formula...............................................................................188 9.3.1
Multiple Section Reports.....................................................................................................191 Chapter 10
About sections.....................................................................................................................191 10.1
Working with sections..........................................................................................................191 10.2
Inserting a section................................................................................................................192 10.2.1
Deleting a section................................................................................................................192 10.2.2
Moving a section..................................................................................................................193 10.2.3
Merging two related sections...............................................................................................193 10.2.4
Splitting and resizing sections..............................................................................................194 10.3
Splitting a section.................................................................................................................194 10.3.1
Resizing a section................................................................................................................195 10.3.2
Using multiple sections in reports ........................................................................................196 10.4
Keeping variable length objects from overwriting each other................................................196 10.4.1
Eliminating blank lines when fields are empty........................................................................197 10.4.2
Adding blank lines conditionally............................................................................................198 10.4.3
2012-03-14 7
Contents
Form letters.........................................................................................................................198 10.5
Working with text objects.....................................................................................................199 10.5.1
Creating a form letter using a text object..............................................................................200 10.5.2
Printing conditional messages in form letters........................................................................205 10.5.3
Formatting...........................................................................................................................207 Chapter 11
Formatting concepts............................................................................................................207 11.1
Using a template..................................................................................................................207 11.2
Applying a template..............................................................................................................208 11.2.1
Removing an applied template..............................................................................................209 11.2.2
Reapplying the last template selected..................................................................................209 11.2.3
Using Template Field Objects...............................................................................................210 11.2.4
Template considerations......................................................................................................211 11.2.5
Using the Report Design Environment..................................................................................213 11.3
Section characteristics.........................................................................................................214 11.3.1
Making an object underlay a following section......................................................................214 11.3.2
Pre-printed forms.................................................................................................................216 11.3.3
Multiple columns..................................................................................................................216 11.3.4
Hiding report sections..........................................................................................................217 11.3.5
Hiding report objects............................................................................................................218 11.3.6
Placing text-based objects....................................................................................................220 11.3.7
Placing multi-line, text-based objects....................................................................................223 11.3.8
Importing text-based objects from a file................................................................................224 11.3.9
Spacing between text-based objects....................................................................................225 11.3.10
To allow for overflow field representation.............................................................................231 11.3.11
Selecting multiple objects.....................................................................................................232 11.3.12
Vertical placement...............................................................................................................233 11.3.13
Inserting character and line spacing......................................................................................233 11.3.14
Setting fractional font sizes..................................................................................................234 11.3.15
Setting page size and page orientation.................................................................................235 11.3.16
Setting page margins...........................................................................................................236 11.3.17
TrueType fonts.....................................................................................................................236 11.3.18
Printer drivers......................................................................................................................237 11.3.19
Formatting a report for web viewing.....................................................................................238 11.3.20
Formatting properties...........................................................................................................238 11.4
Working with absolute formatting.........................................................................................239 11.5
Adding borders, color, and shading to a field........................................................................239 11.5.1
Making a report, section, area, or object read-only...............................................................240 11.5.2
Locking an object's size and position....................................................................................241 11.5.3
Changing your default field formats......................................................................................241 11.5.4
Adding and editing lines.......................................................................................................243 11.5.5
2012-03-14 8
Contents
Adding and editing boxes.....................................................................................................244 11.5.6
Expected behavior of line and box formatting.......................................................................245 11.5.7
Adding shapes to a report....................................................................................................247 11.5.8
Scaling, cropping, and sizing objects....................................................................................248 11.5.9
Using conventional accounting formats................................................................................249 11.5.10
Repeating report objects on horizontal pages.......................................................................250 11.5.11
Using white space between rows.........................................................................................252 11.5.12
Working with conditional formatting......................................................................................254 11.6
Conditional on or off properties............................................................................................255 11.6.1
Conditional attribute properties............................................................................................255 11.6.2
Changing margins conditionally............................................................................................257 11.6.3
Changing X position conditionally.........................................................................................258 11.6.4
Creating footers after the first page.....................................................................................258 11.6.5
Using the Highlighting Expert................................................................................................260 11.6.6
Undo/Redo activities...........................................................................................................263 11.6.7
Using the Format Painter......................................................................................................263 11.7
To copy and apply formatting...............................................................................................264 11.7.1
Working with barcodes.........................................................................................................265 11.8
Adding a barcode.................................................................................................................265 11.8.1
Changing the appearance of a barcode................................................................................266 11.8.2
Removing a barcode............................................................................................................267 11.8.3
Charting..............................................................................................................................269 Chapter 12
Charting concepts................................................................................................................269 12.1
Charting overview................................................................................................................269 12.1.1
Chart layouts.......................................................................................................................270 12.1.2
Chart types..........................................................................................................................271 12.1.3
Where to place a chart ........................................................................................................273 12.1.4
Drill-down with charts...........................................................................................................273 12.1.5
Drill-down with legends........................................................................................................273 12.1.6
Creating charts....................................................................................................................274 12.2
Charting on details or formula fields (Advanced layout)........................................................274 12.2.1
Charting on summary or subtotal fields (Group layout).........................................................275 12.2.2
Charting on Cross-Tab summaries (Cross-Tab layout).........................................................276 12.2.3
Charting on an OLAP cube (OLAP layout)............................................................................277 12.2.4
Working with charts.............................................................................................................278 12.3
Editing charts using the Chart Expert....................................................................................278 12.3.1
Editing charts using the Chart Options menu items..............................................................278 12.3.2
Editing charts using other menu items..................................................................................279 12.3.3
Using the zooming features with bar and line charts.............................................................280 12.3.4
Auto-arranging charts...........................................................................................................281 12.3.5
2012-03-14 9
Contents
Formatting charts.................................................................................................................282 12.3.6
Using the underlay feature with charts.................................................................................283 12.3.7
Mapping..............................................................................................................................285 Chapter 13
Mapping concepts................................................................................................................285 13.1
Mapping overview................................................................................................................285 13.1.1
Map layouts.........................................................................................................................285 13.1.2
Map types............................................................................................................................286 13.1.3
Where to place a map..........................................................................................................288 13.1.4
Drill-down with maps............................................................................................................289 13.1.5
Creating maps......................................................................................................................289 13.2
Mapping on group fields (Group layout)................................................................................289 13.2.1
Mapping on Cross-Tab summaries (Cross-Tab layout).........................................................290 13.2.2
Mapping on an OLAP cube (OLAP layout)............................................................................292 13.2.3
Working with maps...............................................................................................................293 13.3
Editing maps using the Map Expert.......................................................................................293 13.3.1
Changing the map title.........................................................................................................294 13.3.2
Changing the map type........................................................................................................294 13.3.3
Changing map layers............................................................................................................294 13.3.4
Resolving data mismatches..................................................................................................296 13.3.5
Changing the geographic map..............................................................................................296 13.3.6
Zooming in and out on a map...............................................................................................297 13.3.7
Panning a map......................................................................................................................298 13.3.8
Centering a map...................................................................................................................298 13.3.9
Hiding and showing the Map Navigator................................................................................298 13.3.10
Formatting Maps..................................................................................................................300 13.3.11
To underlay a map................................................................................................................300 13.3.12
OLE.....................................................................................................................................301 Chapter 14
OLE overview.......................................................................................................................301 14.1
OLE terminology..................................................................................................................301 14.1.1
Types of OLE objects...........................................................................................................302 14.1.2
General OLE considerations.................................................................................................302 14.1.3
Inserting OLE objects into reports........................................................................................303 14.2
To copy and paste OLE objects ...........................................................................................303 14.2.1
How OLE objects are represented in a report.......................................................................304 14.3
Editing OLE objects in reports..............................................................................................304 14.4
In-place editing.....................................................................................................................304 14.4.1
Dynamic OLE menu commands............................................................................................305 14.4.2
OLE and the Insert Picture command...................................................................................305 14.4.3
2012-03-14 10
Contents
Working with static OLE objects..........................................................................................306 14.5
Inserting a static OLE object.................................................................................................306 14.5.1
Making a static OLE object dynamic.....................................................................................307 14.5.2
Converting a static OLE object to a bitmap image object......................................................308 14.5.3
Working with embedded vs. linked objects...........................................................................308 14.6
Embedded objects...............................................................................................................308 14.6.1
Linked objects......................................................................................................................309 14.6.2
Integrating Shockwave Flash (SWF) objects........................................................................310 14.7
Printing Shockwave Flash (SWF) objects.............................................................................311 14.7.1
Adding an Xcelsius SWF object to a report..........................................................................312 14.7.2
Using data binding to bind an Xcelsius SWF object to a report.............................................313 14.7.3
Cross-Tab Objects..............................................................................................................315 Chapter 15
What is a Cross-Tab object?................................................................................................315 15.1
Cross-Tab example..............................................................................................................316 15.2
Report of order data - no sorting/grouping...........................................................................316 15.2.1
Report of order data - grouped by region.............................................................................317 15.2.2
Report of order data - grouped by product...........................................................................318 15.2.3
Report of order data - grouped by region and product..........................................................319 15.2.4
Order data in a Cross-Tab object.........................................................................................320 15.2.5
Creating a Cross-Tab report.................................................................................................321 15.3
To create a new cross-tab report.........................................................................................322 15.3.1
To add a Cross-Tab to an existing report..............................................................................322 15.3.2
Specifying the data source...................................................................................................323 15.3.3
Modifying the links...............................................................................................................324 15.3.4
Adding a chart......................................................................................................................324 15.3.5
Selecting records.................................................................................................................325 15.3.6
Defining the structure of the Cross-Tab...............................................................................325 15.3.7
Applying a predefined style and finishing the report..............................................................326 15.3.8
Adding a Cross-Tab to an existing report using the Cross-Tab Expert..................................326 15.3.9
Working with Cross-Tabs.....................................................................................................329 15.4
Showing values as percentages...........................................................................................329 15.4.1
Abbreviating large summarized fields ...................................................................................329 15.4.2
Customizing row/column labels............................................................................................330 15.4.3
Using running totals in Cross-Tabs.......................................................................................331 15.4.4
Printing Cross-Tabs that span multiple pages ......................................................................332 15.4.5
Formatting Cross-Tabs.........................................................................................................333 15.5
Changing width, height, and alignment of Cross-Tab cells....................................................333 15.5.1
Formatting background color of entire rows/columns...........................................................333 15.5.2
Formatting fields individually.................................................................................................334 15.5.3
Formatting several fields at one time....................................................................................334 15.5.4
2012-03-14 11
Contents
Suppressing Cross-Tab data................................................................................................334 15.5.5
Displaying summarized fields horizontally.............................................................................336 15.5.6
Advanced Cross-Tab features..............................................................................................336 15.6
Calculated Members............................................................................................................336 15.6.1
Embedded Summaries.........................................................................................................340 15.6.2
Building Queries.................................................................................................................343 Chapter 16
Connecting to a universe.....................................................................................................343 16.1
To connect to a universe......................................................................................................343 16.1.1
Defining the data selection for a query.................................................................................344 16.2
To build a simple query.........................................................................................................344 16.2.1
To create a combined query.................................................................................................345 16.2.2
Quick reference to objects...................................................................................................345 16.2.3
Editing an existing query.......................................................................................................346 16.3
To edit an existing query......................................................................................................347 16.3.1
Viewing the SQL behind a query..........................................................................................347 16.4
To view the SQL when you create a query...........................................................................347 16.4.1
To view the SQL after you have created a query..................................................................348 16.4.2
Query filters and prompts.....................................................................................................348 16.5
Creating query filters............................................................................................................348 16.5.1
Building prompts..................................................................................................................349 16.5.2
Combining query filters and prompts....................................................................................350 16.5.3
Using And or Or to combine query filters.............................................................................351 16.5.4
Quick reference to query filter operators..............................................................................351 16.5.5
Editing and removing query filters.........................................................................................354 16.5.6
Filtering data using subqueries and database ranking...........................................................354 16.6
What is a subquery?.............................................................................................................355 16.6.1
Building a subquery..............................................................................................................355 16.6.2
What is a database ranking?.................................................................................................357 16.6.3
Creating a database ranking.................................................................................................358 16.6.4
Creating and Updating OLAP Reports................................................................................361 Chapter 17
OLAP reporting with Crystal Reports...................................................................................361 17.1
OLAP grid objects................................................................................................................361 17.1.1
Creating an OLAP report......................................................................................................362 17.2
To create an OLAP report....................................................................................................362 17.2.1
Specifying the data source...................................................................................................362 17.2.2
Defining the structure of the grid..........................................................................................363 17.2.3
Setting sliced dimensions and specifying the number of grids..............................................365 17.2.4
Applying a predefined style..................................................................................................366 17.2.5
2012-03-14 12
Contents
Inserting a chart...................................................................................................................367 17.2.6
Updating an OLAP report.....................................................................................................368 17.3
To update a cube location in an OLAP grid object................................................................368 17.3.1
To update the database location...........................................................................................369 17.3.2
Formatting data in an OLAP grid...........................................................................................369 17.4
Changing the background color of a dimension....................................................................370 17.4.1
Creating an alias for a dimension..........................................................................................371 17.4.2
Formatting grid lines.............................................................................................................371 17.4.3
Labeling dimensions.............................................................................................................372 17.4.4
Changing the view of OLAP data..........................................................................................372 17.5
To show or hide dimension members ..................................................................................372 17.5.1
To create asymmetry in an OLAP grid..................................................................................373 17.5.2
To add totals to an OLAP grid..............................................................................................373 17.5.3
To change the display format for member names.................................................................373 17.5.4
To alter the data displayed in the OLAP grid.........................................................................374 17.5.5
To define the order of fields in the OLAP grid ......................................................................374 17.5.6
Sorting and filtering OLAP grid data.....................................................................................374 17.6
Sorting data in an OLAP grid................................................................................................374 17.6.1
Filtering data in an OLAP grid...............................................................................................376 17.6.2
Adding calculations to OLAP grids.......................................................................................377 17.7
Printing, Exporting, and Viewing Reports............................................................................379 Chapter 18
Distributing reports..............................................................................................................379 18.1
Printing a report...................................................................................................................379 18.1.1
Faxing a report.....................................................................................................................379 18.1.2
Exporting a report.................................................................................................................380 18.1.3
Working with Web folders....................................................................................................390 18.1.4
Working with Enterprise folders............................................................................................391 18.1.5
Viewing reports....................................................................................................................393 18.2
Report Parts and other Report objects.................................................................................394 18.2.1
Creating an Enterprise Hyperlink..........................................................................................405 18.2.2
Using smart tags..................................................................................................................406 18.3
To use smart tags with a Crystal Reports object..................................................................407 18.3.1
Report Alerts.......................................................................................................................409 Chapter 19
About Report Alerts.............................................................................................................409 19.1
Working with Report Alerts..................................................................................................409 19.2
Creating Report Alerts.........................................................................................................409 19.2.1
Editing Report Alerts............................................................................................................411 19.2.2
Deleting Report Alerts..........................................................................................................412 19.2.3
2012-03-14 13
Contents
Viewing Report Alerts..........................................................................................................412 19.2.4
Referring to Report Alerts in formulas..................................................................................413 19.2.5
Using Formulas...................................................................................................................415 Chapter 20
Formulas overview...............................................................................................................415 20.1
Typical uses for formulas......................................................................................................415 20.1.1
Formula components and syntax..........................................................................................416 20.2
Formula components............................................................................................................416 20.2.1
Formula syntax.....................................................................................................................417 20.2.2
User Function Libraries in formulas......................................................................................418 20.3
Specifying formulas..............................................................................................................419 20.4
Working with the Formula Workshop....................................................................................420 20.4.1
Working with the Formula Editor...........................................................................................424 20.4.2
Creating and modifying formulas..........................................................................................428 20.5
Creating a formula and inserting it into a report....................................................................428 20.5.1
Creating a formula in the Formula Expert..............................................................................429 20.5.2
Editing formulas....................................................................................................................431 20.5.3
Searching for formula text....................................................................................................431 20.5.4
Copying formulas.................................................................................................................432 20.5.5
Deleting formulas.................................................................................................................434 20.6
Removing the working formula from your report...................................................................434 20.6.1
Deleting the formula specification.........................................................................................434 20.6.2
Debugging formulas.............................................................................................................435 20.7
Debugging evaluation time errors.........................................................................................435 20.7.1
Debugging tutorial................................................................................................................436 20.7.2
Error Messages and Formula Compiler Warnings.................................................................440 20.8
Parameter Fields and Prompts............................................................................................451 Chapter 21
Parameter and prompt overview...........................................................................................451 21.1
Parameter field considerations.............................................................................................451 21.1.1
Prompt considerations.........................................................................................................452 21.1.2
Data and non-data parameters.............................................................................................453 21.1.3
Optional parameters.............................................................................................................454 21.1.4
Understanding dynamic prompts..........................................................................................454 21.2
Understanding lists of values................................................................................................457 21.3
List-of-values types..............................................................................................................457 21.3.1
Determining which list-of-values type to use.........................................................................459 21.3.2
Lists of values and prompt groups contrasted......................................................................460 21.3.3
Creating a parameter with a static prompt............................................................................461 21.4
To create a parameter with a static prompt .........................................................................461 21.4.1
2012-03-14 14
Contents
To incorporate the parameter into the record selection filter.................................................463 21.4.2
To incorporate the parameter into a saved data selection formula........................................464 21.4.3
Creating a parameter with a dynamic prompt.......................................................................464 21.5
To create a parameter with a dynamic prompt......................................................................465 21.5.1
Creating a parameter with a cascading list of values............................................................466 21.6
To create a parameter with a cascading list of values...........................................................467 21.6.1
The Parameter Panel............................................................................................................469 21.7
Working with lists of values..................................................................................................469 21.8
Sharing common lists of values within a report.....................................................................470 21.8.1
Using separate value and description fields..........................................................................471 21.8.2
Using command objects as list-of-values data sources.........................................................472 21.8.3
Null handling........................................................................................................................472 21.8.4
Long lists of values..............................................................................................................473 21.8.5
Best practices for prompting................................................................................................474 21.9
Unmanaged reports.............................................................................................................474 21.9.1
Managed reports..................................................................................................................474 21.9.2
Converting unmanaged reports to managed reports.............................................................475 21.9.3
Deploying managed reports with dynamic prompts..............................................................476 21.9.4
Deleting parameter fields.....................................................................................................476 21.10
To delete a parameter that is not used in a formula..............................................................476 21.10.1
To delete a parameter used with the Select Expert...............................................................476 21.10.2
To delete a parameter that is used in a formula....................................................................477 21.10.3
Responding to parameter field prompts................................................................................477 21.11
Previewing a report for the first time....................................................................................477 21.11.1
Refreshing report data.........................................................................................................478 21.11.2
Advanced parameter features..............................................................................................479 21.12
Creating a parameter with multiple prompting values............................................................479 21.12.1
Applying conditional formatting using parameter fields.........................................................480 21.12.2
Creating a report title using parameter fields........................................................................481 21.12.3
Specifying single or ranges of values...................................................................................481 21.12.4
Incorporating a parameter into a formula..............................................................................482 21.12.5
Defining sort order using parameter fields............................................................................483 21.12.6
Defining entry type and format using the Edit Mask..............................................................484 21.12.7
Creating a saved-data record filter using parameter fields....................................................485 21.12.8
To create a parameter appearing on the Parameter Panel....................................................486 21.12.9
To incorporate the parameter into a saved data selection formula........................................487 21.12.10
Adding dynamic grouping using parameter fields..................................................................487 21.12.11
Subreports...........................................................................................................................489 Chapter 22
What are subreports?...........................................................................................................489 22.1
Unlinked vs. linked subreports.............................................................................................489 22.1.1
2012-03-14 15
Contents
How subreport linking works................................................................................................491 22.1.2
Inserting subreports.............................................................................................................492 22.2
Previewing subreports..........................................................................................................493 22.2.1
Saving a subreport as a primary report.................................................................................494 22.2.2
Updating subreports.............................................................................................................494 22.2.3
Linking a subreport to the data in the primary report............................................................495 22.3
To link a subreport to the data in the primary report.............................................................496 22.3.1
Linking a subreport to the main report without modifying the selection formula....................497 22.4
To link a subreport to a main report without modifying the selection formula........................497 22.4.1
Combining unrelated reports by using subreports................................................................497 22.5
To combine two unrelated reports........................................................................................498 22.5.1
To combine three or more unrelated reports........................................................................498 22.5.2
Using subreports with unlinkable data..................................................................................498 22.6
Linking to/from a formula field..............................................................................................499 22.6.1
Linking unindexed tables......................................................................................................500 22.6.2
Creating an on-demand subreport........................................................................................500 22.7
To create an on-demand subreport.......................................................................................501 22.7.1
Adding captions to on-demand subreports...........................................................................501 22.8
To add a caption...................................................................................................................501 22.8.1
Showing different views of the same data in a report...........................................................502 22.9
Understanding Databases...................................................................................................503 Chapter 23
Databases overview.............................................................................................................503 23.1
Relational database basics...................................................................................................503 23.1.1
Indexed tables......................................................................................................................504 23.1.2
Using SQL and SQL databases............................................................................................507 23.2
What is SQL?.......................................................................................................................507 23.2.1
SQL DBMS.........................................................................................................................509 23.2.2
How does Crystal Reports use SQL?...................................................................................511 23.2.3
The SQL language...............................................................................................................512 23.2.4
Defining an SQL Command..................................................................................................514 23.2.5
Changing data sources.........................................................................................................517 23.3
To change the data source...................................................................................................518 23.3.1
Working with aliases............................................................................................................519 23.4
Linking tables.......................................................................................................................520 23.5
Link from and link to.............................................................................................................520 23.5.1
Link relationships.................................................................................................................520 23.5.2
Performance considerations in one-to-many links.................................................................521 23.5.3
Data file considerations........................................................................................................524 23.5.4
SQL database considerations..............................................................................................526 23.5.5
Performance considerations for all reports...........................................................................527 23.5.6
2012-03-14 16
Contents
The Database Expert Links tab.............................................................................................529 23.5.7
Linking indexed tables..........................................................................................................529 23.5.8
Link processing order...........................................................................................................530 23.5.9
Linking options.....................................................................................................................531 23.5.10
Server-side processing........................................................................................................544 23.6
How server-side grouping affects the SQL query.................................................................546 23.6.1
Mapping database fields.......................................................................................................547 23.7
About the Map Fields dialog box..........................................................................................547 23.7.1
Remapping processes..........................................................................................................548 23.7.2
Remapping altered database fields.......................................................................................550 23.7.3
Saved Data Indexes.............................................................................................................551 23.8
How Report Indexing works.................................................................................................551 23.8.1
Considerations for using Saved Data Indexes......................................................................552 23.8.2
Indexing the right field(s)......................................................................................................552 23.8.3
Unicode support in Crystal Reports.....................................................................................553 23.9
Working with databases.......................................................................................................553 23.10
Using Access queries..........................................................................................................553 23.10.1
Using ODBC data sources...................................................................................................557 23.10.2
Advanced database features................................................................................................559 23.11
One-to-many links ...............................................................................................................559 23.11.1
ACT! databases...................................................................................................................560 23.11.2
Reporting on an NT Event Log.............................................................................................561 23.11.3
For additional information.....................................................................................................562 23.12
Integrating Business Applications with Crystal Reports.....................................................563 Chapter 24
Integrating with SAP.............................................................................................................563 24.1
Introduction..........................................................................................................................563 24.1.1
Add-Ons..............................................................................................................................567 24.1.2
Reporting with SAP..............................................................................................................567 24.1.3
Integrating with Oracle E-Business Suite..............................................................................625 24.2
Overview..............................................................................................................................625 24.2.1
To access data in SAP Crystal Reports................................................................................626 24.2.2
Integrating with Siebel..........................................................................................................626 24.3
Connecting to Siebel............................................................................................................626 24.3.1
Configuring the connection..................................................................................................629 24.3.2
Selecting business components...........................................................................................633 24.3.3
Refreshing reports with Siebel data......................................................................................633 24.3.4
Updating reports created in earlier versions of Crystal Reports............................................634 24.3.5
Security...............................................................................................................................634 24.3.6
Views...................................................................................................................................634 24.3.7
Using Siebel formatting........................................................................................................635 24.3.8
2012-03-14 17
Contents
Linking tables in reports.......................................................................................................635 24.3.9
Multi-value columns.............................................................................................................636 24.3.10
Data Types..........................................................................................................................636 24.3.11
Integrating with PeopleSoft..................................................................................................638 24.4
Overview..............................................................................................................................638 24.4.1
To access data in SAP Crystal Reports................................................................................638 24.4.2
Integrating with JD Edwards.................................................................................................640 24.5
Overview..............................................................................................................................640 24.5.1
To access data in SAP Crystal Reports................................................................................640 24.5.2
Accessing Data Sources.....................................................................................................643 Appendix A
Introduction..........................................................................................................................643 A.1
Six types of data..................................................................................................................643 A.1.1
Direct access database files................................................................................................644 A.2
Advantages..........................................................................................................................644 A.2.1
Disadvantages.....................................................................................................................644 A.2.2
Three layers.........................................................................................................................645 A.2.3
Common database formats..................................................................................................646 A.2.4
ODBC data sources.............................................................................................................655 A.3
Advantages..........................................................................................................................656 A.3.1
Disadvantages.....................................................................................................................656 A.3.2
Five layers............................................................................................................................657 A.3.3
Installing database clients.....................................................................................................659 A.3.4
To ensure correct ODBC DSN configuration.......................................................................659 A.3.5
Common ODBC database formats......................................................................................660 A.3.6
JDBC data sources .............................................................................................................663 A.4
JNDI....................................................................................................................................664 A.4.1
Five layers............................................................................................................................664 A.4.2
Supported JDBC database formats.....................................................................................665 A.4.3
Configuring the Crystal Reports JDBC driver.......................................................................666 A.4.4
Business Objects universes.................................................................................................666 A.5
Business Views...................................................................................................................667 A.6
Crystal SQL Designer files...................................................................................................668 A.7
Crystal Dictionary files.........................................................................................................669 A.8
Report Processing Model....................................................................................................671 Appendix B
Overview..............................................................................................................................671 B.1
What is a "pass"?.................................................................................................................671 B.1.1
Pre-pass 1...........................................................................................................................671 B.1.2
Pass 1..................................................................................................................................671 B.1.3
2012-03-14 18
Contents
Pre-pass 2...........................................................................................................................672 B.1.4
Pass 2..................................................................................................................................672 B.1.5
Pass 3..................................................................................................................................673 B.1.6
Crystal Reports Error Messages.........................................................................................675 Appendix C
Drive:\filename.extension.....................................................................................................675 C.1
Drive:\test.rpt......................................................................................................................675 C.2
Failed to load database connector........................................................................................676 C.3
Failed to open the connection. Details: [Database Vendor Code: ]...676 C.4
Failed to retrieve data from the database. Details: [Database Vendor Code: number>].............................................................................................................................677
C.5
Creating Accessible Reports...............................................................................................679 Appendix D
About accessibility...............................................................................................................679 D.1
Benefits of accessible reports..............................................................................................679 D.1.1
About the accessibility guidelines.........................................................................................680 D.1.2
Accessibility and Business Objects products.......................................................................681 D.1.3
Improving report accessibility...............................................................................................682 D.2
Placing objects in reports.....................................................................................................682 D.2.1
Text......................................................................................................................................684 D.2.2
Color....................................................................................................................................687 D.2.3
Navigation............................................................................................................................688 D.2.4
Parameter fields...................................................................................................................689 D.2.5
Designing for flexibility.........................................................................................................690 D.3
Accessibility and conditional formatting................................................................................690 D.3.1
Accessibility and suppressing sections.................................................................................691 D.3.2
Accessibility and subreports.................................................................................................692 D.3.3
Improving data table accessibility.........................................................................................693 D.4
Text objects and data table values........................................................................................693 D.4.1
Other data table design considerations................................................................................698 D.4.2
Accessibility and BusinessObjects Business Intelligence platform........................................699 D.5
Setting accessible preferences for SAP BusinessObjects Business Intelligence platform.....699 D.5.1
Accessibility and customization............................................................................................700 D.6
Resources...........................................................................................................................701 D.7
More Information.................................................................................................................703 Appendix E
Index 705