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]

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


When we first started thinking about workspaces in mid 2008 we expected to be doing queries using SQL and showing the results in the Sage 200 desktop. After all, Sage 200 is based on a SQL Server database and naturally, our customers are only interested in data that we hold in our database. And Sage 200 is the only tool they use…right?

But then we started thinking “what about data that we don’t keep in our database such as spreadsheets or unstructured documents like emails, PDFs or word documents? And what about data from the web or from web services?” We realised that our customers have useful data held in lots of different places and that we should be looking to bring that together with Sage 200 data in a personalised simple but powerful way to give our customers a fuller, more rounded view of their business.

And once a customer has created a view they like, they should be able to see it in whatever way they want. If they are going to use the data to do tasks in Sage 200 then sure, the Sage 200 desktop is the place to show it. But maybe they want to save the view as a PDF to email to a colleague. Or maybe they want to share it with colleagues in Sage 200 CRM. Or see it on their mobile while they’re out of the office? Or use it in a spreadsheet to do some ad-hoc analysis. Obviously the views can’t look the same in all these places, but the underlying data can be the same, as can the way it is linked together.

This thinking eventually led us to select Linq as a querying language instead of SQL (try querying a web service using SQL) and to the concept of workspaces. As of the Sage 200 2009, the only place that workspaces are visible is in the Sage 200 desktop but with the 2010 release we will start realising the broader vision outlined above with workspaces on the Sage 200 mobile application. The 2011 release will see more of the vision being implemented (probably as formatted reports).

To understand how workspaces translate between the Sage 200 desktop, a mobile device and formatted reports, you first need to understand that the what you see in the Sage 200 desktop is divided into two layers.

At the bottom, more abstract layer, a workspace simply consists of a set of queries, potentially from a variety of data sources. These queries can be linked together at the data level by parent/child relationships that you can define in the workspace designer. But even without these parent/child relationships they are also linked together at the business logical level by virtue of the fact that a user (or developer) has seen fit to put them all in the same workspace. The following diagram shows this abstract view of the sample Google workspace from the Sage 200 SDK:

The top level is how the abstract definition is visualised to the customer. The data from each query is visualised as a list, a detail view or in a web browser pane. The business logical linking is visualised simply by arranging all the lists and views in the same screen. The parent/child relationships are visualised as filters that get applied to the child query when each item in the parent query is selected and by automated hyperlinks. The whole thing is specialised for the Sage 200 desktop by adding filters, column ordering and hiding, sorting and a right-click context menu.

Obviously on a mobile device there is no room to display all that data at the same time, so we keep the same abstract definition, but visualise it in a different way that is tailored to the mobile device. Again the queries are visualised as lists or detail views, but without some of the extra features that you get in the desktop. The business logical linking is visualised by collecting all the lists under a single menu item on the device. and the data level linking is visualised as a menu based drill-down.

 For formatted reports, the absract workspace will be visualised using header and details sections in reports, with sub reports, drill down reports and hyperlinks.

This whole framework is made possible by Linq. Linq provides the powerful query language that can target databases, spreadsheets, web services, full text search engine indexes and the rest. It also provides a uniform querying interface that can be used to bind to windows controls in the Sage 200 desktop, mapped onto SData requests from our mobile application and built into a generic data provider for the Sage Report Designer.

There is certainly a learning curve with Linq, but hopefully this post explains why we think that it is worth getting to grips with!