Report Component for Microsoft PowerPoint

Chapter 6 PTR Files

Using PTR files

About PTR files

Like PTReportGen, PTReportCom can also read a PTR file to generate a report. The PTR file is a text file with a .ptr extension. It contains information such as the name of the report template file, the name of the report file, the name of the log file, data sources, parameters and functions. The PTR file tells PTReportCom how to get data from data sources and how to put data into a report. Using the PTR file, it will simplify your development.

Using a PTR file with PTReport Object

PTReport object provides the PPTReport method to generate a report based on a PTR file. For example, you have created the PTR file "myreport.ptr" and the template file. In the PTR file, there are two parameters. The first parameter is the sales date "$SalesDate", and the second is the category of the products "$Category". You can call PPTReport method to generate the report.

Set pptApp = New PowerPoint.Application
Set ptrpt = New PTReport
Call ptrpt.PPTReport(pptApp, "c:\pptreport\myreport.ptr", _
    "1996-05-01", "Dairy Products")

PTReportCom will replace "$SalesDate" in SQL statements with "1996-05-01", replace "$Category" with "Dairy Products", and then submit SQL statements to data sources.

Using a PTR file in command line

In the PTReportCom, there is an executable file PPTReport.exe that can read a PTR file to generate a report. It is the same as PTReportGen command line. For example, you have created the PTR file "myreport.ptr" and the template file. In the PTR file, there are two parameters. The first parameter is the sales date "$SalesDate", and the second is the category of the products "$Category". You can run PPTReport.exe in command line mode as follows:

pptreport c:\pptreport\myreport.ptr 1996-05-01 "Dairy Products"

PTReportCom will replace "$SalesDate" in SQL statements with "1996-05-01", replace "$Category" with "Dairy Products", and then submit SQL statements to data sources.

Creating a PTR file

The PTR file is a text file. You can create and modify a PTR file in PTReportGen or a text editor.

Sometimes you want to make a PTR file programmatically. You can write a program to create a PTR file using C, perl or DOS shell, and then run PTReportCom to generate report. The two steps can be written into a batch file.

1. Write a program to make the PTR file as you need.

2. Write a batch file to call the program and PPTReport.exe.

For example, you write a batch file runrpt.bat as follows. changeptr is an executable file that reads template.txt and output template.ptr. First runrpt.bat call changeptr to make the PTR file, and then call PPTReport.exe to generate the report.

@echo off
if "%1"=="" goto usage
goto process
:usage
echo Usage: runrpt ReportDate
echo ReportDate	Date format 'YYYY-MM-DD'
goto :EOF
:process
changeptr %1 <"template.txt" >"template.ptr"
PPTReport "template.ptr" %1

Using parameters

You can use parameters in the PTR file. You can pass values to PTReportCom when it processes a PTR file. PTReportCom will replace the parameter names with the actual values. You can use the parameters in the SQL statements and the paths and names of the files.

To use a parameter, you must define it first. If you have defined a parameter name, you can use it in SQL statements. In fact, PTReportCom will replace all strings that are the same as the names of the parameters. You should be careful to define a unique name for each parameter. It is a good choice a name begins with the "$" character.

Example

Input an order id to get the order information. The field OrderID is numeric type.

1. Defining a parameter

Define a parameter as follows:

Name: $OrderID
Title: Order ID (>=10248)
Default: 10360

2. Using a parameter

You can use the parameter "$OrderID" in SQL statements. For example:

SELECT o.OrderID
,o.OrderDate
,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS Amount
FROM Orders o, OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderID = $OrderID
GROUP BY o.OrderID, o.OrderDate
;

Example

Define two parameters. The first parameter is the sales date, and the second is the category of the products. The field OrderDate is date type, and CategoryName is char type.

1. Defining parameters

Define parameters as follows:

Name1: $SalesDate
Title1: Sales Date
Default1: 1996-05-01
Name2: $Category
Title2: Category of Products
Default2:

2. Using parameters

You can use the parameters "$SalesDate", "$Category" in SQL statements. For example:

SELECT ......
FROM Orders, OrderDetails, Products, Categories
WHERE ......
AND OrderDate = '$SalesDate'
AND CategoryName LIKE '$Category%'
;
/* For Microsoft Jet SQL, LIKE '$Category*' */

Example

Get the information from the database, table and column that you identify when the report is generated.

1. Defining parameters

Define parameters as follows:

Name1: $Database
Title1: Database Name
Default1:
Name2: $Table
Title2: Table Name
Default2:
Name3: $Column
Title3: Column Name
Default3:

2. Using parameters

You can use the parameters "$Database", "$Table" and "$Column" in SQL statements. For example:

USE $Database;
or
DATABASE $Database;
SELECT $Column
FROM $Table
;

Example

Use parameters in the path and name of the report file and the log file.

1. Defining a parameter

Define a parameter as follows:

Name: $CustomerID
Title: Customer ID
Default: C000001

2. Using a parameter

ReportFileName=report\report_$CustomerID.doc
LogFileName=log\report_$CustomerID.log
or
ReportFileName=report\$CustomerID\report.doc
LogFileName=log\$CustomerID\report.log

Converting files

You can convert a file from Microsoft PowerPoint presentation to and from another file format. For example, the template file is a presentation file with a .ppt extension, and the report file is a PowerPoint show file with a .pps extension.

The file formats PTReportCom supports can be one of these. What file format PTReportCom supports is dependent on your Microsoft PowerPoint. For example, Microsoft PowerPoint 2003 supports Web archive (.mht), but Microsoft PowerPoint 2000 does not support it. For more information about converting files, please refer to Microsoft PowerPoint Help. The file "pconv.cfg" located in the PTReportCom directory contains the information of file formats. You can expand it if your Microsoft PowerPoint supports more file formats.

File Format Name	Value	Description	Extension	Converter
ppSaveAsPresentation	1	Presentation	ppt	Office2000
ppSaveAsPowerPoint7	2	PowerPoint 95 Presentation	ppt	Office2000
ppSaveAsPowerPoint4	3	PowerPoint 4 Presentation	ppt	Office2000
ppSaveAsPowerPoint3	4	PowerPoint 3 Presentation	ppt	Office2000
ppSaveAsTemplate	5	Design Template	pot	Office2000
ppSaveAsRTF	6	Outline/RTF	rtf	Office2000
ppSaveAsShow	7	PowerPoint Show	pps	Office2000
ppSaveAsAddIn	8	PowerPoint Add-In	ppa	Office2000
ppSaveAsPowerPoint4FarEast	10	PowerPoint 4 Far East	ppt	Office2000
ppSaveAsHTML	12	Web Page	htm html	Office2000
ppSaveAsHTMLv3	13	Web Page v3	htm html	Office2000
ppSaveAsHTMLDual	14	Web Page Dual	htm html	Office2000
ppSaveAsMetaFile	15	Windows Metafile	wmf	Office2000
ppSaveAsGIF	16	GIF (Graphics Interchange Format)	gif	Office2000
ppSaveAsJPG	17	JPEG (File Interchange Format)	jpg	Office2000
ppSaveAsPNG	18	PNG (Portable Network Graphics Format)	png	Office2000
ppSaveAsBMP	19	Device Independent Bitmap	bmp	Office2000
ppSaveAsWebArchive	20	Web Archive	mht mhtml	Office2002
ppSaveAsTIF	21	TIFF (Tag Image Format File)	tif	Office2002
ppSaveAsPresForReview	22	Presentation for Review	ppt	Office2003
ppSaveAsEMF	23	Enhanced Windows Metafile	emf	Office2003

Note: Some of these file formats may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed.

PTR File Reference

PTR File Format

The layout of a PTR file is as the following:

PPTReport Version 1.0
[Data Source]
......
[File]
......
[Parameter]
......
[SQL]
......

"PPTReport" is the flag of PTR file. "Version 2.0" is the version of the PTR file.

a PTR file contains several sections. The sections of [Data Source], [File], and [Parameter] consist of a group of related settings. The sections and settings are listed in the PTR file in the following format:

[section name]
keyname=value

In this example, [section name] is the name of a section. The enclosing brackets ([]) are required, and the left bracket must be in the leftmost column on the screen.

