Using VSView Reporting Edition > Creating a Report Definition Using Code |
The easiest way to create new report definitions is to use the VSReport8 Designer, but you can also create reports from scratch, using code. This approach requires some extra work, but it gives you complete flexibility. You can write your own report designer or ad-hoc report generator.
Note that creating a report definition is not the same as rendering a report. To render a report, you can simply load an existing definition and call the Render method.
The example that follows uses Visual Basic to create a simple tabular report definition based on the NorthWind database. The code is commented and illustrates most important elements of the VSReport8 control object model.
NOTE: Make sure that your Microsoft ODBC Data Source is pointing to the NWIND.MDB and that the name is set as Northwind. |
First, add the VSReports and VSPrinter control on the form. Add a command button. Set the following properties:
Name |
Type |
---|---|
VSR |
VSReport |
VP |
VSPrinter |
CreateReportDefinition |
Command Button |
Second, initialize the control using the Clear method to clear its contents and setting the control font (this is the font that will be assigned to new fields):
Example Title |
Copy Code
|
---|---|
Private Sub CreateReportDefinition_Click() Dim f As Field ' variable used to hold new fields ' initialize control With vsr .Clear ' clear any existing fields .FontName = "Tahoma" ' set default font for all controls .FontSize = 9 End With |
Next, set up the DataSource Object that will retrieve the data we want from the NorthWind database. This is done using the ConnectionString and RecordSource properties, just like you do with the Microsoft ADO DataControl:
Example Title |
Copy Code
|
---|---|
' initialize DataSource With vsr.DataSource .ConnectionString = "dsn=NorthWind" .RecordSource = "Employees" End With |
Next, initialize the Layout object that defines how the report will be laid out on the page. In this case, render the report in Portrait mode and set its Width to 6.5 inches (that's 8.5 minus one inch for margins on each side):
Example Title |
Copy Code
|
---|---|
' initialize Layout With vsr.Layout .Orientation = vsrPortrait .Width = 6.5 * 1440 ' 8.5 - margins End With |
Now comes the interesting part. Every report has five basic sections: detail, report header, report footer, page header, and page footer. We will set up the report header by setting a couple of properties and adding a title field to it.
Example Title |
Copy Code
|
---|---|
' create a report header With vsr.Sections(vsrHeader) .Height = 1440 .Visible = True .BackColor = RGB(200, 200, 200) ' solid background Set f = .Fields.Add("FldTitle", "Employees Report", _ 0, 0, 8000, 1440) f.FontSize = 24 f.FontBold = True f.ForeColor = RGB(0, 0, 100) End With |
The section object has a Fields collection. The collection's Add method creates a new field and assigns it to the section. The parameters specify the new field's Name, Text, Left, Top, Width, and Height properties. By default, the field has the same font as the control. Since this is a title, it makes sense to change the font and make it larger. Note that the field should be tall enough to accommodate the font size, or nothing will appear in it.
Next, we will set up the page footer section. This section is more interesting because it contains calculated fields. Calculated fields contain VBScript expressions in their Text property, which are evaluated when the report is rendered. To make a field calculated, set it's Calculated property to True.
Example Title |
Copy Code
|
---|---|
' create a page footer With vsr.Sections(vsrPageFooter) .Height = 500 .Visible = True Set f = .Fields.Add("FldFtrLeft", _ """Employees: Printed on "" & Now", _ 0, 0, 4000, 300) f.Calculated = True Set f = .Fields.Add("FldFtrRight", _ """Page "" & Page & "" of "" & Pages", _ 4000, 0, 4000, 300) f.Calculated = True f.Align = vsrRightTop f.Width = vsr.Layout.Width - f.Left Set f = .Fields.Add("FldLine", "", _ 0, 0, vsr.Layout.Width, 20) f.LineSlant = vsrLSNoSlant f.BorderStyle = vsrBSSolid f.BorderColor = RGB(0, 0, 100) End With |
The page footer section uses expressions with variables that are not intrinsic to VBScript, but are defined by VSReport8. Page and Pages are variables that contain the current page number and the total page count. The section also uses a field configured to look like a line. This is done using the BorderStyle and LineSlant properties.
Next, we will set up the page header section. This section gets rendered at the top of every page and will display the field labels. Using a page header section to display field labels is a common technique in tabular reports. The code is simple, but looks a bit messy because of all the field measurements. In a real application, these values would not be hard-wired into the program.
Example Title |
Copy Code
|
---|---|
' create a page header with field labels With vsr.Sections(vsrPageHeader) .Height = 500 .Visible = True vsr.FontBold = True Set f = .Fields.Add("LblID", "ID", 0, 50, 400, 300) f.Align = vsrRightTop Set f = .Fields.Add("LblFirstName", "First", _ 500, 50, 900, 300) Set f = .Fields.Add("LblLastName", "Last", _ 1500, 50, 900, 300) Set f = .Fields.Add("LblTitle", "Title", 2500, 50, 1900, 300) Set f = .Fields.Add("LblTitle", "Notes", 4500, 50, 8000, 300) vsr.FontBold = False Set f = .Fields.Add("FldLine", "", _ 0, 400, vsr.Layout.Width, 20) f.LineSlant = vsrLSNoSlant f.LineWidth = 50 f.BorderStyle = vsrBSSolid f.BorderColor = RGB(100, 100, 100) End With |
The code above illustrates a powerful technique for handling fonts. Since every field inherits the control font when it is created, we set the control's FontBold property to True before creating the fields, and set it back to False afterwards. As a result, all controls in the page header section have a bold font.
To finalize the report, we will add the detail section. This is the section that shows the actual data. It has one calculated field below each label in the page header section.
Example Title |
Copy Code
|
---|---|
' create the detail section With vsr.Sections(vsrDetail) .Height = 330 .Visible = True Set f = .Fields.Add("FldID", "EmployeeID", 0, 0, 400, 300) f.Calculated = True Set f = .Fields.Add("FldFirstName", "FirstName", 500, 0, 900, 300) f.Calculated = True Set f = .Fields.Add("FldLastName", "LastName", 1500, 0, 900, 300) f.Calculated = True Set f = .Fields.Add("FldTitle", "Title", 2500, 0, 1900, 300) f.Calculated = True Set f = .Fields.Add("FldNotes", "Notes", 4500, 0, 8000, 300) f.Width = vsr.Layout.Width - f.Left f.Calculated = True f.CanGrow = True f.FontSize = 6 f.Align = vsrJustTop Set f = .Fields.Add("FldLine", "", 0, 310, vsr.Layout.Width, 20) f.LineSlant = vsrLSNoSlant f.BorderStyle = vsrBSSolid f.BorderColor = RGB(100, 100, 100) End With |
Note that all fields are calculated, and their Text property corresponds to the names of fields in the source recordset. Setting the Calculated property to True ensures that the Text property is interpreted as a database field name, as opposed to being rendered literally. It is important to adopt a naming convention for report fields that makes them unique, different from recordset field names. If you had two fields named "LastName", an expression such as "Left(LastName,1)" would be ambiguous. In this example we have adopted the convention of beginning all report field names with "Fld".
Note also that the "FldNotes" field has its CanGrow property set to True, and a smaller font than the others. This was done because the "Notes" field in the database contains a lot of text, and we want it all to appear in the report. Rather than make the field very tall and waste space, setting the CanGrow property to True tells the control to expand the field as needed to fit its contents; it also sets the containing section's CanGrow property to True, so the field doesn't spill off the section.
The report definition is done. To render it, all it takes is one line of code:
Example Title |
Copy Code
|
---|---|
' render the report into the VSPrinter control
vsr.Render vp
End Sub
|
This renders the report into a VSPrinter control named vp, that displays the report on the screen. Here's what the report looks like: