WebFOCUS Online Help > Using Power Painter > Using Standard Reporting Functions

Using Standard Reporting Functions

In this section:

How to:

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

Top of page
Procedure: How to Create a DEFINE Statement
  1. Click the Define button from the Data Sources palette.

    The Field creator dialog box opens.

  2. Type the name of the virtual field in the Field input box.
  3. Enter an expression in the expressions box.
  4. 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.
Top of page
Procedure: How to 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.

Top of page
Procedure: How to 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. Select a target Master File and click OK.

    The Create Join dialog box opens.

     
     

    Note: Joins can also be created on defined fields. For more information about defined fields, see How to 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.

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

Note: To edit a join, double-click the join.

Top of page
Procedure: How to 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. Create the style conditions for the field.
    1. Type a name for the condition or accept the default.
    2. 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.

 
 

Top of page
Procedure: How to Create a WHERE Statement
  1. Select a report or graph object in the Power Painter canvas.
  2. From the View menu, select Selection Criteria from the Palettes submenu.

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

  3. 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. 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 How to Create Parameters and Run in the Auto Prompting Facility.

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

     
     

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

Top of page
Importing Values From External Files for WHERE Statements

How to:

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.

Top of page
Procedure: How to Create a Value List From an External File
  1. Navigate to the Selection criteria area.
  2. Begin creating a WHERE statement by selecting a field from the list of available fields.
  3. Select one of the following data comparison options:
    • IN literal list
    • NOT IN literal list
    • EXCLUDES literal list
    • 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 one of the following File Format options:
    • Flat file
    • Excel Spreadsheet (XLS)
  7. Click the Browse button.

    A standard file selection dialog opens.

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

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

Top of page
Creating Parameters for Use in the Auto Prompting Facility

How to:

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

For more information about creating and applying auto prompting templates, see the Changing WebFOCUS Client Settings in the WebFOCUS Administration Console section of the WebFOCUS Security and Administration manual.

Top of page
Procedure: How to 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. Choose from the following the 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.

      Note: When working with a Reporting Object, you can only select fields from the data source the Reporting Object is defined to access.

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

     
     

WebFOCUS7