Sage Developers' Blog

Sage 200 R&D are busy building a bank of WCF Services to support our next major product release and we recently fell into a common trap like many before us.

To communicate with a WCF Service you need to create a “client”, also known as a “proxy”, which acts as an intermediary between your application and the service you are contacting. Visual Studio has great tools for automatically creating these proxy classes so you don’t need to worry about serialisation or handling the HTTP requests and responses, you only need to know the name of the method on the service that you wish to call.

A common practice is to create an instance of the proxy class in a using statement so that once the service call has been made, you can get rid of the connection to the service (an expensive resource to leave open) and process the response however you wish. But there is a potentially serious problem that arises when you use this approach.

Automatically generated proxy classes implement IDisposable, that’s why they work in a using statement, but the implementation of Dispose() does not take into consideration those cases where a service has thrown an exception. Dispose() always calls Close() on the connection but you can’t call Close() on a connection which is in a Faulted state – i.e. when an exception has been thrown. When a connection is in the Faulted state you need to call Abort() instead, just calling Close() causes another exception to be thrown and the connection never gets closed. As we found out, it doesn’t take long for you to run out of connections!

While you can get rid of the using statements and put a try-catch block around each service call and make sure you’re calling Abort() in your catch blocks it quickly becomes spaghetti code and it is very easy to forget to do, especially in service-heavy code written by many different teams.

How We Solved It

After some discussion, collaboration and research, we came up with an elegant solution that meant Developers didn’t have to care too much about the lifetime of a service, or need to remember to abort connections when exceptions did occur.

Our solution was a generic extension method that neatly called Close() or Abort() where appropriate and simplified the code that a Developer needs to write to handle the connection lifetime.

As an added bit of complexity, Sage 200 is completely extensible, so our code is written in a very generic way. As a result we don’t use Service References, instead we use the ChannelFactory class to instantiate a proxy based on a given interface (the “contract”). If you’re new to WCF Services, don’t worry about what this means, only that it explains the presence of “new ChannelFactory<TChannel>().CreateChannel()” in the code snippet below!

The generic extension method looks like this (don’t worry, it’s mostly comments):

And to use this in your code you would write:

In this example, when you call Foo() and everything goes well, the extension method calls Close() on the connection. When an exception occurs, before handing back control of the program execution to the code that is calling SafeInvoke(), the extension method calls Abort(), which correctly closes the connection.

Also worthy of note is the pattern used in the catch block. If you explicitly catch an exception and then “throw ex;”, the CallStack property of the exception will be unwound and will instead show the call stack at the SafeInvoke method, this effectively hides useful information about what caused the exception, severely hindering debugging. By using catch{ throw; }, all of the original call stack information is retained.

A common pattern when integrating with Sage 200 is to write custom SQL queries in order to read large volumes of data quickly. This can be done by creating views or stored procedures on the database, or by assembling SQL commands in code (for example in an add-on assembly).

This method is often chosen in preference to reading data via the Business Objects because it is fast and memory efficient. In particular, creating a large collection of complicated Business Objects can use a lot of memory and this overhead is unnecessary if the goal of the operation is simply to read a set of data from Sage 200 into an integrating application or system.

Using SQL to access the database directly has advantages, but it also has some problems, namely:

• The database contains very little information about the business relationships between entities – these have to be inferred (or guessed at!) by the query creator
• Application logic that determines access to data based on identity will be ignored
• Values that are calculated by the system but not held in the database are not available to you – for example calculated totals in project structures, or aged debtor values. These are useful figures but if you want to include them in a SQL query you have to recreate the calculation logic yourself (not easy!)
• Direct access to the database is unlikely to be supported in future cloud deployments of Sage 200

However, there is another approach that is both fast and memory efficient and which provides access to calculated fields as well as automatically providing access to the relevant links between tables and entities.

This approach is to use a Data Model to access the data. Data Models provide the data sources for the Sage 200 Report Designer and also for Desktop Workspaces and the Sage 200 Mobile solution. A Data Model is an assembly that provides a representation of entities and collections of entities that can be queried in familiar ways.

