When exporting a report to Excel or CSV, zero values for numeric fields will be represented as the number 0 (screenshot below).

Report screenshot with zeros

A customer had a requirement to that these fields not be populated when the valuie was zero.

Steps to Follow:

1. Exporting The Report
    1.1 Go the the required report on Sage CRM
    1.2 Go to Run report and tick the selection so display options is set to CSV
    1.3 Open the report in Excel
    1.4 Using this excel document follow the steps below

2. Creating the macro (Microsoft Excel 2013)
    2.1 The first time you do this you must make the developer tab visible on the excel homescreen (Go into File -> Options -> Customize Ribbon -> Make sure the box for developer on main tab is checked). It will be visible from then on.
    2.2 Go back to the worksheet and click on developer
    2.3 Press the macros button
    2.4 Type in the name of the macro (do not use spaces) you are going to make and press create
    2.5 in the screen that pops up, replace the text in the window with this:

  Sub replace_zeros()
  For Each cell In ActiveSheet.UsedRange
   If Not IsEmpty(cell) Then
   If cell.Value = 0 Then
   cell.Value = ""
   End If
         End If
  End Sub

    2.6 Exit the screen that popped up and go back to workbook
    2.7 Go into macros again and click run on the macro you created on the current workbook

The zeros should all be removed and all other numbers will remain.

More info:

These steps will need to be carried out each time the report is exported as CSV to excel, as saving macros for universal use on excel is very difficult.


WARNING: The techniques or examples contained in this email are for illustrative purposes only, and have not been tested in a production environment. The techniques or examples used are not necessarily supported features of SAGE CRM, and may not have been previously tested or implemented on a production environment. Before attempting to implement any of these techniques or examples on a production environment, they should be tested thoroughly, as use of these techniques may cause unexpected behaviour. SAGE can accept no responsibility for any issues arising from the use of these techniques. Please ensure that you back up your database and application fully before implementing any changes of this nature.