Creating Crystal Reports

1. Creating Crystal Reports Tips and Tricks Presented by Ben Rimmasch

2. Topics • When to Use Crystal Reports • Getting Started with Crystal Reports • Hands On • Deploying Reports in CMC • Best Practices • Developing and Maintaining • Versioning and Deploying • Tips, Tricks and Strategies • Questions

3. When to Use Crystal Reports • Pros • Very flexible layout for when layout is important • Specific deliverable format • Forms • Graphics • Cons • Not customizable by the user • Changes require deployment • More time consuming than portlets

4. When to Use Crystal Reports (cont’d) versus

5. Getting Started with Crystal Reports • Work Environment Preparation • Setting data sources • Oracle Client and Java for Oracle databases • Add a tnsnames.ora entry for each environment • System DSN for SQL Server • Installation of Crystal Editor • Setting Editor Preferences • Options -> Fonts (so all reports have the same look and feel) • Options -> Layout (to make editing easier)

6. Getting Started with Crystal Reports (cont’d) • Creating Your First Report • To wizard or not to wizard... • All options are available and easy to get to without using the wizard. It’s likely to not save you any time. It’s likely to cost you time. • As far as Crystal Reports go, friends don’t let friends wizard. • Save immediately (and often) • See Tips Tricks and Strategies

7. Getting Started with Crystal Reports (cont’d) • Adding a Command and query parameters • Consider the size of your dataset in choosing query parameters • Determine if a parameter would be single-select or multi-select. Script the SQL accordingly. • When creating parameters in Clarity in the report definition for each parameter a data type must be chosen. • For the data type of Browse or Pull-down a lookup must be specified. • Parameters with lookups can be set to single-select or multi-select. • Clarity will pass the hidden id of the lookup in the Browse and Pull-down parameter types to the Business Objects server. • For all parameters the id of the parameter in the report definition must match the name of the parameter in the Crystal report

8. Getting Started with Crystal Reports (cont’d) • Adding a Command and query parameters (cont’d) • For single-select lookups in filters the query might contain the following components: • {?param_project} is NULL – For testing when the parameter is optional and not supplied. This is only applicable INSIDE the editor because this will never be null from Clarity • {?param_project} = 0 – For testing when the parameter is option and not supplied from Clarity • {?param_project} = invi.id – To do the actual filtering of a single project • For multi-select lookups in filters the query might contain the following components: • 0 IN {?param_project} – For testing when the parameter option is not supplied from Clarity • invi.id IN {?param_project} – To do the actual filtering of multiple projects • The examples above assumed the hidden id in the lookup for the parameter was a lookup_enum and used 0. For lookups where the hidden id is a lookup_code use ‘’. • Test the data source with one or two fields surfaced on the report • Setup groups (report grouping) as one of your first steps

9. Getting Started with Crystal Reports (cont’d) • Sample Query select rim.name ,rim.description ,rim.impact_description ,rim.assumptions ,rim.resolution ,assignee.full_name assigned_to_name ,rim.created_date ,rim.priority_code ,case rim.priority_code when 'HIGH' then 3 when 'MEDIUM' then 2 when 'LOW' then 1 else 0 end priority_sort ,rim.impact_enum ,rim.impact_date ,i.name inv_name ,i.id inv_dbid from rim_risks_and_issues rim left join srm_resources assignee on assignee.user_id = rim.assigned_to join inv_investments i on i.id = rim.pk_id where 1 = 1 and (rim.description is not null or rim.description <> '') and rim.type_code = 'RISK' and (i.id IN {?param_project} OR 0 IN {?param_project})

10. Hands On • Creating a report • Navigating the editor • Field Explorer • Used to add items to the report • Report Explorer • Used to edit or remove items already in the report • Options (Editor-wide) • Fonts • Set fonts for consistent look and feel across all reports • Layout • Make preference changes for easier editing • Summary Options (Report specific) • Report Title • Other fields as needed

11. Hands On (cont’d) • Section Expert • Suppress (vs Hide) • Suppressing removes a section or field from view always. Hiding removes a section from view but leaves it available for drill-down. • Print at Bottom of Page • Forces the section to be at the bottom of the page. All following sections will then be forced to the next page (or column) • Keep Together • Determines when pagination will occur • All “Keep Together” values are essentially ignored once sections are placed in a group and the group’s “Keep Group Together” option is checked. • Group vs Parent Section vs Child Section • Group will always try to print the entire group on the same page • Parent will always try to print the children sections on the same page but when all sections are longer than a page it will page if necessary where the child section groups not set to keep together • Suppress Blank Section (almost never applicable because all values have to be fields)

12. Hands On (cont’d) • Groups • Group By • Section will be printed on any change of this field. Order matters! If the values have already been through a group or have been sorted and the field occurs in your records a few times and then again in your records two groups will be created for that field. • Ordering • Often a “virtual” field must be created for ordering to occur correctly in groups. • Customize Group Name Field • Very useful when the “virtual” sorting field scenario comes into play. • Keep Group Together • Overrides almost all other keep together settings • Repeat Group Header on Each Page • Very useful as a makeshift page header in subreports • Useful for breadcrumb or hierarchy data display