A simple model might be a layer over the database, providing a one-to-one mapping between tables in the database and objects in the assembly.

A more sophisticated model might add calculated fields or even create whole new entities or wrappers in order to present the data in a more useful way.

Below is an example of how to access a model from code.

Why not try this out with your own data and compare the performance against a direct to SQL query? You should find that a query via the model will typically only be a little slower than a direct to SQL query and has the advantages of access to calculated fields, simpler queries due to known joins and references being automatically resolved and insulation from changes to the raw database schema.

Note that this code snippet was written in LinqPad and makes use of the Dump() extension method. The code makes its own connection to the application which would be unnecessary if it was running as an assembly inside the context of the application.

The following are the required assembly references for this example:

Sage.Common.DynamicLinq.dll
Sage.Common.LinqExpandable.dll
Sage.QueryExtensions.dll
Sage.Common.LinqExtensions.dll
Sage.Common.dll
Sage.ObjectStore.dll
Sage.Accounting.PersistentObjects.dll
Sage.Utils.dll
Sage.Accounting.Common.PersistentObjects.dll
Sage.Accounting.Common.dll
Sage.Data.dll
Sage.Accounting.DataModel.dll
Sage.Data.ModelManagement.dll
Sage.Accounting.Financials.dll

… and these are the namespace imports

Sage.Accounting.Common
Sage.Accounting.Financials
Sage.Common
Sage.Common.LinqExtensions
Sage.ObjectStore
Sage.Accounting.DataModel
Sage.Data.ModelManagement
Sage.Data

Today we have released a new project into the Sage 200 Labs – an integration with delivery management service provider, MetaPack.

The aim of the project is to demonstrate how to hook into various points of Sage 200 processing using the messaging service. This particular project hooks into the SOP order processing pipeline by subscribing to a message published by Sage 200 after a sales order is saved. There are more extension points published by Sage 200, both in the SOP order processing pipeline, and elsewhere.

Metapack is a provider of delivery management solutions, and it makes its functionality available through a set of web services. There are services to fetch a list of delivery options, for example, and a service to allocate a consignment to a carrier for delivery.

By hooking into the sales order process, the integration module can read information relating to the saved sales order, and construct a request to the MetaPack web services to create a consignment for delivery. It is automatically allocated to the carrier which best fits the requirements of the order. The allocation process can take into account factors such as package size and weight, the pick-up and drop-off locations, and despatch and delivery dates.

A workspace showing order tracking information.

Workspaces make it possible to add all sorts of rich content to the Sage 200 desktop. Sometimes this needs genuine development work (e.g. custom content part types) but the Linked Web Browser content part type that we ship with Sage 200 v2009 makes it possible to include some pretty nice content quickly and easily. This post will show how you can add charts to your workspaces in a lightweight way using the Google Image Chart API. Now, you may know that we are including a dedicated chart content part type with Sage 200 v2010 so this post will be redundant for customers on that version. It could be useful though as long as you have customers on v2009. As well as this, it is also a good example of the kind of rich content you can get into workspaces using the Linked Web Browser content part type.

To set the scene, I’m going to show you how to create a workspace in Sage 200 v2009 that looks like this

And this

The first panel is a simple customer list and the details view on the right shows the customer details. The bottom left panel allows the user to look at either the top 10 products for the selected customer in a pie chart, or the last 6 months sales for the selected customer in a bar chart. The charts are provided courtesy of the Google Chart API and the Linked Web Browser content part type. No custom content parts are needed for this. In principle you could go to a customer site and create this right inside the Sage 200 desktop in front of them (Note: The query behind it is a bit complex so I wouldn’t recommend actually doing this unless you are very confident with your LINQ!)

If you’ve read enough and you just want to get your hands on the charts, you can just download a package with the sample workspace in it. If you want to delve into some of the LINQ behind the charts, read on…

The Google Image Chart API is a web service that dynamically generates an image of a chart based on the query string of a URI. A simple example from the Google help pages uses this URI

http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250×100&chl=Hello|World

