Creating a Hierarchical Data Report WITHOUT using a DataEnvironment

I am having a problem creating a Hierarchical Data Report without using a DataEnvironment.

I am able to create a simple report in the example below:
For this example you will need a form with a command button(Command1) and a DataReport with 2 rptLabels and 2 rptTextbox's (located in Detail Section1).

Also a copy of the Nwind.mdb

All code can go into the Command1_click() event

Code:

---


Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim j As Integer
Dim i As Integer
Dim k As Integer
Dim intCtrl As Integer

'set the connection to Nwind.mdb database
con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"

'Set the .CommandText to desired SQL statement
With cmd
    .ActiveConnection = con
    .CommandType = adCmdText 'SQL
    .CommandText = "Select FirstName,Lastname from Employees ORDER BY Lastname"
    .Execute
End With

'Open the Recordset
With rs
    .ActiveConnection = con
    .CursorLocation = adUseClient
    .Open cmd
End With

'reset counters
i = 0
j = 0
k = 0

'Populate the Data Report
With DataReport1
    .Hide
    Set .DataSource = rs
    .DataMember = ""
    With .Sections("Section1").Controls 'Detail Section of Report
        For intCtrl = 1 To .Count 'loop through all controls in detail section
            If TypeOf .Item(intCtrl) Is RptLabel Then
                .Item(intCtrl).Caption = rs.Fields(j).Name & " :"
                j = j + 1
            End If
            If TypeOf .Item(intCtrl) Is RptTextBox Then
                .Item(intCtrl).DataMember = ""
                .Item(intCtrl).DataField = rs(k).Name
                k = k + 1
            End If
        Next intCtrl
    End With
     
    .Refresh
    .Show
End With

End Sub

---

This creates a nice simple report with all employees First names and Last names sorted by Lastname.

My Question is Say I wanted to insert a group Header/Footer into the dataReport?

For Example I wanted a Group Header showing each of these employees grouped by what their [Title] is?
1) How would I add this group Header to the DataReport?
2) Once the Headers were added what controls should be added to the Group Header/Footer?
3) What code would have to be added to populate the newly added controls on the DataReport?
4) Any other additional coding...ie. using a SHAPE command to form the recordset to get the necessary results?

Any assistance that anyone can give me would be most appeciated. Thank you.

I have looked all over for information to solve this and still have not found any.

Anyone out there have any ideas on how to do this?

I send you a little example to create a hierarchical command with SHAPE in VB6 and print the result recordset into a Datareport:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim dr As DataReport1

cnn.Provider = "MSDataShape"
cnn.Open "Data Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=MYMACHINE"
rst.Open "SHAPE {SELECT * FROM authors} AS Command1 APPEND ({SELECT * FROM titleauthor} AS Command2 RELATE 'au_id' TO 'au_id') AS Command2", cnn
Set dr = New DataReport1
Set dr.DataSource = rst
dr.Show vbModal


Into DataReport you may create a Group Header in which to print the author informations and a Detail Section in which to print the titles related to each author .
For each textbox that prints the field of authors you may specified only the "DataField" equal to a author field ,for example DataField = au_lname, instead for each textbox that print the field of titleauthors you may specified the "DataField" equal to a titleauthors field , for example DataFiled=title_id and the DataMember=Command2.


In this way the Datareport print hierarchical information without using DataEnvironment.

P.S. You may use dataenvironment to create hierarchical command, aggragation and grouping after you may select the right button on the top command and copy the SHAPE command that Dataenvironment has created correctly for you.

Good work.

Paola Besana

I have looked into this to get a report working in my project. I used the following from the help files to help me out.

It may be worth having a look

<----------------------------------------------------------------------------->

Sub datashape()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rsChapter As Variant

cnn.Provider = "MSDataShape"
cnn.Open "Data Provider=MSDASQL;" & _
"DSN=vfox;uid=sa;pwd=vfox;database=pubs”
'STEP 1
rst.StayInSync = FALSE
rst.Open "SHAPE {select * from authors}
APPEND ({select * from titleauthor} AS chapter
RELATE au_id TO au_id)",
cnn
'STEP 2
While Not rst.EOF
Debug.Print rst("au_fname"), rst("au_lname"),
rst("state"), rst("au_id")
'STEP 3
Set rsChapter = rst("chapter")
'STEP 4
While Not rsChapter.EOF
Debug.Print rsChapter(0), rsChapter(1),
rsChapter(2), rsChapter(3)
rsChapter.MoveNext
Wend
rst.MoveNext
Wend
End Sub

<----------------------------------------------------------------------------->