Report Generator for Microsoft PowerPoint

Chapter 6 Function Reference

Report Function

The REPORT function can execute a SQL statement to get data from data source, and put data into the slides in the report file. The REPORT function can make four types of reports:

Fixed table report
Non-group variable table report
Group variable table report
Form report

Fixed Table Report Function

In a fixed table report, the number of rows and columns is fixed. PTReportGen 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 PTReportGen 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 ...... PTReportGen 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. PTReportGen 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

The following function makes the report: Top 5 Employees for Sales.

@F1=REPORT(table=6 type=fix cell=B2)
@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

Result

The fixed table report defined in the report template:

PPT Report Template - Top N Employees for Sales

The fixed table report generated in the report:

PPT Report Sample - Top N Employees for Sales

Remarks

1. The SQL statement will get the information of top 5 employees for sales, including employee name, quantity of products, sales amount, and percent of Total.

2. type="fix". It is a fixed table report.

3. slide = 5. It means the fifth slide.

4. The default table is 1. PTReportGen will put data into the first table in the sixth slide in the report file.

5. cell=B2. The cells corresponding to the first record are "B2,C2,D2,E2".

6. The default range is "B2:E2".

7. PTReportGen executes the SQL statement, and gets data from data source. First, it fetches the first record, puts the value of the first field into cell B2, the value of the second field into cell C2, the value of the third field into D2, and the value of the fourth field into E2. And then it fetches the next record, skips one row, and puts data into cells B3, C3, D3 and E3......

Non-group Variable Table Report Function

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. PTReportGen 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". PTReportGen 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 PTReportGen 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 PTReportGen 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", PTReportGen will delete the range when no data are returned. "deltable" means to delete the table. "delslide" means to delete the slide. Default is to do nothing.

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

Example

The following function will makes 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

Result

The non-group variable table report defined in the report template:

The non-group variable table report generated in the report:

Remarks

1. The SQL statement will get the information of customers including customer name, city name, country name, and contact name.

2. type="var". It is a variable table report. And there is no GROUP argument, so it is a non-group variable table report.

3. slide=2. It is the second slide.

4. The default table is 1. PTReportGen will put data into the first table in the second slide in the report file.

5. cell=A2. The cells corresponding to the first record are "A2,B2,C2,D2".

6. The default range is "A2:D2".

7. pagebreak = 19. PTReportGen will add a new slide per 19 records. One slide contains 19 records.

8. reserve = 2. You have reserved 2 rows for 2 records in the table in the report template file.

9. PTReportGen executes the SQL statement, and gets data from data source. First, it fetches the first record, and fills data. And then it fetches the next record...... PTReportGen will add some rows after the number of records exceeds the number of rows you have reserved, and add a new slide per 19 records. One slide contains 19 records.

Group Variable Table Report Function

The Group Variable Table Report function generates 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. PTReportGen 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". PTReportGen 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. PTReportGen 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 PTReportGen 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 PTReportGen will put 6 records per slide, "1g" means one group per slide, and "1g,6r" means one group or 6 records per slide. Default PTReportGen will not show the group name in the new page. You can add "s" to show them. For example, "1gs,6rs". 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

The following function will makes 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

Result

The group variable table report defined in the report template:

The group variable table report generated in the report:

Remarks

1. The SQL statement will get the information of customers including company name, contact name, phone, fax, address, city name, country name, and postal code.

2. There is GROUP argument, so it is a group variable table report.

3. slide=2. It is the second slide.

4. The default table is 1. PTReportGen will put data into the first table in the second slide in the report file.

5. group=1. PTReportGen will group data by the first letter of the company name.

6. cell= A2,B3,C3,D3,D4,E3,E4,E5. These cells correspond to the first record.

7. range=2:5. Because the default range is "B3:E5", you must specify a range explicitly.

8. There is no grouprange. PTReportGen will give a default. The default grouprange is "2:5".

9. pagebreak = 5. PTReportGen will add a new slide per 5 records.

10. reserve = 5. Because the grouprange is same as the range of the detail, and the length of the range is more than 1, you must reserve some rows for 5 records.

