Skip to content
logo Knowledgebase

Sage 200 Report Designer - How to create or edit filters

Created on  | Last modified on 

Summary

Sage 200 - How to create filters within Sage 200 Report Designer

Description

This article explains how to get more out of your reports via the use of filters within Sage 200 Report Designer.

NOTE:

This article has been prepared and issued to you as a goodwill gesture only and Sage accepts no liability or responsibility for its use. For further support please refer to your Business Partner.

Resolution

Using Sage Report Designer, you can restrict the records or transactions included on a report, layout, letter or label. To do this you must add a filter. This article explains how to add a filter to a report, layout, letter or label.

TIP:

You can use the Save As Snippet option to save commonly used filters as snippets to add to future reports without the need to recreate them.

TIP:

When you use a filter on a report, the exact filter always applies. If you want to vary the selection when running a report or layout you must add criteria instead of a filter. Using criteria you can make the selection at the point of running the report, and run the same report for different criteria. For further information about this, please refer to article.

These are the basic steps that explain how to create or edit a filter on your report. They include:

To create a new filter - Report Designer v1.3 and above

  1. Report Designer > Report menu > Filters.
  2. From the drop-down lists choose the following:

    Table
    		 Choose the table that contains the variable you want to filter on.
    Variable
    		 Choose the variable you want to filter on.
    Operator
    		 Choose the required operator, for example, Equals or Greater than.
    Value
    		 Enter the relevant value.

    TIP:

    When you use a filter on a report, you can use wildcards and operators.

  3. If required, from the (choose) drop-down list, choose 'And' or 'Or' > repeat step 2 for the second part of the filter.

    TIP:

    To add a more complex filter, click Use Advanced Filter > add the required expression.

  4. To apply the filter, click OK.
 

To edit an existing filter - Report Designer v1.3 and above

  1. Report Designer > Data > Filters.

    NOTE:

    If the existing filter is too complex for the Modify Filter window to display, the Expression Editor window appears. Edit the expression within the Expression Editor > OK.

  2. Amend the values in the drop-down lists as required > OK.

    TIP:

    If the filter contains 2 parts, you can't delete just one part. Instead, click Use Advanced Filter > amend the filter as required > OK.

 

To delete a filter

  1. Report Designer > Data > Filters.
  2. If a Modify Filter window appears, click Clear Filter. Alternatively, if an Expression Editor window appears, click Clear.
  3. To confirm the deletion, click OK.
 

To exclude fully paid transactions from a report

SLPostedCustomerTrans.OutstandingValue <> 0.0

To pick up customers where the balance has exceeded their credit limit

SLCustomerAccounts.AccountBalance > SLCustomerAccounts.CreditLimit

To pick up invoices over a certain value on supplier reports, for example, invoices over £1000

SLPostedCustomerTrans.NetValue > 1000.0

To exclude specific accounts from nominal reports, for example, the VAT values where the VAT is category 26

NLAccountReportCategories.Code <> "26"

To show only certain transaction types over a certain value on a transactional report (the filter would be on the details section

SLPostedCustomerTrans.TransactionTypeShortName in ("SI", "SC") and SLPostedCustomerTrans.NetValue >= 50

Sage Business Partners can now log new cases online!

If you're unable to find the help you require from our online resources, log a new case with us without having to use phone or email. Simply select 'Manage your cases' from the dashboard or visit my.sage.co.uk/cases.

Sage 200 Services

Did you know Sage 200 customers now have access to a range of services direct with Sage. These include a bespoke Report Design Service, a library of How to Webinars and Ideas Portal just to name a few. Explore your potential using Sage 200 Services.