Sage Developers' Blog

Adding charts (and other rich web content) to Sage 200 v2009

with 5 comments

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.

Written by mike.goodwin

April 9th, 2010 at 3:13 pm

5 Responses to 'Adding charts (and other rich web content) to Sage 200 v2009'

Subscribe to comments with RSS or TrackBack to 'Adding charts (and other rich web content) to Sage 200 v2009'.

  1. Hi,

    This functionality looks fantastic, unfortunately I’m not a registered developer, is there any way you get the downloads to me?

    Thank You,

    Tom

    Thomas Collis

    28 May 10 at 16:18

  2. Hi. I’d really appreciate a copy of the add-on package. I’m not a developer either, but I’m trying to figure out how to incorporate external data into Sage 200 workspaces.

    My data is in SQL but I don’t know how to create a Data Model that I can use in the Workspace Designer.

    Any advice would be much appreicated.

    Mat

    20 Sep 10 at 17:10

  3. Digg…

    While checking out DIGG today I found this…

    Oak Fire Doors

    18 Jan 12 at 15:37

  4. Digg…

    While checking out DIGG yesterday I found this…

    Blinds

    31 Jan 12 at 17:36

  5. Its hard to find good help…

    I am constantnly proclaiming that its hard to get quality help, but here is …

    Oak Doors

    8 Feb 12 at 18:13

Leave a Reply