Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/251329

From BetaArchive Wiki
Knowledge Base


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:

  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:
    1. Orders where the CustomerID starts with "A".
    2. Orders shipped by shipper #1.
    3. 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:

189657 How To Use the ADO SHAPE Command


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


249097 How To Re-Shape a Hierarchical Recordset


247662 How To Access a Secured Jet Database w/ the MSDataShape Provider


Keywords: kbhowto kbdatabase KB251329