Creating the Layout and the Report

How to:

Here you will create the layout for the project and add elements to it, such as text, background color, reports, images, and a target frame for the drill-down output. You will also customize the parameters and rearrange the items in your layout.

For the report (which you will create directly from the layout), you will add calculated values, Where statements to limit the data in the report, and drill-down capabilities. You will also style the report with color, borders, and a page heading.


Top of page

x
Procedure: How to Create the Report Layout

Complete the following steps to create a Layout procedure called PRODREP.

  1. Right-click the HTML Files folder under the Century project.
  2. Select New, then select HTML File from the context menus.

    The Add HTML dialog box opens.

  3. Type PRODREP in the File name text box.

    Add HTML File

    Note: Composer is selected as the default.

  4. Click Open.

    The HTML Composer opens, as shown in the following image.

    HTML Composer


Top of page

x
Procedure: How to Add Text and Color to the Layout

Add a heading to your application, style the heading, and add a background color.

  1. Click the Insert text Insert Text button.

    Your mouse pointer will change to crosshairs.

  2. Click and drag the crosshairs to create a rectangular text box.

    You can resize the box after you enter and style the text.

  3. Click on the box and type Century Corporation Product Report and Graph.
  4. Click anywhere outside of the heading text box, then right-click the text box and select Style from the context menu.

    The Style Composer dialog box opens with Font highlighted in the list at the far left.

  5. To set the font type, in the Font name area, select the Family option, then click the ellipsis button to the right. The Font Picker dialog box opens, select Arial in the Installed fonts area, click the right arrow to move Arial to the Selected fonts area, and click OK.
  6. To set the font style, select Bold from the Absolute drop-down list in the Bold area.
  7. To set the font size, in the Size area, select px from the Specific drop-down list and type 16 in the input area to the left.
  8. To set the font color, select Navy from the Color drop-down list in the Font attributes area.
  9. To set the text alignment, select Text below Background in the list at the far left, then select Centered from the Horizontal drop-down list in the Alignment area.

    This centers the heading text horizontally within the text box.

  10. To set the heading background color, select Background below Font in the list at the far left, then select the Transparent option below the Background color menu.

    This allows the light color you will select for the background color to show through the heading.

  11. Click OK to close the Style Composer.
  12. Resize the heading text box as necessary by clicking and dragging the borders.
  13. To set the background color for the HTML page, select BODY from the top drop-down list in the Properties tab of the Properties bar.
  14. Click once in the empty field to the right of Background color, then click the ellipsis button that appears at the far right of the empty field.

    The Color Picker dialog box opens.

  15. Select any light color and click OK.

    The following image shows how the HTML Composer should appear at this point, depending on the size of your text box.

    HTML Composer


Top of page

x
Procedure: How to Create a Report From the Layout

You will now create the report in the Report Painter which is accessed directly from the HTML Composer.

  1. Click the Report Report button.

    The mouse pointer changes to crosshairs.

  2. Click and drag the crosshairs and create a report area placeholder.

    Note: You can resize and reposition the report placeholder at any time.

  3. Double-click the report placeholder.

    The Open dialog box opens.

  4. Select the CENTORD Master File and click Open.

    The following image shows the Field Tree in the Report Painter window. The Object Inspector, located in the left pane, lists the fields in the CENTORD Master File.

    Note: To change the Object Inspector to Field Tree view, right-click in the Object Inspector and select Show Field Tree.

    Report Painter

  5. Double-click the following fields in the Object Inspector Fields tab to add them to the report:
    • PRODNAME (in the INVSEG directory)
    • LINEPRICE (in the PINFO directory)
    • LINE_COGS (in the INVSEG directory)
  6. Select the Product Name field and click the By By button on the toolbar. This sorts all of the data vertically by product name.
  7. Select the Line Total column and click the Sum Sum button. This sums the data in the Line Total column.

    The following image shows how the new report should appear at this point.

    Report Painter


Top of page

x
Procedure: How to Add a Calculated Value to the Report

