Report Authoring

This article explains how to author reports using the ReportWORQ Add-in for Excel, based on ReportWORQ data models.

Each ReportWORQ Distribution Job is based on one or more existing source reports, primarily from an Excel workbook (.xslx file). ReportWORQ uses these reports to generate report output based on financial planning systems data or other supported data sources. Report authors can also create these source reports using the ReportWORQ add-in for Excel.

Note: Authoring features provided by the ReportWORQ add-in apply only to authored reports, not reports generated by other means.

After you finish authoring reports, you can create Distribution Jobs to distribute them.

The main topics in this article are as follows:

• Launching the ReportWORQ Add-in

• Authoring or Editing a Report

• Setting Report Filters

Launching the ReportWORQ Add-in

The ReportWORQ Add-in for Excel enables you to create, edit, and manage authored reports.

To launch the ReportWORQ Add-in:

1. Open an Excel workbook (.xlsx file).
   This can be an existing report workbook you want to modify, or a blank workbook.

2. Select the ReportWORQ tab, and then select the ReportWORQ command button.
   If the ReportWORQ tab is not present in Excel, you must install the add-in before you can launch it.
   
   
   If you are already logged in to ReportWORQ, the ReportWORQ task pane appears.
   
   
   
   If you are not logged in to ReportWORQ, the login prompt appears.
   
   
   

3. If the login prompt appears, do the following:

   1. If ReportWORQ uses a port number other than 8300, or is installed on a different computer, provide the correct Web URL for accessing ReportWORQ.

   2. Select Login, provide your User Name and Password as prompted, and then select Login.
      The ReportWORQ task pane appears.

Authoring or Editing a Report

A report workbook contains one or more reports. You can author new reports or edit existing ones.

Tip: Rename worksheets as required before adding authored reports. The reports reference worksheet names to position report output.

To author or edit a report:

1. In the ReportWORQ add-in, select Manage Reports and then do one of the following:

   • For a new report, select Create a new Report.

   • To edit an existing report, select it from the list of reports.
     The Report Builder window appears.
     
     
     
     Tip: You can drag the corners of the Report Builder window at anytime to resize it.

2. If you are creating a new report, from the Select a Data Model list, select the data model that contains data for the report.

3. If you are editing an existing report and want to view the report output before you make changes, select Run Report.
   The report runs, and data rows appear in the Report Builder window, based on how the report is currently configured.

4. In the Field Selection list, specify which fields to include in the report.

5. If you want to filter the data and/or limit data rows, select Set Filters, and then:

   • Set the report filters as needed.

   • Set Max Row Count to the maximum number of data rows you want the report to include.
     
     
     
     

6. In the Options area of the Import Options pane, specify the following:

   1. Location — A cell reference that defines where to display the report output. When the report runs, the top left corner of the report appears at the top left corner of the specified Location. Report output overwrites previous cell contents.
      
      
      
      You can specify a single cell, a cell range, or a named range.
      
      To specify the report Location, do one of the following:

      • Type a cell reference to the Location cell.

      • Select the Select Range icon , specify or select the Location cell, and then select OK.

      • Select a named range from the list. Location is set to the first value in the named range (top left cell).

   2. Preview Rows — The maximum number of data rows to display in the Report Builder window for test purposes.
      Tip: To include all rows, set Preview Rows to 0.

7. In the Variables area, assign a valid value for each variable. The values are applied when the report runs. You can specify variable values as Text or as a Cell Range:
   
   
   
   

   • To specify a variable value as text, select Text and then type the value.

   • To specify a variable value as a cell reference, select Cell Range and then do one of the following:

     • Type a cell reference to the cell that contains the value.

     • Select the Select Range icon , and then select the cell that contains the value.

     • Select a named range from the dropdown list. The variable is set to the first value in the name range (top left cell).

8. Select Run Report.
   A report output preview appears in the Report Builder window.

9. If you want to reorder the columns, select a column header and drag it to its new position.

10. If you want to group the data rows, decide which column to use as the grouping criteria and then drag its header to the grouping area above the headers.
    A group button appears , and the data rows are grouped.
    
    
    
    You can create nested groups by dragging additional column headers into the grouping area. As you drag a header, you can position it before or after other groups to define the nesting hierarchy. The leftmost group button represents the highest level of the hierarchy.
    
    
    
    To release a grouping, select the blue X on its group button.
    Tip: To expand or collapse groups in the report preview, select the carat icons ( and ).

11. If you want to sort the data rows, decide which column to sort by and then select its header.
    An arrow appears beside the header and indicates whether the rows are sorted in ascending order or descending order. Select the header repeatedly to cycle through the sort options: ascending order, descending order, or none.
    You can implement nested sorting by setting the sort order for additional columns. A number appears beside each header to indicate that column’s priority in the sorting hierarchy.

12. If you want to rename the report, select the Rename Report icon at the top of the Report Builder window, specify type the new name, and select OK.

13. Make any required other adjustments, and then select Save & Close.
    The Report Builder window closes, the report runs, and output appears in the specified location.

14. Apply Excel formatting and arrange reports as desired:

    • If you want to reposition a report, select the it from the list of reports and then, in Report Builder, specify the Location and select Save & Close. The previous output is deleted, and fresh report output appears.

    • If you accidentally delete some or all of the report output, select the report from the list of reports and then, in Report Builder, select Save & Close. The report output reappears.

15. Save the report workbook (.xlsx file) to a Report Provider location.

Setting Report Filters

Report filters are a set of grouped and nested data filters which are conditionally applied based on Boolean operators (AND, OR). Only data that satisfies the full set of report filters appears in distributed reports.

Individual filters are grouped in a nested hierarchy. Each group within the hierarchy contains zero or more filter expressions, and/or zero or more subgroups (also called groups).

For the purposes of discussion, filter expressions and groups within a given group are considered child elements of that group. They are considered sibling elements of each other.

Each group has a header, which features controls that enable you to define the group.

Group controls are as follows:

• AND / OR buttons: Boolean logical operators that define how the child elements of the group are evaluated for inclusion:

  • AND — Only data that satisfies ALL sibling elements is included.

  • OR — Data that satisfies one or more sibling elements is included.

• Add Expression button: Adds a filter expression, as a child element of the group.
  To define the filter expression, you select a data field from the table, select a test, and specify a data value to test against (if applicable).
  
  
  

• Add Group button: Adds a new group, as a child element of the group.
  
  

You can add any number of filter expressions and groups. By nesting these elements, you can define precisely what data appears in report output.

Example of a Report Filter

In the following example, report filters are applied to a table of data about candy bar products. The top-level group contains two child elements: a filter expression and a group. The AND operator is selected, so only data that satisfies both child elements appears in report output.

The second level group contains two filter expressions. The OR operator is selected, so data that satisfies either or both filter expressions satisfies this group of filters.
Tip: Filter expressions can reference any field in the data table, even if the field is not selected to appear in the report.

Put into words, the preceding example functions as follows: Include data rows where the product size is greater than 2 ounces AND the brand is equal to either Mars or Hershey.
The figure below shows returned data for this example:

Without report filters, the returned data would be as follows:

To set Report filters:

1. Select the Filters icon.
   The Set Report Filters pane appears, and shows the top-level group header.
   
   
   

2. In the group header, select a Boolean operator (AND or OR) to define how the child elements of the group are evaluated for inclusion.

3. If you want to add a subgroup, select Add Group.
   A new group header appears, one level lower in the hierarchy than its parent group.
   
   
   

4. If you want to add a filter expression to any group, select Add Expression in the group header and then:

   1. From the leftmost list, select the field to which the filter applies.
      
      
      

   2. From the center list, select a relational operator to evaluate the data.
      
      
      

   3. If the rightmost box is present, specify a data value to test against.
      You can type the value, or specify a ReportWORQ variable. The format for specifying a variable is to enclose the variable name in two sets of curly brackets, for example, {{variableName}}.

5. Continue to add filter expressions and/or groups as required until the report filters are fully defined.

6. When you are finished defining report filters, select OK.