Sage Developers' Blog

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) )
        }
    );

Our recommended approach for deployment of new or customised Workspaces to a customer site is to create an Add-On (SDBX) package for it.
This approach makes deployment quite straight-foward. Eating our own dog-food has, however, shown that using the tool to create these packages is anything but straight-forward. To be honest, it’s quite painful and error prone [see my update on this]:

You need to remember the following files:
1 Workspace xml file(s)
2 Content Part xml file(s)
3 Content Part Info xml file(s)
4 Query xml file(s)
5 Workspace Layout file(s)

You also need to remember to set the correct file type for each file (particularly tricky for Layouts, which are actually two files and must be installed using the “resource” file type).
This is further complicated by the fact that many of the files will have the same names, and therefore folders need to be created inside the package.

In an attempt to simplify this in the short term (until we’ve included a proper solution within the desktop), I’ve created a simple application for packaging workspaces.
The tool will allow you to select any modified Workspaces you have on your system for inclusion in the package and will give you the choice of including multiple Workspaces in a single package, or generating a Workspace per package.

Note that it will only include files in your “Modifications” folder – if you want to include files from other AddOns (”Extensions”) or from the core system (”Default”) you’ll need to copy them to “Modifications” first.
The exception to this are Workspace Layout files, which will always be included if they are not one of the four defaults.

The caveats:

• You will only be able to acces this link if you are a registered Sage 200 Business Partner or Developer. Apologies if this causes you any inconvenience – if it does, please leave a comment and we’ll get in touch to find out what we can do.
• The tool will not be officially supported by Technical or Developer Support – again, please leave a comment with any questions, comments, suggestions or problems.
• The tool was created in my own time for my own convenience – and it’s been a long time since I was a professional developer… It has not had the level of engineering or Q/A rigour you would expect from an official Sage product (it isn’t one) and you should treat it with the same suspicion you would any other tool downloaded from the web!

Download the Installer

Update:

Looking back, perhaps I should have given a little more detail in my post about the purpose of this tool and the reason I developed it.

As I’m sure you’re aware, Sage 200 provides a mechanism for third party developers to package up their amendments or enhancements to the core system and deploy them across a customer’s site via a reliable and supported mechanism. This system, and the tools that enable it, were aimed squarely at our developer community and require a certain level of technical proficiency, as befits their nature.

With the introduction of our “Workspaces” technology we extended these same tools so that new or amended workspaces could be deployed via the same mechanism. Since Workspaces don’t require quite the same level of technical proficiency to develop, we have come to the conclusion that the tool is too complex for this particular application and that a simpler – more focussed – tool would be more appropriate. This is a conclusion we’ve reached over a period of time – using the tools ourselves.

I described the process as “quite painful and error prone” because that’s exactly how I’ve come to see it – Workspaces comprise a number of files and it’s very easy to miss one of them, so the whole process can take longer than it feels like it should. However improving usability is something we’re always keen on (and the approach we have in mind will be much easier than any standalone tool could ever be).  Of course, this takes time – we need to design, implement and test the improvements and then include in a scheduled release. In the meantime, we have something we’re finding helpful ourselves and we have the opportunity to give others the chance to use it too. I think it’s a great thing that we are able to do so.

Our blog is a space where we can be open, honest and do our best to help, so do continue to let us know how we’re doing.

Sometimes it can be useful to create lists that contain content that is fixed by you in the query and isn’t fetched from a data source.

A simple example of this might be creating a set of links to web-based content.  The links are fixed, but the content may change.  In the following example, the list data is first written  into a string array and then ‘Selected’ and cast to an IQueryable collection.  The only slight subtlety with this is the necessity to put this into the Queryable form and to ensure that fields are labelled and that the system can infer their type using the Select statement:

string[] Descriptions = new string[3];
//
Descriptions[0]="What is a workspace?|Bp5nPGr0ptw";
Descriptions[1]="How To Build A Sage 200 Workspace - Part 1|1wBcLZkBlxM";
Descriptions[2]="How To Build A Sage 200 Workspace - Part 2|98I7REkQTGA";
//
Descriptions.Select
(
    s=>new
    {
        Description=s.Split('|')[0],
        URL="http://www.youtube.com/v/" + s.Split('|')[1] + "&hl=en_US&fs=1&"
    }
)
.Dump();

A more practical scenario might be one where you need to programatically develop some fields in a query, but retrieve other fields from a data source.  In the following LINQPad snippet (for example) we compare daily sales for two calendar years.  The Sage 200 data only has information about days where we actually made sales.  If we want to compare these two series against an accurate timeline, then we need to generate our own list of days and join the series for the two years onto it:


int[] days = new int[365];
//
for(int i=0; i<365; i++){days[i]=i+1;}
//
var q1=days.Select
 (
  s=>new
  {
   Day=s
  }
 );
//
var q2=SOPOrderReturns
 .Select
 (
  s=>new
  {
   SaleDay=s.DocumentDate.Value.Day,
   Value2008=(s.DocumentDate.Value.Year==2008) ? s.TotalGrossValue :0,
   Value2007=(s.DocumentDate.Value.Year==2007) ? s.TotalGrossValue :0
  }
  )
 .GroupBy(g=>g.SaleDay)
 .Select
 (
  s=>new
  {
   SaleDay=s.Key,
   V2007=s.Sum(e=>e.Value2007),
   V2008=s.Sum(e=>e.Value2008)
  }
 );
//
var q3 =q1
  .GroupJoin
  (
   q2.DefaultIfEmpty(),
   l=>l.Day,
   r=>r.SaleDay,
   (l,r)=>new
   {
    l.Day,
    V2007=(r.Any()) ? r.First().V2007 : 0,
    V2008=(r.Any()) ? r.First().V2008 : 0
   }
  )
.Dump();


With the new System Admin tool in 2009 we have separated the concepts of menu design and restricting access to specific features.
We have allowed a user (with sufficient privileges) to amend a menu for a role, so that all members of that role will see the changes.

Adding a new menu item to all/many roles, however, is not an obvious process; the apparent approach is to add the same menu item to each role separately. This can be very time consuming for customers with lots of roles.

There is an alternative…!
Note that the approach is covered in the system help file in the topic ‘Add a new navigation group or option to all roles’ (or search for ‘Add a new navigation group’) but here we cover it in a little more detail.

Consider the scenario where the user has created a new report that they want to make available to everyone, irrespective of their role.
The overall principle we’re going to follow is to export a modified menu as an add-on package, then reimport it – which will amend the core menu template used by everyone.

1. Create the feature and target in SAA, then add the report to your own menu as usual

2. In the desktop menu designer, right click on the report and select “Export” from the context menu

3. In the wizard, “Next” past the first page then enter a path to the location where you want to create the package. It doesn’t matter where, so long as you can find it again!

4. “Next” to the last page, enter an “Add-on Name” (again, it doesn’t really matter what) and click “Generate”

5. Open the System Administration tool and go to the “Add-Ons” page
6. Right-click on the right-hand side of the screen and select “Add New Add-On”

7. Click “Next”, “Browse” to the package you just created and “Install”

The menu item has now been added to the core menu template; all you need to do now is authorise access to the feature for all roles that need it – this will determine whether the menu option actually appears for any given user.