Searching for some way to provide flexible data reporting to your customers without tearing your hair out? Don't get bent out of shape, use data shaping—the hierarchical recordset feature of ADO 2.0.

Download the code (41KB)

Looking for a better way to represent data sets than the standard join? Wouldn't it be nice to be able to update all tables that are joined in a recordset? Great strides have been made with ADO 2.0 hierarchical recordsets. Hierarchical recordsets let you represent complex relationships in a hierarchical fashion (like a treeview). This technology offers a lot of advantages, as you'll see. This topic is quite extensive, so let's jump straight to the goods and concentrate on how it works and how it can be used in a real-world situation. Bent Out of Shape?       It's Monday morning, and you've already been given more work than you can handle this week. Then your manager calls you into his office to tell you that your priorities have been rearranged. The sales manager says she needs to see all of the customers' sales in a report on the intranet as soon as possible. She needs to be able to see any or all customers and their total outstanding orders. Oh yeah, and she sometimes wants to see one or more customer's order totals. And by the way, can you make it so the items can be displayed on demand, too?       So you try to gather your thoughts as you walk back to your desk. You need to create a report that displays all customers and their total outstanding order amounts, allows the user to see individual order information for one or more orders, and also allows order details to be reviewed. Phew! You're probably thinking that translates into either a really long Web page of a report or several smaller reports on the intranet. But there is another solution; you can put it all on a single page without compromising any functionality.       How? Use the new ADO 2.0 hierarchical recordset feature (also known as "data shaping"). Like many phrases in this age of ever-evolving technologies, you've probably heard the term data shaping being tossed around recently, but haven't had the time to delve into the details. That's why we decided to write this article—to explain how you can create dynamic and powerful Web pages with data shaping.       You're familiar, of course, with visual controls such as the Windows® Explorer that let you view directories and their files in a hierarchical style. Now remember that picture when you think about hierarchical recordsets in ADO. Data shaping in ADO is like the file structure displayed in Windows Explorer in that it stores data in a parent-child relationship. This feature allows you to define a child recordset as the value of a field in a parent recordset.       OK, the mental picture of hierarchical recordsets isn't hard to imagine, but what about the code? When we first delved into this topic, we couldn't fathom how the SQL that generated the hierarchical relationships would look. Well, luckily for us, the architects of ADO 2.0 came up with the new Shape data manipulation syntax. The syntax can be somewhat complex and rather lengthy, so we'll leave it for you to check out in its entirety in the MSDN™ ADO 2.0 documentation. In fact, we first looked to MSDN for examples on data shaping and were quite impressed with the documentation on the Shape syntax. However, like most developers, we like to see real examples of code. Sometimes the syntax doesn't show you how to handle complex, real-world situations, and the documentation available to us was no exception. We won't just tell you what data shaping is and point you to the help files; we'll give you a real-world use for data shaping and show you the code that puts it all together. Peekaboo       So how does data shaping fit the sales manager's reporting needs? The crux of this requested report is giving users the ability to see customers and their orders in detail or by groupings in a single report. Using some data shaping and a sprinkle of DHTML, we can generate a flexible reporting solution. The best way to begin this explanation is to show you the completed report. So before we get into the details and code, take a look at the solution and what it can do.

Figure 1: Displaying Customers and Order Totals

You can see in Figure 1 that only the customers and their cumulative order totals are displayed. As you'll see later, the total amount owed is calculated by summing all of the customer's order totals. This is an inherent feature of data shaping that allows you to perform aggregate functions on child recordsets. What's a child recordset? Well, we'll delve deeper into that later, but for now just think of it as the many sides of a one-to-many relationship in a database or a join. The customer recordset is the parent recordset and the orders recordset is the child (since a given customer can have many orders).       So far, we have satisfied the sales manager's need to be able to see just the customer group totals. But when you look closer, you notice the + characters to the left of each customer. By clicking on one of these images, the + sign becomes a - and the node expands to display that customer's orders, as shown in Figure 2.

