Ljzsoft PTREPORTCOM User Manual

PTReportCom

User Manual

Version 1.6

2009-08

Copyright© 2007-2009 LJZsoft Corporation

All rights reserved

Contents

CHAPTER 1 INTRODUCTION...........................................................................................................1

1.1 OVERVIEW......................................................................................................................................1

1.2 FEATURES.......................................................................................................................................1

CHAPTER 2 INSTALLATION AND STARTUP...............................................................................3

2.1 SOFTWARE REQUIREMENTS............................................................................................................3

2.2 INSTALLING PTREPORTCOM ..........................................................................................................3

2.3 UNINSTALLING PTREPORTCOM .....................................................................................................3

2.4 PTREPORTCOM.DLL.......................................................................................................................4

2.5 PPTREPORT.EXE ............................................................................................................................5

2.6 RUN-TIME FILES.............................................................................................................................6

CHAPTER 3 QUICK START...............................................................................................................7

3.1 LEARNING HOW TO USE PTREPORTCOM.........................................................................................7

3.2 SAMPLE DATABASE ........................................................................................................................7

3.3 SAMPLES.........................................................................................................................................8

3.4 CREATING A REPORT PROGRAMMATICALLY...................................................................................9

3.5 CREATING A REPORT WITH PPTREPORT.EXE................................................................................10

CHAPTER 4 REPORT TEMPLATES ..............................................................................................12

4.1 ABOUT REPORTS .......................................................................................................................... 12

4.2 ABOUT REPORT TEMPLATES.........................................................................................................12

4.3 POWERPOINT BASIC CONCEPTS....................................................................................................12

4.3.1 Presentations........................................................................................................................12

4.3.2 Slides....................................................................................................................................13

4.3.3 Layout..................................................................................................................................13

4.3.4 Headers and Footers............................................................................................................13

- I -

4.3.5 Formatting...........................................................................................................................13

4.3.6 Shapes..................................................................................................................................14

4.3.7 Pictures................................................................................................................................14

4.3.8 Tables...................................................................................................................................15

4.3.9 Charts...................................................................................................................................15

4.3.10 Sound, Music, Video, and Voice.........................................................................................15

4.4 TABLE REPORTS ...........................................................................................................................16

4.4.1 About Table Reports.............................................................................................................16

4.4.2 Creating a Table for a Fixed Table Report..........................................................................17

4.4.3 Creating a Table for a Variable Table Report.....................................................................17

4.4.4 Formatting Cells..................................................................................................................18

4.4.5 Irregular Tables...................................................................................................................20

4.4.6 Referencing Cells.................................................................................................................21

4.4.7 Referencing Tables...............................................................................................................21

4.4.8 Formatting Cells for Pictures..............................................................................................22

4.5 FORM REPORTS.............................................................................................................................23

4.5.1 About Form Reports.............................................................................................................23

4.5.2 Creating a Slide ...................................................................................................................23

4.5.3 Naming Objects....................................................................................................................24

4.5.4 Formatting text in an Object................................................................................................26

4.5.5 Formatting Objects for Pictures ..........................................................................................27

4.6 CHARTS ........................................................................................................................................28

4.6.1 About Charts........................................................................................................................28

4.6.2 Creating a Blank Chart using Microsoft Graph..................................................................28

4.6.3 Creating a Blank Chart using Microsoft Excel....................................................................29

4.6.4 Referencing Charts ..............................................................................................................30

CHAPTER 5 API REFERENCE........................................................................................................32

5.1 OBJECTS .......................................................................................................................................32

- II -

5.1.1 PTReport Object ..................................................................................................................32

5.2 METHODS.....................................................................................................................................32

5.2.1 FixTableReport Method.......................................................................................................32

5.2.2 VarTableReport Method ......................................................................................................35

5.2.3 GroupTableReport Method..................................................................................................39

5.2.4 FormReport Method.............................................................................................................44

5.2.5 MSGraphChart Method.......................................................................................................48

5.2.6 ExcelChart Method..............................................................................................................51

5.2.7 PPTReport Method .............................................................................................................. 54

5.2.8 GetSlideByIndex Method......................................................................................................55

5.2.9 GetTableInSlide Method......................................................................................................56

5.2.10 GetChartInSlide Method....................................................................................................56