13. Hands On (cont’d) • Menu and Buttons • Insert Text Object • The primary way to insert static text into a report. • Fields and formulas can be dragged into text objects. • Insert Group • Opens the group creation dialogue (see previous slide) • Insert Summary • Summary vs Running Total Fields • Summary fields can be used anywhere and the values have already been calculated. Very limited options compared to running total fields. • Depending on where you put a running total field the values may not have accumulated. This is sometimes desirable and sometimes not. • Insert Subreport • Restricted to parent report and one level below. Subreports may not contain subreports. • Subreports don’t have page headers or footers. • Once a subreport is placed parameters, fields and formulas from the main report can be passed to the subreport as parameters by right clicking the subreport and changing the subreport links. Data types of parameters must match.

14. Hands On(cont’d) • Menu and Buttons (cont’d) • Insert Line and Insert Box • Both objects will grow if placed across sections • Won’t snap to grid or guidelines • Insert Picture • BMP always works and is usually editable through Crystal • JPG and PNG sometimes throw errors and won’t import and are never editable. • Insert Chart (charts and graphs) • Provides basic charts, Gantts, pies, cross-tabs and many others. • There are many options and features here that won’t be covered in class because it’s beyond scope.

15. Hands On (cont’d) • Cross-tab Expert • Right click an existing cross-tab • This is an additional way to get totals other than summaries and running total fields. Essentially the same options as running total fields but in a grid with additional grouping outside of the groups setup in the report. • Database expert • Adding and editing commands • Changing database locations (“Set Datasource Location”) • Parameters • Data types must match those of Clarity. • Clarity always passes numbers for checkboxes • Clarity always passes empty strings (‘’) instead of nulls for blank strings • Clarity never passes a null array for empty multi-value lookups with lookup_code as the hidden key. It passes an array with one value and that value is an empty string. ([‘’]) • Clarity always passes 0 instead of null for blank numbers • Clarity never passes a null array for empty multi-value lookups with lookup_enum as the hidden key. It passes an array with one value and that value is 0. ([0]) • The two rules above will dictate how your report filter lookups are designed.

16. Hands On (cont’d) • Formulas and Formula Workshop • Believe it or not, the built-in Help library is very informative. • Ctrl + space brings up autocomplete drop down • Option of Crystal or Basic syntax • Crystal syntax is included in all versions of Crystal Reports • Basic syntax is modeled on Microsoft Visual Basic • Note that record selection formulas and group selection formulas cannot be written in Basic syntax. For this reason I prefer Crystal syntax across the entire report. • Basic operators and functions are available • if, else if, else, IIF, switch, loops (do while, do, etc.), for (supports exit), assignments, scoping variables, totext, len, concatenate, substring (through operator), in (contains), split, replace, plus many more. • Strings are delimited by double quotes e.g. “this is a string”. • IsNull and HasValue for checking if fields or parameters have values

17. Hands On (cont’d) • Running Total Fields • Choose when to evaluate and when to reset • Multiple summary types including sum, count, distinct count, max, min, Nth largest, Nth smallest, average, sample variance and standard deviation, population variance and standard deviation, median • Useful to duplicate spreadsheet-like reports • Special Fields • Report elements that can be used visually or in formulas to access some of the components of the report • Examples • Record Number, Page Number, Print Date and Print Time, Data Date and Data Time, etc.

18. Deploying Reports in CMC • Login Screen (Almost always uses Enterprise Authentication)

19. Deploying Reports in CMC (cont’d) • If you don’t see the Folders tab or Folders link you do not have sufficient rights

20. Deploying Reports in CMC (cont’d) • To add reports navigate to the appropriate folder and left click the folder in the folder explorer (the left pane) to select it. Click the manage button and then go to Add and select “Crystal Reports.” If you do not see “Crystal Reports” from the Add menu you do not have sufficient rights.

21. Deploying Reports in CMC (cont’d) • Choose a file from the file dialogue popup window. You may also rename the file in the “General Properties” section but only after choosing a file.

22. Deploying Reports in CMC (cont’d) • Setting the Database Configuration • Choose “Use custom database logon information specified here.” • Specify the “Server”. This will typically match the “Server Name” field in CSA). • Usually the rest of the information can be left at blank or default values. (See image on next slide)

23. Deploying Reports in CMC (cont’d) • Setting the Database Configuration • The values that are blank will be filled in by Clarity during the handshake that takes place between Business Objects as long as “Prompt the user for database logon” is chosen. • If you don’t want these values to be automatically filled in (like if you wanted a report to run across environments for example) you can specify the database, user and password as necessary. • The table prefix field can be used if the database server has additional schemas setup but is typically not necessary.

