Microsoft KB Archive/251329

= How To Modify the Command of a Hierarchical DataReport =

Article ID: 251329

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q251329



SUMMARY
This article demonstrates how to base a DataReport on a hierarchical DataEnvironment and refresh the report after changing the command text.



MORE INFORMATION
The following Microsoft Knowledge Base article illustrates how to refresh a DataReport when re-executing a parameterized command:

244779 How To Refresh a Parameterized DataReport

This article demonstrates a similar technique, which modifies the command itself.

NOTE: Any modification to the command cannot change the shape of the hierarchy or the names of the fields.

In the sample program provided in this article, the command is modified to show the following:
 * All orders where the CustomerID starts with the letter "A".


 * All orders shipped through shipper #1.


 * All orders placed in 1996.

To create the sample, use these steps:  Open a new Standard EXE Project in Microsoft Visual Basic. Form1 is created by default. Add a DataEnvironment (DataEnvironment1) and a DataReport (DataReport1) to the project. Add a Connection (Connection1) to the DataEnvironment that points to the SQL Server Northwind database using the OLE DB Provider for SQL Server. Add a Command (Command1) to Connection1. Right-click the command button and then select Properties. In the Properties dialog box, click the General tab and add the following:

SQL Statement: SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

 In the Properties dialog box, click the Grouping tab and set the following:

Group Command Object: (select this item)

Grouping Command Name: Customer

Fields Used for Grouping: CustomerID

 In the Properties dialog box, click the Aggregates tab. Click Add and then edit the following properties:

Name: Company

Function: Any

Aggregate On: Grouping

Field: CompanyName

</li>  Click OK to close the Properties dialog box. The hierarchy changes to the following: <pre class="fixed_text">Command1 grouped using Customer Summary fields in Customer CustomerID Company Detail fields in Command1 CompanyName OrderID CustomerID EmployeeID ...                   </li> In the DataReport, set the following properties:

DataSource: DataEnvironment1

DataMember: Customer

</li> Right-click the body of the report, and then select the Insert Group Header/Footer menu item.</li> Drag the following fields from the DataEnvironment into the Detail section of the DataReport:

OrderID

CustomerID

OrderDate

ShipVia

Drag the following fields from the DataEnvironment into the Group Header section of the DataReport:

CustomerID

Company

</li>  Add three command buttons (Command1, Command2, Command3) and the following code to the form: Option Explicit

Const preSHAPE = "SHAPE {" Const SQL = "SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID" Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'CompanyName') AS Company BY 'CustomerID'"

Private Sub Command1_Click ' ' Selects orders with CustomerID starting with A. ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Customers.CustomerID LIKE 'A%'" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub

Private Sub Command2_Click ' ' Selects orders shipped via Shipper #1 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE ShipVia=1" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub

Private Sub Command3_Click ' ' Selects orders in 1996 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Year(OrderDate)=1996" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub

Private Sub Form_Load Command1.Caption = "CustomerID starts with 'A'" Command2.Caption = "Orders via Shipper #1" Command3.Caption = "1996 Orders" End Sub You may have to modify the SHAPE statement in the code based on your hierarchical command. You can copy the Command1 SHAPE statement by right-clicking it in the DataEnvironment and then select Hierarchy Info. You can paste the SHAPE statement in the code and use it to modify the preSHAPE, postSHAPE, and SQL constants. The DataEnvironment Command and Recordset names used in the preceding code, in this case "Customer" and "rsCustomer", match the name that displays in the top level of the hierarchy.

</li> Save the project and run it. Click each of the command buttons. You see the DataReport show data based on the modified command text, namely: <ol style="list-style-type: lower-alpha;"> Orders where the CustomerID starts with "A".</li> Orders shipped by shipper #1.</li> Orders placed in 1996.</li></ol> </li></ol>

<div class="references_section">