Home

PTReportGen User Manual

1. Hie Chapter 7 Menus Toolbar and Shortcut Keys 7 1 File Menu The File menu offers the following commands New Creates a new PTR file Open Opens an existing PTR file Close Closes an opened PTR file Save Saves an opened PTR file using the same filename Save As Saves an opened PTR file to a specified file name m Opens an existing report template file Open Report File Opens an existing report file Open Log File Opens an existing log file Recent Files Opens last PTR files you closed Exit Exits PTReportGen 7 2 Edit Menu The Edit menu offers the following commands Undo Undo previous editing operation Redo Redo an editing operation that you undid Deletes text from the document and moves it to the Cut clipboard Copy Copies text from the document to the clipboard Paste Pastes text from the clipboard into the document Delete Deletes the selection Select All Selects the entire text Find Finds the specified text Find Next Finds the next matching text Replace Replaces specific text with different text Go to Goes to specified line or function in the document 7 3 View Menu The View menu offers the following commands Toolbar Show or hide the toolbar 725 Status Bar Show or hide the status bar 7 4 Report Menu The Report menu offers the following commands Configuration
2. Flexible deployment PTReportGen can be run on your desktop or server Chapter 2 Installation and Startup 2 1 Software Requirements Supported Operating System Microsoft Windows XP Windows Server 2003 Windows Vista Windows Server 2008 Windows 7 or later This software requires the following W Microsoft NET Framework 4 Open XML SDK 2 5 for Microsoft Office 2 2 Installing PTReportGen Run the installation program and follow the instructions to complete PTReportGen installation If you don t have Microsoft NET Framework 4 installed please install it first If you don t have Open XML SDK 2 5 installed please install it first 2 3 Uninstalling PTReportGen 1 Quit PTReportGen 2 Double click the Add Remove Programs icon in the Windows Control Panel 3 Click PTReportGen in the Currently installed programs box and then click the Change Remove button 4 Follow the instructions on the screen to complete uninstalling the program 2 4 Command Line PTReportGen can be run in Windows mode or command line mode The 2 Syntax of command is pptreport ptr file name C D I interval U1 user1 P1 pwad1 U10 user10 P10 pwd10 pa2 pa10 ptr file name C D interval U1 user U10 user10 P1 pwdi P10 pwd10 pa10 Specifying a PTR ptr file that tells PTReportGen how to get data from data sources and how to put data into a report
3. Configures the file names data sources and parameters Run Runs the PTR file to generate a report 7 5 Tools Menu The Tools menu offers the following commands Option Sets options 7 6 Help Menu The Help menu offers the following commands Help Context Starts the online help system Tutorial Starts a brief step by step tutorial Tip of the Day Displays a dialog containing a useful tip about PTReportGen Hints and Tips Displays miscellaneous hints and tips on how to use PTReportGen productively Shortcut Keys Shows the keyboard map Takes you to the home page of PTReportGen web Home Page site Support i you to the support page of PTReportGen web Buy Now Buy PTReportGen immediately About Displays the version number of PTReportGen 7 7 Toolbar The toolbar provides quick access to many features The buttons on the toolbar perform the following commands Buttons Commands 273 Creates a new PTR file Opens an existing PTR file Saves an opened PTR file using the same filename Open the report template file LED Open the report file de Deletes text from the document and moves it to the clipboard Copies text from the document to the clipboard Pastes text from the clipboard into the document Undo previous editing operation Redo an editing o
4. Run PTReportGen in command line mode Display the generated report with Microsoft PowerPoint Log the processing records message If interval is greater than 1 itis the interval of records If interval is less than 1 it is the percent of interval 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 The values of the parameters defined in the PTR file You can use parameters in SQL statements PTReportGen will replace the names of parameters in a SQL statement with the actual values before it executes a SQL statement You can use no more than 10 parameters in one report For example you have defined two parameters in your PTR file The first parameter is sales date and the second is the category of the products You can run PTReportGen in command line mode as follows pptreport c PPTReport myreport ptr c 1996 05 01 Dairy Products Chapter 3 Quick Start 3 1 Learning how to use PTReportGen You can teach yourself how to use PTReportGen by choosing from the methods available in this section You can study the sample reports and sample database included with PTReportGen W You can use the detailed descriptions and instructions in the My First Report 3 2 Sample Database PTReportGen comes with Sample mdb a sampl
5. 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 W Characters formatting To make text stand out you can format the text in selected characters You Jg 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 c
6. 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 A B C Cell1 Cell2 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 B Use arange 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 24 to add other cells later B Use arange 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
7. Excel Help 32 4 6 3 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 ina 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 233 Chapter 5 Reporting with PTReportGen 5 1 Creating and Opening PTR Files 5 1 1 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 report template file the name of 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 5 1 2 Create a new PTR file On the File menu click New 5 1 3 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 contai
8. If the format expression contains only characters to the left of this symbol numbers smaller than 1 begin with a decimal separator To display a leading zero displayed with fractional numbers use zero as the first digit placeholder to the left of the decimal separator In some locales a comma is used as the decimal separator The actual character used as a decimal placeholder in the formatted output depends on the number format recognized by your system Thus you should use the period as the decimal placeholder in your formats even if you are in a locale that uses a comma as a decimal placeholder The formatted string will appear in the format correct for the locale Percent placeholder Multiplies the expression by 100 The 89 percent character is inserted in the position where it appears in the format string Thousand separator The thousand separator separates thousands from hundreds within a number that has four or more places to the left of the decimal separator Standard use of the thousand separator is specified if the format contains a thousand separator surrounded by digit placeholders 0 or A thousand separator immediately to the left of the decimal separator whether or not a decimal is specified or as the rightmost character in the string means scale the number by dividing it by 1 000 rounding as needed Numbers smaller than 1 000 but greater or equal to 500 are displayed a
9. 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 lf you want to rename the object input new name 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 29 4 5 4 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 PTReportGen 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 PTReportGen calls the format function in Visual Basic The text got f
10. 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 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 29852 irregular table provides for an attractive way to display data but it is harder to be processed 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 A B C Cell1 Cell2 Irregular table To simplify your work and ensure that report function can be executed correctly
11. 1 Use M if this is the only character in your user defined numeric format MM Displays the month as a number with a leading zero for example 01 12 01 MMM Displays the month as an abbreviation for example Jan MMMM Displays the month as a full month name for example January Gg Displays the period era string for example A D H Displays the hour as a number without leading zeros using the 12 hour clock for example 1 15 15 PM Hh Displays the hour as a number with leading zeros using the 12 hour clock for example 01 15 15 PM H Displays the hour as a number without leading zeros using the 24 hour clock for example 1 15 15 HH Displays the hour as a number with leading zeros using the 24 hour clock for example 01 15 15 M Displays the minute as a number without leading zeros for example 12 1 15 95 Mm Displays the minute as a number with leading zeros for example 12 01 15 S Displays the second as a number without leading zeros for example 12 15 5 Ss Displays the second as a number with leading zeros for example 12 15 05 F Displays fractions of seconds For example ff displays hundredths of seconds whereas ffff displays ten thousandths of seconds You may use up to seven f symbols in your user defined format Uses the 12 hour clock and displays an uppercase A for any hour before noon displays an uppercase P for any hour between
12. 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 ina 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 4 4 8 Formatting Cells for Pictures To enhance the visual impact of your report you can insert pictures into your report PTReportGen supports many popular graphics file formats bitmap JPG GIF PNG TIFF and so on For the graphics file formats PT ReportGen supports refer to Microsoft PowerPoint Help You should store the path and name of graphics files in the database and identify the image fields in the report function PTReportGen 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 PTReportGen will get the text of cell and insert a picture 25 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 STRE
13. 4 4 2 Creating a Table for a Fixed Table Report eese 21 4 4 3 Creating a Table for a Variable Table Report esee 21 444 Form tting Cells eic nh e t p ete ned 22 4 45 Irregular Tables ate t te eR ERE IUe e UR B RR EEE 23 4 4 6 Referencing Cells dar ma e p pa E renes 24 44 7 Referencing Tables os EUER Bp egeta db n eee 25 4 4 8 Formatting Cells for Pictures eese eene 25 FORM REPORTS PUERO TREE 26 4 5 L About Form Reports ue a e DR E CHEROKEE M ERU 26 4 5 2 Creating a ERE e E ORI eee er EYE OUO Lew 27 4 3 3 Naming ODIects ien OU IURI ee auis 28 4 5 4 Formatting Text in an Object 4 scs tede ide eie d ie dete bi ive 30 4 5 Formattitig PICIUFeS sio te a der HR Re RR re RR E ERR DO 31 ZG CHARTS em nmeuCu ence 31 4 6 I About Charts s e neue ped n one ep dedi t tee 31 4 6 2 Creating a Blank Chart using Microsoft 1 32 4 6 3 Referencing CHAPS 4 ete e mre te RU P E Ee PH EROR IE PP nEss 33 CHAPTER 5 REPORTING WITH PTREPORTGEN eeeeee eese esteso te tenens tn senses ense ta suse ta snae 34 S T CREATING AND OPENING PTR FIEES er ee eere aai Ees 34 OL About PTR files annia onte d ama anre nit Ri diit ee ils 34 2 1 2 Create a new PIR oras PO aee e e eet 34 Od 3 Open a PTR file sive p RE oe A ed ae S 34 5 J d
14. 96 dpi screen Windows PC 4 pixels are equal to 3 points It is very useful to create a temporary table You can prepare data using INSERT UPDATE DELETE INSERT SELECT and then make a report using REPORT function 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 79 Chapter 9 PTR File Reference 9 1 PTR File Format The layout of a PTR file is as the following PPTReport Version 2 0 Data Source PPTReport is the flag of PTR file Version 2 0 is the version of PTR file A PTR file contains several sections The sections of Data Source File and Parameter consist of a group of related settings The sections and settings are listed in the PTR file in the following format section name keyname value In this example section name is the name of a section The enclosing brackets are required and the left bracket must be in the leftmost column on the screen The keyname value statement defines the value of each setting A keyname is the name of a setting It can consist of any combination of letters and digits and must be followed immediately by an equal sign 2 The value can be an integer a string or a quoted string depending on the setting You can include comments in these sections You must begin each line of a 80 comment with a semicolon The SQL section consists of funct
15. GROUP argument and specifies the range of group in the worksheet 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 group should contain the range of details and the area that includes all cells for this group You reference a group range like 2 4 or B2 D5 The ranges of groups must be same as the range of details The RANGECOUNT argument specifies the number of blank range which you defined in the report template It is valid when the type is var Possible values are 1 or 2 One means one blank range you defined and two means two ranges If you hope that the format of the last row column border can be different from the others you can define two blank ranges Default is 1 The NODATA argument specifies an option when no data are returned from data source It is valid when the type is var If the value is delrange PTReportGen will delete the range when no data are returned Default is to do nothing 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 67 W The sqistatement is SQL statement such as a SELECT state
16. 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 PTReportGen 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 PTReportGen calls the format function in Visual Basic The text got from a 2D cell is used as the format expression in format function For more information about format expression refer to Format Expressions Date Quantity Amount yyyy MM dd THEO 0 00 yyyy MM dd THEO 0 00 yyyy MM dd HHO 0 00 The table defined in the report template file Date Quantity 1999 02 18 560 1999 06 14 890 2000 01 21 1 240 Amount 827 79 1 113 05 1 552 25 The table generatedin the report file 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 4 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
17. Wendy Mackenzie Sales Representative g 170 Prince Edward Parade Hunter s Hill Postal Code 2042 02 555 5914 02 555 4873 aT SEDES http Zwww microsoft com accessdev sampleapps gdaymate htm 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 First it duplicates slides according to the number of records And then it processes records one by one and puts only one record per slide It fetches a record and puts the data into the shapes or text boxes in one slide And then go to the next slide 64 6 3 Chart Function The CHART function is used to make the charts Syntax Chart sqlstatement Arguments TYPE reporttype SLIDE s ide CHART chart FILLORDER fillorder CELL celllist RANGE range GROUP grouplist GROUPRANGE grouprange RANGECOUNT rangecount NODATA nodataoption CONNECT datasource The TYPE argument specifies the report type Possible values are fix or var fix me
18. are optional settings If no connection string PTReportGen will make a connection to data source using the ODBC data source name EncryptPassword Y N This setting specifies how to save the passwords of data sources If the value is Y the passwords will be saved in an encrypted format If the value is N the passwords will be saved in plain text 9 3 FILE Section FILE section contains information about files ReportTemplateFileName lt templatefilename gt This setting specifies the name of report template file lt templatefilename gt value is the name and path of report template file The file path can be a relative path or an absolute path If it is a relative path the base path is the path of PTR file ReportFileName lt reportfilename gt This setting specifies the name of report file lt reportfilename gt value is the name and path of report file The file path can be a relative path or an absolute 83 path If it is a relative path the base path is the path of PTR file In lt reportfilename gt you can use parameters LogFileName lt logfilename gt This setting specifies the name of log file lt logfilename gt value is the name and path of log file The file path can be a relative path or an absolute path If it is a relative path the base path is the path of PTR file In lt logfilename gt you can use parameters 9 4 PARAMETER Section PARAMETER section contains information about paramete
19. basic business reports and typically has information such as Customer Name City Country and Contact Name 3 4 1 Creating a report template 1 Run Microsoft PowerPoint create a new presentation with a blank slide 2 On the Insert menu click Table Select the number of columns and rows Press OK button 3 Click the cell A1 type Customer Name In the same way you input City Country and Contact Name into the cells B1 C1 and D1 4 Format the text of A1 B1 C1 and D1 as you like including font font size font colour bold background alignment and border 5 You can change the width of these columns The report template you have made is as follows Customer List ZI 6 Click Save on the File menu chose a directory such as C Report type custlist pptx in the File name box and press Save button 7 Click Close on the File menu 3 4 2 Creating a PTR file 1 Run PTReportGen 2 Click New on the File menu 3 Click Save on the File menu chose the directory to which you have saved the report template type custlist ptr in the File name box and press Save button 3 4 3 Configuring the report 1 On the Report menu click Configuration The Configuration dialog box appears 2 Click the File tab In the Template File box type custlist pptx In the Report File box type Report custlist pptx In the Log File box type Log custlist log 3 Click the Data Source tab Press New butt
20. ceo des aee decet des cede eed dece deese dee 43 5 6 1 Inserting pictures into a report template essere nennen eene nenne 43 5 8 2 Inserting pictures into a report assesses eese nnne entente enne tenete entente enne 43 5 9 USING PARAMBTERS 44 5 TO PROGRAMMING ig a 47 5 10 1 Making PTR files programmatically eese esses eterne nente enne 47 CHAPTER 6 FUNCTION REFERENCE eee ee eee ee eee enne seen tese ee tn nest seen sees seen ases eene esten a 49 6 1 TABLE REPORT EUNCTIONEzizc itt cer tiere ient Sh cian cad Ege ee 49 6 2 FORM REPORT FUNCTION 54r GI Dec 60 6 3 CHART FUNCTION ccccccccccecccececececececesecesecesssesecssesesesesesscsssesseussssesssssssssseesessesssssssssssssssssessesseseess 65 64 EXECS OL FUNGTION ene RI eai put e ms 70 CHAPTER 7 MENUS TOOLBAR AND SHORTCUT KEYS seseessecesssecssoccessoceessecssssecssooeessese 72 TAL EIER MENU 5 iere eiie erede eee A diee 72 Fut ctor Se aR eee eoe IR A 72 Te VIEW MENU etae 72 TA REPORT MENU oi EH cto aaah bed ae ee as eS ESB 73 Ted LOOES MENU siaran ttr cron E 73 JO HELP MENU 3 aie ette tet ret Se reet rete ebd tet ec ree et ens 73 EEEE 73 T S SHORTCUT KEYS eddie desde deleted cede eene des dece e
21. fixed table defined The fixed table filled data in the report template file by rows in the report file 4 4 3 Creating a Table for a Variable Table Report For a variable table report you do not know how many records will return from database You need to create a table in the report template file PTReportGen will add or delete ranges according to the number of records returned from data source If there are too many records you can put them into more slides 21 Item Id Date Item Id 1998 01 01 1998 01 02 1998 01 03 1998 01 05 1998 01 10 1998 01 21 1998 01 25 1998 01 31 The variable rows table defined The variable rows table filled data in the report template file by rows in the report 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 define two blank ranges The ranges can be nested The inside range is used for the detail data and the external range is used for the group PTReportGen will repeat the inside range for each record and repeat the group range for each group 4 4 4 Formatting Cells 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
22. name option input data source name user name and password press OK button 336 To define a connection using a connection string click Using connection string option input data source name and connection string select a data provider 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 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 connection string and data provider 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 5 4 Configuring Parameters 5 4 1 About parameters You can us
23. reports with parameters PTReportGen enables you to create reports with parameters You may use 08 parameters in SQL statements You will be asked to input the values of parameters while creating reports Supporting Windows mode and command line mode PTReportGen supports command line mode So it is possible to call PTReportGen from other program For developers you can integrate PTReportGen into your application W Creating complex reports You can create complex reports The complexity might come from report formatting as well as report content W Creating reports with charts PTReportGen enables you to include sophisticated colorful charts in your reports You can use charts any time you want to improve the usefulness of a report Creating reports with pictures PTReportGen can insert pictures from the graphics files and change the size of pictures Many reports in one Microsoft PowerPoint presentation One Microsoft PowerPoint presentation may contain many reports One report may be one or more slides You can generate a presentation in one generating process Generating reports automatically The process of report generation can be fully automated periodically or on events PTReportGen can be scheduled with Windows Scheduled Tasks or other tools One time configuration With on time configuration you can repeatedly generate reports especially periodic reports such as daily weekly monthly and annual reports
24. step 1 and insert results into another temporary table 3 Use the variable table report function and join the detail data and the summary data using JOIN The summary fields must be included in the group list For more information refer to the samples sales detail ptr within PTReportGen 5 8 Pictures 5 8 1 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 5 8 2 Inserting pictures into a report Except for inserting the static pictures during report design you want to insert pictures during report building process 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 graphics files in the database You stored the path and file name of 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 43 2 Identify the image fields in the report function Write a report function i
25. the names of the parameters It is a good choice a name begins with the character such as ReportDate In the text editor window you can use comments A comment is the characters followed by any sequence of characters including new lines followed by the characters You cannot nest comments You can use hints in the SQL statement for Oracle database You can reference a slide dynamically means the next slide N 1 means the last slide that the previous function processed To add totals or subtotals you can use the aggregate functions in SQL statement 78 To group data in a report you should use GROUP argument in the report function In REPORT function the order of groups should be in accordance with the order of ORDER BY clause in the SQL statement For a variable table report if the grouprange is not same as the range of detail you must add a pagebreak by group and the length of range can not be more than 1 row column Default PTReportGen will not show the group name in the new page You can add s to show them For example 1gs 6rs You can create reports with pictures unsing PTReportGen You should store the path and name of the graphics file in the database identify the image fields in the report function and specify the size in the report template file To convert from pixels to points it is depend on the screen resolution DPI If you have a
26. the report template file 21 Click to add title Home Page 4 5 3 Naming Objects How to reference an object in a slide PTReportGen 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 PTReportGen 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 Press Add New button and browse to file and Press OK button 28 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 has been loaded a Name menu item will appear under Tools menu You can show the name of
27. to the right of the decimal separator Standard N or n Displays number with thousand separator at least one digit to the left and two digits to the right of the decimal separator Displays number multiplied by 100 with a percent sign Percent appended immediately to the right always displays two digits to the right of the decimal separator Displays number with thousandths separator multiplied by 100 with a percent sign appended to the right and ne separated by a single space always displays two digits to the right of the decimal separator Uses standard scientific notation providing two Scientific significant digits E ore Uses standard scientific notation providing six significant 87 digits Displays number as a string that contains the value of the number in Decimal base 10 format This option is ee supported for integral types Byte Short Integer Long only Displays number as a string that contains the value of the number in Hexadecimal base 16 format This option R is supported for integral types Byte Short Integer Long only Yes No Displays No if number is 0 otherwise displays Yes True False Displays False if number is 0 otherwise displays True On Off Displays Off if number is 0 otherwise displays On 10 1 3 User Defined Numeric Formats The following table identifies characters you can use to create user defined number f
28. used as the time separator in formatted output is determined by your system settings Date separator In some locales other characters may be used to represent the date separator The date separator separates the day month and year when date values are formatted The actual character used as the date separator in formatted output is determined by your system settings E E e e Scientific format If the format expression contains at least one digit placeholder 0 or to the left of E E e or e the number is displayed in scientific format and E or e is inserted between the number and its exponent The number of digit placeholders to the left determines the number of digits in the exponent Use E or e to place a minus sign next to negative exponents Use E or e to place a minus sign next to negative exponents and a plus sign next to positive exponents You must also include digit placeholders to the right of this symbol to get correct formatting Literal characters These characters are displayed exactly as typed in the format string To display a character other than 9 one of those listed precede it with a backslash or enclose itin double quotation marks Displays the next character in the format string To display a character that has special meaning as a literal character precede it with a backslash Y The backslash itself isn t displayed Using a ba
29. Blank Chart using Microsoft Excel To create an Excel chart in the report using PTReportGen 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 in Microsoft PowerPoint 2007 or later 1 Open the report template file using Microsoft PowerPoint 2 Place the insertion point where you want to create the chart On the Insert tab in the Illustrations group click Chart 3 In the Insert Chart dialog box click a chart and then click OK button Microsoft Excel will open and display sample data on a worksheet 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 If the report type is fix you should define the blank ranges according to number of records W If the report type is var you should define one or two blank ranges 5 After you have finished the modification delete data from the worksheet of chart You should keep a blank chart in the report template file PT ReportGen will put data into the worksheet of chart 6 When you ve finished click the Microsoft Office Button in Excel and then click Close For more detail information refer to Microsoft PowerPoint Help and Microsoft
30. PTReportGen User Manual Version 5 1 2014 04 Copyright 2014 LJZsoft Corporation All rights reserved Contents CHAPTER 1 INTRODUCTI N creen otio pose ota ta cua oa ca cada Ua do joe De a 1 P T OVERVEW LL 1 I2 FEATURES Re ER I Le x A tee pr E Edel ts 1 CHAPTER 2 INSTALLATION AND STARTUP u cccssccsssscescscssessssnesscsncscesssssnsssssnessesnessessessosees 5 2 1 SOFTWARE REQUIREMENTS PR TEE Ue xe Eee Ee ee reae Enea E AEE RE a 5 2 2 INSTALLING P TREPORTGEN etti rtr ER EGRE tee RE 5 2 3 UNINSTALLING PT 5 2 4 COMMAND DOOR Pr MPO DOR OI 5 gestas do pita ene eo ond oa aba 7 3 1 LEARNING HOW TO USE PTREPORTGEN eese a A aS EENES 7 3 2 SAMPLE DATABASE RR RE RE En E D ERREUR KA cb ARRIUS etes 7 3 3 STEPS OF REPORTING eese stet E UR NENNT TI RELIER OT PERSEO E EDEA REESE 8 34 MY FIRST REPORT decetero endrerit eee dede etie n 9 3 4 1 Creating a report template eese eene teen etre rennen entente tenerent entren ne 9 3 42 Creating PTR file eei eh P be 10 3 4 3 Configuring the 2 eed edv ete iie ie epe tomate ade reed ie ec dues 10 3 4 4 BIDTHHPRTRIT TRITUM 11 3 4 5 Understanding the function n en et RE ett ete Rege
31. SAVE PTR file oie e dee ated e eet de ode 34 2 2 CONFIGURING FIDES 2 ect tt Y DAN OE E terr DI HR nis 35 2 2 L About files ue en t e OE RO ERE QR 35 5 2 2 Configuring file 35 DDS Converting files e RU ap PEORES 36 5 3 CONFIGURING DATA S OURCBS EE aer e e 36 9 3 About data sOurceszaus ids odo ue pe ERN 36 5 3 2 Adding modifying and deleting a data source eene 36 5 4 CONFIGURING PARAMETERS etes center on annee e eene SER eee eae eire erae Enea neenon 37 od T About parametrs eii REG RE RR UR EP EHE RR RE 37 5 4 2 Adding modifying and deleting a parameter eene 38 5 5 INPUTTING FUNCTIONS 7 5 5 heic ear eO Ea 39 9 0 RUNNING PER led ettet e eerie 39 5 6 1 Windows mode EH ET RENT RE EI E ERR 40 23 6 2 Command lin mod e e e C e RR ER Re ER BE ainei Taa 40 5 7 SORTING GROUPING AND TOTALING cccecssssscecececeesssaececececeessaaececececeeseaaececececeesssaeeeeeeeeeeneaaees 41 D7 Sorting datn 2 esit deed 41 2 72 TOtaling s dh Bit ess 41 5 7 3 Grouping data and subreports 42 2 7 4 Subtot ling ci ii ee eR e 42 DO PICTURES esseen eara des dede
32. 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 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 rangecount 2 You have make 2 ranges in the table in the report template file 56 9 PTReportGen executes the SQL statement and gets data from data source First it inserts some rows 19 rows per slide Second it duplicates slides according to the number of records And then it deletes the extra rows in the last slide Finally it puts the data into the report by records Example 3 Variable Table Report with Group 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 rangecount 5 SELECT LEFT CompanyName 1 CompanyName ContactName Phone amp Phone Fax amp Fax Address CityName amp amp CountryName PostalCode FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries Count
33. TCH means that the picture is resized to fit within the cell W100 means that the width of picture is set to 100 points H50 means that the height of picture is set to 50 points The default means the original size If you just specify the width or height of picture not both PTReportGen will retain the original proportions of picture when PTReportGen resize it Example w120 h90 Remarks PTReportGen will insert a picture and set the width of picture to 120 points the height to 90 points 4 5 Form Reports 4 5 1 About Form Reports Beside table reports PTReportGen 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 296 G day Mate 02 555 5914 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 PTReportGen executes a SQL statement the data values from data source will be put into these shapes or text boxes PTReportGen will add slides according to the number of records returned One record makes one slide You can add 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
34. ange is 2 2 8 rangecount 2 There are two blank ranges in the report template 9 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 6 4 ExecSQL Function The EXECSQL function executes a SQL statement but does not return result to report Syntax ExecSQL sqlstatement Arguments CONNECT datasource 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 B The sqistatement 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 report file 70 F1 EXECSQL CREATE TABLE min_date DATE max_date DATE F2 EXECSQL INSERT INTO tmpO SELECT
35. ans a fixed table report and var means a variable table report Default is var B The SLIDE argument identifies a slide in the report template The slide is the index number of slide The index number starts at 1 If the index number is less than O 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 65 The CHART argument identifies a chart in the report template The chart is the index number or chart in a slide The index number starts at 1 For examples chart 2 is the second chart in the slide Default is 1 The FILLORDER argument specifies the order in which PTReportGen fills data Possible values are row or col row means to fill data by rows col means to fill data by columns Default is row m The CELL argument specifies the positions where data values will be inserted The celllist is the list of cells separated by the character It identifies the cells in a datasheet or 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 n
36. ckslash is the same as enclosing the next character in double quotation marks To display a backslash use two backslashes Y Examples of characters that can t be displayed as literal characters are the date formatting and time formatting characters a c d h m p s t w y and the numeric formatting characters 0 9o E e comma and period and the string formatting characters amp lt gt and ABC Displays the string inside the double quotation marks To include a string in the style argument from within code you must use Chr 34 to enclose the text 34 is the character code for a quotation mark 10 2 Formats for Date Time Values 10 2 1 Predefined Date Time Formats The following table identifies the predefined date and time format names These may be used by name as the style argument Format Name Description General Date or G Displays a date and or time For example 3 12 2008 92 11 07 31 AM Date display is determined by your application s current culture value Long Date Medium Date or D Displays a date according to your current culture s long date format For example Wednesday March 12 2008 Short Date or d Displays a date using your current culture s short date format For example 3 12 2008 Long Time Medium Time or T Displays a time using your current culture
37. contains one of the predefined numeric formats only one section is allowed If you use The result is One section only The format expression applies to all values Two sections The first section applies to positive values and zeros the second applies to negative values Three sections The first section applies to positive values the second applies to negative values and the third applies to zeros The following example has two sections the first defines the format for positive values and zeros the second section defines the format for negative values SH HHO If you include semicolons with nothing between them the missing section is printed using the format of the positive value For example the following format displays positive and negative values using the format in the first section and 86 displays Zero if the value is zero SH 0 Z e r o 10 1 2 Predefined Numeric Formats The following table identifies the predefined numeric format names These may be used by name as the style argument Format name Description General Number G org Displays number with no thousand separator Currency C orc Displays number with thousand separator if appropriate displays two digits to the right of the decimal separator Output is based on system locale settings Fixed F or f Displays at least one digit to the left and two digits
38. e 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 PTReportGen 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 Double click Administrative Tools and then double click Data Sources ODBC The ODBC Data Source Administrator dialog box appears 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 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 Steps of Reporting To create a report with PTReportGen you should do as follows 1 Prepare works Before you create a report you should determine the layout of the report and know where and how to get the data You must know how to access the databases you are reporting from So you need the data source name user name and password If y
39. e parameters in SQL statements These values need to be 37 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 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 5 4 2 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 lf 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 t
40. e s 98 Chapter 1 Introduction 1 1 Overview PTReportGen is a report generator for Microsoft PowerPoint that outputs reports in Microsoft PowerPoint format If you know how to use Microsoft PowerPoint and write SQL statements you can use PTReportGen to create all kinds of reports as you need To create a report PTReportGen Report need to read a report template Template file and a PTR file The report Report template file is a Microsoft PowerPoint presentation that SQL defines the layouts and formats of a report The PTR file contains SQL statements and some information and tells PTReportGen how to get data from database and how to put data into a report First PTReportGen creates a blank report using the report template file executes SQL statements in the PTR file and puts the data from database into the report 1 2 Features PTReportGen 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 presentation format as a result Making report template directly using Microsoft PowerPoint 1 The main advantage of using PTReportGen is based on the fact that all formatting is done directly in Microsoft PowerPoint You can take full advantage of Microsoft PowerPoint including text fo
41. e same as the names of parameters You should be careful to define a unique name for each 44 parameter It is a good choice a name begins with the character Example Input an order id to get the order information The field OrderlD is numeric type 1 Defining a parameter Define a parameter as follows Name OrderlD Title Order ID 2210248 Default 10360 2 Using a parameter You can use the parameter OrderlD in SQL statements For example SELECT o OrderDate SUM d UnitPrice d Quantity 1 d Discount AS Amount FROM Orders o OrderDetails d WHERE o OrderlD d OrderlD AND o OrderlD OrderlD GROUP BY o OrderlD o OrderDate Example Define two parameters The first parameter is the sales date and the second is the category of products The field OrderDate is the date type and CategoryName is the char type 1 Defining parameters Define parameters as follows 1 SalesDate Title1 Sales Date Default1 1996 05 01 45 Name2 Category Title2 Category of Products Default2 2 Using parameters You can use the parameters SalesDate Category in SQL statements For example SELECT sacs FROM Orders OrderDetails Products Categories 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 pa
42. e 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 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 used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data A chart has many elements chart area plot area data points data series axis legend title and data label Some of these elements are displayed by default others can be added as needed You can change the display of the chart elements by moving them to other locations in the chart resizing them or by changing the format You can also remove chart elements that you do not want to display 4 3 10 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 a
43. e 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 35 5 2 3 Converting files PTReportGen 5 can not convert a file to other file format 5 3 Configuring Data Sources 5 3 1 About data sources A data source identifies a database you want to access PTReportGen can access to almost all of the databases such as Oracle DB2 Sybase Informix Microsoft SQL Server Teradata MySQL Microsoft Access and dBase through OLE DB and ODBC It supports more than one data sources in one report You can get data from the different databases such as Oracle DB2 and Microsoft SQL Server and put them into one report You can define a connection to a data source using an ODBC data source name or a connection string If you use an ODBC data source name to make a connection you should specify a user name and a password If you use a connection string to make a connection you also should specify a data source name that you can reference in functions 5 3 2 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 To define a connection using an ODBC data source name click Using ODBC data source
44. eate a report template first The report template is a Microsoft PowerPoint presentation that defines the layouts formats and styles of 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 tables you will fill data into PTReportGen will generate the report based on the report template file All static contents 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 pptx You can 16 open 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
45. ee t de dede ee dee dee eed due 74 CHAPTER HINTS AND TIDBS 6 eee ek esee reos 76 CHAPTER 9 PTR FILE REFERENCE 1 ccssssccssssscccssssccssssccccsssccccssscccssssccscsssaccccsscascessacecessnee 80 9 TP PTREIBEPORMAT eet detecte eee ee tdeo ede hee ede 80 9 2 DATA SOURCE SECTION ccccccesssececsssceceessncecsensececseceeceesececsesaeeecseseecsesaeeecsesaececseseeceeseeeeneaaees 81 O SPICE SSEGCTION hee mtt eret M A a 83 9 4 PARAMETER SECTION tote aie el Ee Sea n eee 84 CHAPTER 10 FORMAT EXPRESSIONS eese eese esee eee tn atten stes seta snue 86 10 1 FORMATS FOR NUMERIC VALUES eene 86 10 1 1 Different Formats for Different Numeric Values eese 86 10 1 2 Predefined Numeric Formats eese eene rene 87 10 1 3 User Defined Numeric Formats eese esee eene eene tenerent 88 10 2 FORMATS FOR DATE TIME 5 92 10 2 1 Predefined Date Time Formats 92 10 2 2 User Defined Date Time Formats 94 CHAPTER 11 LICENSE AND SUPPORT eese esee eese e eene tenen sten tuse ta 97 TUS P unecednde 97 2 5 i rrr te te eer dte tete e
46. ext 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 B The RANGE or COPYRANGE argument specifies the range in the worksheet or datasheet to be used for the records PTReportGen will skip or repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The range 2 4 means entire rows The range means 2 entire columns The default range is the area that includes all cells for the records For the fixed table report PTReportGen will skip the range for each record For the variable table report it will insert the blank range for each record The COPYRANGE argument is similar to the Range It will copy the original range to the range where data will be filled for each record If there is no range or copyrange argument the default for the fixed table report is range and the default for the variable table report is copyrange B The GROUP argument specifies the group of report The grouplist is the 66 list of data source fields separated by the character You can identify a field using the name or 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 GROUPRANGE argument follows the
47. fault range is B3 E5 you must specify a range explicitly 59 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 rangecount 5 You made 5 blank ranges in the slide 11 PTReportGen executes the SQL statement and gets data from data source First it groups the data Second it duplicates slides according to the number of records Next it deletes the extra rows in the last slide Finally it puts the data into the report by records 6 2 Form Report Function The FORM REPORT function executes a SQL statement to get data from data source and puts data into shapes or text boxes in the report file Syntax Report sqlstatement Arguments TYPE reporttype SLIDE s ide CELL celllist GROUP grouplist IMAGE fieldlist NODATA nodataoption CONNECT datasource The TYPE argument specifies the report type form means a form report The SLIDE argument identifies a slide in the report template The slide is the index number of slide The index number starts at 1 If the index number is less than 0 it represents the position from the end of 60 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
48. function processed 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 ProductlD 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 shape or text box using the add in name ppa The GROUP argument specifies the group of 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 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 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 or index number of field but not simultaneously In data source you stored the path and file name of picture not the picture The file path can be a relative path an absolute path or a URL If itis a relative path the base path is the path of report template fi
49. he 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 38 6 Press OK button to confirm the changes press Cancel button to discard the changes 5 5 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 the label of 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 For more detailed information about functions see Function Reference in this document You can use comments in text A comment is the characters followed by any sequence of characters including new lines followed by the characters You cannot nest comments 5 6 Running PTR Files You can run a PTR file to generate a rep
50. ions Each function is begin with the character Syntax functionno functionname arguments sqlstatement The functionno is the label of function The functionname represents a function The arguments define various properties for the function An argument takes the form Name Value The argument value can be delimited by single or double quotes The sqlstatement is a SQL statement You can use comments in SQL section A comment is the characters followed by any sequence of characters including new lines followed by the characters You cannot nest comments 9 2 Data Source Section The Data Source section contains information how to connect to data sources Name1 name1 Name2 lt name2 gt Name 10 lt name 10 gt These settings specify the names of data sources you want to connect to Name1 specifies the name of the first data source Name2 specifies the name of the second data source You can define up to 10 data sources in one PTR file You can make a connection to a data source using an ODBC data source name or a connection string Even if you use a connection string to 81 make a connection you should define a name that you can reference in functions User1 lt username1 gt User2 lt username2 gt User10 lt username 10 gt These settings specify the user names If you use an ODBC data source name to make a connection you should define user name and password If you
51. it SQL statements to data sources 40 5 7 Sorting Grouping and Totaling 5 7 1 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 EmployeelD 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 5 7 2 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 the total first using a fixed table A report function before you use the variable table report function Because the cell address of the total field will change after you use the variable table report function 5 7 3 Grouping data and subreports Grouped data i
52. ixed table report defined in the report template Top N Employees for Sales Rank Employee Name Quantity Amourt of Total oe fo 0 00 2 samo 0 00 3 smo 0 00 eto 0 00 _5 0 wmo 0 00 53 The fixed table report generated in the report Top N Employees for Sales Rank Employee Name Quantity Amournt of Total Nancy Davolio 24 827 45 23 5896 Laura Callahan 20 728 13 19 7796 Janet Leverling 16 360 12 15 60 ET Andrew Fuller 13 937 64 13 29 EN Margaret Peacock 8 298 45 7 9196 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 fifth 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 It puts the data into the report by records Example 2 Variable Table Report The following function will makes the report Customer List F1 Report slide 2 type var cell A2 pagebreak 19 rangecount 2 SELECT CompanyName CityName 54 CountryName ContactName FROM Customers Cities Cou
53. le 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 CONNECT argument specifies the connection to a data source The 61 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 sqistatement is SQL statement such as a SELECT statement There are not RANGE and PAGEBREAK arguments PTReportGen 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 FROM Suppliers Countries Cities WHERE Suppliers CityCode Cities CityCode AND Suppliers CountryCode Cities CountryCode AND Suppliers CountryCode Countries CountryCode ORDER BY CompanyName Result 62 The form report defined in the report template Home Page The form report generated in the report 63 G day Mate eure G day Mate nema
54. luation period you must purchase it Use Each Licensed Copy of this Software may either be used by a user who uses the software personally on one or more computers or installed on a single computer used nonsimultaneously by multiple people but not both This is not a concurrent use license If you install the Software onto a multi user platform server or network each and every individual user of the Software must be licensed separately 97 Distribution You may copy the evaluation version of this software and documentation as you wish and give exact copies of the original evaluation version to anyone and distribute the evaluation version of the software and documentation in its unmodified form via electronic means You are specifically prohibited from charging or requesting donations without permission from the author You may not modify copy share distribute re sell transfer or sub license the full version of this software except that you may make copies for archive purposes only Any such unauthorized use shall result in immediate and automatic termination of this license Edition There are 2 types of editions issued for PT ReportGen 1 PTReportGen Standard Edition User can execute no more than 50 SQL statements in one report processing 2 PTReportGen Professional Edition User can execute SQL statements unlimitedly in one report processing 11 2 Technical Support If you encounter any problems in usage of PTRe
55. ment Example Microsoft Excel Chart The following function makes the chart Sales by Categories F3_3 CHART slide 3 cell A2 rangecount 2 SELECT c CategoryName Sum d UnitPrice d Quantity 1 d Discount FROM Orders o OrderDetails d Products Categories c WHERE o OrderlD d OrderlD AND d ProductID p ProductID AND p CategoryID c CategorylD 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 68 Esiste pestes EC Rn Amount 27761 57499 10773 26999 22877 17998 13685 32499 3325 399995 4083 659998 13031 2 9316 544988 FN Seafood The chart generated in the report ClBeverages Condimerts O Confedions ODairy Products Grains Cereals Mea Poultry Produce OSeafood 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 It is a variable table report 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 r
56. n 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 For a form report write a formatting expression in the shape or text box 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 picture is PTReportGen will return PTReportGen will return Error if it does not find the file of picture For more detail information about pictures refer to the samples employee profile ptr product catalog ptr within PT ReportGen 5 9 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 th
57. nction as follows F1 Report slide 1 cell A2 pagebreak 12 rangecount 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 1 The rangecount argument specifies the number of ranges in the report template You have defined two blank ranges in the report template One row is one range If you hope that the format of the last row column border is different from the others you can define two ranges in the report template 2 The pagebreak argument specifies the number of records in one slide You want a full customer list and remove the specified condition in the WHERE 23 clause But a full customer list is too long in one slide So you can put records into more slides using pagebreak argument In this case one slide contains 12 records 3 4 10 Generating the report again 1 Save the PTR file 2 Run the PTR file to generate the report 3 Open the report view and check the report The report should now look similar to the following Customer List fom f a wm Blauer See Delikatessen Gemany Hanna Moos Blondel p re et fils Strasbourg Fr d rique Cite aux uasa uan iem uas fae 17 Now you have created a report 3 5 Samples After PTRe
58. nerate 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 961 lt template txt template ptr PPTReport template ptr C 961 48 Chapter 6 Function Reference 6 1 Table Report Function The TABLE REPORT function executes a SQL statement to get data from data source and puts data into a table in the report file Syntax Report sqlstatement Arguments TYPE reporttype SLIDE s ide TABLE table FILLORDER fillorder CELL celllist RANGE range GROUP grouplist GROUPRANGE grouprange IMAGE fieldlist RANGECOUNT rangecount PAGEBREAK pagelength NODATA nodataoption CONNECT datasource m The TYPE argument specifies the report type Possible values are fix or var fix means a fixed table report and var means a variable table report Default is var B The SLIDE argument identifies a slide in the report template The slide is the index number of slide The index number starts at 1 If the index 49 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
59. noon and 11 59 P M Tt For locales that use a 12 hour clock displays an uppercase AM with any hour before noon displays an uppercase PM with any hour between noon and 11 59 P M For locales that use a 24 hour clock displays nothing Y Displays the year number 0 9 without leading zeros Yy Displays the year in two digit numeric format with a leading zero if applicable Yyy Displays the year in four digit numeric format Yyyy Displays the year in four digit numeric format Z Displays the timezone offset without a leading zero for example 8 Zz Displays the timezone offset with a leading zero for example 08 Zzz Displays the full timezone offset for example 08 00 96 Chapter 11 License and Support 11 1 License Your Agreement to This License You should carefully read the following terms and conditions before using installing copying or distributing this software Your use installation copying or distribution of PTReportGen indicates your acceptance of this agreement License NO WARRANTY PTReportGen IS DISTRIBUTED AS IS NO WARRANTY OF ANY KIND IS EXPRESSED OR IMPLIED THE AUTHOR WILL NOT BE LIABLE FOR DATA LOSS DAMAGES LOSS OF PROFITS OR ANY OTHER KIND OF LOSS WHILE USING OR MISUSING THIS SOFTWARE Evaluation PTReportGen is not free software You may use this software for evaluation purposes without charge for a period of 30 days If you use this software after the 30 day eva
60. ns 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 5 1 4 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 34 3 Press Save button 5 2 Configuring Files 5 2 1 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 report file can be different from the template file The log file records the log information in the report generating The file path can be a relative path or an absolute path If it is a relative path the base path is the path of PTR file In the paths and names of report file template file and log file you can use parameters For detailed information about parameters refer to Configuring Parameters in this document 5 2 2 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 fil
61. ns 11 3 4 6 RUNNING PER files teen ied RP amet gt Ee eis 12 3 4 7 Opening teport ss a e a e P dr eb e to baee br 12 3 4 8 Modifying th report template ese UE Re Utt aperiens 12 3 4 9 Modifying the eene nene 13 3 4 10 Generating the report again suere i reete eer Reges 14 by Vgl E 14 CHAPTER 4 REPORT TEMPLATES eeeeeee seen ense enne ta tinea sins tastes tuse tasse aestus seasons 16 1 ABOUT REPORTS 16 42 ABOUT REPORT TEMPLATES eire herr Hee ed ripe recs 16 4 3 POWERPOINT BASIC CONCEPTS eese eene E EEr o REE EE 16 e 16 43 258 ides Ss RS RUP qudm RE 17 4 3 3 Layout idet RERBA ee ee Oe ipee EEUU 17 4 34 Headers and Fo0ters i2 odia e aee a re eR e ER RR EFE Een nt 17 4 3 5 Formaltihigixza si eid EA Ree e exa ve UAR du eV ad vena Een 17 4 3 6 Sh pes S dete de etras dete dee ec ite bn oet dete 18 d PICIMFOS scie toten e E RENS Reti ier er e ee E e debe tees 18 4 3 8 rises e 19 BSD CHAVIS EA 19 4 3 10 Sound Music Video ANd Voice eene hehehe nennen nennen nennen 20 AA TABLE REPORTS 5 eth DU IEEE ERERUN ON 20 44 1 About Table nena si aana 20
62. ntactName field into D2 And then it will fetch the next record put them into A3 B3 C3 and D3 3 4 6 Running a PTR file 1 On the Report menu click Run the Run Report dialog box appears 2 Press Start button to run the PTR file 3 PTReportGen will generate a report 4 After the status is Done click Close button 3 4 7 Opening a report 1 On the File menu click Open Report File to open the report you have generated You can view and check the report 2 On the File menu click Open Log File to open the log file that recorded the log information in the report generating You can check the log 3 Close the report file and the log file 3 4 8 Modifying the report template 1 On the File menu click Open Template File to open the report template 2 Change the width of columns It is very useful to copy some sample data from the report file into the report template for formatting 3 Select the second row and insert a row to the table 4 Change the border width of the first row Select the first row click Table on the Format menu and then click the Borders tab Change the border width and press OK button The border of the first row is different from the border of the other rows The report template you have made is as follows 212 Customer List Cora 5 Save and close the template file 3 4 9 Modifying the function Now you want a full customer list In the editor windows modify the fu
63. ntries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName CityName CountryName Result The variable table report defined in the report template Customer List Customer Name City Country Contact Name The variable table report generated in the report 55 Customer List Customer Country Contact Altec F ate rkkte Beril Gemary Marla Arce rs Ata Trillo Em pare dados y telados M xko D F Mexko Aybib Moreiro M xko D F Mexiko Abi Mores Arourdthe Lorca Uk Thomas Hardy Berg laade sabbkip Lue Swed i Berglind Blater See Dellkatezze v Gemany Moos Bkadelp re ett Strasbourg France Fr d rk te C fe aux Bolkio Comidas preparada Spalt Marti Sommer Boi app Marsellk France Laure Lebihan Bottom Dollar Market Tanase Canada Eltzabetk Llicola B s Beverages Londo Uk As bworth para Ik var Bue Alres Argertha Patricio Simpson Cento comerctal Moctezima M xko D F Mexko Frarckoo Chang ChopsreyChhese Switze yag Wang So Path Brall Pedro Consolklae d Hokllags Lordot Uk Eltzabetk Brows Dk Waideride Kab Stuttgart Gemary Pie Miler DrackeiblitDelkatzs es Aacher Gemary Damonck enter Nares France Janine Labrune Remarks 1 The
64. olored 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 18 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 ar
65. on the New Data Source dialog box appears In the Name box type Report Sample press OK button 4 On the Configuration dialog box press OK button 10 3 4 4 Inputting a function In the editor windows input a function as follows F1 Report slide 1 cell A2 SELECT CompanyName CityName CountryName ContactName FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode AND CountryName USA ORDER BY CompanyName CityName CountryName Please note the WHERE clause It passes only records of customers in USA You can test the SQL statement in a query tool such as Microsoft Access or Microsoft Query 3 4 5 Understanding the function Before going any further let us understand this function 1 The Report function will execute the SQL statement get data from data source and put data into the report 2 The slide argument identifies a slide and the value 1 is the index number of the slide So it is the first slide 3 You do not identify a table So PTReportGen will put data into the first table in the slide 4 The cell argument specifies the cells that the first record will be filled into 11 The value is A2 So PTReportGen will fetch the first record put the value of CompanyName field into A2 the value of CityName field into B2 the value of CountryName field into C2 and the value of Co
66. ormats Character Description None Display the number with no formatting 0 Digit placeholder Displays a digit or a zero If the expression has a digit in the position where the zero appears in the format string display it otherwise displays a zero in that position If the number has fewer digits than there are zeros on either side of the decimal in the format expression displays leading or trailing zeros If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression rounds the 88 number to as many decimal places as there are zeros If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression displays the extra digits without modification Digit placeholder Displays a digit or nothing If the expression has a digit in the position where the character appears in the format string displays it otherwise displays nothing in that position This symbol works like the 0 digit placeholder except that leading and trailing zeros aren t displayed if the number has fewer digits than there are characters on either side of the decimal separator in the format expression Decimal placeholder The decimal placeholder determines how many digits are displayed to the left and right of the decimal separator
67. ort in Microsoft PowerPoint format 39 PTReportGen supports Windows mode and command line mode 5 6 1 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 5 6 2 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 subm
68. ou don t have added data sources please add data sources first Run ODBC Administrator you can add a new data source For detailed information about configuring ODBC refer to ODBC Administrator Help 2 Make a report template file Create a report template file using Microsoft PowerPoint The report template file is a Microsoft PowerPoint presentation For detailed information about report template refer to Report Templates in this document 3 Create a PTR file Create a PTR file with a ptr extension using PTReportGen There are two steps to create a PTR file 1 Configure the report Define the names of data sources the name of report template file the name of report file and the name of log file If you want to use parameters in SQL statements define these parameters 2 Write functions Write functions and SQL statements that specify how to get data from data um sources and how to put data into the report For detailed information refer to Reporting with PTReportGen in this document 4 Run the PTR file Run the PTR file to generate a report in Microsoft PowerPoint presentation For detailed information about running report refer to Running PTR Files in this document 3 4 My First Report The following tutorial has been designed to guide you to create your first report In this tutorial you will get an introduction to the program as you create a Customer List report The Customer List is one of the most
69. oup two and the range of 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 detail you must add a pagebreak by group and the length of range can not be more than 1 row column 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 or index number of field but not simultaneously In data source you stored the path and file name of picture not the picture The file path can be a relative path an absolute path or a URL If itis a 25 relative path the base path is the path of report template file The RANGECOUNT argument specifies the number of blank range which you defined the report template It is valid when the type is var One means one blank range you defined and two means two ranges Default is 1 B The PAGEBREAK argument specifies the page breaks The unit of page length is r or g means record g1 means group 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 19 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 gro
70. peration that you undid Finds the specified text Goes to specified line or function in the document Configures the file names data sources and parameters vij 4 E 2 5 0i Runs the PTR file to generate a report Starts the online help system Buy PTReportGen immediately 7 8 Shortcut Keys Shortcut Keys Commands Ctrl N Creates a new PTR file Ctrl O Opens an existing PTR file Ctrl S Saves an opened PTR file using the same filename Ctrl Z Undo previous editing operation Ctrl Y Redo an editing operation that you undid Ctrl X Deletes text from the document and moves it to the clipboard Ctrl C Copies text from the document to the clipboard Ctrl V Pastes text from the clipboard into the document Delete Deletes the selection Ctrl A Selects the entire text Ctrl F Finds the specified text F3 Finds the next matching text Ctrl H Replaces specific text with different text Ctrl G Goes to specified line or function in the document F2 Configures the file names data sources and parameters F5 Runs the PTR file to generate a report 1 Starts the online help system 75 Chapter 8 Hints and Tips You can run PTReportGen from the command line The format is pptreport ptr file name c d u1 user1 p1 pwd1 pa2 For example pptreport c pptreport monthly
71. portGen and need the technical support Goto our support web site at http www jzsoft com support htm Send email to support ljzsoft com 98
72. portGen is installed some sample reports are installed too Use 14 these reports to learn PTReportGen The sample reports can be changed to adapt to your own needs The sample reports include a sample database some report template files pptx and PTR files ptr They are located in the Application Data LJZsoft under All Users or your profile folder Directory Description commonappdata LJZsoft Common Sa_ Contains the sample database mpleDatabase Sample mdb commonappdata LJZsoft PTReportGe Contains the report template files n Samples pptx and the PTR files ptr commonappdata LJZsoft PTReportGe Contains the report files pptx n Samples Report generated by PTReportGen commonappdata LJZsoft PTReportGe Contains the log files created by n Samples Log PTReportGen during generating report files commonappdata is the path to the Application Data folder under All Users The Application Data folder is usually at Windows XP C Documents and Settings All Users Application Data Windows Vista or later C ProgramData 15 Chapter 4 Report Templates 4 1 About Reports The report generated by PTReportGen is a Microsoft PowerPoint presentation The layouts formats and styles of report are defined by a report template and the data of report are got from databases such as Oracle DB2 4 2 About Report Templates To make a report using PTReportGen you should cr
73. rameters 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 46 You can use the parameters Database Table and Column SQL statements For example USE Database or DATABASE Database SELECT Column FROM Table Example Use parameters in the path and name of report file and log file 1 Defining a parameter Define a parameter as follows Name CustomerlD Title Customer ID Default C000001 2 Using a parameter ReportFileName reportWeport CustomerlD pptx LogFileName logWeport CustomerlD log or ReportFileName report Customer D report pptx LogFileName log Customer D report log 5 10 Programming 5 10 1 Making PTR files programmatically 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 47 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 ge
74. rmats Unlike in earlier versions of Visual Basic these format characters are case sensitive Character Description Time separator In some locales other characters may be used to represent the time separator The time separator separates hours minutes and seconds when time values are formatted The actual character that is used as the time separator in formatted output is determined by your application s current culture value Date separator In some locales other characters may be used to represent the date separator The date separator separates the day month and year when date values are formatted The actual character that is used as the date separator in formatted output is determined by your application s current culture Used to indicate that the following character should be read as a 94 single letter format without regard to any trailing letters Also used to indicate that a single letter format is read as a user defined format See what follows for additional details D Displays the day as a number without a leading zero for example 1 Dd Displays the day as a number with a leading zero for example 01 Ddd Displays the day as an abbreviation for example Sun Dddd Displays the day as a full name for example Sunday M Displays the month as a number without a leading zero for example January is represented as
75. rmatting tables shapes charts pictures and graphics drawing headers and footers and more m Manipulating Word reports with OpenXML SDK Open XML is an open file format for the core document oriented Office applications It facilitates document creation and manipulation in server environments where you do not need to install the Office client applications W Accessing to data using SQL PTReportGen 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 W Creating reports without programming experience You know how to use Microsoft PowerPoint and how to write SQL it is enough It does not require programming to create reports W Connection to databases using ADO Net Using ADO NET to connect to databases PTReportGen is independent of database systems It can access to almost all of databases such as Oracle DB2 Sybase Informix Microsoft SQL Server Teradata MySQL Microsoft Access and dBase through OLE DB and ODBC Supporting multi databases in one report PTReportGen supports multi databases in one report You can get data from some different databases such as Oracle DB2 and Microsoft SQL Server and put these data into one report W Generating
76. rom 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 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 30 8 9 as 8 90 displays 123 as 123 00 and displays 0 as 0 00 4 5 5 Formatting Pictures As same as the table repo
77. rs Name1 lt name1 gt Name2 lt name2 gt Name 10 lt name 10 gt These settings specify the names of parameters Name1 specify the name of the first parameter Name2 specifies the name of the second parameter You can define up to 10 parameters in one PTR file Title 1 lt title1 gt Title2 lt title2 gt Title 10 lt title 10 gt These settings specify the titles of parameters Title1 specifies the title of the first parameter Title2 specifies the title of the second parameter 84 Default1 lt default1 gt Default2 lt default2 gt Default10 lt default10 gt These settings specify the default values of parameters Default1 specifies the default value of the first parameter Default2 specifies the default value of the second parameter 85 Chapter 10 Format Expressions To display values in formatting string you should write formatting expressions into a cell shape or text box in the report template file PT ReportGen gets the text and outputs the value using it as the format expression In fact PTReportGen calls the format function in Visual Basic For more information about format refer to Format Function in Visual Basic for Applications Reference 10 1 Formats for Numeric Values 10 1 1 Different Formats for Different Numeric Values A user defined format expression for numbers can have from one to three sections separated by semicolons If the Style argument of the Format function
78. rt you can insert pictures into your form report too You should store the path and name of graphics files in the database and identify the image fields in the report function PTReportGen will read the graphics files and put them at the position of 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 picture is set to 100 points H50 means that the height of picture is set to 50 points The default means the original size If you just specify the width or height of picture not both PTReportGen will retain the original proportions of picture when PTReportGen resize it 4 6 Charts 4 6 1 About Charts Charts are visually appealing and make it easy for users to see comparisons patterns and trends in data Chart tools are fully integrated with Microsoft Office 2007 For Microsoft PowerPoint 2007 or later the default chart software is Microsoft Excel Charts that you create will be embedded in Microsoft PowerPoint and the chart data is stored in an Excel worksheet that is incorporated in the 31 PowerPoint file 4 6 2 Creating a
79. ryCode ORDER BY CompanyName Result The variable table report defined in the report template 57 Customer Profile Curtomer Contact Name Phone Fax The variable table report generated in the report 58 Curtomer Contact Name Phone Fax A Alteds Fulleriz e Mala rder Phone 030 007 4321 Obere 21 57 Fax 030 0076545 Berlin Gemary 12208 Ana Thillo Emparedado z y lelador na Trillo Phone 5 555 47 29 de la Cons thiclon 2222 Fan 5 555 37 45 co D F Mexico 05021 Moreno Tarena nlonio Moreno Phone S 555 3932 Malsero 2312 Fax D F Mexico 05023 Around Hom Thoma Handy 17 1 555 7788 120 Hancuer Sy Fax 07 0 555 6750 London Uk WAL IDP b Berglursdz Clits Ina Berglunsd Phone O82 1 12 34 65 Bergiumruiyen 8 Fax 0921 12 3467 Lule s Sweden 3 958 22 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 The default type is var It is a 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 de
80. s 1 and numbers smaller than 500 are displayed as 0 Two adjacent thousand separators in this position scale by a factor of 1 million and an additional factor of 1 000 for each additional separator Multiple separators in any position other than immediately to the left of the decimal separator or the rightmost position in the string are treated simply as specifying the use of a thousand separator In some locales a period is used as a thousand separator The actual character used as the thousand separator in the formatted output depends on the Number Format recognized by your system Thus you should use the comma as the thousand separator in your formats even if you are in a locale that uses a period as a thousand separator The formatted string will appear in the format correct for the locale For example consider the three following format strings 90 0 which uses the thousands separator to format the number 100 million as the string 100 000 000 0 which uses scaling by a factor of one thousand to format the number 100 million as the string 100000 0 which uses the thousands separator and scaling by one thousand to format the number 100 million as the string 100 000 Time separator In some locales other characters may be used to represent the time separator The time separator separates hours minutes and seconds when time values are formatted The actual character
81. s 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 PTReportGen 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 PTReportGen 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 PTReportGen 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 20 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 PTReportGen executes a SQL statement the data values from data source will be filled into these cells ES 3 The
82. s 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 To group data in a report you should use GROUP argument in the REPORT function For more detail information refer to Table Report Function and Form Report Function in this document Using the feature of grouping data you can make sub reports within a report A sub report would typically be used to perform one to many lookups such as Customer Order OrderDetails To make sub reports within the main report 1 Write a JOIN SQL statement to get data from two or more tables For example you can join Customers Orders and OrderDetails tables 2 Use GROUP argument in the REPORT function For more detail information refer to the samples customer profile ptr product catalog ptr and sales detail ptr within PTReportGen 5 7 4 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 42 group and insert results into a temporary table 2 If you have the different kinds of summaries repeat the
83. s long time format typically includes hours minutes seconds For example 11 07 31 AM Short Time or t Displays a time using your current culture s short time format For example 11 07 AM Displays the long date and short time according to your current culture s format For example Wednesday March 12 2008 11 07 AM Displays the long date and long time according to your current culture s format For example Wednesday March 12 2008 11 07 31 AM Displays the short date and short time according to your current culture s format For example 3 12 2008 11 07 AM Displays the month and the day of a date For example March 12 Formats the date according to the RFC1123Pattern property For example Wed 12 Mar 2008 11 07 31 GMT The formatted date does not adjust the value of the date and time You must adjust the Date Time value to GMT before calling the Format function 93 Formats the date and time as a sortable index For example 2008 03 12T11 07 31 Formats the date and time as a GMT sortable index x For example 2008 03 12 11 07 31Z Formats the date and time with the long date and long U time as GMT For example Wednesday March 12 2008 6 07 31 PM T Formats the date as the year and month For example March 2008 10 2 2 User Defined Date Time Formats The following table shows characters you can use to create user defined date time fo
84. sales ptr c 199605 PTReportGen can be scheduled with Windows Scheduled Tasks or other tools The process of generating reports can be fully automated periodically or on events PTReportGen comes with a sample database Sample mdb and some sample reports You can use them when learning the program To use the sample reports you must add a data source named Report Sample to specify the sample database To make a report template you can use some sample data It is very useful especially for formatting After you have made the report template you delete the sample data For a table report you can format the value from data sources with a format expression You should write a format expression into a data cell in the report template file first PTReportGen 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 For a form report you can reference a shape or text box by its name You can find a PowerPoint add in name ppa under the PTReportGen s working 76 directory that can name an object in a slide For a form report you can format the value from data sources with a format expression too PTReportGen will get the text of the object as a format expression and output the value using the format expression You can define the different formats and colors for positive values negative values and zeros An irregular table does not ha
85. table in a slide The table is the index number of 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 fills data Possible values are row or col row means to fill data by rows 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 It 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 or COPYRANGE argument specifies the range in the table to be used for the records PTReportGen will skip or repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The range 2 4 means 3 entire rows The range A B means 2 entire columns The default range is the area that includes all cells for the records For the fixed table report PTReportGen will skip the range for each record For the
86. uprange is not same as the range of detail you must add a pagebreak by group and the length of range can not be more than 1 row column The NODATA argument specifies an option when no data are returned from data source It is valid when the type is var Possible values are delrange deltable or delslide delrange means to 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 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 B The sqistatement is SQL statement such as a SELECT statement Example 1 Fixed Table Report The following function makes the report Top 5 Employees for Sales 52 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 Employees e WHERE o OrderlD d OrderlD AND d ProductID p ProductlD AND o EmployeelD e EmployeelD AND YEAR o OrderDate 1996 AND MONTH o OrderDate 04 GROUP BY e FirstName e LastName ORDER BY 3 DESC Result The f
87. use a connection string to make a connection PTReportGen will ignore the setting User1 specifies the user name of the first data source User2 specifies the user name of the second data source They are optional settings If defined default user and password in ODBC data source you may not define them Password1 lt password1 gt Password2 lt password2 gt Password10 lt password 10 gt These settings specify the user passwords If you use an ODBC data source name to make a connection you should define user name and password If you use a connection string to make a connection PT ReportGen will ignore the setting Password1 specifies the password of the first data source Password2 specifies the password of the second data source They are optional settings If defined default user and password in ODBC data source you may not define them ConnectionString 1 lt connectionstring 1 gt ConnectionString2 lt connectionstring2 gt 82 ConnectionString 10 lt connectionstring 10 gt These settings specify the connection strings If you defined a connection string PTReportGen will make a connection to the data source using the connection string and ignore the settings of the name user and password But you must define a name that you can reference in functions ConnectionString1 specifies the connection string of the first data source ConnectionString2 specifies the connection string of the second data SOUICE They
88. variable table report it will insert the blank rows columns for each record or copy slides for records The COPYRANGE argument is similar to the 50 Range It will copy the original range to the range where data will be filled for each record If there is no range or copyrange argument the default for the fixed table report is range and the default for the variable table report is copyrange The GROUP argument specifies the group of 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 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 GROUPRANGE argument follows the GROUP argument and specifies the range of 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 group should contain the range of details and the area that includes all cells for this group You reference a group range like 2 4 or B2 D5 For example there are two groups the range of group one contains all cells for the group one and the range of group two and the range of group two contains all cells for the gr
89. ve the same number of cells for each row or column It does make it harder to process the document In an irregular table you have some difficulty to reference a cell and an error may occur when you try to work with some rows or columns You can create a chart using Microsoft Excel To work with charts created in Excel you must have Excel installed To create a chart in the report template file you can use some sample data Using sample data you can set the various chart options After you have made the report template you delete the sample data You can edit a PTR file ptr with a text editor such as Notepad If you associate PTReportGen with the file extension ptr a PTR file with the extension ptr will open in PTReportGen when you double click the file The information File Extension ptr Action open ee Application C Program Files LJZsoft pptreport exe 1 For the report template file report file and log file it is possible to give a relative path If it is a relative path the base path is the path of the PTR file In the SQL statements you can use parameters To use parameters you must define them first In the paths and names of the report file template file and log file you can use parameters To use parameters you must define them first You should be careful to define a unique name for each parameter because PTReportGen will replace all strings that are the same as

PTReportGen User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents