Report Designer - Numeric Formatting

Any numeric variable or expression has particular properties that affect how it appears on a document. These properties can be changed, if required. For example, you might want to change the number of decimal places displayed, or the format in which a date appears. This article explains how to amend the numeric properties of an object within Report Designer.

This article assumes that you have located the report or layout that you want to amend and opened it in Sage Report Designer. If you need further help with this, please refer to article 16965.

The following steps contain everything you need to help you amend the numeric properties of an object on a document.

Note: If any of the panes mentioned in this section are not visible, open the View menu and select the required pane. If the pane doesn't appear, you can reset the Report Designer view to its default. For further information about this, please refer to article 27400.

To alter the numeric properties of an object

  • Select the numeric object > Properties pane > select the option you want to change > click the finder button or drop-down arrow > amend the settings as required.

    Note: For further information about the options available, please refer to the following section.

You've now amended the numeric properties of an object and should make any other changes you require to the document then save your changes. For further information about how to save the amended document, please refer to the section To save the changes to your document later in this article.

The numeric options available

In the Properties pane you can select the following numeric options:

Formatting

Note: To open the Formatting window, from the Properties window, click Formatting then click the finder button .

The Formatting window contains the following tabs:

  • The Date & Time tab

    Provides drop-down lists of various formats for date and time variables. For further information about date properties please refer to article 12646.

  • The Numeric tab

    Provides options governing the style and format of the numeric object. The following options are available:

    Style This option changes the output so that either values, words or a logical yes or no option appears.

    Suppress if zero Suppress if zero is both an Object Change and a Global Change. To suppress the numeric variable if it has a value of zero, select this check box. The line containing the zero value prints, but there is a blank space where the zero value would have appeared.

    Display leading zero You can use this option to suppress values starting with zeros, for example, if the box is not selected a value of 0.5 show as .5.

    Maximum and Minimum decimal places This option is an Object change only. It allows numbers stored to a high precision to be displayed practically when running reports and layouts. For example, an invoice may contain an item with a quantity of 25000 and an item with a quantity of 0.00016. By setting the Minimum decimal place to 0 and the Maximum decimal place to 6, the quantities appear as 25000 and 0.00016, instead of 25000.00000 and 0.000160.

    Format

    From the drop-down list, choose how you want to display the numeric object. The following key applies to the options available:

    1.1 - Represents the numeric value.

    £ - Represents the currency symbol.

    - Represents the negative symbol.

    ( ) - Represents brackets that override the negative symbol.

    Part

    From the drop-down list, choose the part of the numeric value to be displayed. The default is set to Whole, so that the whole of the numeric value appears. This function is commonly used for cheque printing.

    For example, if the numeric value is 123,456,789.90. The following applies:

    Whole - The whole value appears: 123,456,789.90.

    Decimal - Only the numbers after the decimal point appear: .90

    Ones - Only the unit component value appears: 9.

    Tens - Only the tens component value appears: 8.

    Hundreds - Only the hundreds component value appears: 7.

    Thousands - Only the thousands component value appears: 6.

    Tens of Thousand - Only the tens of thousand component value appears: 5.

    Hundreds of Thousand - Only the hundreds of thousand component value appears: 4.

    Millions - Only the millions component value appears: 3.

    Tens of Millions - Only the tens of millions component value appears: 2.

    Hundreds of Millions - Only the hundreds of millions component value appears: 1.

    Negative - Sage Accounts v15 and below

    Enter into this box the single character to be used as the symbol to indicate a negative value for the numeric variable. A minus sign is commonly used for this purpose.

    For example:

    No negative symbol:

    1,000,000.00

    A minus sign as the negative symbol:

    -1,000,000.00

    Note: The positioning of the negative symbol in relation to the numeric variable or expression is determined by the setting for Format.

    Sage 50 Accounts 2010 - The Windows settings are used to determine the symbol used for negative values.

    Currency - Sage Accounts v15 and below

    Enter a character to be used as the symbol for the currency of the numeric object. Sage Report Designer defaults to no currency symbol. A pound sign is commonly used for the UK market.

    For example:

    No currency symbol:

    1,000,000.00

    A pound sign as the currency symbol:

    £1,000,000.00

    Note: The positioning of the currency symbol in relation to the numeric variable or expression is determined by the settings for Fixed Currency Symbol and Numeric Format.

    Thousands - Sage Accounts v15 and below

    Enter the single character to be used as the symbol to separate thousands in the numeric value. A comma is often used for this purpose.

    For example:

    No thousands symbol:

    1000000.00

    A comma used as the thousands symbol:

    1,000,000.00

    Sage 50 Accounts 2010 - The Windows settings are used to determine the symbol used for thousands.

    Decimal - Sage Accounts v15 and below

    Enter the single character to be used as the symbol to separate the decimal places from the remainder of the numeric object. A full stop is commonly used for this purpose.

    For example:

    No decimal symbol:

    1,000,00000

    A full stop used as the decimal symbol:

    1,000,000.00

    Sage 50 Accounts 2010 - The Windows settings are used to determine the appearance of decimals.

    Show DR/CR for negative numbers To display the numeric value prefixed with either DR or CR, depending on whether the value is a debit or a credit, select this option.

    Note: In Sage 50 Accounts 2008 and 2009 this option is called DR/CR.
    Show as currency - Sage Accounts v16 and above

    To display a currency symbol against the values, select this check box.

    Report Designer uses the default currency symbol from the Windows settings. To enter a different currency symbol, select the Use custom currency formatting check box, enter the required symbol then click OK.

    Use custom thousand separator - Sage Accounts 2011 and above To display a thousands separator that is different from the Windows default, select this check box. Enter the required thousands separator symbol, then click OK.
    Preview This displays a preview field into which you can enter a value. This allows you to see the effects that amending the numeric options would have on this value.

  • The Fill Format tab

    Provides options governing the fill format which applies to the numeric object. The following options are available:

    Fill format

    You can use this to enter a fill format against numbers, for example on cheques for security purposes. From the drop-down list, choose from the following options:

    • Pre-fill - posts characters before a value appears
    • Post-fill - posts characters after a value appears
    • Surround - posts characters both before and after a value appears
    • Secure cheque - enhanced security fill mode which features two fill characters to the left of the value and fully fills the rest of the variable.

    Fill length

    Applies if you choose Pre-fill, Post-fill or Surround from the Fill format drop-down menu.

    Fill all available spaces - To fill all of the available space with fill characters, select this check box.

    Characters - If you don't select Fill all available spaces, in here you can enter the number of fill characters to be used.

    Characters If the fill length is not selected, enter the number of fill characters to be used here.

    Fill character Specify the character to be used in the fill.

     

Function

The Function drop-down list controls how the numeric value is used in the document.

The following options are available:

None

To display the value held within the accounts data files, choose this option.

Sum

To add together all occurrences of the variable or expression, choose this option. If the Function of the object is set to Sum in the details section of the document, the object produces a running total. If the Function of the object is set to Sum in a group or page footer section of the document, the object produces a total for the group or document.

Avg

To average all occurrences of the variable or expression, choose this option. If the Function of the object is set to Avg in the details section of the document, the object displays the average value as it moves down the list. If the Function of the object is set to Avg in a group or page footer section of the document, the object produces an average for the group or document.

Min

To display the minimum value of the variable or expression, choose this option. If the Function of the object is set to Min in the details section of the document, the object displays the minimum value so far as it moves down the list. If the Function of the object is set to Min in a group or page footer section of the document, the object displays the minimum value on the group or document.

Max

To display the maximum value of the variable or expression, choose this option. If the Function of the object is set to Max in the details section of the document, the object displays the maximum value so far as it moves down the list. If the Function of the object is set to Max in a group or page footer section of the document, the object displays the maximum value on the group or document.

Count

To display the number of fields rather than its value from the accounts data files, choose this option. If the Function of the object is set to Count in the details section of the document, the object displays the number that this value is in the details list. For example, the second occurrence of a variable appears as 2. If the Function of the object is set to Count in a group or page footer section of the document, it displays the number of occurrences on the group or document.

Count All

To display the number of times that the variable or expression appears in the document, choose this option.

Count Unique To display the number of unique instances of a variable, choose this option.

Sign

This option allows you to control the display of a numeric value depending on whether the value is positive or negative.

The following options are available:

Signed

To display the numeric values as they are held in the data files, choose this option.

For example, within the Customers module a Sales Invoice shows as a positive and a Sales Credit shows as a negative, but in the Suppliers module a Purchase Invoice shows as a negative and a Purchase Credit shows as a positive.

Unsigned

To display every numeric value as a positive, choose this option.

Reversed

To sign the numeric values as the opposite to the information in the data files, choose this option. For example, within the Suppliers module a Purchase Invoice would show as a positive and a Purchase Credit would show as a negative.

Debit

To display only positive values, choose this option. All negative values are displayed as zero.

Credit

To display only negative values, choose this option. All positive values are displayed as zero.

Apply Sign After Group

The Apply Sign After Group drop-down list controls the display of numeric objects whose Function is set to Sum and whose Sign is set to Debit or Credit.

The following options are available:

False

To sum the contents of each group section, choose this option. The Function is set to Sum - the group totals sum the contents of each group section.

True

To net the debit and credit then display the balance in the appropriate column, choose this option.

Is Cumulative - Report Designer v1.3 and above

The Is Cumulative option is used in conjunction with functions. If set to Yes, the value is shown as a running total, if set to No, the value is totalled for the entire group. When set to Automatic, the report determines the appropriate setting depending on the variable.

To save the changes to your document

  1. Report menu > Report Properties > enter a new name and description for the report > OK.

    Report Designer v1.3 and below - Report Explorer pane > select the report name > Properties pane > Name > enter a new name for the report > Enter.

    Tip: You can check your Report Designer version in Help > About > Application > File Version.

  2. File > Save As > File name > enter a new name for the document > Save in > browse to the required location for your document. For further information about where to save your document, please refer to article 16965 .

  3. Save > File > Exit.

You have now saved your amended document.

Tip: If you have any difficulty finding a recently saved document, you can open it from the Report Designer Recent files list and save it to the required location. To open a recently viewed report, from the Sage Accounts desktop open the Tools menu, choose Report Designer then from the Recent files list, click your required report.

Steps

The following detailed steps contain everything you need to help you amend the numeric properties of an object on a document.

This article assumes that you have located the report or layout that you want to amend and opened it in Sage Report Designer. If you need further help with this, please refer to article 16965.

Note: If any of the panes mentioned in this section are not visible, open the View menu and select the required pane. If the pane doesn't appear, you can reset the Report Designer view to its default. For further information about this, please refer to article 27400.

To alter the numeric properties of an object

  • Select the numeric object > Properties pane > select the option you want to change > click the finder button or drop-down arrow > amend the settings as required.

    Note: For further information about the options available, please refer to the following section.

You've now amended the numeric properties of an object and should make any other changes you require to the document then save your changes. For further information about how to save the amended document, please refer to the section To save the changes to your document later in this article.

 

The numeric options available

In the Properties pane you can select the following numeric options:

Formatting

Note: To open the Formatting window, from the Properties window, click Formatting then click the finder button .

The Formatting window contains the following tabs:

  • The Date & Time tab

    Provides drop-down lists of various formats for date and time variables. For further information about date properties please refer to article 12646.

  • The Numeric tab

    Provides options governing the style and format of the numeric object. The following options are available:

    Style This option changes the output so that either values, words or a logical yes or no option appears.

    Suppress if zero Suppress if zero is both an Object Change and a Global Change. To suppress the numeric variable if it has a value of zero, select this check box. The line containing the zero value prints, but there is a blank space where the zero value would have appeared.

    Display leading zero You can use this option to suppress values starting with zeros, for example, if the box is not selected a value of 0.5 show as .5.

    Maximum and Minimum decimal places This option is an Object change only. It allows numbers stored to a high precision to be displayed practically when running reports and layouts. For example, an invoice may contain an item with a quantity of 25000 and an item with a quantity of 0.00016. By setting the Minimum decimal place to 0 and the Maximum decimal place to 6, the quantities appear as 25000 and 0.00016, instead of 25000.00000 and 0.000160.

    Format

    From the drop-down list, choose how you want to display the numeric object. The following key applies to the options available:

    1.1 - Represents the numeric value.

    £ - Represents the currency symbol.

    - Represents the negative symbol.

    ( ) - Represents brackets that override the negative symbol.

    Part

    From the drop-down list, choose the part of the numeric value to be displayed. The default is set to Whole, so that the whole of the numeric value appears. This function is commonly used for cheque printing.

    For example, if the numeric value is 123,456,789.90. The following applies:

    Whole - The whole value appears: 123,456,789.90.

    Decimal - Only the numbers after the decimal point appear: .90

    Ones - Only the unit component value appears: 9.

    Tens - Only the tens component value appears: 8.

    Hundreds - Only the hundreds component value appears: 7.

    Thousands - Only the thousands component value appears: 6.

    Tens of Thousand - Only the tens of thousand component value appears: 5.

    Hundreds of Thousand - Only the hundreds of thousand component value appears: 4.

    Millions - Only the millions component value appears: 3.

    Tens of Millions - Only the tens of millions component value appears: 2.

    Hundreds of Millions - Only the hundreds of millions component value appears: 1.

    Negative - Sage Accounts v15 and below

    Enter into this box the single character to be used as the symbol to indicate a negative value for the numeric variable. A minus sign is commonly used for this purpose.

    For example:

    No negative symbol:

    1,000,000.00

    A minus sign as the negative symbol:

    -1,000,000.00

    Note: The positioning of the negative symbol in relation to the numeric variable or expression is determined by the setting for Format.

    Sage 50 Accounts 2010 - The Windows settings are used to determine the symbol used for negative values.

    Currency - Sage Accounts v15 and below

    Enter a character to be used as the symbol for the currency of the numeric object. Sage Report Designer defaults to no currency symbol. A pound sign is commonly used for the UK market.

    For example:

    No currency symbol:

    1,000,000.00

    A pound sign as the currency symbol:

    £1,000,000.00

    Note: The positioning of the currency symbol in relation to the numeric variable or expression is determined by the settings for Fixed Currency Symbol and Numeric Format.

    Thousands - Sage Accounts v15 and below

    Enter the single character to be used as the symbol to separate thousands in the numeric value. A comma is often used for this purpose.

    For example:

    No thousands symbol:

    1000000.00

    A comma used as the thousands symbol:

    1,000,000.00

    Sage 50 Accounts 2010 - The Windows settings are used to determine the symbol used for thousands.

    Decimal - Sage Accounts v15 and below

    Enter the single character to be used as the symbol to separate the decimal places from the remainder of the numeric object. A full stop is commonly used for this purpose.

    For example:

    No decimal symbol:

    1,000,00000

    A full stop used as the decimal symbol:

    1,000,000.00

    Sage 50 Accounts 2010 - The Windows settings are used to determine the appearance of decimals.

    Show DR/CR for negative numbers To display the numeric value prefixed with either DR or CR, depending on whether the value is a debit or a credit, select this option.

    Note: In Sage 50 Accounts 2008 and 2009 this option is called DR/CR.
    Show as currency - Sage Accounts v16 and above

    To display a currency symbol against the values, select this check box.

    Report Designer uses the default currency symbol from the Windows settings. To enter a different currency symbol, select the Use custom currency formatting check box, enter the required symbol then click OK.

    Use custom thousand separator - Sage Accounts 2011 and above To display a thousands separator that is different from the Windows default, select this check box. Enter the required thousands separator symbol, then click OK.
    Preview This displays a preview field into which you can enter a value. This allows you to see the effects that amending the numeric options would have on this value.

  • The Fill Format tab

    Provides options governing the fill format which applies to the numeric object. The following options are available:

    Fill format

    You can use this to enter a fill format against numbers, for example on cheques for security purposes. From the drop-down list, choose from the following options:

    • Pre-fill - posts characters before a value appears
    • Post-fill - posts characters after a value appears
    • Surround - posts characters both before and after a value appears
    • Secure cheque - enhanced security fill mode which features two fill characters to the left of the value and fully fills the rest of the variable.

    Fill length

    Applies if you choose Pre-fill, Post-fill or Surround from the Fill format drop-down menu.

    Fill all available spaces - To fill all of the available space with fill characters, select this check box.

    Characters - If you don't select Fill all available spaces, in here you can enter the number of fill characters to be used.

    Characters If the fill length is not selected, enter the number of fill characters to be used here.

    Fill character Specify the character to be used in the fill.

     

