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.

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.

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!