Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

<b>Software Review:</b> Sage Intelligent Reporting for Line 50. By John Stokdyk and David Carter

by
9th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

With the release of Sage Intelligent Reporting for Line 50, the Newcastle-based accounting software house has brought sophisticated analytical tools to a much wider market. David Carter and John Stokdyk look inside Sage's spreadsheet-based box of management reporting tricks.

Introduction
Over the past couple of releases, Sage has significantly beefed up the reporting facilities in Line 50. First were enhancements to make the Sage Report Designer more accessible to users. Then came version 12 and Integrated Excel Reporting, which lets users connect Excel to the Line 50 database so they can analyse and present the accounts as they please.

As if that wasn't enough, Sage has now introduced Sage Intelligent Reporting. With the 2004 acquisition of IntelligentApps, Sage gained the expertise and technologies it needed to bolt multi-dimensional online analytical processing (OLAP) data cubes into its range of bookkeeping, accounting and ERP products. By tailoring the IntelligentApps tools specifically for Sage Line 50, Sage has brought business intelligence within the reach of thousands of Line 50 users.

Sage Intelligent Reporting is currently priced at £495, providing capabilities that a few years ago would have set you back nearly 100 times as much. Each subsequent licence costs £100, so people can use Intelligent Reporting to analyse their accounts data without having to run a version of Sage Line 50.

So how big a business intelligence bang do you get for this budget price?

How Sage Intelligent Reporting works
Sage Intelligent Reporting is an Excel Add-In. When you load the software, it takes control of Excel and presents you with a data import menu displaying the companies whose accounts are held in your Line 50 data directory. The wizard asks you to choose a company and to specify the period on which you wish to report, and then builds the data cubes to support your analysis.

Quite a lot of whirring takes place as Intelligent Reporting extracts the data and constructs the cube files. Depending on the size of your database and the speed of your machine, you should be looking at a selection of standard reports in a minute or less. There are 27 readymade financial dashboard and report workbooks. The other options include customers, invoices, sales orders, products suppliers, purchase orders and companies, but currently the templates are limited to one report each. A tool button on the top menu bar makes it easy to save the analysis as an ordinary Excel worksheet to pass on to other users.

If the Sage Intelligent Reporting portfolio includes the analysis you want, you're off and running. Or you can open a report template similar to the one you want and alter the data selections.

 main menu for Sage Intelligent ReportingOnce it has built the data cubes for you. Intelligent Reporting lays its own interface over the normal Excel controls and in spreadsheet terms, you are definitely no longer in Kansas. In place of the familiar Open, File and Print icons there is a collection of somewhat obscure tool buttons along the uppermost menu bar, along with a second row which includes four discreet yellow - these are the main way of navigating around your reports. If you are disconcerted by the unfamiliar controls, there is an option to bring the normal Excel toolbar back.

The main user navigation mechanism (pictured right) is a simple mid-screen window, with tasks displayed on the left of the panel. The available reports for each category are displayed in the right-hand side of the window, you select a report and then choose the action you want to undertake from the task list, either to open or adapt as a user-defined report.

The P&L Current Year Trends report (pictured below - click thumbnail for bigger image) reveals the kinds of reporting goodies that a good BI tool can make possible. Drawing on the company's archived Line 50 data, it instantly graphs your gross/net sales trends for the past five years, or current year, if you change the period criterion.

 typical report generated by Sage Intelligent reporting Each of the report parameters is listed above the chart, and it is a simple matter to alter your view by clicking one of the cells and selecting a different measure - for example to contrast a view of the Actuals figures with the Year to Date, or to cycle through totals for sales, purchases and direct expenses. Nicely formatted reports that could take a lot of time and effort with Excel and Line 50 are there for you to see with a couple of mouse clicks. This is what business intelligence is all about.

But the real power of BI comes from "off-road" explorations into less well-trodden areas of the database. This takes us into user-defined reports. Sales analysis is a common requirement, so our first experiments focused on the Sales Order Trend report template, which we used with Sage's Stationery & Computer Mart demo database.

Most sales organisations would like to know how different product groups sold throughout the year - for example to compare the Statonery & Computer Mart's paper products against PCs, peripherals or furniture sales. One of the menu bar icons takes you into the Report Layout dialogue box, which is similar to the new pivot table layout box in beta versions of Excel 2007. The menu shows a list of the criteria that make up your reporting cube which you can drag and drop into Rows and Columns boxes to create your analysis.

If you click on a particular criterion - products, for example - you see a listing of the items within that category. These can be selected by clicking them individually, or by Shift- and Control-clicking to make multiple selections (pictured right - click thumbnail for bigger image). You can also access this Report Critieria listing by double clicking the data labels on the report worksheet.

A small icon in the criteria selection box displays an interactive listing that lets you move products up and down within the list, but this was somewhat cumbersome to use and provided no means to group the products into coherent categories.

After some laborious trial and error work and some extra user training from Sage, we were able to produce a product-by-product sales analysis. Once the products were listed in different rows and periods set up as columns, we cut and pasted products on different rows into a new Excel worksheet. Summary totals were calculated for each of our product groups, which were displayed on a consolidated chart for comparison. While this seemed to be a roundabout route, all the cells in the analysis worksheets retained their links back to the Intelligent Reporting data cube. We added a Customers data label on each worksheet. By altering the original selection from All customers to individual companies, we could identify customers who were buying a lot of paper, for example, but shunned our PC products. Rather than doing this analysis as an interactive exercise on the product data selection, it would have been relatively simple to reformat the report to show sales figures for different product selections organised by customer rather than date.

While the sales order and customer report libraries were limited, the Financials Statements Designer greatly expands the actions you can carry out on all the reports in the Financials menu.

 Sage Intelligent Reporting Financial StatementFor example, practitioners who have to produce trial balances and balance sheets from shambolic client data will purr over the Chart of Accounts mapping facility (pictured right - click thumbnail for bigger image). This dialogue box brings across the underlying chart of accounts structure from Line 50 and lets you edit the accounts as you wish; a second set of controls lets you define and rename the code ranges against which you want to report. You can easily map messy accounts so they go to the correct P&L and balance sheet positions without having change the client's data. Sage indicated it is working a similar feature for the other reporting menus.

Producing P&Ls by department are notoriously difficult in Line 50, but the Financial Statements Designer makes it easy to play around with the reports and put departmental totals on the rows or columns, whichever you fancy. You can also create different hierarchies on the nominal accounts, so that they go into different groups. So in version one of the P&L Motor Insurance goes into the Insurance group. But in version two of the same P&L it goes into Motor Expenses.

A breakthrough product - but with room for improvements
There is a learning curve to negotiate, but once you are used to its idiosyncrasies, Sage Intelligent Reporting gives you some superb capabilities for exploring Line 50 data - for example to see which customers are buying which products. However there are a few minor shortcomings. First, anyone wishing to analyse their margins on sales will be disappointed. Sage Intelligent Reporting records costs on product sales, but not on customer sales, so you can't work out the margins on sales to a particular customer. This will be corrected in an updated customer reporting cube in the next release, Sage said.

The second shortcoming is in an inability to drill-down into the source data, which is common in many other Excel-based reporting tools. This means you cannot check any of the totals to make sure they are correct, or see what went into them. BI tools are great for spotting anomalies in your source data - for example our product sales analysis from the stationery sales order report indicated that PC sales exceeded the total sales for all products combined. With no drill-down facility, we were forced to go back into Sage Line 50 to investigate out how the anomaly came about. Excel pivot tables, which cost nothing, at least give you the ability to go back into the current year's data.