5.3 EVENTS ........................................................................................................................................57

5.3.1 BeforeConnect Event............................................................................................................57

5.3.2 TemplateOpen Event............................................................................................................58

5.3.3 ReportComplete Event.........................................................................................................59

5.3.4 FunctionBeforeExectue Event..............................................................................................60

5.3.5 FunctionAfterExectue Event.................................................................................................61

5.3.6 FunctionProgress Event.......................................................................................................62

5.4 ERROR MESSAGES........................................................................................................................62

CHAPTER 6 PTR FILES....................................................................................................................65

6.1 USING PTR FILES .........................................................................................................................65

6.1.1 About PTR files....................................................................................................................65

6.1.2 Using a PTR file with PTReport Object...............................................................................65

6.1.3 Using a PTR file in command line .......................................................................................66

6.1.4 Creating a PTR file..............................................................................................................66

6.1.5 Using parameters.................................................................................................................67

6.1.6 Converting files....................................................................................................................70

- III -

6.2 PTR FILE REFERENCE ..................................................................................................................73

6.2.1 PTR File Format..................................................................................................................73

6.2.2 [Data Source] Section..........................................................................................................74

6.2.3 [FILE] Section.....................................................................................................................76

6.2.4 [PARAMETER] Section.......................................................................................................77

6.3 FUNCTION REFERENCE .................................................................................................................78

6.3.1 Fixed Table Report...............................................................................................................78

6.3.2 Variable Table Report..........................................................................................................81

6.3.3 Group Table Report.............................................................................................................84

6.3.4 Form Report.........................................................................................................................89

6.3.5 MSGraph Chart ...................................................................................................................92

6.3.6 ExcelChart ...........................................................................................................................94

6.3.7 ExecSQL...............................................................................................................................96

CHAPTER 7 ADVANCED REPORTS..............................................................................................98

7.1 EXECUTING MULTIPLE SQL STATEMENTS ....................................................................................98

7.2 SORTING, GROUPING AND TOTALING.........................................................................................103

7.2.1 Sorting data........................................................................................................................103

7.2.2 Totaling..............................................................................................................................104

7.2.3 Grouping data and Subreports...........................................................................................104

7.2.4 Subtotaling.........................................................................................................................105

7.3 PICTURES....................................................................................................................................106

7.3.1 Inserting pictures into a report template............................................................................106

7.3.2 Inserting pictures into a report..........................................................................................106

CHAPTER 8 HINTS AND TIPS.......................................................................................................108

CHAPTER 9 FORMAT EXPRESSIONS........................................................................................113

A.1 FORMATS FOR NUMERIC VALUES..............................................................................................113

A.2 FORMATS FOR STRING VALUES .................................................................................................117

- IV -

A.3 FORMATS FOR DATE/TIME VALUES...........................................................................................118

CHAPTER 10 LICENSE AND SUPPORT......................................................................................122

10.1 LICENSE....................................................................................................................................122

10.2 TECHNICAL SUPPORT................................................................................................................123

- V -

Chapter 1 Introduction

1.1 Overview

PTReportCom is a solution that generates reports using Microsoft PowerPoint.
Using Microsoft PowerPoint and PTReportCom, you can create all kinds of
reports quickly and easily. PTReportCom includes an ActiveX DLL and an
executable file that can be used to develop your applications. It will significantly
accelerate your application development.
PTReportCom is a template-based solution. To create a report, you need to
create a report template file first. The report template file is a Microsoft
PowerPoint presentation that defines the layouts and formats of a report.
PTReportCom retrieves data from data source and fills data into PowerPoint
presentation.

1.2 Features

PTReportCom includes the following features:
 Using Microsoft PowerPoint as your reporting tool
Just use Microsoft PowerPoint as your reporting tool. You design reports like
layouts, formats and styles directly using Microsoft PowerPoint. And you will
get reports in Microsoft PowerPoint spreadsheet format as a result. Microsoft
PowerPoint is powerful, flexible and familiar. You do not need to buy and learn
extra reporting tools.
 Making report template directly using Microsoft PowerPoint
The main advantage of using PTReportCom is based on the fact that all
formatting is done directly in Microsoft PowerPoint. You can take full
advantage of Microsoft PowerPoint including text formatting, tables, charts,

- 1 -

pictures and graphics, drawing, headers and footers, preview and printing,
VBA, macros, and more.
 Accessing to databases using SQL
PTReportCom executes SQL statements to extract data from database.
Supports all type SQL: DML, DDL and DCL. Multiple SQL statements can be
executed in one report building process. You can perform queries on
databases, insert data into databases, and create database objects like tables.
The power of SQL can be harnessed for maximum efficiency in reporting.
 Using ADO to access and manipulate data sources
Using ADO, PTReportCom can access and manipulate a wide variety of data
sources such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server,
Teradata, MySQL, Microsoft Access, dBase.
 Integrating Microsoft PowerPoint into your application
PTReportCom includes an ActiveX DLL for building application. Developers
can save time and meet their users’ needs by integrating the report processing
power of PTReportCom into their applications.
 Command line program
PTReportCom includes a command line program PPTReport.exe. You can
use the program to create reports too. It does not require programming. It is
enough if you know how to use Microsoft PowerPoint and how to write SQL.
 Various reporting capabilities
PTReportCom provides various reporting capabilities including sorting data,
grouping data, subreports, totaling and summarizing data, formatting, charting
and pictures. It is easy to create simple reports, and, you can create complex
reports.

- 2 -

Chapter 2 Installation and Startup

2.1 Software Requirements

Microsoft Windows 95, Windows 98, Windows NT, Windows 2000, Windows
XP, Windows 2003, Windows Vista or later.
Microsoft Office 97/98, Office 2000, Office XP, Office 2003 or later.

2.2 Installing PTReportCom

Run the installation program, and follow the instructions to complete
PTReportCom installation. For Windows Vista, the data folder should be
different from the application folder.

If you don’t have Microsoft Office installed, please install it first.
If your environment is Windows 95/98 and Office 97, and you don’t have VB6.0
run-time files installed, please install it. For Windows 2000, Windows XP,
Windows 2003 and Office 2000 or later, you do not need to install VB6.0
run-time files because they are included in OS and Office. To install VB6.0
run-time files, just run vbrun60sp5.exe, and follow the instructions.
If you don’t have ODBC Driver installed for the database you want to access,
please install it.
If your OS is Windows 95/98 and you don’t have Microsoft Data Access
Components 2.0 (MDAC_TYP) or later installed, please install it. For Windows
2000, Windows XP and Windows 2003, you do not need to install MDAC_TYP
because it is preinstalled in OS. To install MDAC_TYP, just run mdac_typ.exe,
and follow the instructions.

2.3 Uninstalling PTReportCom

1. Double-click the Add/Remove Programs icon in the Windows Control

- 3 -

Panel.

2. Do one of the following:
 For Windows 2000, Windows XP and Windows 2003 Edition:
Click PTReportCom in the Currently installed programs box, and then click
the Change/Remove button.
 For Windows 98 and Windows NT 4.0:
Click PTReportCom on the Install/Uninstall tab, and then click the
Add/Remove button.

3. Follow the instructions on the screen to complete uninstalling the program.

2.4 PTReportCom.dll

PTReportCom.dll is an ActiveX DLL that provides PTReport object. You can
write a program to work with the object. Before you can use the PTReport
object, you must create a reference to the object. And you should create
references to Microsoft PowerPoint and Microsoft Graph Object Library too.
To create a reference to the PTReport object

1. Do one of the following:
 For Visual Basic 6.0
From the Project menu, choose References.
 For Microsoft PowerPoint Visual Basic For Application
From the Tools menu, choose References.

2. In the References dialog box, select PTReportCom.

3. You can use the Browse button to search for PTReportCom.dll.

4. In the References dialog box, Select Microsoft PowerPoint and Microsoft
Graph Object Library to create their references.

5. Declare an object variable of the object's class.
Dim ptrpt As PTReport

6. Assign an object reference to the variable by using the New keyword in a

- 4 -

Set statement.
Set ptrpt = New PTReport

2.5 PPTReport.exe

PPTReport.exe is an executable program that developed using
PTReportCom.dll. It likes PTReportGen command line and can read a PTR file
to create a PowerPoint report. The syntax of command is:

