Users Guide InfoMaker® 11.
DOCUMENT ID: DC37789-01-1150-01 LAST REVISED: September 2008 Copyright © 2008 by Sybase, Inc. All rights reserved. This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement. To order additional documents, U.S.
Contents About This Book ......................................................................................................................... xxi PART 1 THE INFOMAKER ENVIRONMENT CHAPTER 1 Working with InfoMaker.................................................................. 3 About InfoMaker ............................................................................... 4 What you can do using InfoMaker.................................................... 5 Reports ..............................
Contents Working with tools .......................................................................... Using property pages ..................................................................... Using toolbars ................................................................................ Toolbar basics ......................................................................... Drop-down toolbars ................................................................. Controlling the display of toolbars ...........
Contents Opening and previewing objects ............................................. Copying, moving, and deleting objects.................................... Setting the root ........................................................................ Moving back, forward, and up one level.................................. Modifying comments ............................................................... Deleting libraries......................................................................
Contents CHAPTER 4 Viewing row information ........................................................ Importing data ....................................................................... Printing data .......................................................................... Saving data ........................................................................... Creating and executing SQL statements ..................................... Building and executing SQL statements ...............................
Contents CHAPTER 6 Users Guide Selecting a data source................................................................ Using Quick Select ....................................................................... Selecting a table .................................................................... Selecting columns ................................................................. Specifying sorting criteria ...................................................... Specifying selection criteria ...............
Contents CHAPTER 7 viii Saving data in an external file ...................................................... Saving the data as PDF ........................................................ Saving the data in HTML Table format.................................. Working with PSR files .......................................................... Modifying general report properties ............................................. Changing the report style ......................................................
Contents CHAPTER 8 Users Guide Resizing controls in a report .................................................. Aligning controls in a report ................................................... Equalizing the space between controls in a report ................ Equalizing the size of controls in a report.............................. Sliding controls to remove blank space in a report................ Positioning controls in a report .....................................................
Contents Defining a validation rule in the Form painter........................ 292 How to maintain extended attributes ............................................ 294 CHAPTER 9 CHAPTER 10 x Filtering, Sorting, and Grouping Rows...................................... Filtering rows................................................................................ Sorting rows ................................................................................. Suppressing repeating values ....................
Contents CHAPTER 11 CHAPTER 12 Users Guide X1, X2.................................................................................... Y ............................................................................................ Y1, Y2.................................................................................... Specifying colors .......................................................................... 342 342 343 343 Using Nested Reports ........................................................
Contents Exporting to XML.......................................................................... Setting data export properties ............................................... Importing XML .............................................................................. Importing with a template ...................................................... Default data import ................................................................ Tracing import ..........................................................
Contents CHAPTER 15 Working with TreeViews............................................................. TreeView presentation style ......................................................... Creating a new TreeView report .................................................. TreeView creation process .................................................... Creating a TreeView report ................................................... Adding and deleting TreeView levels ...........................................
Contents CHAPTER 19 CHAPTER 20 xiv Master/Detail One-To-Many forms ........................................ Master/Detail Many-To-One forms ........................................ Creating and saving forms ........................................................... Creating basic forms ............................................................. Creating a master/detail form ................................................ Defining data so that a form can update a database.............
Contents Equalizing the size of controls in the Form painter................ Undoing changes in the Form painter ................................... Sliding controls in a form ....................................................... Modifying general form properties................................................ Specifying a title for a form .................................................... Setting colors for a form ........................................................
Contents CHAPTER 22 Deploying Your Application........................................................ About deploying applications ....................................................... Installing InfoMaker runtime files.................................................. Making the data source available................................................. Installing native database interfaces ..................................... Installing ODBC and system files ..........................................
Contents Asc ........................................................................................ AscA ...................................................................................... ASin....................................................................................... ATan...................................................................................... Avg ........................................................................................ Bitmap ..................................
Contents IsExpanded ........................................................................... IsNull ..................................................................................... IsNumber............................................................................... IsRowModified....................................................................... IsRowNew ............................................................................. IsSelected....................................................
Contents Real ....................................................................................... RelativeDate .......................................................................... RelativeTime ......................................................................... Replace ................................................................................. ReplaceA............................................................................... RGB.....................................................
Contents AP PE ND IX B The Extended Attribute System Tables ..................................... About the extended attribute system tables ................................. The extended attribute system tables .......................................... Edit style types for the PBCatEdt table ........................................ CheckBox edit style (code 85)............................................... RadioButton edit style (code 86) ...........................................
About This Book Audience This book is for anyone who is using InfoMaker® to work with data. Although the book does not assume you have knowledge about any particular topic, having some familiarity with relational databases and SQL is helpful. Consult books on these topics as needed. InfoMaker works with many DBMSs This book describes how to use InfoMaker using a SQL Anywhere™ database for examples. You use InfoMaker with many different DBMSs, as described in Connecting to Your Database.
Other sources of information Use the Sybase Getting Started CD, the SyBooks CD, and the Sybase Product Manuals Web site to learn more about your product: • The Getting Started CD contains release bulletins and installation guides in PDF format, and may also contain other documents or updated information not included on the SyBooks CD. It is included with your software.
PART 1 The InfoMaker Environment This part introduces you to InfoMaker and describes how to work in and manage its environment. It also describes how to work with databases, tables, views, and extended attributes. Access to the Database painter To have access to the Database painter, select the InfoMaker Database Write Option in the setup program.
CH A PTE R About this chapter Contents Before you begin Users Guide 1 Working with InfoMaker This chapter describes the basics of working with InfoMaker and its painters.
About InfoMaker About InfoMaker InfoMaker is a reporting tool InfoMaker is a powerful and easy-to-use reporting tool that lets you query databases and create sophisticated and effective custom reports of data. When optional painters are installed, it also lets you work with data in a database. InfoMaker is a personal data assistant InfoMaker lets you work with data in many ways—always with no programming required.
CHAPTER 1 Working with InfoMaker If you do not see all the available painters If you installed InfoMaker from the PowerBuilder Enterprise setup program, you, or others in your organization who install and set up your software, chose to install a full set of painters (typical install) or a minimal set (compact install). The minimal combination includes the Report painter, the Query painter, and the Library painter.
What you can do using InfoMaker Here are a few sample reports: Freeform report Label report 6 InfoMaker
CHAPTER 1 Working with InfoMaker Group report Users Guide 7
What you can do using InfoMaker TreeView report Queries Reports and forms both use data from your database. In InfoMaker, you use the Query painter to define queries that specify your data requirements. When you want to create a new report or form using that data, you can simply use the query as the source of your data, without redefining the data.
CHAPTER 1 Working with InfoMaker Forms In InfoMaker, you use the Form painter to create and run interactive forms to view and change data. InfoMaker provides four form styles: Freeform Master/Detail One-To-Many Grid Master/Detail Many-To-One PowerBuilder® developers in your organization can create custom form styles for you to use.
What you can do using InfoMaker Master/detail many-to-one form Data pipelines In InfoMaker, you use the Data Pipeline painter to create and execute data pipeline definitions to pipe data from one or more source tables to a new or existing destination table.
CHAPTER 1 Working with InfoMaker Applications You can use your reports, forms, and data pipelines within the InfoMaker environment; you can also bundle them in a fully functional databasemaintenance and reporting application that can be used outside the InfoMaker environment. You create an application by using the Library painter to create an executable file.
The InfoMaker environment The InfoMaker environment When you start InfoMaker the first time In InfoMaker, you always work within the context of a library. The first time you start InfoMaker, the default library is tutor_im.pbl, which contains sample objects based on the EAS Demo DB. If you want to create a library of your own for storing new objects, click the New button on the PowerBar and use the library wizard on the Library tab page.
CHAPTER 1 Working with InfoMaker About the PowerBar What it is The PowerBar is the main control point for working in InfoMaker. From the PowerBar you can create new objects and libraries and open existing objects.
Working with libraries About wizards InfoMaker provides you with wizards for easy creation of libraries and reports. ❖ To access wizards: 1 Click the New button in the PowerBar, or select File>New from the menu bar. 2 In the New dialog box, select the tab page for the wizard you need. This tab page Has icons for Library Object A wizard for creating new libraries Object wizards for creating reports in specific presentation styles Working with libraries You can create a new library.
CHAPTER 1 ❖ Working with InfoMaker To set the current library: 1 Click the Select Library button in the PowerBar. 2 On the Browse or Recent tab pages of the Select Library dialog box, select the library you want: In the Browse tab page, you can navigate to a library or type the absolute or relative path for a library. About creating a new library You can also use the New tab page in the Select Library dialog box to create a new library and automatically set the current library to that new library.
Working with objects Working with objects In InfoMaker, you can: • Create new objects • Open existing objects • Run or preview objects After you create or open an object, the object displays in its painter and you work on it there. Creating new objects To create new objects, you use the New button in the PowerBar. ❖ To create a new object: 1 Click the New button in the PowerBar, or select File>New from the menu bar.
CHAPTER 1 Working with InfoMaker If you chose the Object tab page and you are creating a report, at this point you use a wizard. If you chose the Database tab page, you can create a query or a data pipeline. The new object opens in the appropriate painter.
Working in painters Accessing recently opened objects You can quickly open recently opened objects by selecting File>Recent Objects from the menu bar. The Recent Objects list includes the eight most recently opened objects, but you can include up to 36 objects on the list. ❖ To modify the number of recent objects: 1 Select Tools>System Options from the menu bar. 2 In the System Options dialog box (General tab page), modify the number for the recent objects list.
CHAPTER 1 Working with InfoMaker Opening painters Painters that edit objects There are several ways to open painters that edit objects: From here PowerBar Library painter Other painters You can Click New (to create new objects) or Open (to open existing objects) Double-click an object or select Edit from the object’s pop-up menu Most other painters are accessible from the New dialog box. Some are also available on the PowerBar and from the Tools menu.
Working in painters Views are displayed in panes in the painter window. Some views are stacked in a single pane. At the bottom of the pane there is a tab for each view in the stack. Clicking the tab for a view pops that view to the top of the stack. Each painter has a default layout, but you can display the views you choose in as many panes as you want to and save the layouts you like to work with.
CHAPTER 1 Working with InfoMaker Moving and resizing panes and views You can move a pane or a view to any location in the painter window. You might find it takes a while to get used to moving panes and views around, but if you do not like a layout, you can always revert to the default layout and start again. To restore the default layout, select View>Layouts>Default. To move a pane, you select and drag the title bar of the view that is at the top of the stack.
Working in painters 3 Release the mouse button to drop the outline in the new location: To move a pane here Drop the outline here Between two panes Between a border and a pane On the splitter bar between the panes At the side of the pane nearest the border Into a new row On the splitter bar between two rows or at the top or bottom of the painter window On the splitter bar between two columns or at the left or right edge of the painter window On the middle of the pane (if the pane was not already tabb
CHAPTER 1 ❖ To float a view in a stacked pane: • ❖ Working with InfoMaker Select Float from the tab’s pop-up menu. To dock a floating view: • Select Dock from the title bar’s pop-up menu. Adding and removing views You may want to add additional views to the painter window. If there are some views you rarely use, you can move them into a stacked pane or remove them. When removing a view in a stacked pane, make sure you remove the view and not the pane.
Working in painters Saving a layout When you have rearranged panes in the painter window, InfoMaker saves the layout in the registry. The next time you open the painter window, your last layout displays. You can also save customized layouts so that you can switch from one to another for different kinds of activities. ❖ To save customized layouts for a painter window: 1 Select View>Layouts>Manage from the menu bar. 2 Click the New Layout button (second from the left at the top of the dialog box).
CHAPTER 1 Example Working with InfoMaker For example, the following screen shows the pop-up menu for a column in a report: ❖ To display a pop-up menu: 1 Select an object, or position the pointer on an object or in a view. 2 Click the right mouse button. Defining colors You can define custom colors to use in most painters and in objects you create. ❖ To define custom colors: 1 In a painter that uses custom colors, select Design>Custom Colors from the menu bar.
Working with tools 2 Define your custom colors: Area of the Color dialog box What you do Basic colors Click the basic color closest to the color you want to define to move the pointer in the color matrix and slider on the right Modify an existing color—click a custom color, then modify the color matrix and slider.
CHAPTER 1 Working with InfoMaker Tool What you use the tool for Database profile Define and use named sets of parameters to connect to a particular database. For information, see Connecting to Your Database. Set data selection and retrieval preferences. For information, see “Using the Query Governor” on page 48.
Using property pages How property pages work in the Properties view The Properties view is dynamically updated when you select another object or control. If you select more than one object or control, group selected displays in the title bar, the properties common to them display, and you can set the properties for more than one control at a time. In the Properties view, you can use a pop-up menu to specify where the labels for the properties display and to get Help on the properties.
CHAPTER 1 Working with InfoMaker Using toolbars Toolbars provide buttons for the most common tasks in InfoMaker. You can move (dock) toolbars, customize them, and create your own. Toolbar basics InfoMaker uses three toolbars: the PowerBar, PainterBar, and StyleBar. You can hide a toolbar by right-clicking in the toolbar area and clearing the check mark text to its name. If a toolbar is not hidden, it displays as shown in Table 1-3.
Using toolbars Default button replaced The button you select from a drop-down toolbar replaces the default button on the main toolbar. For example, if you select the Picture button from the Controls drop-down toolbar, it replaces the Command button in the PainterBar.
CHAPTER 1 Working with InfoMaker Moving toolbars using the mouse You can use the mouse to move a toolbar. ❖ To move a toolbar with the mouse: 1 Position the pointer on the grab bar at the left of the toolbar or on any vertical line separating groups of buttons. 2 Press and hold the left mouse button. 3 Drag the toolbar and drop it where you want it. As you move the mouse, an outlined box shows how the toolbar will display when you drop it.
Using toolbars The Customize dialog box displays. 3 Click the palette of buttons you want to use in the Select palette group. 4 Choose a button from the Selected palette box and drag it to the position you want in the Current toolbar box. If you choose a button from the Custom palette, another dialog box displays so you can define the button. For more information, see “Adding a custom button” on page 33.
CHAPTER 1 ❖ Resetting a toolbar Working with InfoMaker To delete a button from a toolbar: 1 Position the pointer on the toolbar, display the pop-up menu, and select Customize. 2 In the Current toolbar box, select the button and drag it outside the Current toolbar box. You can restore the original setup of buttons on a toolbar at any time. ❖ Clearing or deleting a toolbar To reset a toolbar: 1 Position the pointer on the toolbar, display the pop-up menu, and select Customize.
Using toolbars ❖ To add a custom button: 1 Position the pointer on the toolbar, display the pop-up menu, and select Customize. 2 Select Custom in the Select Palette group. The custom buttons display in the Selected Palette box. 3 Select a custom button and drag it to where you want it in the Current toolbar box. The Toolbar Item Command dialog box displays. Different buttons display in the dialog box depending on which toolbar you are customizing: 4 Fill in the dialog box as shown in Table 1-4.
CHAPTER 1 Working with InfoMaker Button purpose Action in Toolbar Item Command dialog box Run a query Click the Query button and select the query from the displayed list. Run a report Click the Report button and select a report from the displayed list. You can then specify command-line arguments in the Command Line box, as described below. Assign a display format to a column in a report (Report painter only) Click the Format button to display the Display Formats dialog box.
Using toolbars Modifying a custom button ❖ To modify a custom button: 1 Position the pointer on the toolbar, display the pop-up menu, and select Customize. 2 Double-click the button in the Current toolbar box. 3 Make your changes, as described in “Adding a custom button” on page 33. Creating new toolbars InfoMaker has built-in toolbars. When you start InfoMaker, you see what is called the PowerBar. In each painter, you also see one or more PainterBars.
CHAPTER 1 Working with InfoMaker Using report wizards Accessing report wizards Report wizards help you create a report with a specific presentation style. ❖ What report wizards do To access a report wizard: 1 Click the New button in the PowerBar and select the Object tab page. 2 Select the icon for the report presentation style you need and click OK. Table 1-6 summarizes what each report wizard creates.
Using the To-Do List Using the To-Do List Opening the To-Do List The To-Do List displays a list of tasks you want to do in the current library. ❖ To open the To-Do List: • To-Do List entries Click the To-Do List button in the PowerBar, or select Tools>To-Do List from the menu bar. You can create an entry in the To-Do List at any time to remind you about any task you need to complete.
CHAPTER 1 Working with InfoMaker To do this Do this Delete checked entries or all entries Select Delete Checked or Delete All from the pop-up menu. Check or uncheck an entry Select an entry and then select Check/Uncheck from the pop-up menu. Export a To-Do List Select Export from the pop-up menu, name the To-Do List text file, and click Save. Select Import from the pop-up menu, navigate to an exported To-Do List text file, and click Open.
Using online Help About links from Help to book content Some Help topics provide links to book content to extend online Help. The book content is provided by a compiled HTML Help file that you install when you install InfoMaker.
CHAPTER 1 Working with InfoMaker Windows Help files on Vista Windows Vista does not distribute the WinHlp32.exe file required to open Windows Help files such as the imhlp115.hlp file used in InfoMaker. To use .hlp files, you need to download a special Vista version of WinHlp32.exe from the Microsoft Web site at http://go.microsoft.com/fwlink/?LinkID=82148. Compiled HTML Help (.chm) files are supported, but you need to edit the Windows registry to enable a Help macro that supports links from the imhlp115.
Customizing keyboard shortcuts "AllowProgrammaticMacros"=dword:00000001 "AllowIntranetAccess"=dword:00000001 On 64-bit Windows: Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432node\Microsoft\Win Help] "AllowProgrammaticMacros"=dword:00000001 "AllowIntranetAccess"=dword:00000001 Microsoft prohibits the distribution of WinHlp32.exe with deployed applications. If your application uses .hlp files, you should provide your users with instructions on how to download WinHlp32.exe.
CHAPTER 1 ❖ Working with InfoMaker To associate a keyboard shortcut with a menu item: 1 Select Tools>Keyboard Shortcuts from the menu bar. The keyboard shortcuts for the current menu bar display. Keyboard shortcuts in a painter or tool In a painter, the Keyboard Shortcuts dialog box includes both menu items as well as additional actions that apply to the current painter or tool. These nonmenu actions are listed under Additional Global Shortcuts and Additional Painter Shortcuts.
Using the file editor ❖ To remove a keyboard shortcut associated with a menu item: 1 Select Tools>Keyboard Shortcuts from the menu bar. 2 Select the menu item with the shortcut you want to remove. 3 Click Remove. You can reset keyboard shortcuts to the default shortcuts globally or only for the current painter. ❖ To reset keyboard shortcuts to the default: • Click the Reset button and respond to the prompt. Using the file editor InfoMaker provides a text editor that is always available.
CHAPTER 1 ❖ Working with InfoMaker To specify File Editor properties: 1 Select Design>Options to display the property page. 2 Choose the tab appropriate to the property you want to specify. Editor properties apply elsewhere When you set properties for the file editor, the settings also apply to the Interactive SQL view in the Database painter.
Changing fonts Changing fonts Table 1-9 summarizes the various ways you can change the fonts used in InfoMaker. Table 1-9: Changing fonts For this object or painter Do this A table’s data, headings, and labels In the Database painter, display the table’s property page, and change the font properties on the Data, Heading, and Label Font tabs.
CHAPTER 1 ❖ Working with InfoMaker To identify a query library as a source of queries: 1 Click the Library List button in the PowerBar and then select the Query tab. 2 Enter one or more library search paths in the Library Search Path box, or click Browse to select a library search path and then click Open to add the library to the Library Search Path box. 3 Click OK. InfoMaker sets the library search path for queries.
Using the Query Governor ❖ To identify a library as a source of form styles: 1 Click the Library List button in the PowerBar. 2 On the Style tab page, enter one or more library search paths in the Library Search Path box, or click Browse to select a library search path and then click Open to add the library to the Library Search Path box. If the page is disabled If the Style page is disabled, close the dialog box and close any open painters before trying again. 3 Click OK.
CHAPTER 1 Data retrieval options Working with InfoMaker Data selection options Description Allow SELECT DISTINCT statements Usually a SELECT statement retrieves all rows satisfying the SELECT statement. If SELECT DISTINCT is specified, duplicate rows are not retrieved. Retrieval time is often much longer when DISTINCT is specified. The data retrieval settings shown in Table 1-11 specify rows retrieved and maximum time on the client, not the server.
Using the Query Governor Using the Query Governor When you select and retrieve data, the default Query Governor options limit you in the following ways: You can do this Join an unlimited number of tables You cannot do this Specify cross products Retrieve an unlimited number of rows Retrieve for an unlimited time Specify outer joins Use SELECT DISTINCT statements You can change the Query Governor default options at any time by selecting or deselecting the options in the Query Governor dialog box.
CHAPTER 1 Working with InfoMaker How your InfoMaker environment is managed Your InfoMaker configuration information is stored in both the IM.INI file and the registry. When you start InfoMaker, it looks in the registry and the InfoMaker initialization file to set up your environment. About the registry Some InfoMaker features require the use of the IM.INI file, but many features use the registry to get and store configuration information.
How your InfoMaker environment is managed Format of INI files The InfoMaker initialization file uses the Windows INI file format. It has three types of elements: • Section names, which are enclosed in square brackets • Keywords, which are the names of preference settings • Values, which are numeric or text strings, assigned as the value of the associated keyword A variable can be listed with no value specified, in which case the default is used.
CHAPTER 1 Working with InfoMaker Starting InfoMaker from the command line You can start InfoMaker from a command line (or the Windows Run dialog box) and optionally open one of the following painters or tools: Database painter Library painter Data Pipeline painter File Editor Query painter Report painter Form painter To start InfoMaker and open a painter or tool, use the following syntax: directory\im115.
Starting InfoMaker from the command line Table 1-13: InfoMaker command-line parameters Examples Parameter libraryname Description The name of the library that contains the object you want to open. objectname arguments The name of the object you want to open. For a report, retrieval arguments for the specified report. Arguments must be in the correct order, separated by semicolons (;). Array argument values must be separated by commas (,). Decimal arrays are not supported.
CH A PTE R 2 About this chapter Working with Libraries InfoMaker stores all the objects you create in libraries. This chapter describes how to work with your libraries. Contents Topic About libraries Page 55 About InfoMaker libraries and special files Creating new libraries 56 57 About the Library painter Working with libraries 58 59 Optimizing libraries Regenerating library entries 67 67 Creating a library directory report 69 About libraries InfoMaker uses libraries to hold objects.
About InfoMaker libraries and special files • Bundle reports, forms, and pipelines in an application that you and others can use For information about creating an application, see Chapter 21, “Working with Applications.” What you cannot do in the Library painter You cannot create a library or rename a library in the Library painter. For information about creating a library, see “Creating new libraries” on page 57. You cannot create new reports, forms, queries, or pipelines in the Library painter.
CHAPTER 2 Working with Libraries Creating new libraries When you create a new library, the new library becomes the current library. Until you create new objects and save them, no objects exist in the current library. ❖ To create a new library: 1 Click the New button in the PowerBar. The New dialog box displays. Another way to create a new library You can also Click the Select Library button on the PowerBar and use the New tab page in the Select Library dialog box.
About the Library painter About the Library painter ❖ To open the Library painter: • Views in the Library painter Click the Library button in the PowerBar. The Library painter has two views that you use for displaying library files (PBLs) and the objects they contain. The two views, which are available from the View menu, are Tree and List. By default, the Library painter displays one Tree view (on the left) and one List view (on the right).
CHAPTER 2 Working with Libraries About sorting the Name column When you click the Name column header repeatedly to sort, the sort happens in four ways: by object type and then name in both ascending and descending order and by object name in both ascending and descending order. You may not easily observe the four ways of sorting if all objects of the same type have names that begin with the same character or set of characters. Working with libraries You work with libraries in the Library painter.
Working with libraries For example, you can drag a library from the Tree view and drop it in the List view to quickly display the objects the library contains in the List view. For information about using drag and drop to copy or move items, see “Copying, moving, and deleting objects” on page 63. Using the pop-up menu Like the other painters, the Library painter has a pop-up menu that provides menu items that apply to the selected item in the Tree view or the List view.
CHAPTER 2 Working with Libraries Filtering the display of objects You can change which objects display in expanded libraries. Settings are remembered InfoMaker records your preferences in the Library section of the InfoMaker initialization file so that the next time you open the Library painter, the same objects and information are displayed. Specifying which objects display in all libraries Initially in the Tree and List views, the Library painter displays all objects in libraries that you expand.
Working with libraries Filtering the display of libraries and folders In either the Tree view or the List view, you can control what displays when you expand a drive or folder. An expanded drive or folder can display only libraries, only folders, or both. ❖ To control the display of libraries and folders: • In either view, select a drive or folder and then select or clear Libraries and/or Folders from the pop-up menu.
CHAPTER 2 Working with Libraries Opening and previewing objects You can open and preview objects in the current library. ❖ To open an object: • In either the Tree view or the List view, double-click the object, or select Edit from the object’s pop-up menu. InfoMaker takes you to the painter for that object and opens the object. You can work on the object and save it as you work. When you close it, you return to the Library painter. You can run forms and preview reports from the Library painter.
Working with libraries 2 Click the Copy button or the Move button on the Painterbar, or select Entry>Copy or Entry>Move from the menu bar. The Select Library dialog box displays. 3 ❖ Select the library to which you want to copy or move the objects and click OK. To delete objects: 1 Select the objects you want to delete. 2 Click the Delete button, or select Entry>Delete from the menu bar. You are prompted to confirm the first deletion.
CHAPTER 2 2 Working with Libraries If you want the root to be a directory or library, type the path or browse to the path. If you set the root to the Library List in both the Tree view and the List view, only the name of the current library displays in the Tree view and only the objects in the current library display in the List view.
Working with libraries InfoMaker displays the Properties dialog box. The information that displays is for the first object you selected. You can change existing comments, or, if there are no comments, you can enter new descriptive text. 3 Click OK when you have finished with the first object. If you do not want to change the comments for an object, click OK. The next object displays. 4 Enter comments and click OK for each object until you have finished.
CHAPTER 2 Working with Libraries Optimizing libraries You might need to optimize your libraries occasionally. Optimizing removes gaps in libraries and defragments the storage of objects, thus improving performance. Optimizing affects only layout on disk; it does not affect the contents of the objects. Objects are not recompiled when you optimize a library. ❖ To optimize a library: 1 In either Tree view or List view, choose the library you want to optimize.
Regenerating library entries • When you make extensive changes to objects, you can rebuild entire libraries so that objects are regenerated sequentially based on interdependence. • When you upgrade to a new version of InfoMaker, you need to migrate your objects. When you regenerate an entry, InfoMaker recompiles the source form stored in the library and replaces the existing compiled form with the recompiled form. ❖ To regenerate library entries: 1 Select the entries you want to regenerate.
CHAPTER 2 Working with Libraries Migrating libraries When you upgrade to a new version of InfoMaker, your existing libraries need to be migrated to the new version. Make sure PBLs are writable If you make copies of your libraries before you migrate to a new version of InfoMaker, make sure that the libraries you will migrate are writable. Your libraries must be migrated one at a time since a library must be the current library for you to migrate it.
Creating a library directory report 70 InfoMaker
PART 2 Working with Databases This part describes how to use InfoMaker to manage your database and how to use the Data Pipeline painter to copy data from one database to another.
CH A PTE R 3 About this chapter Managing the Database This chapter describes how to manage a database from within InfoMaker.
Working with database components Tables and columns • Indexes • Database views • Extended attributes • Additional database components A database usually has many tables, each of which contains rows and columns of data. Each row in a table has the same columns, but a column’s value for a particular row could be empty or NULL if the column’s definition allows it. Tables often have relationships with other tables.
CHAPTER 3 Managing the Database In the following illustration there is a join on the dept_id column, which is a primary key for the department table and a foreign key for the employee table: For more information, see “Working with keys” on page 97. Indexes An index is a column or set of columns you identify to improve database performance when searching for data specified by the index. You index a column that contains information you will need frequently.
Working with database components Extended attributes Extended attributes enable you to store information about a table’s columns in special system tables. Unlike tables, keys, indexes, and database views (which are DBMS-specific), extended attributes are InfoMaker-specific. The most powerful extended attributes determine the edit style, display format, and validation rules for the column. For more information about extended attributes, see “Specifying column extended attributes” on page 88.
CHAPTER 3 Managing the Database Events can be used in a SQL Anywhere database to automate database administration tasks, such as sending a message when disk space is low. Event handlers are activated when a provided trigger condition is met. If any events are defined for a SQL Anywhere connection, they display in the Events folder for the connection in the Objects view. Managing databases InfoMaker supports many database management systems (DBMSs).
Using the Database painter For all other DBMSs, creating and deleting a database is an administrative task that you cannot do within InfoMaker. Using the Database painter To open the Database painter, click the Database button in the PowerBar. About the painter Like the other InfoMaker painters, the Database painter contains a menu bar, customizable PainterBars, and several views. All database-related tasks that you can do in InfoMaker can be done in the Database painter.
CHAPTER 3 View Description Object Layout Displays a graphical representation of tables and their relationships. Objects Lists database interfaces and profiles.
Using the Database painter Table 3-3: Common tasks in the Database painter To Modify a database profile Connect to a database Create new profiles, tables, views, columns, keys, indexes, or groups Modify database objects You can use the Import and Export Profiles menu selections to copy profiles. For more information, see the section on importing and exporting database profiles in Connecting to Your Database.
CHAPTER 3 Managing the Database Modifying database preferences To modify database preferences, select Design>Options from the menu bar. Some preferences are specific to the database connection; others are specific to the Database painter. Preferences on the General property page The Connect To Default Profile, Shared Database Profiles, Keep Connection Open, Use Extended Attributes, and Read Only preferences are specific to the database connection.
Using the Database painter Logging your work As you work with your database, you generate SQL statements. As you define a new table, for example, InfoMaker builds a SQL CREATE TABLE statement internally. When you save the table, InfoMaker sends the SQL statement to the DBMS to create the table. Similarly, when you add an index, InfoMaker builds a CREATE INDEX statement. You can see all SQL generated in a Database painter session in the Activity Log view. You can also save this information to a file.
CHAPTER 3 Managing the Database Creating and deleting a SQL Anywhere database In InfoMaker you work within an existing database. With one exception, creating or deleting a database is an administrative task that is not performed directly in InfoMaker. The one exception is that you can create and delete a local SQL Anywhere database from within InfoMaker. For information about creating and deleting other databases, see your DBMS documentation.
Working with tables ❖ To delete a local SQL Anywhere database: 1 Open the Database painter. 2 From the Objects view, launch the Delete ASA Database utility included with the ODBC interface. The Delete Local Database dialog box displays. 3 Select the database you want to delete and select Open. 4 Click Yes to delete the database. When you click Yes, InfoMaker deletes the specified database.
CHAPTER 3 2 Managing the Database Enter the required information for this column. For what to enter in each field, see “Specifying column definitions” on page 86. As you enter information, use the Tab key to move from place to place in the column definition. After defining the last item in the column definition, press the Tab key to display the work area for the next column. 3 Repeat step 2 for each additional column in your table.
Working with tables Creating a new table from an existing table You can create a new table that is similar to an existing table very quickly by using the Save Table As menu option. ❖ To create a new table from an existing table: 1 Open the existing table in the Columns view by dragging and dropping it or selecting Alter Table from the pop-up menu. 2 Right-click in the Columns view and select Save Table As from the pop-up menu. The Create New Table dialog box displays.
CHAPTER 3 Field Managing the Database What you enter Null Select Yes or No from the Null drop-down list to specify whether NULLs are allowed in the column. Specifying No means the column cannot have null values; users must supply a value. No is the default in a new table. The value that will be placed in a column in a row that you insert into a form. The drop-down list has built-in choices, but you can type any other value. For an explanation of the built-in choices, see your DBMS documentation.
Working with tables Select this tab To modify this property Heading Font Font for column identifiers used in grid, tabular, and n-up reports and grid forms displayed in the Results view by clicking a Data Manipulation button Font for column identifiers used in freeform reports and forms displayed in the Results view by clicking a Data Manipulation button Label Font 3 Right-click on the Object Details view and select Save Changes from the pop-up menu.
CHAPTER 3 Managing the Database Select this tab To modify these extended attributes Validation Criteria that a value must pass to be accepted in a form. For example, you can associate a validation rule with a Salary column so that you can enter a value only within a particular range. The initial value for the column. You can select a value from the drop-down list. The initial value must be the same datatype as the column, must pass validation, and can be NULL only if NULL is allowed for the column.
Working with tables Specifying additional properties for character columns You can also set two additional properties for character columns on the Display property page: Case and Picture. Specifying the displayed case You can specify whether InfoMaker converts the case of characters for a column in a report or form.
CHAPTER 3 • Prohibit null values for an appended column • Alter an existing index Managing the Database Some DBMSs let you do the following, but others do not: • Append columns that allow null values • Increase or decrease the number of characters allowed for data in an existing column • Allow null values • Prohibit null values in a column that allowed null values Database painter is DBMS aware The Database painter grays out or notifies you about actions that your DBMS prohibits.
Working with tables 2 Make the changes you want in the Columns view or in the Object Details view. 3 Select Save Table or Save Changes. InfoMaker submits the pending SQL syntax statements it generated to the DBMS, and the table is modified. Cutting, copying, and pasting columns In the Database painter, you can use the Cut, Copy, and Paste buttons in the PainterBar (or Cut, Copy, and Paste from the Edit or pop-up menu) to cut, copy, and paste one column at a time within a table or between tables.
CHAPTER 3 Managing the Database Dropping a table Dropping removes the table from the database. ❖ Deleting orphaned table information To drop a table: 1 Select Drop Table from the table’s pop-up menu or select Object>Delete from the menu bar. 2 Click Yes. If you drop a table outside InfoMaker, information remains in the system tables about the table, including extended attributes for the columns.
Working with tables Copying, saving, and printing pending SQL changes When you are viewing pending SQL changes, you can: • Copy pending changes to the clipboard • Save pending changes to a file • Print pending changes To copy, save, or print only part of the SQL syntax Select the part of the SQL syntax you want before you copy, save, or print. ❖ To copy the SQL syntax to the clipboard: • ❖ In the Pending Syntax view, click the Copy button or select Select All and then Copy from the pop-up menu.
CHAPTER 3 Managing the Database Exporting table syntax You can export the syntax for a table to the log. This feature is useful when you want to create a backup definition of the table before you alter it or when you want to create the same table in another DBMS. To export to another DBMS, you must have the InfoMaker interface for that DBMS. ❖ To export the syntax of an existing table to a log: 1 Select the table in the Objects or Object Layout view.
Working with tables In the Employee table, for example, one column name is Emp_lname. A label and a heading for the column are defined for InfoMaker to use in reports. The column label is defined as Last Name:. The column heading is defined as Last Name. The label and heading are stored in the PBCatCol table in the extended attribute system tables. The extended attribute system tables are maintained by InfoMaker and only InfoMaker users can enter information into them.
CHAPTER 3 Managing the Database For SNC, use # for a local temporary table or ## for a global temporary table. Temporary tables must start with the # character. Local temporary tables are visible only in the user’s current connection and are deleted when the user disconnects. Global temporary tables are visible to any user connected to the instance of SQL Server, and they are deleted when all users referencing the table disconnect.
Working with keys • Open the table containing the primary key used by a particular foreign key • Create, alter, and drop keys For the most part, you work with keys the same way for each DBMS that supports keys, but there are some DBMS-specific issues. For complete information about using keys with your DBMS, see your DBMS documentation.
CHAPTER 3 Defining primary keys Managing the Database If your DBMS supports primary keys, you can define them in InfoMaker. ❖ To create a primary key: 1 Do one of the following: • Highlight the table for which you want to create a primary key and click the Create Primary Key drop-down toolbar button in PainterBar1. • Select Object>Insert>Primary Key from the main menu or New>Primary Key from the pop-up menu.
Working with keys Completing the primary key Some DBMSs automatically create a unique index when you define a primary key so that you can immediately begin to add data to the table. Others require you to create a unique index separately to support the primary key before populating the table with data. To find out what your DBMS does, see your DBMS documentation. Defining foreign keys If your DBMS supports foreign keys, you can define them in InfoMaker.
CHAPTER 3 6 Managing the Database Right-click on the Object Details view and select Save Changes from the pop-up menu. Any changes you make in the view are immediately saved to the table definition. Modifying keys You can modify a primary key in InfoMaker. ❖ To modify a primary key: 1 Do one of the following: • Highlight the primary key listed in the table’s expanded tree view and click the Properties button. • Select Properties from the Object or pop-up menu.
Working with indexes Creating an index In SQL Anywhere databases In SQL Anywhere databases, you should not define an index on a column that is defined as a foreign key, because foreign keys are already optimized for quick reference. ❖ To create an index: 1 Do one of the following: • Highlight the table for which you want to create an index and click the Create Index drop-down toolbar button in PainterBar1. • Select Object>Insert>Index from the main menu or New>Index from the pop-up menu.
CHAPTER 3 Managing the Database 2 In the Object Details view, select or deselect columns as needed. 3 Right-click on the Object Details view and select Save Changes from the pop-up menu. Any changes you made in the view are immediately saved to the table definition. Dropping an index Dropping an index removes it from the database. ❖ To drop an index from a table: 1 In the Database painter workspace, display the pop-up menu for the index you want to drop. 2 Select Drop Index and click Yes.
Working with database views You define, open, and manipulate database views in the View painter, which is similar to the SQL Select painter. For more information about the SQL Select painter, see “Selecting a data source” on page 154. Updating database views Some database views are logically updatable and others are not. Some DBMSs do not allow any updating of views. For the rules your DBMS follows, see your DBMS documentation.
CHAPTER 3 Managing the Database Representations of the selected tables and views display in the View painter workspace: 3 Select the columns to include in the view and include computed columns as needed. 4 Join the tables if there is more than one table in the view. For information, see “Joining tables” on page 106. 5 Specify criteria to limit rows retrieved (Where tab), group retrieved rows (Group tab), and limit the retrieved groups (Having tab), if appropriate.
Working with database views Displaying a database view’s SQL statement You can display the SQL statement that defines a database view. How you do it depends on whether you are creating a new view in the View painter or want to look at the definition of an existing view. ❖ To display the SQL statement from the View painter: • Select the Syntax tab in the View painter. InfoMaker displays the SQL it is generating. The display is updated each time you change the view.
CHAPTER 3 ❖ Managing the Database To join tables: 1 Click the Join button. 2 Click the columns on which you want to join the tables. In the following screen, the Employee and Department tables are joined on the dept_id column: 3 To create a join other than the equality join, click the join representation in the workspace. The Join dialog box displays: 4 Select the join operator you want from the Join dialog box.
Manipulating data For more about outer joins, see “Using ANSI outer joins” on page 171. Dropping a database view Dropping a database view removes its definition from the database. ❖ To drop a view: 1 In the Objects view, select the database view you want to drop. 2 Click the Drop Object button or select Drop View from the pop-up menu. InfoMaker prompts you to confirm the drop, then generates a DROP VIEW statement and submits it to the DBMS.
CHAPTER 3 Managing the Database Retrieving data ❖ To retrieve data: 1 In the Database painter, select the table or database view whose data you want to manipulate. 2 Do one of the following: • Click one of the three Data Manipulation buttons (Grid, Tabular, or Freeform) in the PainterBar. • Select Data or Edit Data from the Object or pop-up menu and choose one of the edit options from the cascading menu that displays. All rows are retrieved and display in the Results view.
Manipulating data If looking at data from a view Some views are logically updatable and others are not. Some DBMSs do not allow any updating of views. For the rules your DBMS follows regarding updating of views, see your DBMS documentation. ❖ To modify data: 1 Do one of the following: • To modify existing data, tab to a field and enter a new value. • To add a row, click the Insert Row button and enter data in the new row. • To delete a row, click the Delete Row button.
CHAPTER 3 2 Managing the Database Drag the columns you want to sort on from the Source Data box to the Columns box: A check box with a check mark in it displays under the Ascending heading to indicate that the values will be sorted in ascending order. To sort in descending order, clear the check box. Precedence of sorting The order in which the columns display in the Columns box determines the precedence of the sorting. For example, in the preceding dialog box, rows would be sorted by department ID.
Manipulating data Filtering rows You can limit which rows are displayed by defining a filter. The filters you define are for testing only and are not saved with the table or passed to the Report painter. ❖ To filter the rows: 1 Select Rows>Filter from the menu bar. The Specify Filter dialog box displays. 2 Enter a boolean expression that InfoMaker will test against each row: If the expression evaluates to TRUE, the row is displayed. You can paste functions, columns, and operators in the expression.
CHAPTER 3 Managing the Database Viewing row information You can display information about the data you have retrieved. ❖ To display row information: • Select Rows>Described from the menu bar.
Manipulating data Printing data You can print the data displayed by selecting File>Print from the menu bar. Before printing, you can also preview the output on the screen. ❖ To preview printed output before printing: 1 Select File>Print Preview from the menu bar. Preview displays the data as it will print. To display rulers around the page borders in Print Preview, select File>Print Preview Rulers. 2 To change the magnification used in Print Preview, select File>Print Preview Zoom from the menu bar.
CHAPTER 3 3 Managing the Database For TEXT, CSV, SQL, HTML, and DIF formats, select an encoding for the file. You can select ANSI/DBCS, Unicode LE (Little-Endian), Unicode BE (Big-Endian), or UTF8. 4 Name the file and save it. InfoMaker saves all displayed rows in the file; all columns in the displayed rows are saved. Filtered rows are not saved. Creating and executing SQL statements The Database painter’s Interactive SQL view is a SQL editor in which you can enter and execute SQL statements.
Creating and executing SQL statements Controlling comments By default, InfoMaker strips off comments when it sends SQL to the DBMS. You can have comments included by clearing the check mark next to Strip Comments in the pop-up menu of the Interactive SQL view.
CHAPTER 3 4 Managing the Database • For an INSERT statement, type the values to insert into each column. You can insert as many rows as you want. • For an UPDATE statement, specify the new values for the columns in the Update Column Values dialog box. Then specify the WHERE criteria to indicate which rows to update. • For a DELETE statement, specify the WHERE criteria to indicate which rows to delete.
Creating and executing SQL statements Importing SQL from a text file You can import SQL that has been saved in a text file into the Database painter. ❖ Dragging a procedure or function from the Objects view To read SQL from a file: 1 Put the insertion point where you want to insert the SQL. 2 Select Paste Special>From File from the Edit or pop-up menu. 3 Select the file containing the SQL, and click OK.
CHAPTER 3 Managing the Database Customizing the editor The Interactive SQL view provides the same editing capabilities as the file editor. It also has Script, Font, and Coloring properties that you can change to make SQL files easier to read. With no change in properties, SQL files have black text on a white background and a tab stop setting of 3 for indentation. Setting Script and Font properties Select Design>Options from the menu bar to open the Database Preferences dialog box.
Controlling access to the current database 120 InfoMaker
CH A PTE R About this chapter 4 Working with Data Pipelines This chapter describes how to use the Data Pipeline painter to create data pipelines, which let you reproduce database data in various ways.
About data pipelines Source and destination databases • Upload local data that changes daily to a corporate database • Create a new table when a change (such as allowing or disallowing NULLs or changing primary key or index assignments) is disallowed in the Database painter You can use the Data Pipeline painter to pipe data from one or more tables in a source database to one table in a destination database. You can pipe all data or selected data in one or more tables.
CHAPTER 4 Working with Data Pipelines The Data Pipeline painter supports the piping of columns of any datatype, including columns with blob data. For information about piping a column that has a blob datatype, see “Piping blob data” on page 134. Piping extended attributes The first time InfoMaker connects to a database, it creates five system tables called the extended attribute system tables. These system tables initially contain default extended attribute information for tables and columns.
Creating a data pipeline Piping the extended attribute system tables Selecting the Extended Attributes check box never results in the piping of named display formats, edit styles, and validation rules that are stored in the extended attribute system tables but are not associated with columns in tables you are piping.
CHAPTER 4 Working with Data Pipelines If you do not see the connections you need To create a pipeline, the databases you want to use for your source and destination must each have a database profile defined. If you do not see profiles for the databases you want to use, select Cancel in the New Data Pipeline dialog box and then define those profiles. For information about defining profiles, see “Changing the destination and source databases” on page 136. 3 Select a data source.
Creating a data pipeline The pipeline definition is InfoMaker’s best guess based on the source data you specified. 6 Modify the pipeline definition as needed. For information, see "Modifying the data pipeline definition" next. 7 (Optional) Modify the source data as needed. To do so, click the Data button in the PainterBar, or select Design>Edit Data Source from the menu bar. For information about working in the Select painter, see Chapter 5, “Defining Reports.
CHAPTER 4 Working with Data Pipelines The role of the Query governor Options you set in the Query Governor affect the Data Pipeline painter when you specify the data and when data is piped. In the Query Governor, you can set data selection options and limit the number of rows piped and the elapsed piping time. For more information, see Chapter 2, “Working with Libraries.” At runtime, the number of rows read and written, the elapsed execution time, and the number of errors display in MicroHelp.
Modifying the data pipeline definition Table 4-1 lists properties you can modify that apply to the destination table. These properties display at the top of the Data Pipeline painter workspace. Table 4-1: Pipeline properties for the destination table Item Table Description Name of the destination table. Default If source and destination are different, name of first table specified in the source data or name of the stored procedure. If the same, _copy is appended. Create - Add Table.
CHAPTER 4 Working with Data Pipelines Table 4-2 lists properties that you can modify that apply to the destination table’s columns and keys. These properties display under the properties that apply to the table itself and most can be modified only for the Create and Replace pipeline operations. Column names and datatypes that cannot be modified You cannot modify the source column names and datatypes that display at the left of the workspace.
Modifying the data pipeline definition Choosing a pipeline operation When InfoMaker pipes data, what happens in the destination database depends on which pipeline operation you choose in the Options drop-down list at the top of the workspace. Table 4-3: Effect of pipeline operations on the destination database Pipeline operation Effect on destination database Create - Add Table A new table is created and rows selected from the source tables are inserted.
CHAPTER 4 • Working with Data Pipelines Specify or clear a key name and/or key columns. Specify key columns by selecting one or more check boxes to define a unique identifier for rows. Neither a key name nor key columns are required. • Allow or disallow NULLs for a column. If NULL is allowed, no initial value is allowed. If NULL is not allowed, an initial value is required. The words spaces (a string filled with spaces) and today (today’s date) are initial value keywords.
Modifying the data pipeline definition When using Update For the Update - Update/Insert Rows option, the destination table must already exist. You can: • Select an existing table from the Table drop-down list. • Modify the Commit and Max Errors values. • Change the Key columns in the destination table’s primary key or unique index, depending on what the DBMS supports. Key columns must be selected; the key determines the UPDATE statement’s WHERE clause. • Change the initial value for a column.
CHAPTER 4 When you stop execution Working with Data Pipelines When you click Cancel or a Query Governor limit is reached, if the Commit value is a number, every row that was piped is committed. If the Commit value is All or None, every row that was piped is rolled back. For example, if you click the Cancel button when the 24th row is piped and the Commit value is 20, then: 1 20 rows are piped and committed. 2 3 rows are piped and committed. 3 Piping stops.
Modifying the data pipeline definition About transactions A transaction is a logical unit of work done by a DBMS, within which either all the work in the unit must be completed or none of the work in the unit must be completed. If the destination DBMS does not support transactions or is not in the scope of a transaction, each row that is inserted or updated is committed. About the All and None commit values In the Data Pipeline painter, the Commit values All and None have the same meaning.
CHAPTER 4 Adding blob columns to a pipeline definition Working with Data Pipelines When you select data to pipe, you cannot select a blob column as part of the data source because blobs cannot be handled in a SELECT statement. After the pipeline definition is created, you add blob columns, one at a time, to the definition. ❖ To add a blob column to a pipeline definition: 1 Select Design>Database Blob from the menu bar.
Modifying the data pipeline definition Executing a pipeline with blob columns After you have completed the pipeline definition by adding one or more blob columns, you can execute the pipeline. When you do, rows are piped a block at a time, depending on the Commit value. For a given block, Row 1 is inserted, then Row 1 is updated with Blob 1, then Row 1 is updated with Blob 2, and so on. Then Row 2 is inserted, and so on until the block is complete.
CHAPTER 4 Working with Data Pipelines Correcting pipeline errors If the pipeline cannot pipe certain rows to the destination table for some reason, InfoMaker displays the following information for the error rows: • Name of the table in the destination database • Pipeline operation you chose in the Option box • Error messages to identify the problem with each row • Data values in the error rows • Source and destination column information The following screen shot displays this information: What
Saving a pipeline ❖ To correct pipeline errors: 1 Change data values for the appropriate columns in the error rows. 2 Click the Update DB button, or select Design>Update Database from the menu bar. InfoMaker pipes rows in which errors were corrected to the destination table and displays any remaining errors. 3 Repeat steps 1 and 2 until all errors are corrected. The Data Pipeline painter workspace displays.
CHAPTER 4 Working with Data Pipelines Using an existing pipeline If you save a pipeline, you can modify and execute it any time. You can also pipe data that might have changed since the last pipeline execution or pipe data to other databases. ❖ To use an existing pipeline: 1 Click the Open button in the PowerBar. 2 In the Open dialog box, select the Pipelines object type in the Object Type drop-down list, select the pipeline you want to execute, and click OK.
Pipeline examples Reproducing a table definition with no data You can force a pipeline to create a table definition and not pipe data. To do this, you must use Quick Select, SQL Select, or Query as the data source. It is easiest to do it using SQL Select. ❖ To reproduce a table definition with no data: 1 Click the Pipeline button, click New, select SQL Select as the data source and specify the source and destination databases, and click OK.
PART 3 Reports This part introduces you to the many styles of reports available in InfoMaker and describes how to create and work with reports.
CH A PTE R 5 About this chapter Contents Defining Reports The reports you create are centered around your organization’s data. This chapter describes how to define reports to display the data.
Choosing a presentation style About the term DataWindow The terms DataWindow and report are often used interchangeably. Many of the examples in this book were captured in the PowerBuilder environment. In these examples, the title bar of a report includes the word DataWindow instead of Report. Sometimes dialog boxes use the word DataWindow instead of report. If you need update capabilities If you need update capabilities, use the InfoMaker Form painter to create a form.
CHAPTER 5 Using this Report wizard TreeView Defining Reports You create a new report With data grouped in rows in a TreeView; the TreeView displays the data hierarchically in a way that allows you to expand and collapse it Using the Tabular style The Tabular presentation style presents data with the data columns going across the page and headers above each column. As many rows from the database will display at one time as can fit in the report.
Choosing a presentation style Using the Freeform style The Freeform presentation style presents data with the data columns going down the page and labels next to each column. You can reorganize the default layout any way you want by moving columns and text. Using the Grid style The Grid presentation style shows data in row-and-column format with grid lines separating rows and columns. With other styles, you can move text, values, and other objects around freely in designing the report.
CHAPTER 5 Grid report with modified column widths Defining Reports This grid report was created from the original one by decreasing the width of some columns: Using the Label style The Label presentation style shows data as labels. With this style you can create mailing labels, business cards, name tags, index cards, diskette labels, file folder labels, and many other types of labels.
Choosing a presentation style Business cards Name tags Specifying label properties If you choose the Label style, you are asked to specify the properties for the label after specifying the data source. You can choose from a list of predefined label types or enter your own specifications manually. Where label definitions come from InfoMaker gets the information about the predefined label formats from a preferences file called pblab115.ini.
CHAPTER 5 Table example Defining Reports For a table of daily stock prices, you can define the report as five across, so each row in the report displays five days’ prices (Monday through Friday). Suppose you have a table with two columns, day and price, that record the closing stock price each day for three weeks. In the following n-up report, 5 was selected as the number of rows to display across the page, so each line in the report shows five days’ stock prices.
Choosing a presentation style Another way to get multiple-column reports In an n-up report, the data is displayed across and then down. If you want your data to go down the page and then across in multiple columns, as in a phone list, you should create a standard tabular report, then specify newspaper columns. For more information on newspaper columns, see Chapter 6, “Enhancing Reports.
CHAPTER 5 Defining Reports This composite report consists of three nested tabular reports. One of the tabular reports includes a graph: For more about the Composite presentation style, see Chapter 11, “Using Nested Reports.” Using the Graph and Crosstab styles In addition to the (preceding) text-based presentation styles, InfoMaker provides two styles that allow you to display information graphically: Graph and Crosstab.
Choosing a presentation style There is a graph report in the composite report in “Using the Composite style” on page 150. This crosstab report counts the number of employees that fit into each cell. For example, there are three employees in department 100 who make between $30,000 and $39,999: For more information about these two presentation styles, see Chapter 13, “Working with Graphs,” and Chapter 14, “Working with Crosstabs.” Using the OLE 2.
CHAPTER 5 Defining Reports Using the TreeView style The TreeView presentation style provides an easy way to create reports that display hierarchical data in a TreeView, where the rows are divided into groups that can be expanded and collapsed. Icons (+ or –) show whether the state of a group in the TreeView is expanded or collapsed, and lines connect parents and their children.
Selecting a data source Column limit There is a limit of 1000 on the number of columns in a report. For information about changing your database connection, see Connecting to Your Database. ❖ To create a new report: 1 Select File>New from the menu bar and select the Object tab. 2 Choose a presentation style for the report. The presentation style determines how the data is displayed. See “Choosing a presentation style” on page 144.
CHAPTER 5 InfoMaker data sources Defining Reports InfoMaker has five data sources. All five can be used for reports, but only a subset of the possible data sources can be used for forms and data pipelines: Data source Reports Forms Pipelines Quick Select SQL Select X X X X X X Query External X X X X Stored Procedure If the DBMS supports stored procedures that return result sets X You cannot create a form for data that is not stored in a database.
Using Quick Select Using Quick Select The easiest way to define a data source is using Quick Select. ❖ To define the data using Quick Select: 1 Click Quick Select in the Choose Data Source dialog box in the wizard and click Next. 2 Select the table that you will use in the report. For more information, see “Selecting a table” next. 3 Select the columns to be retrieved from the database. For more information, see “Selecting columns” on page 158. 4 (Optional) Sort the rows before you retrieve data.
CHAPTER 5 Defining Reports Selecting a table When you choose Quick Select, the Quick Select dialog box displays. The Tables box lists tables and views in the current database. Displaying table comments To display a comment about a table, position the pointer on the table and click the right mouse button or select the table. Which tables and views display? The DBMS determines what tables and views display. For some DBMSs, all tables and views display, whether or not you have authorization.
Using Quick Select Meaning of the up and down arrows An arrow displays next to a table to indicate its relationship to the selected table. The arrow always points in the many direction of the relationship— toward the selected table (up) if the selected table contains a foreign key in the relationship and away from the selected table (down) if the selected table contains a primary key in the relationship: In this preceding illustration, the selected table is sales_order.
CHAPTER 5 Defining Reports As you select columns, they display in the grid at the bottom of the dialog box in the order in which you select them. If you want the columns to display in a different order in the report, select a column name you want to move in the grid and drag it to the new location. Specifying sorting criteria In the grid at the bottom of the Quick Select dialog box, you can specify if you want the retrieved rows to be sorted.
Using Quick Select ❖ To specify selection criteria: 1 Click the Criteria row below the first column for which you want to select the data to retrieve. 2 Enter an expression, or if the column has an edit style, select or enter a value. If the column is too narrow for the criterion, drag the grid line to enlarge the column. This enlargement does not affect the column size in a report. 3 Enter additional expressions until you have specified the data you want to retrieve.
CHAPTER 5 Comparison operators Defining Reports You can use the LIKE, NOT LIKE, IN, and NOT IN operators to compare expressions. Use LIKE to search for strings that match a predetermined pattern. Use NOT LIKE to find strings that do not match a predetermined pattern. When you use LIKE or NOT LIKE, you can use wildcards: • The percent sign (%), like the wildcard asterisk (*) used in many applications, matches multiple characters. For example, Good% matches all names that begin with Good.
Using Quick Select SQL expression examples The first six examples in this section all refer to a grid that contains three columns from the employee table: emp_id, dept_id, and salary. Example 1 The expression <50000 in the Criteria row in the salary column in the grid retrieves information for employees whose salaries are less than $50,000. The SELECT statement that InfoMaker creates is: SELECT employee.emp_id, employee.dept_id, employee.salary FROM employee WHERE employee.
CHAPTER 5 Defining Reports The SELECT statement that InfoMaker creates is: SELECT employee.emp_id, employee.dept_id, employee.salary FROM employee WHERE (employee.emp_id >'300') AND employee.
Using Quick Select Example 6 This example shows the use of the word AND in the Or criteria row. In the Criteria row, >=500 is in the EmpId column and >=30000 is in the Salary column. In the Or row, AND <=1000 is in the EmpId column and AND <=50000 is in the Salary column. These criteria retrieve information for employees who have an employee ID from 500 to 1000 and a salary from $30,000 to $50,000. The SELECT statement that InfoMaker creates is: SELECT employee.emp_id, employee.dept_id, employee.
CHAPTER 5 Defining Reports Using SQL Select In specifying data for a report, you have more options for specifying complex SQL statements when you use SQL Select as the data source.
Using SQL Select 6 Limit the retrieved rows with WHERE, ORDER BY, GROUP BY, and HAVING criteria, if appropriate. For more information, see “Specifying selection, sorting, and grouping criteria” on page 175. 7 If you want to eliminate duplicate rows, select Distinct from the Design menu. This adds the DISTINCT keyword to the SELECT statement. 8 Click the Return button on the PainterBar. You return to the wizard to complete the definition of the report.
CHAPTER 5 Defining Reports Below the Table Layout view, several tabbed views also display by default. You use the views (for example, Compute, Having, Group) to specify the SQL SELECT statement in more detail. You can turn the views on and off from the View menu on the menu bar. Specifying what is displayed You can display the label and datatype of each column in the tables (the label information comes from the extended attribute system tables).
Using SQL Select Selecting columns You can click each column you want to include from the table representations in the Table Layout view. InfoMaker highlights selected columns and places them in the Selection List at the top of the SQL Select painter. ❖ To reorder the selected columns: • ❖ To select all columns from a table: • ❖ Drag a column in the Selection List with the mouse. Release the mouse button when the column is in the proper position in the list.
CHAPTER 5 3 Defining Reports Press the Tab key to get to the next row to define another computed column, or click another tab to make additional specifications. InfoMaker adds the computed columns to the list of columns you have selected. About computed columns and computed fields Computed columns you define in the SQL Select painter are added to the SQL statement and used by the DBMS to retrieve the data. The expression you define here follows your DBMS’s rules.
Using SQL Select ❖ To edit the SELECT statement: 1 Select Design>Convert to Syntax from the menu bar. InfoMaker displays the SELECT statement in a text window. 2 Edit the SELECT statement. 3 Do one of the following: • Select Design>Convert to Graphics from the menu bar to return to the SQL Select painter. • Click the Return button to return to the wizard if you are building a new report, or to the Report painter if you are modifying an existing report.
CHAPTER 5 Defining Reports The Join dialog box displays: 4 Select the join operator you want and click OK. If your DBMS supports outer joins, and the Allow Cross Product option is set in the Query Governor, outer join options also display in the Join dialog box. About the Query Governor You can use the Query Governor to set data selection and retrieval options. For more information about the Query Governor, see “Using the Query Governor” on page 48.
Using SQL Select Order of evaluation and nesting In ANSI SQL-92, when nesting joins, the result of the first outer join (determined by order of ON conditions) is the operand of the outer join that follows it. In InfoMaker, an outer join is considered to be nested if the table-reference on the left of the JOIN has been used before within the same outer join nested sequence. The order of evaluation for ANSI syntax nested outer joins is determined by the order of the ON search conditions.
CHAPTER 5 Defining Reports If you select the condition with rows from department that have no employee, you create a right outer join instead. Equivalent statements The syntax generated when you select table A then table B and create a left outer join is equivalent to the syntax generated when you select table B then table A and create a right outer join. For more about outer joins, see your DBMS documentation.
Using SQL Select 3 Enter a name and datatype for each argument. The first character must be alphabetic (a–z); subsequent characters can be alphanumeric (a–z, 1–9), an underscore ( _ ), or a dollar sign ($). 4 Specifying an array as a retrieval argument Click the Add button to define additional arguments as needed, and click OK when done. You can specify an array of values as your retrieval argument.
CHAPTER 5 Defining Reports For the case of the array defined as deptarray, the expression in the Where view will look like the following expression. You can paste the :deptarray argument using the pop-up menus in the value area of the Where view: Supplying values for an array argument When you preview the report, you are prompted to supply the department values. InfoMaker retrieves rows that match one of the set of values that you supply.
Using SQL Select Dynamically selecting, sorting, and grouping data Selection, sorting, and grouping criteria that you define in the SQL Select painter are added to the SQL statement and processed by the DBMS as part of the retrieval. You can also define selection, sorting, and grouping criteria that are created and processed dynamically by InfoMaker after data has been retrieved from the DBMS. For more information, see Chapter 9, “Filtering, Sorting, and Grouping Rows.
CHAPTER 5 5 6 Defining Reports Under Value, specify the right-hand side of the expression. You can: • Type a value. • Paste a column, function, or retrieval argument (if there is one) by selecting Columns, Functions, or Arguments from the pop-up menu. • Paste a value from the database by selecting Value from the pop-up menu, then selecting a value from the list of values retrieved from the database. (It may take some time to display values if the column has many values in the database.
Using SQL Select 3 Continue to specify additional columns for sorting in ascending or descending order as needed. You can change the sorting order by dragging the selected column names up or down. With the following sorting specification, rows will be sorted first by department ID, then by employee ID: Defining GROUP BY criteria 4 Define limiting (Where view), grouping (Group view), and limiting groups (Having view) criteria as appropriate.
CHAPTER 5 ❖ Defining Reports To define GROUP BY criteria: 1 Click the Group tab to make the Group view available (or select View>Group if the Group view is not currently displayed). The columns in the tables you selected display in the left side of the Group view. You might need to scroll to see your selections. 2 Drag the first column you want to group onto the right side of the Group view. This specifies the column for grouping.
Using Query ❖ To define HAVING criteria: • Click the Having tab to make the Having view available (or select View>Having if the Having view is not currently displayed). Each row in the Having view is a place for entering an expression that limits which groups are retrieved. For information on how to define criteria in the Having view, see the procedure in “Defining WHERE criteria” on page 176.
CHAPTER 5 Defining Reports Using ODBC drivers instead of External If you have an ODBC dBASE driver and an ODBC text driver on your computer and you configure ODBC data sources for them, you can access data that resides in a dBASE file or a tab-separated text file. To configure data sources, use the ODBC Administrator, a Windows utility that is accessible from within the Utilities folder in the Database painter.
Using Stored Procedure Alternatively, you can select the name from the file list. Use the Drives drop-down list and the Directories box as needed to display the list of files that includes the one you want. Using Stored Procedure A stored procedure is a set of precompiled and preoptimized SQL statements that performs some database operation. Stored procedures reside where the database resides, and you can access them as needed.
CHAPTER 5 • Defining Reports To define the result set description manually, select the Manual Result Set check box and click Next. In the Define Stored Procedure Result Set dialog box: • Enter the name and type of the first column in the result set. • To add additional columns, click Add. Your preference is saved InfoMaker records your preference for building result set descriptions for stored procedure reports in the variable Stored_Procedure_Build in the InfoMaker initialization file.
Choosing report-wide options The group suffix is an optional integer used in some DBMSs to group procedures of the same name so that they can be dropped together with a single DROP PROCEDURE statement. For other DBMSs the number is ignored. 4 When you have defined the entire result set, click OK. You return to the Report painter with the columns specified in the result set placed in the report. For information about defining retrieval arguments for reports, see Chapter 6, “Enhancing Reports.
CHAPTER 5 Defining Reports Property Meaning for the report Column border and color Wrap Height (Freeform only) The default border and color used for data values. The height of the detail band. When the value is None, the number of columns selected determines the height of the detail band. The columns display in a single vertical line. When the value is set to a number, the detail band height is set to the number specified and columns wrap within the detail band. 5 Click OK.
Generating and saving a report Extended attribute information applies to forms, too InfoMaker uses extended attribute information when generating a form the same way it uses it when generating a report. For example, labels and headings you defined for columns in the Database painter are used in the generated report. Similarly, if you associated an edit style with a column in the Database painter, that edit style is automatically used for the column in the report.
CHAPTER 5 Defining Reports For more information about the extended attribute system tables, see Chapter 3, “Managing the Database,” and Appendix B, “The Extended Attribute System Tables.” Saving the report When you have created a report, you should save it. The first time you save it you give it a name. As you work, you should save your report frequently so that you do not lose changes. ❖ To save the report: 1 Select File>Save from the menu bar.
Defining queries 3 Select the object you want. InfoMaker opens the Report painter and displays the report. You can also open a report by double-clicking it in the System Tree, or, if it has been placed in a window or visual user object, selecting Modify DataWindow from the control’s pop-up menu. To learn how you can modify an existing report, see Chapter 6, “Enhancing Reports.
CHAPTER 5 Defining Reports InfoMaker retrieves the rows satisfying the currently defined query in a grid-style report. 2 Manipulate the retrieved data as you do in the Database painter in the Output view. You can sort and filter the data, but you cannot insert or delete a row or apply changes to the database. For more about manipulating data, see Chapter 3, “Managing the Database.” 3 When you have finished previewing the query, click the Close button in the PainterBar to return to the Query painter.
What's next Modifying a query ❖ To modify a query: 1 Select File>Open from the menu bar. 2 Select the Queries object type and then the query you want to modify, and click OK. 3 Modify the query as needed. What's next After you have generated your report, you will probably want to preview it to see how it looks. After that, you might want to enhance the report in the Report painter before using it. InfoMaker provides many ways for you to make a report easier to use and more informative.
CH A PTE R About this chapter Contents Related topics 6 Enhancing Reports After InfoMaker generates a basic report, you can further enhance its appearance and content. You do that in the Report painter. This chapter describes basic enhancements you can make to a report.
Working in the Report painter Chapter Explains how to Chapter 14, “Working with Crosstabs” Use crosstabs to present analyses of data retrieved in a report Chapter 15, “Working with TreeViews” Use TreeViews to group data and display it hierarchically in a way that allows you to expand and collapse it Working in the Report painter The Report painter provides views related to the report you are working on. Interacting with these views is how you work in the Report painter.
CHAPTER 6 Enhancing Reports Preview view The Preview view in the middle on the left shows the report with data as it will appear at runtime. If the Print Preview toggle (File>Print Preview) is selected, you see the report as it would appear when printed with an optional blue outline that shows where the page margins are located. Export/Import Template view for XML The Export/Import Template view for XML at the bottom left shows a default template for exporting and importing data in XML format.
Working in the Report painter Understanding the Report painter Design view For most presentation styles, the Report painter Design view is divided into areas called bands. Each band corresponds to a section of the displayed report. reports with these presentation styles are divided into four bands: header, detail, summary, and footer. Each band is identified by a bar containing the name of the band above the bar and an Arrow pointing to the band.
CHAPTER 6 Enhancing Reports You can specify additional heading information (such as a date) in the header band and you can include pictures, graphic controls, and color to enhance the appearance of the band. Displaying the current date To include the current date in the header, you place a computed field that uses the Today InfoMaker expression function in the header band. For information, see “Adding computed fields to a report” on page 239. The detail band The detail band displays the retrieved data.
Working in the Report painter The summary and footer bands You use the summary and footer bands of the report the same way you use summary pages and page footers in a printed report: • The contents of the summary band display at the end, after all the detail rows; this band often summarizes information in the report • The contents of the footer band display at the bottom of each screen or page of the report; this band often displays the page number and name of the report Using the Report painter toolba
CHAPTER 6 Enhancing Reports Using the Properties view in the Report painter Each part of the report (such as text, columns, computed fields, bands, graphs, even the report itself) has a set of properties appropriate to the part. The properties display in the Properties view. You can use the Properties view to modify the parts of the report. ❖ To use the Properties view to modify the parts of the report: 1 Position the mouse over the part you want to modify.
Working in the Report painter ❖ To select neighboring multiple controls in a report in the Design view (lasso selection): 1 Press and hold the left mouse button at one corner of the neighboring controls. 2 Drag the mouse over the controls you want to select. A bounding box (the lasso) displays. 3 Release the mouse button. All the controls in the bounding box are selected. ❖ To select non-neighboring multiple controls in a report in the Design view: 1 Click the first control.
CHAPTER 6 Displaying information about the selected control Enhancing Reports The name, x and y coordinates, width, and height of the selected control are displayed in the MicroHelp bar. If multiple controls are selected, Group Selected displays in the Name area and the coordinates and size do not display. Resizing bands in the Report painter Design view You can change the size of any band in the report.
Using the Preview view of a report Using the Preview view of a report You use the Preview view of a report to view it as it will appear with data and test the processing that takes place in it. ❖ To display the Preview view of a report open in the Report painter: 1 If the Preview view is not already displayed, select View>Preview from the menu bar.
CHAPTER 6 Previewing without retrieving data Enhancing Reports If you do not want InfoMaker to retrieve data from the database automatically when the Preview view opens, you can clear the Retrieve on Preview option. The Preview view shows the report without retrieving data. ❖ To be able to preview without retrieving data automatically: 1 Select Design>Options from the menu bar. The Report Options dialog box displays. 2 Clear the Retrieve on Preview check box on the General page.
Using the Preview view of a report Supplying argument values or criteria If the report has retrieval arguments or is set up to prompt for criteria, you are prompted to supply values for the arguments or to specify criteria. InfoMaker retrieves the rows. As InfoMaker retrieves, the Retrieve button changes to a Cancel button. You can click the Cancel button to stop the retrieval at any time. Sharing data with the Data view The Data view displays data that can be used to populate a report.
CHAPTER 6 ❖ Enhancing Reports To modify existing data: • Tab to the field and enter a new value. The Preview view uses validation rules, display formats, and edit styles that you have defined for the columns, either in the Database painter or in this particular report. To save the changes to the database, you must apply them, as described next. ❖ To add a row: 1 Click the Insert Row button. InfoMaker creates a blank row. 2 Enter data for a row.
Using the Preview view of a report ❖ To import data into a report: 1 Select Rows>Import from the menu bar. 2 Specify the file from which you want to import the data. The types of files that you can import into the painter display in the List Files of Type drop-down list. 3 Click Open. InfoMaker reads the data from the file into the Report painter. You can view the data and save the data in an external file.
CHAPTER 6 Controlling the display of rulers Enhancing Reports You can choose whether to display rulers around page borders. ❖ To control the display of rulers in Print Preview: • Changing margins Select/deselect File>Print Preview Rulers from the menu bar. You can dynamically change margins while previewing a report. ❖ To change the margins in Print Preview: • Drag the margin boundaries on the rulers. The following picture shows the left and top margin boundaries.
Using the Preview view of a report Printing data You can print a report while the Preview view is displayed. You can print all pages, a range of pages, only the current page, or only odd or even pages. You can also specify whether you want multiple copies, collated copies, and printing to a file. Avoiding large rows To avoid multiple blank pages and other anomalies in printed reports, no row in the report should be larger than the size of the target page.
CHAPTER 6 Enhancing Reports The extension PRN indicates that the file is prepared for the printer. Change the drive, the directory, or both, if you want. Working in a grid report If you are viewing a grid-style report in the Preview view, you can make the following changes.
Saving data in an external file • To select non-neighboring columns, press and hold Ctrl, then click the headers. • To select cells, press the left mouse button on the bottom border of a cell and drag the mouse. Selected cells are highlighted. 2 Select Edit>Copy from the menu bar. The contents of the selected cells are copied to the clipboard. If you copied the contents of more than one column, the data is separated by tabs.
CHAPTER 6 Enhancing Reports The rest of this section provides more information about saving data in PDF, HTML, and PSR formats. For more information about saving data as XML, see Chapter 12, “Exporting and Importing XML Data.” Saving the data as PDF InfoMaker provides two ways to save a report or DataStore in Portable Document Format (PDF).
Saving data in an external file Saving as PDF using the distill method If you want to save to PDF using the distill method, you do not need to change any properties. The distill method is used by default when you select Save Rows As from the File menu in the Report painter and select PDF as the file type, or when you use the SaveAs method with PDF! as the file type. InfoMaker uses a PostScript printer driver specifically designed for distilling purposes to configure the PDF output.
CHAPTER 6 Enhancing Reports Saving as PDF fails at runtime on Windows 2003 Server. This is caused by a Group Policy that by default disallows installation of printers that use kernel-mode drivers. Kernel-mode drivers have access to system-wide memory, and poorly written drivers can cause system failures. To allow installation of kernel-mode drivers, follow these steps: 1 Select Run from the Windows Start menu. 2 In the Open box, type gpedit.msc and click OK.
Saving data in an external file 4 Save the report, then select File>Save Rows As, select PDF as the Save As Type, specify a file name, and click Save. InfoMaker saves the data in the report to the file you specified. If you selected the Print Using XSLFOP check box, it also sends the PDF file to the default printer for your system.
CHAPTER 6 Enhancing Reports For more information about configuring fonts, see the Apache Web site at http://xml.apache.org/fop. Saving the data in HTML Table format HTML Table format is one of the formats in which you can choose to save data. When you save in HTML Table format, InfoMaker saves a style sheet along with the data.
Saving data in an external file Working with PSR files A PSR file is a special file with the extension PSR created by PowerBuilder, InfoMaker, or DataWindow Designer. Windows and PSR files When InfoMaker is installed, the PSR file type is registered with Windows. A PSR file contains a report definition (source and object) as well as the data contained in the report when the PSR file was created. Figure 6-1: PSR file About reports A report is the same as a nonupdatable DataWindow object.
CHAPTER 6 ❖ Enhancing Reports To open a PSR file in InfoMaker using Explorer or File Manager or from a mail message: • Double-click the PSR file name. InfoMaker displays the report. ❖ To open a PSR file from the menu bar in the Report painter: 1 Select File>Open File from the menu bar. The Select a File Name dialog box displays. 2 Select the PSR file you want. Change drives and directories if needed. The Report painter displays the report in the Design view.
Modifying general report properties InfoMaker mails the PSR file. The recipient can open the report by doubleclicking it if InfoMaker is installed. Modifying general report properties This section describes the general report properties that you can modify.
CHAPTER 6 3 Enhancing Reports Specify the number of milliseconds you want between internal timer events in the report. This value determines how often InfoMaker updates the time fields in the report. (Enter 60,000 milliseconds to specify one minute.) 4 If the DataWindow contains buttons, set the ShowBackColorOnXP property to make sure that the background color you select for the buttons displays on systems using the XP style.
Modifying general report properties Setting gradients and background pictures in a report You can use the background effects to give the report more visual interest. For example, you can set a vertical gradient on a header band to differentiate it from the other bands in the report: ❖ ❖ 218 To set a gradient background in a report: 1 Position the mouse on an empty spot in the report, display the pop-up menu, and select Properties.
CHAPTER 6 4 Enhancing Reports From the Tile Mode drop-down list, select the style you want to use. Selections from the drop-down list allow you to display the picture in its original size, stretch the picture in different directions, or tile multiple copies of the picture in a variety of possible patterns. Setting transparency properties for a report You can change the transparency settings for colors and pictures.
Modifying general report properties ❖ To specify basic grid report properties: 1 Position the mouse pointer on the background in a grid report, display the pop-up menu, and select Properties. 2 Select the options you want in the Grid section on the General page in the Properties view as described in Table 6-3.
CHAPTER 6 Enhancing Reports Defining print specifications for a report When you are satisfied with the look of the report, you can define its print specifications. ❖ To define print specifications for a report: 1 In the Report painter, select Properties from the report’s pop-up menu. 2 In the Units box on the General page, select a unit of measure. It is easier to specify the margins when the unit of measure is inches or centimeters. 3 Select the Print Specifications tab.
Modifying general report properties Table 6-4: Setting print specifications for reports Setting Document Name Printer Name Margins Paper Orientation Description Specify a name to be used in the print queue to identify the report. Specify the name of a printer to which this report should be sent. If this box is empty, the report is sent to the default system printer.
CHAPTER 6 Enhancing Reports Setting Description Print Preview Shows Outline Select to display a blue outline to show the location of the margins. Print Shows Background Whether the background settings of the DataWindow and controls are included when the DataWindow is printed. Preview Shows Background Newspaper Columns Across and Width Whether the background settings of the DataWindow and controls display in the print preview.
Modifying general report properties 1 Create a tabular report, selecting the last name, first name, and phone number columns, and add a title, page number, and date. The Emp_Fname column and the text control holding a comma are defined as Slide Left, so they display just to the right of the Emp_Lname column. 2 On the Print Specifications page of the report’s Properties view, specify two columns across and a column width of 3.5 inches in the Newspaper Columns boxes.
CHAPTER 6 Enhancing Reports The finished report has one set of page heading information and two columns of column header and detail information. Modifying text in a report When InfoMaker initially generates the basic report, it uses the following attributes and fonts: • For the text and alignment of column headings and labels, InfoMaker uses the extended column attributes made in the Database painter. • For fonts, InfoMaker uses the definitions made in the Database painter for the table.
Modifying general report properties • Change the text properties in the StyleBar. • Select the Font page in the control’s Properties view and change the properties there. Naming controls in a report You use names to identify columns and other controls in filters and in InfoMaker expression functions. The Report painter automatically generates names for all controls in a report. To name columns, labels, and headings, the Report painter uses database and extended attribute information.
CHAPTER 6 ❖ Enhancing Reports To add a border to a control in a report: 1 Select one or more controls. 2 Select the border you want from the Border drop-down toolbar in the PainterBar. InfoMaker places the border around the selected controls. You can also specify a border for one or more controls in the Properties view on the General page. Specifying variable-height bands in a report Sometimes reports contain columns whose data is of variable length.
Modifying general report properties In the Preview view, the band resizes based on the contents of the columns you defined as having their height sized automatically. Using the RowHeight function with Autosize Height When a detail band has Autosize Height set to “true”, you should avoid using the RowHeight InfoMaker expression function to set the height of any element in the row. Doing so can result in a logical inconsistency between the height of the row and the height of the element.
CHAPTER 6 2 Enhancing Reports Modify the SELECT statement graphically using the same techniques as when creating it. For more information, see “Using SQL Select” on page 165. Modifying the statement syntactically Select Design>Convert to Syntax from the menu bar to modify the SELECT statement syntactically. 3 Click the Return button to return to the painter.
Storing data in a report using the Data view Modifying the result set If the data source is External or Stored Procedure, you can modify the result set description. ❖ If the data source is a stored procedure To modify a result set: 1 If the Column Specification view is not open, select View>Column Specifications from the menu bar. 2 Review the specifications and make any necessary changes.
CHAPTER 6 ❖ Enhancing Reports To store data in a report: 1 If the Data view is not already displayed, select View>Data from the menu bar. In the default layout for the Report painter, the Data view displays in a stacked pane under the Properties view. All columns defined for the report are listed at the top. 2 Do any of the following: • Click the Insert Row button in the PainterBar to create an empty row and type a row of data. You can enter as many rows as you want.
Retrieving data Retrieving data In a report, you can prompt for retrieval criteria, retrieve rows as needed, and save retrieved rows to disk. Prompting for retrieval criteria in a report You can define your report so that it always prompts for retrieval criteria just before it retrieves data. ❖ To prompt for retrieval criteria in a report: 1 If the Column Specifications view is not already displayed, select View>Column Specifications from the menu bar.
CHAPTER 6 Using edit styles Enhancing Reports If a column uses a code table or the RadioButton, CheckBox, or DropDownListBox edit style, an arrow displays in the column header and you can select a value from a drop-down list when specifying criteria: If you do not want the drop-down list used for a column for specifying retrieval criteria to display, select the Override Edit check box on the General page of the column’s Properties view.
Retrieving data Retrieve Rows As Needed is overridden if you have specified sorting or have used aggregate functions, such as Avg and Sum, in the report. This is because InfoMaker must retrieve every row before it can sort or perform aggregates. In a multiuser situation, Retrieve Rows As Needed might lock other people out of the tables.
CH A PTE R About this chapter Contents 7 Working with Controls in Reports One of the ways you can enhance a report is to add controls, such as columns, drawing objects, buttons, and computed fields. You can also change the layout of the report by reorganizing, positioning, and rotating controls. This chapter shows you how.
Adding controls to a report ❖ To add a column from the data source to a report: 1 Select Insert>Control>Column from the menu bar. 2 Click where you want to place the column. The Select Column dialog box displays, listing all columns included in the data source of the report. 3 Insert columns instead of copying them Select the column and click OK. If you want to add a column from the DataWindow definition to a DataWindow, always use Insert>Control>Column.
CHAPTER 7 Working with Controls in Reports Displaying an ampersand character If you want to display an ampersand character, type a double ampersand in the Text field. A single ampersand causes the next character to display with an underscore because it is used to indicate accelerator keys.
Adding controls to a report Adding a group box to a report To visually enhance the layout of a report, you can add a group box. A group box is a static frame used to group and label a set of controls in a report. The following example shows two group boxes in a report. The Address group box groups address information and the Phone/Fax group box groups telephone numbers. ❖ To add a group box to a report: 1 Select Insert>Control>GroupBox from the menu bar and click in the Design view.
CHAPTER 7 Working with Controls in Reports The picture must be a bitmap (BMP), runlength-encoded (RLE), Windows metafile (WMF), Graphics Interchange Format (GIF), or Joint Photographic Experts Group (JPEG) file. 4 Display the pop-up menu and select Original Size to display the image in its original size. You can use the mouse to change the size of the image in the Report painter.
Adding controls to a report Computed columns versus computed fields When creating a report, you can define computed columns and computed fields as follows: The difference between the two ways • In the SQL Select painter, you can define computed columns when you are defining the SELECT statement that will be used to retrieve data into the report. • In the Report painter, you can define computed fields after you have defined the SELECT statement (or other data source).
CHAPTER 7 Working with Controls in Reports InfoMaker expressions You are entering an InfoMaker expression, not a SQL expression processed by the DBMS, so the expression follows the rules for InfoMaker expressions. For complete information about InfoMaker expressions, see Chapter 23, “Operators and Expressions.” Referring to next and previous rows You can refer to other rows in a computed field. This is particularly useful in N-Up reports when you want to refer to another row in the detail band.
Adding controls to a report ❖ To summarize values: 1 Select one or more columns in the DataWindow object’s detail band. 2 Select one of the options at the bottom of the cascading menu: Average, Count, or Sum. The same options are available at the bottom of the Controls drop-down toolbar on the PainterBar. InfoMaker places a computed field in the summary band or in the group trailer band if the report is grouped. The band is resized automatically to hold the computed field.
CHAPTER 7 5 Working with Controls in Reports Select a function and click OK. You return to the Toolbar Item Command dialog box. 6 Specify text and microhelp that displays for the button, and click OK. InfoMaker places the new button in the PainterBar. You can click it to add a computed field to your report the same way you use the built-in Sum button. Adding buttons to a report The Button control is a command or picture button that can be placed in a report.
Adding controls to a report Controlling the display of buttons in print preview and in printed output You can choose whether to display buttons in print preview or in printed output. You control this in the Properties view for the report (not the Properties view for the button). ❖ To control the display of buttons in a report in print preview and on printed output: 1 Display the report’s Properties view with the Print Specification page on top. 2 Select the Display Buttons – Print check box.
CHAPTER 7 Working with Controls in Reports Adding graphs to a report Graphs are one of the best ways to present information. For example, if your report displays sales information over the course of a year, you can easily build a graph in a report to display the information visually. InfoMaker offers many types of graphs and provides you with the ability to control the appearance of a graph to best meet your application’s needs. For information on using graphs, see Chapter 13, “Working with Graphs.
Reorganizing controls in a report Adding reports to a report You can nest reports (nonupdatable DataWindow objects) in a report. For information on nesting reports, see Chapter 11, “Using Nested Reports.” Adding tooltips to a DataWindow control Tooltips display text when the pointer pauses over a DataWindow column or control. This text can be used to explain the purpose of the column or control.
CHAPTER 7 2 Working with Controls in Reports Select the Show Edges check box. InfoMaker displays the boundaries of each control in the report. Boundaries display only in the Design view The boundaries displayed for controls are for use only in the Design view. They do not display in a running report or in a printed report. Using the grid and the ruler in a report The Report painter provides a grid and a ruler to help you align controls.
Reorganizing controls in a report Moving controls in a report In all presentation styles except Grid In all presentation styles except Grid, you can move all the controls (such as headings, labels, columns, graphs, and drawing controls) anywhere you want. ❖ In grid reports To move controls in a report: 1 Select the controls you want to move. 2 Do one of the following: • Drag the controls with the mouse. • Press an arrow key to move the controls in one direction.
CHAPTER 7 Working with Controls in Reports Resizing controls in a report You can resize a control using the mouse or the keyboard. You can also resize multiple controls to the same size using the Layout drop-down toolbar on PainterBar2. Using the mouse To resize a control using the mouse, select it, then grab an edge and drag it with the mouse.
Reorganizing controls in a report 4 From the cascading menu, select the dimension along which you want to align the controls. For example, to align the controls along the left side, select the first choice on the cascading menu. You can also use the Layout drop-down toolbar on PainterBar2 to align controls. InfoMaker moves all the selected controls to align with the first one.
CHAPTER 7 Working with Controls in Reports You can also use the Layout drop-down toolbar on PainterBar2 to size controls. Sliding controls to remove blank space in a report You can specify that you want to eliminate blank lines or spaces in a report by sliding columns and other controls to the left or up if there is blank space. You can use this feature to remove blank lines in mailing labels or to remove extra spaces between fields (such as first and last name).
Positioning controls in a report In the following label, emp_lname, the comma, state, and zip_code are specified as slide left. Edges are shown to indicate the spacing between the columns. Notice that there is a small amount of space between controls.
CHAPTER 7 Default positioning Working with Controls in Reports InfoMaker uses the defaults shown in Table 7-4 when you place a new control in a report. Table 7-4: Default position properties for controls in a report ❖ Control Graph Default positioning Foreground, movable, resizable All other controls Band, not movable, not resizable To change the position of a control in a report: 1 Select Properties from the control’s pop-up menu and then select the Position tab.
Rotating controls in a report For example, in a grid report, make the band deeper and move the control down into the center of the band. 4 Display the Modify expression dialog box for the Escapement property. (Click the button next to the Escapement property on the Font property page.) 5 Specify the amount of rotation you want as an integer in tenths of a degree. (For example, 450 means 45 degrees of rotation; 0 means horizontal or no rotation.
CHAPTER 7 Working with Controls in Reports 7 Drag and drop the control in the Design view until it is where you want it. 8 In the Design view, select the control that is being rotated and deselect the Moveable check box. If you are using a conditional expression for rotation If you are specifying different rotations depending on particular conditions, you might need to add conditions to the x and y properties for the control to move the control conditionally to match the various amounts of rotation.
Rotating controls in a report 256 InfoMaker
CH A PTE R About this chapter Contents 8 Displaying and Validating Data This chapter describes how to customize your report or form by modifying the display values in columns and specifying validation rules.
About displaying and validating data Database painter is required You must have the Database painter installed to define display formats and edit styles in your database. Presenting the data When you generate a new report or form, InfoMaker presents the data according to the properties already defined for a column, such as a column’s display format and edit style. Display formats Display formats embellish data values while still displaying them as letters, numbers, and special characters.
CHAPTER 8 Displaying and Validating Data If you want data to display differently depending on whether the focus is on or off the column, specify an edit mask (on the Edit property page for the column) as well as a display format (on the Format property page for the column), then check the Use Format check box on the Format property page. The Use Format check box displays only when an edit mask has been specified.
Working with display formats Display formats not used for data entry When you tab to a column containing a display format, InfoMaker removes the display format and displays the raw value for you to edit. If you want to provide formatting used for data entry, you need to specify edit masks, as described in “The EditMask edit style” on page 278. Working with display formats You work with display formats in the Database painter, the Form painter, and the Report painter.
CHAPTER 8 Displaying and Validating Data If the definition of the display format later changes in the extended attribute system tables, the format for the column in a report or form does not change. If you want to use the modified format, you can reapply it to the column in the Form or Report painter. Working with display formats in the Database painter Typically, you define display formats and associate them with columns in the Database painter, because display formats are properties of the data itself.
Working with display formats ❖ To associate a display format with a column in the Database painter: 1 In the Database painter Objects view, position the pointer on the column, select Properties from the pop-up menu, and select the Display tab in the Properties view. 2 Select a format from the list in the Display Format box. The column now has the selected format associated with it in the extended attribute system tables.
CHAPTER 8 2 Displaying and Validating Data Do one of the following: • Delete the display format. • Select a format in the extended attribute system tables from the pop-up list. • Create a format for the column by typing it in the Format box. For more information, see “Defining display formats” next. Format not saved in the extended attribute system tables If you create a format here, it is used only for the current column and is not saved in the extended attribute system tables.
Defining display formats Combining formats You can include different types of display format masks in a single format. Use a space to separate the masks. For example, the following format section includes a date and time format: mmmm/dd/yyyy h:mm Using sections Each type of display format can have multiple sections, with each section corresponding to a form of the number, string, date, or time. Only one section is required; additional sections are optional and should be separated with semicolons (;).
CHAPTER 8 Displaying and Validating Data Table 8-1 lists the blue, green, and red values you can use in the formula to create other colors.
Defining display formats Use at least one 0 In general, a number display format should include at least one 0. If you enter 0 in a field with the mask ###, the field will appear to be blank if you do not provide a zero-format section. If the mask is ###.##, only the period displays. If you want two decimal places to display even if both are 0, use the mask ##0.00.
CHAPTER 8 Displaying and Validating Data Format 5 -5 .5 $#,##0;-$#,##0 $#,##0;[RED]($#,##0) $5 $5 -$5 ($5) $1 $1 [Currency] $#,##0.00;($#,##0.00) $5.00 $5.00 ($5.00) ($5.00) $0.50 $0.50 $#,##0.00;[RED]($#,##0.00) ##0% $5.00 500% ($5.00) -500% $0.50 50% ##0.00% 0.00E+00 500.00% 5.00E+00 -500.00% -5.00E+00 50.00% 5.00E-01 String display formats String display formats can have two sections.
Defining display formats Special characters Table 8-4 shows characters that have special meaning in date display formats.
CHAPTER 8 Displaying and Validating Data Table 8-5: Date display format examples Format [red]m/d/yy Displays 1/30/98 in red d-mmm-yy dd-mmmm 30-Jan-98 30-January mmm-yy dddd, mmm d, yyyy Jan-98 Friday, Jan 30, 1998 Time display formats Time display formats can have two sections.
About edit styles Time keyword You can use the following keyword as a time display format to specify the format specified in the Windows control panel: • Examples [Time] Table 8-7 shows how the time 9:45:33:234567 PM displays when different format masks are applied.
CHAPTER 8 Displaying and Validating Data An EditMask edit style is assigned automatically to some numeric and currency columns. To use a different display format for these columns, make sure you check the Use Format check box on the Format property page. Edit styles Table 8-8 shows the available edit styles.
Working with edit styles Working with edit styles You work with edit styles in the Database painter, Form painter, and Report painter. What you do in the Database painter In the Database painter, you can: • Create, modify, and delete named edit styles The edit styles are stored in the extended attribute system tables. Once you define an edit style, it can be used by any column of the appropriate datatype in the database.
CHAPTER 8 ❖ Displaying and Validating Data To create a new edit style: 1 In the Database painter, select Object>Insert>Edit Style from the menu bar. 2 In the Object Details view, select the edit style type from the Style drop-down list. 3 Specify the properties of the edit style. For information, see “Defining edit styles” on page 274. You can use the new edit style with any column of the appropriate datatype in the database.
Defining edit styles Working with edit styles in the Form or Report painter An edit style you assign to a column in the Database painter is used by default when you place the column in a form or report. You can override the edit style in the Form or Report painter by choosing another edit style from the extended attribute system tables or defining an ad hoc style for one specific column.
CHAPTER 8 Displaying and Validating Data • To have entered values display as asterisks for sensitive data, check the Password box. • To allow you to tab to the column but not change the value, check the Display Only box. • To define a code table to determine which values are displayed and which values are stored in the database, check the Use Code Table box and enter display and data values for the code table. See “Defining a code table” on page 284.
Defining edit styles In the preceding example, when you see the value Business Services, the corresponding data value could be 200. ❖ To use the DropDownListBox edit style: 1 Select DropDownListBox from the Style Type list. 2 Select the appropriate properties. 3 Enter the value you want to have appear in the Display Value box and the corresponding data value in the Data Value box. For more about code tables, see “Defining a code table” on page 284.
CHAPTER 8 Centering check boxes without text Displaying and Validating Data You may find it useful to center check boxes used for columns of information. First make the text control used for the column header and the column control the same size and left aligned. Then you can center the check boxes and the column header.
Defining edit styles The EditMask edit style Sometimes you need to enter data that has a fixed format. For example, in North America phone numbers have a 3-digit area code, followed by three digits, followed by four digits. You can define an edit mask that specifies the format to make it easier for you to enter values: Edit masks consist of special characters that determine what can be entered in the column. They can also contain punctuation characters to aid you.
CHAPTER 8 Keyboard behavior Displaying and Validating Data Note the following about how certain keystrokes behave in edit masks: • Both Backspace and Shift + Backspace delete the preceding character. • Delete deletes everything that is selected. • Non-numeric edit masks treat any characters that do not match the mask pattern as delimiters. Also, note certain behavior in Date edit masks: • Entering zero for the day or month causes the next valid date to be entered.
Defining edit styles The preferred method of creating a currency editmask is to use the predefined [currency(7)] - International mask. You can change the number in parentheses, which is the number of characters in the mask including two decimal places. When you use this mask, InfoMaker uses the currency symbol and format defined in the regional settings section of the Windows control panel. You can enter negative values in a column that uses a currency mask.
CHAPTER 8 Displaying and Validating Data The DropDownDataWindow edit style Sometimes another data source determines which data is valid for a column. Consider this situation: the Department table includes two columns, Dept_id and Dept_name, to record your company’s departments. The Employee table records your employees. The Department column in the Employee table can have any of the values in the Dept_id column in the Department table.
Defining edit styles In the example, you would specify the DropDownDataWindow edit style for the dept_id column that you want to display with the department name as well as the department ID: What happens 3 Click the browse button next to the DataWindow box and select the report that contains the data for the column from the list (in the example, d_dddw_dept). The list includes all the reports in the current library.
CHAPTER 8 Displaying and Validating Data Limit on size of data value The data value for a column that uses the DropDownDataWindow edit style is limited to 511 characters. The RichText edit style You can use the RichText edit style to display column data in a rich text format, and to use different fonts and colors in the same data field. Columns that you format with the RichText edit style require considerably more storage space than columns with plain text edit styles.
Defining a code table The InkEdit edit style is fully functional on Tablet PCs. On other computers, it behaves like the Edit edit style. For more information about ink controls and the Tablet PC, and to download the Tablet PC SDK, go to the Microsoft Tablet PC Web site at http://msdn.microsoft.com/en-us/library/ms950406.aspx. Defining a code table To reduce storage needs, frequently you might want to store short, encoded values in the database, but these encoded values might not be meaningful to users.
CHAPTER 8 Displaying and Validating Data Allowing null values An internal InfoMaker code, NULL!, indicates null values are allowed. To use this code, specify NULL! as the data value, then specify a display format for nulls for the column. ❖ To define a code table as a property of the Edit edit style: 1 Select the Use Code Table check box. 2 Enter the display and data values for the code table. 3 If you want to restrict input in the column to values in the code table, select the Validate check box.
Defining a code table How code tables are processed When data is retrieved into a report or form column with a code table, processing begins at the top of the data value column. If the data matches a data value, the corresponding display value displays. If there is no match, the actual value displays. Consider the example in Table 8-10.
CHAPTER 8 • Displaying and Validating Data For the DropDownListBox and DropDownDataWindow edit styles, clear the Allow Editing check box: you cannot type a value. Although users cannot type a value when Allow Editing is false, they can search for a row in the drop-down list or DataWindow by typing in the initial character for the row display value. The search is case sensitive. For the DropDownDataWindow edit style, the initial character for a search cannot be an asterisk or a question mark.
Working with validation rules Understanding validation rules Validation rules are criteria that a form uses to validate data entered into a column. They are InfoMaker-specific and therefore not enforced by the DBMS. Validation rules apply to forms (which support updating) but not to reports. Validation rules assigned in the Database painter are used by default when you place columns in a form. You can override the default rules in the Report painter.
CHAPTER 8 Displaying and Validating Data Defining validation rules Typically, you define validation rules in the Database painter, because validation rules are properties of the data itself. Once defined in the Database painter, the rules are used by default each time the column is placed in a form. You can also define a validation rule in the Report painter that overrides the rule defined in the Database painter.
Defining validation rules ❖ To modify a validation rule: 1 In the Database painter, open the Extended Attributes view. 2 In the Extended Attributes view, open the list of validation rules. 3 Double-click the validation rule you want to modify. 4 In the Validation Rule view, modify the validation rule as desired. For information, see “Defining the expression” on page 290 and “Customizing the error message” on page 291.
CHAPTER 8 Displaying and Validating Data Use the notation @placeholder (where placeholder is any group of characters) to indicate the current column in the rule. When you define a validation rule in the Database painter, InfoMaker stores it in the extended attribute system tables with the placeholder name. At runtime, InfoMaker substitutes the value of the column for placeholder. Pasting the placeholder The @col can be easily used as the placeholder. A button in the Paste area is labeled with @col.
Defining validation rules 'Item ~'' + @Col + '~' does not pass validation test.' You can edit the string expression to create a custom error message. Different syntax in the Report painter If you are working in the Report painter, you can enter a string expression for the message, but you do not use the @ sign for placeholders. For example, this is the default message: 'Item ~'' + ColumnName + '~' does not pass validation test.
CHAPTER 8 3 Displaying and Validating Data (Optional) Enter a string or string expression to customize the validation error message. For more information, see “Customizing the error message” on page 291. Specifying the expression Since a user might have just entered a value in the column, validation rules refer to the current data value, which you can obtain through the GetText InfoMaker expression function. Using GetText ensures that the most recent data entered in the current column is evaluated.
How to maintain extended attributes To pass the validation rule, the data must be all digits (must match the text pattern ^[0-9]+$) and must be less than the amount in the Full_Price column. Notice that to compare the numeric value in the column with the numeric value in the Full_Price column, the Real function was used to convert the text to a number.
CHAPTER 8 3 Displaying and Validating Data Do one of the following: • To create a new entity, display the pop-up menu for the type you want to add, then select New. • To modify an entity, display its pop-up menu, then select Properties. • To delete an entity, display its pop-up menu, then select Delete. Caution If you delete a display format, edit style, or validation rule, it is removed from the extended attribute system tables. Columns in the database are no longer associated with the entity.
How to maintain extended attributes 296 InfoMaker
CH A PTE R 9 About this chapter Filtering, Sorting, and Grouping Rows This chapter describes how you can customize your report by doing the following in the Report painter: • Defining filters to limit which of the retrieved rows are displayed in the report • Sorting rows after they have been retrieved from the database • Displaying the rows in groups and calculating statistics on each group Contents Topic Filtering rows Page 297 Sorting rows Grouping rows 299 302 Filtering rows You can use WH
Filtering rows Using filters In the Report painter, you can define filters to limit the rows that display at runtime. Filters can use most InfoMaker expression functions. Filters do not affect which rows are retrieved A filter operates against the retrieved data. It does not re-execute the SELECT statement. Defining a filter ❖ To define a filter: 1 In the Report painter, select Rows>Filter from the menu bar.
CHAPTER 9 4 Filtering, Sorting, and Grouping Rows Click OK. Only rows meeting the filter criteria are displayed in the Preview view. Filtered rows and updates Modifications of filtered rows are applied to the database when you issue an update request. Removing a filter ❖ Examples of filters To remove a filter: 1 Select Rows>Filter from the menu bar. 2 Delete the filter expression from the Specify Filter dialog box, then click OK.
Sorting rows ❖ To sort the rows: 1 Select Rows>Sort from the menu bar. 2 Drag to the Columns box the columns on which you want to sort the rows, and specify whether you want to sort in ascending or descending order. The order of the columns determines the precedence of the sort. To reorder the columns, drag them up or down in the list. To delete a column from the sort columns list, drag the column outside the dialog box.
CHAPTER 9 Filtering, Sorting, and Grouping Rows Suppressing repeating values When you sort on a column, there might be several rows with the same value in one column. You can choose to suppress the repeating values in that column. When you suppress a repeating value, the value displays at the start of each new page and, if you are using groups, each time a value changes in a higher group.
Grouping rows If you change your mind You can remove a column from the suppression list simply by dragging it and releasing it outside the Suppression List box. Grouping rows You can group related rows together and, optionally, calculate statistics for each group separately. For example, you might want to group employee information by department and get total salaries for each department. How groups are defined Each group is defined by one or more report columns.
CHAPTER 9 Filtering, Sorting, and Grouping Rows The following screenshot shows the report. How to do it Users Guide You can create a grouped report in three ways: • Use the Group presentation style to create a grouped report from scratch (“Using the Group presentation style” next). • Take an existing tabular report and define grouping (“Defining groups in an existing report” on page 307). • Use the TreeView presentation style (Chapter 15, “Working with TreeViews”).
Grouping rows Using the Group presentation style One of the report presentation styles, Group, is a shortcut to creating a grouped report. It generates a tabular report that has one group level and some other grouping properties defined. You can then further customize the report. ❖ To create a basic grouped report using the Group presentation style: 1 Select File>New from the menu bar. The New dialog box displays. 2 Choose the Object tab page and the Group presentation style, and click OK.
CHAPTER 9 5 Filtering, Sorting, and Grouping Rows Click Next. InfoMaker suggests a header based on your data source. For example, if your data comes from the Employee table, InfoMaker uses the name Employee in the suggested header. 6 Specify the Page Header text. 7 If you want a page break each time a grouping value changes, select the New Page On Group Break box.
Grouping rows • Places the page header and the date (as a computed field) in the header band • Places the page number and page count (as computed fields) in the footer band • Creates sum-computed fields for all numeric columns (the fields are placed in the group trailer and summary bands) Here is the preceding report in the Preview view: Using an expression for a group If you want to use an expression for one or more column names in a group, you can enter an expression as the Group Definition on th
CHAPTER 9 What you can do Filtering, Sorting, and Grouping Rows You can use any of the techniques available in a tabular report to modify and enhance the grouped report, such as moving controls, specifying display formats, and so on. In particular, see “Defining groups in an existing report” next to learn more about the bands in a grouped report and how to add features especially suited for grouped reports (for example, add a second group level, define additional summary statistics, and so on).
Grouping rows ❖ To define subgroups: 1 Select Rows>Create Group from the menu bar and specify the column/expression for the subgroup. 2 Repeat step 1 to define additional subgroups if you want. You can specify as many levels of grouping as you need. How groups are identified InfoMaker assigns each group a number (or level) when you create the group. The first group you specify becomes group 1, the primary group. The second group becomes group 2, a subgroup within group 1, and so on.
CHAPTER 9 Filtering, Sorting, and Grouping Rows Rearranging the report When you create a group, InfoMaker creates two new bands for each group: • A group header band • A group trailer band The bar identifying the band contains: • The number of the group • The name of the band • The name of each column that defines the group • An arrow pointing to the band You can include any control in the report (such as columns, text, and computed fields) in the header and trailer bands of a group.
Grouping rows At runtime, you see this: Suppressing group headers If you do not want a group header to display at the top of each page when you print or display a report, select the Suppress Group Header check box on the General property page for the header. If none of the headers are suppressed, they all display at the top of each page.
CHAPTER 9 Filtering, Sorting, and Grouping Rows The Modify Expression dialog box displays. 3 Specify the expression that defines the computed field (see below). 4 Click OK. A shortcut to sum values If you want to sum a numeric column, select the column in Design view and click the Sum button in the Controls drop-down toolbar. InfoMaker automatically places a computed field in the appropriate band. Specifying the expression Typically, you use aggregate and other functions in your summary statistic.
Grouping rows The group trailer band in this example shows the average and total salary for the group. At runtime, the average and total salaries are calculated and displayed: Sorting the groups You can sort the groups in a report. For example, in a report showing employee information grouped by department, you might want to sort the departments (the groups) by total salary. Typically, this involves aggregate functions, as described in “Adding summary statistics” on page 310.
CHAPTER 9 Filtering, Sorting, and Grouping Rows The General property page for the group displays in the Properties view. 3 Click the ellipsis button next to the Group Sort property. The Specify Sort Columns dialog box displays. 4 Drag the column you want to sort the groups by from the Source Data box into the Columns box. If you chose a numeric column, InfoMaker uses the Sum function in the expression; if you chose a non-numeric column, InfoMaker uses the Count function.
Grouping rows 7 Specify the expression to sort on. For example, to sort the department group (the first group level) on average salary, specify avg(salary for group 1). 8 Click OK. You return to the Specify Sort Columns dialog box with the expression displayed. 9 Click OK again. At runtime, the groups will be sorted on the expression you specified.
CH A PTE R About this chapter Contents 1 0 Highlighting Information in Reports and Forms This chapter describes how you modify the way information displays in reports and forms based on the conditions you specify. The conditions are usually related to data values, which are not available until runtime.
Highlighting information In most cases, the appearance and behavior of controls is fixed; you do not want them to change at runtime. When you make headings bold when designing them, you want them to be bold at all times. In the following report, the Salary Plus Benefits column has a Shadow box border around every data value in the column.
CHAPTER 10 Highlighting Information in Reports and Forms In the following report, the Salary Plus Benefits column has a Shadow box border highlighting each data value that is greater than $60,000: To control the display of the border, you define a property conditional expression for the column’s Border property. When you run the report, InfoMaker changes the border of individual data values based on the condition (value greater than $60,000).
Highlighting information A closer look at the expression The expression you enter almost always begins with If. Then you specify three things: the condition, what happens if it is true, and what happens if it is false. Parentheses surround the three things and commas separate them: If( expression, true, false ) The following expression is used in the example. Because the expression is for the Border property, the values for true and false indicate particular borders.
CHAPTER 10 Highlighting Information in Reports and Forms Modifying properties conditionally at runtime “Modifying properties at runtime” on page 316 described how you can use conditional expressions that are evaluated at runtime to highlight information in a form or a report. This section presents a procedure for modifying properties at runtime and some examples.
Modifying properties conditionally at runtime Set Font.Weight property to 700 for bold Font properties such as Italic, Strikethrough, and Underline take a boolean value, but to specify a value for bold, you use the Font.Weight property, which takes a range of values. For values and an example, see “Font.Weight” on page 335. 7 Replace the f (false) with the value to use for the property if the condition is false. 8 Click OK.
CHAPTER 10 Highlighting Information in Reports and Forms 3 Select Send to Back from the rectangle’s pop-up menu. 4 To hide the border of the rectangle, set the Pen Style to No Visible Line. 5 Click the button next to the Brush Color property on the General page. 6 In the Modify Expression dialog box, enter the following expression for the Brush.
Modifying properties conditionally at runtime The Properties view changes to show the properties that are common to all selected controls. 2 On the Font page in the Properties view, click the button next to the Escapement property. 3 Enter the number 450 in the Modify Expression dialog box and click OK. The value entered for font escapement is in tenths of degrees, so the number 450 means 45 degrees. You do not have to specify a condition. Typically, you do not specify a condition for control rotation.
CHAPTER 10 Highlighting Information in Reports and Forms To make these controls display in bold with two asterisks if the employee is not from Massachusetts: 1 Select one of the controls, then use Ctrl + click to select the other three controls. The Properties view changes to show the properties that are common to all selected controls. 2 On the Font page in the Properties view, click the button next to the Bold property.
Modifying properties conditionally at runtime Tip You can use underlines, italics, strikethrough, borders, and colors to highlight information. Example 4: changing the size and location of controls The following report shows city and state columns enclosed in a rectangle and underlined. The columns change location if the current row contains data for a customer from the state of New York. The rectangle and the line change both location and size. This example shows how to move the rectangle and line.
CHAPTER 10 Highlighting Information in Reports and Forms To change properties of the rectangle and the line for rows with the state column equal to New York: 1 2 Select the rectangle, display the Position page in the Properties view, and specify expressions for the following properties: Property Expression X Width if (state = 'NY', 2890, 1865) Height if (state = 'NY', 160, 120) Select the line, display the Position page in the Properties view, and specify expressions for the following properties:
Supplying property values Valid values of properties are shown in parentheses in the Properties view wherever possible. For example, the drop-down list showing border selections includes the correct number for specifying each border in parentheses after the name of the border (ShadowBox (1), Underline (4)). Table 10-1: Properties for controls in the Report painter Property Painter option in Properties view Description Background.
CHAPTER 10 Highlighting Information in Reports and Forms Background.Color Description Setting for the background color of a control. In the painter Background Color on the Background page or Font page in the Properties view. Value A number that specifies the control’s background color. For information on specifying colors, see “Specifying colors” on page 343. The background color of a line is the color that displays between the segments of the line when the pen style is not solid. If Background.
Supplying property values Value A number that specifies the type of border. Values are: 0—None 1—Shadow box 2—Box 3—Resize 4—Underline 5—3D Lowered 6—3D Raised Example The following statement specifies that if the person represented by the current row has a status of L (on leave), the status column displays with a Shadow box border: If(status = 'L', 1, 0) In this example, the condition is applied to the Border property of the status column. The following is a portion of the resulting report.
CHAPTER 10 Highlighting Information in Reports and Forms Brush.Color Description Setting for the fill color of a graphic control. In the painter Brush Color on the General page in the Properties view. Value A number that specifies the color that fills the control. For information on specifying colors, see “Specifying colors” on page 343. Example See the example for “Brush.Hatch” next. Brush.Hatch Description Setting for the fill pattern of a graphic control.
Supplying property values The following statement is for the Brush.Color property of the rectangle. If the month of the start date matches the current month or the next one, Brush.Color is set to light gray (12632256). If not, it is set to white (16777215), which means it will not show: If(month( start_date ) = month(today()) or month( start_date ) = month(today())+1 or (month(today()) = 12 and month(start_date)=1), 12632256, 16777215) The following statement is for the Brush.
CHAPTER 10 Highlighting Information in Reports and Forms Font.Escapement (for rotating controls) Description The angle of rotation from the baseline of the text. In the painter Escapement on the Font page in the Properties view. Value An integer in tenths of degrees. For example, 450 means 45 degrees. 0 is horizontal. Example To enter rotation for a control, select the control in the Design view and click the button next to the Escapement property in the Properties view.
Supplying property values How to position controls that are rotated Make the controls movable. To do so, display each control and select the Moveable check box in the Position page. Then in the Preview view, click the rotated text control until a gray box displays (try the center of the text). Drag the rotated control where you want it. In the Design view, the controls will be wherever you dragged them.
CHAPTER 10 Highlighting Information in Reports and Forms Font.Italic Description A number that specifies whether the text should be italic. In the painter Italic on the Font page in the Properties view. Value Values are: 0—Not italic 1—Italic Example The following statements are specified for the Font.Italic, Font.Underline, and Font.Weight properties, respectively. If the employee has health insurance, the employee’s information displays in italics.
Supplying property values Font.Strikethrough Description A number that specifies whether the text should be crossed out. In the painter Strikeout on the Font page in the Properties view. Value Values are: 0—Not crossed out 1—Crossed out Example The following statement is for the Font.Strikethrough property of the emp_id, emp_fname, emp_lname, and emp_salary columns. The status column must be included in the data source even though it does not appear in the report itself.
CHAPTER 10 Highlighting Information in Reports and Forms Font.Underline Description A number that specifies whether the text should be underlined. In the painter Underline on the Font page in the Properties view. Value Values are: 0—Not underlined 1—Underlined Example The following statement, when applied to the Font.
Supplying property values Format Description The display format for a column. In the painter Format on the Format page in the Properties view. Values A string specifying the display format.
CHAPTER 10 Highlighting Information in Reports and Forms Pen.Color Description The color of the line or the outline of a graphic control. In the painter Pen Color on the General page in the Properties view. Value A number that specifies the color of the line or outline. For information on specifying colors, see “Specifying colors” on page 343. Example See the example for the Pen.Style property, next. Pen.Style Description The style of the line or the outline of a graphic control.
Supplying property values The following statement is for the Pen.Color property of the line around the edge of the rectangle. If the month of the start date matches the current month or the next one, Pen.Color is set to light gray (12632256). If not, it is set to white (16777215), which means it will not show: If(month( start_date ) = month(today()) or month( start_date ) = month(today())+1 or (month(today()) = 12 and month(start_date)=1), 12632256, 16777215) The following statement is for the Pen.
CHAPTER 10 Highlighting Information in Reports and Forms Pen.Width Description The width of the line or the outline of a graphic control. In the painter Pen Width on the General page in the Properties view. Value An integer in the unit of measure specified for the report. Units of measure include PowerBuilder units, thousandths of an inch (1000 = 1 inch), thousandths of a centimeter (1000 = 1 centimeter), or pixels.
Supplying property values Example The following condition, entered for the Pointer property of every control in a row of expense data, changes the pointer to a column every time the value in the expense column exceeds $100,000. Note that the pointer has no meaning in a printed report. The pointer is for use on the screen display of a report: If(expense 100000, 'pbcolumn.cur', 'arrow!') Protect Description The protection setting of a column.
CHAPTER 10 Highlighting Information in Reports and Forms The status column must be retrieved The status column must be included in the data source even though it does not appear in the report itself. The Design view includes the text control at the right-hand end of the detail line. The text control is visible at runtime only if the value of the status column for the row is L. In the resulting report, the text control is visible only for the two employees on leave. For a picture, see “Font.
Supplying property values Example The following statement causes a rectangle to be located 6.250 inches from the left if the state column for the row has the value NY. Otherwise, the rectangle is 4.000 inches from the left: If(state = 'NY', 6250, 4000) For more details and pictures, see “Example 4: changing the size and location of controls” on page 324. X1, X2 Description The distance of each end of the line from the left edge of the report as measured in the Design view.
CHAPTER 10 Highlighting Information in Reports and Forms Y1, Y2 Description The distance of each end of the specified line from the top of the band in which the line is located. In the painter Y1, Y2 on the Position page in the Properties view. Value Integers in the unit of measure specified for the report. Units of measure include PowerBuilder units, thousandths of an inch (1000 = 1 inch), thousandths of a centimeter (1000 = 1 centimeter), or pixels.
Specifying colors Sample numeric calculation To create cyan, you use blue and green, but no red. If you wanted to create the most saturated (bright) cyan, you would use maximum amounts of blue and green in the formula, which is indicated by the number 255 for each.
CH A PTE R About this chapter 11 Using Nested Reports This chapter provides information about creating reports that have other reports nested in them. Contents About reports and DataWindow objects Topic About nested reports Page 345 Creating a report using the Composite presentation style Placing a nested report in another report 349 351 Working with nested reports 354 A report is the same as a nonupdatable DataWindow object.
About nested reports Composite report For example, the following composite report consists of three tabular reports. One of the tabular reports includes a graph: Composite report in the Design view In the Design view, you see three boxes that represent the individual tabular reports that are included in the composite report.
CHAPTER 11 About placing a nested report within another report Using Nested Reports You can place one or more reports within another report. The report you place is called the nested report. You can place a nested report in any type of report except crosstab. Most of the time you will place nested reports in freeform or tabular reports. Often, the information in the nested report depends on information in the report in which it is placed (the base report).
About nested reports What you see in the Design view In the Design view, you see everything in the base report plus a box that represents the related nested report: The difference between nested and composite reports There are two important differences between nesting using the Composite style and nesting a report within a base report. Data sources The composite report does not have a data source—it is just a container for nested reports.
CHAPTER 11 Limitations on nesting reports Using Nested Reports For the most part you can nest the various types of report styles. However, limitations apply to two of them. Crosstabs You cannot place a crosstab with retrieval arguments within another report as a related nested report. However, you can include a crosstab in a Composite report. RichText reports You cannot nest a RichText report in any way.
Creating a report using the Composite presentation style 5 Select File>Save from the menu bar and assign a name to the composite report. 6 Look at the Preview view of the report: Working with composite reports Many of the options available for working with reports, such as Rows>Filter, Rows>Import, and Rows>Sort, are disabled for a composite report. If you want to use any of these options, you need to access the nested report(s), where these options are available.
CHAPTER 11 Using Nested Reports Placing a nested report in another report When you place a nested report in another report, the two reports can be independent of each other, or they can be related in the database sense by sharing some common data such as a customer number or a department number. If the reports are related, you need to do some extra things to both the base report and the related nested report.
Placing a nested report in another report The WHERE clause in this example tells the DBMS to retrieve rows where the value in the column cust_id equals the value of the argument :customerid: At this point, when you run the report to retrieve data, you are prompted to enter a value for :customerid. Later in these steps, you will specify that the base report supply the values for :customerid instead of prompting for values. 4 Open or create the report you want to have as the base report.
CHAPTER 11 8 Using Nested Reports With the report still selected, select the General page of the Properties view. The Arguments box lists arguments defined for the nested report and provides a way for you to specify how information from the base report will be used to supply the values of arguments to the nested report. 9 Supply the base report column or the expression that will supply the argument’s value. To do this, click the button in the Expression column.
Working with nested reports Placing an unrelated nested report in another report When you place an unrelated nested report in a base report, the entire nested report appears with each row of the base report. ❖ To place an unrelated nested report in another report: 1 Create or open the report you want as the base report. 2 Select Insert>Control>Report from the menu bar. 3 In the Design view, click where you want to place the report.
CHAPTER 11 Using Nested Reports • “Supplying retrieval arguments to relate a nested report to its base report” on page 357 • “Specifying criteria to relate a nested report to its base report” on page 359 • “Using options for nested reports” on page 360 Adjusting nested report width and height When you preview a report with nested reports, the width of the nested report may be unacceptable.
Working with nested reports Changing a nested report from one report to another You can change the nested report that is used. For example, you may work on several versions of a nested report and need to update the version of the nested report that the composite or base report uses. ❖ To change the nested report to a different report: 1 Select the nested report in the Design view. 2 In the Properties view, General property page, click the button next to the Report box.
CHAPTER 11 Using Nested Reports Adding another nested report to a composite report After you have created a composite report, you might want to add another report. The following procedure describes how. For information on adding a nested report to a report that is not a composite report, see “Placing a related nested report in another report” on page 351 or “Placing an unrelated nested report in another report” on page 354.
Working with nested reports • You must enable caching in the database profile. Set the SQLCache database parameter to the number of levels of nesting plus 5. For more information, see the description of the SQLCache and DisableBind database parameters in the online Help. Nested reports in composite reports If the base report is a composite report, you need to define retrieval arguments for the composite report before you can supply them to the nested report.
CHAPTER 11 Using Nested Reports The Modify Expression dialog box displays. In this dialog box, you can easily select one of the columns or develop an expression. In the example, the column named id from the base report will supply the value for the argument :customerid in the nested report. When you run the report now, you are not prompted for retrieval argument values for the nested report. The base report supplies the retrieval argument values automatically.
Working with nested reports Notice that the id column is preceded by a colon (:), which is required: When you run the report now, InfoMaker retrieves rows in the nested report based on the criteria you have specified. In the example, the customer ID column in the base report determines which rows from the sales_order table are included for each customer. Using options for nested reports Using the Autosize Height option Autosize Height should be on for all nested reports except graphs.
CHAPTER 11 Using the Slide options Using Nested Reports InfoMaker determines the appropriate Slide options when positioning the nested report(s) and assigns default values. Usually, you should not change the default values: • The Slide Left option is on by default for grid and crosstab style reports and off by default for all others. Having Slide Left on for grid and crosstab ensures that these reports break horizontally on whole columns and not in the middle of a column.
Working with nested reports 362 InfoMaker
CH A PTE R 1 2 About this chapter Contents Exporting and Importing XML Data The row data in a report can be exported and imported in the Extensible Markup Language (XML). This chapter describes how to create and use templates that control the export and import of data in XML format.
About XML An XML document is made up of declarations, elements, comments, character references, and processing instructions, indicated in the document by explicit markup. The simple XML document that follows contains an XML declaration followed by the start tag of the root element, , nested row and column elements, and finally the end tag of the root element. The root element is the starting point for the XML processor.
CHAPTER 12 Exporting and Importing XML Data XML Schema provides an alternative mechanism for describing and validating XML data. It provides a richer set of datatypes than a DTD, as well as support for namespaces, including the ability to use prefixes in instance documents and accept unknown elements and attributes from known or unknown namespaces. For more information, see the W3C XML Schema page at http://www.w3.org/XML/Schema.
About XML
Mouse | Representing empty elements Empty elements cannot be represented in XML in the same way they are in HTML. An empty element is one that is not used to mark up data, so in HTML, there is no end tag. There are two ways to handle empty elements: • Place a dummy tag immediately after the start tag. For example: ![]()
• Use a slash character at the end of the initial tag: ![]()
PAGE 389CHAPTER 12 Xerces parser Exporting and Importing XML Data InfoMaker includes software developed by the Apache Software Foundation (http://www.apache.org/). The XML services for reports are built on the Apache Xerces-C++ parser, which conforms to both DOM and SAX specifications. For more information about SAX, see the Xerces C++ Parser page at http://xerces.apache.org/xerces-c/index.html.
The Export/Import Template view for XML The Export/Import Template view for XML You define and edit templates for export and import in the Export/Import Template view for XML in the Report painter. The view uses a tree view to represent the template. Using the Web DataWindow in PowerBuilder If you are both an InfoMaker user and a PowerBuilder Enterprise user, you might be creating reports (nonupdatable DataWindows) in InfoMaker and then using PowerBuilder to work with your DataWindows.
CHAPTER 12 Exporting and Importing XML Data Creating, opening, and saving templates From the pop-up menu for the Export/Import Template view (with nothing selected), you can create new templates with or without default contents, open an existing template, save the current template, or delete the current template. You can only open and edit templates that are associated with the current report.
The Export/Import Template view for XML Creating templates To create a template, select the New menu item or the New Default menu item from the pop-up menu in the Export/Import Template view. Creating new base templates The New menu item creates a template that is empty except for the XML declaration, the root element, and the first element of the row data section, referred to as the Detail Start element.
CHAPTER 12 Exporting and Importing XML Data The SQL syntax is: SELECT "employee"."dept_id", "employee"."emp_lname", "employee"."emp_fname", "employee"."salary" FROM "employee" WHERE employee.dept_id = :deptnum ORDER BY "employee"."emp_lname" ASC In the default template, dept_id uses the InfoMaker expression icon. All the other columns used the column control reference icon.
The Export/Import Template view for XML The Detail section contains the row data, and is generated iteratively for each row in the report. The Detail Start element A line across the Export/Import Template view separates the Header section from the Detail section. The first element after this line, d_dept_list_row in the previous screenshot, is called the Detail Start element. There can be only one Detail Start element, and it must be inside the document’s root element.
CHAPTER 12 Exporting and Importing XML Data Header section The Header section can contain the items listed in Table 12-2. Only the root element is required: Table 12-2: Items permitted in the Header section of an XML document Item Details XML declaration This must be the first item in the tree view if it exists. See “XML declaration” on page 375.
Editing XML templates Item Details InfoMaker expressions Literal text See “InfoMaker expressions” on page 378. Literal text does not correspond to a control in the report. Comments Processing instructions See “Comments” on page 381. See “Processing instructions” on page 382. CDATA sections Attributes See “CDATA sections” on page 381. You can assign attributes to all element types. See “Attributes” on page 378.
CHAPTER 12 Exporting and Importing XML Data The examples in this section show the delimiters used in the XML document. When you edit the template in dialog boxes opened from the Export/Import Template view for XML, you do not need to type these delimiters in text boxes. The rest of this section describes some of the items in the template. For more information, see the XML specification at http://www.w3.org/TR/REC-xml. XML declaration The XML declaration specifies the version of XML being used.
Editing XML templates Document type declaration The document type declaration contains or points to markup declarations that provide a grammar for a class of documents. This grammar is known as a document type definition, or DTD. The document type declaration defines constraints on the sequence and nesting of tags, attribute values, names and formats of external references, and so forth.
CHAPTER 12 CustID (#PCDATA)> OrderID (#PCDATA)> Items (ItemID, Quantity)> ItemID (#PCDATA)> Quantity (#PCDATA)> ]> Root element You can change the name of the root element, add attributes and children, and insert comments, instructions, and, if they do not already exist, XML and/or document type declarations before it.
Editing XML templates Drag-and-drop cannot replace You cannot drag-and-drop an item on top of another item to replace it. For example, if you want to replace one control reference with another control reference, or with an InfoMaker expression, you first need to delete the control reference you want to replace. InfoMaker expressions Adding an InfoMaker expression opens the Modify Expression dialog box. This enables you to create references to columns from the data source of the report.
CHAPTER 12 Exporting and Importing XML Data For each attribute specified, you can select a control reference from the drop-down list or enter a literal text value. A literal text value takes precedence over a control reference. You can also use the expression button to the right of the Text box to enter an expression. The expression button and entry operates similarly to report properties in the Properties view.
Editing XML templates Composite reports For composite reports that use the Composite presentation style, the default template has elements that reference each of its nested reports. If a composite report contains two reports that have columns with identical names, you must use the procedure that follows if you want to generate an XML document with a DTD or schema. If you do not follow the procedure, you will receive a parsing error such as “Element ‘identical_column_name’ has already been declared.
CHAPTER 12 Exporting and Importing XML Data CDATA sections You can export the name of a column in a CDATA section using the syntax . You can export the value of a column using the syntax . The ~t is used to introduce an expression. You can also use an expression such as ~t columnname*columnname to export a computed value to the XML. You can import a value into a column using the syntax .
Exporting to XML Processing instructions Processing instructions (PIs) enable you to provide information to the application that uses the processed XML. Processing instructions are enclosed in delimiters and must have a name, called the target, followed by optional data that is processed by the application that uses the XML. Each application that uses the XML must process the targets that it recognizes and ignore any other targets.
CHAPTER 12 Exporting and Importing XML Data Setting data export properties The Data Export page in the Properties view lets you set properties for exporting data to XML. The Use Template property The names of all templates that you create and save for the current report display in the Use Template drop-down list. The template you select from the list is used to conform the XML to the specifications defined in the named template.
Exporting to XML ❖ To save to XML using the current template: 1 Right-click in the Export/Import template view and select Save or Save As from the pop-up menu to save the current template. 2 On the Data Export page in the properties view, select the current template from the Use Template drop-down list. 3 Select File>Save Rows As, select XML from the Files of Type drop-down list, enter a file name, and click Save.
CHAPTER 12 Exporting and Importing XML Data 2002-03-14 - 300 12 2005-09-15
- 301 12 2005-09-14
- 302 12 2005-09-14
2002-03-18 - 401
Exporting to XML For reports with more than one group, when you generate a new default template, each group after the first is identified with a special icon and a check on the pop-up menu next to the Starts Group Header item. When the Iterate Header for Groups check box is selected, each XML fragment in the header section between a Group Header element and the next Group Header element or Detail Start element is iterated.
CHAPTER 12 Exporting and Importing XML Data 2 301 12 3 302 12 <
Exporting to XML To specify how metadata should be saved, select a value from the Meta Data Type drop-down list. The possible values are: • XMLNone!—No metadata is generated • XMLSchema!—An XML schema is generated • XMLDTD!—A DTD is generated If the data item for a column is null or an empty string, an empty element is created. If you select XMLSchema!, child elements with null data items are created with the content "xsi:nil='true'".
CHAPTER 12 Exporting and Importing XML Data 1 Hildebrand Jane ma 1280 Washington St. Emeryville MI 94608 5105551309 5105554209 Example: external metadata If you select MetaDataExternal! instead, the generated XML in dtdexternal.xml looks like this:
Exporting to XML Associating a namespace with an exported schema If you export metadata in the form of a schema, you can associate a namespace with the schema. To do so, right-click the root element in the Export/Import template view and select Schema Options from the pop-up menu. In the dialog box, specify the namespace prefix and URI.
CHAPTER 12 Exporting and Importing XML Data 1 Hildebrand Jane Emeryville MI By default, the generated XML is not associated with a namespace.
Importing XML For complex, nested XML with row data in an iterative structure, you may need to design a structure that uses several linked report definitions to import the data. Each report must define the structure of a block of iterative data with respect to the root element. Importing the data into the reports would require multiple import passes using different import templates. Defining import templates The XML import template can be defined in the Export/Import Template view for XML.
CHAPTER 12 Importing data with group headers Exporting and Importing XML Data For XML import using a template, element and attribute contents in the header section are ignored. However, if the Starts Detail marker does not exist, all element and attribute to column mappings within the template are used for import.
Importing XML Example This example uses a report that includes the columns emp_id, emp_fname, emp_lname, and dept_id. The template used in this example includes only these columns. Any other columns in the report remain empty when you import using this template. To illustrate how template import works, create a new template that has one element in the header section, called before_detail_marker. This element contains a column reference to the emp_id column.
CHAPTER 12 Exporting and Importing XML Data 105 static text content Matthew Cobb ... The exported XML can be reimported into the report columns dept_id, emp_id, emp_fname, and emp_lname. Before importing, you must set the import template on the Data Import page in the Properties view.
Importing XML Empty elements Empty elements (elements that have no content between the start and end tags) are imported as empty values into the report column. If the element text contains only white space, carriage returns, and new line or tab characters, the element is treated as an empty element. Any attributes of empty elements are ignored.
CHAPTER 12 Exporting and Importing XML Data Julie Jordan 6175557835 11/12/1951 Example 2 In this example, the elements are not contained in rows, but they still match the report.
Importing XML Result All three XML documents produce this result: emp_id emp_fname emp_lname phone birth_date 105 148 Matthew Julie Cobb Jordan 6175553840 6175557835 04/12/1960 11/12/1951 Example with empty elements Example 4 This example uses the same report, but there are two empty elements in the XML document. The first has no content, and the second has an attribute but no content. Both are imported as empty elements.
CHAPTER 12 Exporting and Importing XML Data Tracing import When you import data from XML with or without a template, you can create a trace log to verify that the import process worked correctly. The trace log shows whether a template was used and if so which template, and it shows which elements and rows were imported. To create a trace log, select the Trace XML Import check box on in the Data Import page in the Properties view and specify the name and location of the log file in the Trace File Name box.
Importing XML Example: template import The following trace log shows a template import of the department table. The template used is named t_1. Notice that the report column dept_id is referenced twice, as both an attribute and a column. The second occurrence is ignored for the template import, as described in “Restrictions” on page 393. The Detail Start element has an implicit attribute named __pbband which is also ignored.
CH A PTE R 1 3 About this chapter Contents Working with Graphs This chapter describes how to build and use graphs in InfoMaker. Topic About graphs Page 401 Using graphs in reports Using the Graph presentation style 408 420 Defining a graph's properties 421 About graphs Often the best way to display information is graphically. You can add a graph to a report and work with it there, or use the Graph presentation style to create a standalone graph.
About graphs Parts of a graph Here is a column graph created in InfoMaker that contains most major parts of a graph. It shows quarterly sales of three products: Stellar, Cosmic, and Galactic printers: How data is represented Graphs display data points. To define graphs, you need to know how the data is represented. InfoMaker organizes data into three components. Table 13-1: Components of a graph Component Meaning Series Each set of related data points makes up one series.
CHAPTER 13 Working with Graphs Organization of a graph Table 13-2 lists the parts of a typical graph. Table 13-2: Organization of a graph Part of graph What it is Title An optional title for the graph. The title appears at the top of the graph. The axis of the graph along which the values of the dependent variable(s) are plotted. In a column graph, as shown in the preceding graph, the Value axis corresponds to the y axis in an XY presentation.
About graphs Types of graphs InfoMaker provides many types of graphs for you to choose from. You choose the type on the Define Graph Style page in the report wizard or in the General page in the Properties view for the graph. Area, bar, column, and line graphs Area, bar, column, and line graphs are conceptually very similar. They differ only in how they physically represent the data values—whether they use areas, bars, columns, or lines to represent the values. All other properties are the same.
CHAPTER 13 Working with Graphs Pie graphs Pie graphs typically show one series of data points with each data point shown as a percentage of a whole. The following pie graph shows the sales for Stellar printers for each quarter. You can easily see the relative values in each quarter. (InfoMaker automatically calculates the percentages of each slice of the pie.) You can have pie graphs with more than one series if you want; the series are shown in concentric circles.
About graphs Speed Mileage 70 80 24 20 Here is the data in a scatter graph: You can have multiple series of data in a scatter graph. You might want to plot mileage versus speed for several makes of cars in the same graph. Three-dimensional graphs Traditional 3D graphs 406 You can also create 3-dimensional (3D) graphs of area, bar, column, line, and pie graphs. In 3D graphs (except for 3D pie graphs), series are plotted along a third axis (the Series axis) instead of along the Category axis.
CHAPTER 13 DirectX 3D graphs Working with Graphs DirectX 3D rendering allows you to display the 3D graphs (Pie3D, Bar3D, Column3D, Line3D, and Area3D) with a more sophisticated look. You can use data item or series transparency with the DirectX graph styles to improve the presentation of data. The DirectX graph rendering style is supported for standalone graph controls and for graph controls in a DataWindow object.
Using graphs in reports Stacked graphs In bar and column graphs, you can choose to stack the bars and columns. In stacked graphs, each category is represented as one bar or column instead of as separate bars or columns for each series: Using graphs in reports Graphs in reports are dynamic Graphs in reports are tied directly to the data that is in the report. As the data changes, the graph is automatically updated to reflect the new values.
CHAPTER 13 Working with Graphs Placing a graph in a report ❖ To place a graph in a report: 1 Open or create the report that will contain the graph. 2 Select Insert>Control>Graph from the menu bar. 3 Click where you want the graph. InfoMaker displays the Graph Data dialog box: 4 Specify which columns contain the data and the type of graph you want, and click OK. For more information, see “Associating data with a graph” on page 412.
Using graphs in reports Using the graph's Properties view A graph has a Properties view in which you can specify the data as well as the other properties of the graph. ❖ To display the graph’s Properties view: • Select Properties from the graph’s pop-up menu. The Properties view for a graph has several property pages in which you specify information about the graph. Table 13-3 lists the property pages that contain properties that are specific to graphs, and describes what each property page specifies.
CHAPTER 13 Working with Graphs The initial graph is also moveable and resizable, so you have complete flexibility as to the size and location of a graph at runtime. You can change these properties. ❖ To specify a graph’s position and size: 1 Select Properties from the graph’s pop-up menu and then select the Position page or the General page in the Properties view.
Using graphs in reports Associating data with a graph When using a graph in a report, you associate axes of the graph with columns in the report. The only way to get data into a graph in a report is through columns in the report. You cannot add, modify, or delete data in the graph except by adding, modifying, or deleting data in the report. You can graph data from any columns retrieved into the report. The columns do not have to be displayed.
CHAPTER 13 Working with Graphs Specifying the categories Specify the column or expression whose values determine the categories. In the Graph Data page in the Graph dialog box and on the Data page in the Properties view, you can select a column name from a drop-down list. There is an entry along the Category axis for each different value of the column or expression you specify.
Using graphs in reports To graph 110 percent of the sum of units sold, you can specify: sum(units*1.1 for graph) Specifying the series Graphs can have one or more series. Single-series graphs If you want only one series (that is, if you want to graph all retrieved rows as one series of values), leave the Series box empty. Multiple-series graphs If you want to graph more than one series, select the Series check box and specify the column that will provide the series values.
CHAPTER 13 Working with Graphs Examples This section shows how to specify the data for several different graphs of the data in the Printer table in the EAS Demo DB. The table records quarterly unit sales of three printers by three sales representatives.
Using graphs in reports The Quarter column serves as the category. Because the Quarter column has four values (Q1, Q2, Q3, and Q4), there will be four categories along the Category axis. You want only one series (total sales in each quarter), so you can leave the Series box empty, or type a string literal to identify the series in a legend. Setting Value to sum(units for graph) graphs total sales in each quarter. Here is the resulting column graph.
CHAPTER 13 Graphing unit sales of each printer Working with Graphs To graph total quarterly sales of each printer, retrieve all the columns into a report and create a graph with the following settings on the Data page in the Properties view: • Set Rows to All • Set Category to quarter • Set Value to sum(units for graph) • Select the Series check box • Set Series to product You want a different series for each printer, so the column Product serves as the series.
Using graphs in reports Here is the resulting graph: Graphing unit sales by representative and total sales To graph quarterly sales made by each representative, plus total sales for each printer, create a graph with the following settings on the Data page in the Properties view: • Set Rows to All • Set Category to quarter, "Total" • Set Value to sum(units for graph), sum(units for graph) • Select the Series check box • Set Series to rep, rep Here you have two types of categories: the first is Q
CHAPTER 13 Working with Graphs Here is the resulting graph: Notice that InfoMaker uses the literal “Total” supplied in the Category box in the Graph Data window as a value in the Category axis.
Using the Graph presentation style Here is the resulting graph. InfoMaker uses the literals you typed for the series as the series labels in the legend: Using the Graph presentation style Instead of embedding a graph in a report, you can use the Graph presentation style to create a report that is only a graph—the underlying data is not displayed. ❖ To use the Graph presentation style: 1 Select File>New from the menu bar. The New dialog box displays. 2 Select the Object tab.
CHAPTER 13 7 Working with Graphs On the Ready to Create Graph DataWindow page, review your specifications and click Finish. A model of the graph displays in the Design view. 8 Specify the properties of the graph, as described in “Defining a graph's properties” next. 9 Save the report in a library. Defining a graph's properties This section describes properties of a graph. To define the properties of a graph, you use the graph’s Properties view.
Defining a graph's properties Defining a graph's title The title displays at the top of the graph. ❖ To specify a graph's title: • On the General properties page for the graph, enter a title in the Title box. Multiline titles You can force a new line in a title by embedding ~n. For information about specifying properties for the title text, see “Specifying text properties for titles, labels, axes, and legends” on page 423.
CHAPTER 13 Working with Graphs Sorting data for series and categories You can specify how to sort the data for series and categories. By default, the data is sorted in ascending order. ❖ To specify how to sort the data for series and categories in a graph: 1 Select Properties from the graph’s pop-up menu and then select the Axis page in the Properties view. 2 Select the axis for which you want to specify sorting.
Defining a graph's properties Using Auto Size 2 Select a text element from the list in the Text Object drop-down list. 3 Specify the font and its characteristics. With Auto Size in effect, InfoMaker resizes the text appropriately whenever the graph is resized. With Auto Size disabled, you specify the font size of a text element explicitly.
CHAPTER 13 Working with Graphs Using display formats ❖ Modifying display expressions To use a display format for a text element in a graph: 1 On the Text properties page for the graph, select a text element from the list in the Text Object drop-down list. 2 Type a display format in the Format box or choose one from the pop-up menu. To display the pop-up menu, click the button to the right of the Format box. You can specify an expression for the text that is used for each graph element.
Defining a graph's properties To accomplish this, the display expressions were modified for the title and pie graph labels: Element Title Pie graph labels Original expression Modified expression title title + " as of " + date(today()) if(seriescount > 1, series, string (percentofseries, "0.00%")) if(seriescount > 1, series, string(percentofseries,"0% ") + " (" + value + ")" ) Specifying overlap and spacing With bar and column charts, you can specify the properties in Table 13-8.
CHAPTER 13 Working with Graphs Specifying axis properties Graphs have two or three axes. You specify the axes’ properties in the Axis page in the graph’s Properties view. ❖ To specify properties for an axis of a graph: 1 Select Properties from the graph’s pop-up menu and then select the Axis page in the Properties view. 2 Select the Category, the Value, or the Series axis from the Axis drop-down list. If you are not working with a 3D graph, the Series Axis options are disabled.
Defining a graph's properties Table 13-11: Properties for scaling on numeric axes Property Autoscale RoundTo, RoundToUnit Meaning If selected (the default), InfoMaker automatically assigns a scaling for the numbers along the axis. Specifies how to round the end points of the axis (note that this just rounds the range displayed along the axis; it does not round the data itself). You can specify a number and a unit.
CHAPTER 13 Representing divisions with grid and drop lines Working with Graphs You can specify lines to represent the divisions as described in Table 13-12 and illustrated in Figure 13-1. Table 13-12: Representing graph divisions with grid and drop lines Line Grid line Drop line Meaning A line that extends from a tick mark across the graph. Grid lines make graphs easier to read. A line that extends vertically from a data point to its axis (not available for all graph types).
Defining a graph's properties 430 InfoMaker
CH A PTE R 1 4 About this chapter Contents Working with Crosstabs This chapter describes how to build crosstabs. Topic About crosstabs Page 431 Creating crosstabs Associating data with a crosstab 434 436 Previewing crosstabs Enhancing crosstabs 442 442 About crosstabs Cross tabulation is a useful technique for analyzing data. By presenting data in a spreadsheet-like grid, a crosstab lets you view summary data instead of a long series of rows and columns.
About crosstabs Table 14-1: The Printer table in the EAS Demo DB Rep Simpson Quarter Q1 Product Stellar Units 12 Jones Perez Q1 Q1 Stellar Stellar 18 15 Simpson Jones Q1 Q1 Cosmic Cosmic 33 5 Perez Simpson Q1 Q1 Cosmic Galactic 26 6 Jones Perez Q1 Q1 Galactic Galactic 2 1 . . . . . . . . . Simpson . Q4 . Stellar .
CHAPTER 14 Working with Crosstabs The first-quarter sales of Cosmic printers displays in the first data cell. (As you can see from the data in the Printer table shown before the crosstab, in Q1 Simpson sold 33 units, Jones sold 5 units, and Perez sold 26 units—totaling 64 units.) InfoMaker calculates each of the other data cells the same way.
Creating crosstabs Dynamic crosstabs With dynamic crosstabs, InfoMaker builds all the columns and rows in the crosstab dynamically when you run the crosstab. The number of columns and rows in the crosstab match the data that exists at runtime. Using the preceding crosstab as an example, if a new printer was added to the database after the crosstab was saved, there would be an additional row in the crosstab when it is run.
CHAPTER 14 Working with Crosstabs The New dialog box displays. 2 Select the Object tab. 3 Select the Crosstab presentation style, then click OK. 4 On the Choose Data Source for Crosstab DataWindow page, specify the data you want retrieved into the report. For more information, see Chapter 5, “Defining Reports.” 5 In the Define Crosstab Rows, Columns, Values page, enter the definitions for the columns, rows, and cell values in the crosstab. See “Associating data with a crosstab” on page 436.
Associating data with a crosstab Associating data with a crosstab You associate crosstab columns, rows, and cell values with columns in a database table or other data source. ❖ To associate data with a crosstab: 1 If you are defining a new crosstab, the Define Crosstab Rows, Columns, Values dialog box displays after you specify the data source. 2 Specify the database columns that will populate the columns, rows, and values in the crosstab, as described below.
CHAPTER 14 Working with Crosstabs Dynamic crosstab example The process is illustrated using the following dynamic crosstab. The columns in the database are Rep, Quarter, Product, and Units. The crosstab shows the number of printers sold by Quarter: Specifying the columns You use the Columns box to specify one or more of the retrieved columns to provide the columns in the crosstab.
Associating data with a crosstab Columns that use code tables If you specify columns in the database that use code tables, where data is stored with a data value but displayed with more meaningful display values, the crosstab uses the column’s display values, not the data values. For more information about code tables, see Chapter 8, “Displaying and Validating Data.” Specifying the values Each cell in a crosstab holds a value. You specify that value in the Values box.
CHAPTER 14 ❖ Working with Crosstabs To specify an expression for columns, rows, or values: 1 In the Crosstab Definition dialog box (or wizard page), double-click the item in the Columns, Rows, or Values box. The Modify Expression dialog box displays. 2 Specify the expression and click OK. Viewing the crosstab After you have specified the data for the crosstab’s columns, rows, and values, InfoMaker displays the crosstab definition in the Design view.
Associating data with a crosstab In the Design view, the crosstab looks like this: Notice that in the Design view, InfoMaker shows the quarter entries using the symbolic notation @quarter (with dynamic crosstabs, the actual data values are not known at definition time). @quarter is resolved into the actual data values (in this case, Q1, Q2, Q3, and Q4) when the crosstab runs. The crosstab is generated with summary statistics: the rows and columns are totaled for you.
CHAPTER 14 Working with Crosstabs Specifying more than one row or column Typically you specify one database column as the Columns definition and one database column for the Rows definition, as in the printer crosstab. But you can specify as many columns (or expressions) as you want. For example, consider a crosstab that has the same specification as the crosstab in “Viewing the crosstab” on page 439, except that two database columns, quarter and rep, have been dragged to the Columns box.
Previewing crosstabs Previewing crosstabs When you have defined the crosstab, you can see it with data in the Preview view. ❖ To preview the crosstab: 1 If the Preview view is not open, select View>Preview from the menu bar to display the Preview view. 2 Click on the Preview view to be sure it is current. 3 Select Rows>Retrieve from the menu bar. InfoMaker retrieves the rows and performs the cross tabulation on the data.
CHAPTER 14 Working with Crosstabs The rest of this section covers topics either unique to crosstabs or especially important when working with crosstabs: • “Specifying basic properties” next • “Modifying the data associated with the crosstab” on page 444 • “Changing the names used for the columns and rows” on page 444 • “Defining summary statistics” on page 445 • “Cross-tabulating ranges of values” on page 448 • “Creating static crosstabs” on page 451 • “Using property conditional expressions”
Enhancing crosstabs Modifying the data associated with the crosstab When you initially define the crosstab, you associate the crosstab rows and columns with columns in a database table or other data source. You can change the associated data at any time in the Crosstab Definition dialog box. ❖ To open the Crosstab Definition dialog box: 1 Position the mouse below the footer band in the workspace and display the pop-up menu. 2 Select Crosstab from the pop-up menu.
CHAPTER 14 Working with Crosstabs When the crosstab runs, you see this: Defining summary statistics When you generate a crosstab, the columns and rows are automatically totaled for you. You can include other statistical summaries in crosstabs as well. To do that, you place computed fields in the workspace. ❖ To define a column summary: 1 Enlarge the summary band to make room for the summaries. 2 Select Insert>Control>Computed Field from the menu bar.
Enhancing crosstabs For example, this is a crosstab that has been enhanced to show averages and maximum values for each column. This is the Design view: This is the crosstab at runtime: ❖ To define a row summary: 1 Select Insert>Control>Computed Field from the menu bar. 2 Click the empty cell to the right of the last column in the detail band. The Modify Expression dialog box displays. 3 446 Define the computed field. You should use one of the crosstab functions, described next.
CHAPTER 14 Working with Crosstabs Using crosstab functions There are nine special functions you can use only in crosstabs: CrosstabAvg, CrosstabAvgDec, CrosstabCount, CrosstabMax, CrosstabMaxDec, CrosstabMin, CrosstabMinDec, CrosstabSum, and CrosstabSumDec. These functions are listed in the Functions box when you define a computed field in a crosstab: Each of these functions returns the corresponding statistic about a row in the crosstab (average, count, maximum value, minimum value, or sum).
Enhancing crosstabs Here CrosstabSum(1) returns the total of sum(units for crosstab) for the corresponding row. CrosstabSum(2) returns the total for sum(units * 1.2 for crosstab). For more information For complete information about defining computed fields, see Chapter 6, “Enhancing Reports.” For more about the crosstab functions, see Chapter 24, “DataWindow Expression and InfoMaker Functions.
CHAPTER 14 Working with Crosstabs The first step is to determine the expression that, given a salary, returns the next smaller salary that is a multiple of $10,000. For example, given a salary of $34,000, the expression would return $30,000, and given a salary of $47,000, the expression would return $40,000.
Enhancing crosstabs This is the result in the Design view: This is the crosstab at runtime: You can see, for example, that 2 people in department 400 and 5 in department 500 earn between $20,000 and $30,000. Displaying blank values as zero 450 In the preceding crosstab, several of the cells in the grid are blank. There are no employees in some salary ranges, so the value of those cells is null.
CHAPTER 14 ❖ Working with Crosstabs To display blank values in a crosstab as zero: 1 Select the column you want to modify and click the Format tab in the Properties view. 2 Replace [General] in the Format box with ###0;###0;0;0. The fourth section in the mask causes a null value to be represented as zero. Creating static crosstabs By default, crosstabs are dynamic: when you run them, InfoMaker retrieves the data and dynamically builds the columns and rows based on the retrieved data.
Enhancing crosstabs For example, in the following screenshot, the four values for Quarter (Q1, Q2, Q3, and Q4) are displayed in the Design view: At runtime, no matter what values are in the database for the column, the crosstab shows only the values that were specified when the crosstab was defined. In the printer example, the crosstab always has the four columns it had when it was first defined. Making changes You can modify the properties of any of the columns in a static crosstab.
CHAPTER 14 Example Working with Crosstabs In the following crosstab, an expression has been specified for Units: The expression is for the Font.Weight property of the units column: if (units > 100, 700, 400) The expression specifies to use bold font (weight = 700) if the number of units is greater than 100. Otherwise, use normal font (weight = 400). This is the crosstab at runtime: Values larger than 100 are shown in bold.
Enhancing crosstabs 454 InfoMaker
CH A PTE R About this chapter Contents 1 5 Working with TreeViews This chapter describes how to build and use reports in InfoMaker using the TreeView presentation style.
Creating a new TreeView report Example This sample TreeView report uses the department and employee tables in the EAS Demo DB database and has two TreeView levels. The first level is the department name. The second level is the city where each employee resides. The detail data for each employee is grouped in TreeView leaf nodes under these two levels. Similarities to the Group presentation style Creating and using a TreeView report is similar to creating and using a Group report.
CHAPTER 15 Working with TreeViews TreeView creation process A TreeView report has multiple levels, each of which is a node in the TreeView. You use the TreeView wizard to create a TreeView report, but the wizard produces a DataWindow that includes only the top level of the TreeView. Creating a complete TreeView report involves three steps: 1 Using the TreeView report wizard to create the top level (level 1) of the TreeView report.
Creating a new TreeView report 4 Define the tables and columns you want to use. You are prompted to specify the TreeView grouping columns. Multiple columns and multiple TreeView levels You can specify more than one column, but all columns apply to TreeView level one. At this point, you can define only one TreeView level. You define additional levels later.
CHAPTER 15 Working with TreeViews 7 Click Next. 8 Modify the default color and border settings if needed, and then click Next. 9 Review the TreeView report characteristics. 10 Click Finish. The report painter Design view displays. For information about the Design view, see “TreeView report Design view” on page 464. For information about adding additional levels, see “Adding and deleting TreeView levels” on page 461.
Creating a new TreeView report If you selected the Grid Style check box, vertical and horizontal grid lines display: Here is the sample TreeView report in the Preview view: Using an expression for a column name If you want to use an expression for one or more column names in a TreeView, you can enter it as the TreeView definition on the General page in the Properties view after you finish using the TreeView wizard.
CHAPTER 15 What you can do Working with TreeViews All of the techniques available in a tabular report, such as moving controls and specifying display formats, are available for modifying and enhancing TreeView reports. See “Adding and deleting TreeView levels” next to read more about the bands in a TreeView report and see how to add features especially suited for TreeView reports, such as additional TreeView levels or summary statistics.
Selecting a tree node and navigating the tree 4 Click OK. The new TreeView level and a Trailer band for that level are created in the TreeView Design view. For information on how to set properties for a TreeView level, see “Setting TreeView level properties” on page 467. ❖ To delete a level in a TreeView report: 1 Select Rows>Delete TreeView Level from the menu bar. 2 Select the number of the level to delete from the list of levels that displays.
CHAPTER 15 Working with TreeViews Sorting rows in a TreeView report ❖ To sort the rows within levels in a TreeView report: 1 Select Rows>Sort from the menu bar. 2 Drag the columns that you want to sort the rows on from the Source Data box to the Columns box. The order of the columns determines the precedence of the sort. The sort order is ascending by default. To sort in descending order, clear the Ascending check box.
TreeView report Design view TreeView report Design view The Design view for the TreeView report differs from the traditional Design view for most report presentation styles. The Design view has a header band, a TreeView level band for each added level, a detail band, a Trailer band for each level, a summary band, and a footer band.
CHAPTER 15 • Working with TreeViews When there is no tree node icon specified, a shaded square icon in the detail band and in each TreeView level band represents where the connecting line ends. The position of all the icons changes when you change the indent value. For more information about specifying icons and the indent value, see “Setting properties for the TreeView report.
Setting properties for the TreeView report Setting general TreeView properties You set most TreeView report properties on the General page in the Properties view for the report. The properties that are specific to a TreeView report are the TreeView properties and the Grid properties. The grid-related properties display only if you select the Grid Style check box when you define the TreeView report. Property Display Description On – Grid lines always display.
CHAPTER 15 Working with TreeViews Property Description Indent Value The indent value of the child node from its parent in the units specified for the report. The indent value defines the position of the state icon. The X position of the state icon is the X position of its parent plus the indent value. Expand To Level By Default State Icon Align Mode Expand to TreeView level 1, 2, or 3. Align the state icon in the middle (0), at the top (1), or at the bottom (2).
Setting properties for the TreeView report 2 Use the report TreeView Level properties view that displays to edit the properties for the level you selected. The properties that are specific to a TreeView level band are at the bottom of the Properties view: Property Tree Node Icon File Collapsed Tree Node Icon File Description The file name of the tree node icon in a TreeView level band when it is in the expanded state.
CHAPTER 15 Working with TreeViews Setting detail band properties You can specify an icon for the rows in the detail band by clicking the detail band in the Report painter to display the Properties view. If you want to hide tree nodes in the detail band, set the Height property to 0. The only property that is specific to the TreeView DataWindow is located at the bottom of the Properties view: Users Guide Property Description Tree Node Icon File The file name of the tree node icon in the detail band.
Setting properties for the TreeView report 470 InfoMaker
CH A PTE R 1 6 About this chapter Working with Rich Text This chapter explains how to create reports using the RichText presentation style. Contents Topic About rich text Page 471 Using the RichText presentation style Formatting keys and toolbars 472 482 About rich text Rich text format (RTF) is a standard for specifying formatting instructions and document content in a single ASCII document.
Using the RichText presentation style You can use toolbars, editing keys, and a pop-up menu to specify formatting. A print preview lets you view a reduced image of the document to see how it fits on the page. What is not supported InfoMaker supports version 1.6 of the RTF standard, except for the following features: Formatted tables Drawing objects Using the RichText presentation style The RichText presentation style allows you to combine input fields that represent database columns with formatted text.
CHAPTER 16 Working with Rich Text If an input field is not a computed field and its name does not match a column, there is no way to specify data for the input field. There can be more than one copy of an input field in the rich text. In the sample above, there are two instances of the field FNAME. Each instance of the field displays the same data. Unavailable settings Not all the settings available in other report styles are available.
Using the RichText presentation style Available settings Table 16-1 describes the types of settings you can make for the RichText report in the wizard.
CHAPTER 16 Working with Rich Text This sample shows how you might rearrange the input fields in a sales letter: Editing text You can add text by typing directly in the Design view. You do not have to create text objects as you do for other report styles. The Report painter’s StyleBar lets you apply formatting to selected text. The RichText toolbars are not available in the painter.
Using the RichText presentation style Headers and footers You decide whether your RichText report has a header and footer by checking Header/Footer in the wizard or Rich Text Object dialog box (described in "Formatting for RichText objects within the report" next). The decision to include a header and footer must be made at design time; it cannot be changed at runtime. To display a page number or a date in the header or footer, you can insert the predefined computed fields Page n of n or Today().
CHAPTER 16 Working with Rich Text The whole RichText report Settings for the whole RichText report include the values you specified in the wizard, as well as: • Whether pictures are displayed or represented by empty frames • Whether newly entered text will wrap within the display • Whether various nonprinting characters, such as tabs, returns, and spaces, are visible • Standard report settings such as units of measurement and the pointer • Print specifications Use the following procedure to chan
Using the RichText presentation style Paragraphs There are also settings for selected paragraphs. You can display the Paragraph dialog box by pressing Ctrl+Shift+S. The user can double-click the ruler bar or press the key combination to display the same dialog box. Default font You can change the default font by double-clicking on the toolbar or pressing Ctrl+Shift+D. You cannot change the default font in the painter. Input fields An input field can be either a column or a computed field.
CHAPTER 16 Working with Rich Text Computed field input fields When you display the dialog box for a computed field, the settings are a little different. You can specify the input field name and its expression on the Compute page. Computed fields Computed fields have an expression that specifies the value of the computed field. In rich text, they are represented as input fields, too. You specify a name and an expression. The data value comes from evaluating the expression and cannot be edited.
Using the RichText presentation style If there are multiple copies of a computed field input field, the expression and format settings apply to all the copies. Font settings apply to individual instances. For more about computed field expressions and display formats, see Chapter 6, “Enhancing Reports.” Pictures Inserting a picture You can include bitmaps (BMP, GIF, JPG, RLE, or WMF files) in a RichText DataWindow.
CHAPTER 16 Working with Rich Text Previewing and printing To see what the RichText report looks like with data, you can preview it in the Preview view or in preview mode. ❖ To preview the report in preview mode: 1 Select File>Run/Preview from the menu bar, or click the Run/Preview button on the PowerBar. 2 Select Rows>Retrieve from the menu bar.
Formatting keys and toolbars Setting margins To specify permanent margin settings for the RichText report, use the Print Specifications page of the Rich Text Object dialog box. Formatting keys and toolbars When the toolbar is visible, you can use its buttons to format text. The changes you make in preview are temporary. The keystrokes listed in the following tables also assign formatting to selected text.
CHAPTER 16 Category Action Key Aligning text Justify Center Ctrl+J Ctrl+E Left Right Ctrl+L Ctrl+R Set paragraph formatting Insert a new paragraph Ctrl+Shift+S Enter Insert an empty line Delete character to right of insertion point Ctrl+N Delete Delete character to left of insertion point Select the input field at the insertion point Backspace Enter Activate the input field at the insertion point When input field is active, accept data and exit field When input field is active, exit field wit
Formatting keys and toolbars 484 InfoMaker
CH A PTE R About this chapter 1 7 Using OLE in a Report This chapter describes how to use OLE in reports. Contents Topic About using OLE in reports Page 485 OLE objects and the OLE presentation style Using OLE columns in a report 487 498 About using OLE in reports A report can include a control that is a container for an OLE object. The container stores information about the application that created the object and it can launch the application to display or modify the OLE object.
About using OLE in reports The OLE presentation style is similar to an OLE object in a report. The difference is that the OLE container is the only control in the report. The underlying data is not presented in column controls and there are no other controls, such as bitmaps or text. The OLE object is always associated with all the rows in the report.
CHAPTER 17 Using OLE in a Report OLE objects and the OLE presentation style Whether you insert an OLE object into a report or create a new report using the OLE presentation style, you are working with an OLE container object within the report. Similarities Differences They have these characteristics in common: The report can display the OLE object as an icon, or it can display an image of the contents when display of contents is supported by the server.
OLE objects and the OLE presentation style In this section This section includes procedures for: • Adding an OLE object to a report • Using the OLE presentation style • Defining the OLE object • Previewing the report • Specifying report data for the OLE object Adding an OLE object to a report To add an OLE object to a report, you begin by specifying where you want the OLE object and opening the Insert Object dialog box so you can define the OLE object.
CHAPTER 17 Using OLE in a Report Using the OLE presentation style Use the OLE presentation style to create a report that consists of a single OLE object. The following procedure creates the new report and opens the Insert Object dialog box. ❖ To create a new report using the OLE presentation style: 1 In the New dialog box, select OLE 2.0 from the Object tab and click OK. 2 Select data for the report as you do for any report. For more information about selecting data, see Chapter 5, “Defining Reports.
OLE objects and the OLE presentation style Defining the OLE object You define the OLE object in the Insert Object dialog box. It has three tab pages: If you want to Embed an OLE server object in the report Select this tab page Create New Link or embed the contents of an existing file as an OLE object so that it can be activated using the application that created it Create From File Insert an ActiveX control in the report Insert Control This section contains procedures for each of these selections.
CHAPTER 17 Create From File Using OLE in a Report Use the following procedure if you want to link or embed the contents of an existing file as an OLE object so that it can be activated using the application that created it. Most of the steps in this procedure are the same as those for embedding a new OLE server object. A server application must be available You (and the user) must have an application that can act as a server for the type of object you link or embed.
OLE objects and the OLE presentation style If you click Register New, you are prompted for the file that contains the registration information for the ActiveX control. 3 Click OK. 4 If you did not specify how the OLE object will use the report’s data when you created the report, do so on the Data property page. If you have inserted an ActiveX control that does not display data, such as the Clock control, you do not need to transfer data to it.
CHAPTER 17 Using OLE in a Report The way the OLE object uses the data depends on the server. For example, data transferred to Microsoft Excel is displayed as a spreadsheet. Data transferred to Microsoft Graph populates its datasheet, which becomes the data being graphed. Some ActiveX controls do not display data, so you would not transfer any data to them.
OLE objects and the OLE presentation style 3 If necessary, change the order of columns by dragging them up or down within the Target Data box. The order of the columns and expressions is important to the OLE server. You need to know how the server will use the data to choose the order. 4 Double-click an item in the Target Data box to specify an expression instead of a column.
CHAPTER 17 Using OLE in a Report Table 17-1: Associating an OLE object with rows in the report Range of rows All When to use it When the OLE object is in the summary, header, or footer band, or the foreground or background layer. Rows must be All and Layer must be Foreground or Background if you want the user to be able to activate the object. Current Row Target data for all rows is transferred to the object. When the OLE object is in the detail band.
OLE objects and the OLE presentation style Property Effect Contents Whether the object in the OLE container is linked or embedded. The default is Any, which allows either method. Display Type What the OLE container displays. You can choose: • Manual Display a representation of the object, reduced to fit within the container. • Icon Display the icon associated with the data. This is usually an icon provided by the server application.
CHAPTER 17 In preview Using OLE in a Report InfoMaker displays the initial presentation of the OLE object while it is retrieving rows and then replaces it with the retrieved data. You cannot activate the OLE object when you preview the report. If you add the report to a form, you can activate the OLE object when you run the form. For more information, see “Activating OLE objects” on page 486. Saving as a PSR You can save the object with its data by saving the report as a Powersoft report (PSR).
Using OLE columns in a report Using OLE columns in a report You can create OLE columns in a report. An OLE column allows you to retrieve blob (binary large-object) data from a database into a report. Database support for OLE columns If your database supports a blob datatype, then you can implement OLE columns in a report. The name of the datatype that supports blob data varies. For information on which datatypes your DBMS supports, see your DBMS documentation.
CHAPTER 17 2 Using OLE in a Report Specify the table containing the blob as the data source for the report. Be sure to include the key column in the data source. You cannot include the blob column in the data source; if you try, a message tells you that its datatype requires the use of an embedded SQL statement. You add the blob column later in the Report painter workspace. (If you use Quick Select, the blob column is not listed in the dialog box.
Using OLE columns in a report 3 In the Table box, select the database table that contains the blob database column you want to place in the report. The names of the columns in the selected table display in the Large Binary/Text Columns list. 4 In the Large Binary/Text Columns box, select the column that contains the blob datatype from the list. 5 If necessary, change the default key clause in the Key Clause box.
CHAPTER 17 7 Using OLE in a Report Enter text or an expression that evaluates to a string in the Client Name Expression box. The server might use this expression in the title of the window in the OLE server application. The expression you specify can identify the current row in the report. Use an expression to make sure the name is unique To make sure the name is unique, you should use an expression.
Using OLE columns in a report 502 InfoMaker
PART 4 Forms This part describes how to use forms to display and change information in your database Access to the Form painter To have access to the Form painter, you must use the typical or custom install. The InfoMaker form component is not included in the compact install.
CH A PTE R 1 8 About this chapter Defining Forms You use forms to add data to your database easily and efficiently. This chapter introduces InfoMaker forms and provides basic information about working with forms. Contents Topic About forms Page 505 Creating and saving forms Working with forms 514 523 About forms An InfoMaker form is an electronic document you use to enter data in a database. The form displays existing data from your database. You can change the existing data and add new data.
About forms The following illustration shows the Employee table in the EAS Demo DB, which has 20 columns of data for 75 employees. When you view it in the Database painter, you can see only a few rows and columns of data at a time: Changing data or entering data for a new employee directly in the Employee table is difficult. To see other columns, you need to scroll (or InfoMaker scrolls for you as you tab from column to column), and you can never see all the data for an employee at one time.
CHAPTER 18 Defining Forms Forms in InfoMaker and in an InfoMaker application After you design a form, you can use it within InfoMaker. You can also take the form, package it in an InfoMaker application with other forms and reports you have created, and distribute your application. For example, you could create an Employee Data application that includes the Employee Data form and many employee reports that are important to your organization.
About forms Data sources The data source you use determines how InfoMaker retrieves data for your form. You can select one of three data sources when you create a form: Data source Pick this data source when Quick Select SQL Select The data is from tables that are connected through a key, and you need only to sort and limit the data. The data is from tables that are not connected through a key, or you want more control over the SQL SELECT statement generated for the data source.
CHAPTER 18 Defining Forms For example, you can place a button in a form and then attach the Print action to it. After you run the form, you can print the current data by clicking the button: Actions are also available through the toolbar and menu items when you run a form. Freeform forms You use freeform forms for basic data maintenance. In freeform forms, you see one row of data at a time. You can arrange the information any way you want.
About forms After a few enhancements have been made to the basic form, here is the freeform form with data: Grid forms You use grid forms for basic data maintenance where you want to be able to view and update more than one row of data at a time. The data in a grid form displays in a rigid grid. When running a grid form, you can resize and reorder columns. For example, the following form is a grid form that allows you to view information for many customers at a time.
CHAPTER 18 Defining Forms Working in a grid form When you design and run a grid form, you can resize and reorder columns. ❖ To resize a column: 1 Position the pointer at a column boundary. The pointer changes shape to a 2-headed arrow. ❖ 2 Drag the mouse to move the boundary. 3 Release the mouse button when the column is the correct width. To reorder columns: 1 Select a column heading.
About forms You may want to display this type of relationship in a form. Such a form is called a master/detail one-to-many form. For example, the following form displays information about one department at the top and all of the employees of that department at the bottom: About the ID column Note the ID of the department in the master table at the top of the form. Although you cannot see the ID of the department in the detail table at the bottom of the form, it is there.
CHAPTER 18 Defining Forms Note that there is a primary/foreign key relationship between the tables: the Dept_id column in the Employee table has the same values as the Dept_id column in the Department table: Master/Detail Many-To-One forms You might have a lot of information about a particular class of entities, such as customers, employees, or parts. You might want to be able to scroll easily through a list of the entities (the many), then see the details for one of them.
Creating and saving forms For example, the following form lists all customers at the top (the master area) and the details for the selected customer at the bottom (the detail area): Both the master and detail areas use the Customer table in the EAS Demo DB. The Company_name and Id columns were chosen for the master table, and all columns were chosen for the detail table.
CHAPTER 18 ❖ Defining Forms To create a basic form: 1 Click the New button in the PowerBar. 2 Select the Object tab, the Form icon, and click OK. The New Form dialog box displays the data sources and form styles you can choose. 3 Choose the data source for the form: Data source Pick this data source when Quick Select The data is from tables that are connected through a key, and you need only to sort and limit data.
Creating and saving forms InfoMaker generates the basic form and displays it in the Form painter Layout view. The following illustration shows the basic form for the freeform form using all columns in the Customer table: 6 Save the form. For information, see “Saving the form” on page 522. 7 Run the form. For information, see “Running forms” on page 523. At this point, you can enhance the form. For more information, see Chapter 20, “Enhancing Forms”.
CHAPTER 18 Defining Forms You must use Quick Select You must use Quick Select and you can select only one master table and one detail table when creating the form. After the master/detail form is created, if you want to add data from another table, you can modify the data source and add new columns. For information about modifying the data source, see Chapter 20, “Enhancing Forms”. The Select Master Table dialog box displays. 4 Select the master table.
Creating and saving forms If you are told the data is not updatable After selecting the columns, you might see a message box telling you that the data is not updatable. For information about these situations, see “Defining data so that a form can update a database” on page 520. The Select Detail Table dialog box displays. 6 Select the detail table. This is the table that is related to the master table and whose data displays at the bottom of the form.
CHAPTER 18 Defining Forms When the master table and the detail table are the same table, the complete Select Master/Detail Relationship dialog box displays automatically and you specify the relationship: InfoMaker generates the basic form and displays it in the Form painter workspace. The following illustration shows a master/detail one-to-many form: 9 Users Guide Size the master area if necessary and click the Run button to run the form.
Creating and saving forms When you run the form, the form displays with data: At this point, you can enhance the form. To do so, you first click the Close button to return to the Form painter Layout view. For information about how to enhance the form, see Chapter 20, “Enhancing Forms”. Defining data so that a form can update a database If you want to be able to use a form to update data in a database, you must include all columns that make up a table’s unique key when you define the data for the form.
CHAPTER 18 Defining Forms About the master/detail form styles The master/detail one-to-many and master/detail many-to-one form styles each have two sources of data, one for the master area and one for the detail area. The data for both the master area and the detail area can be updatable. If you want to be able to insert new rows in a form, you must include all columns that have been defined in the database as requiring values.
Creating and saving forms These 12 columns were selected because in the database these columns were defined as requiring values. By selecting these columns, you can use the form to insert new data in the Employee table. For information about updating data in a form, see “Defining data so that a form can update a database” on page 520. When generating the basic form, InfoMaker uses the information from the extended attribute system tables.
CHAPTER 18 ❖ Defining Forms To save the form: 1 Do one of the following: • Click the Save button. • Select File>Save from the menu bar. If you have previously saved the form, InfoMaker saves the new version. If you have not previously saved the form, InfoMaker displays the Save Form dialog box. 2 Name the form in the Forms box. The form name can be any valid identifier up to 40 characters. For information about InfoMaker identifiers, see Appendix A, “Identifiers.
Working with forms What happens You are now running the form. Command buttons and picture buttons you have placed in the form are now active. For information about adding controls to a form, see Chapter 20, “Enhancing Forms.” Exactly what you can do when you run the form depends on the form style. It is the form style that determines the menu items that display in the menu bar and the buttons that display in the PainterBar when you run a form.
CHAPTER 18 Defining Forms InfoMaker clears all the data. 2 Specify the criteria. In master/detail forms, you specify the criteria in the form’s master area, which is a freeform area for master/detail one-to-many forms and a grid area for master/detail many-to-one forms. Both are like the grid you use when defining data using the Quick Select data source. Use expressions and operators in the blank spaces of the grid to specify criteria.
Working with forms 2 Navigate to the folder you want and select the file from which you want to import the data. The types of files that you can import into the form are shown in the Files of Type drop-down list. 3 Click Open. InfoMaker reads the data from the file. You can view the data and save it in an external file.
CHAPTER 18 4 Defining Forms Click Save. InfoMaker saves all rows in the file; all columns in the rows are saved. Printing forms Although forms are primarily used for data entry, after you run a form, you can print it. Printing a freeform form is particularly helpful because each page displays the data one row at a time. ❖ To print a form: • Do one of the following: • Select File>Print from the menu bar.
Working with forms Table 18-1: Cross reference of actions to buttons in a form Action Menu item What the action does Apply_Criteria Rows>Apply Criteria Cancel_Updates Rows>Cancel Changes Clear_Filter None Validates the selection criteria, then reretrieves the rows based on the criteria Discards changes made since the last update Clears the current filter Clear_Detail_Filter None Clears the detail filter Clear_Master_Filter None Clears the master filter Form styles available in All All Freef
CHAPTER 18 Form styles available in Freeform, Master/Detail One-To-Many All Action Next_Row Menu item Rows>Next Print File>Print Prints the retrieved data Print_Dialog Print_Setup None File>Print Setup Displays the Windows Print dialog Opens the Windows Printer Setup dialog box allowing you to change the printer or its settings Freeform, Grid All Prior_Row Rows>Get Prior Scrolls to the previous row in the master area Freeform, Master/Detail One-To-Many Retrieve Rows>Retrieve Retrieves rows
Working with forms Accessing and deleting forms ❖ To access a form: 1 Click the Open button in the PowerBar. The Open dialog box displays: 2 If necessary, select Forms in the Object Type box. 3 Highlight the form you want in the list and click OK. 4 The form displays in Layout view in the Form painter. You can work on the design of the form there. For more information, see Chapter 20, “Enhancing Forms”. 5 Click the Run button to run the form.
CH A PTE R About this chapter 1 9 Controlling Updates in Forms When InfoMaker generates the basic form, it defines whether the data is updatable. This chapter describes the default settings and how you can modify them.
About controlling updates What you can do You can: • Allow updates in a form associated with multiple tables or a view; you can define one of the tables as being updatable • Prevent updates in a form associated with one table • Prevent updates to specific columns in a form that is associated with an updatable table • Specify which columns uniquely identify a row to be updated • Specify which columns will be included in the WHERE clause of the UPDATE or DELETE statement InfoMaker generates to updat
CHAPTER 19 Controlling Updates in Forms Specifying the table to update Each form can update one table, which you select from the Table to Update box in the Specify Update Properties dialog box. Specifying the unique key columns The Unique Key Columns box in the Specify Update Properties dialog box specifies which columns InfoMaker uses to identify a row being updated.
Specifying an identity column Specifying an identity column Many DBMSs allow you to specify that the value for a column in a new row is to be automatically assigned by the DBMS. This kind of column is called an identity column. Different DBMSs provide different types of identity columns. For example, some DBMSs allow you to define autoincrement columns so that the column for a new row is automatically assigned a value one greater than that of the previous highest value.
CHAPTER 19 Controlling Updates in Forms Specifying the WHERE clause for update/delete Sometimes multiple users are accessing the same tables at the same time. In these situations, you need to decide when to allow your form to update the database.
Specifying the WHERE clause for update/delete Table 19-1: Specifying the WHERE clause for UPDATE and DELETE Option Key Columns Result The WHERE clause includes the key columns only. These are the columns you specified in the Unique Key Columns box. The values in the originally retrieved key columns for the row are compared against the key columns in the database. No other comparisons are done. If the key values match, the update succeeds. Caution Be very careful when using this option.
CHAPTER 19 Controlling Updates in Forms This statement will succeed regardless of whether other users have modified the row since your form retrieved the row. For example, if another user had modified the salary to $70,000, that change will be overwritten when your form updates the database.
Specifying update when key is modified How to choose a setting 538 Consider the following when choosing the Key Modification setting: • If multiple rows are changed, DELETE and INSERT always work. In some DBMSs, UPDATE fails if the user modifies two keys and sets the value in one row to the original value of the other row. • You might choose the setting here based on your DBMS triggers. For example, if there is an Insert trigger, select Use Delete then Insert.
CH A PTE R 2 0 About this chapter Enhancing Forms Before using a form, you might want to enhance it to make it easier to use and interpret data. This chapter describes enhancements you can make to a form.
Working in the Form painter Layout view Working in the Form painter Layout view Here is the Form painter Layout view for a Freeform form style: This section describes how to work in the Form painter • “Using the Form painter toolbars” next • “Using the pop-up menus in the Form painter” on page 542 • “Using the Properties view in the Form painter” on page 542 • “Selecting controls in the Form painter” on page 543 • “Defining default colors and borders in the Form painter” on page 544 • “Printing
CHAPTER 20 Enhancing Forms About the PainterBars The Form painter PainterBars have buttons for operations such as Save, Close, and Run. They also have five drop-down toolbars, which have a small black triangle on the right side of the button: • Controls – shows the controls you can add to a form. For example, to place a computed field in the form, click the Compute button on the Controls drop-down toolbar, then click the location in the form where you want the computed field to appear.
Working in the Form painter Layout view Using the pop-up menus in the Form painter Each element of the form (such as text, columns, computed fields, buttons, even the form itself) has a pop-up menu you can use to perform appropriate actions and display the associated properties in the Properties view. ❖ To use a pop-up menu in the Form painter Layout view: 1 Position the pointer over the control or the background of the form. 2 Press the right mouse button.
CHAPTER 20 Enhancing Forms Here is the Properties view for the column employee_street. It has several tabbed property pages of information, which you access by clicking one of the tabs. For example, to choose an edit style for a column, you click the Edit tab: When you want to modify part of a form, select the part and use the Properties view. Click the various tabs to change pages. Selecting controls in the Form painter The Form painter provides several ways for you to select controls to act on.
Working in the Form painter Layout view ❖ To select neighboring multiple controls in a form: 1 Press and hold the left mouse button at one corner of the neighboring controls. 2 Drag the mouse over the controls you want to select. InfoMaker displays a bounding box. 3 Release the mouse button. All the controls in the region are selected. ❖ To select non-neighboring multiple controls in a form: 1 Click the first control. 2 Press and hold the Ctrl key and click additional controls.
CHAPTER 20 ❖ Enhancing Forms To specify default colors and borders for a Freeform or Grid style form: 1 Select Design>Options from the menu bar and then select the Generation page: 2 Select the style whose defaults you want to change from the Presentation Style drop-down list.
Reorganizing controls in the form Printing the form definition At any point when you are working on a form, you can print a document that lists all controls in the form and their properties. ❖ To print a document describing the controls in the form: • Select File>Print Form Definition from the menu bar. InfoMaker prints the form definition on the default printer. ❖ To select a different printer: • Select File>Printer Setup from the menu bar and select the printer.
CHAPTER 20 Enhancing Forms The options are: Option Meaning Snap to Grid X If selected, controls snap to the grid when you place or move them The width of each cell in the grid in pixels Y The height of each cell in the grid in pixels Deleting controls in the Form painter ❖ To delete controls in the Form painter: 1 Select the controls you want to delete. 2 Do one of the following: • Click the Clear button. • Select Edit>Delete from the menu bar. • Press the Delete key.
Reorganizing controls in the form InfoMaker selects the column and displays a line representing the column border: 3 Drag the column left or right. 4 Release the mouse button to drop the column into position. Copying and pasting controls in the Form painter You can copy controls within a form and to other forms. All properties of the control are copied. ❖ To copy a control in the Form painter: 1 Select the control in the Layout view. 2 Select Edit>Copy from the menu bar or press Ctrl+C.
CHAPTER 20 Enhancing Forms Resizing controls in the Form painter You can resize a control using the mouse or the keyboard. Using the mouse To resize a control using the mouse, select it, then grab an edge and drag it with the mouse.
Reorganizing controls in the form Avoid lasso selection for aligning controls Avoid selecting controls by dragging the mouse to put a bounding box around multiple controls. You cannot control which control is used as the basis for aligning the other controls. 3 Use the Layout drop-down toolbar in the PainterBar, or select Format>Align from the menu bar. 4 Select the dimension along which you want to align the controls.
CHAPTER 20 Enhancing Forms Equalizing the size of controls in the Form painter Say you have several controls in a form and want their sizes to be the same. You can accomplish this manually or by using the Edit menu. ❖ To equalize the size of controls in the Form painter: 1 Select the control whose size is correct. 2 Select the other controls whose size you want to match to the first control by pressing Ctrl and clicking the controls.
Reorganizing controls in the form ❖ To use sliding columns or controls: 1 Select Properties from the pop-up menu of the controls and then select the Position tab in the Properties view: 2 Select the Slide options you want. Option Slide Left Description Slide the column or control to the left if there is nothing to the left. Be sure the control does not overlap the control to the left. Sliding left does not work if the controls overlap.
CHAPTER 20 Enhancing Forms Modifying general form properties This section describes the general form properties you can modify. Specifying a title for a form You can specify a title that displays in the title bar of a form when you run the form: ❖ To specify a title for a form: 1 Display the pop-up menu of the form and select Properties. 2 On the General page of the Properties view, specify the title in the Title box.
Modifying general form properties ❖ To set colors for a form: • Do one of the following: To set colors for Do this The form’s background A control Position the pointer on an empty spot in the form, display the pop-up menu, then select Properties. On the General page of the Properties view, select a color from the Color drop-down list. Select the control and use the Foreground Color dropdown toolbar and the Background Color drop-down toolbar.
CHAPTER 20 Enhancing Forms Specifying the display of scrollbars for a form You can specify whether your form has scrollbars when you run it. ❖ To specify scrollbars for a form: 1 Display the form’s pop-up menu and select Properties. 2 On the General page of the Properties view, select the type of scrollbar you want. Specifying pointers for a form You can specify a particular pointer image to display when the mouse pointer is over a specific area of a form.
Modifying general form properties ❖ To modify text in a form: 1 Select the text. The first box in the StyleBar is now active: 2 Type the new text. Use ~n~r to start a new line in the text. For example, typing Employee~n~rFirst Name places First Name on the next line. ❖ To change the text properties for a control in a form: 1 Select the control. 2 Change the text properties in one of the following ways: • Using the StyleBar. • Using the Properties view, Font page.
CHAPTER 20 Enhancing Forms The value 0 removes the control from the tab order so that you cannot tab to the control. It does not matter what value you use (other than 0); all that matters is a relative value. For example, if you want to tab to column B after column A but before column C, set the tab value for column B so that it is between the value for column A and the value for column C. 4 Repeat the procedure until you have the tab order you want.
Modifying general form properties Prompting for retrieval criteria in a form You can define your form so that it always prompts you for retrieval criteria just before it retrieves data. In this way, you limit the data retrieved. ❖ To prompt for retrieval criteria in a form: 1 Select Design>Prompt for Criteria from the menu bar.
CHAPTER 20 Using edit styles Enhancing Forms If a column uses a code table or the RadioButton, CheckBox, or DropDownListBox edit style, an arrow displays in the column header, and you can select a value from a drop-down list when you run the form.
Modifying general form properties Modifying the data source of a form When modifying a form, you might realize that you have not included all the columns you need, or you might need to define retrieval arguments. You can modify the data source from the Form painter by graphically modifying the SQL SELECT statement. ❖ To modify the form’s data source: 1 Select Design>Edit Data Source from the menu bar, or select Edit Data Source from the form’s pop-up menu.
CHAPTER 20 Enhancing Forms Adding controls to the form The topics in this section describe how to enhance a form by adding controls: Adding columns to a form You can add columns to a form: you can restore columns you have deleted or add columns after you have modified the data source to include more columns. ❖ To add a column to a form: 1 Click the Column button in the Controls drop-down toolbar, or select Insert>Column from the menu bar. 2 Click where you want to place the column.
Adding controls to the form Adding computed fields to a form You can use computed fields to perform calculations in the form. Typical uses of computed fields include: • Calculations based on column data that change for each retrieved row For example, if you are retrieving yearly salary, you could define a computed field that displays monthly salary (defined as Salary / 12). • Summary statistics For example, you can use a computed field to calculate the average salary of all the retrieved rows.
CHAPTER 20 Enhancing Forms If you want computed values to change dynamically, define computed fields in the Form painter, as described next. Defining a computed field ❖ To add a computed field to a form: 1 Click the Compute button in the Controls drop-down toolbar, or select Insert>Computed Field from the menu bar. 2 Click where you want the computed field.
Adding controls to the form About the Modify Expression dialog box The Modify Expression dialog box provides you with lists and buttons to help you create the computed field: The Modify Expression dialog box contains: Entering the expression • A Functions box with a list of built-in functions you can use in the computed field • A Columns box with a list of columns, named computed fields, and retrieval arguments in the form • Buttons for adding operators and parentheses You can enter any valid exp
CHAPTER 20 Enhancing Forms To display Enter this expression Monthly salary if Salary column contains annual salary Salary / 12 Four asterisks if the value of the Salary column is greater than $50,000 IF(Salary> 50000, "****", "") Average salary of all retrieved rows Count of retrieved rows, assuming each row contains a value for EmpID Avg(Salary) Count(EmpID) For more information about the functions you can use in computed fields in the Form painter, see Chapter 24, “DataWindow Expression and Info
Adding controls to the form Adding command buttons to a form You can add command buttons to a form. Command buttons are used to carry out an action. For example, you can add a button that prints the current form. ❖ To add a button to a form: 1 Click the Button button in the Controls drop-down toolbar, or select Insert>CommandButton from the menu bar. 2 Click where you want to place the command button. A button with the text none displays.
CHAPTER 20 ❖ Enhancing Forms To associate an action with a button: 1 Move the pointer to the button you added to the form, display the pop-up menu, and select Action. The Select Action dialog box displays the actions that are provided with the form style you are using: 2 Select the action from the list. 3 If the action takes parameters, click the Parameters button and supply the parameters. 4 Click OK. You return to the Form painter Layout view.
Adding controls to the form Adding picture buttons to a form Picture buttons are identical to command buttons in their functionality. The only difference is that you can specify a bitmap (BMP), runlength-encoded (RLE), Windows metafile (WMF), CompuServe Graphics Interchange (GIF), or JPEG file to display in the button. Use these controls when you want to be able to represent the purpose of a button using a picture instead of just text.
CHAPTER 20 3 Enhancing Forms In the Properties view, supply the name of the report to show in the control. You can use the browse button to search for the report. InfoMaker places the report in the form (you see everything but the data, which is displayed when you run the form). At this point, you may want to resize the report or change some of its other properties. For example, you may want to make the report itself resizable.
Highlighting information in a form ❖ To group controls with a round rectangle and text in the Form painter: 1 Select Insert>RoundRectangle from the menu bar and add a round rectangle to the Layout view. 2 Size the rectangle and place it over the controls you want to group. 3 Select Send To Back from the rectangle’s pop-up menu to place the rectangle behind the group of controls.
CHAPTER 20 Enhancing Forms You modify properties based on conditions you specify by entering an expression in the Properties view for the control. In this example, the expression for the salary column's color property is: if( employee_salary > 30000, 255, 0 ) For more information For information on modifying properties based on conditions you specify, see Chapter 10, “Highlighting Information in Reports and Forms.” The technique works the same way in forms and reports.
Displaying and validating data in a form 572 InfoMaker
PART 5 Applications This part describes how to create and deploy InfoMaker applications.
CH A PTE R 2 1 About this chapter Contents Working with Applications You can bundle reports, forms, and data pipelines into a package to create a reporting and database-maintenance application. This chapter describes how to create an InfoMaker application.
Creating an application Identifying an application Most applications can be identified by selecting Help>About to display information about the application, such as its name, name of the company producing the application, version, and so on. After you create an application, you can modify the application’s initialization file so that your users see customized information. For information about identifying your application, see “Identifying your application” on page 585.
CHAPTER 21 ❖ Working with Applications To create an application: 1 Connect to the database that the executable will use. 2 Open the Library painter and the library containing the objects you want to include in the application. 3 Select Design>Create Executable from the menu bar. If you are creating your first application in the current library, the Create Executable dialog box displays.
Creating an application The values you specify become part of the Version resource associated with the executable file. The names you enter on the right display on the Version tab page of the Properties dialog box for the executable file in Windows Explorer.
CHAPTER 21 8 Working with Applications Click the Browse button next to the Executable Icon box to assign an icon to the executable file. The Select Icon dialog box displays. 9 Navigate to an ICO file and click Open. The icon you choose will be used if you create a shortcut for the executable file and when you minimize the executable. You return to the Create Executable dialog box. The following example shows the dialog box filled in.
Creating an application 12 Select the reports, forms, and pipelines you want to package in the application. 13 Click Next. The Executable Items dialog box displays, listing all reports, forms, and pipelines you have selected for the executable file. In the Executable Items dialog box, you can define the properties of a toolbar button for any of the items in the executable. Then in the application you can run the report, form, or pipeline by simply clicking the toolbar button.
CHAPTER 21 Working with Applications To quickly prototype an application The remaining steps describe how to define text, MicroHelp, and pictures for the application’s toolbar buttons. You can create an application quickly by clicking Finish now. After you test your prototype, you can reuse the application definition and finish the remaining steps (which define the toolbar buttons).
Reusing an application 16 If you want to omit a report from the list of reports in the generated application so that users cannot run it as a standalone report, select the report and then select the Hide Report check box. This is useful for reports that have been included only to support a: • DropDownDataWindow edit style • Report added to a form • Report nested in another report 17 When you have defined all the toolbar items, click Finish.
CHAPTER 21 ❖ Working with Applications To reuse the most recent application associated with a library: 1 Connect to the database, open the library, and select Design>Create Executable from the menu bar.
Running an application ❖ To modify the Start In property of the application shortcut: 1 Position the pointer on the icon, display the pop-up menu, and select Properties. InfoMaker displays the shortcut’s property sheet. 2 Select the Shortcut tab and type the location of the PowerBuilder runtime files in the Start In box.
CHAPTER 21 Working with Applications Identifying your application Most applications can be identified by selecting Help>About to display information about the application such as its name, name of the company producing the application, version, and so on. By default, InfoMaker includes the application name and Sybase brand information to identify your application. You can modify the application’s initialization file so that your users see customized information about you and your company.
Running an application Running a report, form, or pipeline To run a report, form, or pipeline, you can do any of the following: • Click its button in the toolbar if you defined a toolbar item for the object. • Select it from the Objects menu, which lists all reports, forms, or pipelines in the executable. If you specified a toolbar button and button text for a report, form, or pipeline, this text displays instead of the report, form, or pipeline object name in the Objects menu.
CHAPTER 21 Working with Applications Managing the toolbar You can move the toolbar and suppress the display of text in the toolbar by selecting items from the pop-up menu. Managing the open reports, forms, and pipelines You can manage the display of reports, forms, and pipelines you have opened by selecting items from the Window menu, which InfoMaker automatically provides in the executable.
Using a pipeline in an application You can print the log or save the log to any report format. Users may need to send the log to you to use for debugging a pipeline. You can empty the log at any time by clicking the Reset Log button. Executing pipelines A pipeline in an application executes when you execute it manually or when you set a delayed execution mode to automatically execute the pipeline. The execution mode (Manual by default) shows in the Execution Mode box.
CHAPTER 21 3 Working with Applications In the Start boxes, specify the execution date and time. The day, month, numerical day, and year for the date you specify display under the Start boxes. You should verify that this date is the date you want, because the date is based on the operating system’s short date setting. For example, a date of 04/12/06 could mean April 12, 2006, or December 6, 2004. 4 Click OK. The pipeline will execute at the specified date and time.
Using a pipeline in an application For information about adding a [Pipe] section to the initialization file, see “Modifying the pipeline object’s definition” on page 590. Repairing execution errors When a pipeline executes and has execution errors, the error messages display in the Pipeline Errors box in the workspace and instructions for repairing the errors display in a message box. ❖ To repair pipeline errors: 1 In the Pipeline Errors box, look at the Error Message column to identify errors.
CHAPTER 21 ❖ Working with Applications To enable users to modify the pipeline’s definition: 1 Open the application’s initialization file and create a Pipe section by adding the following line: [Pipe] 2 Include these lines in the Pipe section: [Pipe] AllowTypeChange = 1 AllowRunTimeChange = 1 This keyword Allows the user to change the AllowTypeChange Type of pipeline operation: Create, Refresh, Replace, Append, and Update AllowRunTimeChange Commit value, Max Errors value, and whether to pipe exte
Using a pipeline in an application ❖ To modify the pipeline type: 1 Select Actions>Pipeline Type from the menu bar. A menu of pipeline types displays. 2 ❖ Select the pipeline type: To modify the Commit value: 1 Select Actions>Pipeline Commit After from the menu bar. A menu of Commit values displays. 2 ❖ Select the Commit value: To modify the Max Errors value: 1 Select Actions>Pipeline Max Errors Allowed from the menu bar. A menu of Max Errors values displays.
CHAPTER 21 2 ❖ Working with Applications Select the Max Errors value: To specify whether or not to pipe extended attributes: • Select or clear the Actions>Pipeline Copy Extended Attributes menu item: Starting an application from the command line You or your users can start an InfoMaker application from a command line (or the Windows Run dialog box) and pass parameters to perform actions on reports, forms, and pipelines.
Starting an application from the command line Opening an object or creating a new object Examples You can also add one or more of the following optional parameters to the command line to perform a specific action: Parameter Description /R reportname /RP reportname Run the specified report Run and print the specified report /RPC reportname /A arg1;arg2;argN Run and print the specified report and close the application Provides a list of retrieval arguments for a report specified by one of the /R param
CH A PTE R About this chapter Contents 2 2 Deploying Your Application This chapter provides information required for deploying applications to users’ computers.
About deploying applications Figure 22-1: Deploying an InfoMaker application To set up a user’s machine to run your application, you need to copy all the runtime files and database interface files you need to the right places. If you are using a SQL Anywhere database, the SQL Anywhere files needed to run your application must also be installed. When you deploy an InfoMaker application, what you install on a user’s computer depends on whether the user has InfoMaker installed.
CHAPTER 22 Deploying Your Application 5 Install the database runtime files on the user’s computer. 6 Configure your ODBC drivers, system path, and registry files. Installing InfoMaker runtime files When you install InfoMaker, shared files that are used by both PowerBuilder and InfoMaker are installed in the Shared\PowerBuilder directory. You need to deploy a subset of these files with your application. The following table indicates which feature each file is required to support. For example, PBVM115.
Installing InfoMaker runtime files Microsoft files Required for Name Accessibility (Section 508) support PBACC115.DLL Database connection tracing PBTRA115.DLL When you deploy the core InfoMaker runtime files, you must also deploy the msvcr71.dll and msvcp71.dll Microsoft Visual C++ runtime libraries and the Microsoft .NET Active Template Library (ATL) module, atl71.dll, if they are not present on the user’s computer. The InfoMaker runtime files have a runtime dependency on these files.
CHAPTER 22 Deploying Your Application Making the data source available Your users need access to the DBMS and to the database your application uses. You need to: • If necessary, install the DBMS runtime files in the application directory or in a directory on the system path. Follow the instructions and licensing rules specified by the vendor. • Make sure each user has access to the database the application uses.
Making the data source available Installing ODBC and system files If your application uses ODBC drivers, each user’s computer needs three types of files: • Install PBODB115.DLL in the application directory or a directory on the system path. The PBODB115.INI file must be in a directory defined by the HKEY_CURRENT_USER\Software\Sybase\InfoMaker\11.5\InitPath registry setting or, in the absence of that key, in the same directory as the DLL file.
CHAPTER 22 Deploying Your Application [HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\MyApp DB] "Driver"="C:\Program Files\SQL Anywhere 11\ Bin32\dbodbc11.dll" "Start"="C:\Program Files\SQL Anywhere 11\Bin32\ dbeng11.exe" "UID"="dba" "PWD"="sql" "Description"="Database for my application" "DatabaseFile"="C:\Program Files\myapps\myapp.db" "AutoStop"="Yes" [HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ ODBC Data Sources] "MyApp DB"="SQL Anywhere 11.
Making the data source available CurrentVersion\App Paths\myapp.exe] "Default"="C:\Program Files\myapps\MYAPP.EXE" "Path"="Program Files\sybase\shared\PowerBuilder; C:\Program Files\SQL Anywhere 11\Bin32\;" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ ODBC Drivers] "SQL Anywhere 11"="Installed" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ SQL Anywhere 11] "Driver"="C:\Program Files\SQL Anywhere 11\ Bin32\dbodbc11.dll" "Setup"="C:\Program Files\SQL Anywhere 11\ win32\dbodbc11.
CHAPTER 22 Deploying Your Application If your application uses a standalone database, you can deploy the SQL Anywhere Desktop Runtime System to users’ computers without incurring additional license fees. The runtime system allows the user to retrieve and modify data in the database, but does not allow modifications to the database schema. It does not support transaction logs, stored procedures, or triggers.
Making the data source available OLE DB database providers If your application uses OLE DB to access data, you must install Microsoft’s Data Access Components software on each user’s computer if it is not installed already. The InfoMaker OLE DB interface requires the functionality of the Microsoft Data Access Components (MDAC) version 2.8 or higher software. Version 2.8 is distributed with Windows XP Service Pack 2 and Windows Server 2003.
CHAPTER 22 Deploying Your Application Table 22-5: PowerBuilder JDB interface files Name PBJDB115.DLL Description PowerBuilder JDBC Driver (JDB) for JRE 1.2 or later pbjdbc12115.jar Java package for PowerBuilder JDB driver and JRE 1.2 or later Java support You must deploy the pbjvm115.dll file with any applications or components that use the Java Runtime Environment (JRE), and there must be a JRE installed on the target computer. The JRE is required for JDBC connections.
Making the data source available If none of these directory structures is found, InfoMaker uses the first jvm.dll whose location is defined in the user’s PATH environment variable. If no jvm.dll is found, the Java VM does not start. The runtime Java VM classpath Overriding the runtime static registry classpath When an InfoMaker application starts a Java VM, the Java VM uses internal path and classpath information to ensure that required Java classes are always available.
CHAPTER 22 Deploying Your Application You can override either the configuration or properties entries or both. If you make incorrect entries, InfoMaker attempts to recover by defaulting to the static registry. However, you should be cautious about making any changes since you can cause incorrect behavior in the JVM if you do not configure it correctly.
Saving as PDF and XSL-FO ❖ To install Ghostscript: 1 Into a temporary directory on your computer, download the self-extracting executable file for the version of Ghostscript you want from one of the sites listed on the Ghostscript Web site at http://www.ghostscript.com/awki. See the release bulletin for the version of Ghostscript that was used for testing. 2 Run the executable file to install Ghostscript on your system. The default installation directory is C:\Program Files\gs.
CHAPTER 22 Deploying Your Application You must also deploy the related files that are installed in Sybase\Shared\PowerBuilder\drivers. These files can be copied to or installed on users’ computers. They must be located in this directory structure: dirname\pbdwe115.dll dirname\drivers PostScript printer profile Each user’s computer must have a PostScript printer profile called Sybase DataWindow PS.
Installing the executable application and supporting files Installing the executable application and supporting files When you install your application on a user’s machine, make sure you include any supporting files, such as dynamic libraries, resources such as BMP and ICO files, online Help files, and initialization files.
CHAPTER 22 Deploying Your Application Starting the deployed application Your users can run your application the same way they run other Windows applications. For example, they can double-click the executable file in Explorer or create an application shortcut on the desktop and double-click the shortcut. If users create a shortcut, the Target textbox on the Shortcut properties page should specify the path to the executable, and the Start In textbox should specify the location of the runtime files.
Starting the deployed application 612 InfoMaker
PART 6 Reference This part describes using operators and expressions and InfoMaker expression functions.
CH A PTE R About this chapter Contents 2 3 Operators and Expressions You use an expression to request that InfoMaker perform a computational operation. This chapter explains how expressions work and how to write them.
Where you use expressions In painters, you use expressions in these ways: Table 23-1: Using expressions in InfoMaker painters In this painter Report painter Form painter Database painter Expressions are used in Computed fields Conditional expressions for property values Filters Sorting Series and values in graphs Columns, rows, and values in crosstabs Computed fields Conditional expressions for property values Validation rules Validation rules Other types of expressions you use You also use expression
CHAPTER 23 In filters Operators and Expressions Filter expressions are boolean expressions that must evaluate to true or false: Table 23-3: Using expressions with filters Expression Academics = "*****" AND Cost = "$$$" Emp_sal < 50000 Description Displays data only for colleges with both a 5-star academic rating and a $$$ cost rating Displays data for employees with salaries less than $50,000 Salary > 50000 AND Dept_id BETWEEN 400 AND 700 Displays data for employees in departments 400, 500, 600, and 7
Operators used in InfoMaker expressions You can refer to values in other columns for the current row by specifying their names in the validation rule: Other columns in the rule Table 23-4: Using expressions with values from other columns Expression in Database painter @column >= 10000 Expression in Form painter Integer(GetText())>= 10000 @column IN (100, 200, 300) Integer(GetText()) IN (100, 200, 300) @salary > 0 Long(GetText()) > 0 Match(@disc_price, "^[0-9]+$") and @disc_price < Full_Price Match
CHAPTER 23 Operators and Expressions Arithmetic operators in InfoMaker expressions When you write an expression, you can use the following arithmetic operators: Table 23-5: Using expressions with arithmetic operators Multiplication and division Operator Meaning Example + - Addition Subtraction SubTotal + Tax Price - Discount * / Multiplication Division Quantity * Price Discount / Price ^ Exponentiation Rating ^ 2.5 Multiplication and division are carried out to full precision (16–18 digits).
Operators used in InfoMaker expressions When you write an expression, you can use the following relational operators (more information about LIKE, IN, and BETWEEN follows the table): Table 23-7: Using expressions with relational operators Operator = Meaning Is equal to Example Price = 100 > < Is greater than Is less than Price > 100 Price < 100 <> >= Is not equal to Greater than or equal to Price <> 100 Price >= 100 <= NOT = Less than or equal to Is not equal to Price <= 100 Price NOT= 100 LIKE
CHAPTER 23 Operators and Expressions For example, the following expression for the Background.Color property of the Salary column displays salaries in red for employees with last names beginning with F and displays all other salaries in white: If(emp_lname LIKE'F%',RGB(255,0,0),RGB(255,255,255)) Escape keyword If you need to use the % or _ characters as part of the string, you can use the escape keyword to indicate that the character is part of the string.
Operators used in InfoMaker expressions IN and NOT IN operators Use IN to check if a value is in a set of values. Use NOT IN to check if a value is not in a set of values. For example, the following expression for the Background.
CHAPTER 23 Operators and Expressions To compare strings when trailing blanks are significant, use an expression such as the following to ensure that any trailing blanks are included in the comparison: City1 + ">" = City2 + ">" For information about these functions, see “Using DataWindow expression and InfoMaker functions” on page 629. Logical operators in InfoMaker expressions You use logical operators to combine boolean expressions into a larger boolean expression.
Operators used in InfoMaker expressions If one expression has this value And the logical operator is And if another expression has this value The resulting expression has this value FALSE TRUE AND OR FALSE TRUE FALSE TRUE TRUE FALSE OR OR FALSE TRUE TRUE TRUE FALSE NOT TRUE OR AND FALSE TRUE FALSE FALSE NOT TRUE NOT FALSE AND AND FALSE TRUE FALSE TRUE NOT FALSE AND FALSE FALSE NOT TRUE NOT TRUE OR OR TRUE FALSE TRUE FALSE NOT FALSE NOT FALSE OR OR TRUE FALSE TRUE TRUE If you
CHAPTER 23 Operators and Expressions Operator precedence in InfoMaker expressions To ensure predictable results, operators in InfoMaker expressions are evaluated in a specific order of precedence. When operators have the same precedence, they are evaluated from left to right.
Matching text patterns Matching text patterns A text pattern is an expression that you can use to evaluate whether a string contains a particular pattern of characters. Text patterns consist of metacharacters, which have special meaning, and characters (nonmetacharacters). The way the metacharacters and normal characters are combined specify the pattern you are looking for. Metacharacters Valid metacharacters are: • ^ (caret) • $ (dollar sign) • .
CHAPTER 23 Users Guide Operators and Expressions This pattern Matches ABB*C Any string containing the pattern ABC or ABBC or ABBBC, and so on (1 B plus 0 or more Bs). ^AB AB?C Any string starting with AB. Any string containing the pattern AC or ABC (0 or 1 B). ^[ABC] [^ABC] Any string starting with A, B, or C. A string containing any characters other than A, B, or C. ^[^abc] ^[^a-z]$ [A-Z]+ A string that begins with any character except a, b, or c.
Matching text patterns 628 InfoMaker
CH A PTE R 2 4 About this chapter Contents DataWindow Expression and InfoMaker Functions This chapter provides syntax, descriptions, and examples of the functions you can use in expressions in the DataWindow painter and in InfoMaker’s Report painter and Form painter.
Four examples Within an expression, a function can return other datatypes (such as boolean, date, or integer), but the final value of an expression is converted to one of these datatypes. Restrictions for aggregate functions An aggregate function is a function (such as Avg, Max, StDev, and Sum) that operates on a range of values in a column. When you use an aggregate function, some restrictions apply.
CHAPTER 24 What you want to do DataWindow Expression and InfoMaker Functions Suppose you are working with the Fin_code table in the Enterprise Application Sample Database.
Four examples What you get Here is the design for the report. Here is the report showing eight descriptions, three of which are null and five of which are not null. The last description for Id=8 is null. Example 2: counting male and female employees Example 1 demonstrates the use of the Sum and Count functions. Sum and Count are two examples of a class of functions called aggregate functions. An aggregate function is a function that operates on a range of values in a column.
CHAPTER 24 DataWindow Expression and InfoMaker Functions About crosstab functions Although the crosstab functions (CrosstabAvg, CrosstabAvgDec, CrosstabCount, CrosstabMax, CrosstabMaxDec, CrosstabMin, CrosstabMinDec, CrosstabSum, and CrosstabSumDec) behave like aggregate functions, they are not included on the list because they are for crosstabs only and are designed to work in the crosstab matrix. A few restrictions apply to the use of aggregate functions.
Four examples Here is the last page of the report, with the total number of males and females in the company displayed. If you want more information What if you decide that you also want to know the number of males and females in each department in the company? ❖ To display the males and females in each department: 1 Select Design>Data Source from the menu bar so that you can edit the data source.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Here is what the design of the grouped report looks like. Here is the last page of the report with the number of males and females in the shipping department displayed, followed by the total number of males and females in the company.
Four examples Example 3: creating a row indicator This example demonstrates the use of several functions: Bitmap, Case, CurrentRow, GetRow, and RGB. The example is presented in the DataWindow painter, which is the same as InfoMaker’s Report painter. You can use all the functions shown in the example in the Report painter.
CHAPTER 24 • DataWindow Expression and InfoMaker Functions An expression for the Background.Color property of the salary column: Case(salary WHEN IS >60000 THEN RGB(192,192,192) WHEN IS >40000 THEN RGB(0,255,0) ELSE RGB(255,255,255)) The expression causes a salary above $40,000 to display in green, a salary above $60,000 to display in gray, and all other salaries to display in white.
Four examples Example 4: displaying all data when a column allows nulls When you create an arithmetic expression that has a null value, the value of the expression is null. This makes sense, since null means essentially undefined and the expression is undefined, but sometimes this fact can interfere with what you want to display. What you want to do A table in your database has four columns: Id, Corporation, Address1, and Address2. The Corporation, Address1, and Address2 columns allow null values.
CHAPTER 24 DataWindow Expression and InfoMaker Functions When you preview the report, notice that the first computed field displays null for ABC Corporation and XYZ Corporation. The second computed field displays the first part of the address, which is not null. Other examples In InfoMaker, to see some examples of using functions, examine the reports and forms in TUTOR_IM.PBL, which is InfoMaker’s sample library. The reports and forms were created using data in the EAS Demo DB.
Alphabetical list of DataWindow expression and InfoMaker functions Alphabetical list of DataWindow expression and InfoMaker functions The list of DataWindow expression and InfoMaker functions follows in alphabetical order.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Abs Description Calculates the absolute value of a number. Syntax Abs ( n ) Argument n Description The number for which you want the absolute value Return value The datatype of n. Returns the absolute value of n.
Asc Asc Description Converts the first character of a string to its Unicode code point. A Unicode code point is the numerical integer value given to a Unicode character. Syntax Asc ( string ) Argument string Description The string for which you want the code point value of the first character Return value Unsigned integer. Returns the code point value of the first character in string. Usage Use Asc to test the case of a character or manipulate text and letters.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples This expression for a computed field returns the string in code_id if the ASCII value of the first character in code_id is A (65): If (AscA(code_id) = 65, code_id, "Not a valid code") This expression for a computed field checks the case of the first character of lname and if it is lowercase, makes it uppercase: IF (AscA(lname) > 64 AND AscA(lname) < 91, lname, WordCap(lname)) See also CharA WordCap ASin Description Calculates the arc si
ATan ATan Description Calculates the arc tangent of an angle. Syntax ATan ( n ) Argument n Description The ratio of the lengths of two sides of a triangle for which you want a corresponding angle (in radians) Return value Double. Returns the arc tangent of n if it succeeds. Examples This expression returns 0: ATan(0) This expression returns 1.000 (rounded to three places): ATan(1.55741) This expression returns 1.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description FOR range (optional) The data that will be included in the average. For most presentation styles, values for range are: • ALL – (Default) The average of all values in column. • GROUP n – The average of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1. • PAGE – The average of the values in column on a page.
Avg Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a report always retrieves all rows.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Bitmap Description Displays the specified bitmap. For computed fields only You can use the Bitmap function only in a computed field.
Case Case Description Tests the values of a column or expression and returns values based on the results of the test. Syntax Case ( column WHEN value1 THEN result1 { WHEN value2 THEN result2 { ... } } { ELSE resultelse } ) Argument Description column The column or expression whose values you want to test. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. Column is compared to each valuen.
CHAPTER 24 DataWindow Expression and InfoMaker Functions This expression for the Background.
Char To pass this validation rule, the value in discount_amt must be less than or equal to the smallest whole number that is greater than or equal to discount_factor times price: discount_amt <= Ceiling(discount_factor * price) See also Int Round Truncate Char Description Converts an integer to a Unicode character. Syntax Char ( n ) Argument Description n The integer you want to convert to a character Return value String. Returns the character whose code point value is n.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Cos Description Calculates the cosine of an angle. Syntax Cos ( n ) Argument n Description The angle (in radians) for which you want the cosine Return value Double. Returns the cosine of n. Examples This expression returns 1: Cos(0) This expression returns .540302: Cos(1) This expression returns -1: Cos(Pi(1)) See also Pi Sin Tan Count Description Calculates the total number of rows in the specified column.
Count Argument Description FOR range (optional) The data that will be included in the count. For most presentation styles, values for range are: • ALL – (Default) The count of all rows in column. • GROUP n – The count of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1. • PAGE – The count of the rows in column on a page.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a report always retrieves all rows.
CrosstabAvg Syntax CrosstabAvg ( n {, column, groupvalue } ) Argument n column (optional) groupvalue (optional) Description The number of the crosstab-values expression for which you want the average of the returned values. The crosstab expression must be numeric. The number of the crosstab column as it is listed in the Columns box of the Crosstab Definition dialog box for which you want intermediate calculations. A string whose value controls the grouping for the calculation.
CHAPTER 24 DataWindow Expression and InfoMaker Functions When you define the crosstab described above, the painter automatically creates the appropriate computed fields. A computed field named avg_amount returns the average of the quarterly figures for each year. Its expression is: CrosstabAvg(1, 2, "@year") A second computed field named grand_avg_amount computes the average of all the amounts in the row.
CrosstabAvg What the function arguments mean When the crosstab definition has more than one column, you can specify column qualifiers for any of the Crosstab functions, so that the crosstab displays calculations for groups of column values.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Consider a crosstab that has two columns (region and city) and the values expression Avg(sales for crosstab).
CrosstabCount Return value Decimal. Returns the average of the crosstab values returned by expression n for all the column values or, optionally, for a subset of column values. Usage Use this function instead of CrosstabAvg when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabAvg.
CHAPTER 24 DataWindow Expression and InfoMaker Functions For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg. Reviewing the expressions To review the expressions defined for the crosstab values, open the Crosstab Definition dialog box (select Design>Crosstab from the menubar).
CrosstabMax CrosstabMax Description Calculates the maximum value returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabMax can also calculate the maximum of the expression’s values for groups of column values. For crosstabs only You can use this function only in a crosstab report.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples These examples all use the crosstab-values expressions shown below: Count(emp_id for crosstab),Sum(salary for crosstab) This expression for a computed field in the crosstab returns the maximum of the employee counts (the first expression): CrosstabMax(1) This expression for a computed field in the crosstab returns the maximum of the salary totals (the second expression): CrosstabMax(2) The next two examples use a crosstab with two column
CrosstabMin Syntax CrosstabMaxDec ( n {, column, groupvalue } ) Argument n column (optional) groupvalue (optional) Description The number of the crosstab-values expression for which you want the maximum returned value. The expression’s datatype must be numeric. The number of the crosstab column as it is listed in the Columns box of the Crosstab Definition dialog box for which you want intermediate calculations. A string whose value controls the grouping for the calculation.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description column (optional) The number of the crosstab column as it is listed in the Columns box of the Crosstab Definition dialog box for which you want intermediate calculations. A string whose value controls the grouping for the calculation. Groupvalue is usually a value from another column in the crosstab.
CrosstabMinDec This expression for a computed field returns the minimum of all the average sales in the row: CrosstabMin(1) For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the crosstab functions, see CrosstabAvg.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Usage Use this function instead of CrosstabMin when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabMin. See also CrosstabAvgDec CrosstabMaxDec CrosstabSumDec CrosstabSum Description Calculates the sum of the values returned by an expression in the values list of the crosstab.
CrosstabSum For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg. Reviewing the expressions To review the expressions defined for the crosstab values, open the Crosstab Definition dialog box (select Design>Crosstab from the menubar).
CHAPTER 24 DataWindow Expression and InfoMaker Functions CrosstabSumDec Description Calculates the sum of the values returned by an expression in the values list of the crosstab and returns a result with the decimal datatype. When the crosstab definition has more than one column, CrosstabSumDec can also calculate the sum of the expression’s values for groups of column values. For crosstabs only You can use this function only in a crosstab report.
CumulativePercent CumulativePercent Description Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column (a running percentage). Syntax CumulativePercent ( column { FOR range } ) Argument column FOR range (optional) Description The column for which you want the cumulative value of the rows up to and including the current row as a percentage of the total value of the column for range.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Settings for Rows include the following: • For the Graph presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies. In calculating the percentage, null values are ignored.
CumulativeSum CumulativeSum Description Calculates the total value of the rows up to and including the current row in the specified column (a running total). Syntax CumulativeSum ( column { FOR range } ) Argument column Description The column for which you want the cumulative total value of the rows up to and including the current row for group. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column.
CHAPTER 24 DataWindow Expression and InfoMaker Functions In calculating the sum, null values are ignored.
Date Alternatively, this expression for the Visible property of an arrow picture control makes the arrow bitmap visible for the row with focus and invisible for rows not having focus. As the user moves from row to row, an arrow marks where the user is: If(CurrentRow() = GetRow(), 1, 0) See also “Example 3: creating a row indicator” on page 636 GetRow Date Description Converts a string whose value is a valid date to a value of datatype date.
CHAPTER 24 DataWindow Expression and InfoMaker Functions An expression has a more limited set of datatypes than the functions that can be part of the expression. Although the Date function returns a date value, the whole expression is promoted to a DateTime value. Therefore, if your expression consists of a single Date function, it will appear that Date returns the wrong datatype. To display the date without the time, choose an appropriate display format.
Day Using this expression for a computed field displays 11/11/01 11:11:00: DateTime(11/11/01, 11:11) See also Date Time Day Description Obtains the day of the month in a date value. Syntax Day ( date ) Argument Description date The date for which you want the day Return value Integer. Returns an integer (1–31) representing the day of the month in date.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Usage DayName returns a name in the language of the deployment files available on the machine where the application is run. If you have installed localized deployment files in the development environment or on a user’s machine, then on that machine the name returned by DayName will be in the language of the localized files.
DaysAfter See also Date Day DayName IsDate DaysAfter Description Gets the number of days one date occurs after another. Syntax DaysAfter ( date1, date2 ) Argument Description date1 date2 A date value that is the start date of the interval being measured A date value that is the end date of the interval Return value Long. Returns a long containing the number of days date2 occurs after date1. If date2 occurs before date1, DaysAfter returns a negative number.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Dec Description Converts the value of a string to a decimal. Syntax Dec ( string ) Argument string Description The string you want returned as a decimal Return value Decimal. Returns the contents of string as a decimal if it succeeds and 0 if string is not a number. Usage The decimal datatype supports up to 28 digits. You can also append the letter D in upper or lowercase to identify a number as a decimal constant in InfoMaker expressions.
Describe Describe Description Reports the values of properties of a report or form object and the controls within the object. Each column and graphic control in the report or form has a set of properties. You specify one or more properties as a string and Describe returns the values of the properties. Syntax Describe ( propertylist ) Argument propertylist Return value Description A string whose value is a blank-separated list of properties or Evaluate functions String.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Fact Description Gets the factorial of a number. Syntax Fact ( n ) Argument n Description The number for which you want the factorial Return value Double. Returns the factorial of n. Examples This expression returns 24: Fact(4) Both these expressions return 1: Fact(1) Fact(0) Fill Description Builds a string of the specified length by repeating the specified characters until the result string is long enough.
FillA This expression returns the string -+-+-+-: Fill("-+", 7) This expression returns 10 tildes (~): Fill("~", 10) See also FillA Space FillA Description Builds a string of the specified length in bytes by repeating the specified characters until the result string is long enough. Syntax FillA ( chars, n ) Argument chars Description A string whose value will be repeated to fill the return string n A long whose value is the number of bytes in the string you want returned Return value String.
CHAPTER 24 DataWindow Expression and InfoMaker Functions First Description Reports the value in the first row in the specified column. Syntax First ( column { FOR range { DISTINCT { expresn {, expres2 {, ... } } } } } ) Argument column Description The column for which you want the value of the first row. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column.
First For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following: • For the Graph or OLE presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group.
CHAPTER 24 DataWindow Expression and InfoMaker Functions GetRow Description Reports the number of a row associated with a band in a report. Syntax GetRow ( ) Return value Long. Returns the number of a row if it succeeds, 0 if no data has been retrieved or added, and –1 if an error occurs.
GetText GetText Description Obtains the text that a user has entered in a column. Syntax GetText ( ) Return value String. Returns the text the user has entered in the current column. Usage Use GetText in validation rules to compare what the user has entered to application-defined criteria before it is accepted into the data buffer.
CHAPTER 24 DataWindow Expression and InfoMaker Functions If Description Evaluates a condition and returns a value based on that condition. Syntax If ( boolean, truevalue, falsevalue ) Argument boolean Description A boolean expression that evaluates to true or false. truevalue The value you want returned if the boolean expression is true. The value can be a string or numeric value. The value you want returned if the boolean expression is false. The value can be a string or numeric value.
Int Int Description Gets the largest whole number less than or equal to a number. Syntax Int ( n ) Argument n Description The number for which you want the largest whole number that is less than or equal to it Return value The datatype of n. Returns the largest whole number less than or equal to n. Examples These expressions return 3.0: Int(3.2) Int(3.8) These expressions return -4.0: Int(-3.2) Int(-3.
CHAPTER 24 DataWindow Expression and InfoMaker Functions This expression for a computed field returns “Not a valid age” if age does not contain a number.
IsExpanded IsExpanded Description Tests whether a node in a TreeView report with the specified TreeView level and that includes the specified row is expanded. Syntax IsExpanded(long row, long level) Argument row Description The number of the row that belongs to the node level The TreeView level of the node Return value Returns true if the group is expanded and false otherwise. Usage A TreeView report has several TreeView level bands that can be expanded and collapsed.
CHAPTER 24 DataWindow Expression and InfoMaker Functions See also “Example 1: counting null values in a column” on page 630 “Example 4: displaying all data when a column allows nulls” on page 638 IsNumber Description Reports whether the value of a string is a number. Syntax IsNumber ( string ) Argument Description string A string whose value you want to test to determine whether it is a valid number Return value Boolean. Returns true if string is a valid number and false if it is not.
IsRowNew Examples This expression in a computed field in the detail area displays true or false to indicate whether each row has been modified: IsRowModified() This expression defined in the Properties view for the Color property of the computed field displays the text (true) in red if the user has modified any value in the row: If(IsRowModified(), 255, 0) See also GetRow IsRowNew Description Reports whether the row has been newly inserted. Syntax IsRowNew ( ) Return value Boolean.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Usage When you use IsSelected in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes. Examples This expression for a computed field in the detail area displays a bitmap if the row is selected: Bitmap(If(IsSelected(), "beach.bmp", "")) This example allows the report to display a salary total for all the selected rows.
Large Large Description Finds a large value at a specified ranking in a column (for example, thirdlargest, fifth-largest) and returns the value of another column or expression based on the result. Syntax Large ( returnexp, column, ntop { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) Argument Description returnexp The value you want returned when the large value is found.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Return value The datatype of returnexp. Returns the ntop-largest value if it succeeds and –1 if an error occurs. Usage If you specify range, Large returns the value in returnexp when the value in column is the ntop-largest value in range.
Last Last Description Gets the value in the last row in the specified column. Syntax Last ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) Argument column Description The column for which you want the value of the last row. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. FOR range (optional) The data that will be included when the value in the last row is found.
CHAPTER 24 DataWindow Expression and InfoMaker Functions For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following: • For the Graph presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group.
Left Return value Long. Returns a long whose value is the starting position of the last occurrence of string2 in string1 within the characters specified in searchlength. If string2 is not found in string1 or if searchlength is 0, LastPos returns 0. If any argument’s value is null, LastPos returns null. Usage The LastPos function is case sensitive. The entire target string must be found in the source string.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples This expression returns BABE: Left("BABE RUTH", 4) This expression returns BABE RUTH: Left("BABE RUTH", 40) This expression for a computed field returns the first 40 characters of the text in the column home_address: Left(home_address, 40) See also LeftA Mid Pos Right LeftA Description Obtains a specified number of bytes from the beginning of a string.
LeftTrim LeftTrim Description Removes spaces from the beginning of a string. Syntax LeftTrim ( string ) Argument string Description The string you want returned with leading spaces deleted Return value String. Returns a copy of string with leading spaces deleted if it succeeds and the empty string (“”) if an error occurs.
CHAPTER 24 DataWindow Expression and InfoMaker Functions LenA Description Reports the length of a string in bytes. Syntax LenA ( string ) Argument string Description The string for which you want the length Return value Long. Returns a long containing the length of string in bytes if it succeeds and –1 if an error occurs. Usage LenA replaces the functionality that Len had in DBCS environments in InfoMaker 9. In SBCS environments, Len and LenA return the same results.
LogTen See also Exp LogTen LogTen Description Gets the base 10 logarithm of a number. Syntax LogTen ( n ) Return value Argument Description n The number for which you want the base 10 logarithm. The value of n must not be negative. Double. Returns the base 10 logarithm. Obtaining a number The expression 10^n is the inverse for LogTen(n). To obtain n given number (nbr = LogTen(n)), use n = 10^nbr.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Return value Long. Returns the contents of string as a long if it succeeds and 0 if string is not a valid number. Examples This expression returns 2167899876 as a long: Long("2167899876") LookUpDisplay Description Obtains the display value in the code table associated with the data value in the specified column.
Lower Lower Description Converts all the characters in a string to lowercase. Syntax Lower ( string ) Argument string Description The string you want to convert to lowercase letters Return value String. Returns string with uppercase letters changed to lowercase if it succeeds and the empty string (“”) if an error occurs.
CHAPTER 24 DataWindow Expression and InfoMaker Functions The following tables explain the meaning and use of these metacharacters: Metacharacter Meaning Example Caret (^) Matches the beginning of a string Matches the end of a string Matches any character ^C matches C at the beginning of a string. s$ matches s at the end of a string. . . . matches three consecutive characters. \$ matches $. Dollar sign ($) Period (.
Max Sample patterns The following table shows various text patterns and sample text that matches each pattern: This pattern AB Matches Any string that contains AB, such as ABA, DEABC, graphAB_one. B* Any string that contains 0 or more Bs, such as AC, B, BB, BBB, ABBBC, and so on. Since B* used alone matches any string, you would not use it alone, but notice its use in some the following examples. Any string containing the pattern AC or ABC or ABBC, and so on (0 or more Bs).
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description column The column for which you want the maximum value. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric. The data that will be included when the maximum value is found.
Median • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies. Null values are ignored and are not considered in determining the maximum. Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description column The column for which you want the median of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric. The data that will be included in the median.
Median Settings for Rows include the following: • For the Graph presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies. In calculating the median, null values are ignored. Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Assuming a report displays the order number, amount, and line items for each order, this computed field returns the median of the order amount for the distinct order numbers: Median(order_amt for all DISTINCT order_nbr) See also Avg Mode Mid Description Obtains a specified number of characters from a specified position in a string.
MidA To pass this validation rule, the fourth character in the column password must be 6: Mid(password, 4, 1) = "6" MidA Description Obtains a specified number of bytes from a specified position in a string. Syntax MidA ( string, start {, length } ) Argument string Description The string from which you want characters returned. start A long specifying the position of the first byte you want returned (the position of the first byte of the string is 1).
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description column The column for which you want the minimum value. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric. The data that will be included in the minimum. For most presentation styles, values for range are: FOR range (optional) • ALL – (Default) The minimum of all values in column.
Min For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include: • For the Graph presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Minute Description Obtains the number of minutes in the minutes portion of a time value. Syntax Minute ( time ) Argument time Description The time value from which you want the minutes Return value Integer. Returns the minutes portion of time (00 to 59). Examples This expression returns 1: Minute(19:01:31) See also Hour Second Mod Description Obtains the remainder (modulus) of a division operation.
Mode Mode Description Calculates the mode of the values of the column. The mode is the most frequently occurring value. Syntax Mode ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) Argument column Description The column for which you want the mode of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
CHAPTER 24 DataWindow Expression and InfoMaker Functions For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include: • For the Graph or OLEpresentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group.
Month Assuming a report displays the order number, amount, and line items for each order, this computed field returns the mode of the order amount for the distinct order numbers: Mode(order_amt for all DISTINCT order_nbr) See also Avg Median Month Description Gets the month of a date value. Syntax Month ( date ) Argument Description date The date from which you want the month Return value Integer. Returns an integer (1 to 12) whose value is the month portion of date.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Return value Time. Returns the current time based on the system time of the client machine. Usage Use Now to compare a time to the system time or to display the system time on the screen. The timer interval specified for the form or report determines the frequency at which the value of Now is updated. For example, if the timer interval is one second, it is updated every second. The default timer interval is one minute (60,000 milliseconds).
Page Page Description Gets the number of the current page. Syntax Page ( ) Return value Long. Returns the number of the current page. Calculating the page count The vertical size of the paper less the top and bottom margins is used to calculate the page count. When the print orientation is landscape, the vertical size of the paper is the shorter dimension.
CHAPTER 24 DataWindow Expression and InfoMaker Functions This example obtains the absolute page number for the first row on the page in the string variable ret: string ret, row row = dw1.Object.DataWindow.FirstRowOnPage ret = dw1.Describe("Evaluate('pageabs()', "+row+")") See also Page PageCount PageCountAcross PageAcross Description Gets the number of the current horizontal page.
PageCountAcross Usage PageCount applies to Print Preview. Calculating the page count The vertical size of the paper less the top and bottom margins is used to calculate the page count. When the print orientation is landscape, the vertical size of the paper is the shorter dimension.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Percent Description Gets the percentage that the current value represents of the total of the values in the column. Syntax Percent ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) Argument column Description The column for which you want the value of each row expressed as a percentage of the total of the values of the column. Column can be the column name or the column number preceded by a pound sign (#).
Percent Usage Usually you use Percent in a column to display the percentage for each row. You can also use Percent in a header or trailer for a group. In the header, Percent displays the percentage for the first value in the group, and in the trailer, for the last value in the group. If you specify range, Percent returns the percentage that the current row of column represents relative to the total value of range.
CHAPTER 24 DataWindow Expression and InfoMaker Functions This expression returns the value of each row in the column named cost as a percentage of the total of cost in group 2: Percent(cost for group 2) This expression entered in the Value box on the Data tab page in the Graph Object property sheet returns the value of each row in the qty_ordered as a percentage of the total for the column in the graph: Percent(qty_ordered for graph) Assuming a report displays the order number, amount, and line items for
Pos Pos Description Finds one string within another string. Syntax Pos ( string1, string2 {, start } ) Argument string1 Description The string in which you want to find string2. string2 start (optional) The string you want to find in string1. A long indicating where the search will begin in string. The default is 1. Return value Long. Returns a long whose value is the starting position of the first occurrence of string2 in string1 after the position specified in start.
CHAPTER 24 DataWindow Expression and InfoMaker Functions PosA Description Finds one string within another string. Syntax PosA ( string1, string2 {, start } ) Argument string1 Description The string in which you want to find string2. string2 start (optional) The string you want to find in string1. A long indicating the position in bytes where the search will begin in string. The default is 1. Return value Long.
ProfileInt Argument Description key A string specifying the setting name in section whose value you want. The setting name is followed by an equal sign in the file. Do not include the equal sign in key. Key is not case sensitive. An integer value that ProfileInt returns if filename is not found, if section or key does not exist in filename, or if the value of key cannot be converted to an integer. default Return value Integer.
CHAPTER 24 DataWindow Expression and InfoMaker Functions ProfileString Description Obtains the string value of a setting in the specified profile file. Syntax ProfileString ( filename, section, key, default ) Argument filename section key default Description A string whose value is the name of the profile file. If you do not specify a full path, ProfileString uses the operating system’s standard file search order to find the file.
Rand Rand Description Obtains a random whole number between 1 and a specified upper limit. Syntax Rand ( n ) Argument n Description The upper limit of the range of random numbers you want returned. The lower limit is always 1. The upper limit cannot exceed 32,767. Return value A numeric datatype, the datatype of n. Returns a random whole number between 1 and n. Usage The sequence of numbers generated by repeated calls to the Rand function is a computer-generated pseudorandom sequence.
CHAPTER 24 DataWindow Expression and InfoMaker Functions RelativeDate Description Obtains the date that occurs a specified number of days after or before another date. Syntax RelativeDate ( date, n ) Argument date Description A date value n An integer indicating the number of days Return value Date. Returns the date that occurs n days after date if n is greater than 0. Returns the date that occurs n days before date if n is less than 0.
Replace Replace Description Replaces a portion of one string with another. Syntax Replace ( string1, start, n, string2 ) Argument string1 Description The string in which you want to replace characters with string2. start A long whose value is the number of the first character you want replaced. (The first character in the string is number 1.) A long whose value is the number of characters you want to replace. n string2 The string that replaces characters in string1.
CHAPTER 24 DataWindow Expression and InfoMaker Functions ReplaceA Description Replaces a portion of one string with another. Syntax ReplaceA ( string1, start, n, string2 ) Argument string1 Description The string in which you want to replace bytes with string2. start A long whose value is the number of the first byte you want replaced. (The first byte in the string is number 1.) A long whose value is the number of bytes you want to replace. n string2 The string that replaces bytes in string1.
RGB Usage The formula for combining the colors is: Red + (256 * Green) + (65536 * Blue) Use RGB to obtain the long value required to set the color for text and drawing objects. You can also set an object’s color to the long value that represents the color. The RGB function provides an easy way to calculate that value. Determining color components The value of a component color is an integer between 0 and 255 that represents the amount of the component that is required to create the color you want.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Right Description Obtains a specified number of characters from the end of a string. Syntax Right ( string, n ) Return value Argument string Description The string from which you want characters returned n A long whose value is the number of characters you want returned from the right end of string String. Returns the rightmost n characters in string if it succeeds and the empty string (“”) if an error occurs.
RightTrim Usage RightA replaces the functionality that Right had in DBCS environments in InfoMaker 9. In SBCS environments, Right and RightA return the same results. See also LeftA MidA PosA Right RightTrim Description Removes spaces from the end of a string. Syntax RightTrim ( string ) Argument string Description The string you want returned with trailing blanks deleted Return value String.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples This expression returns 9.62: Round(9.624, 2) This expression returns 9.63: Round(9.625, 2) This expression returns 9.600: Round(9.6, 3) This expression returns -9.63: Round(-9.625, 2) This expression returns -10: Round(-9.625, -1) See also Ceiling Int Truncate RowCount Description Obtains the number of rows that are currently available in the primary buffer. Syntax RowCount ( ) Return value Long.
Second Usage When you call RowHeight in a band other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes. When a band has Autosize Height set to true, you should avoid using the RowHeight DataWindow expression function to set the height of any element in the row. Doing so can result in a logical inconsistency between the height of the row and the height of the element.
CHAPTER 24 DataWindow Expression and InfoMaker Functions SecondsAfter Description Gets the number of seconds one time occurs after another. Syntax SecondsAfter ( time1, time2 ) Argument time1 Description A time value that is the start time of the interval being measured time2 A time value that is the end time of the interval Return value Long. Returns the number of seconds time2 occurs after time1. If time2 occurs before time1, SecondsAfter returns a negative number.
Sin Sin Description Calculates the sine of an angle. Syntax Sin ( n ) Argument n Description The angle (in radians) for which you want the sine Return value Double. Returns the sine of n if it succeeds and –1 if an error occurs. Examples This expression returns .
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description nbottom The relationship of the small value to the column’s smallest value. For example, when nbottom is 2, Small finds the second-smallest value. The data that will be included when finding the small value. For most presentation styles, values for range are: FOR range (optional) • ALL – (Default) The small value of all rows in column. • GROUP n – The small value of rows in column in the specified group.
Space Settings for Rows include the following: • For the Graph or OLE presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies. Min might be faster If you do not need a return value from another column and you want to find the smallest value (nbottom = 1), use Min; it is faster.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples This expression for a computed field returns 10 spaces in the computed field if the value of the rating column is Top Secret; otherwise, it returns the value in rating: If(rating = "Top Secret", Space(10), rating) See also Fill Sqrt Description Calculates the square root of a number. Syntax Sqrt ( n ) Argument n Description The number for which you want the square root Return value Double. Returns the square root of n.
StDev StDev Description Calculates an estimate of the standard deviation for the specified column. Standard deviation is a measurement of how widely values vary from average. Syntax StDev ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) Argument column Description The column for which you want an estimate for the standard deviation of the values in the rows. Column can be the column name or the column number preceded by a pound sign (#).
CHAPTER 24 DataWindow Expression and InfoMaker Functions For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data tab page (the Range property), and the aggregation function uses that range. Settings for Rows include the following: • For the Graph or OLE presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group.
StDevP This expression for a computed field in a crosstab returns the estimate for standard deviation of the values in the qty_ordered column in the crosstab: StDev(qty_ordered for crosstab) Assuming a report displays the order number, amount, and line items for each order, this computed field returns the estimated standard deviation of the order amount for the distinct order numbers: StDev(order_amt for all DISTINCT order_nbr) See also StDevP Var StDevP Description Calculates the standard deviation fo
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description DISTINCT (optional) Causes StDevP to consider only the distinct values in column when determining the standard deviation. For a value of column, the first row found with the value is used and other rows that have the same value are ignored. One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression. expresn (optional) Return value Double.
String This expression returns the standard deviation of the values in group 1 in the column named salary: StDevP(salary for group 1) This expression returns the standard deviation of the values in column 4 on the page: StDevP(#4 for page) This expression entered in the Value box on the Data tab page in the graph’s property sheet returns the standard deviation of the values in the qty_ordered column in the graph: StDevP(qty_ordered for graph) This expression for a computed field in a crosstab returns th
CHAPTER 24 DataWindow Expression and InfoMaker Functions Syntax String ( data {, format } ) Argument data format (optional) Description The data you want returned as a string with the specified formatting. Data can have a date, DateTime, numeric, time, or string datatype. A string of the display masks you want to use to format the data. The masks consist of formatting information specific to the datatype of data. If data is type string, format is required.
StripRTF String(2005-01-31 06:08:00, 'mmm dd, yyyy, h "hrs and" m "min"') This expression: String(nbr, "0000;(000);****;empty") returns: 0123 if nbr is 123 (123) if nbr is -123 **** if nbr is 0 empty if nbr is null This expression returns A-B-C: String("ABC", "@-@-@") This expression returns A*B: String("ABC", "@*@") This expression returns ABC: String("ABC", "@@@") This expression returns a space: String("ABC", " ") This expression returns 6 hrs and 8 min: String(06:08:02,'h "hrs and" m "min"') Thi
CHAPTER 24 DataWindow Expression and InfoMaker Functions Examples Argument Description string The column to be stripped of rich text formatting. This expression is used in a compute field expression to remove the formatting from a rich text edit column and display plain text in the compute field. StripRTF(rte_description) Sum Description Calculates the sum of the values in the specified column. Syntax Sum ( column { FOR range { DISTINCT { expres1 {, expres2 {, ...
Sum FOR range (optional) The data to be included in the sum. For most presentation styles, values for range are: • ALL – (Default) The sum of all values in column. • GROUP n – The sum of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1. • PAGE – The sum of the values in column on a page. For Crosstabs, specify CROSSTAB for range: • CROSSTAB – (Crosstabs only) The sum of all values in column in the crosstab.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a report always retrieves all rows.
Time Pi Sin Time Description Converts a string to a time datatype. Syntax Time ( string ) Argument string Description A string containing a valid time (such as 8 AM or 10:25) that you want returned as a time datatype. Only the hour is required; you do not have to include the minutes, seconds, or microseconds of the time or AM or PM. The default value for minutes and seconds is 00 and for microseconds is 000000. AM or PM is determined automatically. Return value Time.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Today Description Obtains the system date and time. Syntax Today ( ) Return value DateTime. Returns the current system date and time. Usage To display both the date and the time, a computed field must have a display format that includes the time.
Truncate Argument Description x n The number you want to truncate. The number of decimal places to which you want to truncate x. Valid values are 0 through 28. Return value The datatype of x. If n is positive, returns x truncated to the specified number of decimal places. If n is negative, returns x truncated to (- n +1) places before the decimal point. Returns –1 if it fails. Examples This expression returns 9.2: Truncate(9.22, 1) This expression returns 9.2: Truncate(9.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Upper Description Converts all characters in a string to uppercase letters. Syntax Upper ( string ) Argument string Description The string you want to convert to uppercase letters Return value String. Returns string with lowercase letters changed to uppercase if it succeeds and the empty string (“”) if an error occurs.
Var Argument Description FOR range (optional) The data to be included in the estimate of the variance. For most presentation styles, values for range are: • ALL – (Default) The estimate of the variance for all rows in column. • GROUP n – The estimate of the variance for rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1. • PAGE – The estimate of the variance for the rows in column on a page.
CHAPTER 24 DataWindow Expression and InfoMaker Functions Settings for Rows include the following: • For the Graph presentation style, Rows is always All. • For Graph controls, Rows can be All, Page, or Group. • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies. Estimating variance or calculating actual variance Var assumes that the values in column are a sample of the values in rows in the column in the database table.
VarP Assuming a report displays the order number, amount, and line items for each order, this computed field returns the estimate for the variance of the order amount for the distinct order numbers: Var(order_amt for all DISTINCT order_nbr) See also StDev VarP VarP Description Calculates the variance for the specified column. The variance is the square of the standard deviation. Syntax VarP ( column { FOR range { DISTINCT { expres1 {, expres2 {, ...
CHAPTER 24 DataWindow Expression and InfoMaker Functions Argument Description expresn (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression. Return value Double or decimal if the arguments are decimal. Returns the variance for column. If you specify group, Var returns the variance for column within range. Usage If you specify range, VarP returns the variance for column within range.
WordCap This expression returns the variance of the values in group 1 in the column named salary: VarP(salary for group 1) This expression returns the variance of the values in column 4 on the page: VarP(#4 for page) This expression entered in the Value box on the Data property page in the graph’s property sheet returns the variance of the values in the quantity column in the graph: VarP(quantity for graph) This expression for a computed field in a crosstab returns the variance of the values in the quan
CHAPTER 24 DataWindow Expression and InfoMaker Functions This expression concatenates the characters in the emp_fname and emp_lname columns and makes the first letter of each word uppercase: WordCap(emp_fname + " " + emp_lname) Year Description Gets the year of a date value. Syntax Year ( date ) Argument date Return value Description The date value from which you want the year Integer.
Year 762 InfoMaker
PART 7 Appendixes This part contains descriptions of the rules for identifiers in InfoMaker and the extended attribute system tables.
A P PE N DI X A About this chapter Identifiers You use identifiers to name objects. This chapter describes valid identifiers.
Reserved words Examples Here are some valid identifiers: first_quarter_summary EMPLOYEE_LABELS EmployeeSalarySummary Employee_by_# Here are some invalid identifiers: 2nd-quarter // Does not start with a letter emp list // Contains a space Employee’sInfo // Contains an invalid character Reserved words You cannot use the following reserved words as identifiers, because InfoMaker uses them internally: Table 24-2: Reserved words alias and autoinstantiate call case catch choose close commit connect constant
A P PE N DI X B About this appendix Contents The Extended Attribute System Tables This appendix describes each column in the extended attribute system tables.
The extended attribute system tables Caution You should not change the values in the extended attribute system tables. InfoMaker maintains this information automatically whenever you change information for a table or column in the Database painter. The extended attribute system tables This section lists and describes all of the columns in each of the extended attribute system tables.
APPENDIX B The Extended Attribute System Tables Column Column name Description 21 22 pbl_funl 23 pbl_fptc Labels font Underline (Y=Yes, N=No) Labels font character set (0=ANSI, 2=Symbol, 255=OEM) Labels font pitch and family (see note) 24 25 pbl_ffce pbl_fchr pbt_cmnt Labels font typeface Table comments About font pitch and family Font pitch and family is a number obtained by adding together two constants: Pitch: 0=Default, 1=Fixed, 2=Variable Family: 0=No Preference, 16=Roman, 32=Swiss, 48=
The extended attribute system tables Table B-4: The PBCatFmt table Column 1 Column name pbf_name Description Display format name 2 3 pbf_frmt pbf_type Display format Datatype to which format applies 4 pbf_cntr Concurrent-usage flag Table B-5: The PBCatVld table Column 1 Column name 2 3 pbv_vald pbv_type Validation rule Datatype to which validation rule applies 4 pbv_cntr Concurrent-usage flag 5 pbv_msg Validation error message pbv_name Description Validation rule name Table B-6: The P
APPENDIX B The Extended Attribute System Tables Edit style types for the PBCatEdt table Table B-7 shows the edit style types available for the PBCatEdt table. Table B-7: Edit style types for the PBCatEdt table Edit style type pbe_type value (column 3) CheckBox RadioButton 85 86 DropDownListBox DropDownDataWindow 87 88 Edit Edit Mask 89 90 CheckBox edit style (code 85) Table B-8 shows a sample row in the PBCatEdt table for a CheckBox edit style.
Edit style types for the PBCatEdt table Value Meaning Flag 32-bit flag. Low-order four hex digits are generic edit type; high-order four are styles within the type. A 1 in any bit indicates the corresponding style is checked. A 0 in any bit indicates the corresponding style is unchecked.
APPENDIX B The Extended Attribute System Tables Value Meaning Flag 32-bit flag. Low-order four hex digits are generic edit type; high-order four are styles within the type. A 1 in any bit indicates the corresponding style is checked. A 0 in any bit indicates the corresponding style is unchecked.
Edit style types for the PBCatEdt table Value Meaning Flag 32-bit flag. Low-order four hex digits are generic edit type; high-order four are styles within the type. A 1 in any bit indicates the corresponding style is checked. A 0 in any bit indicates the corresponding style is unchecked.
APPENDIX B The Extended Attribute System Tables Value Meaning Limit Key Character representation (in decimal) of Limit value. One-character accelerator key. Width% Flag Width of the dropdown part of the DropDownDataWindow in %. 32-bit flag. Low-order four hex digits are generic edit type; highorder four are styles within the type. A 1 in any bit indicates the corresponding style is checked. A 0 in any bit indicates the corresponding style is unchecked.
Edit style types for the PBCatEdt table Table B-16: Sample row in PBCatEdt for an Edit edit style Name MyEdit Edit Limit Type 89 Cntr 1 Seqn 1 Flag Flag Work Key MyEdit MyEdit Format Display1 89 89 1 1 2 3 0 0 Focus MyEdit MyEdit Data1 Display2 89 89 1 1 4 5 0 0 MyEdit Data2 89 1 6 0 Table B-17: Values used in Edit edit style sample Value Limit Meaning Character representation (in decimal) of Limit value. Key One-character accelerator key. Format Focus Display format mask.
APPENDIX B The Extended Attribute System Tables Edit Mask edit style (code 90) Table B-18 shows a sample row in the PBCatEdt table for an EditMask edit style. Table B-19 shows the meaning of the values in Table B-18. About the example This example shows an Edit Mask edit style using a code table of display and data values as part of a spin control. Rows 2 and beyond exist in PBCatEdt only if the edit mask is defined as a spin control (bit 29 of Flag is 1).
Edit style types for the PBCatEdt table Value Meaning Range Character representation (in decimal) of spin control range. The min value and max value are tab-delimited. Example: SpinInc "1[tab]13" means min = 1, max = 13 Character representation (in decimal) of spin increment. Display1 Data1 Display value for first entry in code table. Data value for first entry in code table. Display2 Data2 Display value for second entry in code table. Data value for second entry in code table.
Index Symbols * (multiplication) 619 + (addition) 619 + operator 240, 564 / (division) 619 = (relational) 620 @ used in crosstabs 439 used in validation rules ^ (exponentiation) 619 291 Numerics 24-hour times 269 A Abs function 641 absolute value 641 accelerator keys and CheckBox edit style 276 and RadioButton edit style 277 ACos function 641 actions assigning to command buttons in forms 566 in forms 508, 527 ActiveX controls adding to a report 491 deploying 610 activity log 82 Activity Log view 78 usi
Index applications about 575 creating 576 defining toolbars 581 distributing 576 executing pipelines 588 identifying in Help>About 585 initialization files for 582 modifying commit value 592 modifying maximum errors value 592 modifying pipeline definition 590 modifying pipeline type 592 piping extended attributes 593 prototyping 581 repairing pipeline execution errors 590 reusing most recent 582 running 576, 583 specifying icon for 579 using pipelines 587 using query governor 587 arc cosine 641 arc sine 64
Index BMP files adding to forms 565 adding to reports 238 in rich text 480 books, online 40 boolean expressions in filters 298 in validation rules 290, 293 border property 327 borders around controls in forms 557 defaults in forms 544 in forms 545 in reports 226 Borders dropdown toolbar in Form painter 541 in Report painter 196 brackets in text patterns 703 breaks, in grouped reports 302 brush.color property about 329 specifying colors 343 brush.
Index columns adding to data source for forms 560 adding to forms 561 adding to reports 235 appending to table 90 applying display formats to 261 applying edit styles to 273 average value 644 checking for null value 688 counting null values, example 631 cumulative percent 668 cumulative sum 670 defining display formats 261, 262 defining edit styles 272 defining validation rules 289, 292 display value 701 displaying as a drop-down DataWindow 281 displaying as check boxes 276 displaying as drop-down lists 27
Index conditional expressions example 633, 634, 638 conditional expressions, IF function 685 conditional modification example, gray bar 320 example, highlighting rows 322 example, rotating controls 321 example, size and location 324 in forms 570 modifying controls 319 configuration settings, reading 725, 727 configuring ODBC 600 continuous data, graphing 404 Control List view 198 control names in the Report painter 226 Controls dropdown toolbar 541 controls in DataWindow objects moving 248 controls in forms
Index CUR files selecting mouse pointers 220 CUR files, selecting mouse pointers currency display format 263 currency, and rows 683 current library PBL file 55, 56 setting 14 when opening InfoMaker 56 custom colors 25, 554 Customize dialog box 31 555 D data accessing in freeform form 509 accessing in grid form 510 accessing in master/detail many-to-one form accessing in master/detail one-to-many form associating with graphs in reports 412 caching in InfoMaker 201 changing 109, 202 converting to type long
Index specifying fonts in tables 87 using in graphs 413 database administration database access 119 executing SQL 115 painting SQL 115 security 119 Database Blob Object dialog box 499 Database painter changing colors in 81 creating tables 84 defining display formats 261 defining validation rules 289 dragging and dropping 79 previewing data 108 specifying extended attributes 88 tasks 79 views 78 working with edit styles 272 workspace 78 Database painter, validation rules 617 database profiles in pipelines 12
Index defining primary keys 100 executing SQL statements 118 exporting table syntax 95 exporting view syntax 108 generating SQL statement 108 specifying an outer join 107 stored procedures 182 supported 77 Dec function 677 decimal, converting to 677 default buttons in forms 567 default layouts in views 24 defaults colors and borders in forms 544 for reports 216 in forms 522 Delete ASA Database utility 84 Delete Library dialog box 66 DELETE statements building in Database painter 117 specifying WHERE clause
Index defining code tables with 285 Edit Mask edit style defining 278 defining code tables with 285 spin controls 280 edit style properties 273 edit styles about 270 and selection criteria 160 applying to columns 273 deleting 294 in databases 88 in forms 571 in reports 258 in Specify Retrieval Criteria dialog box 233, 559 maintaining 294 working with in Database painter 272 working with in Report painter 274 elevation, in 3D graphs 422 encoding declaration 375 Equality Required property in forms 559 in repo
Index Fill function 679 FillA function 680 filters functions in expressions for 629 in Data Manipulation view 112 removing 299 First function 681 focus, moving from column to column font.escapement property 331 font.height property 332 font.italic property 333 font.strikethrough property 334 font.underline property 335 font.
Index running 523 running in executable 586 saving 522 saving data in 526 selecting rows when running 558 setting borders 545 setting colors 545, 553 tab order 556 text, adding 561 titles in 553 U.S.
Index grid lines, graph 429 Grid style basic properties 219 detail band in 195 displaying grid lines 219 of reports 146 reordering columns 207 resizing columns 207 working in 207 group box, adding to reports 238 GROUP BY criteria 178 group headers, in XML 384 Group presentation style properties of 305, 459 using 304 grouping in SQL Select 178 restricting 179 groups in reports of rows 302 sorting 312 H HAVING criteria 179 header band, in Report painter 194 header section in XML template 373 heading extende
Index Month 716 Now 716 Number 717 Page 718 PageAcross 719 PageCount 719 PageCountAcross 720 Percent 721 Pi 723 Pos 724, 725 ProfileInt 725 ProfileString 727 Rand 728 Real 728 RelativeDate 729 RelativeTime 729 Replace 730, 731 RGB 731 Right 733 RightTrim 734 Round 734 RowCount 735 RowHeight 735 Second 736 SecondsAfter 737 Sign 737 Sin 738 Small 738 Space 740 Sqrt 741 StDev 742 StDevP 744 String 746 StripRTF 748 Sum 749 Tan 751 Time 752 Today 753 Trim 753 Truncate 753 Upper 755 Var 755 VarP 758 WordCap 760 Y
Index Join dialog box 107 joins number of tables in 48, 50 joins, in Select painter 170 JPEG files adding to forms 565 adding to reports 238 JRE, required for deployment 605 K key and modified columns, updating rows 535 key and updatable columns, updating rows 535 key columns for OLE columns 498 updating rows 535 key modification, updating rows 537 keyboard shortcuts customizing 42 resetting 44 keys, database arrows specifying key relationship 157 displaying in Database painter 98 dropping from tables 101
Index sorting 59 using drag and drop 59 views 58 what you can do in 55 workspace 58 LIKE operator 620 in expressions 620 LIKE operator, in Quick Select 161 limit 649 line drawing controls 237, 569 line graphs about 404 making three-dimensional 406 line styles, graph 429 List view about 58 sorting 59 using drag and drop 59 local SQL Anywhere databases 83 localized deployment files 598 log files about 82 saving 82 Log function 699 logarithms 699, 700 logging exporting table syntax 95 exporting view syntax 108
Index N Name column, sorting 59 name tags 148 names of columns in reports 195 of controls in reports 226 of executable files 577 of forms 523 of queries 189 of reports 187 naming conventions for forms 523 for queries 189 for reports 187 negative numbers 737 nested reports adding another report 357 adding to report (DataWindow) 351 adjusting width 355 autosize height 360 changing 356 changing definition of 356 displayed in Design view 352 how retrieval works 348 limitations 349 slide options 361 specifying
Index detail band in 195 of reports 148 O Object Details view 78 Object Layout view 78 objects accessing recently opened 18 creating new 16 opening 17 previewing 18 regenerating 67 running 18 selecting 60 Objects dropdown toolbar, in Report painter 196 Objects view 78 OCX see ActiveX control ODBC driver 600 OLE columns in reports 498 presentation style 487 previewing columns 501 report objects 485 OLE custom control see ActiveX control OLE Database Blob command 499 OLE object activating object 495 display
Index using views 19 working in 18 painting SQL statements 115 panes adding 23 docking 22 floating 22 in views 20 moving 21 removing 23 resizing 21 title bar, displaying and using 20 paragraph alignment 477 parsing strings 696, 697, 724, 725 passwords displaying as asterisks 274 fields 274 pasting controls in forms 548 SQL statements in Database painter 116 pattern matching 702 PBCatCol system table 96, 769 PBCatEdt system table 96, 770 PBCatFmt system table 96, 770 PBCatTbl system table 96, 768 PBCatVld s
Index placeholders, in validation rules 291 plus sign in text patterns 703 point of view, in 3D graphs 422 pointer property 339 pointers in forms 555 in graphs 429 in reports 220 points, specifying size for tables 87 pop-up menus controlling toolbars with 30 in Form painter 542 in Library painter 60 using 24 Pos function 724 PosA function 725 position changing graph’s 410 positive numbers 737 PowerBar about 29 adding custom buttons to 33 controlling display of 30 displaying available buttons 32 using 13 Pow
Index brush.hatch 329 color 330 font.escapement 331 font.height 332, 336 font.italic 333 font.strikethrough 334 font.underline 335 font.weight 335 format 336 pen.color 337 pen.style 337 pen.
Index MicroHelp 199 moving controls 248 opening 63 resizing bands 199 resizing controls 249 retrieving data 200 saving data 208 selecting controls 197 sliding controls 251 toolbars in 196 undoing changes 199 using the Properties view 197 working in 192 working with edit styles 274 zooming 199 report wizards about 37 list of, for presentation styles 37 reports OLE see reports, OLE about the extended attribute system tables 185 adding controls 235 adding to forms 568 aligning controls 249 and graphs in 408 bl
Index text, adding 236 U.S.
Index selecting 690 selecting when running forms 558 sorting 110, 112, 299 sorting in SQL Select 177 suppressing repeating values 301 using forms to insert 521 Rows to Disk command 234 RTF 471 rulers, in Report painter 205, 247 Run/Preview dialog box 18 running forms 523 S Save As dialog box 208 Save Rows As dialog box 114 saving data in external files 208, 526 data in HTML Table format 213, 526 data in reports 230 forms 522 layouts in views 24 pipelines 122, 138 queries 189 reports 187 scatter graphs 405
Index sliding in forms 551 in reports 251 Slide dropdown toolbar, in Report painter 196 used in nested reports 361 Small function 738 snaking columns, in reports 223 sort criteria, specifying in Quick Select 159 sorting groups 312 in graphs 423 in SQL Select 177 rows 299 sorting Name column in Library painter 59 sources of data 155 Space function 740 space, in libraries 67 spaces deleting leading 698 deleting trailing 734 inserting in a string 740 removing from strings 753 spacing equalizing in forms 550 e
Index Lower 702 Match 702 Mid 709 MidA 710 Pos 724 PosA 725 Replace 730 ReplaceA 731 Right 733 RightA 733 RightTrim 734 Space 740 Trim 753 Upper 755 WordCap 760 strings comparing 622 concatenating 624 converting 672, 700, 717, 728 deleting leading spaces 698 detecting contents 687, 689, 691 extracting 709, 710 finding substrings 724, 725 lowercase 702 uppercase 755 StripRTF function 748 style libraries 47 StyleBar about 29 controlling display of 30 in Form painter 540, 541 in Report painter 196 styles, of r
Index rows, number retrieved 48 saving data in external files 114 selecting for SQL Select 156, 165 specifying extended attributes 89 specifying fonts 87 specifying number in joins 50 specifying updatable 533 temporary 96 working with data 109 Tabular style detail band in 195 of reports 145 Tan function 751 tangent 751 target data for OLE 493 temporary tables, ASE 96 text cutting, copying, and pasting 92 editing 44 finding substrings 724, 725 in forms 555, 561 in reports 225, 236 inserting newline characte
Index icons in the Design view 464 properties 465 tree node icons 465 Trim function 753 Truncate function 753 truth table for boolean expressions TUTOR_IM.
Index WordCap function 760 workspace in Data Pipeline painter 125, 127 in Database painter 78 in Library painter 58 wrap height, default in freeform reports Z zero display format 265 zero, determining 737 Zoom command, in print preview 205 185 X x property 341 x1, x2 property 342 XML about 363 associating a namespace with a schema 390 Detail Start element 372 exporting 382 exporting metadata 387 header and detail sections 371 importing 391 importing group headers 393 importing with a template 391 impor