These quibbles are reminders that Sage Intelligent Reporting is still in its early days. In a way, the product has been caught on the hop by its own success - there's so much that it can already do that you immediately wonder what else would be possible if only Sage added a few more features and report templates. In feedback to this review, Sage said it was currently working on many of the points raised, including the drill-down capability, a customer analysis cube that will support margin calculations, and picking up product group data from Sage Line 50 to facilitate product sorting and comparisons.

Sage Intelligent Reporting makes it possible for almost anyone to interrogate Line 50 data for insights - without even having to run the accounting application. The achievement in delivering all this ready-to-analyse data so quickly should not be underestimated. With Intelligent Reporting, Sage has made the grinding drudge work of OLAP - assembling, validating and formatting the data - incredibly easy. Ironically, some of the simpler aspects of the program such as the user interface are less intuitive, but this is something that will improve as customers become more familiar with the product and steer Sage towards more useable refinements.

With its first release, Sage Intelligent Reporting is best suited for practitioners who will be able to generate consistent reports for different companies - a boon for firms that prepare regular management accounts and reports for business clients. The Financial Statements designer will also help firms who receive messy trial balances from clients and need to tidy them up before producing P&Ls and balance sheets.

But more work is needed before Sage Intelligent Reporting will fully satisfy analysis-hungry finance and sales managers. When Sage extends the sophistication found in the Financial Statements Designer into the product, customer and sales analysis options, watch out. Sage Intelligent Reporting is set to become a genuinely ground-breaking BI application that will appeal to Line 50 users who are beyond the reach of giants such as Microsoft, Oracle and Systems Union.

Tags:

Replies (2)

Please login or register to join the discussion.

avatar
By AnonymousUser
28th Apr 2006 08:12

Sage Intelligent Reporting Aditional Points
Thank you for providing a good introduction to Sage Business Intelligence and we will certainly take on your comments on things that need improving as the product matures.

In a roadshow around the UK this week showing Sage Intelligent Reporting to accountants and partners in Perth, Newcastle, Manchester and Winnersh, I picked up some good feedback which highlights some important aspects not covered in the review..

Firstly, the scalability which we address through the core Intelligent Reporting front end engine within Excel is a more important issue in the SME market than we first thought. One Line 50 user had transactions with 10,000 customers involving 2,000 products over the past five years. This did not draw gasps from our partners, but nods of the head and comments such as, “We have similar customers ourselves”.

Trying to get an analysis of the best customer/product combinations over a 12 month period, sorted on year to date would not possible to do in Excel, one partner told me. "It would take days and ultimately crash Excel”. I was able to show how our product uniquely handles millions of rows in Excel in seconds.

Secondly, as you suggest in the review, the product does so much so quickly that we sometimes take it for granted and immediately want more, missing the real business benefits in the process. At one workshop, a lady asked if I could put one of her Sage backups through Intelligent Reporting.

She knew the data, so she could take a view if the product was working as she would think it would. Her company had 5 years worth of data, so she wanted to see any trends that she had not previously been able to view.

She also wanted to see if I could prove my ambitious claim that I could tell her things about her customers that she didn't know - without knowing them or anything about Line 50.

We build three cubes, Financials, Nominals and Suppliers in 28 seconds. Financials had five years of data. She then asked me to drill to nominals and show an analysis of revenue by salesperson by year. Instantly, I noticed that every salesperon’s revenue had increased year on year, but one had decreased year on year for the past four years.She said whilst she was personally shocked, she and the business had never suspected this was the case.

So the conclusion from this was that Sage Intelligent Reporting went from zero information into a working application that delivered added value information in less than 2 minutes. That’s a return on investment at a speed I have never ever personally witnessed during my 20 years in the BI business.

Paul Martin,

Sage Business Intelligence Division

Thanks (0)
avatar
By richardterhorst
04th Oct 2010 17:01

It's still useless

 If you wish to add specific reports you still need to go into Report writer.

Many of the existing "standard" reports require extensive formating in Excel before they become readable.

Sage is one of the worst reporting program I have ever experienced.

Thanks (0)