Using Power Painter

 

Describes how to use the Power Painter to create reports, graphs, and page layouts.

Power Painter is a Web layout and report creation tool (available with DB2® Web Query), built using AJAX technology, that enables you to create output formats and page layout formats. It combines reporting, graphs, and page layout design in a single tool.

 

Parent topic:

IBM DB2 Web Query for System i Overview

 

Functionality of Power Painter

You can accomplish the following tasks and functions using the Power Painter tool:

 

Selecting and Adding a Data Source

You may use Power Painter to access a Data Source to build reports and perform ad hoc analysis. You can open and work with Master Files by using the Data Sources palette in Power Painter.

 

Open a Data Source

  1. Upon opening the Power Painter tool, you are prompted to select a Data Source. Select a Master File and click OK.

    The fields from the Data Source are available in the Data Sources palette.

    This step is optional. You may open or close Data Sources in Power Painter at any time.

  2. To open a new Data Source, click the Open a new data source

    button from the Data Sources palette toolbar.

    The Open dialog box appears prompting you to select a Data Source.

 

Close a Data Source

If there is a component on the canvas that is referencing data from an open Data Source, you will not be able to close the Data Source unless you delete the associated component.

To close an open Data Source, click the Close current data source

button from the Data Sources palette toolbar.

The fields are removed from the Data Source palette.

 

Using Power Painter Options and Palettes

The following image is an example of the Power Painter tool with multiple components. Several options and palettes enable you to customize the layout for the Power Painter tool.

The following options are available in Power Painter:

Menu bar

Standard browser menu bar options such as File, View, Tools, and so on.

Toolbar

Power Painter toolbar options such as Layout, Run, View, Alignment, Palettes, and so on.

Canvas

The canvas where you drop objects and paint the reporting document. The reporting document can be made up of text elements, images, tabular report objects, charts, and lines.

The following palettes are available in Power Painter:

Toolbox palette

Contains the tabular report, chart, text, image, and line objects for painting your document.

Query palette

Enables you to build and customize queries for tabular reports and charts.

Data Sources palette

Contains your Data Source information.

Properties palette

Contains the property settings for the components in the canvas.

 

Using Palettes in the Canvas

Upon opening the Power Painter tool, the canvas appears with all of the palettes minimized and pinned in the Power Painter margins. You can show, hide, or move these palettes as needed. Power Painter retains these settings.

Power Painter is able to store information so that the options you set in a single session are remembered and applied the next time you use the tool. Examples of the settings that can be saved are how the palettes are displayed, the theme selected, field display options, and so on. The information for these settings is stored under each user's directory in the DB2 Web Query repository.

Reusing stored settings is only available if you access the same DB2 Web Query environment.

Palettes in Power Painter:

 

Dock and UnDock a Palette

  1. Required: Double-click a palette to dock it on the canvas.

    or

    Drag a palette within 10 pixels of the edge (top, bottom, left, right) of the canvas.

    A palette is docked when both the pin and close button are available from the palette toolbar.

  2. To undock a palette (float it), click the palette toolbar, left-click and hold your mouse, and drag the palette to a new location on the canvas.

 

Stack Palettes

Drag and dock multiple palettes to one side of the canvas to create vertical or stacked palettes. You may adjust the height/width of each palette in the docking area.

Resizing any palette automatically resizes the rest of the palettes so that they can fit into the docking area.

The following image is an example of vertically stacked palettes in the Power Painter tool.

 

Pin a Palette

  1. Required: Click the pin

    button located on the toolbar of an open palette.

    The palette is pinned and minimized.

  2. You may perform the following actions with pinned palettes:

    • Hover your mouse over the palette to show its contents.

    • Hover your mouse out of the palette to minimize it again.

    • Double-click a palette to dock it on the canvas.

 

Show a Palette

  1. Select Palettes from the View menu.

    Palettes that are checked are shown in the canvas.

  2. Select an unchecked palette name.

    The palette is shown maximized on the canvas. You may move or resize the palette as needed.

 

Close a Palette

  1. Required: Select Palettes from the View menu.

  2. Required: Select a checked palette name from the list.

    The palette is closed and removed from the canvas.

You may also close an open palette by clicking the Close

button from the palette toolbar.

 

Reset the Power Painter Layout

From the View menu, select Reset Layout from the Palettes submenu.

The Power Painter layout resets with the default palettes shown (Toolbox, Properties, Data Sources) in the canvas.

 

Using Power Painter Components

The available Power Painter components are Report, Graph, Image, Text, and Line. You may select these components from the Toolbox palette or from the Main toolbar. The component objects are associated with corresponding properties that you may set using the properties palette.

The following image is an example of the Toolbox palette in Power Painter.

 

Insert a Report Component in Power Painter

  1. Required: Select the Report button from the Toolbox palette and drag it onto the canvas.

    or

    Select Report from the Insert menu. The component is automatically inserted into the canvas.

    The report appears on the canvas.

  2. From the Data Sources palette, select fields and drag them to the report object.

    As fields are dragged over the report, sorting folders are available which enable you to sort the fields as a By, Across, Sum, or Coordinated report field.

    Coordinated fields contain a common sort field for multiple reports and graphs and are burst into separate page layouts. Each value for the coordinated sort field displays on a separate page.

  3. Required: To edit or delete these field selections, open the Query palette or the Query Popup.

    • From the View menu, select Query from the Palettes submenu.

    • Select a report, right-click and select Query Popup from the context menu.

      Double-click the report to bring up the Query Popup.

    The sort folders appear with the selected fields in the report. You may drag and drop the fields to different sort folders, delete fields, and add and edit compute fields from this dialog box. You may also select Print to display all the values of the selected fields found in the data source and Sum to sum the values of a numeric field.

  4. You may select a field from the Query palette to further customize the field properties in the report.

  5. You may select the report to view or edit the properties for the report in the Report properties palette.

  6. Select Save from the File menu to save the report.

 

Report Properties

When a report object is selected, the following properties are available from the Report properties palette.

Position: Top

Indicates the starting position for the top edge of the report. You may type in a different value to change the position or manually drag the report on the canvas.

Position: Left

Indicates the starting position for the left edge of the report. You may type in a different value to change the position or manually drag the report on the canvas.

Height

Indicates the height of the report object. You may type in a different value to change the height or manually drag the report object on the canvas.

Width

Indicates the width of the report object. You may type in a different value to change the width or manually drag the report object on the canvas.

Stylesheet: reference

Click the browse (...) button to select an external stylesheet. The report references the selected stylesheet which controls the global level styling of the report. You can choose from a predefined set of stylesheets, or a stylesheet from the Import directory.

Font

Shows the default global font for the report.

Column title styling

Click the browse (...) button to open the font dialog box. Select the font, style, size, text and background color for the column titles in the report.

Column data styling

Click the browse (...) button to open the font dialog box. Select the font, style, size, text and multiple background colors for the data in the report.

Heading

Adds a heading to the report. Click the browse (...) button to open the Heading dialog box.

  • You may use the Headings toolbar to customize and modify the font, style, size, text color, background color, and so on.

  • You may use the Data Sources area to add fields from the Master File as your heading.

Footing

Adds a footing to the report. Click the browse (...) button to open the Footing dialog box.

  • You may use the Footings toolbar to customize and modify the font, style, size, text color, background color, and so on.

  • You may use the Data Sources area to add fields from the Master File as your footing.

Column totals

Shows columns totals in the report. No is selected by default. To show column totals, use the drop-down list to select Yes.

Row totals

Shows row totals in the report. No is selected by default. To show row totals, use the drop-down list to select Yes.

Sizing and Overflow

Determines the vertical sizing of the report by setting fixed or flowing overflow. For more information about sizing and overflow, see Setting Overflow Options in Power Painter.

Minimize Column Width

Shrinks the column width to fit the widest value in the report. Yes is selected by default. Select No from the drop-down list to expand the all the columns.

Record Limit

Limits the number of records used to retrieve data at run time.

Use DB optimization

Enables the database to use its internal optimization techniques which reduce the volume of database-to-server communications and improve response time.

Page numbering

Select Default, On, Off, or No lead from the list box.

 

Right-click Report Properties

When you right-click a report object, the following options are available from the dialog box that appears:

Query

Opens the query dialog box.

Size to Fit

Resizes the report as fields are added and deleted.

Delete

Deletes the report.

Properties

Displays the Report properties dialog box.

 

Field Properties

When a report is selected, the following properties are available from the Field properties palette.

The field properties available vary depending on the type of sort field selected.

Visible

Controls whether a field appears in the report graph, or is hidden. If Visible is set to No, the column is hidden, but is still used in calculations.

Title

Enables you to enter a title for the field in the report graph.

Sorting

Sorts the column data in ascending or descending order.

Page break

Starts a new page when the value of a selected sort field changes.

Subtotal

Enables you to display subtotals for numeric data whenever the value of the selected sort (By) field changes. By default, numeric field values are summed and displayed as subtotals. Additionally, you can display other summary values by applying prefix operators to numeric fields, including computed fields, by clicking the browse (...) button to open the Sub Total Options dialog box. For more information, see Display Subtotals and Other Summary Values Using Prefix Operators.

Underline

Includes a horizontal line across the width of a report after the value of the selected sort field changes.

Include missing instances

Displays missing instances of data for related fields in the report.

Drill down

Enables you to supply information to another report by using a hyperlink, procedure, or parameter.

Conditional Styling

Enables you to assign a conditional style to a report element. Conditional styling enables you to define conditions that determine when to apply particular fonts, text style, and so on. For more information about conditional styling, see Create Conditional Styling Rules.

Subheading

Adds a subheading to the report at sort breaks. Click the browse (...) button to open the subheading dialog box.

  • You may use the subheadings toolbar to customize and modify the font, style, size, text color, background color, and so on.

  • You may use the Data Sources area to add fields from the Master File as your subheading.

Subfooting

Adds a subfooting to the report at sort breaks. Click the browse (...) button to open the subfooting dialog box.

  • You may use the Footings toolbar to customize and modify the font, style, size, text color, background color, and so on.

  • You may use the Data Sources area to add fields from the Master File as your footing.

Ranking

Includes a column with a numeric rank for each row in a vertically sorted report.

Ranking limit

If specified, this value limits the number of ranked values to be retrieved.

Ranking title

Displays a default column title as RANK with the sort field. You may type in a unique title name for the ranked field.

Ranking title font

Click the browse (...) button to open the font dialog box. Select the font, style, and size for the ranking title.

Ranking data font

Click the browse (...) button to open the font dialog box. Select the font, style, and size for the ranking data.

Title font

Click the browse (...) button to open the font dialog box. Select the font, style, size, text and multiple background colors for the column title.

Data font

Click the browse (...) button to open the font dialog box. Select the font, style, size, text and multiple background colors for the data column.

Table of Contents

Enables you to generate a Table of Contents (TOC) page which shows a summary of the document's contents, along with page numbers, and can be printed with the document. The entries in the Table of Contents enable you to easily navigate to a particular section while viewing the document.

The Table of Contents only appears as a field option if you are using a coordinated report.

 

Insert a Graph Component in Power Painter

  1. Select the Graph button from the Toolbox palette and drag it onto the canvas.

    or

    Select Graph from the Insert menu.

    The New Chart dialog box appears.

  2. Select a chart type and click Finish.

    The graph appears on the canvas.

    Tip: To view the New Chart dialog box after the graph has been added to the canvas, select Show Chart Types from the right-click context menu of the graph.

  3. From the Data Sources palette, select fields and drag them to the graph object.

    As fields are dragged over the graph, sorting folders are available which enable you to sort the fields as a X and Y axis fields.

    Coordinated fields contain a common sort field for multiple reports and graphs and are burst into separate page layouts. Each value for the coordinated sort field displays on a separate page.

  4. To edit or delete these field selections, open the Query palette or the Query Popup.

    • From the View menu, select Query from the Palettes submenu.

    • Select a graph, right-click and select Query Popup from the context menu.

      or

      Double-click the graph to bring up the Query Popup.

    The sort folders appear with the selected fields in the graph. You may drag and drop the fields to different sort folders, delete fields, or add and edit compute fields from this dialog box. You may also select Print to display all the values of the selected fields found in the data source and Sum to sum the values of a numeric field.

  5. You may select a field from the Query palette to further customize the field properties in the graph.

  6. You may select the graph to view or edit the properties for the graph in the Chart properties palette.

  7. From the View menu, select Chart Editor from the Palettes submenu to further customize the chart titles, legends, axis, and so on.

  8. Select Save from the File menu to save the graph.

 

Chart Properties

For most chart types, when a graph object is selected, the following properties are available from the Chart properties palette.

Some chart types with unique properties, such as pie and three dimensional charts, have specialized properties not shown below.

Crosstab Mode

Select from Yes or No. Displays the Series-legend icon and enables secondary X-axis functionality.

Record limit

Type a number to edit the record limit value.

Use DB optimization

Enables the database to use its internal optimization techniques which reduce the volume of database-to-server communications and improve response time.

Top

Indicates the starting position for the top edge of the graph. You may type in a different value to change the position or manually drag the graph on the canvas.

Left

Indicates the starting position for the left edge of the graph. You may type in a different value to change the position or manually drag the graph on the canvas.

Height

Indicates the height of the graph object. You may type in a different value to change the height or manually drag the graph object on the canvas.

Width

Indicates the width of the graph object. You may type in a different value to change the width or manually drag the graph object on the canvas.

Apply template

Select from any of the available templates to apply a predefined color scheme to change the appearance of your chart.

Chart title

Type the desired title text.

Chart subtitle

Type the desired subtitle text.

Chart footnote

Type the desired footnote text.

Use 3D effect

Select from Yes or No to apply or not apply a three dimensional effect on the graph.

Legend position

Select the desired option for positioning the chart's legend.

Show data values

Select from Yes or No to display or not display data values on the graph.

X-axis Major grid

Select from Yes or No to display or not display X-axis major gridlines on the graph.

Y-axis Major grid

Select from Yes or No to display or not display Y-axis major gridlines on the graph.

Fit to Chart area

Select from Yes or No to turn auto placement on or off.

 

Chart Editor Dialog Box

When the Chart Editor dialog box is open in Power Painter, properties are available for each object in the chart editor. The following image is an example of the Chart Editor and the properties palette in Power Painter.

 

Insert a Text Component in Power Painter

  1. Required: Select the Text button from the Toolbox palette and drag it onto the canvas.

    or

    Select Text from the Insert menu.

    The Text Component dialog box appears on the canvas.

  2. Manually type text into the text box area to use the default font.

  3. Click the Font Properties button to open the Font dialog box.

    You may customize the font, style, size, and text color of the text.

  4. Click OK to close the Font dialog box.

  5. Required: Click OK to close the Text component dialog box.

    The text is inserted into the canvas.

  6. You may select the text component to view or edit the properties for the text in the Text properties palette.

  7. Select Save from the File menu to save the text.

 

Text Properties

When a text object is selected, the following properties are available from the Text properties palette.

Height

Indicates the height of the text object. You may type in a different value to change the height or manually drag the text object on the canvas.

Position: Left

Indicates the starting position for the left edge of the text.

Position: Top

Indicates the starting position for the top edge of the text.

Width

Indicates the width of the text object. You may type in a different value to change the width or manually drag the text object on the canvas.

 

Insert an Image Component in Power Painter

  1. Select the Image button from the Toolbox palette and drag it onto the canvas.

    or

    Select Image from the Insert menu.

    The Open dialog box appears on the canvas.

  2. Select from the list of image files available.

    The Reporting Server is the default location for all stored images.

    Images need to be copied to the appropriate directories by your administrator.

  3. Required: Click OK to insert the image. The image is added to the canvas, and the size defaults to the size of the image.

  4. You may select the image component to view or edit the properties for the image in the Image properties palette.

  5. Select Save from the File menu to save the image.

 

Right-click Image Properties

When you right-click an image, the following options are available from the dialog box that appears:

Reset to original size

Resets the image to the original size.

Deletes

Deletes the image.

Properties

Opens the Image properties dialog box.

Hover over an image to view the file name.

 

Image Properties

When an image object is selected, the following properties are available from the Image properties palette.

Height

Indicates the height of the image object. You may type in a different value to change the height or manually drag the image object on the canvas.

Position: Left

Indicates the starting position for the left edge of the image.

Position: Top

Indicates the starting position for the top edge of the image.

Width

Indicates the width of the image object. You may type in a different value to change the width or manually drag the image object on the canvas.

 

Insert a Line Component in Power Painter

  1. Required: Select the Line button from the Toolbox palette and drag it onto the canvas.

    or

    Select Line from the Insert menu.

    The line object is added to the canvas as a positioned horizontal line.

    To insert a vertical line, use the properties palette to change the orientation to vertical.

  2. You may select the line component to view or edit the properties for the line in the Line properties palette.

  3. Select Save from the File menu to save the line.

 

Line Properties

When a line object is selected, the following properties are available from the Line properties palette.

Color

Sets the color of the line. Click in the color value field to open the color dialog box.

Height

Indicates the height of the line object. You may type in a different value to change the height or manually drag the line object on the canvas.

Orientation

Indicates the horizontal or vertical orientation of the line on the canvas.

Position: Left

Indicates the starting position for the left edge of the line.

Style

Sets the style of the line. Use the drop-down list to select from solid, dotted, dashed, and so on.

Weight

Sets the thickness of the line to light, medium, or heavy.

Width

Indicates the width of the line object. You may type in a different value to change the width or manually drag the line object on the canvas.

 

Display Subtotals and Other Summary Values Using Prefix Operators

The Subtotal property in the Field Properties palette enables you to display subtotals, and other summary values using prefix operators, for any or all of the numeric fields, including computed fields, in a report query. From the Field Properties dialog, when you click the browse (...) button to the right of the Subtotals property, the Sub Total Options dialog box opens.

  1. Select the Sub Totals option to replace the default setting of None.

    If you are using computed fields, select the Recomputed Sub Totals option instead.

  2. Numeric field values are summed and displayed as subtotals in the report by default. To display summary values other than the default subtotals, select prefix operators using the drop-down lists in the Prefix area that are adjacent to each of the selections in the Columns area. Note that the Sum prefix produces the same subtotal results as not selecting a prefix operator.

    All of the available prefix operators are listed and described in the following table.

    Prefix

    Description

    Displayed in Prefix List

    SUM. Compute the sum of values Sum
    ASQ. Compute the average sum of squares Average Square
    AVE. Compute the average value Average
    CNT. Count the items Count
    FST. Select the first value only Show first in group
    LST. Select the last value only Show last in group
    MAX. Select the maximum value only Maximum
    MIN. Select the minimum value only Minimum

  3. To display all numeric field values as subtotals or other summary values in the report, select the Apply totals to all columns check box in the Columns area.

  4. Required: To display subtotals or other summary values for a subset of the numeric fields in the report, make sure the Apply totals to all columns check box is de-selected, then select check boxes for the individual numeric fields in the Columns area of the Subtotal tab.

    When Apply totals to all columns is selected, options for all individual fields in the Columns area are inactive by default.

  5. To modify the default subtotal title, which is set to *TOTAL, type the desired text you want to display as the title for all subtotals and any other selected summary values in the Current Subtotal Text field.

  6. To hide subtotals for sort fields that have only a single value, select the Suppress for single lines check box.

The following image shows the Sub Total Options dialog box.

 

Using Reporting Functions

The following reporting functions are available within the Data Sources palette of the Power Painter tool:

 

Create a DEFINE Statement

  1. Required: Click the Define

    button from the Data Sources palette.

    The Field creator dialog box opens.

  2. Required: Type the name of the virtual field in the Field input box.

  3. Required: Enter an expression in the expressions box.

  4. Required: Click OK to close the Define field creator dialog box.

    The DEFINE field is added to the report field list.

  5. Drag and drop the DEFINE field into the report.

 

Create a COMPUTE Statement

  1. Click the Compute

    button from the Query palette.

    The Field creator dialog box opens.

  2. Type the name of the computes field in the Field input box.

  3. Enter an expression in the expressions box.

  4. Click OK to close the Compute field creator dialog box and apply the COMPUTE statement.

    The COMPUTE field is added to the report.

 

Create a JOIN Between Data Sources

  1. Click the Joins tab in the Data Sources palette.

  2. Click the Add Join button from the Data Sources palette toolbar.

  3. Required: Select a target Master File and click OK.

    The Create Join dialog box opens.

    Joins can also be created on defined fields. For more information about defined fields, see Create a DEFINE Statement.

  4. Type a name for the Join in the Description field.

  5. Select the Type and Instances for the Join or use the default selections.

  6. Click the Select a source field browse (...) button to select the Join source field.

  7. Click the Select a target field browse (...) button to select the target fields.

    If no target fields are available, you are notified that there are no fields found.

  8. Click Save & Create to save the Join relationship.

    The joined field is added to the Joins tab.

  9. Click the Fields tab in the Data Source palette to view the joined data.

  10. Drag and drop joined fields onto the canvas as you would any other field.

To edit a join, double-click the join.

 

Create Conditional Styling Rules

  1. Ensure that the Query palette or popup is open on the canvas:

    • From the View menu, select Query from the Palettes submenu.

    • Select a report or graph component, right-click and select Query Popup from the context menu.

      or

    • Double-click the report or graph component to bring up the Query Popup.

  2. Select the field name in the Query palette.

    When fields are selected, properties for each field are available in the Field properties palette.

  3. Click the Field properties palette and scroll down until you see Conditional styling.

  4. Click in the Conditional styling value field and select the New styling rule button.

    The Create Conditional Style Rule dialog box opens.

  5. Required: Create the style conditions for the field.

    1. Required: Type a name for the condition or accept the default.

    2. Required: Select a relationship for the condition from the Relationship drop-down list.

    3. Specify a value by doing one of the following:

      • Type a literal value in the Value input field.

        or

      • Click Values from the Compare type section to display existing data source values.

    4. Click Done to save and apply the conditional style rule.

      The condition is added to the Field properties palette and the condition is applied to the data on the Power Painter canvas.

