Tag Archive: Report Designer


I have come across a couple of issues relating to Emailing documents from Sage 200.

At one of my sites recently users were using had a mixture of Outlook 2010 and Outlook 2013. I had configured all layouts for email set up to use MAPI as the Mail provider.

The first issue this brought up was for every email Sage tried to send, an Outlook security warning would pop up.

Outlook security message

Outlook security message

This is not so bad for users if one or two emails are being sent, but imagine how this is for a batch of 100+ statements!

This issue is actually caused by a Microsoft Outlook update that is designed to prevent security breaches and is not a Sage problem. The update effectively removes the ‘Send e-mails automatically’ feature from within Format > E-mail and this can’t easily be corrected. For further information, refer to Microsoft article 262634 at http://support.microsoft.com/?kbid=262634

NB. In Outlook 2000 and 2003 the security update is installed separately, however it is built into Outlook 2007 and above. For Outlook 2013, there are Programmatic Access security settings which are generally controlled at a Group level. These should be enabled.

In Sage 200 2013 and above, this issue doesn’t occur if the report or layout has the Mail Provider set to Outlook instead of MAPI.

Having changed the Mail provider to Outlook on the layouts at my site, this identified another issue but this time for users of Outlook 2013. With the Mail Provider set as Outlook, when attempting to Email a document, a message appeared to advise that the Sage200Desktop.exe has stopped working.

StoppedWorking

Sage200Desktop.exe has stopped working error

The Problem Details section gives more information (???????):

Problem signature:

Problem Event Name: APPCRASH

Application Name: Sage200Desktop.exe

Application Version: 17.300.14086.3

Application Timestamp: 5334325b

Fault Module Name: StackHash_17c0

Fault Module Version: 6.3.9600.17630

Fault Module Timestamp: 54b0d74f

Exception Code: c0000374

Exception Offset: PCH_0F_FROM_ntdll+0x0003CC2C OS

Version:6.3.9600.2.0.0.16.7

Locale ID:2057

Additional Information 1: 17c0

Additional Information 2: 17c083e57d5c8adee4818c4ba7d6ebbb

Additional Information 3: 643b

Additional Information 4: 643b2a225326ecea5e5cb96f62ab3b6e

Troubleshooting detected that although this issue occurs for Outlook 2013, it only occurs when using Windows 8/8.1 or Server 2012/2012R2. Windows 7 machines were fine.

This is a known issue with the Sage report designer (so impacts Sage 50 and payroll also). The problem I had for my site was the resolution is to switch the layout Mail Provider to MAPI……..!

NB. The Sage200Desktop.exe has stopped working error can also occur when the Mail Provider is MAPI and Outlook is closed when attempting to Email the document.

My options for this site were:

Solution 1
Upgrade all users to Outlook 2013 and set all layouts to use MAPI.

Solution 2
Keep layouts as MAPI but for Outlook 2010 users download ‘Click Yes’ http://www.contextmagic.com/express-clickyes/

Solution 3
Sell the customer Spindle Professional

A substring allows you to grab part of a text. It may be that you only want to display the first few characters or the last characters.

In the Legacy Report Designer (pre Sage 200 2010) you would have used Left or Right

e.g.

left(PLSupplierAccountNumber, 3)

In the New Report Designer you need to use Substring.

Use Substring to restrict the number of characters

To only show the first 3 characters of the Supplier Account number, you would enter the following expression:

Substring(PLSupplierAccounts.SupplierAccountNumber,0,3)

The 0 says which character to start from. Above we want to start before the first character. The 3 is how many characters we want to return (display).

Do not display a variable unless it begins with a particular character

Substrings can be used with IF type expressions to extend their use. You may not want to display something, unless it begins witha paticular character.

In this example the variable is SupplierAccountNumber and the letter is ‘S’. If the account number doesn’t begin with ‘S’ it won’t show anything.

Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) = “S” ?
Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1): “”

Restrict the number of characters displayed if the variable begins with a particular character

Similar to the above, we could say if the field begins with an ‘S’ then show only this character, otherwise show something else.