The keyname=value statement defines the value of each setting. A keyname is the name of a setting. It can consist of any combination of letters and digits, and must be followed immediately by an equal sign (=). The value can be an integer, a string, or a quoted string, depending on the setting.

You can include comments in these sections. You must begin each line of a comment with a semicolon (;).

The [SQL] section consists of functions. Each function is begin with the "@" character. Syntax:

@functionno=functionname(arguments)
sqlstatement

The functionno is a label of the function.

The functionname represents a function.

The arguments for a function define various properties for the function. An argument takes the form Name="Value". The argument value can be delimited by single or double quotes.

The sqlstatement is a SQL statement.

You can use comments in [SQL] section. A comment is the "/*" characters, followed by any sequence of characters (including new lines), followed by the "*/" characters. You cannot nest comments.

[Data Source] Section

The [Data Source] section contains information how to connect to data sources.

Name1=< name1 >
Name2=< name2 >
......
Name10=< name10 >

These settings specify the names of data sources you want to connect to. Name1 specifies the name of the first data source. Name2 specifies the name of the second data source...... You can define up to 10 data sources in one PRT file. You can make a connection to a data source using an ODBC data source name or a connection string. Even if you use a connection string to make a connection, you should define a name that you can reference in functions.

User1=< username1 >
User2=< username2 >
......
User10=< username10 >

These settings specify the user names. If you use an ODBC data source name to make a connection, you should define user name and password. If you use a connection string to make a connection, PTReportCom will ignore the setting. User1 specifies the user name of the first data source. User2 specifies the user name of the second data source...... They are optional settings. If defined default user and password in ODBC data source, you may not define them.

Password1=< password1 >
Password2=< password2 >
......
Password10=< password10 >

These settings specify the user passwords. If you use an ODBC data source name to make a connection, you should define user name and password. If you use a connection string to make a connection, PTReportCom will ignore the setting. Password1 specifies the password of the first data source. Password2 specifies the password of the second data source...... They are optional settings. If defined default user and password in ODBC data source, you may not define them.

ConnectionString1=< connectionstring1 >
ConnectionString2=< connectionstring2 >
......
ConnectionString10=< connectionstring10 >

These settings specify the connection strings. If you defined a connection string, PTReportCom will make a connection to the data source using the connection string, and ignore the settings of the name, user and password. But you must define a name that you can reference in functions. ConnectionString1 specifies the connection string of the first data source. ConnectionString2 specifies the connection string of the second data source...... They are optional settings. If no connection string, PTReportCom will make a connection to data source using the ODBC data source name.

EncryptPassword =Y/N

This setting specifies how to save the passwords of the data sources. If the value is Y, passwords will be saved in an encrypted format. If the value is N, the passwords will be saved in plain text.

[FILE] Section

[FILE] section contains information about files.

ReportTemplateFileName=< templatefilename >

This setting specifies the name of the report template file. < templatefilename > value is the name and path of the report template file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the PTR file.

ReportFileName=< reportfilename >

This setting specifies the name of the report file. < reportfilename > value is the name and path of the report file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the PTR file. In < reportfilename >, you can use parameters.

ReportFileType=< reportfiletype >

This setting specifies the type of the report file. < reportfiletype > value is the name or value of the file format. For example, ppSaveAsRTF or 6. What file format PTReportCom supports is dependent on your Microsoft PowerPoint and converters installed.

LogFileName=< logfilename >

This setting specifies the name of the log file. < logfilename > value is the name and path of the log file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the PTR file. In < logfilename >, you can use parameters.

[PARAMETER] Section

[PARAMETER] section contains information about parameters.

Name1=< name1 >
Name2=< name2 >
......
Name10=< name10 >

These settings specify the names of the parameters. Name1 specify the name of the first parameter, Name2 specifies the name of the second parameter...... You can define up to 10 parameters in one PTR file.

Title1=< title1 >
Title2=< title2 >
......
Title10=< title10 >

These settings specify the titles of the parameters. Title1 specifies the title of the first parameter. Title2 specifies the title of the second parameter......

