Member Avatar for fizznur

I am creating a DataReport using the Data Report Designer (VB6) and using the following SHAPE command to create a hierarchical recordset.

Dim cmd As New ADODB.Command
Dim rz As New ADODB.Recordset

With cmd
      .ActiveConnection = CN
      .CommandType = adCmdText
      .CommandText = " SHAPE  {SELECT * from Relation  where Institution = '" & DataCombo1.Text & "'}" & _
         " APPEND ((SHAPE  {SELECT 'Department: ' + A.Department  as Dpmt, A.Department, B.TestLocation from Department A, Relation B " & _
          " where A.Institution = B.Institution and A.Department = B.Department }" & _
          " APPEND ({select * from querypack  where deliveryPK =" & txtPK.Text & "} AS cmdGroup " & _
             " RELATE Department TO Department)) AS cmdGroup2 " & _
                        " RELATE TestLocation TO TestLocation)"
                      .Execute
            End With

In my database, I have 2 institution. Institution A have 7 department and Institution B have 2 department. The result I want to be displayed in datareport is like this

Institution A
    Department 1
            Record Detail  1.1
            Record Detail 1.2
    Department 2
            Record Detail 2.1
            Record Detail 2.2
     :::
     till 7 department

Institution B
   Department 1
          Record Detail  1.1
          Record Detail 1.2
    Department 2
          Record Detail 2.1
          Record Detail 2.2

Currently, the report prints like this:

Institution A
    Department 1
            Record Detail  1.1
            Record Detail 1.2

Institution A
    Department 2
            Record Detail  2.1
            Record Detail 2.2


Institution A
    Department 3
            Record Detail  1.1
            Record Detail 1.2

Please somebody help me to group my datareport correctly. I suspect the error is in my Shape command but I don't know how to fixed it.

Edited by TrustyTony because: fixed formatting
  • 2 Contributors
  • 2 Replies
  • 143 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by Capritarius21
Member Avatar for Capritarius21

Hi FIZZNUR

To make it less complicated,
why don't you create just
one table for INSTITUTION A and B.

Member Avatar for Capritarius21

Then on your SHAPE COMMAND

"SHAPE{SELECT DEPARTMENT FROM TABLE WHERE INSTITUTION='" & DATACOMBO1.TEXT & "'"
GROUP BY DEPARTMENT APPEND (SELECT * FROM TABLE WHERE INSTITUTION='" & DATACOMBO1.TEXT & "'} RELATE DEPARTMENT TO DEPARTMENT) AS COMMAND 2"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.