Function

The Function drop-down list controls how the numeric value is used in the document.

The following options are available:

None

To display the value held within the accounts data files, choose this option.

Sum

To add together all occurrences of the variable or expression, choose this option. If the Function of the object is set to Sum in the details section of the document, the object produces a running total. If the Function of the object is set to Sum in a group or page footer section of the document, the object produces a total for the group or document.

Avg

To average all occurrences of the variable or expression, choose this option. If the Function of the object is set to Avg in the details section of the document, the object displays the average value as it moves down the list. If the Function of the object is set to Avg in a group or page footer section of the document, the object produces an average for the group or document.

Min

To display the minimum value of the variable or expression, choose this option. If the Function of the object is set to Min in the details section of the document, the object displays the minimum value so far as it moves down the list. If the Function of the object is set to Min in a group or page footer section of the document, the object displays the minimum value on the group or document.

Max

To display the maximum value of the variable or expression, choose this option. If the Function of the object is set to Max in the details section of the document, the object displays the maximum value so far as it moves down the list. If the Function of the object is set to Max in a group or page footer section of the document, the object displays the maximum value on the group or document.

Count

To display the number of fields rather than its value from the accounts data files, choose this option. If the Function of the object is set to Count in the details section of the document, the object displays the number that this value is in the details list. For example, the second occurrence of a variable appears as 2. If the Function of the object is set to Count in a group or page footer section of the document, it displays the number of occurrences on the group or document.

Count All

To display the number of times that the variable or expression appears in the document, choose this option.

Count Unique To display the number of unique instances of a variable, choose this option.

Sign

This option allows you to control the display of a numeric value depending on whether the value is positive or negative.

The following options are available:

Signed

To display the numeric values as they are held in the data files, choose this option.

For example, within the Customers module a Sales Invoice shows as a positive and a Sales Credit shows as a negative, but in the Suppliers module a Purchase Invoice shows as a negative and a Purchase Credit shows as a positive.

Unsigned

To display every numeric value as a positive, choose this option.

Reversed

To sign the numeric values as the opposite to the information in the data files, choose this option. For example, within the Suppliers module a Purchase Invoice would show as a positive and a Purchase Credit would show as a negative.

Debit

To display only positive values, choose this option. All negative values are displayed as zero.

Credit

To display only negative values, choose this option. All positive values are displayed as zero.

Apply Sign After Group

The Apply Sign After Group drop-down list controls the display of numeric objects whose Function is set to Sum and whose Sign is set to Debit or Credit.

The following options are available:

False

To sum the contents of each group section, choose this option. The Function is set to Sum - the group totals sum the contents of each group section.

True

To net the debit and credit then display the balance in the appropriate column, choose this option.

Is Cumulative - Report Designer v1.3 and above

The Is Cumulative option is used in conjunction with functions. If set to Yes, the value is shown as a running total, if set to No, the value is totalled for the entire group. When set to Automatic, the report determines the appropriate setting depending on the variable.

 

To save the changes to your document

  1. Report menu > Report Properties > enter a new name and description for the report > OK.

    Report Designer v1.3 and below - Report Explorer pane > select the report name > Properties pane > Name > enter a new name for the report > Enter.

    Tip: You can check your Report Designer version in Help > About > Application > File Version.

  2. File > Save As > File name > enter a new name for the document > Save in > browse to the required location for your document. For further information about where to save your document, please refer to article 16965 .

  3. Save > File > Exit.

You have now saved your amended document.

Tip: If you have any difficulty finding a recently saved document, you can open it from the Report Designer Recent files list and save it to the required location. To open a recently viewed report, from the Sage Accounts desktop open the Tools menu, choose Report Designer then from the Recent files list, click your required report.


Note: If you are viewing this article from the Sage website, a full list of Printing and Report Designer Ask Sage - How To articles is now available.