Adding Custom Fields with Numbers or Dates to Report Writer

If you try to do calculations on your ConnectWise Manage custom fields in Report Writer, you’ll notice that custom fields that are supposed to be formatted as a number or date type just come through as text. ConnectWise stores custom fields as the SQL data type varchar, instead of the relevant type you selected for your custom field. Here’s how to convert the fields so you can use them in your calculations and filters.

Convert Custom Field to Currency or Other Decimal Amounts

If your custom field is meant to be a decimal number, click on the gear icon to the right of your custom field and enter SQL code similar to below in the Expression box. Then, tell Report Writer what type of expression it is. The 10, 2 below means that there can be up to 10 digits total on both sides of the decimal point and 2 digits to the right. For more information on SQL decimal data type, check out this post on SQL Shack.

Note that the field you place inside the brackets refers to the actual database field name, not the description you gave it on the Report Writer fields tab.

CONVERT(decimal(10,2),[Custom Amount])

Convert Custom Field to Date

For Date custom fields, follow the steps above to enter an expression for the field and enter it in the format below. For the Expression type, choose Date.

Filtering Based on Your Custom Field

To perform calculations or filter based on your custom field, make sure you’re using your version of the field, rather than the built in varchar-based version. Go to the Filters tab and scroll all the way to the bottom of the Filter Field dropdown to get to your modified fields. The ones with square brackets around them are your modified fields.

In the date field example above, I can now use the calendar filter in Report Writer.

Want to do more?

Do you have any other Report Writer formatting issues you’ve been struggling with? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.