The following image is an example of the Field properties palette with a Low Balance condition rule. The report object shows red text where the conditional styling rule was applied.

 

Create a WHERE Statement

  1. Required: Select a report or graph object in the Power Painter canvas.

  2. Required: From the View menu, select Selection Criteria from the Palettes submenu.

    The Selection Criteria palette appears on the bottom of the canvas.

  3. Required: Drag a field name from the Data Sources palette to the Selection Criteria palette.

    The WHERE statement is added to the Selection Criteria palette.

  4. Specify the condition by using the drop-down list.

  5. Click Select Value to open the Select Value dialog box.

  6. Required: Select a Constant, Parameter, Field or Value selection button.

    • If creating a constant, manually type in a value for the WHERE statement.

    • If creating a parameter, click the browse (...) button to open the Variable Editor dialog box. For details, see Create Parameters in a Standard Report.

      Select the name, selection, a value options for the WHERE statement.

    • If creating a Field or Value, select a value from the list supplied.

  7. Required: Click OK to close the Select Value dialog box.

    The value is added to the Selection Criteria palette and the report or graph reflects the WHERE statement as soon as the value is selected.

    To delete the WHERE statement, select the WHERE statement check box and click the red X on the blue highlighted line in the Selection Criteria palette.

 

Importing Values From External Files for WHERE Statements

When developing a query, you can use a locally saved, external file as selection criteria to limit query results. This enables you to quickly build a query containing a large number of WHERE statement values without having to manually enter repetitive or readily available data.

 

Create a Value List From an External File

  1. Required: Navigate to the Selection criteria area.

  2. Required: Begin creating a WHERE statement by selecting a field from the list of available fields.

  3. Required: Select a data comparison option that can accept multiple values.

    Valid options include EQUAL to, NOT EQUAL to, IN literal list, NOT IN literal list, EXCLUDES literal list, and INCLUDES literal list.

  4. Click Select values.

    The Values dialog opens.

  5. Click the import button as shown in the following image.

    A dialog box opens as shown in the following image.

  6. Select either the Flat file or Excel Spreadsheet (XLS) File Format option.

  7. Click the Browse button.

    A standard file selection dialog opens.

  8. Select an external file to import from your local machine or network.

    The external file must only contain text with new line delimiters.

  9. Click OK.

    The values contained in the file are displayed in the Multiple values entered list in the right pane of the dialog.

  10. Optionally, you can remove specific values from the Multiple values entered list or move values up or down within the list.

  11. Click OK.

    You can save the query for future use and reopen the query to append new or remove existing values.

 

Creating Parameters for Use in the Auto Prompting Facility

When a parameter selection criteria is added in Power Painter, the auto prompting facility appears at run time.

 

Create Parameters and Run in the Auto Prompting Facility

  1. From the Selection Criteria palette, create a WHERE statement.

  2. From the Select Value dialog box, select Parameters and click the blue browse (...) button.

    The Variable Editor dialog box opens.

  3. In the Variable Editor dialog box, enter appropriate text in the Name and Description fields for the parameter.

    These fields may be automatically filled in based on the field you selected to create the WHERE statement.

  4. Required: Choose from the following Selection options:

    • Dynamic. The Data Source and Field are selected by default based on the field selected to create the WHERE statement. Dynamic is the default option.

    • Static. The Values area at the bottom of the Variable Editor is activated. Select the Constant or Value option. For Constant, enter a value or values. For the Value option, all of the values for the selected field appear in the Value list box. Move the values you want to the Multiple values entered area using the plus and minus buttons.

    • Select multiple values at runtime. Instead of choosing the Dynamic or Static options, you can select to be prompted for multiple values at runtime.

  5. Click OK to exit the Variable Editor.

    The parameter is added to the Select Value dialog box.

  6. Click OK to close the Select Value dialog box.

  7. Click Run from the File menu in Power Painter.

    The Auto Prompting facility appears.

 

Controlling the Overflow and Relative Positioning of Objects in Power Painter

You may control the overflow and relative (vertical) positioning of reports and graphs in Power Painter so that reports fill the entire page, as well as maintain their relative position in the document.

Overflow is defined as the area of a report that exceeds it defined space in the page layout. For example, a multi-page report and a graph can both be added to a single page layout with the graph positioned beneath the report. The report can flow or fill the page and the graph can be relatively positioned to the report so it is placed at the end of the document following the complete rendering of the report.

The Sizing and Overflow property is available from the Report Properties palette.

 

Setting Overflow Options in Power Painter

When executing a report, the area that flows outside of the canvas is considered the overflow. The following image is an example of the canvas in Power Painter.