Figure 2: Displaying Order Headers

In Figure 2, you see all of the customer's order headers on the same screen. When we clicked on the + sign, the orders for the customer appeared directly below the corresponding customer row. This way, the orders do not appear until invoked explicitly by clicking on the + sign. Likewise, we can make them disappear by clicking on the - sign. We accomplish this expansion and compression by using a basic DHTML effect through the <DIV> tag.       The order headers for each customer show the number of line items on the order as well as the total price of the order. Again, this is accomplished by using the aggregate features of data shaping. We simply counted the line items using the COUNT aggregate function and totaled the line item prices using the SUM aggregate function.

Figure 3: Customer Order Line Items

Figure 3 shows the line items of one of the customer's orders after we expanded the line items by clicking on the + sign next to the last order. All of this data was hidden on the page using DHTML <DIV> tags, so you can simply show the data or hide it by clicking on the + or - sign images. You can verify that the total price of the order is in fact the sum of the extended price of each line item on that order.       So what makes this report so flexible? Well, you can easily change this same report to show all customers and their orders by expanding each customer row (see Figure 4). You could even show all line items on all orders if you wanted to see all customers and all of their orders and line items. But we think you get the point. You can see that this report is quite flexible and chock full of information that will give your sales manager hours of fun.

Figure 4: Showing All Customer Orders

Chances are you can relate this situation to your everyday work. One of the greatest features of data shaping is that it allows you to display data in a more intuitive fashion. For example, when you think of customers and their orders, you think of them in a hierarchical structure with each customer having zero or more order records and then each order having zero or more line items. Figure 5 shows an example of how this would look.

Figure 5: Order Information Hierarchy

The topmost recordset, rsCustomers, contains three data fields: the customer number, the customer name, and a recordset of each customer's orders (rsOrders). The rsOrders recordset is actually a field within the rsCustomers recordset. You can identify this field either by its name, rsOrders, or by its data type, which is adChapter (or 136):

'— Here, we check if the column is a child recordset. If rsCustomers.Fields(lngColumn).Type = adChapter Then …

The other thing that stands out in the rsCustomers recordset is the Total Amount Owed field. This field is derived from an aggregate function that sums the total of a customer's orders. So we can have three types of fields within a shaped recordset: standard fields, child recordsets, and columns derived from aggregates.       In turn, the rsOrders recordset contains the corresponding customer's order header information and the rsLineItems recordset, which contains each order's line items. There are also two aggregate fields in the rsOrders recordset that represent the total number of line items and the total price of the line items on each order. There is a grandparent recordset (rsCustomers), a parent recordset (rsOrders), and a grandchild recordset (rsLineItems).       Before data shaping, you would have had to use a left join from the customers to their orders and again to their line items, returning a single recordset. This would duplicate much of the customer and order data. Using the same customer name and number would produce a result containing all of that customer's order headers and line items. As you might have guessed, this is an area where data shaping can yield better performance on larger sets of data that would otherwise need to be joined. There are some additional features of data shaping that we'll explore later, but now that you've seen what you can do, let's take a look at how it works. What's the Recipe?       Data shaping by itself can't produce this report with its dynamic hiding and showing of the children recordsets. As you might have guessed, DHTML plays a big part in this report. We use a common feature of data shaping that simply makes sections of HTML appear and disappear from the page. This feature is implemented using <DIV> tags and DHTML style properties. Using the style property of the <DIV> tags, we were able to manipulate the visibility and the mouse icon on the page. These are relatively simple aspects of DHTML, but can be quite powerful when combined with data shaping.       By this point, you probably want to know what the code looks like, so here it comes. The first thing we do is set up the ASP by including the ADO constants we'll need and by declaring all variables we will use in the ASP (see Figure 6). Since we used Option Explicit, we have to declare each of the variables we will use on this page. We did not have to use Option Explicit, of course, but it makes debugging much easier—you can quickly track down problems stemming from misnamed variables, for instance. We could have simply included the entire adovbs.inc file so we wouldn't have to list each constant we needed. In this case, since we are only interested in using a handful of its constants, it seems a bit easier to simply list the ones we'll use. We've also commented each variable with a brief explanation of its purpose.       There are more efficient ways to code this page using recursion. We avoided recursive code here for simplicity. Of course, you are free to take this code and alter it to suit your needs.        Figure 7 contains the standard ADO code. We need to create a connection to the Northwind database using the Data Shaping data provider. We do this by setting the Connection object's Provider property to the value MSDataShape. This tells ADO to preprocess all commands with its Hierarchical Cursor engine. So ADO asks the data provider (Microsoft® Access, in this case) to process the SQL before stepping in and using its data-shaping tools to combine the returned data sets from the data provider.       Before you take a look at the SQL in Figure 8 that shapes the customers, orders, and line items into a series of hierarchical recordsets, let's go over the basic syntax of using data shaping in SQL:

SHAPE {parent-sql-command} [AS name] APPEND ( {child-sql-command} [AS name] RELATE parent-field TO child-field )

The parent-sql-command represents the parent recordset, which are the customers in our example. The child-sql-command represents the child recordset, the orders. We use the SHAPE clause to specify which command will be the parent recordset and we likewise use the APPEND clause to specify the child recordset. Finally, we connect the two recordsets, by using the RELATE clause. This clause specifies the parent field and the child field that form the relationship between the two recordsets. We will relate the customers to the orders through the CustomerID field.       ADO performs its magic without joining the two recordsets. Rather, ADO takes each customer's orders and creates a recordset out of them. If there were three customers in the customers recordset, there would effectively be three separate orders recordsets. These separate orders recordsets are then appended as a new field in the customers recordset. The end result is a customer recordset with an additional field that is actually another recordset containing all of the current customer's orders.       The following syntax will generate a hierarchical recordset of customers and their order headers.

SHAPE { SELECT CustomerID AS [Cust #], CompanyName AS Customer FROM Customers } APPEND ( { SELECT OrderID AS [Order #], OrderDate AS [Order Date], PurchaseOrderNumber AS [PO #], Orders.CustomerID AS [Cust #] FROM Orders ORDER BY OrderDate DESC } RELATE [Cust #] TO [Cust #] ) AS rsOrders

Figure 8 expands on this by nesting a third level, the line items, into the mix. But first take a look at how we applied the basic syntax to a single hierarchical relation. We selected the fields we wanted to retrieve regarding each customer. Then we select the fields regarding each order. Finally, we related the two using the RELATE clause and specified the common key field. Notice that we can also rename the fields as we wish. Keep in mind that the embedded SQL syntax within the curly braces must adhere to the underlying OLE DB provider's syntax. In this case we are using Microsoft Access, so we could use square brackets to identify a field alias.       OK, now that we've explained how to create a basic hierarchical relationship, here comes the grand poobah: a nested SHAPE clause with aggregate fields. When you examine Figure 8 , notice that we have indented the SQL for effect and readability. The same relation between the customers and orders is represented in addition to a nested relation to the line items (Order_Details table).       It may strike you that we have also included aggregate fields in this command. Tacked on to the rsOrders recordset is a field that represents the number of line items in each order's corresponding rsLineItems recordset, so we don't have to traverse this recordset to get to this information. In the rsOrders recordset, we have also included a field to represent the total of the extended price of the line items on each order. We could also have used other aggregate functions and expressions, such as those shown in Figure 9.       We can also define aliases for our nested recordsets within the hierarchy, as shown in Figure 8. For example, we alias the orders recordset as rsOrders. How does this help us? Remember that this recordset of orders is a field within its parent recordset of customers. So we can reference this recordset by name, like this:

set objOrderRS = objCustomerRS.Fields("rsOrders").Value

As shown above, don't forget to specify the Value property of the Field object. In a perfect world, we wouldn't have to specify it, since Value is the default property of the Field object. But thanks to the ever-present issue of the lack of data typing in ASP, you need to specify this property explicitly. Otherwise, you will get an invalid object reference.       Well, the hard part is over. The trick with data shaping is getting past the command syntax. And as you can see from Figure 8, this can appear overwhelming at times. But like any type of coding problem, just tackle it one piece at a time and you'll find that it isn't so bad. All of this syntax is a series of SQL statements connected by the SHAPE, APPEND, and RELATE clauses.       From here on out, the code formats the hierarchical recordsets using server-side VBScript. Figure 10 contains two server-side functions that format our currency values. The function FormatValue accepts a Field object and evaluates its datatype. If it is currency or a double, we will return the data formatted as money. Keep in mind that you wouldn't normally convert a double field into money, but for this example it serves our purposes.       The HTML in Figure 11 specifies the single style sheet for this page. Cascading Style Sheets (CSS) allow you to specify certain attributes for all like tags. Here, all <FONT> tags will use the Arial font, if available, or the Verdana® font otherwise.       We've created one style called Point and another called DontPoint. These styles specify that the tags they are assigned to will change the cursor to a hand and a standard arrow, respectively. These styles won't take effect until we specify them to be invoked. Look for this technique later in the onmouseover and onmouseout events of the <IMG> tags we use in our code.       Since we are using DHTML to dynamically change the look of the Web page within the browser without going back to the Web server, we need to include some client-side script (see Figure 12). We'll use VBScript here, but if you're also targeting Netscape browsers you'll want to use JavaScript.       We have a few basic routines called Point and DontPoint that, as you can probably guess, make the mouse icon change for the image that is passed into them. The other client-side routine, DisplayItem, evaluates which <DIV> tag to either display or hide. This routine simply determines which state the <DIV> tag was in and toggles it.       Finally, Figure 13 shows the nested HTML <TABLE> structure that displays the hierarchical recordsets. There is a lot of code in here, but it can all be broken down into relatively simple HTML and ASP. We won't go into too much detail here since we don't want to stray too far off-topic, but it is obvious that any technology on the Web these days requires a working knowledge of several other technologies to produce worthwhile solutions. This code uses ASP, ADO, VBScript, HTML, DHTML, and CSS.       The code in Figure 13 performs two basic tasks: it loops through the hierarchical recordsets and embeds the child recordsets within their own <DIV> tags. We have to loop through the recordset just to get the data displayed. To do this, we loop through the open, topmost recordset and display its column names and values. We actually skip the rsOrders recordset column of the topmost parent customer recordset. We do this by avoiding the fields that have a datatype of adChapter:

<%if objField.Type <> adChapter then%> <td align="right" bgcolor="#ffffc0" nowrap> <font size="3" color="#000080"> <%=FormatValue(objField)%> </font> </td> <%end if%>

In this code snippet, we simply display the value of the field (formatted, of course). If the field is a recordset, the adChapter check skips it until a later section, where it can be formatted like this:

<!————————————> <!— Begin Orders table. —> <!————————————> <tr> <td align="center" colspan="<%=objCustomerRS.Fields.Count + 1%>"> <div id="divCustomer<%=lngCustomerRow%>" style="display:none"> <%set objOrderRS = objCustomerRS.Fields("rsOrders").Value

This code snippet begins a DHTML division with the unique ID of divCustomern, where n represents the current customer this division represents. We ID this <DIV> tag and set its display style to none so it will not show up on the screen until we invoke it. Then we retrieve the child recordset, rsOrders in this case, from the parent customer recordset. The remainder of the code simply repeats this process for the orders and line item data. http://msdn.microsoft.com/workshop/essentials/forstarters/starts0611.asp http://msdn.microsoft.com/workshop/server/asp/maxperf.asp From the January 1999 issue of Microsoft Internet Developer.