pptreport <ptr file name> [-D] [-U1 user1] [-P1 pwd1] … [-U10 user10]
[-P10 pwd10] [pa1 pa2 … pa10]

ptr file name Specifying a PTR (.ptr) file that tells PTReportCom how to get

data from data sources and how to put data into a report.

-D Display the generated report with Microsoft PowerPoint.

-U1 user1 …

-U10 user10

-P1 pwd1 ...

-P10 pwd10
pa1 … pa10 The values of the parameters defined in the PTR file. You can

For example, you have defined two parameters in your PTR file. The first
parameter is the sales date, and the second is the category of the product. You

Specify the user names. user1 is the user name of the first
data source. user2 is the user name of the second data
source……
Specify the passwords. pwd1 is the password of the first data
source. pwd2 is the password of the second data source……

use parameters in SQL statements. PTReportCom will replace
the names of the parameters in a SQL statement with the
actual values before it executes the SQL statement. You can
use no more than 10 parameters in one report.

can run PPTReport.exe as follows:
pptreport c:\pptreport\myreport.ptr 1996-05-01 “Dairy Products”

- 5 -

2.6 Run-Time Files

You can distribute royalty-free the run-time files of PTReportCom with your
applications. The run-time files are files your application must have in order to
work correctly after installation. The following are the run-time files you need to
distribute:
File Description
ptreportcom.dll The PTReportCom ActiveX DLL. It must be registered.
pconv.cfg The file contains the information of the file format. If you

are using PPTReport method to convert files, you should
include it and copy it to the same directory as
ptreportcom.dll.

scrrun.dll Microsoft script runtime. PTReportCom used some

functions in this file. It should be copied to Windows

System directory, and must be registered.
To register a DLL file, use regsvr32.exe. For example,
regsvr32.exe /s “C:\Program Files\LJZsoft\PTReportCom\PTReportCom.dll”

- 6 -

Chapter 3 Quick Start

3.1 Learning how to use PTReportCom

You can teach yourself how to use PTReportCom by choosing from the
methods available in this section:

 You can study the samples included with PTReportCom.
 You can use the detailed descriptions and instructions in this document.

3.2 Sample Database

PTReportCom comes with Sample.mdb, a sample database you can use when
learning the program. Sample.mdb is a Microsoft Access database. Virtually all
of the examples in this manual are based on Sample.mdb data.
The sample reports access the sample database through the ODBC data
source name “Report Sample”. When you install PTReportCom, you can
choose to add the ODBC data source name. And you also can add the ODBC
data source name manually.
To create the System DSN “Report Sample”, do as follows:

1. Click the Windows Start button, choose Settings, and then click Control
Panel.

2. On computers running Microsoft Windows 2000 or later, double-click

Administrative Tools, and then double-click Data Sources (ODBC). The
ODBC Data Source Administrator dialog box appears. On computers
running previous versions of Microsoft Windows, double-click 32-bit ODBC or
ODBC.

3. Select the System DSN tab, and then press Add button.

4. Choose Microsoft Access Driver (*.mdb), then press Finish button.

5. In the ODBC Microsoft Access Setup dialog box, type Report Sample in

- 7 -

the Data Source Name box.

6. Press the Select button, and browse to select Sample.mdb.

7. Press OK button to close the ODBC Microsoft Access Setup dialog box.

8. Press OK button to close the ODBC Data Source Administrator dialog
box.

3.3 Samples

After PTReportCom is installed, some samples are installed too. Use these
samples to learn PTReportCom.
The samples include a sample database, VB sample programs, VBA sample
programs and sample reports. They are located in the Application Data\LJZsoft
under All Users or your profile folder. PTReportCom was tested with Microsoft
Office 2007. Please download the sample reports for Microsoft Office 2007
from our website.
Directory Description
{data}\Common\SampleDatabase Contains the sample database

“Sample.mdb”.

{data}\PTReportCom\Samples\PPTReport Contains the report template files

(.ppt) and the PTR files (.ptr).

{data}\PTReportCom\Samples\VB Contains the sample programs

for VB6.0.

{data}\PTReportCom\Samples\VBA Contains the sample programs

for Microsoft PowerPoint VBA.