Default1=< default1 >
Default2=< default2 >
......
Default10=< default10 >

These settings specify the default values of the parameters. Default1 specifies the default value of the first parameter. Default2 specifies the default value of the second parameter......

Function Reference

Fixed Table Report

Uses FixTableReport method to generate a fixed table report. In a fixed table report, the number of rows and columns is fixed. PTReportCom executes a SQL statement to get data from data source, and directly fills data into the cells of a table.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "fix"
SLIDE = slide
TABLE = table
FILLORDER = fillorder
CELL= celllist
RANGE = range
IMAGE = fieldlist
CONNECT = datasource

The TYPE argument specifies the report type. "fix" means a fixed table report.

The SLIDE argument identifies a slide in the report template. The slide is the index number of the slide. The index number starts at 1. If the index number is less than 0, it represents the position from the end of presentation. For examples, slide 2 is the second slide in a presentation, slide -1 is the last slide in a presentation. You can also reference a slide dynamically. "N" means the next slide. "N-1" means the last slide that the previous function processed.

The TABLE argument identifies a table in a slide for a table report. The table is the index number of the table in a slide. The index number starts at 1. For examples, table 1 is the first table in a slide, table 2 is the second table. Default is 1.

The FILLORDER argument specifies the order in which PTReportCom fill data. Possible values are row or col. "row" means to fill data by rows, and "col" means to fill data by columns. Default is row.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the celllist should correspond to the data source fields in the SQL statement. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ...... PTReportCom will use the next cell if you omit a cell except the first cell. If FILLORDER="row", the next cell is the right cell. If FILLORDER="col", the next cell is the below cell.

The RANGE argument specifies the range in the table to be used for the records. PTReportCom will skip the range for each record. You can reference a range of cells like "2:4" or "B:D". The default range is the area that includes all cells for the records.

The IMAGE argument specifies the data source fields are picture files. The fieldlist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the report template file.

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the PTR file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Fixed Table Report function to make the report "Top 5 Employees for Sales".

@F1=REPORT(slide=5 type=fix cell=B2)
SELECT TOP 5 e.FirstName + ' ' + e.LastName
	, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount)) AS SalesAmount
, SalesAmount / (SELECT amount FROM tmp_amount)
FROM Orders o
	,OrderDetails d
	,Products p
	,Employees e
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.EmployeeID = e.EmployeeID
AND YEAR(o.OrderDate) = 1996
AND MONTH(o.OrderDate) = 04
GROUP BY e.FirstName, e.LastName
ORDER BY 3 DESC

Variable Table Report

Uses VarTableReport method to generate a variable table report. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. PTReportCom executes a SQL statement to get data from data source, inserts some blank rows/columns or insert new slide for some records, then fills data into the cells of a table.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
SLIDE = slide
TABLE = table
FILLORDER = fillorder
CELL= celllist
RANGE = range
IMAGE = fieldlist
RESERVE = reserverecords
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "var" means a variable table report. Default is var.

The RANGE argument specifies the range in the table to be used for the records. A range is composed of some rows or columns. You can reference a range of cells like "2:4" or "B:D". PTReportCom will insert some rows/columns for each record, or copy slides for some records. If the length of the range is 1 row/column, you need to reserve 1 or 2 rows/columns in one slide. Otherwise you must reserve all blank rows/columns for records in one slide. The default range is the area that includes all cells for the records.

The IMAGE argument specifies the fields are picture files. The fieldlist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the report template file.

The RESERVE argument specifies the number of the records for which you reserved some rows/columns in the report template for the report. The reserverecords represents the number of the records you reserved in the report template. One means you reserved some rows/columns for one record, and two means some rows/columns for two records. Default is 1. If the length of the range is 1 row/column, you need to reserve 1 or 2 rows/columns in one slide. Otherwise the value of RESERVE must be equal to the value of PAGEBREAK.

The PAGEBREAK argument specifies the page breaks, and tells PTReportCom to insert new pages in the report. One page is one slide. The unit of page length is r that means record. For example, "6r" or "6" means that PTReportCom will put 6 records per slide. Default is no page break. If the length of the range is more than 1, the value of PAGEBREAK must be equal to the value of RESERVE.

