Formulas

Sometimes data can be missed from data sources even though you have to put them on your report.  Let's take addresses for example.  A typical address contains the street, city, country, and postal code.  However, addresses from other countries do not have states.  Sometimes companies have an extra line that includes PO Boxes.  Because of these exceptions, the word Null may appear all over the report if fields are used.  Unwanted gaps and spaces will also appear, distorting the address format.  Therefore, is it necessary to use formulas in reports. 

Activating the Formula Editor

• Catalog Browser:   Click on the button in the Catalog Browser toolbar.  You can also right click on Formula in the Data tab and select New Formula.  A menu will prompt you for a new name for your formula. Click the OK button and the Formula Editor will appear.  If you would like to activate the Formula Editor for a previous formula, select a formula, right click and select Edit Formula.
  
  
• Editor Toolbar: Click on the button in the Editor Toolbar window.  The Insert DBFields menu will appear with the Formula tab.  To bring up the Formula Editor, either select a formula and click on the Modify button or click on the New button.  
  
  
• Menu: Click Insert/DBField...  The Insert DBField will appear.  Click on the Formula tab.  To bring up the Formula Editor, either select a formula and click on the Modify button or click on the New button.  
  . 

The Formula Editor
The upper left column will show how to display all the previous queries, tables, fields, summaries, and previous formulas that are available to you depending on what fields you placed in the Working Set.  The upper center and the upper right column contain predefined functions which can be used by double clicking on the desired function.

The bottom menu allows you to type in specific formulas.  Click on the syntax hyperlink to view a listing of formula syntax.

The Check button allows you to check the syntax of your report before you place it in your report.


Formula examples:

• Address12
  
  if ( IsNull( @"Address1" ) )
      @"Address2"
  else if ( IsNull( @"Address2" ) )
      @"Address1"
  else
      @"Address2" + ", " + @"Address1"
  
  
• city_state_postcode
  
  if ( IsNull( @"City" ) )
  {
  trim(@"Region") + ", " + trim(@"Postal Code")
  }
  else if ( IsNull( @"Region" ) )
  {
  trim(@"City") + ", " + trim(@"Postal Code")
  }
  else
  {
  trim(@"City") + ", " + trim(@"Region") + ", " + trim(@"Postal Code")
  }