[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 (http://www.windowsitpro.com/SQLServer/Article/ArticleID/5222/5222.html) help?

[Dennis DVR]

Does this (http://www.windowsitpro.com/SQLServer/Article/ArticleID/5222/5222.html) 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 (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q189/6/57.ASP&NoWebContent=1) 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 (http://www.windowsitpro.com/SQLServer/Article/ArticleID/5222/5222.html) help?
It's need a subscription.
Can you copy it here ?