You can set the size and overflow of a report object to control the overflow at run time. Sizing and overflow options are available through the Report Properties palette.

These options only apply to report objects. There are no size and overflow properties for graphs, images, text, or lines.

The following image is an example of report with fixed overflow. Note how the report output maintains the size of the report object in the Power Painter canvas, spreading the report across 14 pages.

The following image is the same report with flowing overflow. Note how the report expands on the page at run time.

 

Using Fixed Overflow in Power Painter

Reports using fixed overflow maintain the size and position of the report object from the Power Painter canvas in the report output. You may customize and maintain the fixed position of the report output by adjusting the position and size options from the Properties palette.

 

Customize the Fixed Position of an Object

  1. Required: Select an object from the Power Painter canvas.

    The Properties palette shows the available properties for the selected object.

  2. Select Fixed from the Sizing and Overflow drop-down list.

  3. Change the Position: Left, Position: Top, Height, and Width options from the Report properties palette to adjust the position of the object at run time.

  4. Run the report.

If the document contains multiple fixed reports, the report output maintains the same size and position as the report objects in the Power Painter canvas, as shown in the image below.

If the document contains a fixed report and a graph, the report is broken up around the graph object. You must set the relationship between the graph and the report. For more information about setting relationships, see Relate Surrounding Objects to a Report.

 

Using Flowing Overflow in Power Painter

Reports using flowing overflow begin the report output at the top of the page and fill the page with the report results. Flow margins are available which enable you to adjust the canvas so that flowing objects fill the page.

Flowing overflow is not available for graphs, images, text, or lines. For example, if a graph is included in the document, the graph overlaps the report at run time. You must set a relationship between the graph and the report so that the graph flows after the report at run time.

 

Set the Flowing Report Property

  1. Required: Select an object from the Power Painter canvas.

    The Properties palette shows the available properties for the selected object.

  2. Required: Select Flowing from the Sizing and Overflow drop-down list.

    Repeat this step for any surrounding report objects to prevent the overlapping of reports and graphs at run time.

    Tip: Flow margins are available for the canvas which further enable you to adjust the page so that the report fills each page between the flow margins. For more information about flow margins, see Set Flow Margins for the Power Painter Canvas.

  3. Run the document.

If the document contains multiple flowing reports, the reports overlap at run time. You must set the relationship between the reports. For more information about setting relationships, see Relate Surrounding Objects to a Report.

If the document contains a flowing report and a graph, the graph overlaps the report at run time. You must set the relationship between the graph and the report. For more information, see Relate Surrounding Objects to a Report.

 

Set Flow Margins for the Power Painter Canvas

Each document in the canvas has flow margins. Flow margins are set through the top and bottom margins. Flowing reports utilize flow margins to ensure that header and footer information is preserved in the output.

  1. Select the canvas from Power Painter.

  2. From the Document properties palette, manually type in the Flow margin: bottom value.

    The flow margin bottom value sets the ending vertical coordinate where a flowing report ends on each page.

  3. From the Properties window, manually type in the Flow margin: top value.

    The flow margin top value sets the beginning vertical coordinate where a flowing report starts on each new page.

    Flow margins are indicated by blue lines that appear on the top and bottom of the Power Painter canvas.

    Tip: You may also click and drag the flow margins up and down the Power Painter canvas.

  4. Adjust the objects on the canvas between the flow margins and run the report.

    The report fills each page between the flow margins until it reaches the end. For example, in the image below the flow margin was adjusted so that an image could be used as a header.

 

Setting a Relative Position Between Objects in Power Painter

You may set a relative (vertical) relationship between objects. Similar to the flowing report option, you can select multiple report and graph objects and set a relationship. The following relationship options are available from the Main toolbar, or from the Layout menu in Power Painter:

There is currently no method of controlling horizontal overflow.

 

Relate Surrounding Objects to a Report

A surrounding object can only be related to one flowing report. However, a flowing report can have multiple surrounding objects related to itself.

  1. Select an object from the Power Painter canvas.

  2. Select two reports, or a graph and a report, by using the Shift key to select the surrounding objects.

    The primary object is indicated by clear boxes around the edges, when selected. The surrounding object is indicated by solid black boxes around the edges, when selected.

  3. Set the relationship between the objects by selecting the Relate objects

    button from the Main toolbar.

    The relationship buttons are only available from the Main toolbar when multiple objects are selected.

    An arrow appears on the canvas, indicating that the relationship has been set.

    In the following image, the graph is the surrounding object to the report.

    Tip: To break the relationship, click the same surrounding objects using the Shift key and click the Relate objects button from the Main toolbar. The arrow is removed and the relationship is broken.

  4. Run the report.

