Chapter 4 Report Templates

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.

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.

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.

Presentations

A presentation is a Microsoft PowerPoint file with extension .ppt. You can open and save it using Microsoft PowerPoint. The presentation is made up of a series of slides.

Slides

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

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.

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.

Formatting

You can use these formatting features of Microsoft PowerPoint to effectively display your data.

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.

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

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.

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.

Sound, Music, Video, and Voice

You can add music and sounds from files on your computer, a network, the 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.

Table Reports

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.

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.

PowerPoint Report - Fixed Table

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.

PowerPoint Report - Variable Table

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

Formatting Cells

To format cells that contain static contents, use "Format" menu in Microsoft PowerPoint. To format cells that contain (Formula) fields, use the switches of fields in Microsoft PowerPoint. For more detail information, refer to Microsoft PowerPoint Help. To format cells that contain static contents, use "Format" menu in Microsoft 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".

PowerPoint Report - Formatting Cells

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

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.

PowerPoint Report - 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.

PowerPoint Report - Regularize Table

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:

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.

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.

Formatting a Cell 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 points, the height to 90 points.

Form Reports

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:

PowerPoint Report - Form Report

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

PowerPoint Report Template - Form Report

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.

We developed a PowerPoint add-in "name.ppa" that can name an object in a slide. The add-in file is located in the PTReportCom's working directory. To load the add-in:

1. Launch Microsoft PowerPoint.

2. Click Add-Ins under Tools menu. The Add-Ins dialog box appears.

3. Press Add New button, and browse to "name.ppa" file, and Press OK button.

4. If prompted to enable macros, press Enable Macros button. The add-in will be listed in the Add-Ins dialog box. Press Close button to close the dialog box.

If however you do not find the add-in listed in the Add-Ins dialog box, then check the macro settings. If this is not set to at least Medium, the add-in won't load. To resolve this:

1. Click Macros under Tools menu, and click Security. The Security dialog box appears.

2. Click the Security Level tab, and click Medium.

3. Press OK button.

4. Now load the add-in.

5. Once the add-in has been loaded you can set the security level back to High.

When you open Microsoft PowerPoint after "name.ppa" have been loaded, a Name menu item will appear under Tools menu. You can show the name of an object, and rename the object. To name an object:

1. Select an object in a slide.

2. On the Tools menu, click Name. The Object Name dialog box appears.

3. You will see the name of the object you have selected.

4. If you want to rename the object, input a new name in the New Name box, and press Rename button. If you get an error message "Permission denied", it probably means that the name already exists.

5. Press Close button to close the Object Name dialog box.

Remember to save all your works. To ensure to save your change to object names, you should save a complete file.

1. On the Tools menu, click Options, and then click the Save tab.

2. Clear the Allow fast saves check box when you finish working on the file, and then save it one last time. A full save occurs when this check box is clear.

Formatting text in an Object

You can use Microsoft PowerPoint to change an object and the attached text. You can change the font, color, fill, shadow and so on. For more detail information, refer to Microsoft PowerPoint Help. But to display values in formatting string, you should use the way similar to cell formatting.

You should write formatting expressions into a shape or text box in the report template file. PTReportCom will get the text as a format expression before it puts a value into the object, and output the value using the format expression. In fact, PTReportCom calls the format function in Visual Basic. The text got from an object is used as the format expression in format function. For more information about format expression, refer to "Format Expressions".

For example, you add a text box with a text "YYYY-MM-DD" in the report template file. In the report file, you will get a formatted date string. For example, "1996-04-01".

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

Formatting Objects for Pictures

As same as the table report, you can insert pictures into your form report too. 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 put them at the position of the objects in the report file.

To specify the size, you should write a formatting expression into the shape or text box in the report template file. The format expression for pictures in form report is the same as the format expression in table report.

[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 object. "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.

Charts

About Charts

You can create many different types of charts in Microsoft PowerPoint. The chart software may be Microsoft Graph or Microsoft Excel. It is depended on the version of your Microsoft PowerPoint. For Microsoft PowerPoint 2003 or earlier, the default chart software is Microsoft Graph. For Microsoft PowerPoint 2007 or later, the default chart software is Microsoft Excel.

PTReportCom supports two kinds of charts created by Microsoft Graph or Excel. It executes a SQL statement, and puts the result data into the datasheet or worksheet of the chart. To work with charts created in Graph or Excel, you must have Graph or Excel installed.

Creating a Blank Chart using Microsoft Graph

To create a Graph chart in the report using PTReportCom, you need to add a Graph chart in the report template file first. The chart will be brought into the report file with the same chart type, display option, data format, label format and other chart item

If your Microsoft Office is earlier than Office 2007, or Microsoft Excel 2007 is not installed, when you create a new chart in Microsoft PowerPoint, Microsoft Graph opens.

To add a Graph chart in the template file:

1. Open the report template file using Microsoft PowerPoint.

2. On the Insert menu, click Chart.

3. Change the sample data on the datasheet as you need.

4. Modify the chart. For example, you want to change the chart type, make the text larger, or change colors, patterns, lines, fills, and borders in charts.

5. After you have finished the modification, delete data from the chart. You should keep a blank chart in the report template file. PTReportCom will put data into the datasheet of the chart.

For more detail information, refer to Microsoft PowerPoint Help and Microsoft Graph Help.

Creating a Blank Chart using Microsoft Excel

To create an Excel chart in the report using PTReportCom, you need to add an Excel chart in the report template file first. The chart will be brought into the report file with the same chart type, display option, data format, label format and other chart item.

To add an Excel chart in the template file:

1. Open the report template file using Microsoft PowerPoint.

2. Insert a chart with a chart sheet and a worksheet. For more information to insert an Excel chart object in Microsoft PowerPoint, please refer to the following part.

3. Change the sample data on the worksheet as you need.

4. Modify the chart. For example, you want to change the chart type, make the text larger, or change colors, patterns, lines, fills, and borders in charts.

5. After you have finished the modification, delete data from the chart. You should keep a blank chart in the report template file, and make the chart sheet active. PTReportCom will put data into the worksheet of the chart.

By default, Microsoft PowerPoint 2007 uses Microsoft Excel to create charts, but doesn't expose the chart as a normal Excel object. To insert an Excel chart object, you can insert an Excel worksheet first, and then create a chart in the Excel worksheet object. Another way is to copy an Excel chart object from earlier PowerPoint presentation.

For Microsoft PowerPoint 2007 or later, to insert an Excel chart object:

1. In Microsoft PowerPoint, on the Insert tab, in the Tables group, click Table, and then click Excel Spreadsheet. You will see an Excel worksheet object.

2. Right-click the object, point to Worksheet Object on the shortcut menu, and choose Open from the submenu. Microsoft Excel will appear.

3. Create a chart in Microsoft Excel, and move the chart to a new worksheet.

4. When you've finished, choose Close & Return from the File menu.

For Microsoft PowerPoint 2003 or earlier, to insert an Excel chart object:

1. In Microsoft PowerPoint, click Object on the Insert menu, and then select the Microsoft Excel Chart.

2. You can work the Excel chart object by right-clicking the object, and pointing to Chart Object on the shortcut menu, and choosing Open from the submenu.

3. When you've finished, choose Close & Return from the File menu.

For more detail information, refer to Microsoft PowerPoint Help and Microsoft Excel Help.

Referencing Charts

If you want to reference a chart, 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.

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