Date Calculations

You can use the InterAction Report Designer to calculate the current date, compare two dates, subtract or add a number of days from the current date, and more. Date calculations are entered in the DataField property of a TextBox control.

Current Date

The easiest date calculation is the current date. To add a date to your report, create the date as a calculated field (see Calculated Fields). Then select the calculated field in the DataField property for the date field.

Current Date Scripts

Script	Date Format
=System.DateTime.Now.ToShortDateString()	4/21/2006
=System.DateTime.Now.ToLongDateString()	Friday, April 21, 2006

All InterAction out-of-the-box reports include the calc_Current_Date calculated field as the current date in the 4/21/2006 format. This date field is included in the PageFooter section of all InterAction out-of-the-box reports. To save time, you can copy and paste the PageFooter from an out-of-the-box report and paste it into a new report.

Advanced Date Calculations

An example of an advanced date calculation is the InterAction out-of-the-box New Marketing Lists for Contacts report. This report displays contacts that were added to marketing lists within the last 30 days.

New Marketing Lists for Contacts Report

The date calculation for the Date_Added field in this report is:

Date_Added>System.DateTime.Now.AddDays(-30)

For the Date_Added field, the system takes the current date (System.DateTime.Now) and looks for dates in the Date_Added field that fall within the last 30 days (AddDays(-30)).

In the New Marketing Lists for Contacts report, this date calculation is used in an If Statement:

=(Marketing_List is System.String) & (Date_Added>System.DateTime.Now.AddDays(-30)) ? Date_Added : null;

This statement says that if the Marketing_List field contains data, and the Date_Added field falls within the last 30 days, then display the data in the Date_Added field. Otherwise, leave the field blank.

This report also excludes contacts that do not meet the date criteria set for the report. See “Excluding Data That Does Not Meet Date Criteria,” later.

Excluding Data that Does Not Meet Date Criteria

The InterAction out-of-the-box New Marketing Lists for Contacts report is a good example of omitting rows of data that do not meet the date criteria set for the report. This report displays contacts that were added to marketing lists within the last 30 days.

To omit rows of data that do not meet the date criteria for your report, create a calculated field with a date calculation for each field in that row of data. If a contact does not meet the date criteria for all fields in the row, that contact is omitted from the report.

For example, in the New Marketing Lists for Contacts report, there are five data fields in the report: Full_Name, Company_Name, Marketing_List, Added_By, and Date_Added.

The following If Statement is written in the calc_Full_Name calculated field for the Full_Name field:

=(Marketing_List is System.String) & (Date_Added>System.DateTime.Now.AddDays(-30)) ? Full_Name : null;

This statement says that if the Marketing_List field contains data, and the Date_Added field falls within the last 30 days, then display data in the Full_Name field. Otherwise, leave the field blank. The same If Statement is written in calculated fields for the remaining fields, replacing Full_Name with the given field name.

In your use, write a similar If Statement in a calculated field for all data fields in the row, replacing Full_Name with the current field name. If the contact does not meet the date criteria for all fields, that row of data is excluded from the report.

InterAction Reports that Use Date Calculations

All InterAction Reports contain the current date in the PageFooter section of the report. Following are some InterAction out-of-the-box reports that use advanced date calculations. To save time, you can copy and paste from existing reports.

InterAction Out-of-the-Box Reports that Use Advanced Date Calculations

Report	Date Calculation
Stagnant Contacts	The following date calculation is written for the calc_Stagnant calculated field, indicating whether a contact is stagnant. The date calculation is part of an If Statement: =Start_Date>System.DateTime.Now.AddDays(-181) ? "Recently Contacted (in order of last contact date) " : "Stagnant (no communication in 6 months, in order of last contact date)" This statement says that if the value in the Start_Date field is greater than 181 days before the current date on the user's machine (more recent than 181 days ago), display the message “Recently Contacted (in order of last contact date).” Otherwise, display “Stagnant (no communication in 6 months, in order of last contact date).”
Recently Opened Opportunities Section (InterAction Opportunities users only)	The following date calculation is written for the calc_Open_Date calculated field. The date calculation is part of an If Statement: =System.String.Equals(Status,"Open") && System.DateTime.Now.AddDays(-90) <= Open_Date ? Open_Date : null; This statement says that if the Status field has the value of Open, and the Open_Date field is greater than 90 days before the current date on the user’s machine, display the value in the Open_Date field. Otherwise, leave the field blank.