The NODATA argument specifies an option when no data are returned from data source. If the value is "delrange", PTReportCom will delete the range when no data are returned. Default is to do nothing.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Variable Table Report function to make the report "Customer List".

@F1=Report(slide=2 type=var cell=A2 
pagebreak=19 reserve=2)
SELECT CompanyName
	,CityName
	,CountryName
	,ContactName
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName,CityName,CountryName

Group Table Report

Uses GroupTableReport method to generate a variable table report and group data. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. PTReportCom executes a SQL statement to get data from data source, copy the group range for each group, copy the detail range for each record, then fills data into the table.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
SLIDE = slide
TABLE = table
FILLORDER = fillorder
CELL= celllist
RANGE = range
GROUP= grouplist
GROUPRANGE = grouprange
IMAGE = fieldlist
RESERVE = reserverecords
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "var" means a variable table report. Default is var.

The RANGE argument specifies the range in the table to be used for the details. A range is composed of some rows or columns. You can reference a range of cells like "2:4" or "B:D". PTReportCom will insert some rows/columns for each record, or copy slides for some records. If the length of the range is 1 row/column, you need to reserve 1 or 2 rows/columns in one slide. Otherwise you must reserve all blank rows/columns for records in one slide. The default range is the area that includes all cells for the records.

The GROUP argument specifies the group of the report. The grouplist is the list of data source fields separated by the "," character. You can identify a field using the name or index number of the field, but not simultaneously. In one report, there may be up to 10 groups. Notes: the order of the groups should be in accordance with the order of the ORDER BY clause in the SQL statement.

The GROUPRANGE argument follows the GROUP argument, and specifies the range of the group in the table. For example, the grouprange of level 1 must follow the group of level 1, and the grouprange of level 2 must follow the group of level 2. PTReportCom will repeat the group range for each group. The range of the group should contain the range of the details and the area that includes all cells for this group. You reference a group range like "2:4" or "B:D". For example, there are two groups, the range of the group one contains all cells for the group one and the range of the group two, and the range of the group two contains all cells for the group two and the range of the details. The default range is the area that includes all cells for this group and the range or group range for the lower level group. If the grouprange is not same as the range of the detail, you must add a pagebreak by group, and the length of the range can not be more then 1 row/column.

The PAGEBREAK argument specifies the page breaks, and tells PTReportCom to insert new pages in the report. One page is one slide. The unit of page length is r or g. "r" means record, "g1" means group one, "g2" means group two...... For example, "6r" or "6" means that PTReportCom will put 6 records per slide, "1g" means one group per slide, and "1g,6r" means one group or 6 records per slide. Default is "" that means no page break. If the grouprange is not same as the range of the detail, you must add a pagebreak by group, and the length of the range can not be more then 1 row/column. If the grouprange is same as the range of the detail, and the length of the range is more than 1, you should add a pagebreak by record, and the value of PAGEBREAK must be equal to the value of RESERVE.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Group Table Report function to make the report "Customer Profile".

@F1=Report(slide=2 cell=A2,B3,C3,D3,D4,E3,E4,E5 
range=2:5 group=1 pagebreak=5 reserve=5)
SELECT LEFT(CompanyName,1)
	,CompanyName
	,ContactName
	,'Phone: ' & Phone
	,'Fax: ' & Fax
	,Address
	,CityName & ', ' & CountryName
	,PostalCode
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName

Form Report

Uses FormReport method to generate a form report and group data. For a form report, you can put data from data source into shapes or text boxes in the report file. PTReportCom gets data from a recordset object, copy the slide for each record.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "form"
SLIDE= slide
CELL= celllist
GROUP= grouplist
IMAGE = fieldlist
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "form" means a form.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of shapes or text boxes separated by the "," character. For example, "ProductName, ProductID, QuantityPerUnit, UnitPrice". The shapes or text boxes in the celllist should correspond to the data source fields in the SQL statement. The value of the first data source field is put into the first object as a text, and the value of the second data source field is put into the second object......You can get the name of the shape or text box using the add-in "name.ppa".

