September 9–11, 2013 Anaheim, California

The Power of Parameters Brian Reed, Claraview

Who Am I?      

2

Brian Reed: Senior Consultant I Company: Claraview, a division of Teradata Joined Claraview in January 2011 Currently residing in Richmond, VA Been using Crystal Reports and Business Objects since 2000 BOCP – Crystal Reports 2008

Learning Points  Understand the different parameter properties and how to use them  Change what data is returned from your data source  See how to use parameters to control sorting, grouping, and formula output  Use parameters to visually change your report

3

What is a Crystal Reports Parameter?  A field that can be used in a Crystal Reports formula  It requires a value before the report can process it  Can be used in a variety of places within Crystal to change the behavior of a single report  Has many properties available to customize it for different uses

4

Parameter Usages  Data Parameters - causes refresh of report data from data source    

Record Selection Formulas Command Objects Stored Procedures Universe Parameters

 Display Parameters - interacts with report without a data source refresh  Saved data selection formulas  Conditional formulas  Parameters placed directly on the report

5

Parameter Considerations  Consider how you intend to use the Parameter in your report  What Data Type? – Boolean, Currency, Date, DateTime, Number, String, or Time  Free Form text input or List of Values?  Required or Optional?  Putting thought into how you are going to use your parameter will save you some rework later

6

Creating a Parameter Within the Field Explorer      

Parameters are created from the Field Explorer Required properties are prepopulated for you Give the parameter a meaningful name Pick an appropriate data type Determine your list of value needs, if any Set your Prompt text to something more meaningful than the default  Define any additional properties as necessary

7

Creating a Parameter Within the Field Explorer

8

Creating a Parameter for a SQL Command  Parameters are created within the Command itself  It helps to know some basic SQL when working with Commands  There are only a few properties you can populate when creating the Command Parameter  You have to add the Command to the SQL in the WHERE clause  Use “=“ or the “IN” keyword depending if you are allowing multiple values or not  Need to put a single quote around the parameter if you use “=“  Click OK to test the Command  You can edit the Command Parameter in the Field Explorer and modify properties that effect how the prompt will act

9

Creating a Parameter for a SQL Command

10

List of Values    

Allows the user to select a value from a list Can help avoid spelling errors in values Lists can be either static or dynamic Values can either be entered by the developer or pulled from a database field  If using Enterprise, you can have the list generated on a schedule and multiple reports can have access to it

11

List of Values –Value and Description fields  Value field is what the Parameter field will hold and have available to your report  Description field is available for providing context around the Value field but only available in the Prompt screen  In v. 2008 SP5, there is now an option to allow you to display the Description field in the report instead of the Value field

 Why use both rather than just one?  Your database might be indexed on an ID field but your users know the Description, not the ID

 You can set the properties to show just both field or both in the prompt screen

12

Prompts  There is a difference between what a Prompt is in Crystal and what it is in WebI  Prompts are different from Parameters even though some people use the two interchangeably  Prompts are the interface that allows the user to provide a value for a parameter  Not used directly by a Crystal Reports formula  Designer has some control over what appears in the prompt screen  Can include a list of values to assist the user in providing values

13

Examples In the Demo             14

Basic Parameter Multivalue Parameter Range Parameter Dynamic Cascading Parameter Optional Parameter SQL Command Parameter Parameter Used in Grouping Parameter Used in Sorting Displaying Parameter Values Using a Parameter to change the value of a field Visually Change your Report Top N Parameter

Parameters

DEMO

15

Parameters

Data Parameter Examples 16

Basic Data Parameter - Example  Parameter is created to allow user to pull back data for the report for a single value  Set the Properties     

Name Type List of Values – Static and Actions>Append All Database Values Prompt Text – Modify from the default (optional) Allow Custom Values – change to False

 Change Record Selection formula to compare a database field to the parameter  Refresh the report and provide values to the prompt for the parameter 17

Basic Data Parameter – Example (cont.)

18

Basic Data Parameter – Example (cont.)

19

Basic Data Parameter – Example (cont.)

20

Basic Data Parameter – Example (cont.)

21

Multivalue Data Parameter - Example  Parameter is created to allow user to pull back data for the report for one or more values  Steps to create the parameter are the same as the basic parameter in the previous example  Modify “Allow multiple values” from False to True  Record Selection Formula is still the same  Refresh/Run the report and pick more than one value in the prompt screen  Notice the difference in prompt screen and in the data returned in the report

22

Multivalue Data Parameter – Example (cont.)

23

Multivalue Data Parameter – Example (cont.)

24

Multivalue Data Parameter – Example (cont.)

25

Multivalue Data Parameter – Example (cont.)

26

Dynamic Cascading Parameter - Example  Dynamic allows user to get data dynamically every time the report is run  Cascading allows for filtering down on a hierarchy of data  Change List of Values to Dynamic  Click on a row in the grid to add a data field and then map it to a parameter. Repeat for as many levels as you need  Each row will have its own set of properties at the bottom  Record Selection Formula needs to have mapping of each field to its parameter  Refresh/Run the report and pick more than one value in the prompt screen

