Hp Intelligent Management Center Standard Software Pl Manual de usuario Pagina 1

Crystal Reports 2008 SP3 User'sGuideCrystal Reports 2008windows

Multiple Section Reports 273Chapter 12About sections...274Workin

Report creation optionsEach time you create a new report, you have three options:• Use a Report Creation Wizard.• Use another report as a model.• Crea

New reportThe Blank Report option is used to create a report from scratch. This is usefulwhen you want the full flexibility and control of building a

New reportThe Blank Report option is used to create a report from scratch. This is usefulwhen you want the full flexibility and control of building a

Data tabTree ViewThe Data tab of the Database Expert shows a tree view of possible datasources you can select when creating a report. The tree—in the

Use this option to add a Table object that has been created using an SQLcommand.• Make New ConnectionUse this option to create a new connection.• Remo

Links tabDatabase tables are linked so records from one database match relatedrecords from another. For example, if you activate a Suppliers table and

Use this option to add a field to the report. You can insert more than onefield at a time by selecting multiple fields, right-clicking, and choosingIn

Use this option to find occurrences of the selected item throughout all ofthe formulas in your report.• Set Prompt OrderUse this option to open the Se

About the report design environmentDesign tabWhen working with Crystal Reports, you will probably use the Design tabmore than any other part of the pr

Design tab areasWhen you first begin creating a report, Crystal Reports automatically createsfive areas in the Design tab.• Report HeaderThis section

Making an object underlay a following section...303Pre-printed forms...

This section generally holds the summary value, if any, and can be usedto display charts or cross-tabs. It is printed once at the end of a group.When

Identifying and working with areas and sectionsBy default, each area contains only a single section. The name for that sectionappears directly to the

•Add horizontal and vertical guidelines by clicking the rulers. See Designingwith guidelines .• Zoom in and out on a report at any magnification from

• From that point on, the program uses the saved data whenever youpreview the report unless you specifically refresh it or add a field thatrequires th

Group TreeThe Group Tree can be shown or hidden using the Toggle PreviewPanel button on the Standard toolbar.The Group Tree presents a split screen:•

forth between different groups, the smart navigation features of the GroupTree make your work extremely efficient.The Parameter PanelThe Parameter Pan

Comparisons with the Design tabYou have the same formatting capabilities in the Preview tab as you do inthe Design tab. Menus (both menu bar and short

• The program places a sizing frame around the specific value youselect.• It highlights every other value in the field.• Likewise, when you select a s

For information about how to configure the HTML Preview Options, searchthe Crystal Reports online help for "Smart Tag & HTML Preview tab (Opt

• Access/Excel (DAO)This option lets you connect to a supported database type (Access,dBASE, Excel, Lotus, and so on). You can create a new connection

Repeating report objects on horizontal pages ...344Using white space between rows ...

To add a table1. Choose Database Expert from the Database menu.The Database Expert dialog box appears.2. On the Data tab, search for the database you

4. To create links manually, drag a field from one table to a field in anothertable. If successful, a link line is created. If unsuccessful, a message

satisfied with the link, you can modify it using the Links tab of the DatabaseExpert.Related Topics• Understanding Databases• Linking optionsPlacing d

Formula fieldsTo display data that is a calculated value, you need to create a formula fieldand place that formula field on the report. For example, i

4. Type the expression in the SQL Expression Editor.Note:For an overview of the formula language, see Formula components andsyntax.5. Click Save.Param

Running total fieldsTo display a total that evaluates each record and provides a running sum ofall the values in a field (or all the values in a certa

Each special field is inserted into the report as an object. An object frameappears. You can now place it on the report.Note:To change the formatting

The ruler allows you to add indents and align text within the text object.Note:When you first insert the text object into the report, the object is au

Note:Crystal Reports can also access BLOB objects by reference (that is,dynamically through a file path), so that you do not need to store the BLOBobj

To create a hyperlink field1. Select a report object on the Design or Preview tab.2. On the Format menu, click Hyperlink.The Format Editor dialog box

Working with charts...381Editing charts using the Chart Expert...

• A FileSelect this option to create a hyperlink to a file on a specific computeror networked computer. Use the formula button to create a file pathba

Formatting dataAt this point in creating a report, you may want to do some basic formatting.Perhaps you would like to change the font size and style o

sort the records within each region in alphabetic order by Customer Name.Many of your reports will use some type of sorting. Depending on the report,y

• If you have multiple summaries, you can look at the summaries behindsummaries (the city summaries that make up the region summaries, forexample), or

• Information to appear at the top of every page goes in the Page Header(PH).• Information to appear at the bottom of every page goes in the Page Foot

4. Move the object frame to the Report Header section and click once toplace the frame.5.With the report title selected, click Section Expert on the E

Enter the desired information and click OK when finished to return to yourreport.Exploring reports and working withmultiple reportsCrystal Reports let

objects are listed. Any item you select in the tree view will be selected in thereport (in either Design or Preview modes).You can modify report field

•Show/Hide Grids and SubreportsUse this option to show or hide cross-tabs, OLAP grids, and subreportsin your report.Note:•Items connected to a Busines

Note:This option is available only when there is an active report.• Add New ProjectWhen you select this option, the program adds a new folder under th

Hiding and showing the Map Navigator ...408Formatting Maps...

entity.) When you choose this option, the Publish Object dialog boxappears.For information about using this dialog box, search the Crystal Reportsonli

MeaningIconSuccess. The Dependency Checkerhas verified that there are no errorsin the file.Warning. The Dependency Checkerhas found something in the f

• Sort ByUse this option to sort the messages by type, number, description, orlocation.• ClearUse this option to remove the selected message.• Clear A

After installation, each explorer opens in a docked position in the ReportDesigner. You can dock explorers in any area of the designer by draggingthem

•Form letters (see Form letters)•Subreports (see Subreports)•Cross-tabs (see Cross-Tab Objects)•Multi-section reports (see Multiple Section Reports)14

BusinessObjects EnterpriseRepository7

What is the BusinessObjects EnterpriseRepository?The BusinessObjects Enterprise Repository is a database in which youmanage shared report objects. You

The Repository Explorer displays the contents of your BusinessObjectsEnterprise Repository as a tree with folders, subfolders, categories, andsubcateg

• Commands can be updated and added back to the repository throughthe Database Expert.Accessing the BusinessObjects EnterpriseRepositoryThe repository

4. Expand nodes to see the repository contents.The Repository Explorer might appear in a docked position in the ReportDesigner depending on where it w

Cross-Tab example...431Report of order data - no sorting/grouping...

Use this option to add a new subfolder to the repository. For information,see Adding subfolders and subcategories to the repository.Note:This option i

Tip:The insert button changes automatically from folder to category dependingon the type of object that you select.A new subfolder or subcategory is a

Tip:Author and description information appears in tooltips and can be searchedfor using the Advanced filtering option.You'll see that your text o

Adding a custom function1. In the Formula Workshop, expand the Report Custom Functions nodeand select the custom function you want to add to the repos

Using repository objects in reportsOnce you have an object or a collection of objects added to the repository,you can start using them in your Crystal

For information about how to log on to BusinessObjects Enterprise, seeWorking with Enterprise folders .3. Expand the appropriate folder in the Reposit

Tip:Another way to do this is to click the Database Expert option under theDatabase menu.2. In the Database Expert, expand the Repository folder.Tip:I

To modify a repository objectNote:This procedure shows you how to modify and update a text object in therepository. Custom functions and commands are

To set the update option1. On the File menu, click Options.The Options dialog box appears.2. Click the Reporting tab.3. Select the Update Connected Re

To delete an object from the repository1.Click the Repository Explorer button on the Standard toolbar.Tip:Another way to do this is to click the Repos

Embedded Summaries...458Building Queries 461Chapter 18Connecting to a universe...

160 Crystal Reports 2008 SP3 User's GuideBusinessObjects Enterprise Repository7Using Undo in the repository

Designing Optimized WebReports8

OverviewWhether you distribute your enterprise reports over a local network, acorporate Intranet, or the Web, you can use Crystal Reports' powerf

Note:Understanding databases and how they work is often important whenconsidering performance. See Databases overview for backgroundinformation.Key st

BusinessObjects Enterprise provides granular group, user, and data-levelsecurity to help you protect sensitive reports and deliver a morepersonalized

Evaluation times for date functions inBusinessObjects EnterpriseDifferent date and time functions available in Crystal Reports are evaluatedat differe

Making the right design choicesThis section offers design options and considerations that will help improvethe performance of your reports. The topics

Note:If for some reason you need to keep a report in its original file format, usethe Save As command (instead of Save) and enter a new name for theve

Saved dataReports with saved data are useful for dealing with data that isn't continuallyupdated. When users navigate through reports with saved

Designing summary reportsDesigning and distributing summary reports is a relatively easy way to ensurethat users quickly find the data they need over

Creating an OLAP report...487To create an OLAP report...

Note:For details on minimizing data transfer with summary reports, see Performinggrouping on server.To hide details in a summary report1. Open your re

report, no data is retrieved for the on-demand subreport until you drill downon the hyperlink.For example, when designing a report that shows each emp

database record in the main report.) As an alternative, consider usinglinked on-demand subreports in the Details section of your main report.Linking t

Designing reports to maximize data sharingBusinessObjects Enterprise has data-sharing functionality that improvesperformance by reducing the number of

Streamlining your reporting environmentAnother step to ensuring that report users receive their information quicklyis to assess your reporting environ

ways to adhere to this guideline are to assess your reporting needs and toplan your strategy before creating your report. When you know exactly whatyo

Using thread-safe database driversIf you share and refresh reports over the web, open them using thread-safedatabase drivers whenever possible. The Cr

• crdb_olap.dll (OLAP)• crdb_oracle.dll (Oracle)• crdb_p2ssby10.dll (Sybase)Note:Check the Release Notes in the Crystal Reports product distribution f

Crystal Reports analyzes your record selection formula and generates anSQL query from it. This SQL query is then processed by the database, whichsends

Crystal Reports, no further records need to be eliminated. Click Show SQLQuery on the Database menu and notice that the resulting SQL query hasa WHERE

Faxing a report...506Exporting a report...

• You can only push down AND clauses (not OR).SQL Databases• You can push down record selection on indexed or non-indexed fields.• Your SQL server wil

Consideration 2Any selection formula that is of the form: DataBaseField SupportedOperator ConstantOrParameterExpression can be pushed down.Of course D

Consideration 4SqlExpression SupportedOperatorConstantOrParameterExpression can be pushed down.For example, the selection formula {@ExtendedPrice} >

In general, parameter fields provide interactivity for users, who respond tothe parameter prompts in order to specify the data they want to see. Howev

Note:You've now created the parameter field. The rest of these proceduresdescribe how to add the parameter field to the record selection formulaw

For more information•To create enhanced parameters, see Advanced parameter features.•For general information about parameter fields, see Parameter and

Finally, if your database supports Case Logic, and your report needs tosummarize an If-Then-Else formula calculation, replace the formula with anSQL E

Tip:When Use Indexes or Server for Speed is selected, you can quickly enablePerform Grouping On Server from the Database menu.3. Click OK.Benefits of

8. Drill down on a country by double-clicking the report.Tip:When drill down is available, your cursor turns into a magnifying glass.Crystal Reports r

For instance, suppose that you're reporting off of an MS SQL Server 7database, which supports Case Logic. You need to include an If-Then-Elsecalc

Specifying formulas...559Working with the Formula Workshop...

190 Crystal Reports 2008 SP3 User's GuideDesigning Optimized Web Reports8Improving grouping, sorting, and totaling

Record Selection9

Selecting recordsWhen a field is selected to appear on a report, field values from every recordin the active table(s) are printed by default. In many

Assume, for example, that you want a report that only shows data fromCalifornia. The challenge is to find the best way to identify those records thatc

The Select Expert can also be used to set up some sophisticated requests:• Customers whose names start with "A", "M", or "S.&

Tip:To base record selection on more than one field, click the New tab. Selectthe next field from the Choose Field dialog box.A selection formula is g

Interaction of the Select Expert and the FormulaEditorThe Select Expert and the Record/Group Selection Formula Editor areinteractive. That is, record

4. Click the Hide Formula button when you are done with your review.5. Use the Select Expert to change your selection formula.6. Review the updated fo

The Select Expert expands so you can review the formula the programgenerated based on your selection criteria.4. Click the Hide Formula button when yo

Saved data selection formulasSaved data selection formulas filter report data after the records have beenstored in the report. Like record selection f

© 2011 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge,ByDesign, SAP Business ByDesign, and other SAP products and servicesCopy

Creating a parameter with a dynamic prompt...607To create a parameter with a dynamic prompt...

Selects those records in which the value in the {file.FIELD} field does notbegin with the character "C" (includes values like Bob's Bik

Selects those records in which the value in the {file.FIELD} field is greaterthan 11111 but less than 99999 (both 11111 and 99999 are included in ther

Selects those records in which the date found in the {file.DATE} field fallswithin the last full month. (If the month is May, this selects all records

• The first stage of record selection takes place when the database serverprocesses the SQL query and returns a set of records to Crystal Reports.• In

4. Make certain that all fields referenced in the record selection formula (theselectors) are on the report physically and are not hidden.For example,

• If it does, then you know that this part of the selection formula isworking.• If it does not, then troubleshoot this part of the selection formula.7

in the {customer.CONTACT FIRST NAME} field are mixed case ("Bob", forexample), the selection formula will find no matches and thus will not

Check your selection formula closely, and make sure that the spaces in theselection formula match the spaces in the fields you are trying to match.Usi

208 Crystal Reports 2008 SP3 User's GuideRecord Selection9Troubleshooting record selection formulas

Sorting, Grouping, andTotaling10

Creating a saved-data record filter using parameter fields...633To create a saved-data record filter using parameter fields...

Sorting dataSorting means placing data in some kind of order to help you find andevaluate it.When you first insert a database field into your report,

Sort OrderField Typeblankspunctuationnumbersuppercase letterlowercase lettersSingle-character string fieldstwo lettersthree lettersfour letters, and s

Sort OrderField TypeFalse values (0)True values (1)Boolean comparison fieldsnull valuesnon-null valuesNull valuesNote:If sorting and grouping are perf

Sorting single and multiple fieldsIn single field sorting, all the records used in the report are sorted based onthe values in a single field. Sorting

2. Highlight the field to be sorted from the Available Fields area.3. Click the > arrow.The selected field is added to the Sort Fields list.4. Spec

Sort controlsYou may want to design your report so that users can modify a sort field orsort direction without refreshing information from the databas

Note:Sort fields that begin with "Group" specify that the sort was doneautomatically when the data was grouped.2. Highlight the fields that

b. Select a sort field and click OK.c. Click and drag your cursor to the report location where you wouldlike to insert the Sort Control.d. Enter a nam

Group and sort directionWhen data is grouped, four sort and group direction options are available.Direction refers to the order in which the values ar

2. Select the field you want the data grouped by from the top drop-down list.3. Select the sort direction from the second drop-down list.Note:If you w

Relational database basics...658Aliases ...

Tip:To hide the group header name, right-click the group header, selectFormat Field, and click Suppress on the Common tab of the Format Editor.6. Clic

Specified order grouping provides a solution to these custom sorting andgrouping challenges. It enables you to create both the customized groupsthat w

For example, if you group a sales report by invoice date, you might wantyour users to choose whether they see that data from earliest invoice dateto m

For this example, enter this text:If {?Sort Order} = "Ascending" then crAscendingOrderelse crDescendingOrder9. Click Save and close to retur

Note:Sort fields that begin with "Group" specify that the sort was doneautomatically when the data was grouped.2. Highlight the field you wa

• You might want to see only the groups with the highest summary values,or the lowest.You can select the groups that appear in the report in two diffe

The Select Expert dialog box appears.Note:If you click the Select Expert button without first highlighting a summaryfield in your report, the Choose F

2. Click Record to create a record selection formula.- or -Click Group to create a group selection formula.The Formula Workshop appears.3. Enter your

Note:Xtreme.mdb is located on the Business Objects Technical Support website http://support.businessobjects.com/samples/.For each order, the report sh

use the group selection formula:Sum({orders.ORDER AMOUNT}, {customer.REGION})% Sum({orders.ORDER AMOUNT}) < 5Now when you print, only the regions t

For additional information...714Accessing Data Sources 715Chapter 26Introduction.

5. Click the New button.The Define Named Group dialog box appears.6. Type "Less than $10,000" in the Group Name field.This is the name that

7. Since the first group is to contain only those records that have a LastYear's Sales figure of less than $10,000, set the fields so your condit

customers in a group, then all the "B" customers, and so forth. To do thisrequires the use of a formula.Do not worry if you are unfamiliar w

7.On the Insert menu, click Group.The Insert Group dialog box appears.8. Select the formula field as the field you want your data grouped by fromthe f

2. On the Insert menu, click Group.3. In the Insert Group dialog box, select the field that you want to use as thebasis of your hierarchy (the child f

7. On the Report menu, click Hierarchical Grouping Options.8. In the Available Groups list of the Hierarchical Options dialog box, selectthe group you

11. In the Group Indent field, enter the amount you want to indent for eachsubgroup.The value that you enter in the Group Indent field affects all oth

The report is now grouped by employee name and further sorted to showthe supervisor hierarchy. You can see that Mina, whose employee data hadno superv

2.Click the Conditional Formula button next to the X position valuefield.3. In the Formula Workshop, enter your conditional X position formula text.Fo

• There cannot be any circular logic in the data (that is, A cannot be relatedto B, while B is related to C, and C is related back to A).For example,

Pre-pass 1...750Pass 1...

Supervisor (parent)Employee (child)GillianFrancesGillianRuthMinaMargaretMargaretPaulMargaretCharlesThe Employee and Supervisor fields contain overlapp

• The report contains hierarchical group footers that include the recordsthat are lower in the hierarchy of each group. You can summarize dataacross t

• Determine the maximum, minimum, average, or Nth largest value.• Calculate up to two kinds of standard deviations and variances.For example:• Custome

For more information about percentages, see Percentages.6. If you want to summarize across a hierarchy, select Summarize acrosshierarchy.For more info

In such a case, you would select the sum or the average from thisdrop-down list.5. Specify the sort direction.6. To select a second group sort, repeat

With top N groups, you are instructing the program to display thosegroups that have the highest summary values (Top N).•• With bottom N groups, you ar

In such a case, you would select the sum or the average from thisdrop-down list.5. In the Where N is text box, enter the number of groups you want tod

Selecting top or bottom groups or percentagesconditionallyIf you want your users to choose their own value for a Top N, a Bottom N,or a top or bottom

10. Click OK to save your group sort.11. When you are prompted to enter a number or percentage, enter the valuethat you want, and click OK.Your report

The Insert Summary dialog box appears with the chosen field listed asthe field to be summarized.3. Click Insert Group.The Insert Group dialog box appe

Text objects and data table values...777Other data table design considerations...

The Formula Name dialog box appears.4. Enter the name you want to identify the formula, and then click OK.The Formula Workshop appears with the Formul

11. Click OK to return to the Insert Summary dialog box.12. Select the group you just created from the Summary location list andthen click OK.Your dat

The Insert Summary dialog box appears.2. Select the field for which you want to calculate the sum.For example, you may want to insert a field which ca

Group headersCreating group headersWhenever you create a group, a subtotal, or a summary, the program createsboth a Group Footer (GF) section (where i

2. When the object pointer appears, move the object frame into the GroupHeader section.3. Enter the text you want to use for the header.4. Click outsi

Group name with textA more complex type of live header combines a field value and text. A typicalgroup header of this kind for data broken down by reg

For example, if you create this formula:{customer.CUSTOMER NAME}[1]and then group on the formula, the program will group your data based onthe first l

Suppressing group headersYou have the option to hide group headers in your report.To suppress group headers1. Right-click the group header and select

3.Click Refresh.When you place the cursor over a group header, the cursor becomes amagnifying glass.4. Double-click the group header to drill-down to

Running Totals11

26 Crystal Reports 2008 SP3 User's GuideContents

Understanding running totalsRunning total fields are similar to summary fields but allow more control overhow the total is calculated and when it is r

The following list summarizes the records that are included in the calculationwhen a running total is placed in various report sections. This list ass

Creating running totalsCreating running totals in a listRunning totals are totals that can be displayed on a record by record basis.They total all rec

6. Select sum from the Type of summary list.7. In the Evaluate section of the dialog box, click On change of field, andselect Orders.Order ID as the O

Link the Customers and Orders tables and place the following fields fromleft to right in the Details section:{customer.CUSTOMER NAME}{orders.ORDER ID}

Creating conditional running totalsThere may be times when you have a list of values, and you only want tosubtotal some of the values in the list. For

Tip:Another way to do this is to click the Record Sort Expert button onthe Expert Tools toolbar.3. Sort the records based on the Customer.Customer Nam

15. Now create the "CanadaTotal" running total field using the process outlinedin steps 5-13. The only difference is that this time you will

2.On the Insert Tools toolbar, click Insert Group and create a groupbased on the Customer.Customer Name field.3. Right-click the Customer.Last Year&ap

When you create a running total manually, you need to create three formulas:• A summary formula.• A reset formula to set a variable to zero.• A displa

Introduction to CrystalReports 20081

7. Place this formula in the Details section of your report, just to the right ofthe Orders.Order Amount field.This formula prints the running total o

Note:This formula prints the same value that @RunningTotal prints as therunning total for the last record in each group. But since it is printing it i

272 Crystal Reports 2008 SP3 User's GuideRunning Totals11Creating running totals using a formula

Multiple Section Reports12

About sectionsCrystal Reports provides five design areas to use when building your report:• Report Header• Page Header• Details• Report Footer• Page F

Inserting a section1.Click Section Expert on the Expert Tools toolbar.The Section Expert appears with a list of all the sections in the report.When th

Moving a section1.Click Section Expert on the Expert Tools toolbar.The Section Expert appears with a list of all the sections in the report.When there

Note:The program enables only those options (free form, new page before,and so on) that apply to the highlighted section.2. Move the sections so the t

Splitting a section1. Move the pointer over the left boundary of the section you want to split.2. When the pointer becomes a Section Splitting cursor,

A section automatically expands vertically in two instances:• When you place an object and the object is bigger (vertically) than thesection you place

About Crystal ReportsCrystal Reports is designed to work with your database to help you analyzeand interpret important information. Crystal Reports ma

length object is toggled on in the Format Editor, that object may overprintobjects positioned directly below it unless you have:• Expanded the section

• OLAP gridsEliminating blank lines when fields are emptyIt is very common to have two address lines in a customer table, one forstreet address (Addre

To add blank lines conditionally1.Use the Section Expert to create two Details sections. See Working withsections.2. Place the report detail data in t

Working with text objectsYou will use many of the capabilities of text objects when creating form letters.A brief discussion of text objects should ma

wrap. In the edit mode you can insert text and such non-text objects asdatabase fields and formulas. Whenever the object is in edit mode, it containsa

• You can leave edit mode by clicking outside of the text frame. You canalso click Ctrl+Enter.Creating a form letter using a text objectThe following

5. Drag the resizing handle on the right side of the object to the right edgeof the Design tab. This will make the object about eight inches wide, the

Inserting a date1. To insert a date into the letter, expand Special Fields in the Field Explorerdialog box and scroll until you find Print Date.2. Cli

2. Drag in the Address 1 field and place it at the insertion point, and pressEnter. The insertion point moves down to the line below.3. Drag in the Ci

3. In the Field Explorer dialog box, highlight the Contact Title field from theCustomer table and drag it into the text object, placing it immediately

Whether it's the web master in IT, the promotion manager in marketing, thedatabase administrator in finance or the CEO, Crystal Reports is a powe

thank you and your staff for your support. I hope next year will be a banneryear for you."5. Press Enter twice.6. Type "Sincerely yours"

Printing conditional messages in form lettersIt is likely that you will want to print conditional messages in form letters. Forexample, you may want t

• Format the first section to be suppressed when the balance is lessthan the credit limit.• Format the second section to be suppressed when the balanc

Formatting13

Formatting conceptsThis section explains how to format a report. Formatting refers to changesyou can make to the layout and design of a report, as wel

Applying a templateWhen you create a new report in the Standard Report Creation Wizard, youcan apply a template as an optional step. You can also appl

To apply a template to an existing report1. On the Report menu, click Template Expert.The Template Expert appears.Tip:Another way to do this is to cli

Note:To remove a template, you must use this option; the Undo command isnot available from the Edit menu.Reapplying the last template selectedIf you w

To add a Template Field Object to a template report1. On the Insert menu, click Template Field Object.A placement frame is attached to your cursor.2.

2. In the Formula Editor, replace the Space(10) section of the argumentwith a database field of the type you want to see in your sample, saveyour chan

ContentsIntroduction to Crystal Reports 2008 27Chapter 1About Crystal Reports...

30 Crystal Reports 2008 SP3 User's GuideIntroduction to Crystal Reports 20081About the Crystal Reports documentation

The formatting and objects from an existing report used as a template canoverride choices you made in the Standard Report Creation Wizard. Forexample,

ResultIf the target reporthas...If the Template has...Chart retainedAdvanced ChartNo ChartCross-Tab/OLAP GridretainedCross-Tab/OLAP gridCross-Tab/OLAP

Using the Report Design EnvironmentThis section describes several things to keep in mind when designing reportsthat are distributed to different envir

Making an object underlay a following sectionUsing this example, you can make a logo underlay multiple sections. Thisprocedure is similar to the one f

Note:In this example, the picture is placed to the right of the fields to avoidunderlaying the text. When you are working with a watermark, a subduedp

• The position of the picture in the section.By modifying size and placement of an object, you can create a variety ofvisual effects, using the underl

Keep in mind the width of your paper when deciding your column width.For example, if you have three fields in your Details section, and theytake up fo

one to suppress when sales are equal to or over $X and one to suppresswhen sales are under $X.Suppress Blank SectionThe Suppress Blank Section propert

Note:• This option does not work for text fields that contain embedded fields.• This option compares record values, not formatted field values. Thepro

Note:This will only work if there are no other objects in the section.Suppress (Common tab)The Suppress property hides an object when you run the repo

What's New in CrystalReports 2008 SP32

Note:You can click the Conditional Formula button for any of these properties andcreate a formula that will make the setting conditional on some event

• For text-based objects, the default width is approximately 19 averagecharacter widths wide. Text objects are different from database fields inthat t

To prevent the breaks in non-spacing text inside an object1. Select the object you want to format.2. Expand the object frame to make it wider than the

Note:The suppression of embedded field blank lines is designed to remove blanklines within a text object if the text object contains an entirely blank

Placing multi-line, text-based objectsWhile text-based objects that are formatted to print on multiple lines followthe same design rules as other obje

Importing text-based objects from a fileUsing Crystal Reports, you can import a formatted, text-based object froman existing file onto your report.To

Each report contains a design grid. You can select the grid on or off, as wellas set it to different sizes when required. By default, the grid is not

Designing with guidelinesCrystal Reports provides guidelines to help you align and size report objectswith accuracy. Guidelines are non-printing lines

To insert, move, and remove guidelines manually1. In the Design or Preview tab, click the ruler at the top to activate a verticalguideline; click the

You can snap an object's left side, right side, or vertical midline (the invisibleline that bisects an object vertically) to a vertical guideline

IntroductionThis section provides a high-level overview of the components, features, andbenefits that are provided by the latest release of Crystal Re

To place several text objects of different font sizes on one line with theirbaselines lined up, snap each object's baseline to the same horizonta

5. Drag the resizing handle over to the second guideline so that the objectsnaps to the guideline.6. For each additional object you want to snap to bo

• When you select the "Right to Left" reading order, indents aremeasured from the opposite side of the object. That is, a left indentationis

To view the results, refresh the report. If you disabled field clipping, anynumeric/currency field values that are larger than the field objects conta

Vertical placementOn the Common tab of the Format Editor, you can use the text rotationoptions to vertically align the fields and text-based objects o

4. In the Spacing area, in the Character spacing exactly field, specify thevalue n that each character occupies.The value n is defined as the distance

5. Click OK to save your changes.Note:When setting fractional font sizes for individual database fields and text-basedobjects that you've already

Tip:Another way to do this is to click the Section Expert button on theExpert Tools toolbar.2. In the Sections list, select the section to be modified

the margins to 0. If you next choose a bigger page, this (reduced) printablearea is kept and the left/right, top/bottom margin ratios become 1:1.TrueT

Problems such as these may arise when you have:• Two identical printers, but each one is using a different printer driver.• Two different printers usi

XML and Web Services Database DriverWeb services created with SOAP 1.2 are now accessible to Crystal ReportsXML and Web Services driver. This includes

reliance on a printer driver also prevents inconsistencies that can occur ifyour report is viewed with a different printer driver than the one you cre

• Format Editor to format field values.• Section Expert to format entire sections.• Highlighting Expert to conditionally format all types of fields.Ea

To make a report read-only1. On the File menu, select Report Options.2. Select Read-only.To make a section or area read-only1. On the Report menu, sel

To lock an object's size and position1. Select the object whose size and position you want to lock.2.On the Formatting toolbar, click Lock Size/P

Note:These default settings will affect only the objects that you subsequently addto a report. To format fields that you've already added to a re

Note:If you chose to format "Date and Time" fields at Step 3, then you will seethree tabs in the Custom Style dialog box (Date and Time, Dat

2. Use the pencil cursor to draw the line where desired.Note:You cannot draw diagonal lines.To edit lines on a report1. Right-click the line you want

2. Use the pencil cursor to draw the box where desired.To edit boxes on a report1. Right-click the box you want to format to bring up the shortcut men

EndStartBetween these sec-tionsIn the bottom section, orif that section is sup-pressed, at the top ofthe next visible section.In the top section, or i

EndStartBetween these sec-tionsIn the first instance ofthe GH3 section, or ifthat section is sup-pressed, at the top ofthe next visibleGH/D/GF section

HTML interpretation tag “<a></a>” is now supportedWhen you add a hyperlink to your report, you can now use the “<a></a> ”tag t

EndStartBetween these sec-tionsIn the GF2 section thatcorresponds to the GH2section, or if that sectionis suppressed, at the topof the next visibleGH/

EndStartBetween these sec-tionsIn the end section, or ifthat section is sup-pressed, at the top ofthe next visibleGH/D/GF section. Ifthese sections ar

6. Once the appropriate shape is created, click OK to save your changes.Scaling, cropping, and sizing objectsWhen you create or modify a Crystal repor

To reduce the size of the object to a half inch square, reset the Sizesettings to a half inch each.Using conventional accounting formatsAs a way of su

• In the Negatives list, how the negative values appear on your reportare determined by the Windows locale settings. The negative valuesare represente

modifying an object in the repository, see Modifying objects in therepository.3. On the Common tab of the Format Editor, select the Repeat onHorizonta

Using white space between rowsThe height of a section in relation to the objects within it affects the amountof white space that appears between rows

Note:White space can also be added to a section by right-clicking the shaded areato the left of the section and selecting Insert Line from the shortcu

To delete white space by suppressing a section1. On the Report menu, click Section Expert.The Section Expert appears.Tip:Another way to do this is to

Crystal Reports makes it easy to apply conditional formatting in these andhundreds of other situations.With absolute formatting, you follow the "

Installing Crystal Reports2008 SP33

Conditional attribute propertiesA conditional attribute property tests to see which of two or more conditionsis met. The program then applies the form

Crystal syntax exampleIf Condition A ThencrRedElseDefaultAttributeBasic syntax exampleIf Condition A Thenformula = crRedElseformula = DefaultAttribute

formula = crBlueEnd IfUse a multi-condition If-Then-Else formula for this kind of conditionalformatting.Changing fonts conditionallyFor memo or string

Margins based on page numberThe following formula checks whether a page number is even or odd andsets the margins accordingly: if the page is an even

Note:You cannot conditionally change the X position of line or box objects.To conditionally change the X position of an object1. Right-click the field

2. On the Report menu, click Section Expert.Tip:Another way to do this is to click the Section Expert button on theExpert Tools toolbar.The Section Ex

section conditionally, using the same formula that was used forsuppressing the Page Footer section.Using the Highlighting ExpertThe Highlighting Exper

create your own conditional formatting formulas with the Formula Workshop(accessible through the Format Editor dialog box). For complete details, seeU

3. In the Item editor area, click the Value of list and select the field that youwant to base your condition on.The field chosen here is the field upo

Note:If the field selected in the "Value of" list is not numeric, the text box turnsinto a list of available values, from which you must sel

Installation overviewThe Crystal Reports Installation Wizard works with Microsoft Windows Installerto guide you through the installation process. The

Note:A formula has priority over another formula when it is higher in the Itemslist area.4. Click OK.5. Click the Preview tab or refresh the report to

Note:You can only undo or redo actions in order from the most recent backward.You cannot undo an action without undoing more recent actions.Using the

Target object or fieldSource object or fieldReport field in a cross-tabReport field in a cross-tabTemplate fieldTemplate fieldText object (not in a cr

Note:• The Format Painter button is not available until you select an objector field.• Click the button a second time, or press ESC, to exit the Forma

This may cause the new data to overlap existing fields if there is notenough space between fields.• Barcodes can be applied to number and string field

• To display the barcode in a different color.Related Topics• Adding a barcode• Removing a barcodeTo change the appearance of a barcode1. Right-click

7. Change the font and size to the desired size, and then click OK .8. On the report, resize the field to a an appropriate length.366 Crystal Reports

Charting14

Charting conceptsCharting overviewCrystal Reports enables you to include sophisticated, colorful charts in yourreports. You can use charts any time yo

Chart layoutsThe Chart Expert provides four layouts that correspond to certain sets ofdata.You can create charts with any of the following layouts, an

Installation requirementsMinimum installation requirementsWindows Vista and Vista SP1Windows XP Professional SP2Windows 2003 ServerWindows 2008 server

OLAPUse the OLAP layout to chart on an OLAP grid. An OLAP chart uses thefields in the OLAP grid for its condition and summary fields.Note:Your report

AreaAn area chart displays data as areas filled with color or patterns. This typeof chart is best suited for showing data for a limited number of grou

pool of customer information. Viewing all of this data on an XY Scatter chartwould allow you to speculate as to why certain products were selling bett

GanttA Gantt chart is a horizontal bar chart often used to provide a graphicalillustration of a schedule. The horizontal axis shows a time span, while

Drill-down with chartsNot only is charting a means of presenting data—it is also an analysis tool.Move your cursor over a section of the group chart o

Charting on details or formula fields (Advancedlayout)The Advanced layout allows you to create a chart based on specific values.Since charts are a goo

3. On the Type tab, in the Chart type list, select a chart type.Click the chart subtype that best illustrates your data. See Chart types.4. Click the

8. If you do not want Crystal Reports to automatically summarize the chartvalues for a formula field, select the Don't summarize check box.9. If

default chart, and then click Chart Expert. The Chart Expert dialog boxappears.3. On the Type tab, in the Chart type list, select a chart type.Click t

Tip:Another way to create a chart is to click the Insert Chart button onthe Insert Tools toolbar.3. Drag the frame to the desired position in the Repo

registry entries and may update some system files that require Administratorrights.Close all currently running programs and stop as many services as p

Charting on an OLAP cube (OLAP layout)The OLAP layout lets you chart on an OLAP grid. In order to create an OLAPchart, you must first have an OLAP gri

9. If the Axes and Options tabs appear, you can customize some of thechart's properties, such as the scaling of the axes, the legend, and thedata

Editing charts using the Chart Options menu itemsSome of the editing options available in the Chart Expert are also availabledirectly from the Chart O

The options on the Custom tab represent directory locations under\Program Files\BusinessObjects\Common\4.0\ChartSupport\Templates where customchart fi

To choose a viewing angle for a 3D chart• Right-click your chart, then select 3D Viewing Angle from the menu.Using the zooming features with bar and l

Auto-arranging chartsIf you move or resize chart objects on the Preview tab, select theAuto-Arrange Chart feature to reset the chart.To auto-arrange a

Note:• The option to apply conditional formatting is not available for everychart type.• An area chart must have two "On change of" values f

To underlay a chart1.Create your chart and place it in the Report Header section. See Creatingcharts.2. On the Report menu, click Section Expert.Tip:A

388 Crystal Reports 2008 SP3 User's GuideCharting14Working with charts

Mapping15

11. Choose the type of installation that you want to perform:• Typical installs the most common application features.• Custom enables you to choose th

Mapping conceptsMapping overviewWith Crystal Reports, you can include geographic maps in reports. Mapshelp you analyze report data and identify trends

Cross-TabUse the Cross-Tab layout when mapping on a Cross-Tab object. A Cross-Tabmap does not require groups or summary fields.OLAPUse the OLAP layout

very distorted representation of the data. A more useful map would haveranges like 0-5000, 5000-10000, 10000-15000, 15000-20000, and over20000. It is

dots. However, in some coastal states, such as South Carolina, you mightbe able to count the dots on the map, since their dispersal would be fairlywid

pie) of electricity because of the hydropower in that region, while Idaho wouldprobably use a high percentage (a large slice of the pie) of natural ga

Drill-down with mapsNot only is mapping a means of presenting data—it is also an analysis tool.Move your cursor over a section of the map on the Previ

a map showing last year's sales for the countries, the Last Year's Salesfield would be the value.To map on a details field1. On the Insert m

3. In the Placement area, in the Place map list, specify how often your mapappears on the report, then click Header or Footer to specify where toplace

9. In the Options area, apply formatting options to your map.10. Click the Text tab.11. In the Map title field, enter a title for your map.12. In the

Mapping on group fields (Group layout)To map on a group, you can use the Group layout, in which you show asummary (such as Last Year's Sales) on

Running a silent installation...45Setting custom banners...

service packs each time you open Crystal Reports; if you disable it, youcannot activate it later.14. Click Next to begin copying files to your local d

11. If you click Full legend, click Made by map to have Crystal Reportscreate a legend title based on your map, or click Specify to enter yourown lege

6. In the Map on list, click a summary field to supply the numeric data foryour map.7. Click the Type tab.8.Click the map type that best illustrates y

Tip:Another way to do this is to click the Insert Map button on the InsertTools toolbar.The Map Expert dialog box appears.2. On the Data tab, in the L

12. Click OK.Your map is placed in the Header or Footer section of the report,depending on your selection in Step 3.Working with mapsOnce you have cre

Changing the map typeYou can change the map type and set the properties for that map directlyfrom the menu that appears when you right-click a map on

To change map layers1. On the Preview tab, right-click the map to bring up the shortcut menu.2. On the shortcut menu, and choose Layers.The Layer Cont

Use this dialog box to set the default display mode and zoom range (theminimum and maximum possible magnification) for the layer in question,then clic

Changing the geographic mapIf you prefer to have your data values presented with a different geographicmap, specify your changes using the Change Map

Note:• If a map occurs once for each instance of a group, any panning or zoomingsettings you specify are instance-specific. In other words, if you zoo

You have the option of hiding or showing the Map Navigator.To hide the Map Navigator1. On the Preview tab, right-click the map to bring up the shortcu

Note:Restricting access to this folder lets you stay within your license limit.Installing Crystal Reports from a networkIf your network administrator

Formatting MapsChanging the border of a map1. On the Design or Preview tab, right-click the map to bring up the shortcutmenu.2. On the shortcut menu,

4. Click OK.Crystal Reports returns you to the report. Your map will now underlay thesections below it.5. If necessary, move or resize the map.Crystal

412 Crystal Reports 2008 SP3 User's GuideMapping15Working with maps

OLE16

OLE overviewObject Linking and Embedding (OLE) enables you to insert objects (OLEobjects) into a report from other applications (OLE server applicatio

An OLE container application is one that can contain and process OLEobjects created in other applications (such as Paint or Excel). CrystalReports is

in the server document is changing and you want the object in your reportto be updated when you open the report.General OLE considerationsThere are se

To copy and paste OLE objectsThis procedure assumes that you have Microsoft Excel or anotherspreadsheet program on your computer that is an OLE server

• If the object was created from an existing file, the data from that file (oran icon) is displayed in the report. This data can be edited bydouble-cl

• If the object is linked, the Edit menu displays commands for that type oflinked object.Commands on the context menus change in a similar fashion.The

Tip:You may need to contact your Administrator for the product activationkeycode.9. Click Next.10. In the Choose Language Packs dialog box, select the

Inserting a static OLE object1. Open or create a report that you want to insert a static OLE object in.2. On the Insert menu, click Picture.Tip:Anothe

• When you schedule a report that contains a dynamic static OLE objectin BusinessObjects Enterprise, the instances that are created contain theversion

2. Select Paintbrush Picture in the Object Type list, and click OK.You have just converted a static OLE object to a modifiable OLE object.Note:This op

The dialog box changes, allowing you to either type in an object name orbrowse.4. Click Browse and choose a bitmap file (BMP).5. Click Open to return

When you have a linked object and you break the link using the Links dialogbox, all connections to the original data in the server document are broken

Integrating Shockwave Flash (SWF)objectsFlash technology is a popular way to add animation to web pages. Flashfiles can be viewed in a standalone Flas

The only format that will display SWF objects in exported reports is PDF.However, if the object contains data binding to the report, the defaultimage

Note:In the "Design" view, if you choose to insert an SWF that contains anembedded movie or sound, you will hear the sound, but you will not

A user interface that is like the cross-tab expert allows you to map rows,columns, and summarized fields to Xcelsius variables.• Bind to an external c

Cross-Tab Objects17

The default location for Crystal Reports is C:\ProgramFiles\Business Objects\14. Click Next to begin copying files to your local drive.Customizing you

What is a Cross-Tab object?A Cross-Tab object is a grid that returns values based on the criteria youspecify. Data is presented in compact rows and co

• At the end of each row is a total for that row. In the example above, thistotal represents a single product sold in all countries. At the end of the

The first way of looking at the data is in the most basic of all reports, acolumnar report with no grouping or sorting.Report of order data - no sorti

The next logical step is to group the data in some way. You can group it byregion, or by product line. The following section shows a look at both of t

Report of order data - grouped by productThis report groups the data by product. Each group displays all the ordersfor a specific product. At first it

regions in each product group, then it seems to make sense to combine thetwo. Doing that, you group first by Region and then by Product.Each group con

In this Cross-Tab:• Product names make up the row headings.• Regions make up the column headings.• The value at each row/column intersection is the su

Creating a Cross-Tab reportThis section provides you with the steps to create a Cross-Tab object in anew report and how to add a Cross-Tab object to a

3. On the Insert menu, click Cross-Tab.Tip:Another way to do this is to click the Insert Cross-Tab button on theInsert Tools toolbar.An object frame a

9. Finally, drag Last Year's Sales to the Insert Summary cells of theCross-Tab, and click to release it.Tip:Notice that all of the cells change c

• A red X means that the feature or subfeature is either unavailable or willnot be installed.Crystal Reports uses an "install on-demand" tec

Modifying the links1. In this example, check to make sure that the Customer table is linked tothe Product table via the Orders and Orders Detail table

For this example choose Region.2. Click the > arrow.The field is added to the Filter Fields area and the filter types list appearsbelow it.3. From

4. Select the summary operation you want to perform on Order Amountfrom the list beneath the Summary Fields area.5. Click Next.The Chart screen appear

This example uses the Group By Intervals.rpt included in the FeatureExamples directory.2. Click the Design tab.3. On the Insert menu, click Cross-Tab.

• Click Group Options.• Select in specified order from the drop-down list.• Type North America for the name of the Named Group.• Click New.• Choose is

Finishing the Cross-Tab1. Click OK.2. On the Report menu, click Refresh Report Data.The updated report appears.Working with Cross-TabsThis section des

Abbreviating large summarized fieldsBecause the values in a Cross-Tab's summarized fields are often very large,Crystal Reports lets you abbreviat

6.Click Check to identify any errors in the formula.7. Fix any syntax errors the Formula Checker identifies.8. When the formula has the correct syntax

You return to the Format Editor dialog box.7. Click OK to return to your Cross-Tab.Your customized row and column names appear when the conditions you

2.Click Record Sort Expert and sort your Cross-Tab by the field youdesignated as a Row in the Cross-Tab Expert.3. Create a second sort on the field yo

Note:Subfeatures are listed below each feature. A subfeature can have a differenttype of installation than its parent feature.Running a silent install

Formatting Cross-TabsCrystal Reports has powerful formatting capabilities that can be applied toCross-Tabs. The following sections describe some key p

2. Click the Customize Style tab.3. Click the row (in the Rows area) or column (in the Columns area), andselect a color from the Background Color drop

• Empty rows and columns.• Row and column grand totals.• Subtotals and their labels.To suppress empty rows and columns1. Right-click the blank top-lef

2. Click the Customize Style tab.3. Click the field whose subtotal you want to suppress.The Suppress Subtotal and the Suppress Label check boxes becom

Calculated MembersYou can add rows or columns to your Cross-Tab by inserting a CalculatedMember. These rows or columns can contain custom calculations

A row or column that displays the results of the selected calculation appearsin your Cross-Tab .To add a blank row or column to your Cross-Tab1. In yo

The pre-defined formulas are as follows:• Sum of• Difference of• Product of• Quotient ofCrystal Reports is equipped with a variety of functions to hel

Value FormulasIf you want to reference a Calculated Member in a formula, you must assigna value to it. Value Formulas assign values to Calculated Memb

3. In the Formula Workshop, enter your desired formula.For example, the following formula inserts a Calculated Member afterthe country field for Canad

To change the processing order of Embedded SummariesIf you have multiple Embedded Summaries in your Cross-Tab, the orderin which they are calculated c

• <%keycode%> Replace this variable with a valid product activationkeycode.The example uses the most common parameters. You can choose anynumber

460 Crystal Reports 2008 SP3 User's GuideCross-Tab Objects17Advanced Cross-Tab features

Building Queries18

Connecting to a universeYou access the Business Objects Query Panel in Crystal Reports throughthe Database Expert. Once you have selected Universes fr

Defining the data selection for a queryYou build queries in the Query Panel by using objects in a Business Objectsuniverse. The objects in the univers

Objects that appear in the Result Objects pane become fields that youreport on in your Crystal report.3. Repeat the previous step for each object that

Quick reference to objectsAn object is a named component that maps to data or a derivation of datain the database. For example, an object may represen

DescriptionExamplesObjectThis object provides de-scriptive data about adimension. A detail isalways attached to thedimension for which itprovides addi

To edit an existing query1. In Crystal Reports, open a report created with a universe as a data source,go to the Database menu, and click Query Panel.

To view the SQL after you have created a query• In Crystal Reports, open a report created with a universe as a data source,go to the Database menu, an

For step-by-step instructions on selecting objects to build a query, seeDefining the data selection for a query.2. Drag a predefined filter to the Que

DescriptionInstallation parameterUse this to specify the language ver-sion code for the install.•For English, enter EN.•For French, enter FR.•For Germ

Tip:Prompts allow multiple users view a single report but specify a differentsub-set of the database information. Prompts also reduce the time it take

Combining query filters and promptsYou can apply multiple filters and prompts on a single query.To combine filters and/or prompts1. Create each query

SelectExampleRetrieve this dataAndCustomers who orderedsupplies in Q1 and inQ2 (the data you re-trieve will include: cus-tomers who placed or-ders in

Filter createdSelectExampleValues to re-trieve<Quarter> Differ-ent from Q4Different fromRetrieve data forall quarters ex-cept Q4.Values differen

Filter createdSelectExampleValues to re-trieve<Weeks> Not Be-tween 25 and 36Not betweenRetrieve all theweeks of theyear, except forweeks 25 thro

Filter createdSelectExampleValues to re-trieve<DOB> Matchespattern, '72'Matches patternRetrieve cus-tomers whosedate of birth is1972.V

The Filter Editor appears.2. Change the definition of the filter in the Filter Editor.For information on how to define filters, see Creating query fil

• They allow you to restrict the values returned by the subquery with aWHERE clause.Subqueries allow you to pose complex questions that are difficult

Note:For more information on query filter operators and values, see Queryfilters and prompts.5. Click Add a subquery to add an additional subquery to

DescriptionParameterThe operator that specifies the rela-tionship between the Filter object andthe Filter By object.Because of database restrictions y

DescriptionInstallation parameterSpecifies the machine and directorywhere you want to create the CrystalReports sub-directory, CrystalReports and inst

What is a database ranking?Database rankings allow you to answer questions like "what are the top threecustomers, based on the revenue they gener

To create a database ranking1. Add the objects that you want to appear in your query to the Result Objectspane of the Query Panel.2. Select the object

DescriptionParameterRanking order.Top - ranks in descending order.Bottom - ranks in ascending order.Top/BottomThe number of records to return inthe ra

DescriptionParameterAdditional restriction on the valuesreturned in the ranking that appearsbelow the other parameters. For ex-ample, a ranking of reg

484 Crystal Reports 2008 SP3 User's GuideBuilding Queries18Filtering data using subqueries and database ranking

Creating and UpdatingOLAP Reports19

OLAP reporting with Crystal ReportsAlthough relational databases such as SQL servers and PC databases arethe most common sources of data, Online Analy

grid. Or instead of viewing multiple dimensions within one OLAP grid, createmultiple OLAP grids within the same report.Note:When Crystal Reports displ

1. Click Select Cube.The OLAP Connection Browser appears.2. Browse your OLAP server for the cube you want to connect to.If your server isn't in t

4. Click the Up and Down arrow buttons associated with the Rows andColumns areas to arrange the order of the dimensions.Note:If you accidentally add a

DescriptionInstallation parameterSpecifies a list of features, delimitedby commas, that you want to install.For example, ADDLOCAL=ALLNote:When using a

8. Select a dimension in either the Rows or the Columns areas and clickCreate/Edit Parameter to create a parameter for use with the dimension.The Crea

To specify a slice1. To determine the member that is going to be used as the slice, select adimension in the Slice list and click Select Slice, or dou

Applying a predefined style1. Select a predefined style for the grid from the list.Tip:If you do not want to use a predefined style, click Next.A prev

Inserting a chart1. Select the kind of chart you want to add to your report from the optionsshown on the Chart screen.Tip:If you do not want to insert

• Removing dimensions contained in the report that are not in the cube.• Removing fields referred to explicitly in the design of the report, such asa

that your data remains synchronized. The program can change the locationautomatically, or you can change it manually in the Set Datasource Locationdia

• Use the Formula Workshop to write your own conditional formattingformulas with Crystal or Basic syntax. The Formula Workshop gives youmaximum contro

2. In the Group Options area, select the color from the Background Colorlist.3. Click OK if you have finished customizing your grid.Creating an alias

Formatting grid linesIn the Format Grid Lines dialog box, you can define whether the lines showor not, as well as the color, style, and width.1. On th

Search the online help for "Using the OLAP Worksheet" to learn more aboutthe functionality available on the Cube View tab.To show or hide di

Determining printing area characteristics...95Developing a prototype on paper...

installer then records the installation options you select as you install CrystalReports, and saves the file to disk.The created INI file can be read

To change the display format for member names1. Right-click the dimension name.2. On the shortcut menu, point to Display Members Using, and then click

2. Right-click the dimension and, on the shortcut menu, click ReorderDisplayed Members.Note:You cannot reorder members for dimensions that you have cr

In this case, the OLAP grid respects the parent/child relationships betweengrid members and sorts the data values accordingly. (Frozen Goods precedesB

Tip:To locate a sorted row or column, move the mouse pointer over the OLAPgrid. When you reach a sorted member, the pointer turns into a double-arrow.

You can filter data by "Actual values," or by selecting "Top/bottom n" or"Top/bottom n%."4. Use the Filter Definition op

Printing, Exporting, andViewing Reports20

Distributing reportsCrystal Reports enables you to distribute your report using a variety ofmethods.Printing a report1. On the File menu, select Print

The Find Printers dialog box appears. Use this dialog box to select yourfax driver.3. Click OK.You are returned to the Print dialog box.4. Choose the

between these attributes and other objects. Depending on the format youchoose, it may not be possible for the program to preserve all layout andformat

MHTMLMHTML export format can be used to send HTML files in email messages.It can also be used to save a web page with all its pictures, applets, and s

DescriptionInstallation parameterTo be used with the -r parameter.Creates a silent install log file at thepath specified by filepath.If this parameter

format is intended for use in applications such as fill-out forms where thespace for entering text is reserved as empty text objects.Almost all of the

Report DefinitionThe Report Definition format exports your report to a text file that containsa brief description of the report's design view. Th

report's layout. Text format assumes that a font of a constant dimension isused throughout the export. The Characters Per Inch (CPI) option speci

• When exporting in ODBC format, Application and Disk file are the same.• You will need to specify a file path if you are exporting in HTML format.• T

The program exports the report and opens it in the appropriate application.In this example, Microsoft Excel opens with the exported data.Exporting to

Exporting to a Microsoft Exchange folderCrystal Reports enables you to export a report file to a Microsoft Exchangefolder. You select the folder, and

10. When the Select a Folder dialog box appears, select the folder in theprofile in which you want the report to appear, and click OK.The report is ex

The Select Database dialog box appears.8. Double-click the Lotus Domino server you would like to export your reportto.The file name defaults.9. Select

The Number and Date Format Settings dialog box appears.6. Select the check boxes as required, then click OK.7. Enter password information as required,

Tip:Click Cancel Exporting to cancel the export process.Exporting to HTMLBy exporting reports in HTML format, Crystal Reports provides you with anew o

DescriptionImage filenameThe Crystal Reports splash screenthat is shown after installation.This must be placed in the win32x86folder.splash.bmpThe ima

sections and set the top and bottom page margins to zero in the PageSetup dialog box.5. Select an existing directory, or create a new directory for yo

To export to an ODBC data source1. With the report you want exported active, go to the File menu, point toExport, and then click Export Report.Tip:Ano

• Are running Windows 2000 (or higher) or have Office 2000 (or higher)installed.• Have access to a web server that is configured to support Web Folder

Workbench. For more information about publishing through the Workbench,see The Workbench.When you choose the Enterprise option in the Open dialog box,

5. Click the Authentication list to select the appropriate authentication type.Enterprise authentication requires a user name and password that isreco

Enterprise folder is the same as publishing the report to BusinessObjectsEnterprise.To save a report to an Enterprise folder1. On the File menu, click

What are Report Parts?Report objects displayed by themselves in a viewer—without the rest of thereport page—are referred to as Report Parts. More prec

Report Parts use this navigation functionality when linking between ReportPart objects. The key difference between Report Part navigation and regular(

See The Report Part Drilldown option for information about how to use theseoptions.When you select the Another Report Object option, the "Hyperli

You cannot select the following report object types as destinations:• Objects inside the Page Header or Page Footer.• Lines or boxes.• Subreports or a

Quick Start4

The Report Part Drilldown optionThe Report Part Drilldown option lets you define a hyperlink so that the ReportPart Viewer can emulate the drill-down

Include another object from the same section of the report by entering asemi-colon (;) and typing the name of the object.5. Click OK.To create a Repor

The Available Fields area shows only the sections and report objects youcan select for drill down. In general, these objects include field objects,cha

Tip:Use the Report Explorer to quickly identify the default namesassigned to each of your report objects. To open the Report Explorer,click its button

Enterprise, or it must be part of a stand-alone Report Application Serverenvironment. The Report Part Viewer displays only the objects you specify.Lik

Your source report is the report you copy an object from; the target reportis the report you add the hyperlink information to.When you view your targe

The Paste Link button includes a list of options that you can use whenpasting a link to the destination object. Depending on the type of link youare c

Use this option to paste a link that includes a generic data context(that is, a data context that does not point to a specific record). Usethis option

•For more information, see Data context formats.8. Click OK.You have established a hyperlink from your home object to a destinationobject or objects.

data context is /Country[Australia]/ChildIndex[5], the ContextReport Part data context is "/"+{Customer.Country}.Cross-tabsIf the object you

Learning how to use Crystal ReportsYou can teach yourself how to use Crystal Reports by choosing from themethods available in this section:• You can s

• For cross-tab objects, you can use navigation on cells, columns, or rows.To define the data context, use the GridRowColumnValue formattingfunction.

Report Part DrilldownAnother Report ObjectThe object must be in the same re-port.The object can be in a different reportthat is managed in BusinessObj

Creating an Enterprise HyperlinkYou can create hyperlinks from a report to enterprise documents such asCrystal Reports, Webi and other documents.Note:

Using smart tagsCrystal Reports lets you take advantage of smart tags in Office XP. Whenyou paste a chart, a text object, or a field object into an Of

To use smart tags with a Crystal Reports object1. Open the Crystal report that contains the object you want to copy to anOffice XP application.2. On t

Report Alerts21

About Report AlertsReport Alerts are custom messages created in Crystal Reports that appearwhen certain conditions are met by data in a report. Report

2. Click New.The Create Alert dialog box appears.3. Enter a name for your new alert in the Name box.4. Enter your alert message in the Message box.The

The Formula Workshop appears.9. Enter your alert condition formula.Alert formulas can be based on recurring records or on summary fields,but cannot be

Only enabled and disabled alerts appear in the Create Alerts dialog box.If an alert is triggered, it is seen in the Report Alerts dialog box.Editing R

alternative ways of designing a database, this design was selected to keepthe tutorials and examples focused on reporting, not on data manipulation.Re

Deleting Report Alerts1. On the Report menu, point to Alerts and then click Create or ModifyAlerts.2. In the Create Alerts dialog box, select the aler

2. Select the alert whose records you want to see.3. Click View Records.A new report tab is opened showing the report record(s) that triggeredthe aler

These functions behave the same as alerts created in the Create Alertsdialog box:• IsAlertTriggered("AlertName") is true only for the record

Using Formulas22

Formulas overviewIn many cases, the data needed for a report already exists in database tablefields. For example, to prepare an order list you would p

Pulling out a portion, or portions, of a text stringTo extract the first letter of the customer name:Crystal syntax example:{Customer.Customer Name} [

Formula componentsCreating a formula in Crystal Reports is like creating one in any spreadsheetapplication. You can use any of the following component

Control StructuresExample: "If" and "Select", "For" loopsGroup field valuesExample: Average (fld, condFld), Sum (fld, co

If you are already comfortable with Crystal syntax, you can continue to useit, and benefit from the new functions, operators and control structuresins

Note:After you have completed the configuration outlined in the Developer's Guide,you must return to Crystal Reports, point to the File menu, and

Cross-TabThe Cross-Tab Report Creation Wizard guides you through the creation ofa report in which your data is displayed as a cross-tab object. Two sp

Search formulasSearch formulas help you locate data in your report. Like selection formulas,you normally do not enter these formulas directly, but ins

You can open the Formula Workshop by itself before you begin addingspecific kinds of formulas.To access the Formula Workshop1. On the Report menu, cli

Formula Workshop buttonsThe main toolbar in the Formula Workshop is made up of three smallertoolbars. Each of these toolbars contains a set of buttons

Opens the Formula Expert. Use theFormula Expert to help you create aformula based on a custom function.Note:This button is not available when creat-in

Adds the selected repository customfunction to the report.The buttons for the Expressions Editor Toolbar of the Formula Workshopperform the following

Inserts a bookmark at the beginningof a selected formula line. Click thebutton again to remove the book-mark.Jumps to the next bookmark.Jumps to the p

Allows you to select Exceptions ForNull or Default Values For Nulls as amethod for dealing with null valuesin your data.Comments out the highlighted s

Description of contentsWindowFunctions are prebuilt proceduresthat return values. They perform cal-culations such as average, sum,count, sin, trim, an

Setting the default syntaxWhen you open the Formula Editor, Crystal syntax appears as the syntaxdefault. If you want to change the syntax default, sel

Formula Editor Key ControlsThe Formula Editor accepts the following key controls:Action PerformedKeyboard CombinationOpens Browse dialog box for high-

You begin by learning the basic concepts: selecting a database, placingsome fields on the report, and then selecting specific records to be included.Y

Action PerformedKeyboard CombinationCopies.Ctrl+CGoes to the end of the last line of theformula.Ctrl+EndOpens the Find dialog box (same asclicking the

Action PerformedKeyboard CombinationChanges focus to next control box(reverse order of Ctrl-Tab).Ctrl+Shift+TabChanges focus to next control box.Ctrl+

Creating and modifying formulasCreating a formula and inserting it into a report1. On the View menu, click Field Explorer.2.In the Field Explorer dial

Creating a formula in the Formula ExpertThe Formula Expert is a component of the Formula Workshop. Use theFormula Expert to create and modify formulas

5. In the Custom Function area, choose the custom function you want tobase your formula on.You can choose a Report Custom Function (a custom function

Editing formulas1. On the View menu, click Field Explorer.The Field Explorer dialog box appears.2. Right-click the formula you want to edit and choose

Copying formulasTo copy an existing formulaCrystal Reports lets you copy an existing formula and then modify it to createa new formula.1. Choose View

Windows places a copy of the selected text on the Clipboard.6. Return to Crystal Reports, choose View from the main menu and selectField Explorer.The

Key points for editing a copy of a formulaWhen making changes, use the following points as a guide:• All fields, formulas, and group fields referenced

Note:You cannot delete the specification without deleting all working copies of theformula.Removing the working formula from your report1. Right-click

The New Reports area of the Start Page contains a number of wizardsto guide you through the creation of specific types of reports. Since youwill be le

Debugging evaluation time errorsWhen the Formula Workshop is being displayed as a result of an evaluationtime error, the Workshop Tree will contain a

About this tutorial• This tutorial uses the Xtreme.mdb sample database.• This tutorial uses Crystal syntax.• The following formula is the formula you

4.Click Check to test for errors. You will receive the following errormessage:The ] is missing.5. Correct the formula by inserting the missing "

4. Correct the formula by replacing the comma (,) in the field name with aperiod (.).5. Click Check again. The formula should now be error-free.6. Pla

Formula41. Create a new formula called Formula4.2. Type the following in the Formula text box of the Formula Editor:If ToText({customer.CUSTOMER ID})

Else"FALSE"3. Place the formula to the right of the @Formula4 field.You should see "TRUE" next to each customer whose name begins

586 Crystal Reports 2008 SP3 User's GuideUsing Formulas22Debugging formulas

Parameter Fields andPrompts23

Parameter and prompt overviewParameters are Crystal Reports fields that you can use in a Crystal Reportsformula. As a formula component, a parameter m

Example: Display customers with sales over XXXXX.• Date: Requires an answer in a date format.Example: Enter the start and end dates of the quarter.• D

• If you wish to see database and server properties after you haveselected a database, right-click the database node in the DatabaseExpert and select

Prompt considerationsThere are a number of things to keep in mind when working with prompts:• Prompts can be static or dynamic. As well, a dynamic pro

Changes made to the value of a data parameter require a report refresh tofetch new data from the database. For example, a report will be refreshedwhen

• If you change the parameter from optional to mandatory, the HasValue()function is not automatically removed from your formula.Handling a parameter w

Available when Crystalreports are publishedto BusinessObjectsEnterprise?Available when Crystalreports are storedoutside of BusinessOb-jects Enterprise

Available when Crystalreports are publishedto BusinessObjectsEnterprise?Available when Crystalreports are storedoutside of BusinessOb-jects Enterprise

• ActiveX• .NET Winform• .NET Webform• Java, COM, and JSF DHTML page viewers• Java and COM Advanced DHTML Viewer• The InfoView and Central Management

For example, a list of countries could be a dynamic prompt, while ahierarchical list of countries, regions, and cities could be a dynamic promptwith a

Every report that you store in BusinessObjects Enterprise uses managedlist-of-values objects. All managed list-of-values objects are based on aBusines

Managed list ofvaluesUnmanaged list of valuesFeatureBusiness ViewsCommand ob-jectsReport fieldsYesIf the BusinessView containsparameters, thevalue(s)

Managed list ofvaluesUnmanaged list of valuesFeatureBusiness ViewsCommand ob-jectsReport fieldsYesThe schedulingcapability comesfrom the Busi-ness Vie

Adding summary information to the report...135Exploring reports and working with multiple reports...

Note:Depending on how it appeared when you last used Crystal Reports, theField Explorer dialog box might be docked or in floating mode. For moreinform

Managed list ofvaluesUnmanaged list of valuesDescriptionBusiness ViewsCommand ob-jectsReport fieldsWell suited.You can define fil-tering within thesou

Managed list ofvaluesUnmanaged list of valuesDescriptionBusiness ViewsCommand ob-jectsReport fieldsWell suited.Partially sched-uled lists of val-ues e

Creating a parameter with a static promptA static prompt is one that always contains the same values. For example,if your parameter prompts for a coun

Note:When creating a parameter whose Type is either Date or DateTime, youcan change the date format to suit your needs. For details, see Changingyour

9. Click OK.10. Return to the Field Explorer dialog box, and drag the Country parameterinto your report.The "Enter prompt values" dialog box

12. Click OK.To incorporate the parameter into the record selectionfilter1. On the Report menu, click Select Expert.Tip:Another way to do this is to c

5. Click OK.The report appears with the information for Brazil.With parameter fields, you can create a single report that can be customizedquickly to

Creating a parameter with a dynamicpromptThe values of a dynamic prompt are retrieved from the data source and arenot stored within the report. For ex

Note:If your report already contains a list of values, or if you want to pick a listof values from your repository, you can select Existing and choose

Note:If you don't want to see the parameter field you dropped in your report,place it in a section you can suppress, such as a report header or f

This dialog box is set to remain on-screen until you close it. All the tablesavailable for use are listed in this box.You will now start placing objec

To create a parameter with a cascading list of values1. Open the sample report called Group.rpt.Sample reports are located on the Business Objects Tec

The program automatically expands the Value list. You use this area todefine the fields that make up your cascading list of values. This exampleuses a

14. Click OK.15. Return to the Field Explorer dialog box, and drag the Supplier Cityparameter into your report.Note:If you don't want to see the

In the Create New Parameter and Edit Parameter dialog boxes, you canspecify parameters to display on the Parameter Panel via the Show on(Viewer) Panel

scheduling it in the Business View Manager. For more information, see theBusiness Views Administrator's Guide.You can add lists of values to Busi

This example uses SupplierCity.4. In the List of Values area, click Dynamic.5. Enter prompting text for your prompt group in the Prompt Group Textfiel

Using separate value and description fieldsIt is common in relational databases to make use of code fields that representvalues. These codes are often

Using command objects as list-of-values data sourcesCrystal Reports supports command objects as data sources for unmanagedlists of values. To use this

Long lists of valuesBecause the number of items in a list of values (LOV) can be very large,numbering in the hundreds or thousands, Crystal Reports ha

Where {number} in the above example is the number of items in the batch.Best practices for promptingUnmanaged reportsUnmanaged reports are reports tha

If you move the field too far to the left, the Arrow cursor turns into a Stopcursor, indicating that you cannot drag the field that far. Keep in mind

To maximize performance and manageability of your managed reports, thefollowing practices are recommended:• Define your list-of-values objects in Busi

• Create a new report object from within the Central Management Consoleportal.In all of these cases, these actions are carried out on the prompt objec

To delete a parameter that is not used in a formula1. On the View menu, click Field Explorer.The Field Explorer appears.2. Expand the Parameter Fields

To delete a parameter that is used in a formula1. On the View menu, click Field Explorer.The Field Explorer appears.2. In the Formula Fields folder, s

Refreshing report dataWhen you refresh data from the Preview tab, the Refresh Report Data dialogbox appears.Select the "Use current parameter val

• Enter DateTime values using the following format: Date (Year, Month,Day), Time (Hour, Minutes, Seconds AM/PM). For example, Date (1997,5, 21), Time

6. Add the values from the field you selected by clicking Actions andchoosing Append all database values.You can also import values, or you can create

2. Create the formula and use the parameter field in place of the fixed valueyou would normally use.For example, to be prompted for all the customers

The Create New Parameter dialog box appears.3. Type a name for the parameter field in the Name field.4. Select String from the Type list.5. To specify

a report that uses this parameter for record selection will display allrecords with values between 5 and 10. This also works for stringparameter field

• The font and style (Bold, Underline, and so on) used in displaying thecharacters or Xs indicate the font and style selected for the characters inthe

6. Create a formula using the parameter field as you would any constantvalue. For example, rather than creating a formula that hard-codes thecountry n

Now the parameter field will only accept single-character values. The fieldwill accept "C" as a value, but not "City."4. In the Pr

4. For a string parameter field, you can choose to enter an edit mask in theEdit mask field, rather than specifying a range.An edit mask can be any of

• "\" (causes the subsequent character to be displayed as a literal). Forexample, the edit mask "\A" would display a parameter val

• Exported reports retain all recently applied parameter changes.• Sub-report parameters cannot be shown on the Parameter Panel.To create a saved-data

6. Click Actions, and select Append all database values to move all ofthe applicable values to the Values area.This example will enable the user to ch

4. Choose the parameter from the adjacent drop-down list and click OK.If the parameter is being referenced for the first time, the Enter Valuesdialog

Else if {?GroupBy} = 'Country' then{Customer.Country}Else if {?GroupBy} = 'Order' thenToText({Orders.Order ID})Note:Crystal Report

638 Crystal Reports 2008 SP3 User's GuideParameter Fields and Prompts23Advanced parameter features

Subreports24

• Position the cursor inside the object frame and click once. The handlesappear, indicating the object is selected.• Move the cursor away from the obj

What are subreports?A subreport is a report within a report. The process for creating a subreportis similar to the process of creating a regular repor

Unlinked vs. linked subreportsUnlinkedUnlinked subreports are free-standing; their data is not in any way coordinatedwith the data of the primary repo

When you are considering whether to use a subreport or linked tables, youneed to understand the ramifications of each. These issues are discussedfully

• When the subreport is finished, the program locates the second recordit needs in the primary report, prints the customer data, and then passesthis c

Inserting subreports1. On the Insert menu, click Subreport.Tip:Another way to do this is to click the Insert Subreport button on theInsert Tools Toolb

7. If you chose On-demand subreport (similar to a hyperlink), click thesubreport preview tab to see your subreport.This tab is labeled with the name o

To save a subreport as a primary report1. In the Design tab, right-click the subreport and click Save Subreport Asfrom the shortcut menu.The Save As d

To update a specific subreport when opening a main report1. From the Format menu, click Format Subreport.2. In the Format Editor dialog box, click the

To link a subreport to the data in the primary report1. If you are creating a new subreport or importing an existing report as asubreport, from the In

dates, or date/time fields, you must make sure your subreport parameterfield type matches the field type set up in Report Options in the main reportfo

Note:The first time you preview a report, you must click Print Preview on theStandard toolbar to activate the Preview tab. The Preview tab appearsto t

Combining unrelated reports by usingsubreportsAt times, you may wish to combine unrelated reports into a single report. Forexample, you may want to cr

For example, if you want to use three subreports, insert two new ReportFooter sections so that you have a total of three Report Footer sections.4. In

Linking to/from a formula fieldThere are situations in which you may need to link to or from a formula(calculated) field. For example, an employee ID

Linking unindexed tablesWhen using PC (not SQL or ODBC) databases, the link field in the lookupdatabase needs to be indexed in order to create a valid

To create an on-demand subreport1. Place an ordinary subreport in your primary report.2.Click the Format button on the Expert Tools toolbar.The Format

4. Enter your formula in the Formula text box.Crystal syntax formula example:"More Information About " + {Customer.Customer Name}Basic synta

Use the appropriate link fields to link the report and coordinate the data.656 Crystal Reports 2008 SP3 User's GuideSubreports24Showing different

Understanding Databases25

Databases overviewThough there are hundreds of Database Management Systems (DBMS)available, Crystal Reports eliminates many of the differences once it

Often, data in two different tables can be related by a common field. Forexample, a Customers table will have a Customer ID for each customer, andan O

Combining database fields in a text objectInstead of adding the Contact First Name and Contact Last Name fields asseparate objects, you can insert bot

not be difficult, but to find every formula that uses that field could be a difficultand time consuming task.To solve this problem, the Report Designe

You can change an alias at any time using the Database Expert. However,if you have already created formulas in your report using the original aliasnam

command provides a simple way to indicate the new name or location ofdatabase files. In addition, the Set Datasource Location commandautomatically con

AmountCustomerOrder#7911.80SFB Inc.104951956.20La Bomba de Bicicleta105011683.60BG Mountain Inc.1051119766.20Sierra Bicycle Group1054429759.55Mountain

Pointer to Order#Customer10444Allez Distribution10470BG Mountain Inc.10511BG Mountain Inc.10501La Bomba de Bicicleta10488Mountain Toad10568Mountain To

since information in the index is organized according to the customer names,the database engine does not need to continue searching through the indexo

One-to-one relationshipsIn a one-to-one relationship between records in two linked tables, for everyrecord in the primary table there is only one matc

When a one-to-many situation exists between two database tables and theprogram matches up records from the tables, there are a number of factorsthat d

Does your primary report include a record selection formula that setsrange limits on the key (indexed) field in Table A?• Index AIs Table A on the fie

TotalRecordsReadFor eachA readsin BReads AIndex BIndex ASelec-tion For-mulaLinking/Subreport200100(2*100)2YesYesYesLinking67,6002600(26*2600)26NoNoNoS

12. Drag the field to the text object.13. Move the cursor over the text object until the cursor becomes a Drag andDrop cursor. Move the cursor to the

SQL DataTotalRecordsReadFor each Areads in BReads ASelectionFormulaLinking/Subreport2600100 (26*100)26NoLinking200100 (2*100)2YesLinking2600100 (26*10

the database DLL (for PC data) or the server (for SQL data). See RecordSelection.The second half of the selection formula, however, requires processin

• The program passes this merged record (A+B) back to the ReportEngine, which tests it against the entire selection formula.• The program then locates

SQL database considerationsSince indexes are not critical with SQL data, the primary concern with bothlinked tables and subreports is whether or not t

• The number of records read by each subreport remains the sameregardless of whether there was range limit selection on Table A. Eachsubreport will re

program passes down the first condition, retrieves the data set thatsatisfies the condition, and then applies the second condition only to theretrieve

Consideration 3If the fields you are using from Table A are not indexed, but there is anindexed field that you can use in your record selection reques

• Some DBMS applications allow you to convert the field value to anotherdata type in the index. For instance, the field in the table can be numeric,wh

For example, if you have chosen the Credit, Customer, Orders, and OrdersDetails tables from the Xtreme sample database, the links tab shows thetables

Note:When you link fields using joins, no indexed fields are required.The join types are:• Inner join• Left Outer join• Right Outer join• Full Outer j

2. Enter information about your report in the text boxes provided. Be sureto enter the title "Customer List" in the Title text box. This inf

Orders TableCustomer TableCustomer TableOrder AmountCustomer NameCustomer ID19164.30BG Mountain Inc.531683.60BG Mountain Inc.5315716.40Hansen MTB Inc.

every customer who has not placed any orders. These customers appear atthe end of the list with blanks in the fields that would otherwise hold orderin

Orders TableCustomer TableCustomer TableOrder AmountCustomer NameCustomer IDDeely MTB Inc.55Note:Left Outer and Right Outer joins are handled differen

Orders TableOrders TableCustomer TableOrder AmountOrder IDCustomer ID19164.3011531683.60215315716.404571956.20205824580.5016607911.80196219766.2028631

like in an SQL statement, refer to Microsoft ODBC documentation or to thedocumentation for your SQL database.Full Outer joinA Full Outer join is a bid

Orders TableOrders TableCustomer TableOrder AmountOrder IDCustomer ID7911.80196219766.20286312763.9532638233.501464656610320.8725Not EnforcedWhen you

still include the join to TableA because it is enforced. Conversely, selectingonly from TableA with the same join condition will not cause the join to

This statement produces the following data:Orders TableCustomer TableCustomer TableOrder AmountCustomer NameCustomer ID25141.50Allez Distribution52191

Greater Than [>] linkThe result set from a Greater Than link includes all records in which thelinked field value from the primary table is greater

Manager TableManager TableSalesRep TableSalesRep TableSalaryLast NameSalaryLast Name$32,000.00Fuller$48,300.00Dodsworth$30,000.00Brid$48,300.00Dodswor

5. Position the object frame in the upper left-hand corner of the Page Header(PH) section of the report and release the mouse button to place theobjec

WHERE SalesRep.'Salary' >=Manager.'Salary'This statement might produce data such as this:Manager TableManager TableSalesRep Tab

Less Than [<] linkThe result set from a Less Than link includes all records in which the linkedfield value in the primary table is less than the li

SalesRep TableSalesRep TableManager TableManager TableSalaryLast NameSalaryLast Name$48,300.00Dodsworth$29,500.00Buchanan$30,000.00Patterson$29,500.00

SalesRep TableSalesRep TableManager TableManager TableSalaryLast NameSalaryLast Name$35,000.00Davolio$32,000.00Fuller$48,300.00Dodsworth$32,000.00Full

Not Equal [!=] linkThe result set from a Not Equal link includes all records in which the linkedfield value in the primary table is not equal to the l

Product2Product1Product NameProduct NameXtreme Adult HelmetXtreme Mtn LockInFlux Lycra GloveXtreme Mtn LockRoadster Micro Mtn SaddleXtreme Mtn LockXtr

Using SQL and SQL databasesPerhaps the most popular and most powerful database formats are DBMSapplications based on the Structured Query Language (SQ

An SQL query is an SQL statement designed specifically to request datafrom one or more SQL databases. Some SQL applications require that youtype in an

network. Because of the high processing demands required by a networkserver, the computer used as the server is often a high-powered, fast machinethat

Do not confuse server applications with network server computers. Both areoften referred to as servers. However, a server application resides on anetw

To delete an object from the repository...159Using Undo in the repository...

Adding a field headingAs you can see, the Contact Name field is the only field without a heading.In this section you will create a heading.To add a fi

Stored proceduresIn addition to the common relational database attributes (tables, fields,records, and so on) many SQL DBMS systems support stored pro

do the sifting and gets back a much smaller set of data, thus reducing thetime and resources your workstation must use in order to finish the report.T

identify the table in your report. The following example illustrates the FROMclause used with the SELECT clause:SELECTTABLEA.'CUSTNAME',TABL

ORDER BYThe ORDER BY clause specifies that the database records retrieved besorted according to the values in a specific field. If you do not use the

MYTABLE.'STATE',MYTABLE.'ZIPCODE'Server-side processingServer-side processing allows you to set up a report that performs the majo

amount of processing that must take place on the client side. If the Detailssection is shown, server-side processing will not be possible.• In some ca

enabled, individual aspects of server-side processing will modify the SQLstatement in different ways.• If you select Use Indexes Or Server For Speed (

This check box is inactive if Use Indexes or Server for Speed is notselected.3. Click OK.Note:You can also enable or disable this option quickly by se

• The lower-right box displays the names of mapped database fields. Whenyou map fields in the upper boxes, they appear in the lower boxes.For each dat

You can use each of these commands for a specific function; however, anyof these commands will open the Map Fields dialog box if the program detectsa

Notice that a default file name, based on the report title you defined earlier,is also displayed.2. Type Custlist.rpt in the File name box and click S

• If there is a check mark beside Verify on First Refresh, the option is active(the option is active by default for new reports).• If there is no chec

• If the program detects no changes in the active database, the messagebox displays this message: "The database is up to date." In this case

considerable performance gains—especially in larger reports—by indexingfields that are referred to by record selection formulas.Note:The benefits of S

grouping, sorting, or formatting of the report doesn't change at all. The indexesmerely allow Crystal Reports to locate particular records quickl

these criteria, then you should prioritize the fields and index only someof them.• Don't index fields that contain unique values only.For instanc

Accessing Data Sources26

IntroductionCrystal Reports can access data stored in almost any common databaseformat, as well as many uncommon formats. This section discusses the m

•Direct access database files•ODBC data sources•OLE DB•Business Views•Crystal SQL Designer files•Crystal Dictionary filesEach type of data must be acc

In addition, data access is simple. Direct access database files arepoint-and-click data sources. You need only select the required databasefiles, and

Crystal Reports can then use a native method of communication to talk tothe translation files.Data translationData is translated through a set of DLLs

Entering the selection criteriaWhen you scroll through the report, you will see that it contains informationfor customers from many different countrie

different set of DLLs. However, some formats expand the basic three-tieredstructure.The following sections cover the systems used by Crystal Reports t

ParadoxFiles created with Paradox (.DB) are made available to other applicationsthrough the Borland Database Engine (BDE). The BDE does the actual wor

To translate information and data to and from DAO, the Report Designeruses the DAO translation file crdb_dao.dll.Secured Microsoft Access DatabasesIf

Pervasive DDF filesCrystal Reports does not determine the definitions of Pervasive data filesdirectly from the data files themselves. It needs a set o

• Distribution Lists• Exchange Folder Contents• Mail messages• Exchange Form applications• Properties of OLE documents• Exchange Administrator• Proper

Data sourceFile nameExchange Public Folder Replicacrdb_p2srepl.dllPublic Folders Access Control Lists(ACL)crdb_p2sacl.dllPhysical Server DLL for Micro

Crystal Reports lets you report on your Outlook data by providing thetranslation files crdb_p2soutlk.dll and p2soutlk.dll. Because Outlook data isstor

Sybase Adaptive ServerCrystal Reports opens SQL data created by Sybase Adaptive Server directlythrough the crdb_p2ssyb10.dll, installed with Crystal R

Informix client installed and configured properly before using this translationfile.DB2 ServerIBM's DB2 Server is an SQL compliant, client/server

DescriptionFileFor Crystal Data ObjectCrystal Data Object COM DLLcrdb_cdo.dllcdo32.dllFor Microsoft Data Access Objectscrdb_dao.dllFor ODBCcrdb_odbc.d

Imagine that you are completing the following sentence:Select all records where a customer's country isYou complete the sentence with the conditi

files communicate with the Event Logging API in Advapi32.dll, a part of theWindows NT operating system.Microsoft IIS/Proxy log fileIf you use Microsof

accessed by a single application. An application need only communicatewith one set of files (ODBC) to be able to work with any source of data thatcan

Finally, by using SQL pass-through technology to send an SQL statementto ODBC and retrieve an initial set of data, Crystal Reports off-loads muchof th

•Crystal Reports layer•ODBC translation layer•ODBC layer•DBMS translation (ODBC data source) layer•Database layerBy using the Structured Query Languag

ODBC uses the SQL language for all transactions between Crystal Reportsand ODBC. Even if the database does not normally use SQL to create andwork with

Note:ODBC drivers find their specific DBMS client files on the local machine mainlythrough key directories that the DBMS client has installed in the s

For example, if you have installed the Informix database client, selectInformix from the Data Access folder.5. Click the selected data access option a

ExcelYou can convert Microsoft Excel spreadsheets into databases that can beread by Crystal Reports through ODBC. In Excel 4.0 and earlier, use the Se

Reports provides several DataDirect ODBC drivers through download,allowing you to access many of the most popular SQL databases, including:• Informix•

Visual FoxProMicrosoft Visual FoxPro data is accessed through ODBC, while FoxPro datafrom version 2.6 and earlier is accessed directly through the xBa

10. Save this version of the report without overwriting the original report bychoosing Save As from the File menu and giving the new report the nameUS

JDBC data sourcesJava Database Connectivity (JDBC) is a Java API developed by SunMicrosystems that acts as an interface between a developer's Jav

Five layersThe process by which Crystal Reports accesses data from a JDBC datasource consists of five layers.•Crystal Reports layer•JDBC translation l

this feature of JDBC is transparent, but more advanced users often takeadvantage of the features of the SQL language used by JDBC.DBMS translation (JD

Configuring the Crystal Reports JDBC driverWhen you install Crystal Reports, the following Crystal Reports JDBC driverfiles are added to your system:•

Note:• When you report off a universe in Crystal Reports, the query that isproduced is based on the structure of the database the universereferences a

• Business ElementsUsers can access Business Views through products such as Crystal Reportsand the Report Application Server.For detailed information

BusinessObjects Enterprise Repository and shared between many users.For more information, search for the topic called "Defining an SQLCommand&quo

Note:The file crdb_dictionary.dll must be installed before you can use dictionaryfiles in your reports.Crystal Reports reads the dictionary file by us

748 Crystal Reports 2008 SP3 User's GuideAccessing Data Sources26Crystal Dictionary files

Report Processing ModelA

Balancing field spacingNow that the Country field has been deleted, there is a large amount of whitespace between the City and Contact Name fields. Yo

OverviewCrystal Reports uses a three-pass reporting method to generate reports.The sections below describe what happens during each step of this proce

Formulas that contain references to subtotals or summary informationare processed in the second pass.• Application of the record selection locally. If

• Group selection formulas.• Running totals.• Calculation of formulas marked "WhilePrinting Records."These are formulas that contain referen

Crystal Reports ErrorMessagesB

Drive:\filename.extensionThis document could not be opened. It does not appear to be a CrystalReports document.ContextOccurs when attempting to open a

Failed to load database connectorReasonThe database connector is not available on the machine that is processingthe report.The database client softwar

ContextOccurs when refreshing or previewing a Crystal report that does not containsaved data.Resolution• Ensure that the data source required by the r

In the Crystal Reports designer, verify that the SQL query, as shown underthe Database menu Show SQL Query option does not contain syntax errors.If th

758 Crystal Reports 2008 SP3 User's GuideCrystal Reports Error MessagesBFailed to retrieve data from the database. Details: [Database Vendor Code

Creating Accessible ReportsC

Grouping and sortingData in reports can be grouped and sorted in a variety of ways. Sorting andgrouping tools provide a great deal of flexibility for

About accessibilityWhen you create Crystal reports for a large audience across theorganization—and around the world—you need to account for the divers

Many accessibility guidelines result in improved usability. An accessiblereport must provide logical and consistent navigation. Its content mustbe cle

If you build accessible features into your reports now, it will be significantlyless expensive than to redesign existing reports later.About the acces

Accessibility and Business Objects productsBusiness Objects products allow you to design accessible reports and deliverthem to your users via the Web.

The following sections provide tips and guidelines on how to design reportswith accessibility in mind. Click the appropriate link to jump to that sect

For example, you place Quarter, Year, and Invoice fields in the Details sectionand then add the report title "Invoices by Quarter" to the Re

TextThe most common accessibility issue encountered by report designers isalso one of the easiest to resolve: providing text-only versions of non-text

Describe the purpose of the non-text object. For example, if an imageperforms an action when you click it, describe the action. For a buttonthat opens

To add a text-only alternative to a subreport1. Create a text-only version of the report and save it.2. Open a new report.3. On the Insert menu, click

Formatting textAfter you create text equivalents or alternatives for non-text objects, ensurethat the text is clearly written and easy to read. Observ

The program takes all records with the same value in the region field andplaces them together in a group on the report.3. Select in ascending order fr

equivalent. Adding text descriptions for decorative objects can produceunnecessary clutter.Text versions of visual or auditory objects in reports shou

Contrasting colorsUsers with limited vision may be unable to distinguish between colors. Totest the color contrast in your report, print or view a bla

• HyperlinksUsing color as the only method for identifying hyperlinks may also causeproblems for color-blind users. When you print your report in blac

Parameter fieldsWhen you include parameter fields in a report, make sure they are clear andsimple. Although parameter fields can be a useful tool for

to choose their own accessibility options using a parameter field that promptsthem to choose whether or not to display accessible formats.Using this p

4.Click the Formula button that corresponds to the Size list.The Format Formula Editor opens a new formula named Font Size.5. In the Formula text wind

To suppress an accessible section1. Right-click the left boundary of the section you want to suppressconditionally, and click Section Expert.2.In the

If you want only screen readers to be able to see the subreport, you can hideit by changing the subreport link to the same color as the background.Alt

Include whatever information is necessary to establish the meaning andcontext of the value displayed. When appropriate, include information thatdescri

Providing extra information for each value can make a data table appearcluttered for people without vision impairments, so you may want to hide theext

Note:For many reports, you will want to insert summaries, subtotals and grandtotals. For example, when creating a sales report rather than a customer

Labelling data tables conditionallyAlthough adding text objects is relatively easy to implement, it does notaddress all accessibility concerns. Invisi

Note:The report shown also uses the ?Access parameter field to enable the CanGrow option (also on the Common tab of the Format Editor) and increasethe

This report uses the following formulas:@Employee IDIf {?Access}="Yes" then "Employee ID "+ ToText({Employee.Employee ID},0) + &qu

Note:• The report also uses the ?Access parameter field to enable the Can Growoption and increase the font size.• In @Employee ID, ?Access parameter f

• Ensure that headings provide enough information to clearly identify thevalues that they label.• To test a table's accessibility, read its headi

are not currently accessible to everyone, the BusinessObjects Enterpriseweb desktop and the DHTML viewer allow for accessible access to reportsover th

If you customize Crystal reports or the BusinessObjects Enterprise webdesktop extensively, you may encounter other accessibility issues. For onlineres

is clearly located next to the form component. For example, for a Searchbox, ensure that the "Search" title appears alongside the appropriat

• the Government of Canada Internet Guide:http://www.cio-dpi.gc.ca/ig-gi/788 Crystal Reports 2008 SP3 User's GuideCreating Accessible ReportsCRes

More InformationD

The Available Fields list box displays all fields currently on your reportand all the fields in the data source. You can choose to sort based onany of

LocationInformation Resourcehttp://www.sap.comSAP BusinessObjects productinformationNavigate to http://help.sap.com/businessobjects and onthe "SA

LocationInformation Resourcehttps://cw.sdn.sap.com/cw/community/docupediaDocupedia provides additional documentation resources,a collaborative authori

LocationInformation Resourcehttp://www.sap.com/services/bysubject/businessobjectsconsultingConsultants can accompany you from the initial analysisstag

Index3-D Riser chart 3703-D Surface chart 370Aabsolute formatting 330Access 720database 720, 736via ODBC 736via the DAO Engine 721accessibility 760and

Bottom N 244selecting groups 244selecting groups conditionally 247selecting percentages 244selecting percentages conditionally 247sorting 751boxes 337

charts (continued)types (continued)histogram 370line 370line, zooming 384numeric axis 370pie 370radar 370stock 370using underlay feature with 386XY Sc

Crystal SQL Designer files, ODBC data sources745Crystal syntaxcreating formula 572currency fields, formatting with HighlightingExpert 356custom banner

database types (continued)SQL 737Sybase Adaptive Server 727Visual FoxPro 739Web/IIS log files 730databases 658alias 659and performance 174changing nam

designing reports (continued)inserting (continued)sections 275special fields 125text objects 126linking two or more database tables 120manipulating da

equal count, on Ranged map 391equal link 686equal ranges option, Ranged map 391error messages, described 754Excel 737database 737via ODBC 737Exchangew

Using SQL Expressions for groups, sorts, and totals...188Using SQL Expressions for Case Logic...

Notice that the records within each group are in alphabetic order.Completing the reportYou have just one step left to complete the report. A company l

filters (continued)creating 468editing 475reference to operators 472removing 475using And or Or to combine 471Flash objects 425binding to an Xcelsius

formulas (continued)debugging 579evaluation time errors 580deleting 578editing 575copies of formulas 578enhanced record selection 177for record select

groups (continued)creating (continued)custom 220running totals 263running totals using formula 268editing 241selecting with Select Expert 225sorting 2

JNDI, description 740join types 678full outer 684inner 679left outer 680right outer 682Kkey controls for Formula Editor 569Llanguages, SQL 701layouts

Map Expert 391editing maps with 403map layouts 390Advanced 390Cross-Tab 390Group 390OLAP 390Map Navigator 408hiding 408showing 408map types 390Bar Cha

numeric values, using accounting conventions343OObject Package, adding to Workbench 139objects 317and guidelines 317changing X position conditionally

PPage Footer, section 109page footers 133creating after first page 354Page Header, section 109page headers 133page margins 327Paradox database 721para

prompting (continued)creating a dynamic prompt that cascades609creating a static prompt 602overview 588prompts 469building 469combining with filters 4

Report Alerts (continued)deleting 550editing 549referring to in formulas 551viewing 550Report Definition (TXT), exporting to 507report design environm

reports (continued)inserting (continued)sections 275special fields 125text objects 126title 68layout 92making read-only 331manipulating data 94merging

3. Position the object frame in the upper left-hand corner of the ReportHeader (RH) section of the report and click once to place it.Placing the logo

running totals 260creating 262for a group 263using formula 268one-to-many linking 267Ssample data 54Save As dialog box 70Save dialog box 70saved data

sorting (continued)Top N 751with SQL expressions 188sorts, adding to OLAP grid 501spacing between text-based objects 315indenting lines 321selecting t

TTab Separated Text (TTX), exporting to 507table indexes, for performance 174tables 119, 777adding 119alias 659deleting blank lines 281indexed 662link

Vvalues 212ascending sort order 212, 218creating custom groups 220currency, display 322descending sort order 212, 218numeric, display 322parameter fie

814 Crystal Reports 2008 SP3 User's GuideIndex

Quick start for advanced usersIf you are an experienced Windows user who wants to get right into theprogram, follow these steps to set up a report for

Working with report elements on the Design tab1.Each of the default report areas contains a single section. To addadditional sections, click Section E

When you place multiple fields, they appear in the same order that theyappear in the Field Explorer dialog box. The program marks the positionof each

To speed processing time while building a report, you can preview thereport using only a small subset of the available data. To do this, go tothe File

3.Click New. The Formula Name dialog box appears. Enter a namefor the formula and click OK. The Formula Workshop appears with theFormula Editor active

11.To add a map, click Insert Map on the Insert Tools toolbar. SeeMapping.12. To insert a spreadsheet, picture, or other OLE object that you can editf

88 Crystal Reports 2008 SP3 User's GuideQuick Start4Quick start for advanced users

Report Design Concepts5

Group selection...224Grouping data in intervals...

Basic report designThe purpose of this section is to suggest a structured approach to preparinga Crystal report. This approach includes the following

• The purpose of this report is to show sales activity for each item ininventory, and to suggest reorder quantities based on that activity.• The purpo

Determining the layout of the reportWhat is the report title going to be?Write out a working title for the report. You may decide to change it later,

Are you familiar enough with the data to find the necessary information?When looking for a Customer Contact name, can the field be found in adatabase

working with, before you start any calculations. For example, some functionsrequire numeric data, while others work with only string fields. For morei

bottom of the selected column. See Calculating a percentage and Selectingtop or bottom N groups or percentages .What information should be flagged on

How often do report objects print?The way objects print will determine how you design your report. This willhelp you decide where to place charts, Cro

Details areaObjects placed in the Details area print with each new record.• Charts or Cross-Tabs cannot be placed in this area.• Formulas placed in th

learning and using the commands, rather than into trying to design and learnat the same time.To design a paper prototype1. Get the same size paper you

Introduction to Reporting6