Category: Excel Integration


Excel is everyone’s favourite for working with data. So for most users they want to get data from Sage 200 into Excel. Despite there already being a number of ways to achieve this, I think Sage 200c final brings an easy, quick and useable option.

Send To Excel is a new feature within Sage 200c which allows you to send data into Excel from a report, in a useable format for Excel.

It adds the raw data from the report to Excel and inserts it as a table so you can begin working with the data straight away by sorting and filtering each column. The format of the data also means you can quickly begin to work with it using Pivot tables and charts etc.

This means there is no reformatting, no deleting of duplicated fields and no un-merging of cells. All of which can be experienced with the Export to .csv and/or .xls option.

Also this functionality has no hidden/extra Sage 200 Services costs which come with the Excel Reporting (although as an aside Sage 200c does introduce some new ‘Free’ Excel reports).

So, how does Send to Excel Work?

Simply generate your report to Preview (NB. This is currently only available using the Preview Output Mode, not using the Spooler Preview).

There is now a new Send to Excel button.

Send to Excel button

Once the button is selected Excel is instantly executed and the data will be presented in the format below:

Send to Excel report

Advertisements

Imagine my horror, when doing a Sage 200 upgrade recently, when faced with a report used by the customer, which used Sage 200 Excel Integration. The report was quoted as being their ‘Bible’ report.

In the 8 years of implementing Sage 200/MMS I have tended to steer clear of using Excel Integration. To my knowledge all colleagues of mine have done the same.

As much as anything this is probably because I have never taken the time to look at it, play with it and learn what it was capable of. It was never an element of the system that Sage focused on during their accreditation course(s) (Appendix B I think of the manual I received back then!!).

Generally speaking there has always seemed to be a better way of getting data out of Sage 200, whether it was using a good old EXCEL QUERY, a tool such as CRYSTAL REPORTS or more recently BUSINESS INTELLIGENCE, so I have never really seen the reason to use Excel Integration.

I do not think Sage have ever really extended the Functions that Excel integration offers so it never really became that powerful a reporting tool.

However this report I was faced with recently was very impressive. Excel Integration had been used to bring Budget and Actual values in to, basically, a Profit and Loss report structure.

There was the flexibility of being able to create the P & L based on the Nominal accounts, as opposed to the Report Categories and being in excel other required calculations were made from the data using standard excel formulae and functions. Also it was able to link to other reports that the customer had in Excel.

The report needed a slight tweaking following the upgrade as I had moved Sage 200 onto a new server, and the functions had a path to the MMS_Excel.xla file in front of them. A quick find and Replace and the report was running perfectly…pheeew!

It got me thinking though; about weather I had missed a trick with this tool over the years. Does anyone else use it? Or did anyone else use it?

For me, ok, I think Business Intelligence supersedes Excel Integration now. But maybe I should have given it a chance in the early days!

 

Ctayts