Archive for January, 2012

Filters can be used to limit the information you want on a report. This may seem similar to criteria; however filters cannot be entered/amended as the report is run and are always applied to a report.

An example of where a filter would be useful would be a report for Sales Orders. The Sage 200 table structure includes data for different types of orders (Sales Orders, Sales Returns, Quotations and Pro-Formas) within the same tables. In order to generate a report that only contains Sales Order information a filter could be applied.

Filter vs. Criteria

If, as in the above scenario, you always only wanted Sales Order type data then a Filter is the best option. If sometimes you want Sales Orders, but other times you want Sales Returns or a mixture of the other order types, then a criteria would be a better option as you could choose the type of order when generating the report.


It should be noted that Filters can be applied to the whole report, or to a section within a report.

Report Filter vs. Section Filter

A filter entered to the whole Report will not collect unwanted data from the Sage 200 database. A section filter would collect all information from the database, but exclude it based on the specific sectional data. An example of this is within the Aged Debtors (Detailed) report. There is a section that only prints, if the customer uses a foreign currency. So all the data is collected, and it is only shown if the customer uses a foreign currency.


The below document shows how to add a filter to the Sales Orders, Returns, Pro Formas and Quotations (Summary) report, using the Sage 200 Report Designer.

Talking Sage 200 – Report Filters

What would you change about Sage 200? Or what functionality would you add?

Let me know you ideas and thoughts.

Here is a taster: When a Customer account is put ‘On Hold’, an option is available to also put the customers Live Sales Orders ‘On Hold’.

Using the Sage 200 Report Designer you can choose to sort the data on your reports, into a particular order. For example, you may create a report that shows a list of Sales Orders and want the list to be sorted by Sales Order Date.

Adding a sort to a report, allows you to put the data into an order that is logical for the reports intentions, therefore making it easier for you to analyse.

When a sort is applied it affects the display of data within each section.

NB. It does not affect the order of the sections/groups, just the data within.

The example below is based on the Sales Order, Returns, Pro Formas and Quotations Report (Summary). This is grouped by Customer, and then lists the orders for each customer. The sort we are going to apply will change the order in which the Sales Orders are displayed, for each customer.

Talking Sage 200 – Sorting a Report

Using the Sage 200 Report Designer it is possible to amend criteria available on reports. The majority of standard reports are pre-set with criteria options.

Report Criteria

By using the report designer you are able to Enable or Disable the existing options, Modify the way existing criteria works, Add new criteria or Remove existing criteria.

The document below shows you how to do it, with an example of how to amend the Sales Ledger Account Details report to include a criterion for Credit Position.

Talking Sage 200 – How to Configure Report Criteria in Report Designer

Particularly for Sage 200 end users, you may be more used to seeing an IF statement lay out as below:

          If ( SOPInvoiceCredit.SOPInvoiceCreditTypeID = 0,  1, -1 )  * SOPInvoiceCredit.InvoicedGrossValue

This is how excel and indeed the old Report Designer (Pre 2010) would require an IF statement to be entered.

Below is how you would now enter the expression:

          (SOPInvoiceCredits.SOPInvoiceCreditTypeID = 0 ? 1 : -1) * SOPInvoiceCredits.InvoicedGrossValue

In this example we have said If the SOPInvoiceCreditTypeID is  0, multiply the InvoicedGrossValue by 1, otherwise multiply it by -1.

This expression would be used when trying to determine if the value on a SOP report is Positive or Negative, where the report is using the Type of transaction therefore displaying everything as Positive.

I encountered an issue, that is known by Sage, with Sage 200 SP2. The issue is with one of the Update Scripts, and the following exception error ocurrs when updating the Company database in System Administration:

 <Message>Failed to run SQL script ‘SchemaMigration’ against company dataset ‘CompanyX’ on server ‘SAGESERVER’. The Exception was: SQL script execution failed. Inner Exception:</Message>


Sage are aware of this problem which they think is due to an issue with inserting BOM document lines into a new table.  The Manufacturing script ‘rolls back’ and so the update process does not complete.

Be aware that the error produced is a generic message which can relate to other problems. 

Sage have released two scripts to help with the identifying and fixing the BOMDocumentLines issue. The ‘FindBOMDocumentLinesToMigrate.sql’ script checks whether there are any BOM Document Lines that need to be migrated.  If there are lines present, you have established that the issue is as above. You are then able to run the ‘UpdateBOMDocumentLines-v2011SP2.sql’ script BEFORE updating the Company database. Bothe scripts are detailled below.

Please note that these scripts should only be used once 2011 SP2 has been installed.


SELECT BomBuildPackageID, BomLineSequence, DocumentURL
      INNER JOIN StockItem ON StockItem.ItemID = BOM_MIG.StockItemID
      INNER JOIN BomRecord ON BomRecord.Reference = StockItem.Code
      INNER JOIN BomBuildPackage ON BomBuildPackage.BomRecordID = BOMRecord.BomRecordID