27

Dynamic Cascading Parameter – Example (cont.)

28

Dynamic Cascading Parameter – Example (cont.)

29

Dynamic Cascading Parameter – Example (cont.)

30

Dynamic Cascading Parameter – Example (cont.)

31

Optional Parameter - Example  Optional Parameters allow the user to leave a prompt blank if they do not want to provide a value  Parameter is defined like the Basic Parameter  Change the property “Optional Prompt” to True  Record Selection Formula needs to use the HasValue() function and some OR logic to properly evaluate the formula  Refresh/Run the report once and pick a value in the prompt  Run the report a second time and change the value back to blank (the … value in the drop down list)  Gives the user more flexibility in their report  Developers used to have to provide a value like “ALL” in the list of values and then code the Selection Formula to do this 32

Optional Parameter – Example (cont.)

33

Optional Parameter – Example (cont.)

34

Optional Parameter – Example (cont.)

35

Optional Parameter – Example (cont.)

36

SQL Command Parameter - Example  SQL Command Parameters allow the user to create parameters within a Command  Parameters must be defined in the Command  Command Parameters can be “augmented” with List of Values by editing the parameter outside of the Command in the Field Explorer. Requires list to be loaded or point to a Business View  Do NOT put any Parameter references in the Selection Formula if you are using a Command  Command Parameters can be defined to allow multiple values, just make sure you use the IN keyword rather than =

37

SQL Command Parameter – Single Value Ex.

38

SQL Command Parameter – Example (cont.)

39

SQL Command Parameter – “All Mult Values” Ex.

40

SQL Command Parameter – Example (cont.)

41

Parameters

Display Parameter Examples 42

Grouping Parameter - Example  Parameter does not change what data is returned in the report  Parameter is created with a static list of values that you define and do NOT allow custom values  Create a formula that checks each parameter in your list of values and returns the appropriate database field.  If you have different data types, you will need to convert those database fields to the same type  Insert a group and pick the formula field to group on  Test the report with each possible value

43

Grouping Parameter – Example

44

Grouping Parameter – Example

45

Grouping Parameter – Example

46

Grouping Parameter – Example

47

Grouping Parameter – Example

48

Sorting Parameter - Example  Parameter is setup exactly like the Grouping Example  Formula is also created just like the Grouping Example  Instead of inserting a group into the report, the formula is added to the Record Sort Expert  Run the report and test the different parameter values

49

Sorting Parameter – Example

50

Sorting Parameter – Example

51

Sorting Parameter – Example

52

Sorting Parameter – Example

53

Sorting Parameter – Example

54

Parameters in Formulas – Displaying Parameter Vals  Single value parameters can be displayed on a report by simply dragging and dropping on the report or in a text object  Multivalue Parameters require a formula to concatenate the values together  Optional Parameters also require use of a formula to help display what value was chosen

55

Displaying a Multivalue Parameter - Example

56

Displaying a Multivalue Parameter - Example

57

Displaying a Multivalue Parameter - Example

58

Displaying an Optional Parameter - Example

59

Displaying an Optional Parameter - Example

60

Displaying an Optional Parameter - Example

61

Displaying an Optional Parameter - Example

62

Parameters in Formulas – Using to Change a Value  You can also use a parameter to change the outcome of a formula  Perhaps you want to allow the user to project what your sales number could potentially be in the future  Create a new numeric parameter to represent a percent change to last year’s sales  Use the parameter in your calculation to change the value of the sales number  Display the parameter and the new formula on the report

63

Parameters in Formulas – Using to Change a Value

64

Parameters in Formulas – Using to Change a Value

65

Parameters in Formulas – Using to Change a Value

66

Parameters in Formulas – Using to Change a Value

67

Return on Investment  Parameters will allow you make reports more dynamic  More dynamic reports mean less versions of the same report with slight variances  Less reports mean less maintenance on the reports in the future  Less maintenance means more time for developers to be working on new projects rather than old ones

68

Best Practices  Use parameters in your reports to allow a single report to work in different ways  Eliminate multiple versions of reports by using parameters  Identical reports that have different selection criteria  Similar looking reports that are formatted slightly different

 Use the minimum number of parameters that you need  Consider using both the value and description fields as it may make your queries perform better

69

Key Learnings  Parameters can be used for more than just changing what data comes back to your report  Leverage parameters to make your reports more flexible  Look for ways that you can reduce the number of reports that you currently have and eliminate excess maintenance time  Parameters make your reports more interactive for the user

70

…and Finally…

Questions? Contact Info: Brian Reed [email protected]

71

Thank you for participating. Please provide feedback on this session by completing a short survey via the event mobile application. SESSION CODE: 0312

Learn more year-round at www.asug.com