11. PTReportGen executes the SQL statement, gets data from data source, and puts data into the table in the report file. PTReportGen will add a new slide per 5 records. One slide contains the information of 5 records.

Form Report Function

For a form report, you can put data from data source into shapes or text boxes in the report file. PTReportGen executes a SQL statement to get data from data source, and copy the slide for each record, then fills data into the slides.

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

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 NODATA argument specifies an option when no data are returned from data source. If the value is "delrange" or "delslide", PTReportGen will delete the slide when no data are returned. Default is to do nothing.

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

The following function will makes 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

Result

The form report defined in the report template:

The form report generated in the report:

Remarks

1. The SQL statement will get the information of suppliers including company name, contact name, contact title, address, city, country, postal code, phone, fax, home page.

2. type="form". It is a form report.

3. slide=2. It is the second slide.

4. cell=SlideTitle, Company, ContactName, ContactTitle, Address, City, Country, PostCode, Phone, Fax, HomePage. These shapes or text boxes are in the second slide, and correspond to data source fields in the SQL statement.

5. PTReportGen executes the SQL statement, and gets data from data source. It fetches records, copy the slide for each record, and inserts data into the shapes or text boxes.

Chart Function

The CHART function executes a SELECT statement to get data from data source, and put data into the datasheet of a chart in the report file. The CHART function supports two types of charts:

MSGraph chart
Excel chart

MSGraph Chart Function

If you use Microsoft Graph to create a chart, you can use MSGraph Chart function.

Syntax

Chart(...)
sqlstatement

Arguments

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

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

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells or fields separated by the "," character. The celllist identifies the cells in a datasheet. 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 ...... PTReportGen 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. Note: On the datasheet, the leftmost column and the top row, which are commonly used for legend text or axis labels, are referred to as column 0 (zero) and row 0 (zero).

The RANGE argument specifies the range in the datasheet of the chart to be used for the records. PTReportGen 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 details.

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

Example

The following function makes 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

Result

The datasheet of the chart defined in the report template:

PPT Report Template - Sales by Categories

The chart defined in the report template is a blank chart.

The datasheet of the chart generated in the report:

The chart generated in the report:

Remarks

1. The SQL statement will get the information of sales by categories, including category name, and sales amount.

2. slide = 3. It means the third slide.

3. The default chart is 1. PTReportGen will put data into the first chart in the third slide in the report file.

4. The default fillorder is col. PTReportGen will fill data by columns.

5. cell=A0. The cells corresponding to the first record are "A0,A1".

6. The default range is "A:A".

7. PTReportGen executes the SQL statement, and gets data from data source. First, it fetches the first record, puts the value of the first field into cell A0, the value of the second field into cell A1. And then it fetches the next record, skips one cplumn, and puts data into cells B0, B1......

Excel Chart Function

If you use Microsoft Excel to create a chart, you can use Excel Chart function.

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 PTReportGen will directly fill data vales into the worksheet of the chart. "var" means that PTReportGen 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 CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells or fields separated by the "," character. The celllist identifies the cells in a worksheet. 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 ...... PTReportGen 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 worksheet of the chart to be used for the records. PTReportGen 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 details.

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

Example

The following function makes 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

Result

The worksheet of the chart defined in the report template:

The chart defined in the report template is a blank chart.

The worksheet of the chart generated in the report:

The chart generated in the report:

Remarks

1. The SQL statement will get the information of sales by categories, including category name, and sales amount.

2. slide = 3. It means the third slide.

3. The default chart is 1. PTReportGen will put data into the first chart in the third slide in the report file.

4. The default type is var. You should reserve 2 blank rows in the worksheet, and select the 2 rows as the data range of the chart.

5. The default fillorder is row. PTReportGen will fill data by rows.

6. cell=A2. The cells corresponding to the first record are "A2,B2".

7. The default range is "2:2".

8. PTReportGen executes the SQL statement, and gets data from data source. First, it will add some blank rows in the worksheet according to the number of the records. And then it will put data into the worksheet.

ExecSQL Function

The EXECSQL function executes a SQL statement, but does not return result to 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

The following functions will create a table tmp0, and add some records into table. No result is returned to the PPT Report file.

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