Grouping with Data Reports

CodeHunter
06-01-2002, 09:17 PM
I have been trying to generate a report with the Visual Basic Data Reports and every time I try to run a report that is grouped by certain fields and has group headers and footers in the report layout I get the error "Report Sections do not match data source". I would appreciate any suggestions on how to fix the error

Thinker
06-01-2002, 09:32 PM
Unless you describe your data source and how it is supposed to
be shown in the report, I doubt anyone can help. An attached
project would be even better, but you would have to create a
sample database and zip everything up together.

CodeHunter
06-02-2002, 12:50 PM
Apologies for not including more information about the project, below is the code I am using to connect to the database and the SQL statement I am using to pull out the information. The report layout has the following sections: one report header and footer, one page header and footer, one group header and footer, and the detail section. I would appreciate any suggestions.

CodeHunter


Private Sub RunReport()
Dim dbString As String
Dim cnnConnection As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim strQry As String
Dim TotalPercent As Double
Dim GrossPercent As Double

'Open a connection.
dbString = App.Path & "\SODatabase.mdb"
Set cnnConnection = New ADODB.Connection
cnnConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbString & ";" & _
"Persist Security Info=False"
cnnConnection.Open

'Open the Recordset.
strQry = "SELECT rs_cat.rsct_description, pc_description.Description, rs_scat.rsc_amount, rs_scat.rsc_retail, [rs_scat].[rsc_retail]-[rs_scat].[rsc_amount] AS GrossDollars, [GrossDollars]/[rs_scat].[rsc_retail] AS GMPercent, [rs_scat].[rsc_retail]/" & TotalSales & " AS SalesPercent
FROM (pc_description INNER JOIN rs_shiftlink ON pc_description.ProfitCenterNumber = rs_shiftlink.rssl_prft_ctr) INNER JOIN (rs_cat INNER JOIN rs_scat ON rs_cat.rsct_catagory = rs_scat.rsc_catagory) ON rs_shiftlink.rssl_shl = rs_scat.rsc_shl
GROUP BY rs_cat.rsct_description, pc_description.Description, rs_scat.rsc_amount, rs_scat.rsc_retail;"


Set rstRecordset = cnnConnection.Execute(strQry, ,adCmdText)

'Connect the Recordset to the DataReport.
Set CategorySales.DataSource = rstRecordset

'Display Report
CategorySales.Show
End Sub

lebb
06-03-2002, 07:28 AM
To do grouping with a DataReport requires a hierarchical recordset. You can create one manually using SHAPE commands, or you can use the DataEnvironment to do this for you.

Here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjfabricatinghierarchicalrecordsets.asp) is some nice documentation from Microsoft on SHAPE syntax, and I also posted a simple example in this thread (http://www.visualbasicforum.com/showthread.php?s=&threadid=24883#post117299). There have also been a number of discussions on this forum about using the DataEnvironment; a search should turn them up.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum