vb6

Writing Repor ts with the Microsoft DataRepor t Designer

The Microsoft Data Report designer is a versatile data report generator that features theability to created banded hierarchical reports. Used in conjunction with a data source suchas the Data Environment designer, you can create reports from several different relationaltables. In addition to creating printable reports, you can also export the report to HTMLor text files.

Possible Uses• Automatically create reports that are exported in HTML format for instant

distribution on the Internet.

• Create reports that show the sums of transactions occurring on a daily basis.

Data Report Designer FeaturesThe Data Report designer has several features:

1. Drag-and-Drop Functionality for Fields—Drag fields from the Microsoft DataEnvironment designer to the Data Report designer. When you do this, VisualBasic automatically creates a text box control on the data report and sets theDataMember and DataField properties of the dropped field. You can also drag aCommand object from the Data Environment designer to the Data Reportdesigner. In that case, for each of the fields contained by the Command object, atext box control will be created on the data report; the DataMember and DataFieldproperty for each text box will be set to the appropriate values.

2. Toolbox Controls—The Data Report designer features its own set of controls.When a Data Report designer is added to a project, the controls are automaticallycreated on a new Toolbox tab named DataReport. Most of the controls arefunctionally identical to Visual Basic intrinsic controls, and include a Label,Shape, Image, TextBox, and Line control. The sixth control, the Function control,automatically generates one of four kinds of information: Sum, Average,Minimum, or Maximum. For more information about the Function control, see"Adding a Function Control to the Data Report."

3. Print Preview—Preview the report by using the Show method. The data report isthen generated and displayed in its own window.

Note A printer must be installed on the computer to show the report in printpreview mode.

No license: PDF produced by PStill (c) F. Siegert - http://www.this.net/~frank/pstill.html

4. Print Reports—Print a report programmatically by calling the PrintReportmethod. When the data report is in preview mode, users can also print by clickingthe printer icon on the toolbar.

Note A printer must be installed on the computer to print a report.

5. File Export—Export the data report information using the ExportReport method.Formats for export include HTML and text.

6. Export Templates—You can create a collection of file templates to be used withthe ExportReport method. This is useful for exporting reports in a variety offormats, each tailored to the report type.

7. Asynchonous Operation—The DataReport object's PrintReport andExportReport methods are asynchronous operations. Using theProcessingTimeout event, you can monitor the state of these operations andcancel any that are taking too long.

Topics• Parts of the Data Report

• Creating a Simple Data Report

• Exporting a Data Report

• Printing a Data Report

• Data Report Events

• Understanding Control Placement on the Data Report Designer

Sample ApplicationThe sample application named prjNwind corresponds to the project built in the series oftopics that begin with "Creating a Simple Data Report." If you have installed the VisualBasic sample applications, the file can be found in the Samples directory. If you have notinstalled the sample applications, the file can be found on the MSDN™ CD thataccompanies Visual Basic.

Par ts of the Data Report

The Data Report designer consists of the following objects:


1. DataReport object—Similar to a Visual Basic form, the DataReport object hasboth a visual designer and a code module. Use the designer to create the layout ofa report. You can also add code to the designer's code module toprogrammatically format controls or sections contained by the designer.

2. Section object—Each section of the Data Report designer is represented by aSection object in a Sections collection. At design time, each section is representedby a header that you can click to select the section, and the section's pane whereyou can place and position controls. Use the object and its properties todynamically reconfigure a report before it is built.

