Category: Documents/Report Designer


Yesterday finally saw the release of the much-anticipated Service Pack 2 for Sage 200 Extra 2015.

One of the key items as part of this is the support of Windows 10 operating system.

There are lots of fixes and some additions as detailed in this document released with the update –

Reports

There are some New Reports to be aware of which are not installed automatically as well as Layout updates too. Check these out with your BP. The introduction of the SOP Profitability reports which include Returns and Credit Notes will be music to some people’s ears, although if you needed this previously you have probably had the report written already.

There also some nice Stock reports, offering different views of Sales by Customer, Product Group and Monthly Sales etc.

Summary Screens

My particular favourite addition in this service pack is the added drill down functionality to the Summary screens. Previously a great visual aid (and Sage Sales Persons dream!) they were not altogether complete from a ‘day to day’ point of view. However we are now able to drill all the way down to the transactional items which makes them much more beneficial as an analysis tool.

Sales Summary Outstanding Debt (Totals)

Sales Summary Outstanding Debt (Totals)

 

Sales Summary Outstanding Debt (Drill down to Customers)

Sales Summary Outstanding Debt (Drill down to Customers)

 

Sales Summary Outstanding Debt (Drill down to Transactions)

Sales Summary Outstanding Debt (Drill down to Transactions)

Installation

Service Pack 2 can be applied to sites running either the Base version of Sage 200 Extra 2015 (all SP1 updates are included) or sites with Sage 200 Extra 2015 Service Pack 1.

As with Service Pack 1 there are two installation files:

  • Sage200v2015SP2ClientPrerequisites.exe
  • Sage200v2015SP2.msp

On client machines, both files must be run in the order listed above.

Sage200v2015SP2ClientPrerequisites.exe checks the client files to ensure the correct registry keys are present on the machine.

Sage 200v2015SP2.msp updates Sage 200 Extra.

NB. On a server, you only need to install the Sage 200v2015SP2.msp file.

As ever this Sage 200 Extra 2015 SP2 Installation and Release Notes document gives all the information needed to install the service pack.

 

Its been billed as the most important update in years and I have to say, I do not think users will be disappointed. Here are some details of the new features within Sage 200 2015 which was officially released at the end of February.

Look and Feel
Whilst the basis is familiar to current Sage 200 users, the screens and menus have changed in appearance giving a more modern design. Many of the changes are designed to make it easier for users to navigate Sage 200 and find/access what they want.

Look 1

Homepage

List View

List View

Look 3

Redesigned Process Maps

The 2015 design is much more intuitive.  A really nice feature is the frequently accessed page where the system remembers the functions you have used the most, so there is a quick way of getting back to them. What’s even more impressive is it remembers by day. So if you access something on a Friday, the next Friday it will be in your recently accessed.

Frequently used

Frequently used

It is now possible to use a search function on the menu allowing you to search for a menu item and the menu is filtered by items containing the word searched.

Menu Search

Menu Search

Custom colour settings can be applied and you are able to make each dataset/company you work in a different colour to make it more obvious which one you are working in.

Homepage and Favourites still exist whilst being more easily managed. Favourites can be added with one click, and can be arranged in folders, easily renamed and re-ordered.

Fixed Assets
After years without one  a simple Fixed Asset register is now included within the Financials modules for Sage 200. This is a welcomed introduction allowing you to set up, depreciate, dispose and delete assets.

Excel Reporting
Excel Reporting gives you more choice over how you can produce the reports you want. The important thing to note here, and I am not sure how I feel about it, is that some Excel reports are provided free while others are available if you subscribe to Excel Reporting. You are able to preview reports within the program to see what you would get in subscription packages.

Excel reports

Excel reports

Excel Reporting 2

Summery Screens
New Graphical Workspaces give a great visual representation of certain KPI’s. The Graphs also allow a drill down function to gain more specific answers.

Summaries 1 Summaries 2

Summaries 3

Other Improvements/changes

  • The Logon speed has significantly increased and you will struggle to find time to make a coffee before it has opened. Joking aside this is a major improvement.
  • There is a completely new ‘list view’ that does away with the older-style list views of the previous Sage 200 versions. These new list views include improved searching and filtering methods, and mixes the function of workspaces with the easier to read layout of the old list view.
    • List views can be opened up in a separate form/window. With a scroll bar!
    • Totals have been added to quickly show the cumulative values of selected records.
  • Workspaces also run more quickly, and also make better use of screen space.
  • Enquiry Screens have returned (I know they could be added to the menu manually in 2013) so the normal forms can be accessed of the menus as per 2011.
  • Project Accounting now includes the much requested Work in Progress (WIP) function which accrues for part-delivered project work. This is automatically posted to and reversed from the balance sheet as projects complete. This means costs can be posted to the same period as revenue to ensure true project profitability is shown in financial reports.
  • Increased narrative length
  • Ability to edit manual nominal journals so that you can amend references or narrative.
  • Functionality to properly account for the VAT legislation changes for early settlement discount. If you deal with a lot of settlement discounts you must upgrade for this alone.
  • Support for SQL 2014 is now included.
  • Ideas Hub this gives an easier route to offering Sage ideas of what changes you would like to see. It also gives you the opportunity to join a Sage 200 community, and hopefully this will offer an increased source of information and communication for users of Sage 200 (hopefully not turning people away from Talking Sage 200!!!!).

I am nothing but positive about Sage 200 2015. A number of the key issues have been rectified and it is definitely the most updated and improved release since the move to a SQL database.

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

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: