RTD Servers and Add-in Express 2010
Real-time data (RTD) is data that updates on its own schedule. The most common example is stock quotes, warehouse activities and web server loads. In this post, Northwind Traders has requested a sales dashboard in Excel that updates sales figures and targets dynamically by reading order information from their sales database.
Let's start by creating a new project in Visual Studio 2010, and selecting the ADX RTD Server project type:
Complete and finish the wizard, by selecting your language of choice, in my case I’ve chosen C#. After the wizard has generated the necessary items, select the RTDServerModule.cs file and view its designer by either selecting it from the context menu or clicking the View Designer button on the Solution Explorer toolbar. Leave the RTDServerModules' Interval property on 5000 milliseconds for now, when we go-live it will be set to 15 minutes as per the customers' requirements.
Add a RTD topic to the designer by right-clicking on the designer surface and selecting Add RTD Topic from the context menu. In order for us to pass a parameter to the RTD server we need to set the String01 property to an asterisk (*). Add-in Express will then create a new topic and pass it to the RefreshData event, but more on that a bit later. Next, set the String02 property to ActualSales. This string is used to identify the topic.
Ok, in order to hook up the RefreshData event handler, select the topic in the RTDServerModule designer, view its event and double click on the RefreshData event to generate an event handler:
In the code view, add the following to the RefreshData event:
private object adxrtdTopic1_RefreshData(object sender) { object returnValue; AddinExpress.RTD.ADXRTDTopic topic = (AddinExpress.RTD.ADXRTDTopic)sender; ActualSales actualSales = new ActualSales(topic.String01); returnValue = actualSales.ActualUnitsSold; return returnValue; }
The ActualSales object is a custom class that retrieves the actual units sold based on the product name from the Northwind database. The code for this class:
public class ActualSales { public int ActualUnitsSold { get; set; } public ActualSales(string productName) { productName = productName.Replace("'", "''"); using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=True")) { using (SqlCommandcmd = new SqlCommand(String.Format("Select ActualSales From Products Where ProductName = '{0}'", productName), conn)) { conn.Open(); this.ActualUnitsSold = (int)cmd.ExecuteScalar(); } conn.Close(); } } }
Once you have all the code in place, build and register your project and start Excel. Create a sheet with a list of Northwind Products and the target number of units to be sold per product, for example:
Next to each product name, add the following Excel formula:
=RTD(“SalesRTDServer.RTDServerModule”,,A3,”ActualSales”)
The RTD server should now pick up the data from the database per product:
As the data change in the database, so too will the data update automatically in the excel sheet. But Northwind asked for a dashboard and what is a dashboard without a graph? Luckily adding a graph to Excel is an arbitrary task.
Select all the cells from A1 to C6 and select a Line chart from the Insert ribbon tab. Your final result will look like this:
You'll notice as the data changes automatically every 5 seconds, so too will the graph update automatically, providing a real-time view of Northwinds sales.
Thank you for reading, until next time keep coding!