Chapter 27 - Creating an Interactive Digital Dashboard

Article
01/28/2010

Introduction

A digital dashboard is a portal composed of Web components (called Web Parts) that can be combined and customized to meet the needs of individual users. Web Parts are reusable components that wrap Web-based content such as XML, HTML, and scripts with a standard property schema that controls how Web Parts are rendered in a digital dashboard.

This chapter explains how to build a digital dashboard that contains interactive Web Parts that respond to events generated by other parts in the same dashboard. (This chapter assumes you are familiar with Microsoft® SQL Server™ 2000, XML, scripting, and Web application development.) Dashboards support part integration through a set of services provided by the Digital Dashboard Services Component (DDSC). The DDSC includes Part Discovery, Part Notification, Session State Management, and Item Retrieval. There is an underlying object model that you can use to program the services into your code.

When building an integrated or interactive dashboard, Part Notification provides the most relevant service. Part Notification service refers to event notification and a corresponding response. Understanding how this service works is key to building interactive Web Parts. This chapter describes how to deploy this service in the context of building a simple dashboard.

A dashboard can be an arbitrary container for unrelated parts (for example, a collection of your favorite Web sites or applications arranged into a personal dashboard for easy access), or it can be a container of parts that work together by sharing, summarizing, or filtering the same data set. In the later case, the dashboard operates more like an application, with features and functionality distributed across multiple parts. This chapter describes the basic techniques you need to build exactly this kind of dashboard.

The objective of this chapter is to show you the process of creating an interactive dashboard and how to retrieve sample data from the Northwind database using the XML features in SQL Server 2000. Specifically, this chapter teaches you how to:

Create parts that get and transform XML-based data from SQL Server.
Reference an HTC file that defines HTML behaviors in your dashboard.
Use the Digital Dashboard Service Component (DDSC) to raise and respond to events occurring at the part level.
Create isolated frames that enable DDSC events to occur on the client, eliminating round trips to the server and improving security.

To illustrate these points, a Customer Information dashboard that contains two parts is created. The first Web Part presents a list of customers retrieved from Northwind. The second Web Part is a bar chart that shows order volume by year for a specific customer that you select. When the user clicks a value in the customer list in the first part, the DDSC raises an event that causes the second part to get and display summarized order data about that customer.

The actual dashboard and Web Part definitions will be created by you. The code samples included with this chapter provide the Web-based content that you use to create the Web Parts. Code for this chapter is provided on the SQL Server 2000 Resource Kit CD-ROM. Each step of the process is explained, and the tools and software you need to perform each step are identified.

To follow the steps in this chapter, you must have SQL Server 2000 running on Microsoft Windows® 2000, and the Digital Dashboard Resource Kit (DDRK) 2.01. From the DDRK, you must also install the SQL Server sample Administration digital dashboard. The sample dashboard provides a way to create dashboards and parts.The sample Administration dashboard is used to define the dashboard and parts described in this chapter.

In the process of creating the dashboard, you will need to do the following:

Ensure that your SQL Server 2000 installation supports SQL Server authentication.
Install the DDRK 2.01.
Install the SQL Server sample Administration dashboard from the DDRK.
Create virtual and physical directories to store the code sample files.
Copy the files to the directories you created in the previous step.
Edit the files to correct server name and path information.
Define a dashboard using the sample Administration digital dashboard.
Define a Customer List Web Part.
Define a Customer Order Chart Web Part.

About the Code Samples

Code samples provide the content of the Web Parts you will create. Web Part content can be XML, HTML, or scripts that get and transform data or that define events and behaviors. You can put the content in separate files that you reference or you can type it directly into the Web Part definition. For this exercise, the content is provided in files. Note that a single Web Part can use multiple files to supply functionality.

Code samples provided with this chapter include the following:

Customerlist.htm (provides content for the Customer List Web Part).
Customerlist.xml (contains an XML-based SQL Server query. This query gets a list of company names from the Customers table in Northwind).
Customerlist.xsl (transforms the company names in the Customer List Web Part).
Customerlist.htc (defines mouseover, mouseout, and click events for the Customer List Web Part).
Orderchart.htm (provides content for the Order Chart Web Part).
Orderchart.xsl (transforms order data for a specific customer).

The code sample files are commented to help you interpret the purpose and intent of the code. Snippets from these files appear in this chapter to illustrate key points.

Note Code samples require editing before you can use them. Many of the files contain placeholder values for your Microsoft Internet Information Services (IIS) server and virtual directories. Where indicated in the instructions, you need to replace the placeholder values with values that are valid for your computer.

Required Software

This chapter requires Microsoft SQL Server 2000, Microsoft Windows 2000, Internet Explorer 5.0 or later, and the Digital Dashboard Resource Kit (DDRK) 2.01.

SQL Server 2000

SQL Server 2000 is required because it includes XML support for exposing relational data as XML. In the sample dashboard you create, you access Northwind as XML from your Web browser by way of a virtual directory. SQL Server 2000 provides a tool for configuring a virtual directory for the Northwind database. Instructions for configuring this directory are covered later in this chapter.

If you install the DDRK on the same computer as SQL Server, your SQL Server installation needs to support SQL Server authentication. Hosting a dashboard and a SQL Server on the same computer means that the Web server (IIS) and SQL Server need to talk to each other. Having both the Web Server and SQL Server use the same integrated authentication mode results in a security violation; the Web server will be prevented from issuing a query to a SQL query when both servers reside on the same computer. To be able to query Northwind from your development computer, you need to use SQL Server authentication. Note that if SQL Server authentication is not enabled, you may need to reinstall SQL Server, selecting SQL Server authentication during the install process.

If SQL Server and the DDRK are installed on different computers, you can use whatever authentication mode you like. For more information about supported platforms and installation, see SQL Server Books Online.

Windows 2000

For this chapter, Windows 2000 and IIS 5.0 are required on the server hosting the digital dashboard. This means that the computer on which you install the DDRK must be running some edition of Windows 2000 server.

Clients do not require Windows 2000. Client platforms include any edition of Windows 2000, Windows NT®, and Windows 98.

Internet Explorer 5.X

Viewing the dashboard and processing the underlying XML requires Internet Explorer 5.0 or 5.5.

Digital Dashboard Resource Kit (DDRK)

Dashboard development starts with the DDRK, which provides the design-time framework and run-time components you need to deploy dashboards and parts. The DDRK 2.01 provides information and development resources. To learn about dashboards, you can read white papers, reference material, and overviews. Development resources include sample Administration digital dashboards that you can analyze to further your understanding of dashboard functionality.

More important, the sample Administration digital dashboards offer real functional value¯installing a sample dashboard simultaneously installs digital dashboard components, such as the dashboard factory, the DDSC, and dashboard storage support. The sample Administration digital dashboards also provide a user interface for creating new and modifying existing dashboards and parts, as well as the ability to set properties that control access and presentation.

The DDRK contains several sample Administration digital dashboards. For this chapter, we assume you are using the SQL Server Sample Administration Digital Dashboard. You will use this dashboard to create your own dashboard as well as define the Customer List and Order Chart parts.

Downloading and Installing the DDRK and SQL Server Sample Digital Dashboard

You can download and install the DDRK 2.01 from https://www.microsoft.com/business/default.mspx.

To install the SQL Server Sample Digital Dashboard, open the DDRK and go to Building Digital Dashboards. Choose Install the Microsoft SQL Server 7.0 Sample Digital Dashboard (note that this sample dashboard is fully compatible with SQL Server 2000).

During installation, you will be asked to create a new SQL Server database to store the dashboards and parts you create. When defining the login to this database, use sa for the user name and leave the password blank.

After installation completes, the Welcome page of the SQL Server Sample Administration Digital Dashboard appears (note the HTTP address for future reference). Click Administration to open the Administration page. This is the page you will use later to define a new dashboard and Web Parts.

Setting Up

This section explains how to get files into the right places and configure virtual directories.

Download the Code Samples

The code samples for this chapter are available on the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\DigitalDashboard. There are six files altogether.

In the next several steps, we will tell you where to place the files and which files need editing.

Create Physical and Virtual Directories for Your HTM and HTC Files

Use Windows Explorer to create a physical directory in your Default Web Site directory. By default, the path is C:\Inetpub\Wwwroot. To this path, you can add a subdirectory named Tutorial, resulting in this path: C:\Inetpub\Wwwroot\Tutorial.

Into this directory, copy the following code sample files:

Customerlist.htm
Customerlist.htc
Orderchart.htm.

Use Internet Services Manager to create a new virtual directory under Default Web Site for your HTM and HTC files. In Windows 2000, this tool is located in the Administrative Tools program group. To create a virtual directory, right-click Default Web Site, and then click New Virtual Directory. To match the path names used in the code samples, name your virtual directory Tutorial.

Create Physical and Virtual Directories for Your XML and XSL Files

To issue an SQL query through HTTP, you need to configure Northwind as a virtual directory. To do this, you use the Configure SQL XML Support in IIS tool, located in the Microsoft SQL Server program group. Instructions that describe this process in detail are provided in the topic "Creating the nwind Virtual Directory" in SQL Server Books Online. You should follow the instructions exactly. When you are finished, you should have the following physical directories:

\Inetpub\Wwwroot\nwind
\Inetpub\Wwwroot\nwind\schema
\Inetpub\Wwwroot\nwind\template

For each physical directory, you should have a corresponding virtual directory of the same name.

Into the \Inetpub\Wwwroot\nwind\template directory, copy the following code sample files:

Customerlist.xml
Customerlist.xsl
Orderchart.xsl

Note The nwind virtual directory is accessed by SQL Server when it retrieves data. The application virtual directory that you use to store the HTM and HTC files is accessed by the dashboard. This is why you need separate directories for each group of files.

Copy and Edit the HTM and HTC Files

After you copy all the files, you can adjust the server name and paths in the code sample files. In all cases, replace < your server name > with the name of your IIS server, correcting the virtual path names if necessary. Use the proper name rather than localhost for the server name. Using localhost results in permission denied errors when you add Web Parts later in the tutorial.

Open Customerlist.htm from the Tutorial folder using an HTML or text editor.
Edit the path in the IFRAME element: <IFRAME ID="CustFrame" SRC="https://< your server name >/nwind/template/customerlist.xml".
Save and close the file.
Open Orderchart.htm from the Tutorial folder using an HTML or text editor.
Edit the path in the SRC property of the ChartFrame object: document.all.ChartFrame.src = "https://< your server name >/Nwind?xsl=…".
Save and close the file.
Open Customerlist.xsl from the Template folder using an HTML or text editor.
Edit the path in td style 1048528364element1048528364: td {behavior:url(https://< your server name >/tutorial/customerlist.htc)}.
Save and close the file.

Building the Dashboard

This section tells you how to use the Administration sample dashboard to define a new dashboard and the parts that go in it.

Defining the Dashboard

A dashboard is a container for Web Parts. It is defined by a schema and supports properties that determine dashboard appearance and behavior. To create the Customer Information dashboard, you start by defining a new dashboard.

In your browser, open the Administration page of the SQL Server Sample Digital Dashboard. The default address is https://<your server name>/Dashboard/Dashboard.asp?DashboardID=https://<your server name>/Sqlwbcat/Welcome/Administration.
In the Dashboard View pane, select Sqlwbcat, and then click New to define a new dashboard. Sqlwbcat is the default name of both the SQL Server database and IIS extension that manages dashboard and part storage. The dashboard that you define will be stored and managed by Sqlwbcat.
In the Dashboard Properties pane, replace the default name NewDashboard1 with CustomerInfo, and then replace the default title New Dashboard with Customer Information Dashboard.
If you wish, choose a different predefined stylesheet.
Click Save. The CustomerInfo dashboard is added to the list of dashboards for Sqlwbcat.

To test your progress so far, open your browser and paste this Address: https://<your server name>/Dashboard/Dashboard.asp?DashboardID=https://<your server name>/Sqlwbcat/CustomerInfo. You should see an empty dashboard, correctly titled and styled, with the Content, Layout, and Settings items in the top right corner.

Save this URL in your Favorites list so that you can view the changes as you add each part.

Defining the Customer List Web Part

The Customer List Web Part contains a list of customers, identified by Company Name. The content for this Web Part is an HTM file.

In your browser, open the Administration page of the SQL Server Sample Digital Dashboard. In the Dashboard View pane, select the CustomerInfo dashboard.
Scroll down to the Web Part List pane, and then click New to define a new part.

In the General tab of Web Part Properties, do the following four things:
1. Replace the default name NewPart1 with CustomerList.
2. Replace the default title NewPart1 with Customer List.
3. Select Left Column for the position on the page.
4. Set Fixed Size to a fixed height of 500 pixels. This shows more rows in the Customer List.
Click the Advanced tab.
Choose HTML for the Content Type.
In Content Link, type the following: https://<your server name>/tutorial/customerlist.htm
Click Save.

Note that if you subsequently change any properties (for example, to adjust the part position or change the title), the values you entered for fixed height will migrate to the fixed width fields. This bug will be fixed in a subsequent release. The workaround for now is to redo the fixed height, and then click no to disable the fixed width.

To test your progress so far, open or refresh the Customer Information dashboard in your browser. The Customer List Web Part should appear in the dashboard.

Defining the Order Chart Web Part

The Order Chart Web Part is an HTML file that contains summarized order data for the customer selected in the Customer List Web Part.

In your browser, open the Administration page of the SQL Server Sample Digital Dashboard, then select the CustomerInfo dashboard.
Scroll down to the Web Part List pane, and then click New to define a new part.

In the General tab of Web Part Properties, do the following four things:
1. Replace the default name NewPart1 with OrderChart.
2. Replace the default title NewPart1 with Order Chart.
3. Select Right Column for the position on the page.
4. Set Fixed Size to a fixed height of 350 pixels to give the part more room.
Click the Advanced tab.
Choose HTML for the Content Type.
In Content Link, type the following: https://<your server name>/tutorial/orderchart.htm
Click Save.

Testing the Dashboard

After you add the two parts, the dashboard is ready to use. Open the Customer Information dashboard in your browser. Click a Company Name in the Customer List Web Part. The Order Chart Web Part responds by querying Northwind for order information about the customer, and then aggregating that information into a set of values that can be represented by a bar chart. The name of the customer you select appears above the chart. The following sections detail the events and actions occurring behind the scenes that create the appearance and behavior you see in this dashboard.

Reviewing the Code Samples

This section highlights the more interesting aspects of the code samples. Each file is discussed separately. The following table describes the role of each file.

File	Description
Customerlist.htm	Creates a structure for the part.
Customerlist.xml	Gets customer data.
Customerlist.xsl	Transforms data by selecting it and applying HTML.
Customerlist.htc	Adds dynamic HTML behaviors, including definitions for the onclick event used to raise an event notification. This notification is received by the Order Chart Web Part.
Orderchart.htm	Creates a basic structure for the part, gets data by building a query that includes a Company Name passed through the onclick event defined in Customerlist.htc.
Orderchart.xsl	Transforms the data by selecting it and applying HTML. The bars in the bar chart are dynamically sized based on the amount of annual orders. Two functions different functions are used to calculate these values.

Customerlist.htm

This HTML file provides the content for the Customer List Web Part. It contains a reference to the Customerlist.xml file, which in turn contains a reference to Customerlist.xsl, which references the Customerlist.htc file.

The Customerlist.htm file defines an isolated frame to contain Customer data from Northwind. Although you can isolate Web Parts in the Web Part definition, using this approach (that is, manually creating IFRAME elements) offers more security and allows you to invoke the DDSC at the part level.

Invoking the DDSC at the part level means that you can control other Web Parts (in this case, the Order Chart Web Part) from script inside an IFRAME. To do this, you create a variable named DDSC in the IFRAME content and then set its value equal to the DDSC that exists outside of the frame (that is, the DDSC instance for the dashboard). You can then use the DDSC variable to communicate with other parts.

In this example, a DDSC variable is declared in the source for the IFRAME (that is, in the Customerlist.xsl file, which in turn is referenced by the Customerlist.xml file, which provides the content to the IFRAME element).

This approach works because a parent can access an IFRAME (note that the reverse case of IFRAMEs accessing parents is not true). In this case, the DDSC instance at the dashboard level can access the IFRAME content you define and participate in the script that you associate with a given IFRAME element.

In the code snippet below, the IFRAME 1048528365ID 10485283661048528365attribute 1048528366is defined so that you can reference the frame in script.

Next, the IFRAME SRC attribute specifies the XML template file containing the Northwind query. This file is used to populate the frame with a scrollable list of Company Names. The names are retrieved from Northwind when the dashboard loads. Note that UTF-16 encoding is needed to accurately display foreign language characters in the data.

Finally, the IFRAME HEIGHT and WIDTH attributes expand the frame so that it occupies all of the available space of the Web Part.

<IFRAME ID="CustFrame" 
SRC="https://<server>/nwind/template/customerlist.xml?contenttype=text/html&outputencodin
g=UTF-16" HEIGHT="100%" WIDTH="100%">
</IFRAME>

Further on in this file, you find a script block that instantiates a DDSC instance at the frame level, using the value of the IFRAME ID. The DDSC is one of the objects used to implement the Part Notification service. It exposes methods that both raise and respond to event notifications.

CustFrame.ddsc= DDSC;

Customerlist.xml

This XML template file issues an SQL SELECT statement through IIS using the nwind virtual directory you configured earlier. Specifying the nwind virtual directory is equivalent to specifying the Northwind database (recall that this specification is part the value for the IFRAME SRC attribute in Customerlist.htm).

The root element defines a namespace and the XSL file used to transform the result set. The query statement is a child of the root element.

<root xmlns:sql='run:schemas-microsoft-com:xml-sql' sql:xsl='customerlist.xsl'>
<sql:query>
SELECT CompanyName FROM Customers FOR XML AUTO
</sql:query>
</root>

Customerlist.xsl

This XSL file transforms the XML result set so that it appears in the page. It defines a template pattern that finds all Customer nodes and gets the value of the Company Name. The Company Name is inserted into a TD element in the order returned by the query.

In the code snippet below, the STYLE element defines CSS styles for TH and TD elements.

The STYLE TH element is styled with a gray background color.

The STYLE TD element calls an HTC file that combines style attributes with script to produce dynamic HTML for the content in each TD element.

<STYLE>
TH {background-color:#CCCCCC}
TD {behavior:url(https://<server>tutorial/customerlist.htc)}
</STYLE>

This file also declares a variable for DDSC. This variable is used in the Customerlist.htm file to invoke the DDSC object for an IFRAME element. Note that this declaration was discussed previously, in the Customerlist.htm section.

<script language="JScript">
var DDSC;
</script>

Customerlist.htc

The Customer List Web Part is programmed for three events: onmouseover, onmouseout, onclick.

Onmouseover and onmouseout define rollover behavior.

Through the Click function, the onclick event instantiates the DDSC object at the part level. Clicking a company name raises an event (that is, broadcasts an event notification to other parts in the same dashboard). The RaiseEvent method is a method of the DDSC object.

function Click() {
ddsc.RaiseEvent("URN:Customer", "SelectCustomer", this.innerHTML);
}

The URN:Customer parameter is a user-defined namespace that you can create to provide a context for the event. For example, in any given application you may have multiple Click functions. Using a namespace provides a way to distinguish between click events that occur in an Employee form, a Customer list, or an Order bar chart.

The SelectCustomer parameter is an event name. This is a user-defined name that identifies the event to other Web Parts that respond to this event. Script attached to the responding Web Part (that is, the Order Chart) refers to the same event name when registering for the event.

The this.innerHTML parameter is an event object. This is the object upon which the function operates. In this case, it is a specific Company Name that the user clicks on. This value is passed as part of the event notification, making it available to other parts that want to use it.

Orderchart.htm

This file provides the content for the Order Chart Web Part. The file contains an SQL SELECT statement issued through IIS using the nwind virtual directory you configured earlier. The query is multipart, using a combination of fixed strings and a Company Name value that is passed in as a parameter. The data that is returned is total order volume for a single customer, grouped by year. Clicking a different customer in the Customer List issues another query against the database, using new values that correspond to the selected customer. The return values are used to update the contents of the Order Chart.

The code that relates the Order Chart to the Customer List Web Part is the following:

DDSC.RegisterForEvent("URN:Customer", "SelectCustomer", this.innerHTML);

The SelectCustomer parameter is the event name, and this.innerHTML is the event object.

As with the Customer List, an isolated frame is used to contain the data. The IFRAME element is defined as follows:

<IFRAME ID="ChartFrame" WIDTH="100%" FRAMEBORDER="0" NORESIZE SCROLLING="auto"></IFRAME>

The onSelectCustomer function provides the code that creates the multipart query. (Note that the first several lines of this function are used to search and replace special characters like ampersands and apostrophes to XML or HTTP equivalents). The query is specified through the SRC parameter of the IFRAME element by way of the document object model.

document.all.ChartFrame.src = "https://<server>/nwind?xsl=template/orderchart.xsl&contenttype=text/html&outputencoding=
UTF-
16&sql=Select+datepart(year,%20Orders.OrderDate)+as+Year,Sum([order%20details].UnitPrice
*[order%20details].Quantity)+as+OrderTotal+from+[order 
details]+inner+join+Orders+on+[order%20details].OrderID=Orders.OrderID+inner+join+Custom
ers+on+Orders.CustomerID=Customers.CustomerID+where+customers.companyname='" 
+customerName +"'+group+by+datepart(year,%20Orders.OrderDate)+FOR+XML+RAW&root=root";

In this query, an XSL file and encoding attribute are specified before the SELECT statement.

The SELECT statement itself is articulated in HTTP syntax. Because the query contains a dynamic element (CustomerName, which is the value passed in as "this.innerHTML" and it varies each time the user clicks a Company Name), a static XML template file could not be used. Passing the SQL query as a string provides a way to combine static and dynamic elements together.

Orderchart.xsl

This file transforms the XML result set returned for the Order Chart, creating the bar chart and displaying customer information based on an SQL query. This file is referenced in the HTTP statement for the SRC parameter.

The bar chart is simple HTML (in this case, TD elements in a table) and it shows differences among annual order volumes for a specific customer. To get differences in bar color and size, different attributes on the TD element are set. These attributes are BACKGROUND-COLOR and WIDTH. WIDTH is an XSL attribute (name=style) that is attached to the TD element. The value of WIDTH is calculated through script.

Color coding is based on the year (year values are detected through XSL). Because there are only three years worth of data in the Northwind database, we get by with XSL test cases that detect 1996, 1997, and 1998.

<xsl:attribute name="style">width:<xsl:eval>getOrderPercent(this)</xsl:eval>;
<xsl:choose>
<xsl:when test=".[@Year='1996']">background-color:red</xsl:when>
<xsl:when test=".[@Year='1997']">background-color:blue</xsl:when>
<xsl:otherwise>background-color:purple;</xsl:otherwise>
</xsl:choose>

Sizing is based on order volume. In Northwind data, order volumes vary from two-digit to five-digit values. The wide range makes it difficult to scale the bars using fixed values (a bar chart based on pixels would need to accommodate bars that are 42 pixels long and 64,234 pixels long). To work around this, we use percentages. Percentage values show relative rather than absolute differences in the order volumes. For a specific customer, each annual volume (for 1996, 1997, or 1998) is some percentage of the combined three-year volume. To get the three different WIDTH values needed for the three bars in the bar chart, we use two functions.

The getOrderPercent function calculates the value of the TD WIDTH attribute by dividing an Order Total by the sum of all Order Totals. This function is called from an xsl:eval element (as shown in the first line of the previous code snippet).

The getOrderTotal function sums the Order Totals into one lump sum. This sum becomes the denominator in the getOrderPercent function.

Both functions are reproduced here in their entirety:

var nTotal = 0;
function getOrderPercent(nNode) {
var nPercent;
if (nTotal == 0)
nTotal=getOrderTotal(nNode.ParentNode);
nPercent=Math.round((nNode.getAttribute("OrderTotal") / nTotal) * 100) + '%';
return nPercent;
}

function getOrderTotal(nNode) {
var sum=0;
var rows=nNode.selectNodes("row");
for (var i = rows.nextNode(); i; i = rows.nextNode())
sum += parseInt(i.getAttribute("OrderTotal"));
return sum;
}