If the document contains multiple reports, and the relationships have been set, the first report runs until it is complete, followed by the surrounding reports, at run time.

If the document contains a report and a graph, and the relationship has been set, the report runs until complete, followed by the graph, at run time.

 

Rules for Setting Relationships Between Objects

The following rules apply when setting relationships between objects:

Setting Relationships

This sets a relative distance between the flowing report and a surrounding object. The surrounding object is rendered immediately following the end of a flowing report with the related distance between the end of the flowing report and the beginning of the surrounding object.

Fixed objects

A fixed object cannot be broken across a page break, it needs enough vertical space to fit on the last page of the flowing report.

Flowing object (reports)

If a surrounding object is another flowing report, then the required space does not need to be set. A flowing report can be broken across pages. The reporting server determines if there is enough room to begin rendering the surrounding flowing report.

Draw object

Relationships do not apply to draw objects such as images, lines, and text. Draw objects can be placed above flowing reports in a page layout, but draw objects placed below a flowing report should be placed outside of the bottom flow margin.

Setting a relationship between a report and a graph

If the overflow property of the report is set to flowing and there is no set relationship between the graph and report, then the report will overlap and display on top of the graph at run time. There is no way for Power Painter to automatically set relationships, so set the relationship between the report and the graph.

A surrounding object can only be related to one flowing report; however, a flowing report can have multiple surrounding objects related to itself.

 

Customizing the Power Painter Environment

After the components are in the canvas, you can customize the layout properties, alignment options, and page to configure the canvas for precise output. You can also adjust the work area and change the theme of the Power Painter environment.

 

Setting Preferences

When you access the Edit menu and select Preferences, the following dialog box appears where you can set preferences for various defaults.

The Preferences dialog contains a General tab and a Reports and Graphs tab. The General tab enables you to select Output Format, Page Orientation, and Page Size options. The Reports and Graphs tab enables you to select a Preview option and set a Record Limit for both reports and graphs, select a Default Stylesheet for reports, and select a Default Template for graphs.

These settings do not affect the current Power Painter session. The new settings are applied the next time Power Painter is opened.

 

Setting Field Display Options

When you access the View menu and select Field Display Options, another menu opens providing you with options to display fields as a Name, Alias, Title, or Description. You can also choose the Prefix with option to prefix fields with a filename or segment name.

 

Adjusting the Layout of Objects in the Canvas

You may use the Main toolbar and the Layout menu to adjust the layout of objects in the Power Painter canvas. The following layout options are available:

 

Showing Grids, Rulers, and Guides in the Canvas

The following image shows the Layout menu in Power Painter.

 

Aligning and Sizing Objects in the Canvas

You can align objects relative to one another or to the canvas. Alignment options are available from the Layout menu or from the Main Toolbar.

The following image shows the Main toolbar in Power Painter.

 

Adjusting the Power Painter Work Area

Click anywhere on the canvas and select the Properties palette to adjust the document properties.

Data preview

Shows live data or sample data for reports and graphs.

By default, sample data is shown in the Power Painter canvas.

Design Record Limit

Sets the number or records to be shown when gathering data for reports and graphs.

The default record limit value for the report is 500.

Flow margin: bottom

Sets the bottom margin for flowing reports.

Flow margin: top

Sets the top margin for flowing reports.

Output format

Enables you to select the output format from the following options: PDF, DHTML, Power Point, Active Report, or Excel.

Page orientation

Sets the page orientation of the document. Select either Portrait or Landscape.

Portrait is the default page orientation.

Page size

Sets the page size of the document. Use the drop-down list to select a page orientation option.

Letter is the default page orientation.

Units

Specifies the unit of measurements for the tool. Select Inches, Centimeters, or Points.

Inches is the default unit of measurement.

 

Changing the Theme of the Power Painter Environment

Power Painter enables you to customize your environment for seamless integration with your organization's color scheme.

From the View menu, select Theme to access the available skins to choose from.

The Power Painter environment changes to the theme of the environment that you have selected.

 

Accessing Power Painter

You can access Power Painter from a report in the Reports folder.

Users who have the capability to create reports can access Power Painter to create a new report in the Domains tree. From a Reports folder, right-click a folder and select Power Painter from the context menu. Users can then save a report for editing or running at a later time.

To edit a report, click a report and select Power Painter from the pop-up menu that opens. The report you previously saved opens in Power Painter.