Solved

Grouping Datareport more than 1 level and using functions

Posted on 2001-08-28
3
259 Views
Last Modified: 2012-06-21
Can anybody give me an example on how to group up to 2 levels?
Say you have Customers table, then Orders, then Products table. I want to group by CustomerID, OrderIR, an then see the products the customer bougth.

I'm using a DataEnviroment with a child command for the first group, but I don't know what to do next. I'm using the VB 6 Datareport Designer.

Also How can I insert functions not coming from the recordset in the report, say "iif(cColor='W','White','Other Color').


Thanks in advance.


Miguel
0
Comment
Question by:msuarez
3 Comments
 
LVL 12

Accepted Solution

by:
roverm earned 200 total points
Comment Utility
It easy ;-). Just use sub-commands in a dataenvironment.

From MSDN:

To create a simple hierarchical cursor in the Data Environment designer

Create a new Standard EXE project.


On the Project menu, click Add Data Environment to add a designer to your project. If the designer is not listed on the Project menu, click Components. Click the Designers tab, and click Data Environment to add the designer to the menu.
Note   The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu on the Project menu.

On the Data Link Properties dialog box, click Microsoft Jet 3.51 OLE DB Provider. This selects the correct OLE DB provider for accessing a Jet database.


Click the Next button to get to the Connection tab.


Click the ellipsis button (?) next to the first text box.


Use the Select Access Database dialog box to navigate to the nwind.mdb file, which is installed in the Program Files\Microsoft Visual Studio\Vb98 directory.


Click OK to close the dialog box.


Right-click the Connection1 icon, and click Rename. Change the name of the icon to Northwind.


Right-click the Northwind icon, and then click Add Command to display the Command1 dialog box. In the dialog box, set the properties as shown below: Property Setting
Command Name  Customers
Connection  Northwind
DataBase Object  Table
Object Name  Customers


Click OK to close the dialog box.


Right-click the Customers command, and click Add Child Command to display the Command2 dialog box. In the dialog box, set the properties as shown below: Property Setting
Command Name  Orders
Connection  Northwind
DataBase Object  Table
Object Name  Orders


Click the Relation tab. The Relate to a Parent Command Object check box should be checked. The Parent box should contain Customers; both the Parent Fields and Child Fields/Parameters boxes should contain CustomerID.
When designing relational databases, it's customary for related tables to use the same name for linking fields. In this case, the linking fields are both named CustomerID. The Data Environment designer automatically matches such pairs in the dialog box.

Click Add. Click OK to close the dialog box.
Clicking the Add button adds the relation to the Command object. After closing the dialog box, the Data Environment designer reflects the relationship by displaying the two commands as a hierarchy. This hierarchy will be used to create the data report.

Set the properties of the project and designer according to the settings below, then save the project: Object Property Setting
Project Name prjNwind
DataEnvironment Name deNwind
Form Name frmShowReport


Creating the Data Report
Once the Data Environment designer has been created, you can create a data report. Because not all of the fields in the data environment will be useful in a report, this series of topics creates a limited report that displays only a few fields.

To create a new data report

On the Project menu, click Add Data Report, and Visual Basic will add it to your project. If the designer is not on the Project menu, click Components. Click the Designers tab, and click Data Report to add the designer to the menu.
Note   The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu on the Project menu.

Set the properties of the DataReport object according to the table below: Property Setting
Name rptNwind
Caption Northwind Data Report

On the Properties window, click DataSource and then click deNwind. Then click DataMember and click Customers.
Important   To set the DataSource property to deNwind, the Data Environment designer must be open. If it is closed, press CTRL+R to display the Project window, then double-click the data environment icon.

Right-click the Data Report designer, and click Retrieve Structure.
You have added a new group section to the designer. Each group section has a one-to-one correspondence to a Command object in the data environment; in this case, the new Group section corresponds to the Customers Command object. Notice also that the Group Header has a matching Group Footer section.

Note   The Data Environment allows you to create hierarchies of Command objects wherein a Command object has more than one child object ? child Command objects parallel to each other. The Data Report designer, however, is not as flexible, and can't display more than one child object at a time. In such cases, when executing a Retrieve Structure command, the Data Report will display only the first of the child commands, and none below it. Thus you should avoid creating Command hierarchies with parallel children commands.

From the Data Environment designer, drag the CompanyName field (under the Customers command) onto the Group Header (Customers_Header) section.
The Group Header section can contain any field from the Customers command, however, for demonstration purposes, only the Customer name is displayed at this time.

Delete the Label control (rptLabel) named Label1.
If you do not want a Label control to be included with the TextBox control, you can uncheck the Drag and Drop Fields Caption option on the Field Mapping tab of the Data Environment designer's Options dialog box.

From the Data Environment designer, drag the OrderDate field (under the Orders command) onto the Details (Orders_Detail) section. Delete the Label control.
The Details section represents the innermost "repeating" section, and thus corresponds to the lowest Command object in the Data Environment hierarchy: the Orders Command object.

Resize the Data Report designer's sections to resemble the figure below:


It's important to resize the height of the Details section to be as short as possible because the height will be multiplied for every OrderDate returned for the CompanyName. Any extra space below or above the OrderDate text box will result in unneeded space in the final report.

Save the project.

===================

There is also a very good example on the MSDN Library CD-Roms/DVD: Q:\Samples\VB98\DataRept where Q = your CD-drive.

D'Mzzl!
RoverM
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi msuarez,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept roverm's comment(s) as an answer.

msuarez, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now