Create a Report Part 5: Calculated Fields and Format Strings

In this final article we're going to be discussing some ways to format and/or change how the information in fields displays.

Calculated Fields

A calculated field does not display the data as it is saved in the database. Instead, we use the data to calculate what to display using an expression. In this report, we'll be using an Iif statement but there are many more functions available. For detailed documentation, please see this article on the DevExpress website.

In the status field, right now we are simply showing the data contained in the is_definite field, which is a true or false value. Instead, we want to show the status name. With the calculated field we will say that if this certain status is true, display this status name but if it is false do something else. We'll show you the actual expression when we get there!

When we built our data source, we only included the is_definite field. Since we may have events of other statuses in our report, the first thing we'll do is add the fields for those other statuses to our data source.

To update the data source:

1. Click the field list icon in the far right navigation, click the arrow by the data source, and then click our data source to see its icons. Click the pencil to open the SQL Data Source Wizard.
2. Click Run Query Builder....
3. In vw_events_lite, select the other four statuses:
   • is_tentative
   • is_prospect
   • is_internal
   • is_blackout
   And then click OK.
4. In the Query Builder, click Next and then Finish to save your work and return to the report designer. You should now see all five status fields.

Now that we have all of the status fields in our data source, we can build the calculated field.

To build the calculated field:

1. In the field list, click the Add calculated field icon by the data source.
2. The field is listed; select it and click the pencil to edit it.
3. Add the field information:
   • Name: For this field, we'll call it Status. Note that when you tab or click away from this, your field will drop down in the field list since they're ordered alphabetically by name!
   • Display Name: Leave this field blank.
   • Field Type: String
   Once you've added the field information, click the three dots at the right of the Expression field to open the Expression Editor.
4. Now we have to build our expression. For status, we will nest Iif statements so that all the statuses display correctly. Once you've added the expression, click OK.
   • Here is the same expression in text so that you can copy and paste it:
     
     Iif([is_internal]==1, 'Internal',
  Iif([is_blackout]==1, 'Blackout',
    Iif([is_tentative]==1, 'Tentative',
      Iif([is_definite]==1, 'Definite',
        Iif([is_prospect]==1,'Prospect' ,'Other' )
      )
    )
  )
 )
     
     Note that the expressions are applied in the order that they are listed. We put internal and blackout so that they display as internal or blackoiut rather than their tentative or definite space status.
5. Drag the Status calculated field to the cell where is_definite is and drop it - the Status field will replace it.
6. Preview the report to make sure the status is displaying as you wish.

Format Strings

In the report preview above, you'll notice that we are displaying the start and end date, but since this is a header, it would be nice to have them display as 8/2/2019-8/4/2019. To do that, we'll use a format string. A format string is a way to print and combine data into a specific format.

1. Format strings are placed in a single field, so we need to remove one of the existing date fields. To remove a field, select the field and then click the X in the header.
2. Select the remaining date field. In side bar go to Properties Actions. In the Expression field, click the three dots on the right to open the Expression Editor.
3. Add the Format String:
   1. Clear the editor.
   2. Start typing FormatString and select it from the list.
   3. Add the placeholders for the date fields and date field format, and then specify which fields the zero and one refer to. See the gif below for how this is done, or you can copy and paste in this code: FormatString('{0:MM/dd/yyyy} - {1:MM/dd/yyyy}',[start_date_time],[end_date_time])
   4. Click OK to save the format string
   
4. Remove the contents of the Text Format String field.
5. Note that the date field now contains the format string.
6. Preview the report to see how the start and end dates will now display

In the report preview above, you'll see that while our date range looks great, we're also displaying a date range for single day events. Since the start and end date are the same, we'd prefer to just show a single date. We can do that by adding an Iif statement to the format string (it will be in the same format as our calculated field: logical test, value if true, value if false):

Iif(GetDate([start_date_time])=GetDate([end_date_time]),
 FormatString('{0:MM/dd/yyyy}',[start_date_time]),
 FormatString('{0:MM/dd/yyyy} - {1:MM/dd/yyyy}',[start_date_time],[end_date_time]))

Follow the steps above to select the date field, open the expression editor, and replace the original format string with the one above. Preview the report to check that single day events will display differently from multi-day events.

If you'd like an easier way to format date ranges, we've built a custom function. In the expression editor, open functions custom and then double click on DateRange(,) to add it to the editor. Add the date fields and you're good to go!

General Formatting

You are also able to do more basic formatting: font, font size, bold/italic, background colors, alignment, etc. If you are used to formatting in other programs, you'll be able to do it here easily as well. Some notes:

• To update the title of the report, select the cell and open Properties Actions. Update the text in the Text field.
• To swap out our logo for yours, select the logo cell and click the three dots on the right of the Image Source field (also in Properties Actions). An uploader will open and you'll be able to upload a logo file from your computer.
• You are able to change the width of columns. Select a cell and hover over the box on its edge (you'll see your cursor change). Click and drag the cell/column to your desired width.
• To format a cell or table of cells, select the cell/table (to select the whole table click in the margin of the band and drag your mouse to the table). Update the formatting as you wish in the Properties Appearance menu.
• There are also pre-built styles which you can apply to cells. Select the cells and choose a style from Properties Style.

And our report is complete!

Additional Reading

Our report engine is a third-party program called DevExpress. If you'd like to deep dive into reporting, their documentation can be useful. Please be aware that you may see some small differences between the what is in their documentation and what is in Elite, as we have made some customizations.