Using the Report Designer

When you have refreshed a report, you can use the Report Designer to perform calculations, add filters, or manipulate the data. Using the Report Designer has the following advantages in comparison to working with Microsoft Excel® directly:

• Reference values are not lost when the report is refreshed.
• The report dynamically re-sizes if the number of returned lines increases or decreases.

Note:

The Report Designer is only available after you have connected to a source and refreshed one or more reports.

To use the Report Designer:

1. Ensure that you have connected to and refreshed the reports that you want to work with. If there are no reports in your workbook, the Report Designer button is disabled. For more information, see Refreshing Reports.
2. Click Report Designer in the Report Design Tools group to open the Report Designer Wizard.
3. Select the report you want to use and, if it contains more than one data range, select the range you want to work with.
4. Add up to twenty columns to the report.
   1. Under the Columns tab, select individual columns or, to select all columns, click Select All Columns. Columns that can be summed have a symbol.
      
   2. [Optional] Add duplicate columns by clicking + Column and selecting a column from the drop-down menu that appears. You can remove duplicate columns by clicking the next to the column name in the list.
   3. [Optional] Reorder the columns in the report by clicking the column name in the list and dragging it into place. The Report Preview is updated instantly.
      
5. Add up to ten rows based on filter conditions to the report.
   1. Expand the Rows tab by clicking the .
   2. Click + Row, then select a column from the Column drop-down menu and select a value from the Equals drop-down menu.
   3. [Optional] Click + Add Condition to add more filters to the row. The Report Preview is updated instantly. Cells that show #.## contain formulas with more than 255 characters. Select Instant formula evaluation in Display Options to retrieve their results.
      
   4. [Optional] Select Show Totals (selected by default) to sum the numeric rows, and select Show Details to show details for each row.
      
6. Customize the layout of your report.
   1. Expand the Display Options tab by clicking the .
   2. Select the display options you want to apply. The following options are available:

      Display Options

      Display Option	Description
      Swap rows and columns	Shows the current column headings as rows, and the current rows as the column headings.
      Show headers	Shows the names of the columns as labels. This option is selected by default.
      Show column names	Shows full details of columns, including the column name, the report name, and the data range.
      Show row conditions	Shows the filter conditions as labels.
      Show report totals	Adds a Grand totals line to the preview grid showing the totals of the summable values on the source report. Row filter conditions are not taken into account.
      Format to 2dp	Truncates the value displayed in the cell to show only two decimal places, without rounding. The actual value remains the same. This option is selected by default.
      Copy values	Changes the copy settings so that when you copy data from the preview area, only cell values are copied and not cell references or formulas.
      Instant formula evaluation	Evaluates complex formulas in the Report Designer. Complex formulas are formulas with more than 255 characters. This option is selected by default.

7. When you are satisfied with the data in the Report Preview, select the data in the grid then copy or drag it into a worksheet. To copy your selected data, click Copy Selection then paste the data into a worksheet.

Notes:

• To clear the whole design and start again at any time, click .
• When an action is in progress (such as copying a selection of data), a spinner appears in the top-left corner of the window and all other actions are disabled.

On your Microsoft Excel worksheet, you can click a data cell within the new report area to see or edit its formula in the formula bar.