Sage Developers' Blog

One of our key focuses for the 2010 release, as part of our ongoing Information Management strategy, is to improve the ease of reporting on Sage 200 data.

As you may have read in a previous article, one way we’re doing this is through the introduction of the new Report Designer and a LINQ data model. This isn’t all we’re doing, however; the changes I’m going to discuss in this article specifically concern the changes we are making to Analysis Codes throughout the system to make them easier to report on.

Sage 200 is a fairly highly normalised database which has been designed for transactional performance and efficiency. Making it easy for the application itself to quickly and reliably read/write data in this way is not always consistent with easy access to the data in a “human readable” format.
This is always a trade-off in database design and is particularly relevant to Analysis Codes, where up to now we have put the emphasis on power and flexibility more than ease of reporting.

In 2010 we’re aiming to increase the emphasis on usability – to make Analysis Code much easier to report on, without significantly affecting their flexibility. As part of this overall simplification, we’ll be improving consistency between the implementation of Analysis Codes in different modules that were developed over a period of time – the improvements affect SL, PL, SOP, POP, Stock Transactions and BOM.

The overarching approach is that Analysis Code values will now be stored directly in the “entity” table in the Sage 200 database:

We believe that this will make it really easy to get the information you want out of the database!

Another change is that Analysis Codes are created centrally and then assigned to a field on a particular object – this means, for example, that a “Region” field on a customer account could actually be the same Analysis Code as the “Region” field on a supplier account.

The central list of Analysis Codes is stored in the AnalysisCode table; mapping to the data tables is through the AnalysisCodeMapping table (in both cases the schema has been simplified slightly for clarity”):

These tables allow you to look up the user-friendly name for any given Analysis Code and also store some of the other options for a particular code.
For any given Analysis Code it is possible to specify that values:

• can only be selected from a pre-defined list (stored in the AnalysisCodeValue table)
• may be selected from a pre-defined list or entered ad-hoc
• can be added to the pre-defined list on entry

It is also possible to specify that a field is mandatory for a particular object – i.e. the user interface will always insist that the user enter a value.

The (very) observant amongst you may have noticed that while this approach great simplifies Analysis Codes and hopefully makes them far more useful and valuable, there is one minor limitation to this approach: there will now be a limit on the number of Analysis Codes that can be applied to a particular table.
Well, that’s true – it’s now only possible to have 20 Analysis Codes on any single object (although the list of possible Analysis Codes is still unlimited). While it was theoretically possible to have an unlimited number of Analysis Codes on a SL customer (for example) in prior versions, the practical complexities of managing user interface, etc. means that very few customers use more than a handful – we firmly believe that 20 slots per module will be more than sufficient (if you disagree, please let us know!)

This article has only briefly covered the changes but has hopefully given you a flavour of what’s coming. Of course, this subject will be covered in a great deal more detail in the SDK documentation.
In the meantime, if you have any questions – please comment below!

In Sage 200 v2010 we are introducing the .NET based report designer that first shipped with Sage 50 in 2007. Our old reporting framework will be deprecated, which means that we are continuing to fully support it at the moment but we are formally stating our intention to drop support for it as some point in the future. How and when we drop support is still to be decided but our advice is that new work is done with the new framework wherever possible and that you start making plans to migrate to the new framework.

From the 2010 release, all (or almost all) our out-of-the-box reports will ship on the new framework only. However, we know there are a lot of custom reports across customer sites today. You’ll be pleased to hear that if you have customised any of our reports or layouts on the old framework, or if you have any completely new reports, they will all still work transparently to the user – they simply use the old reporting framework. The way we have implemented the report designer integration was very much with backward compatibility in mind. All of the new reports were migrated using a tool, rather than re-written and we will ship this tool to developers and partners the release (or very close to). This tool should dramatically reduce the effort needed to move to the new framework, and because both reporting systems will co-exist in Sage 200 2010, you won’t have to convert them all before upgrading – you can take your time with the migration.

There is a lot in the new framework for developers and over the summer we will be presenting on it at dEvents and also offering some detailed “how to” sessions and more blog posts. For this post, I wanted to introduce you to some of the new report designer features and give a taste of what is in it for developers. To get the full low-down, be sure to go to dEvents or one of the other sessions or courses.

For people that have not seen or used the new report designer here is what the main editor page looks like:

It has a more modern look and feel with the kind of navigation and property pages that will be familiar to anyone using a modern IDE such as Eclipse or Visual Studio. Some of the key features that are different from the old framework are:

• The ability to edit the underlying query and criteria behind a report in-situ. This makes the existing RDE tool redundant (although it will still be supported for reports on the old framework).
• Derived field in the reporting layer now behave exactly like regular database fields (e.g. you can sort and filter by derived fields etc.)
• The reporting layer is fully extensible by developers and partners, and amendments can be deployed by Add-on packages.
• Basic editing can be done in the report preview which makes it much easier to get the layout right

For those of you that are familiar with the report designer from Sage 50, there are a few new things too. Some of these will follow in the next release of Sage 50, but some may only be enabled for Sage 200:

• The ability to create sub-reports. This allows one report to be embedded in another, linked by its criteria. The data source for the sub-report can be different from the main report which allows cross-product reporting (e.g. reports that contain both accounts and CRM data)
• We’ve provided a LINQ data provider that can access any LINQ Data Source, so you can access a range of different data sources, not just relational data.
• Navigation of Tables and Fields in the model is easier. The list can be filtered to help find the table you’re looking for.
• The Join Editor can suggest relevant tables and joins to make, based on the database relationships
• Improved criteria editing, so all the criteria capabilities are editable in the designer.
• Improved rendition when exporting to Excel, PDF and HTML.
• Loading and saving of criteria
• Special dates in criteria (e.g. last financial year, last accounting period)

All these features will be showcased at dEvents in the summer.

These are all features for the general report designer user. Specifically for developers, there are a lot of extensibility points built into the new framework, as shown in this diagram:

The shapes with dotted outlines are extensibility points for developers. A developer can:

• Add client-side custom expression libraries that can be used inside reports
• Extend our standard models (what we used to call reporting layers) by adding new database fields or by adding new derived fields to existing entities/tables in the model
• Extend our models by adding completely new entities/tables
• Add a completely new model based on a relational database, a web service, in-memory objects (i.e. these would be called Application Fabricated Data, or AFD reports in the old framework), or in general, any data source that has a LINQ provider. There are an increasing number of these around and a good list can be found here. You can also write your own LINQ provider. The 2009 SDK had a couple of samples of this and later this year we are hoping to give you more help doing this as part of our upcoming Sage 200 Labs project (more on this in a later post!).
• Except for expression libraries, all this will be integrated with our Add-On management framework that we introduced in 2009 to make deployment easy.

So, quite a few options!

To close this post, I’d like to try to put our approach to the report designer integration into some context. One of the design objectives we had for the integration was to make reporting more consistent with our overall information management plans for Sage 200. Over time this will cover any way that users get information out of Sage 200. Our aim is to remove the special cases and unusual or inconsistent behaviour from the reporting framework, as well as to unify the data models across lots of different endpoints and data sources (see this post for a longer discussion on how we are approaching this).

We’ve made good progress towards this aim with the 2010 release:

• Derived field behaviour is now consistent with regular Database field behaviour – they are not special cases any more, so users can just consume the derived fields without having to worry about any unusual behaviour.
• The data models that underpin reports are the same ones that are used for Workspaces in the Sage 200 Desktop and for the Sage 200 Mobile solution that is coming with the 2010 release. This means that investments you make extending our models with derived fields or new entities can deliver more customer benefit than just a new report. They can be exposed in reports, in desktop workspaces and on mobile devices. We have more  interoperability planned for future releases, and more endpoints (maybe CRM and Excel). Watch this space for details.
• In-memory data models (i.e. AFD reports) can be joined to regular database tables in reports. This means that AFD reports are much more amendable and behave much more like “normal” reports.

That’s it for now. Hopefully this has whetted your appetite for the new reporting and information management features in Sage 200 v2010. It’s been a pretty tough task (we had a lot of reports and derived fields to migrate!) but I think we’ve pulled off a good integration job. And remember, this is just an overview. There will be a lot more details coming your way over the next few months up to the release through dEvents etc.

P.s. For more details on dEvents email developers.programme@sage.com or telephone 0845 111 7733.