[RESOLVED] Dynamic Data Reports

[JatinProg]

Can Anyone HELP me in Data Reports. My Prob. is to display Grouped Report at runtime... without using DataEnviornment....
Thanks in Advance

[Hack]

Does this help?

[Dennis DVR]

Does this help?

I believe he wants to avoid the use of DataEnvironment and he wants it to be unbound or runtime

You could use an ADO recordset object, but since DataReport need to have a group section you are going to use the ADO SHAPE command for this matter not just a normal SELECT statement.

You will need to use the MSDataShape provider and another provider which will be your Data Provider.

The tree level of your hierarchical recordset will be based on the number of group section in your designer, but bear in mind that datareport does not support multi hierarchical recordset, meaning there should always be 1 child recordset per tree level.

Here's is what I meant

Multi Hierarchical -> not supported
Parent
  |
   ----Child
  |
   ----Child -> Same level. This is not allowed as long as the datareport designer is concern.

Hierarchical -> supported

Parent
  |
   ----Child
        |
         ----Grand Child -> inner level

If you are not familiar with ADO SHAPE COMMAND perhaps this ADO SHAPE TUTORIAL could help.

ok I found an old code in my HD perhaps it can give you some shed and will get you started

Private Sub DataReport_Initialize()
    Dim cnn As String
    Dim rs As ADODB.Recordset
    Dim rsChild As ADODB.Recordset
    Dim strSQL As String
    Dim xx As Integer
    Dim yy As Integer 'Additional counter for fields
    cnn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\..\Demo.mdb"
    Set rs = New ADODB.Recordset
    Set rsChild = New ADODB.Recordset 'Child recordset
    strSQL = "SHAPE {SELECT Members.MemberID, MemberName, DateMember, Rank FROM Members ORDER BY Members.MemberID} AS Parent "
    strSQL = strSQL & "APPEND ({SELECT * FROM MemberPost} AS Child RELATE MemberID TO MemberID) AS Child"
    'This time we have Parent and Child recordset
    'Members.* - Parent
    'MemberPost - Child
    '   |
    '   -rsChild.*
    rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
    If Not rs.EOF And Not rs.BOF Then 'Check for empty recordset
       Set DRTutorial.DataSource = rs 'set the data source of our report to rs recordset
       'Populate our textboxes control in the group section with parent recordset
       With DRTutorial.Sections("Parent") 'bind the textboxes in the Group section
            'we need additional counter coz we have placed the detail heading under the group header
            'and this cost us additional control so the count of our control inside the Group header is bigger than the count of our field
            For xx = 1 To .Controls.Count 'loop through controls under the Group section
               If TypeOf .Controls(xx) Is RptTextBox Then 'is it textbox control
                  .Controls(xx).DataField = rs.Fields(yy).Name 'yes, so we need to bind it
                  yy = yy + 1
               End If
            Next
       End With
       'This is where our child recordset goes
       With DRTutorial.Sections("Child")
          Set rsChild = rs.Fields("Child").Value 'our Child recordset is in the last field of our rs recordset
          'Access all the controls in this section and bind the textboxes to a child recordset
          For xx = 1 To .Controls.Count
              If TypeOf .Controls(xx) Is RptTextBox Then 'is it textbox control
                 'Start at field 1 to ignore the MemberID field we don't want it to appear in the detail section
                 .Controls(xx).DataField = rsChild.Fields(xx).Name 'bind it
                 'set the datamember to our child recordset
                 .Controls(xx).DataMember = rs.Fields("Child").Name 'we need to set the datamember to point to our child recordset
              End If
          Next
          'Bind it regardless of the value of chkWithTotal
          DRTutorial.Sections("ParentFooter").Controls("Function1").DataMember = rs.Fields("Child").Name
          DRTutorial.Sections("ParentFooter").Controls("Function1").DataField = rsChild.Fields(0).Name
          DRTutorial.Sections("ReportFooter").Controls("Function2").DataField = rsChild.Fields(0).Name
          rsChild.Close 'we are safe to close the recordset
          Set rsChild = Nothing 'release the object from memory
       End With
       If frmStep4.chkWithTotal.Value <> vbChecked Then 'Hide the total post and post count when chkWithTotal is not checked
          DRTutorial.Sections("ParentFooter").Controls("lblPostCount").Visible = False
          DRTutorial.Sections("ReportFooter").Controls("lblTotalPost").Visible = False
          DRTutorial.Sections("ParentFooter").Controls("Function1").Visible = False
          DRTutorial.Sections("ReportFooter").Controls("Function2").Visible = False
       End If
       DRTutorial.Refresh 'refresh the datareport to update changes
       DRTutorial.WindowState = vbMaximized
    End If
    rs.Close
    Set rs = Nothing
End Sub

PS: It is an old code, so it might not be optimized

[alMubarmij]

Does this help?

It's need a subscription.
Can you copy it here ?