Skip to main content
OCLC Support

Refine report results with Report Designer

Learn how to use conditional formatting, input controls, filters, and ranking to refine report results in Report Designer.

Overview

You can analyze data in a standard or custom report using Report Designer. Data analysis methods allow you to refine your results and uncover the data you need to justify a decision or determine next steps.

Apply conditional formatting rules to report data

You can create, apply, and manage conditional formatting rules using Report Designer. Conditional formatting rules can be used to format the following report elements:

  • Columns in a vertical table
  • Rows in a horizontal table
  • Section headers
  • Free-standing cells

Create a conditional formatting rule

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Conditional > New Rule. The Formatting Rule Editor dialog opens.
  3. From the Formatting Rule Editor dialog:
    • Enter a name for the conditional formatting rule.
    • (Optional) Enter a description for the conditional formatting rule.
    • Click the Select Object button (Select Object button) and select what you want to apply the conditional formatting rule to:
      • Select an object or variable - If selected, the Available Objects dialog opens. Select a report object and then click OK.
      • Cell contents - If selected, the conditional formatting will be applied to any cells whose contents match the conditional formatting rule.
    • Select a query operator from the Operator drop-down list.
    • Enter or select the value that triggers the conditional formatting rule in the Operands text field. To enter an operand:
      • Enter a value in the Operands text field.
        Or
      • Click the Select operand button (Select operand button) and then click:
        • Select an object or variable, select a report object from the Available objects dialog, and then click OK.
          Or
        • Select Values, select the value that will trigger the conditional formatting from the List of values dialog, and then click OK.
    • (Optional) Click the Add condition after current condition button (Add condition after current condition button) to add additional conditions to the formatting rule. You can remove a condition by clicking the Delete condition (Delete condition button) button.
    • (Optional) Click Add ... to add an additional conditional formatting rule.
    • Click Format to open the Formatting Rules Display dialog.
      1. From the Formatting Rules Display dialog, set the format that appears when the rule is triggered.
      2. Click OK when the format has been set.
  4. Click OK to save the conditional formatting rule.

Apply a conditional formatting rule

You can apply conditional formatting rules from the Design toolbox or the right-click menu.

 Note: You must create a conditional formatting rule to enable the right-click menu Formatting Rules option.

  1. From the open report, click Design to display the report in Design mode.
  2. Select the report element you want to conditionally format.
  3. Navigate to Analysis > Conditional, and then click the arrow next to Formatting rules
  4. Click the rule you want to apply from the drop-down menu. The conditional formatting rule is applied to the report.

Or

  1. Right-click the report element you want to conditionally format.
  2. Click Formatting Rules > Manage Rules. The Conditional Formats dialog opens.
  3. From the Conditional Formats dialog, select the rule you want to apply and then click OK. The conditional formatting rule is applied to the report.

Manage conditional formatting rules

You can manage conditional formatting rules from the Design toolbox or the right-click menu.

 Note: You must create a conditional formatting rule to enable the right-click menu Formatting Rules option.

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Conditional, click the arrow next to Formatting rules, and then click Manage Rules from the drop-down menu. The Conditional Formats dialog opens.
    Or
    Right-click any report element and then click Formatting Rules > Manage Rules. The Conditional Formats dialog opens.
  3. From the Conditional Formats dialog, you can perform the following actions on available formatting rules:
    1. Sort - Click the up or down arrow button to move a rule higher or lower in the formatting rules list.
    2. Edit
      1. Select the formatting rule you want to edit and then click Edit. The Formatting Rule Editor dialog opens.
      2. From the Formatting Rule Editor dialog, update the formatting rule as needed.
      3. Click OK to close the Formatting Rule Editor dialog.
    3. Duplicate - Select the formatting rule you want to duplicate and then click Duplicate.
    4. Remove - Select the formatting rule you want to remove and then click Remove. The formatting rule is removed from the list immediately.
  4. Click OK to return to the report.

Apply an input control

Input controls are used to filter and analyze report results. You define input controls using text boxes and radio buttons. Input controls are associated with report elements, like tables and section headers, and use control to apply a filter on report elements.

When you select a value for input control, it filters the values in the report element that are associated with input control by the selected value.

Define an input control

 Note: If a button is greyed out, it is not available for use.

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Filters and then click Input Controls. The Select Report Object dialog opens.
  3. From the Select Report Object dialog opens, select the report object you want to assign to the input control and then click Next >. The Choose Control Type dialog opens.
  4. From the Choose Control Type dialog:
    • Select an input control format.
      • Simple Selection - Allows you to apply one value from the input control to the report results. Select a format from the available options.
        • Entry field - Use this format on a dimension or measure report object where you want to be able to directly enter a value name into a text field.
        • Combo box - Use this format on a dimension object to select a value from a list of possible values.
        • Radio buttons - Use this format on a dimension object to select a value from a radio button.
        • List box - Use this format on a dimension object to display all available values from a list box.
      • Multiple Selections - Allows you to apply multiple values from the input control to the report results. Select a format from the available options.
        • Check box - Use this format on a dimension object to select more than one value from a list of alternatives.
        • List box - Use this format on a measure object to create a slider list based on the measure object from which you can select two values from an interval. You must set interval bounds and default values.
    • Set the input control properties.
      • List of values - The values available for the input control. By default, all values for the selected report object are used. If you want to set a custom set of values, click the Select values button (Select values button).
      • Sort List of Values - Allows you to sort a list of values dynamically. Select one of the following sorts from the drop-down list.
        • None (default)
        • Ascending
        • Descending
      • Allow selection of null values - If selected, null values (e.g., [NULL_VALUE]) will be selectable for the Combo box, radio buttons, and check box input control formats.
      • Default value(s) - The default values that the input control uses to filter the associated report element. To select default values:
        1. Select Select values from the drop-down list. The Default values dialog opens.
        2. Select the value or values you want apply by default.
        3. Click OK to close the Default values dialog.
      • Reset on refresh - If selected, the default value of the input control is refreshed automatically when the report is refreshed.
      • Filter operators - Select the query operator that the input control uses to filter the associated report elements from the drop-down list
  5. Click Next >.
  6. In the Assign Report elements panel:
    • Select Entire document to assign the input control as a document input control. Document input controls change the report results across all tabs in the report.
    • Select Current report to assign the input control as a report input control. Report input controls change only the report results in the selected tab.
  7. Click Finish. The input control appears on the Input Controls pane.

Edit an input control

  1. From the open report, click Design to display the report in Design mode.
  2. Click the Input Controls button (Input Controls button) to view all input controls currently assigned to the report.
  3. Locate the input control you want to edit and click the Edit button (Edit button) to open the Edit Input Control dialog.
  4. From the Edit Input Control dialog, update the appropriate fields and information.
  5. Click OK to save your edits.

Remove an input control

  1. From the open report, click Design to display the report in Design mode.

Click the Input Controls button (Input Controls button) to view all input controls currently assigned to the report.

  1. Locate the input control you want to edit and click the Remove button (Remove button). A confirmation dialog opens.
  2. From the confirmation dialog, click Yes to delete the input control.

Filter report data

You can filter reports to limit report results from the Design toolbox or the right-click menu. The data that you filter out remains in the document but is hidden from view.

You can apply different filters to different parts of a report (e.g., limit results in the entire report to a specific product and then limit results in the table to focus on a specific material format or customer type).

Add a filter

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Filters, click the arrow next to Formatting rules, and then click Add Filter from the drop-down menu. The Report Filter dialog opens.
    Or
    Right-click anywhere in the report and then click Filter > Add Filter. The Report Filter dialog opens.
  3. From the Report Filter dialog, click Add Filter. The Available Objects dialog opens.
  4. From the Available Objects dialog, select the report object you want to add as a filter to the report.
  5. Click OK to close the Available Objects dialog. All available values for the report object you selected as a filter are visible in the Report Filter dialog.
  6. Select the value you want to apply to the report.
    • To select more than one value, select the first value, then hold CTRL and select the remaining values you want to include.
  7. Repeat steps 4 through 8 to apply additional filters to the report. When you apply additional filters, they are joined with an AND. Double-click the AND to change it to an OR.
  8. Click OK. The filter and all selected values are automatically applied to the report.