The GROUP argument specifies the group of the report. The grouplist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In one report, there may be up to 10 groups. The first GROUP is group one, the second is group two...... Notes: the order of groups should be in accordance with the order of ORDER BY clause in the SQL statement.

The sqlstatement is a SQL statement such as a SELECT statement.

Remarks

In FormReport method, there is no Range and PageBreak. It will put only one record per slide.

Example

This example uses Form Report function to make the report "Supplier Profile".

@F1=Report(slide=2 type=form cell=SlideTitle,Company,ContactName
,ContactTitle,Address,City,Country,PostCode,Phone,Fax,HomePage)
SELECT CompanyName
,CompanyName
,ContactName
,ContactTitle
,Address
,CityName
,CountryName
,PostalCode
,Phone
,Fax
,HomePage
FROM Suppliers, Countries, Cities
WHERE Suppliers.CityCode = Cities.CityCode
AND Suppliers.CountryCode = Cities.CountryCode
AND Suppliers.CountryCode = Countries.CountryCode
ORDER BY CompanyName

MSGraph Chart

Uses MSGraphChart method to generate a chart. PTReportCom gets data from a recordset object, and fills data into the datasheet of a chart in the report file.

Syntax

Chart(...)
sqlstatement

Arguments

SLIDE = slide
CHART = chart
FILLORDER = fillorder
CELL= celllist
RANGE = range
CONNECT = datasource

The CHART argument identifies a chart in the report template. The chart is the index number of the chart in the slide. The index number starts at 1. For examples, chart 2 is the second chart in the slide.

The RANGE argument specifies the range in the datasheet of the chart to be used for the records. PTReportCom will skip the rows/columns of the range for each record. A range is composed of some rows or columns. You can reference a range of cells like "2:4" or "B:D". The default range is the area that includes all cells for the records.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Chart function to make the chart "Sales by Categories".

@F3_3=CHART(slide=3 cell=A0)
SELECT c.CategoryName
	, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
	,OrderDetails d
	,Products p
	,Categories c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND YEAR(o.OrderDate) = 1996
AND MONTH(o.OrderDate) = 04
GROUP BY c.CategoryName
ORDER BY c.CategoryName

Excel Chart

Uses ExcelChart method to generate a chart. PTReportCom gets data from a recordset object, and fills data into the worksheet of a chart in the report file.

Syntax

Chart(...)
sqlstatement

Arguments

SLIDE = slide
CHART = chart
TYPE = type
FILLORDER = fillorder
CELL= celllist
RANGE = range
CONNECT = datasource

The TYPE argument specifies the report type. Possible values are fix or var. "fix" means that PTReportCom will directly fill data vales into the worksheet of the chart. "var" means that PTReportCom will add some blank rows/columns before filling data values into the worksheet of the chart. Default is var. When the report type is "var", you should reserve two rows/columns in the worksheet in the report template, and set the data range of the chart to 2 rows/columns. The RESERVE must be 2.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Chart function to make the chart "Sales by Categories".

@F3_3=CHART(slide=3 cell=A2)
SELECT c.CategoryName
	, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
	,OrderDetails d
	,Products p
	,Categories c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND YEAR(o.OrderDate) = 1996
AND MONTH(o.OrderDate) = 04
GROUP BY c.CategoryName
ORDER BY c.CategoryName

ExecSQL

Executes a SQL statement, but no data is returned to the report.

Syntax

ExecSQL(...)
sqlstatement

Arguments

CONNECT= datasource

The sqlstatement is a SQL statement that can be DDL (Data Definition Language), DML (Data Manipulation Language) and even DCL (Data Control Language).

Using EXECSQL function, you can open a database, create a temporary table, insert data into a temporary table, update data, execute a stored procedure, and drop a table. It is very useful to create a temporary table, and prepare data for REPORT function.

Example

This example uses ExecSQL functions to create a table tmp0, and add some records into the table. No result is returned to the report.

@F1=EXECSQL()
CREATE TABLE tmp0 (
min_date DATE,
max_date DATE)
;
@F2=EXECSQL()
INSERT INTO tmp0 
SELECT ...