In this example if SupplierAccountNumber begins with the letter is ‘S’ it will display the letter S, other wise it will display the SupplierAccountName.

Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) = “S” ?
Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) : PLSupplierAccounts.SupplierAccountName

An update has been released for Sage 200 Report Designer (Update 1.4.2013.27). This is to resolve the following known issue:

Issue 5454 – PCs running Sage 200 2011 cannot view reports spooled on the machine that has received the Sage 50 Payroll auto-update

Sage 200 users that also have Sage 50 Payroll installed, on their client machine, found problems with Sage 200 reports after installing Payroll Update 4 (v19.02).

This was because the Report Designer is updated to a higher version being run than the Report Designer used by Sage 200-only users. Reports modified or spooled (in Sage 200) by the Sage 50 Payroll users cannot be used by the Sage 200 users.

To overcome this Sage have released a standalone Report Designer update which can be installed on machines running Sage 200 2011 Service Pack 5. This update aligns the Report Designer to the same version as the Payroll users.

NB. This is the same version of Report Designer that comes as standard with Sage 200 2013 On Premise.

This update should be installed to all Sage 200 machines (not just those using Sage Payroll). Please be aware that a re-boot is also required once completed.

I posted last week to advise that Sage 200 and Sage Payroll RTI edition are now compatible after previously blogging about a Report Designer conflict.

http://wp.me/p1CtiO-aa

There are however some Report Designer issues when installing Sage 200 on the same machine as Sage 50 Accounts 2013.

Some criteria is not able to be selected (fixed by installing the latest version of Report Designer) and some duplication of Transactions may occur when exporting reports to .CSV.

Sage recently released their Sage Payroll RTI (Real Time Information) this allows for the reform being introduced by HMRC which will require regular submissions rather than the current Year End submission. Every business will need to move to this format by October 2013.

Please be aware that the software is not currently compatible (or Supported) with Sage 200. Sites that have installed this software on to a machine running Sage 200 have identified a conflict with Sage 200 Report Designer.

I have written various blogs about Sage 200 Report Designer functions but realise I have never advised how to add a report to a users menu. Until NOW!!!

The following document explains the steps required.

  • Including User and Role permissions
  • Adding features
  • Menu Design Mode

Talking Sage 200 – How to Add Reports to the Sage 200 Menu

 

 

A great feature of Sage Report Designer is the ability to format objects in a certain way, depending on certain conditions.

You can configure the fields, sections, items etc. within a report to display in different ways, depending on different conditions.

For example, you might want to highlight Stock items on a report that are below their re-order level. Using conditional formatting you can specify to make the text colour of the Item Name red, if the item is below its re-order level.

The following document uses an example to show how to apply conditional formatting to a particular variable.

Talking Sage 200 – Using Conditional Formatting

With the price of stamps increasing today (First Class from 46P to 60p, and Second Class from 36p to 50p) it is as good a time as ever to think about how you are distributing documents to your customers, suppliers and may be even between colleagues who are at different sites.

Sage 200 has the facility to cut down on postage already built-in. All documents and reports can be configured to email contacts either automatically or manually. The automatic configuration can utilise the Roles and Contacts within the Sales and/or Purchase Ledger to send documents to the required email address.

Click here to see my previous post about emailing documents out of Sage 200.

As well as the standard Sage 200 functionality, there is also the add-on option. Spindle Professional is an add-on for Sage 200 that aids in the distribution of documents.

Spindle is capable of much more than just emailing documents as you can configure a document to be Emailled, Faxed or Printed based on the contact details available in Sage. As well as the Distribution options you also have the ability to add backdrops to documents (these are basically electronic stationary layouts), you can attach other documents as part of distribution (for example attach the relevent Despatch Note when sending an Invoice), there is also the ability to Archive documents that have been sent out.

Spindle Professional can also be used with other software, so you could create a Word document and distribute it as necessary.

Click here to see my previous post about Spindle Professional

Both of the above options that you have with your Sage 200 software will save you money in relation to Postage as well as Paper, Envelopes, Stationary and Time.

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: