Skip to content

Including a.m. and p.m. in your strings

The ToText function, used with times, lets you add an am or pm string to your times. You can even pass your own am/pm strings if you don’t want to use the default AM and PM.

Here’s a look at how it works. (Where timefield= 4:15:00 p.m.)

 
 

Example

Result

ToText({timefield}, “HH:mm:ss tt”)

“16:15:00 PM” (default)

ToText({timefield}, “HH:mm:ss tt”, “morning”, “night”)

“16:15:00 night”

ToText({timefield}, “hh:mm:ss t”, “am”, “pm”)

“04:15:00 p”

 
 

A Formula to Determine if Date in Current Week

On occasion, I will need to create a report showing only events that occur in the current week. (For example, all deliveries scheduled for the week.) Note: This assumes you are using Sunday as the first day of the week.)

Here’s how to set this up:

I frequently find I need to create reports that show if a date field falls on a specific day, for example if an event occurred yesterday. Here’s how:

  1. Create a new variable called WeekStart and enter the formula:

    CDateTime (CurrentDate-(DayOfWeek(CurrentDate)+1),CTime(00,00,00) )

    This uses a few different functions to calculate the date of the first day of the week. It starts with the current date (say, Friday, 9/10.) Then it uses the DayOfWeek function to get the value for the day of week (Sunday is 1, Saturday 7, so Friday is 6.) Subtracting 6 from 9/10 gives us 9/4 which is the date of the previous Saturday. We add 1 to get Sunday.) Finally, It then sets the time to 0:00:00 which is the start of the day (or midnight of the previous night.)

  2. Create a new variable called WeekEnd and enter the formula:

    CDateTime (CurrentDate+(7-(DayOfWeek(CurrentDate)),CTime(23,59,59) )

    This uses a few different functions to calculate the date of the last day of the week. It starts with the current date (say, Friday, 9/10.) Then it uses the DayOfWeek function to get the value for the day of week (Sunday is 1, Saturday 7, so Friday is 6.) Subtracting 6 from 7 gives us 1. Add that to 9/10 and we get Saturday’s date, 9/11. It then sets the time to 23:59:59 which is just before midnight at the end of the day.

  3. Now, you can select your records on your date field (Event_Date in our example):

    {Event_Date} in {@WeekStart} to {@WeekEnd}

Create a Formula to Select Dates that Occurred Yesterday

I frequently find I need to create reports that show if a date field falls on a specific day, for example if an event occurred yesterday. Here’s how:

  1. Create a new variable called YesterdayMin and enter the formula:

    CDateTime (CurrentDate-1,CTime(0,0,0) )

    This variable takes the current day (today), and subtracts 1 to get yesterday. It then sets the time to 0:00:00 which is the start of the day (or midnight of the previous night.)

  2. Create a new variable called YesterdayMax and enter the formula:

    CDateTime (CurrentDate-1,CTime(23,59,59) )

    This variable takes the current day (today) and subtracts 1 to get yesterday. It then sets the time to 23:59:59 which is just before midnight at the end of the day.

  3. Now, you need to create a formula to evaluate your date field (Event_Date in our example):

    If {Event_Date} in {@YesterdayMin} to {@YesterdayMax} then "yesterday" else "not yesterday"

    You can then use the results of this formula to group or select your records.

Accounting for the Weekend

The above works well if your report runs every day. However, what if the report doesn’t run on the weekend, and you need to include Saturday and Sunday events in your Monday report? You just need to modify your YesterdayMin variable to include multiple days if it’s Monday. You can do this using the DayOfWeek function which evaluates the current day of the week (1 is Sunday, 2 is Monday, and so on.)

Change YesterdayMin to have the following formula:

If DayOfWeek (CurrentDate)=2 then CDateTime (CurrentDate-3,CTime(0,0,0) ) Else CDateTime (CurrentDate-1,CTime(0,0,0) )

Hiding Calculations in Subreports

I have put together several reports where a count or sum is performed in a subreport. I want to use the value returned by the sub report, but don’t want to show the subreport. However, if I suppressed the subreport or the section it appeared in, the value became 0 and the report no longer worked.

I also tried setting all the text to white, making the subreport very small and turning of the "Can Grow" option. This worked if the subreport only runs a single time, but for some of my reports I ended up with very large white spaces.

Fortunately, there is a way to hide the subreport but still have it run:

  1. Create your subreport, and use the Section Expert in the subreport to hide or suppress all sections.
  2. Return to the main report, select the section that holds the subreport and use the Section Expert to suppress the section if blank.
  3. The subreport will be hidden in the report, but the calculations will still be performed.

How to Add a Thermometer Rating Using Repeated Characters

We previously looked at adding a rating icon using the smiley faces available in the Wingdings font. You can also quickly create a thermometer rating by using the ReplicateString function.

The Replicate String Function

The ReplicateString function lets you define a string to repeat, and the number of times to repeat it. The power of the function is that the number of times to repeat the string can be a formula. Continue reading ‘How to Add a Thermometer Rating Using Repeated Characters’ »

Parameters: Create a Report that Selects Records on One or More Text Values

For our next example, we’re going to create a report that lets the user select one or more text values to filter on. We will create a parameter that lets the user select one or more Customer to show in the report.

Create the Parameter

Follow these steps to create a new parameter:

  1. Make sure that your Field Explorer is visible. (If not, select View|Field Explorer.)
    clip_image001
  2. Right-click on Parameter Fields and select New… from the pop-up menu.
    clip_image002
  3. You see the Create Parameter Field dialog.
    clip_image003
  4. Enter a Name for the field. This is the actual name of the field, not what appears to the user. For our example, we’ll use Customer.
  5. Enter the Prompting Text. This is the text the user sees, prompting them to enter data. Enter Customer(s) to view.
  6. Select the Value Type of the parameter. For our example, it is String.
    clip_image004
  7. Under Options, check Allow multiple values. This will let the user enter more than one client.
  8. Leave the value choice to Discrete Value(s); our users will select one or more customer name but not ranges.
  9. Then, click on Default Values. We will begin by entering our current Customer List. (Note: We are adding the customer list as it is now, if we add clients later on they will not appear on the list unless you update the report.)
  10. You see the Set Default Values dialog. Select the Table and Field that contain the options you want to include (this should be the same as the field you are filtering on.) You can use the move all field to move all the options into the right-hand screen.
    clip_image005 
    clip_image006
  11. Click on OK, then OK to return to your report.

Select on the Parameter

Next, modify your selection formula to find only those records that have a customer selected by the user.

  1. Select Report|Selection Format|Select Records. You’ll see the Record Selection Formula Editor.
  2. Enter the following formula (you’ll replace {Customers.CompanyName} with the field you are selecting on)
    {Customers.CompanyName} in {?Customer}
  3. Click Save and Close to modify the selection formula. You may be prompted to enter values for the Customer. Go ahead and enter values and preview your report.
    clip_image007

Display the Parameter in the Title

Displaying the parameter in the title is a little more complicated when you let the user select multiple options; this turns the parameter into an array and means you’ll have to use the Join function. (See my earlier post for more details.)

Enter the following into your ReportTitle formula:

‘Orders placed by ‘ & Join ({?Customer}, ‘, ‘)

Preview your report:

clip_image008

Parameters: Creating a Report that Selects Records on a Date Range

In this example, we will create a report that selects records based on a date range entered by the user.

Creating a Parameter

In our example, we’ll use a report that shows customer orders, and we want to limit our report to orders entered in a selected date range. This means we’ll need two date parameters: a start date and an end date.

Continue reading ‘Parameters: Creating a Report that Selects Records on a Date Range’ »

Working With Parameters: Part 1

Parameters can help you create flexible, powerful reports. This series will introduce parameters and explain how to use them.

What is a Parameter?

A parameter is a variable that lets your report users select the value of at run time. For example, you could set a parameter that lets them select the date range, or to select a record number, or the specific staff member to review.
Parameters let you create more flexible reports–you don’t have to hard-code data, you can let the user select what they want to see when they run the report.

Parameters are defined as follows:

Name The name of the field. When Parameters appear on your Crystal Reports in design view, the field name is preceded by a question mark.)
Prompting Text The text the user sees when prompted to enter the data.
Value Type The type of data stored in the parameter. (Boolean, Currency, Date, DateTime, Number, String, or Time)
Allow Multiple Values If you select this option, the parameter will be treated as an array. This is useful, for example, if you are letting users select specific salesmen to review; they don’t have to run the report once for each salesman, they can enter all salesmen at one time.

If you do select this, you will need to treat your parameters as arrays (for example, to include the parameter in a title will require you to use the Join function.

Discrete Value(s) Selecting this option lets users enter a single, discrete value. For example, a single date (3/12/10), or a single record number (12332).

If you have also selected Allow Multiple Values, users will be able to enter several discrete values (12332, 12456, 12743).

You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s)

Range Value(s) Selecting this option lets users enter a range of values. For example, a date range (3/12/10-4/12/10), or a range of record numbers (12332-12338).

If you have also selected Allow Multiple Values, users will be able to enter several ranges of values (12332-12338, 14857-15000).

You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s)

Discrete and range value(s) If you have selected Allow Multiple Values, this option will become available.

Selecting this option lets users enter a combination of discrete and range values. For example, (12332-12338, 14435).

You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s)

Set default values This option lets you enter default values for the parameter. For example, if you are using a parameter to determine what field to group on, you may define the choices available to the user.

On the other hand, if you are asking users to select a date range, you probably don’t want to give them default values.

Allow editing of default values If this is checked, users can edit the default values, or enter their own. If it is not checked, they are limited to the values you have entered.

How to Display when a Record is Continued on Another Page

I usually try to format my reports so that a record has a clearly delineated start and end (either a line below, or shading the group header.) However, it can still get confusing if a record is continued, especially if the record contains a lot of text. Here is a quick way to use a formula to add a “Continued on Next Page” blurb when a record is broken by a page break: Continue reading ‘How to Display when a Record is Continued on Another Page’ »

Adding a Checkbox to a Report

If you want to display a checkbox on a report, you can create a formula to show an unchecked box or a checked box (using the desired WingDing character) depending on the values of the field.

You will need to create a new formula, with an appropriate name (for example, Checkbox) that defines what character code to show, depending on the value of the field. For example, if you want to show a checked box if the field MyField is true, and an unchecked box if it is False, you would create: Continue reading ‘Adding a Checkbox to a Report’ »