Quick add a filter

  1. From the open report, click Design to display the report in Design mode.
  2. Click to highlight the column on which you want to filter results.
  3. Right-click and then click Filter > Add Filter. The Report Filter dialog opens with the report object already added to any existing filter elements with an AND.
  4. Select the value you want to apply to the report.
    • To select more than one value, select the first value, then hold CTRL and select the remaining values you want to include.
  5. Click OK. The filter and all selected values are automatically applied to the report.

Add a nested filter

Nested query filters allow you to create more complex filter conditions than is possible when you combine filters at the same level.

When you nest filters, you set the order in which they are evaluated. For example, you can return the data given by two query filters joined in an OR relationship (where either one filter condition or the other is true) and then further restrict this data by applying another filter to it. In this case, the two filters in an OR relationship are nested, then compared with the other filter in an AND relationship.

Example: Filter all checked out and returned items from a specific temporary or permanent shelving location by the date the item was checked out or returned.

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Filters, click the arrow next to Formatting rules, and then click Add Filter from the drop-down menu. The Report Filter dialog opens.
    Or
    Right-click anywhere in the report and then click Filter > Add Filter. The Report Filter dialog opens.
  3. From the Report Filter dialog, click Add Filter. The Available Objects dialog opens.
  4. From the Available Objects dialog, select Event Date and then click OK.
  5. Select Between from the Query operators drop-down list.
  6. Click the Add nested filter button (Add nested filter button) to add a nested filter. By default, nested filters are joined with OR. Double-click the OR to change it to an AND.
  7. Click Add Filter, select Item Temporary Shelving Location, and then click OK. To select more than one value, select the first value, then hold CTRL and select the remaining values you want to include.
  8. Select an item temporary shelving location from the available locations and then click > to select.
  9. Click Add Filter, select Item Permanent Shelving Location, and then click OK. To select more than one value, select the first value, then hold CTRL and select the remaining values you want to include.
  10. Select an item permanent shelving location from the available locations and then click > to select.
  11. Click OK to close the Available Objects dialog. All available values for the report object you selected as a filter are visible in the Report Filter dialog.

Edit a filter

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Filters, click the arrow next to Formatting rules, and then click Edit Filter
    Or
    Right-click anywhere in the report and then click Filter > Edit Filter. The Report Filter dialog opens.
  3. Update the existing filter as needed or add an additional filter.
  4. Click OK to save your updates.

Remove a filter

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Analysis > Filters, click the arrow next to Formatting rules. and then click Remove Filter.
    Or
    Right-click anywhere in the report and then click Filter > Remove Filter. The Report Filter dialog opens.
  3. Click Yes in the Web Intelligence dialog to confirm you want to remove the filter. Any filters applied to the report are immediately removed.

Rank report data

You can apply a rank to limit report results to only the top or bottom results from the Design toolbox or the right-click menu.

 Note: Only measures can be ranked in a report.

Add a ranking

  1. From the open report, click Design to display the report in Design mode.
  2. Click anywhere in the report and then navigate to Analysis > Filters > Ranking. The Ranking dialog opens.
    Or
    Right-click anywhere in the report and then click Ranking > Add Ranking. The Ranking dialog opens.
  3. From the Ranking dialog:
    • Select Top and enter the number of values you want to rank to rank the highest values in the column.
      Or
      Select Bottom and enter the number of values you want to rank to rank the lowest values in the column.
       Note: By default, Top is selected and 3 is entered as the number of values when the Ranking dialog opens.
    • Select the measure on which the ranking is based from the Based on drop-down list.
    • Select a dimension from the Ranked by drop-down list to rank by a particular dimension rather than all dimensions in the report.
      • If you specify a ranking dimension, the aggregated values of the Based on parameter, calculated for the dimension, determine the ranking.
      • If you do not specify a ranking dimension, the ranking returns the top/bottom rows in the report based on the measure.
    • Select a rank calculation mode from the Calculation mode drop-down list.
      • Count (default) - The ranking returns the top/bottom records based on the measure specified as the Based on parameter (e.g., the top 3 countries by revenue generated, the bottom 3 year/quarter combinations by revenue generated).
      • Percentage - The ranking returns the top/bottom percent of the total number of records based on the measure specified as the Based on parameter (e.g., if there are 100 records and you rank the top 10%, the ranking returns the top 10 records).
      • Cumulative sum - The ranking returns the top/bottom records, for which the cumulative sum of the measure specified as the Based on parameter does not exceed the value specified in the top/bottom.
      • Cumulative percentage - The ranking returns the top/bottom records, for which the cumulative sum of the measure specified as the Based on parameter does not exceed n% of the total of the measure, specified in the top/bottom.
  4. Click OK.

Remove a ranking

  1. From the open report, click Design to display the report in Design mode.
  2. Click anywhere in the report, navigate to Analysis > Filters, and then click the arrow next to Ranking.
  3. Click Remove Ranking from the drop-down menu. The applied ranking is immediately removed.

Or

Right-click anywhere in the report and then click Ranking > Remove Ranking. The applied ranking is immediately removed.

Visualize data with a chart

You can add a chart to your report to help visualize the report data.

Create a chart

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to Report Elements > Chart and select the type of chart you want to insert.
  3. Click the report to insert your selected chart. You can reposition the chart after you place it initially.
  4. Choose the data you want to display.
    • Click the Available Objects button ( Available Objects pane button ) and drag the desired objects directly onto the chart. The system will automatically determine how to display the data based on the chart type you selected.
      Or
    • Right-click on the chart and then select Assign Data... from the right-click menu to open the Assign Data... dialog. From the Assign Data... dialog, select which data elements you want to display for specific areas of the chart using the drop-down menus. Click OK when you are finished to update the chart.

Turn a table into a chart

You can turn a table into a chart in order to visualize the report data.

  1. From the open report, click Design to display the report in Design mode.
  2. Click the table to select it and then navigate to Report Element > Tools > Turn Into.
  3. Select a chart type from the Turn Into drop-down list. All of the report data is turned into the chart you select.
    • Column Chart
    • Line Chart
    • Pie Chart
     Note: The Turn Into function includes all data from the table in the chart type you selected.
  4. (Optional) Select More Transformations... from the Turn Into drop-down list to open the Turn Into dialog. The Turn Into dialog allows you to specify the data you want to include in the chart.

    From the Turn Into dialog:
    1. Select the chart type you want to turn the table into from the left navigation.
    2. (Optional) Select a chart sub-type. By default, the basic chart sub-type is selected (e.g., Column > Column Chart).
    3. Click X to remove any Category Axis values you do not want to include in the chart.
    4. Click OK to turn the table into a chart.

Style a chart

You can choose from several formatting options, including color palettes, chart styles, and detailed formatting options. The formatting options available depend on the type of chart you selected.

  1. From the open report, click Design to display the report in Design mode.
  2. Navigate to the Formatting tab and then click your chart to select it. A Chart Style tab appears when you select your chart. The Chart Style tab provides the following options:
    • Palette Style - Use the drop-down to apply a different color scheme to your chart.
    • Format Chart Style (Format Chart Style button) - Click this button to select a different chart style (e.g., High Contrast).
    • Format Chart (Format Chart button) - Click this button to open the Format Chart dialog, which provides access to the full range of options for adjusting the appearance of your chart. The options available in the Format Chart dialog depend on which chart format you have selected.
      • You can select which area of the chart you want to format from the Select chart area drop-down list.
      • Click OK to see the results of your changes.

Remove a chart

  1. From the open report, click Design to display the report in Design mode.
  2. Right-click the chart and then select Delete from the right-click menu. The chart is deleted automatically.