Continue creating your report by adding a calculated value, called PROFIT. This field is created using a calculation with two existing fields in the CENTORD data source, LINE_PRICE and LINE_COGS.

  1. Select Computes from the Report menu, or click the Computes button from the Setup toolbar.

    The Report Options dialog box opens at the Computes tab.

  2. Type PROFIT in the input area labeled Field.
  3. Type D4.2 in the input area labeled Format.

    Tip: In this case, you know the exact format you want for this field. If you are unsure of the format and want to see all available formats, click the Format button to access the Format dialog box.

  4. Place the cursor in the box below the input area labeled Field.
  5. Create the following expression: (LINEPRICE / LINE_COGS) – 1.

    To create this expression:

    • Click the Fields button.
    • Double-click LINEPRICE in the CENTORD field list box.
    • Click the forward slash (/) on the Report Options calculator.
    • Double-click LINE_COGS in the CENTORD field list box.
    • Highlight the statement and click the double parentheses ( ).
    • Click the minus (-) sign and then the number 1.

    The following image shows how the Report Options expression should appear.

    Report Options expression

  6. Click OK.

    The new column PROFIT is added to the Report Painter window.

  7. Select the PROFIT column and then click the Sum Report Options button.
  8. Right-click the PROFIT column and select Column Title.
  9. Change the title by typing Profit Margin.
  10. Click OK.

    The following image shows how the report should appear at this point.

    Report Painter


Top of page

x
Procedure: How to Limit the Data to a Selected Plant and Year

You will now create a list of acceptable values for the plant location and the year. These are the values the user will be able to select when they are using this application. Later, you will use each set of values you create here to run a parameterized report.

To do this, you will need to create two Where statements.

  1. Click the Where/If Where/If button on the toolbar.

    The Report Options dialog box opens at the Where tab.

  2. Click Assist.

    The Expression Builder opens.

  3. To create the first Where statement:
    • From the Fields list, double-click PLANT (under Location Dimension).
    • From the Logical Relation drop-down list, select equals.
    • In the Compare Type box, select Parameter.
    • Double-click the Compare Value box to open the Variable Editor.
  4. In the Variable Editor:
    • For Name, type LOCATION.
    • In the Prompt input field, type Please select a Plant.
  5. From the Variable Type drop-down list, select Multiselect OR.
  6. In the Data Context area, select Values for field.
  7. Click the Select a field ellipsis button to get values for the PLANT field. The Value Retrieval dialog box opens.

    The Value Retrieval dialog box provides a list of available fields in your data source. Double-click the PLANT field to close the Value Retrieval dialog box and return to the Variable Editor. The available values for the PLANT field are listed.

  8. Double-click each of the following values (BOS, DAL, LA, ORL, SEA, STL) to add them to the Accept List.

    The following image shows the Variable Editor populated with the values you selected.

    Variable Editor

  9. Click OK to close the Variable Editor.

    The following image shows the Expression Builder populated with the values you selected.

    Expression Builder

  10. Click OK to close the Expression Builder and return to the Where/If dialog box.

    Leave the Where/If dialog box open to create the next Where statement.

  11. Click New from the Where/If dialog box.
  12. Click Assist.

    The Expression Builder opens.

  13. To create the second Where statement:
    • From the Fields list, double-click YEAR (under the Time Period Dimension).
    • From the Logical Relations drop-down list select equals.
    • In the Compare Type box select Parameter.
    • Double-click in the Compare Value box to open the Variable Editor.
  14. In the Variable Editor:
    • For Name, type YRVAL.
    • In the Prompt input field, type Please select a Year.
  15. From the Variable Type drop-down list, select Multiselect OR.
  16. In the Data Context area, select Values for field.
  17. Click the Select a field ellipsis button to get values for the YEAR field. The Value Retrieval dialog box opens.

    The Value Retrieval dialog box provides a list of available fields in your data source. Double-click the YEAR field to close the Value Retrieval dialog box and return to the Variable Editor. The available values for the YEAR field are listed.

  18. Double-click each of the following values (2000, 2001, 2002) to add them to the Accept List.

    The following image shows the Variable Editor populated with the values you selected.

    Variable Editor

  19. Click OK to close the Variable Editor.
  20. Click OK to close the Expression Builder.
  21. Click OK to close the Report Options dialog box.

Top of page

x
Procedure: How to Create a Page Heading for the Report

Now you will create a page heading for the report. Page headings appear at the top of every report page.

  1. In the Report Painter window, place your cursor in the Page Heading area.
  2. Double-click YEAR in the Object Inspector Fields tab.

    The embedded field <YEAR appears in the Page Heading area.

  3. After the <YEAR field, type Sales Metrics for All Products.
  4. Press the Enter key.
  5. Type For the followed by one space.
  6. Double-click PLANTLNG in the Object Inspector Fields tab.
  7. Move the cursor into the Page Heading area after <PLANTLNG and type Plant.

    The following image shows how the report should appear at this point.

    report

  8. Highlight the second line of text in the heading.
  9. From the Font toolbar, select the following font attributes:
    • For font type, select Arial.
    • For font style, select Bold.
    • For font size, select 10.
  10. Right-click in the Page Heading area and select Options.

    The Properties for Page Header dialog box opens.

  11. Select the Style tab, then click the Single Color option in the Background Coloring section.
  12. Click the Select Colors button and choose a color from the color palette.
  13. Click the Justification drop-down list and select Center.
  14. Click OK to close the Properties for Page Header dialog box.

Top of page

x
Procedure: How to Style Your Report

You will now style the report by adding alternating row colors to the report data and a border around the heading.

  1. From the Report menu, select Styling.
  2. Select Data from the active object drop-down list.

    The following image shows the Report Options dialog box populated with the values you selected.

    Report Options

  3. In the Background Color area, select the Alternating Colors option.
  4. Click Select Colors.

    The Choose Background Colors dialog box opens.

  5. Leave the first color as the default selection of white.
  6. Select the Second Color check box and select the second color.
  7. Select the Alternate On A By Field check box.
  8. Select the PRODNAME field.

    The following image shows the Choose Background Colors dialog box.

    Choose Background Colors dialog box

  9. Click OK to close the Choose Background Colors dialog box.
  10. Click Apply to apply your selection.

    Leave the Report Options dialog box open to add a border to the heading.

  11. In the Report Options dialog box, select Heading from the active object drop-down list.
  12. In the Graphical area, click Select Borders.
  13. Deselect the Make all borders the same check box.
  14. In the Top Border area, select:
    • Medium for width.
    • Dotted for style.
    • Black for color.
  15. In the Bottom Border area, select the Same as Top Border check box.
  16. In the Left Border and Right Border areas, select OFF from the Width drop-down lists.

    The following image shows the Border dialog box populated with the selected values.

    Borders

  17. Click OK to close the Borders dialog box.
  18. Click Apply in the Report Options dialog box, then click OK.

Top of page

x
Procedure: How to Set Up the Drill-Down Report

Now you will set up this report so you can drill down from the data in the Product Name column to the STORSAL graph you created earlier. Drill down on the Product Name field, so the information you see in the resulting graph will be specific to that product.

  1. In the Report Painter, right-click the Product Name column and select Options.

    The Field Properties dialog box opens.

  2. Click the Drill Down tab.
  3. In the active object drop-down list, select Column Data.
  4. In the Drill Down Type drop-down list, select Execute Procedure.
  5. Click the Procedure Name Browse button, select STORSAL.fex from the Open dialog box, and click Open.

  6. In the Target Frame area, type graphframe.
  7. In the With Parameters area, click Add.

    The Drill Down Parameter dialog box opens.

  8. Type PNAME in the Parameter Name input box.

    PNAME is the parameter you created in the STORSAL procedure.

  9. In the Parameter value area, select the Field option.
  10. Select the PRODNAME field from the drop-down list in the Parameter value area.

    Drill Down Parameter

  11. Click OK to close the Drill Down Parameter dialog box.

    The following image shows the Field Properties dialog box populated with the values you selected.

    Field Properties

  12. Click OK to close the Field Properties dialog box.
  13. Select Close from the File menu to close the Report Painter.
  14. When you are prompted to save your changes, click Yes.

    You are returned to the HTML Composer and the New Parameters dialog appears.

  15. Click OK to automatically add your parameters to the HTML page.

The following image shows how the HTML Composer should look at this point.

HTML Composer


Top of page

x
Procedure: How to Customize Parameters

You will now customize the Plant (LOCATION) parameter by modifying the value from the data source to make it more user-friendly. You will also change the control type for the Year (YRVAL) parameter from a list box to a radio button, rearrange the order of values, and change the default value.

  1. In the HTML Composer, select the Parameters tab.

    The Properties and settings dialog box opens, showing a Legend of the available controls for the parameters on the HTML page.

  2. Click the list box control (Input control listbox1) associated with the LOCATION parameter.
  3. Change the control type to a drop-down list box by right-clicking the LOCATION list box control (Input control listbox1) and select Drop down list from the Set Control Type context menu.

    Drop Down list

  4. From the Properties and settings dialog box, double-click each of the values in the Display column and type the full city name as indicated below:
    • Change BOS to Boston
    • Change DAL to Dallas
    • Change LA to Los Angeles
    • Change ORL to Orlando
    • Change SEA to Seattle
    • Change STL to St. Louis

    This changes the display name in the drop-down list from the value stored in the data source to the values you specify.

  5. To make Boston the default selection, click the Boston check box in the Selected column.

    The following image shows how the LOCATION drop-down list settings should appear.

    HTML Composer

  6. Click the list box control (Input control listbox2) associated with the YRVAL parameter.
  7. Change the control type to a Radio button by right-clicking the YRVAL list box control (Input control listbox2) and select Radio button from the Set Control Type context menu.

    Radio button

  8. From the Properties and settings dialog box, drag and drop the Year values so they appear in descending order.

    or

    Select a year and use the up/down arrows above the Static Values area to change the order of the years.

  9. To make 2002 the default selection, click the check box in the Selected column for the 2002 value.

    The following image shows how the YRVAL Radio button settings should appear.

    Properties and settings

  10. Close the Properties and settings dialog box for the YRVAL Radio button.
  11. Switch to the Design tab to view the Parameter changes on the HTML page.

Top of page

x
Procedure: How to Create the Target Frame For Drill-Down Output

You will now add an output target frame that will contain the graph output from the main report when a user selects a drill-down link. The frame is invisible to the user until a drill-down link is selected.

  1. In the HTML Composer from the Insert menu, select Components, then select Frame.

    Your mouse pointer changes to crosshairs.

  2. Scroll down past the report placeholder to insert the frame by clicking and dragging the crosshairs to form a rectangular box just below the report area.
  3. Click anywhere inside the frame you inserted and view the Properties window.

    The properties associated with the new frame are displayed in the Properties window.

  4. Double-click the iframe1 value in the right column next to Name in the left column of the Properties tab. Replace this value by typing graphframe which is the same Target Name entered in the Drill Down tab. See How to Set Up the Drill-Down Report.

    The following image shows the Properties window with graphframe as the new Name value.

    Properties


Top of page

x
Procedure: How to Reorganize the Layout and Add an Image

Now you will move some items around in the HTML Composer and add an image.

  1. Rearrange the heading, the Please select a Plant prompt and selection box, and the Please select a Year prompt and option buttons so the report page in the HTML Composer appears like the following image.

    HTML Composer

  2. Click the new frame and grab the handles to resize it to almost the same size as the report placeholder.

    This frame will contain graph output when a user clicks a drill-down link in the report.

  3. Press the Ctrl key and click the report placeholder and then the frame placeholder.
  4. Click the Make same size Make same size button.
  5. Click the Align right Align right button.
  6. Click the Insert image Insert image button. The cursor changes into a crosshair.
  7. Click and drag the crosshair to create a rectangular image box below the bottom-left corner of the new frame.

    The Get source file dialog box opens.

  8. Select the following from the drop-down box:
    1. Developer Studio Desktop
    2. Window's Desktop
    3. My Computer
  9. Navigate to the following directory (where install_dir is most likely the C directory):
    install_dir:\ibi\apps\ibinccen\images\powered.gif
  10. Click Open.

    The powered.gif image is added to your report layout.

  11. Pressing the Ctrl key, click the image and then the frame.
  12. Click the Relate Bottom Left Relate Bottom Left button.

    This ensures that the image always appears in relation to the bottom-left corner of the frame, regardless of the size of the frame.

  13. From the File menu, select Save.

    The following image shows the completed reporting application. Your report layout will appear somewhat different depending on the size and location of the components you added to the PRODREP.htm file.

    HTML Composer

  14. From the File menu, select Close.

WebFOCUS