24. Best Practices • Developing and Maintaining • When placing database fields place them inside text objects. • If you have to change your query this can help you avoid losing formatting. • This will allow you to use the format painter field across all of your text. You can’t format paint the format of a text object onto a database field and vice versa. • Use underscores in variables, formula, and database field names. This allows for easy copying and pasting later when the requirements change down the road and you will inevitably have to rewrite portions of the report. • Use a naming scheme for similar variables e.g. all running total fields for a certain group and a certain field match and are easily identifiable. • Limit the number of records when possible in the query. Often times the Business Objects server is separate from the database server and transferring many records can negatively impact report speed.

25. Best Practices (cont’d) • Developing and Maintaining (cont’d) • Grouping Records in Crystal is magnitudes of times slower than grouping records on the database. Sometimes avoiding grouping in Crystal is not possible but strive to do so when possible because grouping records in Crystal is one of the largest contributors to poorly performing reports.

26. Best Practices (cont’d) • Versioning and Deploying • Save a copy of your report in your local work environment before you start a new version. I like to use the following format as it allows files to sort nicely on the file system: • OriginalReportName yyyy-mm-dd • Save a copy of your report in CMC before you deploy a the new version. I like to follow the same naming scheme as above. If many revisions to the reports happen an archive folder can be used so the main report folder does not become cluttered and time consuming to navigate and search. • Clarity Report Definitions • Because users can have saved parameters and add reports to the “My Reports” portlet and because Clarity supports security on a per report basis and because it’s less effort it is preferred to change the original report definition. If a legacy copy must be kept XOG the original report definition out and then in with a new name and id.

27. Best Practices (cont’d) • Clarity Report Definitions (cont’d) • Report Read XOG <?xml version="1.0" encoding="UTF-8"?> <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd"> <Header version="8.0" action="read" objectType="contentPack" externalSource="NIKU"> </Header> <JobQuery> <Filter name="code" criteria="OR">RPT_DATA_SETUP</Filter> </JobQuery> </NikuDataBus>

28. Tips, Tricks and Strategies • Save often! There are a few ways that the Crystal editor can get into a state where it will not save. • If the computer that the Crystal Editor is running on goes into sleep mode often when it wakes up the save and save as functions will error out with a “Failed to save document.” message. • If the dataset is very large and a lot of moving around in the pages is performed graphical glitches will begin to manifest and Crystal will soon crash or stop responding. • When deleting subreports Crystal will sometimes crash. • Crystal will sometimes crash when editing reports in general. • SAVE OFTEN!

29. Tips, Tricks and Strategies (cont’d) • Space fields with real data but don’t do all formatting until everything is placed in the final font and size. • Fine tune and format the fields after everything fits. • Use the guidelines! They will save development time and make reports appear more professional. • If the entire dataset is reasonably small run the report wide open (with all possible records) and look for problems. This will help eliminate fields that have values that are too long. • Consider using a text object with background colors rather than separate images for “lights.” They’re easier to create, repurpose and move around. • If the out-of-the-box Gantt chart doesn’t meet your needs you can create your own using ratios and formulas to set the size and position of text objects. • If the report renders in the Crystal viewer in Clarity incorrectly check to see if the report has a printer configured. Sometimes removing or configuring a printer can solve difficult rendering problems. • Vertical alignment is not possible within a text object or field so changing the height (size) of the element’s box and changing its location is the best option • If you have text from one column bleeding over into another column and overwriting characters you can solve this by opening the format editor and changing the “Text Interpretation” of the field that is bleeding to HTML. Another option is to turn the field into a formula and check the length and truncate extra characters when necessary.

30. Tips, Tricks and Strategies (cont’d) • Obtain report resources (logos, fonts, color palettes, icons) as soon as possible from the marketing or branding department. • A report can be set to have multiple columns in the section expert. All of the paging functions will continue to put records on the next page rather than the next column. To force sections to the next column a detail section forced to “Print at Bottom of Page” will suffice. • Reports can be ran from a URL and set as Actions on Clarity objects. • Reports can be auto-scheduled to email directly from Business Objects. • A report can render differently in the editor, the viewer and its final intended format (pdf, Word, Excel, etc.). Test all three forms. • Suppressed subreports can be used to perform calculations, set variables, etc. Even though they’re suppressed they still run.

31. Tips, Tricks and Strategies (cont’d) • Clarity always passes the following “hidden” parameters: • param_language = This can be used for localization • param_user_id = This can be used to personalize the report to the logged in user • param_host_server = This can be used to create URLs back to Clarity • The following formula can be used to check if no records are returned from inside a details section: • IsNull (Count ({Command.INVESTMENT_KEY})) where {Command.INVESTMENT_KEY} is any field that can’t be null in Clarity • This is useful to create a detail section for the user to inform them that the filter criteria chosen didn’t return any results. • Give the report a title from the Summary Info dialogue box under File in the menu. It will change the title in the report’s editor tab as well as populate the correct value in the “Report Title” special field.

32. Questions Contact US 888.813.0444 Email Contact info@regoconsulting.com Web Site www.regoconsulting.com Thank you!