To generate this chart image

The API is tricky but quite flexible and you can generate lots of different chart types. The key for us is to create a LINQ query that generates the right URI from the source data. You can see from the sample that the chart data itself is contained in the URI. The bit of the URI that says

…chd=t:60,40…

Specifies the size of the pie chart segments. The labels are specified by the bit that says

…chl=Hello|World

You can cut and paste this URI into a browser and play with the values to see how it works.

The Linked Web Browser content part type works in a  simple way. It exposes a single property which represents the URI of the web content it will display. The query behind its parent content part must generate a URI which is then passed to the Linked Web Browser using the normal parent/child relationship mechanism for workspaces. This URI can be a local one (e.g. a file:: scheme URI to display a PDF file from the local file system) or a web one as in this post. The only thing the Linked Web Browser content part does is validate that the URI is well-formed. If doesn’t do any database access or manipulate the passed-in URI in any way. So the responsibility for creating the URI sits with the LINQ query from the parent content part.

This chart API is easy for simple charts like the Hello World pie chart, but obviously more complex when you have to generate a more realistic URI in a LINQ query. In real situations these queries will probably be quite involved so performance may be an issue if you’re not careful. A tip that can help this is to use a simple linking content part to ensure that you only construct each URI when you need it. In the example this is the small list in the middle on the left hand side. The query logic  to generate the chart API URIs is contained in this content part, rather than the main customer list. The linking part is then filtered by the selected customer ID in the first list so the complicated part of the query is only executed for a single customer at a time. This should significantly reduce the query time, but as always, it’s best to thoroughly check out performance on real data before you put anything live.

In the sample example for this post, there are actually two different queries (one for each chart type) that are concatenated together using the LINQ Concat query operator.

The first query (called last6MonthSales) gets the last six months sales (obviously). It is structured into 4 sections:

The first section sets does basic filtering  to get the right SOPOrderReturns to start with

DateTime date = DateTime.Today;
string baseUri = "http://chart.apis.google.com/chart?cht=bvg&chs=800x375&chd=t:";
var last6MonthSales = cxt.SOPOrderReturns
.Where(order => order.DocumentDate.Value.Year == date.Year)
.Where(order => order.DocumentDate.Value.Month >= date.Month - 6 && order.DocumentDate.Value.Month <= date.Month) .Where(order => order.DocumentType.SOPOrderReturnTypeName == "Sales Order")
.Where(order => order.DocumentStatus.Name != "Cancelled")

The second section groups the results by customer ID and the month of the order relative to the current date and selects the sum of the the order gross value

.GroupBy(
order => new
{
order.CustomerID,
RelativeMonth = date.Month - order.DocumentDate.Value.Month
},
(key, orders) => new
{
key.CustomerID, key.RelativeMonth,
Total = orders.Sum(order => order.TotalGrossValue)
})

This is then grouped again, this time just by the customer ID and the total order value for each of the current month and six previous months are separately selected. This select also pulls out the month name for each month.
.GroupBy(
item => item.CustomerID,
(customer, items) => new
{
SLCustomerAccountID = customer,
Max = items.Max(item => item.Total),
Month0Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 0).Total) ?? 0,
Month1Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 1).Total) ?? 0,
Month2Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 2).Total) ?? 0,
Month3Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 3).Total) ?? 0,
Month4Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 4).Total) ?? 0,
Month5Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 5).Total) ?? 0,
Month6Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 6).Total) ?? 0,
Month0Name = (date.AddMonths(-0)).ToString("MMM"),
Month1Name = (date.AddMonths(-1)).ToString("MMM"),
Month2Name = (date.AddMonths(-2)).ToString("MMM"),
Month3Name = (date.AddMonths(-3)).ToString("MMM"),
Month4Name = (date.AddMonths(-4)).ToString("MMM"),
Month5Name = (date.AddMonths(-5)).ToString("MMM"),
Month6Name = (date.AddMonths(-6)).ToString("MMM"),
})
The final select uses these monthly totals to construct the chart URI
.Select(
customer => new
{
GraphType = "Last 6 month sales",
ID = customer.SLCustomerAccountID,
ChartUri = baseUri +
customer.Month6Total + "," +
customer.Month5Total + "," +
customer.Month4Total + "," +
customer.Month3Total + "," +
customer.Month2Total + "," +
customer.Month1Total + "," +
customer.Month0Total +
"&chds=0," +
customer.Max +
"&chco=5131C9&chxt=y,x" +
"&chxl=1:|" +
customer.Month6Name + "|" +
customer.Month5Name + "|" +
customer.Month4Name + "|" +
customer.Month3Name + "|" +
customer.Month2Name + "|" +
customer.Month1Name + "|" +
customer.Month0Name +
"&chxs=0,000000,12,-1|1,000000,12,0" +
"&chxr=0,0," + customer.Max +
"&chbh=a" });
The second query (productBreakdown) gets the top 10 stock items per customer. This is structured as follows:

The first section selects from SLCustomerAccounts with a sub-query to select the top 10 stock items using a GroupJoin (which results in a SQL left outer join) between StockItems and SOPOrderReturnLines

var productBreakdown = cxt.SLCustomerAccounts
.Select
(
s=>new
{
ID=s.SLCustomerAccountID,
Items = cxt.StockItems
.GroupJoin
(
SOPOrderReturnLines.Where(line => line.SOPOrderReturn.CustomerID == s.SLCustomerAccountID),
item => item.Code,
line => line.ItemCode,
(item, line) => new { item.Code, line }
)
.SelectMany
(
itemLine => itemLine.line.DefaultIfEmpty(),
(itemLine, line) => new
{
itemLine.Code,
Value = (decimal?)line.LineTotalValue
}
)
.GroupBy
(
itemLine => itemLine.Code,
(code, itemLines) => new { code, Total = itemLines.Sum(item => item.Value) }
)
.OrderByDescending(item => item.Total)
.ThenBy(item => item.code)
}
)

The second section constructs the desired chart URI

.Select
(
s=>new
{
GraphType = "Product breakdown",
s.ID,
ChartUri="http://chart.apis.google.com/chart?cht=p&chds=0,100000&chco=FF0000,FFFF00,00FF00,0000FF&chs=500x250&chd=t:" +
s.Items.Skip(0).First().Total + "," +
s.Items.Skip(1).First().Total + "," +
s.Items.Skip(2).First().Total + "," +
s.Items.Skip(3).First().Total + "," +
s.Items.Skip(4).First().Total + "," +
s.Items.Skip(5).First().Total + "," +
s.Items.Skip(6).First().Total + "," +
s.Items.Skip(7).First().Total + "," +
s.Items.Skip(8).First().Total + "," +
s.Items.Skip(9).First().Total +
"&chdl=" +
s.Items.Skip(0).First().code + "|" +
s.Items.Skip(1).First().code + "|" +
s.Items.Skip(2).First().code + "|" +
s.Items.Skip(3).First().code + "|" +
s.Items.Skip(4).First().code + "|" +
s.Items.Skip(5).First().code + "|" +
s.Items.Skip(6).First().code + "|" +
s.Items.Skip(7).First().code + "|" +
s.Items.Skip(8).First().code + "|" +
s.Items.Skip(9).First().code
}
);

You can download an add-on package containing a complete workspace featuring the two queries. Note – you will need to be a registered developer to access this. If you are not a developer, post a comment and I’ll find another way to get it to you.

This is a lightweight way of livening up workspaces with charts. It doesn’t have any advanced features such as being able to interact with the chart, and the underlying querying can be complicated because you have to build the entire chart data and formatting into a single string. These are the reasons why we have developed a dedicated chart content part type for Sage 200 v2010. However, if you can’t wait until then or if you have customers that do not want to upgrade to v2010, you could use this approach.

The syntax for a flat left outer join in LINQ is a bit different from the corresponding SQL. Using Lambda syntax it looks like this.

(Note: to make these samples work in LINQPad you have to remove the references to “context”. See this post for details.)

