Datareport and groupings

[jopaumier]

Through a long process of SQL statements invlovling several tables over a couple databases, we have created an Access table that already groups the data and computes statistics the way we want it. In Access, we can create multiple groups to create and run the report. Now working with the VB6 dataenvironment, I see that I can only create one group to 'drag and drop' fields. If I had known of and understood command hierachies, we might have saved on the SQL to get to this one table. But that's for another day since a 'draft' is due next week.

My question is, can we use our already-developed single table to dynamically populate a data report (without the data environment) in which I have added two groupings? Or will VB balk again and tell me that "report sections do not match data source"?

The reports will have the following layout (hope it makes some sense)

Report header: (nothing in it)
Page header: a title
Group header 1: first grouping, only a name in here (e.g., pollutant)
Group header 2: second grouping, with name and statistics we already computed (e.g., source categories)
Detail: user-defined "Top N" details (e.g., top 10 counties out of all counties in a state: note the statistics are based on all detail rows before we reduce it to our top N choices)

Can I tell VB where to put page breaks?

Thanks,
Jim

 

[jopaumier]

Well, I think I found a good part of the answer I need in Microsoft's Knowledgebase article 289793 (I had #240019 adn found 289793 after I posted my quesitons). The example works with Nwind.mdb and they use SHAPE to create a data group programatically and display the report. The SQL is:

" SHAPE {SELECT FirstName,Lastname,City FROM `Employees`} AS Command1 COMPUTE Command1 BY 'City'"

However, I don't have a firm enough grasp of SHAPE to extend this to two levels of grouping on a single database. For example if I want to extend it to group by country as well (one of the fields in the 'Employees' table), how would I go about modifying the above SQL?

The question on page breaks remains as well.

Jim

 

[petevick]

Can't help with your groupings, but if you click on a report group header to display its properties, one prop is ForcePageBreak, this can be set to Before, After or BeforeAndAfter. Hope this helps you.

 

[Som76]

Hi

I have renamed my Section Page Header to "PageHeader" in the Name property.

now i ca do the following
With DataReport1
.Sections("PageHeader&quot.ForcePageBreak = rptPageBreakAfter
.Sections("PageHeader&quot.ForcePageBreak = rptPageBreakBefore
.Sections("PageHeader&quot.ForcePageBreak = rptPageBreakBeforeAndAfter
.Sections("PageHeader&quot.ForcePageBreak = rptPageBreakNone
End With

U can replace "PageHeader" with ur section/group header name. Click on the Group header & check the name. Default it is Section... .

Regards
Som

 

[jopaumier]

Thank you Petevick and Som. While sitting at home staring at the Microsoft example and trying to extend it, I noticed the ForcePageBreak property. It's amazing what one might get done without the distractions of the office and after a glass of wine!

Jim