Crystal Reports XI - Essentials for New Users

Crystal Reports XI Essentials for New Users Alan Mayer, Integra Solutions Crystal Reports XI … The Marriage of Reporter and Designer! Slide 3 Our Figureheads BusinessObjects Reporter (now Desktop Intelligence in XI R2) BusinessObjects Designer Slide 4 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 5 Getting Started Crystal starts without logging in Login usually required to access data Enterprise-based content is secured No way to restrict most Crystal Reports functionality 1/2 Slide 6 Getting Started Users can choose blank or wizard-driven reports Wizards help with a particular type of report Blank reports are built from scratch 2/2 Slide 7 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 8 Retrieving Data The source of data is chosen first Crystal has more potential sources to choose from Native drivers are available for Oracle, DB2, … 1/15 Slide 9 Retrieving Data Choose a data source then establish the connection Similar to establishing a connection to a universe Not as many options for controlling the connection 2/15 Slide 10 Retrieving Data Once the connection is set, data objects can be retrieved Works like the BusinessObjects Designer table browser Options can alter the objects displayed (right-click on list) 3/15 Slide 11 Retrieving Data Select tables and views to report on 4/15 Slide 12 Retrieving Data Crystal automatically links the tables by default 5/15 This feature can be turned off Links are created based on field name or index, data type, and data format Links can be manually created (traced) Similar to BusinessObjects Designer join strategies Slide 13 Retrieving Data Use Link Options for fine-tuning 6/15 Change inner joins to outer joins Change relationships (other than equal) Enforcing Joins similar to implicating joins in Designer (Table button) Cardinalities not necessary – no loops Slide 14 Retrieving Data End of the road for a blank report No fields (or data) is displayed The Field Explorer can be used to select report fields 7/15 The Database Expert returns you to any previously selected options Slide 15 Retrieving Data Report fields can be added Breaks (groups) can be defined Totals can be added Predefined templates may also be selected 8/15 The Standard Report Wizard asks additional questions Slide 16 Retrieving Data The Report Wizard’s final result: A report with data! 9/15 HEY! Slide 17 Retrieving Data 10/15 A report without conditions? Traditional BusinessObjects users add conditions within the Query Panel Crystal users add conditions through the Select Expert Slide 18 Retrieving Data The Select Expert 11/15 The Select Expert adds one or more conditions to the query It acts like the condition panel within a traditional BusinessObjects query Slide 19 Retrieving Data The Select Expert The Select Expert initially has no conditions Select the New tab or button to start a condition 12/15 Slide 20 Retrieving Data The Select Expert, cont’d Choose a field from the tables previously selected 13/15 The field you select does not have to be displayed in the report Don’t find the field you need? Add more tables using the Database Expert Slide 21 Retrieving Data The Select Expert, cont’d Choose a comparison operator and operand 14/15 List of values is automatic! Slide 22 Retrieving Data The Select Expert, cont’d The final condition: 15/15 More complicated record-selection conditions can be created in the Formula Editor Slide 23 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 24 The Crystal Reports Model 1/3 BusinessObjects Reporter characteristics Multiple blocks per report Blocks located anywhere Multiple reports per document Frame-based Slide 25 The Crystal Reports Model Crystal Reports characteristics One main report Section-based (band-based) Multiple tables per band Sub-sections allowed 2/3 Slide 26 The Crystal Reports Model Report header and report footer Printed once per report Used for report title Also used for summary charts, tables 3/3 Page header and page footer Printed top and bottom of every page Column headings are commonly found in header Page numbers found in footer Details Prints detailed information once per record Field values found here Slide 27 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 28 Formatting 1/8 Placing fields Drag fields from the Field Explorer to the Design window One or more fields can be selected at a time Drag one field at a time for precise placement Slide 29 Formatting Placing fields, cont’d Position fields using guidelines Fields are attached to nearest guideline Moving the guideline moves the field No similar technique in BO Reporter 2/8 Guideline Slide 30 Formatting Placing fields, cont’d Other alignment options are available Like Reporter, Crystal offers Snap to Grid Crystal does it with more precision Individual fields/objects can be aligned 3/8 File \ Tools \ Layout View \ Grid \ Design Slide 31 Formatting Formatting fields The Field Formatter controls the format 4/8 Formats can be conditionally applied Super Alerter! Slide 32 Formatting Formatting sections Format sections using the Section Expert 5/8 Slide 33 Formatting Formatting sections, cont;d Some (but not all) of these features are available from the Reporter’s Map tab 6/8 Slide 34 Formatting Using refreshed vs. saved data 7/8 Previewing your report the first time retrieves new data from the data source Crystal will only retrieve new data when necessary New fields added to report or a formula on report More records needed Different conditions applied using Select Expert Crystal may prompt you to choose between saved (existing) and refreshed data You can always force a refresh Slide 35 Formatting Previewing the report Use the Print Preview button to view your report You can make changes in the Preview window Resizing columns, formatting fields, … 8/8 Most developers make changes in the Design tab Slide 36 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 37 Grouping and Sorting Sorting Use the Record Sort Expert to sort detail records Adds an ORDER BY clause to the SQL query Can sort on fields not included in the report 1/10 Query Sort Slide 38 Grouping and Sorting Previewing sorted results The Preview tab shows the sorted records 2/10 Ascending Ascending Ascending (for ID) Descending Slide 39 Grouping and Sorting Previewing sorted results, cont’d 3/10 The Preview tab may be several pages long Use the navigation controls (upper right hand corner) to move between pages Slide 40 Grouping and Sorting Grouping 4/10 Use the Insert Group button to group similar records No limit to the number of groups added Can order groups Breaks Slide 41 Grouping and Sorting Grouping, cont’d Options can be specified for a group Fewer options than BusinessObjects Reporter Most options available in different areas 5/10 Slide 42 Grouping and Sorting Grouping, cont’d Header and footer added after group is inserted Group name added to header, nothing to footer 6/10 Slide 43 Grouping and Sorting Grouping, cont’d Group trees allow quick navigation Like the navigation control of Report Manager 7/10 Slide 44 Grouping and Sorting Adding totals Use the Insert Summary button to add totals 8/10 Slice and Dice Slide 45 Grouping and Sorting Adding totals, cont’d More calculations available! 9/10 Slide 46 Grouping and Sorting Adding totals, cont’d Location specified within window BusinessObjects Reporter used calculation contexts 10/10 Create groups before summaries! Slide 47 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 48 Formulas Use the Field Explorer or Formula Workshop to add formulas Formulas are another Crystal Reports “sweet spot” More power … more features … more control! 1/8 Select Formula Fields then New button .. Or .. Formula Workshop button Slide 49 Formulas Supply a name for the new formula Named formulas are variables in BusinessObjects Reporter 2/8 I like variables! Slide 50 Formulas The Formula Editor should satisfy all audiences Simple enough for the beginning user Powerful enough for a programmer 3/8 Slide 51 Formulas More functions are available! 4/8 Slide 52 Formulas More operators to choose from! 5/8 Slide 53 Formulas Formulas can be simple … 6/8 {Orders.Order Amount} * .175 No equal sign! Use braces to reference fields rather than < > No leading zeros needed! Slide 54 Formulas To conditional … if {Customer.Region} = "AL" then {Orders.Order Amount} * .05 else if {Customer.Region} = "AR" then {Orders.Order Amount} * .075 else 7/8 Slide 55 Formulas To procedural … //Reverse a string version 1 Comments! Local StringVar str := ""; Local NumberVar strLen := Length ({Customer.Customer Name}); Local NumberVar i; For i := 1 To strLen Do ( Local NumberVar charPos := strLen - i + 1; str := str + {Customer.Customer Name}[charPos] Arrays! ); str 8/8 Slide 56 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 57 Parameters 1/16 Parameters act like prompts in BusinessObjects Reporter Use the Field Explorer Select Parameter Fields then New button @Prompt Slide 58 Parameters Static prompts The New Parameter window will open 2/16 Slide 59 Parameters Static prompts, cont’d Add the parameter name and data type Tell Crystal Reports where to retrieve the values Specify a table field or Add values manually or Import values 3/16 Slide 60 Parameters Static prompts, cont’d We’ll add all possible database values Once added, these values are static 4/16 Slide 61 Parameters Static prompts, cont’d Parameter options can now be filled in Our current options: one value (mono) 5/16 Slide 62 Parameters Static prompts, cont’d The parameter can now be used in a SQL condition Use the Select Expert Modify the existing condition on Country The new parameter should appear in the list 6/16 Slide 63 Parameters Static prompts, cont’d 7/16 Crystal Reports will prompt for Country when this report is refreshed Slide 64 Parameters Dynamic prompts This approach creates independent prompts Each prompt is not constrained by any other A BusinessObjects report writer’s dream would be cascading prompts Each prompt would reflect previous prompt choices This was possible (but not elegant) in Reporter This feature has been added to Crystal Reports XI Wanna see how? 8/16 Slide 65 Parameters Dynamic prompts, cont’d Create another parameter based on Region Make its list of values dynamic 9/16 Slide 66 Parameters Dynamic prompts, cont’d Add a value based on Country Select the Insert button Choose Customer.Country 10/16 Slide 67 Parameters Dynamic prompts, cont’d Add a value based on Region 11/16 Select the Insert button again Choose Customer.Region Crystal Reports indents values in the direction they will cascade Slide 68 Parameters Dynamic prompts, cont’d 12/16 Two parameters are created to support the cascade The Field Explorer show two new parameters Slide 69 Parameters Dynamic prompts, cont’d Use the Select Expert to adjust the query Replace the Country prompt with Region - Country 13/16 Slide 70 Parameters Dynamic prompts, cont’d Use the Select Expert to adjust the query Create a new condition for Region based on Region - Region 14/16 Slide 71 Parameters Dynamic prompts, cont’d Refresh the query Choose any country The second prompt will reflect only regions of that country 15/16 Slide 72 Parameters Dynamic prompts, cont’d Try it again 16/16 Slide 73 Topics Getting started Retrieving data The Crystal Reports model Formatting Grouping and sorting Formulas Parameters Multi-pass reporting Q&A Slide 74 Multi-Pass Reporting 1/7 BusinessObjects Reporter is a single-pass report writer Crystal Reports XI uses a three-pass approach Multiple processing passes has many benefits More control over calculations New features that are not available using single-pass solutions Each pass requires Crystal to read or manipulate data Crystal processing top down, from left to right Formulas in header processed before footer Slide 75 Multi-Pass Reporting Pre-Pass 1 Constant formulas are evaluated Process known as BeforeReadingRecords No database fields allowed Example: 1000 * 25 2/7 Constant formulas Slide 76 Multi-Pass Reporting Pass 1 3/7 Record retrieval (selection and sorting passed to database if possible) Evaluate formulas with database fields (recurring formulas) • Known as WhileReadingRecords • NO references to subtotals or summaries Record selection formulas • Record selection too complex for database to handle Records sorted, grouped, then totaled per group Cross-tabs, charts, and maps • Database fields and recurring formulas • No running totals, PrintTime formulas Processed data is saved, used for all other processing Record retrieval Recurring formulas Record selection formulas Sorting, grouping, totaling Save results Slide 77 Multi-Pass Reporting Pre-Pass 2 Order groups • Only for Top N, Bottom N, or Hierarchical Grouping reports 4/7 No data read Uses saved data (group instances) from Pass 1 Sort groups Top N Hierarchical grouping Slide 78 Multi-Pass Reporting Pass 2 Process group selection formulas Running totals Formulas that reference subtotals or summaries • Known as PrintTime formulas • Called WhilePrintingRecords process 5/7 Cross-tabs, charts, and maps • Include running totals, PrintTime formulas OLAP grids Sub-reports Page on demand generation Group selection Running totals PrintTime formulas Subreports, charts, maps OLAP grids, cross-tabs Page On Demand Slide 79 Multi-Pass Reporting Pass 3 Total page count Used for total page count, Page N of M formulas 6/7 Total page count Slide 80 Multi-Pass Reporting Pass Summary -1 Constant formulas 7/7 1 Record retrieval Recurring formulas Record selection formulas Sorting, grouping, totaling Save results -2 Sort groups Top N Hierarchical grouping 2 Group selection Running totals PrintTime formulas Subreports, charts, maps OLAP grids, cross-tabs Page On Demand 3 Slide 81 Total page count The Storybook Ending Slide 82 Q&A Questions Contact information Alan Mayer Phone: 214-637-6622 Email: amayer@integrasolutions.net Slide 83