Creating and Opening PTR Files

About PTR files

To generate a report with PTReportGen, you must create a PTR file with a .ptr extension. The PTR file contains information such as the name of the report template file, the name of the report file, the log file name, data sources, parameters and functions. The PTR file tells PTReportGen how to get data from data sources and how to put data into a report.

Create a new PTR file

On the File menu, click New.

Open a PTR file

1. On the File menu, click Open.

2. In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.

3. In the folder list, locate and open the folder that contains the file.

4. Click the file, and then press Open button.

Save a PTR file

On the File menu, click Save. If you're saving the file for the first time, you'll be asked to give it a name.

If you want save a file to another name, do as follows:

1. On the File menu, click Save As.

2. In the File name box, enter a new name for the file.

3. Press Save button.

Configuring Files

About files

You should specify the report template file, report file, report file type and log file. The report template file defines layouts, formats and styles of the report. The report file is the report you want to generate. The type of the report file can be different from the template file. The log file records the log information in the report generating.

Configuring file information

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the File tab.

3. Input the path and name of the template file, the report file and the log file into their text box.

4. In the File Type box, click the file type you want. If the file type of the report is same as the template file, click the (Default) in the File Type box.

5. Press OK button to confirm the changes, press Cancel button to discard the changes.

Converting files

You can convert a file from Microsoft PowerPoint presentation to and from another file format. For example, the template file is a RTF file with a .rtf extension, and the report file is a HTML file with a .htm extension.

The file formats PTReportGen supports can be one of these. What file format PTReportGen 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 PTReportGen directory contains the information of file formats. You can expand it if your Microsoft PowerPoint supports more file formats.

Configuring Data Sources

About data sources

A data source identifies a database computer you want to access. Because of accessing data through ODBC, PTReportGen can access a wide range of data sources, such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Teradata, MySQL, Microsoft Access, dBase. PTReportGen supports more than one data sources in one report. You can get data from some different databases such as Oracle, DB2 and Microsoft SQL Server, and put them into one report.

Adding, modifying and deleting a data source

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the Data Source tab.

3. If you want to add a data source, press New button, the New Data Source dialog box appears. Input data source name, user name and password, press OK button.

• To define a connection using an ODBC data source name, click Using ODBC data source name option, input data source name, user name and password, press OK button.
• To define a connection using a connection string, click Using connection string option, input data source name, and connection string, press OK button.

4. If you want to modify a data source, click the data source name in the Data Source list box, and press Edit button, the Edit Data Source dialog box appears. Change data source name, user name and password, press OK button.

• To define a connection using an ODBC data source name, click Using ODBC data source name option, change data source name, user name and password, press OK button.
• To define a connection using a connection string, click Using connection string option, change data source name, and connection string, press OK button.

5. If you want to delete a data source, click the data source name in the Data Source list box, and press Delete button, the confirmation dialog box appears. Press Yes button to delete the data source.

6. You can test a data source. Click the data source name in the Data Source list box, and Press Test button to display the information of connection to the data source.

7. Select or clear the Encrypt Password check box. If the check box is selected, passwords will be saved in an encrypted format. Or passwords will be saved in plain text.

8. Press OK button to confirm the changes, press Cancel button to discard the changes.

Configuring Parameters

About parameters

You can use parameters in SQL statements. These values need to be provided to PTReportGen before it executes these SQL statements. To use a parameter, you must declare it first. When PTReportGen generate a report, it will prompt you to input the value of the parameter. PTReportGen will replace the parameter name in the SQL statements with the actual value before it submits the SQL statements to data sources.

A parameter has a name, a title and a default value. The name of a parameter identifies the parameter. You can use the names in SQL statements. The titles will be displayed in the prompt dialog box when PTReportGen is run.

Note: PTReportGen 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. For example, you give the name "$ReportDate" for a parameter. Parameters are case-sensitive.

Adding, modifying and deleting a parameter

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the Parameter tab.

3. If you want to add a parameter, press New button, the New Parameter dialog box appears. Input parameter name, parameter title and default value, press OK button.

4. If you want to modify a parameter, click the parameter name in the Parameter list box, and press Edit button, the Edit Parameter dialog box appears. Change the name, title and default value of the parameter, press OK button.

5. If you want to delete a parameter, click the parameter name in the Parameter list box, and press Delete button, the confirmation dialog box appears. Press Yes button to delete the parameter.

6. Press OK button to confirm the changes, press Cancel button to discard the changes.

Inputting Functions

You should input functions in the editor window. A function includes a SQL statement and some arguments. PTReportGen executes the SQL statement, and determines whether or how to add data into the report. PTReportGen sequentially executes functions.

Each function is begin with the "@" character. Syntax:

@functionno=functionname(arguments)
sqlstatement

The functionno is a label of the report function.

The functionname represents a report function.

The arguments for a function define various properties for the function. For example, the "slide" argument identifies a slide in the Microsoft PowerPoint presentation. An argument takes the form Name="Value". The argument value can be delimited by single or double quotes.

The sqlstatement is a SQL statement.

Running PTR Files

You can run a PTR file to generate a report in Microsoft PowerPoint format. PTReportGen supports Windows mode and command line mode.

Windows mode

1. On the Report menu, click Run, the Run Report dialog box appears.

2. If you want to display the generated report, select the Display Report with Microsoft PowerPoint check box.

3. Press Start button to run the PTR file.