var q =
context.PCProjectItems
　　　　.GroupJoin
　　　　(
　　　　　　　　context.PCProjectEntries,
　　　　　　　　item=>item.PCProjectItemID,
　　　　　　　　entry=>entry.ProjectItemID,
　　　　　　　　(item,entry)=>new{item.PCProjectItemID,entry}
　　　　)
　　　　.SelectMany
　　　　(
　　　　　　　　itemEntry=>itemEntry.entry.DefaultIfEmpty(),
　　　　　　　　(itemEntry,entry)=>new
　　　　　　　　{
　　　　　　　　　　　　itemEntry.PCProjectItemID,
　　　　　　　　　　　　Goods=(decimal?)entry.GoodsAmountInBaseCurrency
　　　　　　　　}
　　　　);

In comprehension syntax, the same query looks like this:

var q = from item in context.PCProjectItems
join entry in context.PCProjectEntries
on item.PCProjectItemID equals entry.ProjectItemID into itemEntries
from itemEntry in itemEntries.DefaultIfEmpty()
select new
{
item.PCProjectItemID,
Goods = (decimal?)itemEntry.GoodsAmountInBaseCurrency
};

Take your pick which syntax you prefer, or you can mix them in the same query if you like! In any case, both queries result in the same SQL being issued to the database:
SELECT [t0].[PCProjectItemID], [t1].[GoodsAmountInBaseCurrency] AS [Goods]
FROM [PCProjectItem] AS [t0]
LEFT OUTER JOIN [PCProjectEntry]
AS [t1] ON [t0].[PCProjectItemID] = [t1].[ProjectItemID]

In Sage 200 2010 purchase order authorisation has been extended to offer, amongst other things, an email notification feature. Purchase orders that qualify for authorisation can trigger email notifications as they progress through the system, alerting those involved in the authorisation workflow when they might need to take action.

Out of the box, there are two kinds of email format supported: plain text and HTML. The default format is HTML because of the rich formatting options it provides and its wide support from email progams. A typical email notification will look like this:

Organisations with concerns over HTML security or that simply prefer a text layout may find it more appropriate to use the plain text option.

Changing between email formats is a relatively simple matter of changing a configuration option and since this is the kind of change you will only ever make once or twice, we hid the setting away in the Sage200 Web Services web.config file.

To change the setting:

• open the web.config file in your favourite text editor, on most systems it should be somewhere like C:\inetpub\wwwroot\Sage200WebServices
• search for: mapTo=”Sage.Web.Services.EmailFormatting.HtmlNotification, Sage.Web.Services.EmailFormatting”
• replace “HtmlNotification” with “PlainTextNotification”
• save the file and restart the web server

The next set of authorisation emails will look like this:

You may notice that both designs are fairly plain, this is intentional so that they will display consistently on a wide variety of desktop and web email client programs.

Their content is also a balance between including all the possible information about a purchase order and conveying the most important information to be able to quickly make an authorisation decision.

You may find that too much or too little information is included in these layouts or that you want to add some graphics or branding to the emails to better fit in with your organisation’s existing designs. We based the email generation on XML and XSLT so reformatting the information and changing the entire look is fairly easy. When the notification service generates the emails it creates an XML representation of the purchase order and applies a set of default XLST transforms to it to produce the email body. This default transformation can be overridden by creating an XLST file in the service bin folder called POPOrderNotification.xslt. If this file exists, the notification service will use it in preference to the defaults.

Below is an example of what can be achieved in a short amount of time. The changes we will make are:

1. Added a Sage logo
2. Changed the main heading to include the order creation date
3. Reduced the amount of information on each order line
4. Changed background and highlight colours

The XSLT code has been slightly simplified for clarity.

<xsl:template match=”/”>
<html>
<body>
<img src=”http://www.sage.co.uk/images/sageLogo80.gif” />

<!– Order Header –>
<h2 style=”background: #fff; color:#666; margin: 0px; padding: 0 2px 2px 4px; line-height: 0.9em;”>
P.O. <xsl:value-of select=”PurchaseOrder/Header/DocumentNumber” /> entered on <xsl:value-of select=”PurchaseOrder/Header/DocumentDate” />
</h2>
<h3 style=”background: #fff; color:#666; font-family: Segoe UI, Tahoma, Arial, Helvetica, sans-serif;”>
<xsl:value-of select =”PurchaseOrder/Header/Supplier/Name”/> (<xsl:value-of select =”PurchaseOrder/Header/Supplier/Reference”/>)
</h3>
<!– End of Order Header –>

<!– Order Lines –>
<xsl:apply-templates select=”PurchaseOrder/Detail” />
<!– End of Order Lines –>

<!– Totals –>
<xsl:apply-templates select=”PurchaseOrder/Header/Totals” />
<!– End of Totals –>
</body>
</html>
</xsl:template>

<xsl:template match=”Detail”>
<table width=”100%” border=”0″ cellpadding=”2″ style=”color:#000000;font-family: Segoe UI, Tahoma, Arial, Helvetica, sans-serif; font-size: 0.875em;”>
<tr>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Code</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Description</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Quantity</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Net Value</b></th>
</tr>

<xsl:for-each select=”Line”>
<tr>
<td valign=”top” style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Code”/>
</td>
<td valign=”top” style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Description”/>
</td>
<td valign=”top” align=”right” width=”50″ style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Quantity”/>
</td>
<td valign=”top” align=”right” width=”100″ style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Total”/>
</td>
</tr>
</xsl:for-each>

</table>
</xsl:template>

One thing to note in designing the XSLT is that the HTML used in the email not very (or at all) compliant with modern web standards. This is a consequence of trying to achieve a relatively consistent look across the diversity of HTML rendering engines used by email programs. Most of these are still not standards compliant and none of them agree on what a correctly formatted HTML email looks like. Creating an HTML layout is often more about finding an agreeable “lowest common denominator” than the single “correct” solution. If you are targeting a single email client application you may be able to clean up a lot of this code and reduce the number of table and style attributes.

This is what the new email should look like:

If neither of these alternatives will give you the results you need, you can also write your own .NET assembly and have that do whatever formatting you like. We will put more information on this approach in the Sage 200 2010 developer SDK.

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!

Please note: This is a long and very technical discussion of some of the .NET language features we use in Workspaces. It is not Sage 200 specific.
Please let us know what you think of the technical level of the article – we’d love to get feedback on how we’re doing that helps us improve the value of this blog!

Lambda Expressions and Extension Methods are powerful new features in .NET 3.0 and are often used extensively when writing LINQ. In fact, these features are little more than syntactic sugar; they don’t enable functionality that wasn’t previously possible, but they do allow code that would previously have been quite complex to be written in a succinct, clear and elegant way.

While they are quite straightforward to use, they are not particularly easy to explain or intuitive to understand. This document aims to explain how these language features work.

Error checking has been excluded from all examples for brevity and clarity – the code is not intended to be production quality. Some of the examples are also quite weak; they are intended to demonstrate use of the language, not necessarily good examples of when to use the features!

Many of the examples make use of IEnumerable collections. This was to allow complete examples to be given, including the data types. When using LINQ to SQL the collection types are based on the IQueryable interface, but this makes little difference to the code using the collections.

Simple Extension Methods
At their simplest, Extension Methods allow the definition of a class to be extended with additional methods; to add new methods to the class that appear identical to member functions.

For example, given a very simple “bank account” class:

class Account
{
public Account(string name, decimal balance)
{
this.name = name;
this.balance = balance;
}

public string name;
public decimal balance;
}

We may wish to create a function that allows funds to be deposited in the account.
This could be added as a member function, or as a public function taking the account as a parameter:

public void Deposit(Account account, decimal deposit)
{
account.balance += deposit;
}

// ....
// To deposit £10~~
Deposit(account, 10);

Alternatively, we could write this as an Extension Method.

static class Extensions
{
public static void Deposit(this Account account, decimal deposit)
{
account.balance += deposit;
}
}

// ....
// To deposit £10
myAccount.Deposit(10);

The defining characteristics of the extension method are:
• It’s a static function in a static class
• Use of the “this” keyword in the parameter list

Note that the method is not a “true” member function; private members of the class cannot be accessed, for example.

Extending Collections
Extension methods are not limited to concrete types; they can be used to extend anything that the compiler recognises as a strong type such as a collection of objects:

public List accounts = … ;

// ....
// To deposit £10 in Steve’s account
accounts.Deposit(“Steve”, 10);

Alternatively, we might want a method that finds all of the overdrawn accounts:

public static IEnumerable GetOverdrawnAccounts(this IEnumerable accounts)
{
foreach (Account account in accounts)
{
if (account.balance < 0)
yield return account;
}
}
Note the use of the “yield” keyword, which essentially says “add the item to the return set and continue”

Extension Methods and Delegates
A very common pattern with Extension Methods is to have a function delegate as one of the parameters, allowing function logic to be determined by the caller.

For example, we could rewrite our “GetOverdrawnAccounts” example as:

public static IEnumerable GetAccounts(this IEnumerable accounts, Func predicate)
{
foreach (Account account in accounts)
{
if (predicate(account))
yield return account;
}
}

IEnumerable overdrawnAccounts = accounts.GetAccounts(IsOverdrawn);
Note the use of the “Func” delegate overload, specifying a delegate parameter which takes an Account as its parameter and returns a bool.

The advantage of this approach is that we can now supply an alternative delegate without changing the code of the extension method:

static public bool IsStevesAccount(Account a)
{
return a.name == "Steve";
}

… = accounts.GetAccounts(IsStevesAccount);
This can be written (slightly) more succinctly using an anonymous delegate:

… = accounts.GetAccounts(delegate(Account a)
{
return a.name == "Steve";
});
This approach has an additional advantage in that the anonymous delegate can access variables from the calling scope.

In the examples above, we have determined the name of the account to be found at design time – if, however, the account name we want is not known until run-time then the named delegate approach would have a problem. As the prototype of the predicate is controlled by the extension method, we have no way to pass in a variable containing the name we want to find: we would need to create an alternative extension method, or use a variable at a higher scope (e.g. a global variable).

Using an anonymous delegate we can keep everything else the same, and:

string requiredAccount = "Steve";
… = accounts.GetAccounts(delegate(Account a)
{
return a.name == requiredAccount;
});
Similarly, we can now allow for an arbitrary overdraft limit in our list of the overdrawn accounts:

int overdraftLimit = 25;
… = accounts.GetAccounts(delegate(Account a)
{
return a.balance + overdraftLimit < 0;
});

Standard Extension Methods for Collections
In the examples given so far, we have written all of our own Extension Methods. This has been largely for expository purposes; a wide variety of extension methods are provided as part of the .NET framework which implement many of the standard operations you might wish to perform on a collection of objects (such as an IEnumerable or an IQueryable).

Here we give some simple examples of the standard extension methods, including versions of some of the earlier examples:

string requiredAccount = "Steve";
… = accounts.Where(delegate(Account a)
{
return a.name == requiredAccount;
});

int overdraftLimit = 25;
… = accounts.Where(delegate(Account a)
{
return a.balance + overdraftLimit < 0;
});

decimal totalBalance = accounts.Sum(delegate(Account a)
{
return a.balance;
});

decimal averageBalance = accounts.Average(delegate(Account a)
{
return a.balance;
});

For lists of the standard methods, see the MSDN articles IEnumberable Members and IQueryable Members.

Lambda Expressions
Lambda expressions are nothing more than an alternative syntax for defining an anonymous function delegate.

The expression follows the pattern:

Parameter list => Expression

Therefore :
delegate(Account a)
{
return a.name == requiredAccount;
}
Can be re-written as:
(Account a) => a.name == requiredAccount

As the compiler can infer the return type of the expression, it is not actually necessary to supply the type of the parameter, so this can be furthered simplified to:
a => a.name == requiredAccount

So we can now re-write our previous examples as:
string requiredAccount = "Steve";
… = accounts.Where(a => a.name == requiredAccount);
int overdraftLimit = 25;
… = accounts.Where(a => a.balance + overdraftLimit < 0);
decimal totalBalance = accounts.Sum(a => a.balance);
decimal averageBalance= accounts.Average(a => a.balance);
For more detail on other possibilities for Lambda expressions, including support for multiple parameters and multi-statement expressions, see the MSDN “Lambda Expressions in LINQ
One of the places we make most use of lambda expressions and the standard extension methods is in the LINQ for the Sage 200 Workspaces.

So an example of a simple query which returns a stock item code and the total confirmed quantity in stock across all warehouses could be written as:
var q = from si in StockItems
let wiSet = WarehouseItems.Where(wi => si.ItemID == wi.ItemID)
select new
{
si.Code,
ConfirmedQtyInStock = (decimal?)wiSet.Sum(wi => wi.ConfirmedQtyInStock)
};
Replacing the lambda expressions with anonymous delegate syntax:
var q = from si in StockItems
let wiSet = WarehouseItems.Where(delegate(WarehouseItem wi)
{
return (si.ItemID == wi.ItemID);
})
select new
{
si.Code,
ConfirmedQtyInStock = (decimal?)wiSet.Sum(delegate(WarehouseItem wi)
{
return wi.ConfirmedQtyInStock;
})
};
return q;

And without using extension methods (assuming very simplistic implementation of the query functions):
var q = from si in StockItems
select new
{
si.Code,
ConfirmedQtyInStock = GetTotalConfirmedQtyInStock(
GetWarehouseItemsForStockItem(WarehouseItems, si))
};
return q;

return result;
}


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.

Sometimes we need to work with data that should be grouped by a particular field or set of fields, but where we can’t be sure that there will always be data in each group.

For example, we might have a list of transactions, each one of which has been posted into a particular period. If I want to plot these on a chart, or pivot the data by period then I need to ensure that all of the period values are represented in the dataset, even though there may be some periods in which no transactions were posted. If no transactions were posted in period 2, then I want to see a gap in my chart, or a zero value under the “Period 2″ heading in my pivotted column – I don’t just want to ignore it.

One way of achieving this is by cross-joining the relevant set of category headings. Joining is one of the areas where the syntax and behaviour of LINQ is often a little different to SQL, but the behaviours provided are tremendously powerful, and can often simplify queries that would be difficult in SQL.

Cross joins in LINQ can be acheived using the SelectMany syntax. The query fragment below is an example of solving the above issue. Here we cross join nominal accounts to the current year’s accounting periods, ensuring that however we further group or filter the data, there will always be a value for any given period – perfect for populating a chart or pivotting.

return cxt.NLNominalAccounts
   .SelectMany
   (
      n=>cxt.SYSAccountingPeriods.Where(w=>w.SYSFinancialYear.YearRelativeToCurrentYear==0),
      (n,s)=>
      new
{
            NLNominalAccountID = n.NLNominalAccountID,
            PeriodYear = ((("FY" + s.SYSFinancialYear.FinancialYearStartDate.Year.ToString().Substring (2, 2)) + "P") + s.PeriodNumber.ToString().PadLeft (2, '0')),
            SYSAccountingPeriodID = s.SYSAccountingPeriodID,
      }
   )
   .GroupJoin
   (
        cxt.NLPostedNominalTrans.DefaultIfEmpty(),
        a=>new{a.NLNominalAccountID,a.SYSAccountingPeriodID},
        n=>new{n.NLNominalAccountID,n.SYSAccountingPeriodID},
        (a,n)=>new
        {
            a.NLNominalAccountID,
            a.PeriodYear,
            a.SYSAccountingPeriodID,
            CreditValue = (n.Count()==0 ? 0 : n.Sum(t=>t.GoodsValueInBaseCurrency < 0 ? -t.GoodsValueInBaseCurrency : 0) ),
            DebitValue = (n.Count()==0 ? 0 : n.Sum(t=>t.GoodsValueInBaseCurrency > 0 ? t.GoodsValueInBaseCurrency : 0) )
        }
    );