Archive for February, 2012

A hotfix has been released for Sage 200 2011 SP2 Manufacturing. This fixes an issue where the Create Sales Order Tick Box on the Create WO Dialogue doesn’t work.


I recently posted that CRM Patch D for Sage 200 SP2 was released.

Please now be aware that Sage have found an issue with this patch. It has been removed from their website whilst they investigate the problem. If you have already downloaded the Patch it is obviously recommended that you do NOT install it.

Details of the issue, from the Sage website, are as follows:

The update tool reads information in IIS to determine where the physical directory is for the Sage 200 web services. In some cases when an upgrade from Sage 200 2010 has been done there are two instances of Sage200WebServices, one in the default web site and the other in the Sage 200 App Services site. The upgrade tool reads some of this information from the IIS 6 compatibility layer, where the issue occurs we see that the IIS 6 compatibility data in the IIS metabase retains two entries for the Sage 200 web services one under the default web site and the other under the Sage 200 App Services web site. IIS 7.0 and above does not use this information for its sites and thus works correctly, however the upgrade process incorrectly identifies two Sage 200 web services applications.

Creating Snippets can be a real time saver for future report writing, as they reduce the need to re-enter expressions you have created before. This is particularly useful if you have created long, detailed or complicated expressions that may be required on other reports etc. that you might go on to write.

When entering an expression into the Expression Editor there is an option available to ‘Save as Snippet’. This opens up the Expression Snippets screen which allows you to save expressions and calculations for use in the future.

The snippets are saved for use in all future reports and layouts etc. not just the report that they are created in.

There is also an Expression Snippets Manager, where you can manage any snippet(s) that you have created.

See this document for a step by step guide to creating and managing snippets: Talking Sage 200 – How to Create and Manage Expression Snippets

The Expression function allows you to create a manual field by building a calculation, based on standard database variables (Fields). You would use an expression when you want to create a value that isn’t available within the database. For E.g you may want to subtract a Cost Price from a Sales Price.  

The Sage 200 Report Designer has an expression editor which can make the building of the expression easier.  

The expressions and the expression editor can be used for ‘Advanced filters’, Sort fields, Criteria Fields, Drill Down fields and to Group Data. 

Add an Expression 

  • Select Toolbox > Expression 

  • Select the position you want the new field to go, on the report 
  • The Expression editor window opens

On the right hand side of the screen you have a Functions pane and a Fields pane.  

  • The Functions Pane contains the different functions you can use such as + (Adding), – (Subtracting) functions or operators such as < (less than) or > (greater than).  
  • The Fields Pane contains the different database Tables and Fields available to this report. The number of different functions gives an indication of how detailed the Expression can be. 
  • You can enter your expression by manually typing it. Alternatively you can Double click or drag Fields and Functions from the relevant pane.
    • To add a variable, find and select the required table and then variable within the Fields pane. Left click and drag the variable to the left-hand pane. 
    • To add a function to the expression, open the relevant folder within the Functions pane. Select the relevant function then left click and drag the function to the left-hand pane.
    • To add a numeric or text, type the required information into the left-hand pane. A text value must be surrounded by quotation marks, for example, “Abbey Retail Ltd”. A numeric value does not need to be surrounded, for example, 100.00.

To confirm the Expression, click OK

  • You are returned to the Design screen 
  • Preview the report to see the outcome

Expressions Errors

If an expression is entered in a way that does not make sense, then you will not be able to save it. The expression editor will highlight the area that has a problem, as below:

I have received a number of queries recently relating to Time Out errors being experienced during the running of a year end. There have been a couple of known issues with regards to these errors which were actually fixed in Sage 200 2011 SP1.

Below are details with regards to the issues now fixed in Sage 200 2011 SP1:

Known issue 3226 related to SQL timeout errors when running year end. The routine reached about 4% of Stage 2.

Known issue 4179 related to running year end on large data sets. Data sets with a high volume of transactions for a particular period or companies with a large number of accounts, timeout errors could occur when calculating period values.

When BI is installed it is set by default to only show basic functions. This means that you are limited to what you are able to achieve.

To enable full functionality select ‘Personalise’ from the Sage BI Toolbar.

Select Advanced functionality within the following screen

N.B. The changes will only take effect when Excel is relaunched.

When creating a BI report it is automatically given a name/title, for example Report 1. The almost natural thing to do is to select the cell where the title is displayed and type over it. However if you do this it will make the report disappear.

The reason for this is because the title is generated by an MDX expression (MDX is the cube language that reports are written in).

To rename a report properly select the ‘Rename Report’ icon    

Enter your title in the screen that displays

Click Ok.

Alternatively, if creating a report using the ‘New Report’ icon you are able to enter a title when selecting the the connection

Its a basic tip but one that catches out many!!!!

An issue was found with Sage 200 BI when Sage 200 2011 SP1 was released. The problem related to the SQL collation settings on either the Sage 200 Database or the SQL Server instance. A hotfix has now been released for Sage 200 2011 SP2 to fix this problem.

It should be noted that the SQL collation for Sage 200 SHOULD be Latin1_General_CI_AS.

Details from Sage, about the fix are below.

Issue no. 4437: Customer receives the following error when trying to Refresh BI Cube (or create a new Data Warehouse): “Error 468 : Cannot resolve the collation conflict between ‘SQL_Latin1_General_CP1_CI_AS’ and ‘Latin1_General_CI_AS’ in the equal to operation.”
This looks to be due to 2 different causes:1) The Collation of the main Sage 200 database is set to ‘SQL_Latin1_General_CP1_CI_AS’, instead of the recommended ‘Latin1_General_CI_AS’
2) The Collation of the Sage 200 / BI databases are correct but the overall Collation held against the SQL Server instance is set to ‘SQL_Latin1_General_CP1_CI_AS’.Reason: The Collation of a database can be compared to a language and when the program finds 2 database with different collations it is like comparing English to Chinese. The problem seems to have surfaced with v2011 SP1 as we are now using more temporary tables and, especially in point 2) above, these temporary tables will be stored in the TempDb system database which uses the Collation from the overall SQL Server instance.
This file will fix both sets of issues.
The customer must have upgraded to Sage 200 v2011 SP2.
The Sage.BI.Database.SQL.dll file will need to be copied onto each Client machine.
[Note: The new File version will become 16.0.12019.4]Note: This file needs to be copied to the folder path C:\Program Files\Sage\Sage200
This is confirmed within the “Installation guide for v2011 SP2 Hotfix 3.doc” within the Zip files.Noticeable Changes to the Application:No changes.

Sage 200 Service Pack 2 has now been updated to include support for CRM 7.1 Patch D.

Before installing this update you must make sure that all patches have been installed for CRM 7.1, and that Sage 200 2011 Service Pack 2 has been installed.

The following files then need to be installed:

CRM7.1.d-Hotfix.1.exe on to the CRM Server

Sage200v2011SP2Hotfix1.exe onto the Sage 200 Web Server

Sage200v2011CRMSP2Hotfix1.exe onto the CRM Server

For Manufacturing sites, please be aware of a hotfix release for Sage 200 2011 SP2.

This hotfix brings Works Orders into line with Bill of Materials, for converting Stock unit quantities.

Works Orders previously converted all quantities to stock units and then multiplied by the quantity required. Bill of Materials (Trial Kitting) multiplies the component quantities by the assembly quantity required and then converts to the stock units.

This hotfix makes Works Orders use the same method as BOM.