4. If parameters are defined in the PTR file, PTReportGen will pop up a prompt dialog box. Input the values of the parameters, and press OK button.

5. While PTReportGen is being run, it will display some information such as status, SQL count, error count, function No., records count and log information.

6. You can interrupt the running. Click End button to interrupt it. PTReportGen will immediately save and close the report.

7. Click Close button after completion.

8. If you want to open the report, click Open Report File on the File menu.

9. If you want to check the log, click Open Log File on the File menu.

Command line mode

You can run a PTR file in command line. You have defined two parameters in the PTR file "myreport.ptr". The first parameter is sales date "$SalesDate", and the second is the category of the products "$Category". You can run PTReportGen in command line mode as follows:

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

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

Sorting, Grouping and Totaling

Sorting data

Sorting means placing data in some kind of order to help you find and evaluate it. For example, you may want to have a customer list sorted alphabetically by name or by country.

To sort your data, you can use SQL. Use the ORDER BY clause to have your results displayed in a sorted order.

SELECT EmployeeID
,LastName
,FirstName
,HireDate
FROM Employees
ORDER BY HireDate;	/* ascending sort */

In the example above, results will come back in ascending order by hire date. To explicitly specify ascending or descending order, add ASC or DESC, to the end of your ORDER BY clause. The following is an example of a descending order sort.

ORDER BY HireDate DESC;	/* descending sort */

Totaling

You can sum the values, count all the values or only those values that are distinct from one another, and determine the maximum, minimum, average. To add totals, you can use aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, MIN.

1. In the fixed table report, you can add total directly using a separate SQL.

2. In the variable table report, you must add total first using a Fixed Table report function before you use the Variable Table report function. Because the cell address of the total field will change after you use Variable Table report function.

Grouping data and subreports

Grouped data is data that is sorted and broken up into meaningful groups. In a customer list, for example, a group might consist of all those customers living in the same Region.

Subtotaling

A subtotal is a summary that totals or sums numeric values in a group. You can sum the values in each group, count all the values in each group, and determine the maximum, minimum, average in each group. For example, determine the total sales per sales representative in a sales report.

To add subtotals, you can use aggregate function in SQL statement.

1. Use aggregate function and GROUP BY clause, get summary data for each group, and insert results into a temporary table.
2. If you have different kinds of summaries, repeat the step 1, and insert results into another temporary table.
3. Use group report function, and join the detail data and the summary data using JOIN. The summary fields must be included in the group list.
4. Except for sub-totals, you can add total too using aggregate function in SQL statement. You must add total first using a Fixed Table report function before you use the Variable Table report function. Because the cell address of the total field will change after you use Variable Table report function.

For more information, refer to the samples sales_detail.ptr within PTReportGen.

Pictures

Inserting pictures into a report template

To make eye-catching reports, you can add pictures to your reports. You can insert pictures into the report template directly in Microsoft PowerPoint. For example, you want to display a logo in your report. You can insert the logo graphics file into the report template. For more information about adding pictures to a presentation, refer to Microsoft PowerPoint Help.

Inserting pictures into a report

Except for inserting the static pictures during report design, you want to insert pictures during report building process. You hope a reporting tool to pull pictures from database into PowerPoint report. PTReportGen can insert pictures from the graphics files, and support all graphics file format that Microsoft PowerPoint support.

To insert pictures into a report using PTReportGen, you should do as follows:

1. Store the path and name of the graphics files in the database

You stored the path and file name of the pictures in database, did not store the pictures. The file path can be a relative path, an absolute path or a URL. For example, you store "images\emp1.jpg" in Photo field.

2. Identify the image fields in the report function

Write a report function in the PTR file, and identify the image fields using IMAGE argument. For example,

@F1=Report(slide=1 ... image=photo)

3. Specify the size in the report template

To specify the size, you should write a formatting expression in the report template file. For a table report, you write a formatting expression in the cell. PTReportGen will get the formatting expression, and insert a picture into the report according to the instruction in the format expression.

4. Run PTReportGen to generate report with pictures

During report generating process, PTReportGen will read the graphics files, and insert them into the report according to your instruction. If the path and file name of the picture is "", PTReportGen will return "". PTReportGen will return "#Error" if it does not find the file of the picture.

For more detail information about pictures, refer to the samples employee_profile.ptr, product_catalog.ptr within PTReportGen.

Using Parameters

To use a parameter, you must define it first. If you have defined a parameter name, you can use it in SQL statements. When PTReportGen is run, it will replace the parameter name in the SQL statements with the actual value before it submits the SQL statements to data sources. Besides in SQL statements, you can use parameters in the paths and names of report file and log file.

In fact, PTReportGen 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 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 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.ppt
LogFileName=log\report_$CustomerID.log
or
ReportFileName=report\$CustomerID\report.ppt
LogFileName=log\$CustomerID\report.log

Programming

Using add-ins, macros

In Microsoft PowerPoint, you can automate a task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task.

You can write macros in the report template file, and can use automatic macros, such as Auto_Open, Auto_Close to automate a task. For examples, you can use Auto_Open macro to make the template, or use Auto_Close to change the report after PTReportGen puts data into the report.

Sometimes you want to make a PTR file programmatically. You can do this because the PTR file is a text file. You can write a program to make a PTR file using C, Perl or DOS shell, and then run PTReportGen 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 PTReportGen in command line mode.

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 PTReportGen 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" -C %1