3. Data Report Controls—Special controls that only work on the Data Reportdesigner are included with it. (Note: you cannot use Visual Basic's intrinsiccontrols, or any ActiveX controls, on the Data Report designer). These controlsare found in the Visual Basic Toolbox, but they are placed on a separate tabnamed "DataReport."

Sections of the Data Report DesignerThe default Data Report designer contains these Sections:

• Report Header—contains the text that appears at the very beginning of a report,such as the report title, author, or database name. If you want the Report Headerto be the first page in the report, set its ForcePageBreak property torptPageBreakAfter .

• Page Header—contains information that goes at the top of every page, such as thereport's title.

• Group Header/Footer—contains a "repeating" section of the data report. Eachgroup header is matched with a group footer. The header and footer pair areassociated with a single Command object in the Data Environment designer.

• Details—contains the innermost "repeating" part (the records) of the report. Thedetails section is associated with the lowest-level Command object in a DataEnvironment hierarchy.

• Page Footer—contains the information that goes at the bottom of every page, suchas the page number.

• Report Footer—contains the text that appears at the very end of the report, such assummary information, or an address or contact name. The Report Footer appearsbetween the last Page Header and Page Footer.

Data Report Controls


When a new Data Report designer is added to a project, the following controls areautomatically placed in the Toolbox tab named DataReport:

• TextBox Control (RptTextBox)—allows you to format text, or assign aDataFormat.

• Label Control (RptLabel)—allows you to place labels on the report to identifyfields or sections.

• Image Control (RptImage)—enables you to place graphics on your report. Notethat this control cannot be bound to a data field.

• Line Control (RptLine)—lets you draw rules on the report to further distinguishsections.

• Shape Control (RptShape)—enables you to place rectangles, triangles, or circles(and ovals) on a report.

• Function Control (RptFunction)—a special text box that calculates values as thereport is generated.

Creating a Simple Data Repor t

This topic creates a simple data report using a Data Environment designer as a datasource. The Data Environment designer uses the NorthWind database supplied withVisual Basic to create a simple hierarchical cursor. The cursor contains two tables,Customers and Orders, and uses the CustomerID field to link the two. The finished reportresembles the figure below.

Simple Data Report: Order Dates by Customers

Before you begin the step-by-step process, ensure that the Northwind database(Nwind.mdb) is present on your computer. If it is not present, copy the file from yourVisual Basic CD onto your hard disk.

To create a simple hierarchical cursor in the Data Environment designer

1. Create a new Standard EXE project.

2. On the Project menu, click Add Data Environment to add a designer to yourproject. 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 themenu.

Note The first four kinds of ActiveX designers loaded for a project are listed onthe Project menu. If more than four designers are loaded, the later ones will beavailable from the More ActiveX Designers submenu on the Project menu.

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

4. Click the Next button to get to the Connection tab.

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

6. 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.

7. Click OK to close the dialog box.

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

9. Right-click the Northwind icon, and then click Add Command to display theCommand1 dialog box. In the dialog box, set the properties as shown below:

Property SettingCommand Name Customers

Connection Northwind

DataBase Object Table

Object Name Customers

10.


12. Right-click the Customers command, and click Add Child Command to displaythe Command2 dialog box. In the dialog box, set the properties as shown below:

Property SettingCommand Name Orders



Object Name Orders

13.


14. Click the Relation tab. The Relate to a Parent Command Object check boxshould be checked. The Parent box should contain Customers; both the ParentFields and Child Fields/Parameters boxes should contain CustomerID.

When designing relational databases, it's customary for related tables to use thesame name for linking fields. In this case, the linking fields are both namedCustomerID. The Data Environment designer automatically matches such pairs inthe dialog box.

15. Click Add. Click OK to close the dialog box.

Clicking the Add button adds the relation to the Command object. After closingthe dialog box, the Data Environment designer reflects the relationship bydisplaying the two commands as a hierarchy. This hierarchy will be used to createthe data report.

16. Set the properties of the project and designer according to the settings below, thensave the project:

Object Property SettingProject Name prjNwind

DataEnvironment Name deNwind

Form Name frmShowReport

17.

Creating the Data ReportOnce 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 seriesof topics creates a limited report that displays only a few fields.

To create a new data report

1. On the Project menu, click Add Data Report, and Visual Basic will add it toyour project. If the designer is not on the Project menu, click Components. Clickthe 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 onthe Project menu. If more than four designers are loaded, the later ones will beavailable from the More ActiveX Designers submenu on the Project menu.

2. Set the properties of the DataReport object according to the table below:

Property SettingName rptNwind

Caption Northwind Data Report


3. On the Properties window, click DataSource and then click deNwind. Thenclick DataMember and click Customers.

Important To set the DataSource property to deNwind, the Data Environmentdesigner must be open. If it is closed, press CTRL+R to display the Projectwindow, then double-click the data environment icon.

4. Right-click the Data Report designer, and click Retrieve Structure.

You have added a new group section to the designer. Each group section has aone-to-one correspondence to a Command object in the data environment; in thiscase, 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 Commandobjects wherein a Command object has more than one child object — childCommand objects parallel to each other. The Data Report designer, however, isnot as flexible, and can't display more than one child object at a time. In suchcases, when executing a Retrieve Structure command, the Data Report willdisplay only the first of the child commands, and none below it. Thus you shouldavoid creating Command hierarchies with parallel children commands.

5. From the Data Environment designer, drag the CompanyName field (under theCustomers 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 thistime.

6. Delete the Label control (rptLabel) named Label1.

If you do not want a Label control to be included with the TextBox control, youcan uncheck the Drag and Drop Fields Caption option on the Field Mappingtab of the Data Environment designer's Options dialog box.

7. From the Data Environment designer, drag the OrderDate field (under theOrders command) onto the Details (Orders_Detail) section. Delete the Labelcontrol.

The Details section represents the innermost "repeating" section, and thuscorresponds to the lowest Command object in the Data Environment hierarchy:the Orders Command object.

8. 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 possiblebecause the height will be multiplied for every OrderDate returned for the


CompanyName. Any extra space below or above the OrderDate text box willresult in unneeded space in the final report.

9. Save the project.

Preview the Data Report Using the Show MethodNow that the data environment and the data report objects have been created, you arealmost ready to run the project. One step remains: to write code to show the data report.

To show the data report at run time

1. On the Project Explorer window, double-click the frmShowReport icon todisplay the Form designer.

2. On Toolbox, click the General tab.

When you add a Data Report designer to your project, its controls are added to thetab named DataReport. To use the standard Visual Basic controls, you mustswitch to the General tab.

3. Click the CommandButton icon and draw a CommandButton on the form.

4. Set the properties of the Command1 control according to the table below:

Property SettingName cmdShow

Caption Show Report

5.

6. In the button's Click event, paste the code below.7. Pr i vat e Sub cmdShow_Cl i ck( )8. r pt Nwi nd. Show

End Sub

9. Save and run the project.

10. Click Show Report to display the report in print preview mode.

Optional—Setting the Data Report as the Startup Object

You can also display the data report with no code at all.

1. On the Project menu, click prjNwind Properties.

2. In the Startup Object box, select rptNwind.



Note If you use this method, you can remove the Form object from your project.

Step by Step

This topic is part of a series that walks you through creating a sample data report.

To SeeGo to the next step Extending the Data Report

Exporting a Data Report

After compiling a report you may wish to reuse it, either as part of a larger document orperhaps for distribution on an intranet or the Internet. The Data Report designer'sExportReport method allows you to accomplish these tasks. Using the ExportReportmethod, you can export any report as a text file or as an HTML file. Additionally, youcan use any of a number of ExportFormat objects to tailor the content and appearance ofan exported file.

Important The ExportReport method does not support the exporting of images orgraphic shapes.

ExportFormat ObjectsThe ExportReport method was designed to work with the ExportFormats collection. EachExportFormat object in the collection represents a separate format for the report. Forexample, a report formatted for intranet distribution might include names of groups oremployees as part of the report header; for Internet distribution, those same names wouldbe removed or replaced. You would therefore create at least two ExportFormat objects,each tailored for the distribution mechanism. However, it is possible to export a reportwithout creating any ExportFormat objects because four are already provided for you.

Four Default ExportFormat Objects

By default, the ExportFormats collection contains four members. The four members andtheir associated file filters are shown in the chart below:

Object File Filter DescriptionExportFormats(1) * .htm, * .html HTML

ExportFormats(2) * .htm, * .html Unicode HTML

ExportFormats(3) * .txt Text

ExportFormats(4) * .txt Unicode Text


If you need to use any of the default types, you can also use the Key property to specify adefault type. The Key property values and the constants are shown below:

Object Key ConstantExportFormats(1) key_def_HTML rptKeyHTMLExportFormats(2) key_def_UnicodeHTML_UTF8 rptKeyUnicodeHTML_UTF8ExportFormats(3) key_def_Text rptKeyTextExportFormats(4) key_def_UnicodeText rptKeyUnicodeText

By using one of the four members, you can export a report without creating anotherExportFormat object, provided the default meets your requirements. For example, toexport a daily HTML report, you might use the following code:Dat aRepor t 1. Expor t Repor t r pt KeyHTML

Displaying a Dialog Box Is Optional

The programmer can determine whether or not a dialog box will be presented whenexporting a report. For example, if the report is created automatically every morning, andwritten to the same file for distribution by an intranet, there is no need to display a dialogbox. As long as a valid file path and key are supplied, and the Overwrite parameter is setto True, the dialog will not be displayed.' Expor t a r epor t as HTML, over wr i t i ng any exi st i ng f i l e. Expor t' al l pages t o t he Dai l y_Repor t . ht m f i l e.Dat aRepor t 1. Expor t Repor t r pt KeyHTML, " C: \ Temp\ Dai l y_Repor t " , Tr ue, , _r pt RangeAl l Pages

Note In the above code the second argument seems to be a directory but is actually thefile name. "Daily_Report.htm" is the name of the written file. The ExportFormat objectsupplies the file extension (.htm), and thus there's no need to write it in the file nameargument.

Expor tFormat Supplies Dialog Information

The ExportFormat object also contains the information that is displayed when the userinvokes the ExportReport method. In particular, the FileFormatString property sets thetext that is displayed in the Export dialog box's Save As Type box. For example, imaginethat a company has a standard ExportFormat object to be used with all reports. Thefollowing code would ensure that the ExportFormat is available from the list of formattypes on the Export dialog box:Di m st r Templ at e As St r i ng

' Fi r st cr eat e t he t empl at e f or t he Expor t For mat obj ect .st r Templ at e = " MyCompany Dai l y Repor t " & vbCr Lf & r pt TagBody

' Add an Expor t For mat obj ect . The Fi l eFor mat St r i ng det er mi nes' what wi l l be di spl ayed i n t he Expor t di al og box.Dat aRepor t 1. Expor t For mat s. Add _ Key: =" St andar dRepor t " , _ For mat Type: =r pt Fmt Text , _


Fi l eFor mat St r i ng: =" St andar d Repor t ( * . t xt ) " , _ Fi l eFi l t er : =" * . t xt " , _ Templ at e: =st r Templ at e

' I nvoke t he Expor t Repor t met hod speci f yi ng t he Expor t For mat' obj ect named St andar dRepor t t o use.Dat aRepor t 1. Expor t Repor t " St andar dRepor t " , , Fal se, Tr ue, _r pt RangeFr omTo, 1, 10

When invoked, the Export dialog box resembles this:

Template Codes

The core of an ExportFormat object is its template. A template is simply a stringcontaining both the text you want to appear along with constants that represent variousparts of the data report. The constants, values, and descriptions are shown in the tablebelow:

Constant Value DescriptionrptTagTitle  Represents the title of the

report, as found in the Titleproperty.

rptTagBody  Represents the body of thereport.

To create a simple data report that includes only the name of the author followed by thebody of the report, the template would resemble this:Di m st r T As St r i ngst r T = " Aut hor : " & I nput Box( " Your name" ) & vbCr Lf & r pt TagBodydr pNwi nd. Expor t For mat s. Add " AuExp" , r pt Fmt Text , _" Aut hor Onl y Text Fi l e" , " * . t xt " , st r T

Printing a Data Report

Printing a data report can be accomplished in one of two ways. The user can click thePrint button that appears on the data report in Print Preview mode (using the Showmethod), or you can programmatically enable printing using the PrintReport method. Ifan error occurs during printing, trap it in the Error event.

For More Information See "Data Report Events."

Choosing to Display a Pr int Dialog BoxWhen printing a report programmatically, you have two choices: to print by displayingthe Print dialog box, or by printing without displaying the dialog box.

To display the Print dialog box

1. Add a CommandButton to a Form.

2. In the button's Click event, place the following code:3. Dat aRepor t 1. Pr i nt Repor t Tr ue

The Print dialog box allows the user to select a printer, print to file, select a range ofpages to print, and specify the number of copies to print.

Note Printers must be installed on the computer in order to present a choice of printers.

Printing Without a Dialog BoxIn some cases, you may wish to print the report without user intervention. ThePrintReport method also gives you the option of selecting a range of pages to print, eitherall, or a specified range.

To print without displaying the dialog box

1. Add a CommandButton to a Form.

2. In the button's Click event, place the following code:3. Dat aRepor t 1. Pr i nt Repor t Fal se

Or, to specify a range of pages to print, use the code below:Dat aRepor t 1. Pr i nt Repor t Fal se, r pt RangeFr omTo, 1, 2

Visual Basic Concepts

Data Report Events

Like the standard Visual Basic form, the life of a Data Report designer is marked bycertain key events. Those events, and the order in which they occur, are shown in thefollowing table:

Event DescriptionInitialize Occurs after the query has completed, and controls are sited on the

form.

Resize Occurs when the designer is first displayed or when the windowstate of an object changes.

Activate Occurs when the designer becomes the active window.

ProcessingTimeout Occurs approximately once every second until all processing hasended. Use this event to determine if processing has taken too long,and to cancel the processing.


Note This event will not occur until the query has completed. Seebelow.

[Deactivate] Occurs when the designer is no longer the active window. Use thisevent to determine if the user has clicked another form or designer.

QueryClose Occurs before the designer is terminated. Set the Cancel argumentto True to cancel termination. The CloseMode argument returns thetype of action that is causing the termination.

Terminate Occurs when all references to the designer have been set to 0.

Timeout and Asynchronous Call EventsIn addition to the designer's lifetime events, the DataReport object also features eventsthat allow you to trap errors and monitor synchronous and asynchronous function calls.

Expor tRepor t and Pr intRepor t: Query, Synchronous and AsynchronousProcesses

When either the ExportReport or PrintReport method is invoked, the process is dividedinto three stages—the query, synchronous processing, and asynchronous printing orexportation:

1. Query—When the data report is first created, a query is sent to the data provider.

2. Processing—The data retrieved by the query is processed by Visual Basic tocreate the report. The data is cached in a temporary file on the computer. Thisprocess is synchronous.

3. Asynchronous Printing or Exporting—After creating the report, the report isexported or printed. This process is asynchronous.

When the Show method is invoked, the data report executes the query and then processesthe data in a synchronous process before displaying the report.

Because these methods combine both synchronous and asynchronous processes, there areseparate events to monitor each kind of process.

The ProcessTimeOut Event—for Synchronous Functions

Processing a large data report may take some time. If you want to allow your users tocancel out of a lengthy operation (such as Show, ExportReport, or PrintReport), you canuse the ProcessingTimeout event to monitor how many seconds have passed, and set thecancel argument to True at the user's command. The code below shows an example.Pr i vat e Sub Dat aRepor t _Pr ocessi ngTi meout ( ByVal Seconds As Long, _Cancel As Bool ean, ByVal JobType As MSDat aRepor t Li b. AsyncTypeConst ant s,_ByVal Cooki e As Long)


Sel ect Case Seconds Case 30 I f MsgBox( " Thi s has t aken " & Seconds & " seconds. Cancel ?" , _ vbRet r yCancel ) = vbCancel Then Cancel = Tr ue End I f Case 45 I f MsgBox( " Thi s has t aken " & Seconds & " seconds. Cancel ?" , _ vbRet r yCancel ) = vbCancel Then Cancel = Tr ue End I f Case 60 ' Cancel aut omat i cal l y af t er 60 seconds. Cancel = Tr ue End Sel ectEnd Sub

Note It is not guaranteed that the ProcessingTimeout event will occur at the intervalsspecified above. For example, other Visual Basic code running in the background mayprevent the event from occurring. In that case, set the Case statement to a range of values;when the event occurs, set a module-level flag to True, and check it on subsequentoccurrences.

Error Event—For Asynchronous Functions

To trap errors that occur when no Visual Basic code is executing (that is, anasynchronous function), use the Error event. For example, if the PrintReport orExportReport method fails in the asynchronous stage, the Error event will occur. Theexample below traps asynchronous errors:Pr i vat e Sub Dat aRepor t _Er r or ( ByVal JobType As _MSDat aRepor t Li b. AsyncTypeConst ant s, ByVal Cooki e As Long, _ByVal Er r Obj As MSDat aRepor t Li b. Rpt Er r or , ShowEr r or As Bool ean) Sel ect Case JobType ' The JobType i dent i f i es t he pr ocess. Case r pt AsyncPr i nt ' Tr ap Pr i nt Repor t er r or s her e. Case r pt AsyncRepor t ' Tr ap Expor t Repor t er r or s her e. End Sel ectEnd Sub

You can also use the Error event to trap specific cases, such as the lack of a printer on thecomputer, as shown in the code below:Pr i vat e Sub Dat aRepor t _Er r or ( ByVal JobType As _MSDat aRepor t Li b. AsyncTypeConst ant s, ByVal Cooki e As Long, _ByVal Er r Obj As MSDat aRepor t Li b. Rpt Er r or , ShowEr r or As Bool ean) Sel ect Case Er r Obj . Er r or Number Case r pt Er r Pr i nt er I nf o ' 8555 MsgBox " A pr i nt i ng er r or has occur r ed. " & _ " You may not have a Pr i nt er i nst al l ed. " ShowEr r or = Fal se Exi t Sub Case El se ' handl e ot her cases her e. ShowEr r or = Tr ue


End Sel ectEnd Sub

The AsyncProgress Event

The AsyncProgress event is not designed to trap errors, but to allow you to monitor thestate of the asynchronous function. By the time this event occurs, all of the data has beenprocessed; thus two of the event's arguments are PagesCompleted and TotalPages. Theevent also includes arguments that identify the asynchronous operation: the JobType andCookie arguments can then be used to monitor the progress of any process.

Understanding Control Placement on theData Report Designer

Note You will understand this topic better if you read the Data Report tutorial first,beginning at Creating a Simple Data Report.

One way of creating a data report is to:

1. Create a Data Environment designer containing a hierarchy of Command objects.

2. Set the DataSource property of the Data Report designer to the DataEnvironment designer.

3. Set the DataMember property to the topmost Command object.

4. Right-click the Data Report designer and click Retr ieve Structure.

After retrieving the structure, an appropriate number of Group headers and footerswill be created, and each header/footer pair is assigned a name that corresponds toa Command object.

5. Drag Command objects from the Data Environment designer to the correspondingsection on the Data Report designer.

All of the data fields contained by a Command object are automatically created onthe data report as TextBox controls on the section where the Command object isdropped. The DataMember and DataField properties of each TextBox are set inaccordance with the Command object and its data fields.

6. Drag TextBox controls from the section where each was created onto a differentsection of the Data Report designer.


7. Add Function controls as needed to the report.

When the data report is bound to a data environment, the rules for placement of controlson the report are not immediately apparent. This topic explains how the hierarchy createdin the data environment relates to the system of group headers and footers constructed inthe data report.

Command Object Corresponds to Group Header /Footer or Details Section

With two exceptions, every Command object in the data environment corresponds eitherto a pair of group header and footer sections, or to the Detail section. The exceptions arediscussed later in this topic.

Hierarchy Versus Headers, Footers, and Detail

The figure below shows a schematic view of the Data Environment designer with fourCommand objects, each in a parent/child relationship with at least one other command.The data fields that belong to the table are not shown.

The Data Report designer, on the other hand, is constructed as a series of sections. Andeach section can be categorized into one of four types: Report header/footers, Pageheader/footers, Group header/footers, and the Detail section. For the purposes ofinstruction, we can disregard the Report and Page header/footer pairs. This leaves theGroup headers/footers and the Detail section.

The Detail section, the innermost section of the designer, corresponds to the lowest-levelCommand object. As you go up through the hierarchy, the Detail section is bracketed bypairs of sections, with each pair associated with a single Command object. The figurebelow correlates the Command objects with the sections:

Sections Corresponding to Command Objects

Thus the figure shows that the hierarchy of the data environment actually corresponds toan expanding series of brackets, with the innermost (Detail) section corresponding to thelowest level of the hierarchy, and the outermost corresponding to the highest-levelCommand object.

Controls Can Be Placed in Any Section in a Lower Level

The placement of a control is governed by what section (or pair of sections) it belongs to.In brief, a control can be placed in the section where it originates, and in all sections thatare at a level lower than itself. For example, if a control belongs to the Command1section pair, it can also be placed in the section pairs for Commands 2, 3, and 4. A secondexample: A control that originates in section 3 can also be placed in section 4, but not in


sections 1 and 2. Taken to its conclusion, controls that originate in the Command 4section (the Details section) cannot be placed anywhere else except in the Details section.

Placing the Function ControlThe Function control has three exceptions to the above guidelines regarding controlplacement. The Function control is not directly bound to the recordset, as is the TextBoxcontrol. Instead, the Function control calculates its value as the report is generated. Forthis reason, a Function control can only be placed in Footer sections of the report.

A second exception for the Function control: it can only be placed in any section pair thatis one level above its own. For example, if the Command 3 object contains a Quantityfield, you can place a Function control that sums the Quantity values onto the footersection for Command 2, or the footer section for Command 1.

The third exception concerning the Function control is this: unlike the other data-boundcontrol (the TextBox control), the Function control can be placed in the Report Footersection. When you do so, the scope of the control's calculation will be increased toinclude the whole report. For example, a Function control placed in the Report footer tocalculate the sum of the Quantity field will calculate the sum of every Quantity control onthe report.

The Exceptions: Grouping and Grand Total AggregateWith two exceptions, every Command object in the Data Environment corresponds to apair of group headers and footers. The first exception occurs when you use the Groupingfeature of the Data Environment designer.

Grouping Fields

When you create grouping fields, the Data Environment designer creates two foldersunder a single Command object. The first contains the grouping fields, and the secondcontains the Detail fields. Even though a new Command object is not created for thegrouping fields, you must create a new group header/footer pair on the data report andthis requirement qualifies it as an exception.

Grand Total Aggregate Fields

The second exception occurs when you create a Grand Total aggregate field in the dataenvironment. As happens when creating a grouping field, a new folder is created for theCommand object. The new folder contains any Grand Total aggregate fields created, andyou must add a new group header/footer pair to the data report.

For More Information Details about creating aggregate field can be found in "CreatingAggregates" in "Using the Data Environment Designer."

Checking the Hierarchy


If you are in doubt as to the hierarchy of the Data Environment Command objects, youhave two ways of ensuring that the data report has the correct group header/footerstructure:

Retrieve Structure—If you have not placed many controls onto the Data Reportdesigner, and you can tolerate the restructuring of your data report, use the RetrieveStructure command to automatically create the right number of group headers andfooters.

ADO Hierarchy Information—Right-click the topmost Command object in the DataEnvironment designer and click Hierarchy Info to display the Hierarchy Informationdialog box. Click the View ADO Hierarchy tab to see a graphical representation of theCommand objects' hierarchy.

Visual Basic Concepts

Extending the Data Report

Once you have a simple data report, it's easy to extend it and make a more complexpresentation. In the procedure below, the data environment is first extended with theOrder Details and Products tables. The extended report includes the names of productsordered on a specific date, and resembles the figure below.

Extended Data Report With Order Details

Note This topic is part of a series that walks you through creating a sample data report.It begins with the topic, Creating a Simple Data Report.

To extend the data environment

1. On the Data Environment designer, right-click the Orders Command object.Then click Add Child Command.

2. On the Command1 Properties dialog box, set the following properties:

Property SettingCommand Name OrderDetails



Object Name Order Details

3.


4. Click the Relation tab. The Relate to a Parent Command Object check boxshould be checked. The Parent box should contain Orders; both the ParentFields and Child Fields/Parameters boxes should contain OrderID. Click theAdd button and then click OK to close the dialog box.

5. Right-click the OrderDetails Command object, and click Add Child Command.Set the properties of the connection as shown below:

Property SettingCommand Name Products



Object Name Products

6. Click the Relation tab. The Relate to a Parent Command Object check boxshould be checked. The Parent box should contain OrderDetails; both theParent Fields and Child Fields/Parameters boxes should contain ProductID.Click the Add button and then click OK to close the dialog box.

Extending the Data ReportOnce the data environment has been extended with new tables, you can extend the datareport as well by dragging fields from the Data Environment designer to the Data Reportdesigner.

To Extend the data report

1. Right-click the Data Report designer, and clear Show Page Header/Footer box.

Clearing this option deletes the page header and footer, which are not being usedat this point.

2. Right-click the Data Report designer, and click Insert Group Header/Footer .The Insert New Group Header/Footer dialog box will be displayed.

The dialog box allows you to determine if the new header and footer will"bracket" other header/footer pairs. This becomes important as you add moreheader and footer pairs because the outermost pair of header/footers subordinatesall other pairs. This is discussed in greater detail in "Parts of the Data Report."

3. Click OK to select the default placement of the new header and footer pair andclose the dialog box.

4. Select the new group header, and on the Properties window, change its namefrom Section1 to Orders_Header . Change the corresponding footer name fromSection4 to Orders_Footer .


5. Repeat steps 2 to 3. Name the new group header OrderDetails_Header , and thenew group footer OrderDetails_Footer .

6. Click the Detail (Orders_Detail) section to select it. On the Properties window,change the section's name to Products_Detail.

7. Using the mouse, drag the OrderDate field from the Detail (Products_Detail)section to the Orders_Header section.

8. From the Data Environment designer, drag the ProductName field (under theProducts command) into the Detail (Products_Detail) section.

9. Delete the Label control named Label1.

10. Resize the group headers, and rearrange the text box controls to resemble thefigure below.

The figure above requires some explanation. First, the Group footers are all closedin order to take up the least possible space. Like the Details section, anyadditional space left in any header or footer will be multiplied in the final report.Therefore, if a header or footer doesn't contain any fields, you can close thedistance between the headers or footers.

The Group Header named OrderDetails_Header is also closed. If you wonderwhy no fields are being shown, you must understand that the Order Details tablein the Northwind database is a join table—the table contains only the IDs ofrecords from the Orders table joined to IDs of records from the Products table.Thus the Order Details table doesn't contain fields which are actually displayed.Instead, it functions only to join two other tables. In the Data Report designer, theOrder Details table therefore functions only to create groups of records—theproduct names grouped under the order dates.

Finally, the Details section contains only the names of products. The Detailssection contains the innermost level of repeating records.


Step by Step


To SeeGo to the next step Adding a Calculated Field to the Data Report

Start from the beginning Creating a Simple Data Report


Adding a Calculated Field to the DataReport

A calculated field is a field whose value is calculated as the report is generated. Forexample, when estimating the tax on an order, you must multiply the total price by thelocal tax rate. But since local tax rates differ and won't be stored in the database, the taxis generated as the report is created—a calculated field.

In this example, the extended Data Report designer is modified to include a Total fieldthat calculates the value of Quant i t y * Uni t Pr i ce. Adding a calculated field involvesthe following steps:

1. Using a SQL statement in the data environment's Command object to create thecalculated field.

2. Adding three text box controls to the data report: Quantity, UnitPrice, and (forthe calculated field) Total.

The modified data report resembles the figure below.


To add a calculated field to the data report

1. In the Data Environment designer, right-click the OrderDetails command. Thenclick Properties to display the OrderDetails Properties dialog box.

2. On the General tab, click the SQL Statement button, and add the followingstatement to the box:

3. Sel ect Or der I D, Pr oduct I D, Uni t Pr i ce, Quant i t y, ( Quant i t y *Uni t Pr i ce) As Tot al Fr om [ Or der Det ai l s]

The SQL statement multiplies the Quantity value by the UnitPrice value to createthe Total value—the calculated field. Also note that the name of the table (OrderDetails) contains a space, and must be enclosed by brackets.



5. From the Data Environment designer, drag the Quantity, UnitPrice, and Totalfields (under the OrderDetails command) onto the Detail (Products_Detail)section of the Data Report designer.

6. Delete two Label controls, and change the Caption value of the remaining Labelcontrol to * , and arrange the controls to resemble the figure below:

7. On the Data Report designer, click the UnitPrice text box to select it. On theProperties window, double-click DataFormat to display the Property Pagesdialog box.

8. In the Format Type box, click Currency. In the Symbol combo box, select thecurrency appropriate to your country.

9. Repeat steps 6 and 7 to change the DataFormat property of the Total text box toCurrency.

10. Click the Total text box control to select it. On the Properties window, change theAlignment property to 1 – rptJustifyRight.


Step by Step


To SeeGo to the next step Adding a Function Control to the Data Report


Adding a Function Control to the DataReport

The Data Report designer features its own set of controls. Among these, the Functioncontrol has no counterpart among the Visual Basic intrinsic controls and deserves furtherexplanation.

The Function control displays data that is calculated at run time, using a built-in function,as the report is generated. A typical example is shown in the figure below, where the


Function control is used to display the sum of sub-totals to create a total value for aparticular order.

If you have created a calculated field using a SQL statement (in the previous topic,"Adding a Calculated Field to the Data Report"), you may wonder why a Functioncontrol can't also be used to calculate the value of Quant i t y * Uni t Pr i ce. In short, theFunction control can calculate values only after all other records in a group section havebeen processed. In contrast, a SQL statement calculates the values as part of the fields ina record as they are processed.

The steps to adding a Function control to the Data Report designer are:

1. Draw a Function control in an appropriate Footer section of the Data Reportdesigner.

2. Set the DataMember and DataField properties to appropriate values (a numericfield from a relevant data environment Command object.)

Data Report with Function Control Showing Totals


To add a Function control to the data report

1. Using the mouse pointer, click the group footer named Customers_Footer , anddrag it towards the bottom of the window, to create a space between it and theOrders_Footer footer.

2. On the Toolbox, click the rptFunction control.

3. Draw the rptFunction control in the space between the two footers.

4. Set the properties of the rptFunction control according to the table below:

Property SettingDataMember OrderDetails

DataField Total

Name fncTotal

Alignment 1 – rptJustifyRight

DataFormat Currency

5.


6. The FunctionType property of the control determines what operation will beperformed with the data found in the DataField. By default, the property is set to0-RptFuncSum, to sum the data. Other functions include: Average, Minimum,Maximum, Row Count, STDDev (Standard Deviation), and Value Count.

7. Draw a Line control just above the Quantity text box, and a Label control to theleft of the text box. Set the Caption property of the label to Total.

8. Click the fncTotal control to select it. On the Properties window, double-clickFont to display the Font dialog box. Change the Font style to Bold.

9. Repeat step 6 with the Label control.

10. Position the controls on the designer to resemble the figure below:


Increasing the scope of the functionYou can increase the scope of the function by placing the control in a lower group footer.For example, you may want to find out the grand total of each customer's set of orders.

To show a grand total for all orders

1. Draw a new Function control in the Customers_Footer section. Align the newcontrol with the Function control created above.

2. Draw a Label control to the left of the new Function control.

3. Set the properties of the controls as shown in the table below:

Control Property SettingrptFunction Name fncGrandTotal

rptFunction DataMember OrderDetails

rptFunction DataField Total

rptFunction Font (Font Style) Bold

rptFunction Alignment 1 – rptJustifyRight

rptLabel Caption Grand Total

rptLabel Font (Font Style) Bold

rptLabel Alignment 1 – rptJustifyRight

4.


5. The Data Report designer should now resemble the figure below:


Optional—Adding a Report TotalIt's possible to increase the scope of the Function control even further. If you wish to seea total of all orders in the report, you can add a Function control to the Report Footersection.

To add a Report Total to the data report

1. Right-click the Data Report designer and click Show Report Header/Footer .

2. Add a Function control to the report footer.

3. Set the property of the control as shown in the table below:

Property SettingName fncReportTotal

DataMember OrderDetails

DataField Total

DataFormat Currency

Alignment 1 – rptJustifyRight

4.

5. Note The number for the entire report will be especially large. Therefore thewidth of the Function control will have to be adjusted accordingly.


Step by Step


To SeeGo to the next step Grouping Information in the Data Report


Grouping Information in the Data Report


Information that is grouped can give the user a different perspective on data. Working intandem with the Data Environment designer, the Data Report designer gives you theability to group data according to any field in a table. The figure below shows a datareport grouped by countries.

Data Report Grouped By Country

Grouping by Command Field

The grouping field provided by the data environment's Command object differs from thegrouping already achieved by creating group headers and footers. Instead of using thetable of a database as the basis for grouping, the Grouping feature of the DataEnvironment designer allows you to select a particular field in the table as the groupingfield without having to create a new Command object.

The steps to adding a grouping field include:

1. Creating a group field in the Data Environment designer.

2. Adding a Group Header/Footer to the Data Report designer to correspond to thenew command.

3. Resetting the DataMember property of the data report to the new GroupingCommand object created in the data environment.

4. Dragging the group field from the data environment to the data report.


To add a grouping field to the Data Report designer

1. In the Data Environment designer, right click the Customers Command object.Click Properties to show the Customers Properties dialog box.

2. Click the Grouping tab.

3. Click Group Command Object.

4. In the Fields in Command box, double-click Country. Click OK to close thedialog box.


Notice that the Customers Command object has been renamed as CustomersGrouped using Customers_Grouping, and that two new folders have beencreated for the fields. The first, Summary fields in Customer_Groupingcontains the field(s) that the subordinate fields will be grouped by. Thesubordinate fields are contained in the second folder, Detail Fields inCustomer_Grouping.

5. Right-click the Data Report designer, and then click Insert GroupHeader/Footer .

6. Click the up arrow three times to insert the group header at the outermost edge ofthe header/footer pairs. Click OK to close the dialog box.

7. On the Data Report designer, click the new Group Header to select it, andchange its name from Section1 to Customers_Grouping_Header in theProperties window. Click the new Group Footer to select it, and change itsname to Customers_Grouping_Footer .

8. Click the Data Report designer's title bar to select the entire data report. On theProperties window, click DataMember and change the property from Customersto Customers_Grouping.

When the grouping field was added to the data environment, the equivalent of anew Command object was also added to the data report. That virtual Commandobject is displayed in the drop-down list of data members asCustomers_Grouping.

9. In the Data Environment designer, open Summary Fields inCustomers_Grouping. Drag the Country field into the new section on the DataReport designer.

10. Delete the Label control that accompanies the Country field. Place the new fieldat the leftmost edge of the designer, which should now resemble the figure below:


Step by Step


To SeeGo to the next step Adding an Aggregate Field to the Data Report



Adding an Aggregate Field to the DataReport

In the Data Environment designer you can also create aggregate fields—fields thatsummarize data from a section. An aggregate field is thus similar to the Function controlin that both are calculated as the report is generated. But there are a few differences:whereas the Function control can only be placed in a Group Footer, an aggregate fieldcan be placed in any section of the Data Report designer, except the ReportHeader/Footer and Page Header/Footer sections.

Another difference is found in how the two fields are created: while the Function controlis a feature of the Data Report designer, the aggregate field is a feature of the DataEnvironment designer. The steps to adding an aggregate field to the report are:

1. Create an aggregate field in the Data Environment designer.

2. From the Data Environment designer, drag the aggregate field onto the DataReport designer.

As an example, the figure below shows an aggregate field that displays the number ofproducts ordered.

Data Report with Aggregate Field

Using a Function control can be more efficient than an aggregate field. When the datareport calculates the value for a Function control, it takes a certain amount of processingto create each value. When the data environment creates an aggregate field, it takes asimilar amount of processing. However, you can save that processing time by using theFunction control because the Data Report designer must create the entire report in its ownprocess.


To create an aggregate field in the data environment

1. On the Data Environment designer, open the Detail Fields in Customers folder,and right-click Orders, then click Properties.

2. On the Orders Properties dialog box, click the Aggregates tab.


3. Click the Add button.

4. Click the Name box and type ProductCount.

5. In the Function combo box, select CNT.

6. In the Aggregate combo box, select OrderDetails (it should be selected bydefault).

7. In the Field combo box, select ProductID.


Now that you have created the aggregate field, you can place it on the Data Reportdesigner.

To Add the aggregate field to the data report

1. From the Data Environment designer, drag the ProductCount aggregate fieldonto the group section named Orders_Header .

2. Change the Caption of the Label control to Product(s) Ordered.

3. Reposition and resize the TextBox control and the Label control so the designerresembles the figure below:


Step by Step


To SeeGo to the next step Grouping Information in the Data Report



vb6

Documents

data report data report

data report information

simple data report

report headercontains

report title

report type

data source

visual designer