DOCUMENT:Q251329  23-AUG-2001  [vbwin]
TITLE   :HOWTO: Modify the Command of a Hierarchical DataReport
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5,2.6,6.0
OPER/SYS:
KEYWORDS:kbADO210 kbDatabase kbVBp600 kbGrpDSVBDB kbDSupport kbADO210sp2 kbADO250 kbADO260 kbATM

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Professional Edition for Windows, version 6.0 
 - Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 
 - ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5, 2.6, 2.7 
-------------------------------------------------------------------------------

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:

   Q244779 HOWTO: 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:

1. Open a new Standard EXE Project in Microsoft Visual Basic. Form1 is created
   by default.

2. Add a DataEnvironment (DataEnvironment1) and a DataReport (DataReport1) to
   the project.

3. Add a Connection (Connection1) to the DataEnvironment that points to the SQL
   Server Northwind database using the OLE DB Provider for SQL Server.

4. Add a Command (Command1) to Connection1. Right-click the command button and
   then select Properties.

5. 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

6. 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

7. 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

8. Click OK to close the Properties dialog box. The hierarchy changes to the
   following:

Command1 grouped using Customer
  Summary fields in Customer
    CustomerID
    Company
  Detail fields in Command1
    CompanyName
    OrderID
    CustomerID
    EmployeeID
    ...

9. In the DataReport, set the following properties:

   DataSource: DataEnvironment1
   DataMember: Customer

10. Right-click the body of the report, and then select the Insert Group
   Header/Footer menu item.

11. 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

12. 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.

13. 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:

   a. Orders where the CustomerID starts with "A".

   b. Orders shipped by shipper #1.

   c. Orders placed in 1996.

REFERENCES
==========

For additional information regarding the SHAPE syntax, click the article numbers
below to view the articles in the Microsoft Knowledge Base:

   Q189657 HOWTO: Use the ADO SHAPE Command

   Q185425 INFO ADO Hierarchical Recordsets via SHAPE APPEND w/C++/VBA/Java

   Q249097 HOWTO: Re-Shape a Hierarchical Recordset

   Q247662 HOWTO: Access a Secured Jet Database w/ the MSDataShape Provider

Additional query words:

======================================================================
Keywords          : kbADO210 kbDatabase kbVBp600 kbGrpDSVBDB kbDSupport kbADO210sp2 kbADO250 kbADO260 kbATM kbmdac270 kbado270 
Technology        : kbVBSearch kbAudDeveloper kbADOsearch kbADO210 kbADO210sp1 kbADO210sp2 kbADO250 kbADO260 kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVB600 kbADO270
Version           : WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5,2.6,6.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.