{data} is the path of the data folder. You can select the data folder when you
install PTReportCom. By default, the data folder is the Application
Data\LJZsoft folder under All Users. If you install PTReportCom without
administrative privileges, the data folder is the Application Data\LJZsoft folder
under the current user. The data folder is usually at:

Windows 95/98: C:\windows\All Users\Application Data\LJZsoft

- 8 -

Windows NT: C:\WinNT\Profiles\All Users\Application Data\LJZsoft
Windows 2000/XP: C:\Documents and Settings\All Users\Application
Data\LJZsoft
Windows Vista: C:\ProgramData\LJZsoft

3.4 Creating a Report Programmatically

1. Create a template
In Microsoft PowerPoint, create a report template file named “custlist.ppt”.
Static values and any PowerPoint features included in the template will be
included in the generated report. The template file you have created as follows:

2. Write the code in your application.
Set con = New ADODB.Connection
Set rec = New ADODB.Recordset
con.ConnectionString = "Data Source=Report Sample"
con.Open
strSQL = "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"
rec.Open strSQL, con
ptrpt.VarTableReport Recordset:=rec, Slide:=pptSlide, Table:=1,

- 9 -

CellList:="A2", Reserve:=2, PageBreak:="12"
rec.Close

3.5 Creating a Report with PPTReport.exe

1. Create a template
In Microsoft PowerPoint, create a report template file named “custlist.ppt”.
Static values and any PowerPoint features included in the template will be
included in the generated report. The template file you have created as follows:

2. Create a PTR file
Create a PTR file named “custlist.ptr” using PTReportGen or a text editor. The
following is the content of the PTR file.

PPTReport Version 2.0
[Data Source]
Name1=Report Sample

[File]
ReportTemplateFileName=custlist.ppt
ReportFileName=Report\custlist.ppt
LogFileName=Log\custlist.log

[SQL]

- 10 -

@F1=Report(slide=1 cell=A2 pagebreak=12 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

3. Run PPTReport.exe
pptreport c:\report\custlist.ptr

- 11 -

Chapter 4 Report Templates

4.1 About Reports

The report generated by PTReportCom is a Microsoft PowerPoint presentation.
The layouts, formats and styles of the report are defined by a report template,
and the data of the report are got from databases such as Oracle, DB2.

4.2 About Report Templates

To make a report using PTReportCom, you should create a report template
first. The report template is a Microsoft PowerPoint presentation that defines
the layouts, formats and styles of the report. In the Microsoft PowerPoint report
template, you can input static content such as titles, descriptions, comments, a
cover, a company logo, format the static content, and define the format of the
data you will get from databases.
PTReportCom will generate the report based on the report template file. All
static contents and the layouts, formats and styles defined in the report
template file will be brought to the final report file.

4.3 PowerPoint Basic Concepts

If you have known these concepts of Microsoft PowerPoint, please skip this
section. For more detail information about Microsoft PowerPoint, refer to
Microsoft PowerPoint Help.

4.3.1 Presentations

A presentation is a Microsoft PowerPoint file with extension .ppt. You can open

- 12 -

and save it using Microsoft PowerPoint. The presentation is made up of a
series of slides.

4.3.2 Slides

A slide is a frame in a presentation. A presentation contains one or more slides.
Slide is the primary component that contains content.

4.3.3 Layout

Layout refers to the way things are arranged on a slide. A layout contains
placeholders, which in turn hold text such as titles and bulleted lists and slide
content such as tables, charts, pictures, shapes, and clip art. Each time you
add a new slide, you can choose a layout for it. You can also choose a blank
layout.

4.3.4 Headers and Footers

Headers and footers consist of the header and footer text, slide or page
number, and date you want at the top or bottom of your slides or notes and
handouts.
You can use headers and footers on single slides or all slides. For notes and
handouts, when you apply a header or footer, it applies to all notes and
handouts. Headers and footers that you create for handouts also apply to
printed outlines.

4.3.5 Formatting

You can use these formatting features of Microsoft PowerPoint to effectively
display your data.
 Characters formatting
To make text stand out, you can format the text in selected characters. You

- 13 -

can change the font, color, size of text, bold and italic formats.
 Paragraphs formatting
You can align, center or justify a paragraph, change indent and tab settings,
and change the line spacing of a paragraph.
 Bulleted and numbered lists
Bulleted and numbered lists in Microsoft PowerPoint are easy to create. You
can quickly add bullets or numbers to existing lines of text, or Microsoft
PowerPoint can automatically create lists as you type.
 Automatic formatting
Microsoft PowerPoint, by default, automatically formats certain types of text as
you type. Automatic paragraph formatting includes automatic bulleted and
numbered lists and resizing of text in text placeholders if the text doesn't fit at
its current font size.

4.3.6 Shapes

Shapes can be resized, rotated, flipped, colored, and combined to make more
complex shapes. Many have an adjustment handle that you can use to change
the most prominent feature of a shape — for example, you can change the size
of the point on an arrow. The AutoShapes include several categories of shapes:
lines, connectors, basic shapes, flowchart elements, stars and banners, and
callouts. You can add text to shapes (except lines, connectors, and freeforms).
The text you add becomes part of the shape.
Text boxes can be treated as shapes. They are formatted in many of the same
ways shapes are formatted, including adding colors, fills, and borders.

4.3.7 Pictures

There are two types of pictures: bitmaps or drawn pictures.
Bitmap pictures are made from a series of small dots, much like a piece of

- 14 -

graph paper with specific squares filled in to form an image. Bitmaps are
created with and edited in paint programs, such as Microsoft Paint. All scanned
graphics and photographs are bitmaps. Bitmap pictures are often saved with
a .bmp, .png, .jpg, or .gif extension.
Drawn pictures are created from lines, curves, rectangles, and other objects.
The individual lines can be edited, moved, and rearranged. When a drawn
picture is resized, the computer redraws the lines and shapes so that they
retain their original definition and perspective. AutoShapes are drawn pictures.
Drawn pictures are saved in the format of the application that created them.
For example, Microsoft Windows Metafiles are saved with a .wmf extension.

4.3.8 Tables

A table is made up of rows and columns of cells that you can fill with text and
graphics. Tables are often used to organize and present information. You can
create tables in PowerPoint, or you can add a table from another program.
When you use PowerPoint, you can create a simple table with little formatting,
or one with more complex formatting. You can include fills and border colors
from the presentation's color scheme.

4.3.9 Charts

Charts are visually appealing and make it easy for users to see comparisons,
patterns, and trends in data. You can create a chart in a slide using Microsoft
Graph or Microsoft Excel. When you create a new chart in PowerPoint,
Microsoft Graph or Microsoft Excel opens and a chart is displayed with its
associated data in a data sheet or worksheet.

4.3.10 Sound, Music, Video, and Voice

You can add music and sounds from files on your computer, a network, the

- 15 -

Internet, or Microsoft Clip Organizer. You can also record your own sounds to
add to a presentation, or use music from a CD.
You can add movies and animated GIFs to slides from files on your computer,
the Microsoft Clip Organizer, a network or intranet, or the Internet. "Movies"
are desktop video files with formats such as AVI, QuickTime, and MPEG, and
file extensions such as .avi, .mov, .qt, .mpg, and .mpeg. An animated GIF file
includes motion and has a .gif file extension. Though not technically movies,
animated GIFs contain multiple images which stream to create an animation
effect.

4.4 Table Reports

4.4.1 About Table Reports

A table is made up of rows and columns of cells that you can fill with text and
graphics. Tables are often used to make reports, and organize and present
information.
PTReportCom supports two types of table reports: fixed table report, variable
table report.
Fixed table report: The number of rows and columns in the table is fixed. When
PTReportCom executes a SQL statement, directly puts the result data into
cells in the table.
Variable table report: The number of rows or columns in the table is unfixed,
and it is variable as the number of result records. When PTReportCom
executes a SQL statement, it repeats the table rows or columns for each
record or group, and then puts data into cells of the table.

- 16 -

4.4.2 Creating a Table for a Fixed Table Report

For a fixed table report, you need to create a table in the report template file
according to the report. The format of the table is the same as the format in the
report, but cells that should be filled data into are blank. When PTReportCom
executes a SQL statement, the data values from data source will be filled into
these cells.

BA

BA

1

1
2

2
3

3

BA

BA

3.4141

3.4141

5.2202

5.2202

2.783

2.783

The fixed table defined
in the report template file

The fixed table filled data
by rows in the re port file

4.4.3 Creating a Table for a Variable Table Report

For a variable table report, you also need to create a table in the report
template file according to the report. But you just need to reserve some
rows/columns in the table for one or two records. PTReportCom will add some
rows/columns according to the number of the records returned from data
source.

- 17 -

SalesItem IdDate

SalesItem IdDate SalesItem IdDate

SalesItem IdDate

1503 1998-01-01

1503 1998-01-01
2003 1998-01-02

2003 1998-01-02
2503 1998-01-03

2503 1998-01-03
3503 1998-01-05

3503 1998-01-05
5503 1998-01-10

5503 1998-01-10
1503 1998-01-21

1503 1998-01-21
2003 1998-01-25

2003 1998-01-25
1003 1998-01-31

1003 1998-01-31

The variable-rows tab le defined

in the report template file

The format of the last row/column border can be different from the others. For
example, the outside borders used double lines, and the inside borders used
single lines. To do this, you should reserve the blank rows/columns for 2
records. When PTReportCom inserts some blank rows/columns, the new
rows/columns will inherit the format of the first row/column in the reserved
rows/columns.
One record from data source can be put into two or more rows/columns. To do
this, you need to reserve the blank rows/columns for all records that you want
to put them into one slide. For example, there are 91 records returned from a
database, and you want to put 5 records per slide and 3 rows per record. You
must prepare one slide that contain one table and reserve 15 blank rows in the

The variable-rows table filled data

by rows in the report file

table. If 1 rows per record, you just need to reserve 1 or 2 blank rows in the
table. PTReportCom can insert rows, delete rows, copy slides with tables, but
can not copy rows in one slide.

4.4.4 Formatting Cells

To format cells that contain static contents, use “Format” menu in Microsoft

- 18 -

PowerPoint. For more detail information, refer to Microsoft PowerPoint Help.
For cells in which data are got from database, you can set font, color,
alignment using Microsoft PowerPoint. But to display values in formatting
string, you should use other way.
You should write formatting expressions into data cells in the report template
file. PTReportCom will get the text of the cell as a format expression before it
puts a value into a cell, and output the value using the format expression. In
fact, PTReportCom calls the format function in Visual Basic. The text got from
a cell is used as the format expression in format function. For a variable table
report, PTReportCom will use the format expressions in the reserved
rows/columns. For more information about format expression, refer to “Format
Expressions”.

AmountQuantityDate

AmountQuantityDate

#,##0.00#,##0yyyy-mm-dd

#,##0.00#,##0yyyy-mm-dd
#,##0.00#,##0yyyy-mm-dd

#,##0.00#,##0yyyy-mm-dd
#,##0.00#,##0yyyy-mm-dd

#,##0.00#,##0yyyy-mm-dd

The table defined in the report template file The table generated in the report file

AmountQuantityDate

AmountQuantityDate

827.795601999-02-18

827.795601999-02-18

1,113.058901999-06-14

1,113.058901999-06-14
1,552.251,2402000-01-21

1,552.251,2402000-01-21

A format expression for numbers can have from one to four sections separated
by semicolons. You can define the different formats and colors for positive
values, negative values and zeros.
For example, the format "$#,##0;($#,##0)" has two sections: the first defines
the format and color (black) for positive values and zeros; the second section
defines the format and color (red) for negative values. It displays “2345.12” as
“$2,345”, displays “-5432” as “($5,432)”.
The format “#,##0.00;;” has three sections: the first defines the format and
color (black) for positive values, the second defines the format and color (red)
for negative values, the third section defines the format and color (blue) for
zeros. Note, the first semicolon “;” is red, the second semicolon “;” is blue. The

- 19 -

negative values and zeros are printed using the format of the positive value.
But the color for negative values is red, the color for zeros is blue. It displays
“8.9” as “8.90”, displays “-123” as “-123.00”, and displays “0” as “0.00”.

4.4.5 Irregular Tables

Tables don't have to consist of simple grids. Not every row has to have the
same number of columns. You can merge and split cells to create irregular
tables. An irregular table is the table that contains split cells or merge cells, and
it does not have the same number of cells for each row or column. While an
irregular table provides for an attractive way to display data, but it does make it
harder to process the presentation. You have some difficulty to reference a cell
in an irregular table. For example in the following table, for most Office version,
cell1 is in column 3 and row 2, cell2 is in column 3 and row 3. But for some
lower Office version, cell2 is not in column 3 and row 3. Moreover, an error
may occur when you try to work with some rows or columns in an irregular
table.

CBA

CBA

Cell1

Cell1
Cell2

Cell2

Irregular table

To simplify your work and ensure that report function can be executed correctly,
you should regularize the irregular tables. Split the merge cells, and remove
the border in these cells. For example, the following table is a regularized table,
cell1 is in column 3 and row 2, and cell2 is in column 3 and row 3.

- 20 -

Cell1

Cell1
Cell2

Cell2

CBA

CBA

Regularized table

4.4.6 Referencing Cells

You can reference table cells as A1, A2, B1, B2, and so on, with the letter
representing a column and the number representing a row. Cell references in
Microsoft PowerPoint are always absolute references and are not shown with
dollar signs. You can reference an entire row or column in a calculation in the
following ways:
 Use a range that includes only the letter or number that represents it - for

example, 1:1 to reference the first row in the table. This designation allows
the calculation to automatically include all the cells in the row if you decide
to add other cells later.

 Use a range that includes the specific cells - for example, a1:a3 to

reference a column with three rows. This designation allows the calculation
to include only those particular cells. If you add other cells later and you
want the calculation to include them, you need to edit the calculation.

4.4.7 Referencing Tables

If you want to reference a table, you should reference a slide first. You can
reference a slide by an index number. The index number represents the
position of the slide in a presentation. The index number starts at 1. If the index
number is less than 0, it represents the position from the end of presentation.
So slide 1 is the first slide in a presentation, slide 2 is the second slide in a
presentation, slide -1 is the last slide in a presentation. You can reference a
slide dynamically. “N” means the next slide.

- 21 -

You can reference a table in the slide by an index number. The index number
represents the position of the table in the slide. The index number starts at 1.
So table 1 is the first table in a slide, table 2 is the second table, and so on.

4.4.8 Formatting Cells for Pictures

To enhance the visual impact of your report, you can insert pictures into your
report. PTReportCom supports many popular graphics file formats: bitmap,
JPG, GIF, PNG, TIFF and so on. For the graphics file formats PTReportCom
supports, refer to Microsoft PowerPoint Help.
You should store the path and name of the graphics files in the database, and
identify the image fields in the report function. PTReportCom will read the
graphics files, and insert them into the cells in the report file.
To specify the size, you should write a formatting expression into the cell in the
report template file. PTReportCom will get the text of the cell, and insert a
picture into the cell according to the instruction in the format expression. The
format expression for pictures as follows:

[size]
The size specifies the size of a picture. Possible values are STRETCH, Wnnn
or / and Hnnn. "STRETCH" means that the picture is resized to fit within the
cell. “W100” means that the width of the picture is set to 100 points. “H50”
means that the height of the picture is set to 50 points. The default means the
original size. If you just specify the width or height of the picture, not both,
PTReportCom will retain the original proportions of the picture when
PTReportCom resize it.

Example

w120 h90

Remarks

PTReportCom will insert a picture, and set the width of the picture to 120

- 22 -

points, the height to 90 points.

4.5 Form Reports

4.5.1 About Form Reports

Beside table reports, PTReportCom supports form reports too. For a form
report, you can get data from data sources, and put data into shapes or text
boxes. So you can make a form report as follows:

4.5.2 Creating a Slide

For a form report, you must create a slide including some shapes or text boxes
in the report template file according to the report. When PTReportCom
executes a SQL statement, the data values from data source will be put into

- 23 -

these shapes or text boxes. PTReportCom will add some slides according to
results returned. One record makes one slide.
You can add some shapes as your needs, such as reshaping shapes, resizing
shapes, changing colors, changing the font. For more detail information about
shapes, refer to Microsoft PowerPoint Help.
To generate the previous PowerPoint report, you should make the following
slide in the report template file.

4.5.3 Naming Objects

How to reference an object in a slide? PTReportCom can reference an object
using its name. The name is not case-sensitive, and uniquely identifies an
object in a slide. But Microsoft PowerPoint can not give a way to know the
name of an object.

- 24 -