WHERE BomBuildPackageTypeID = 0 and  BOMLineTypeID = 2


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spr_GetNextPK]’) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
EXECUTE(‘CREATE PROCEDURE [dbo].[spr_GetNextPK](@Value bigint output) AS
 DECLARE @Counter bigint
 SET @Counter = 1 + (SELECT TOP 1 NextValue FROM Counter)
 UPDATE Counter SET NextValue = @Counter

–Return value
SET @Value = @Counter

— MIGRATE MMS BOM Document Lines
IF NOT EXISTS(SELECT * FROM BOMComponentLine WHERE BOMComponentLineTypeID = 4)
 IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BOMLine_MIG]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)

   SELECT BomBuildPackageID, BomLineSequence, DocumentURL
    INNER JOIN StockItem ON StockItem.ItemID = BOM_MIG.StockItemID
    INNER JOIN BomRecord ON BomRecord.Reference = StockItem.Code
    INNER JOIN BomBuildPackage ON BomBuildPackage.BomRecordID = BOMRecord.BomRecordID
   WHERE BomBuildPackageTypeID = 0 and  BOMLineTypeID = 2

  DECLARE @BomDocumentBuildPackageID BIGINT
  DECLARE @BomDocumentLineSequence BIGINT
  OPEN BOMDocumentLineCursor
   FETCH BOMDocumentLineCursor INTO @BomDocumentBuildPackageID, @BomDocumentLineSequence, @DocumentURL

   DECLARE @bomDocumentCounter bigint
   exec spr_GetNextPK @bomDocumentCounter OUTPUT
   –add the comment
   INSERT INTO BomComponentLine(BomComponentLineID, BomBuildPackageID, SequenceNumber, BomComponentLineTypeID,FixedQuantity, Suspended,Notes, DocumentFile, BomCommentLine, ShowCommentOnReport)
    VALUES(@bomDocumentCounter, @BomDocumentBuildPackageID, @BomDocumentLineSequence,4, 0,0, ”, @DocumentURL, ”,0 )
   FETCH BOMDocumentLineCursor INTO @BomDocumentBuildPackageID, @BomDocumentLineSequence, @DocumentURL
  — Close and deallocate cursor
  CLOSE BOMDocumentLineCursor
  DEALLOCATE BOMDocumentLineCursor

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spr_GetNextPK]’) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE [dbo].[spr_GetNextPK]

Using the Sage 200 Report Designer it is possible to create a report, within a report (embed a report within a report) this is done by creating a Sub Report.

The sub report is maintained separately but within a primary report and is linked using criteria. When the Primary report is generated, the sub report is also generated. The sub-report is displayed within the appropriate section of the primary report and on the same page.

Sub Reports can be very useful when wanting to display related information that is contained in different data sources. This gives the ability to report on data within another system such as Sage 200 CRM.

Sub reports can also be an easier way of displaying information from different areas of Sage 200, rather than adding additional tables or views to a report. Also they can help get around grouping and calculation issues encountered by adding lots of Sections to a report.

The attached document guides you through creating a sub report by amending the Stock Levels report to show details of outstanding Purchase Orders for each Stock item at each Location.

Talking Sage 200 – Sub Reports

I recently blogged about how Sage 200 is capable of distributing batches of documents via email. This has prompted the questions from a number of followers regarding the need for Spindle.

For some people the Sage 200 functionality will be enough, at least to start with. However, my personal opinion is that Spindle Professional is a must for any Sage 200 site. It offers so much more than the ability to email a batch of documents and anyone using Sage 200 would benefit from adding Spindle Professional to their system.

During implementations of Sage 200 I nearly always find a reason for talking about Spindle Professional and simply talking about what it does and how it can benefit the customer is often enough for them to purchase it.

The Benefits and Cost Savings that companies gain from using Spindle Professional with Sage 200 are massive.

Here are just 8 of the benefits:

Multiple Distribution methods in one click – Email, Fax or Print documents – You could have a batch of documents (invoices for example), and by printing the batch through Spindle each individual document will be sent out as required.

Send Multiple Copies to Multiple Destinations in One Click – For example, you may have a batch of invoices. You want to send each invoice to its customer as they require (Email, Fax or Print).  At the same time you may want to send a copy to the relevant Sales Rep and/or generate a copy for the accounts department. A Spindle Automation can be set up to do all of this in just one click.

You could even save a copy (Archive) to your Network/CRM/SharePoint at the same time.

Backdrops – Documents can look professional with the use of backdrops replacing pre-printed stationary.

Cost Savings – This really can be huge. Consider this: If you currently send 500 Invoices a month by post and begin to send 60 % of these via Email or fax, you could see savings of around £300 a month.

Now consider all of the other documents that you send out!!!  

Money will be saved on postage, paper, envelopes, pre-printed stationary, printer cartridges….. etc.

Calculate your potential savings yourself:

Time – No more or at least reduced time sorting print outs, stuffing envelopes, franking everything etc.

Use data from Sage 200 – Subjects, attachments and Messages sent with the documents can include information from the Sage 200 database with the use of commands. This allows you to make messages more personal to each company, rather than having a generic message.

Easy to use – For most users all they will need to learn is to select a printer called Spindle, and consider that this can be defaulted on the Sage document templates!

Use with multiple systems – Send Microsoft Office documents for example a letter, or a Mail Merge.

So is Spindle Professional a benefit to Sage 200 users?………….YES!!!