Report Layouts

Last modified

Overview

The Filtering options described within Productivity Tips apply to both Grid and Pivot Table layouts.  However, there are a few exceptions detailed below.

Grid Layout

When selecting Grid from Step 1 of the report wizard, the output is displayed in a standard grid layout.

GridLayout.png

Calculated Columns

It is possible to insert a calculated column into a report. Right click on the heading and select Insert Calculated Column.

GridLayout_CalculatedColumn.png

The Insert Calculated Column option uses the Expression Editor.  (See Expression Editor)

To delete an Inserted Calculated Column, right click on the customised column heading and select Delete Column.

GridLayout_CalculatedColumn1.png

Formatting

It is possible to change the column format into a report. Right click on the heading and select Formatting.

GridLayoutoptions.png

The following options are available:

FormattingOptions.PNG

General

This displays the cell value in a no specific format.

Percent

This multiplies the cell value by 100 and displays the result with a percent symbol (%).

.2dp

This displays the cell value with two decimals.

.4dp

This displays the cell value with four decimals.

Money

This attaches to the cell value the currency symbol (£).

Date

This displays the cell value as date.

This option can be used for both grid and pivot report.

Pivot Tables

When selecting Pivot from Step 1 of the report wizard, the output is displayed as a pivot table.

PivotTable.png

Multiple years

It is possible to view your data in a cross tabular format, e.g. for each of the last 3 years.  To achieve this, the report must contain a field with an associated date range, e.g. Tax Year, Accounting Period, Accounting Year etc.

From the Ribbon amend the Parameters Start and End Dates accordingly.  Click Run to refresh the report.

PivotTable_Parameters.png

TIP:  If the Parameters panel is not present, a date driven field is missing from the report.

Variance Column + % Variance Column

It is possible to insert either a variance or % Variance column into the Pivot Table. Right click “Data Headers” and select Show Field List.

PivotTable_Variance.png

Note:  The Data Headers description is only returned where four or more fields are used within the ‘data’ area on the Pivot Table.  Where three or less fields are used, the actual field names are returned.

The PivotGrid Field List appears.

PivotTable_FieldList.png

In the Data Area, right click the column to apply the variance and select either Add Variance Column or Add % Variance Column.

Click the PivotTable_Close.png to close the form, the Variance Column now appears within the report.

PivotTable_VarianceReport.png

TIP:  To ensure that the Variance Column appears within the report; the Pivot Table must span multiple years.

Renaming the Data Columns

Where three or less fields are used within the Data area, double click on the field Name, rename and remove focus by tabbing or clicking elsewhere.

PivotTable_RenameDataColumns.png

To rename a column used within the Data area of a Pivot Table where four of more fields are used, hover over Data Headers, the fields are displayed.  Position the mouse over the field to rename until the cursor changes to a line (similar to I).  Rename and remove focus by tabbing or clicking elsewhere.

To rename a column used within the Data area of a Pivot Table where four of more fields are used, hover over Data Headers, the fields are displayed.  Position the mouse over the field to rename until the cursor changes to a line (similar to I).  Rename and remove focus by tabbing or clicking elsewhere.

pivotTable_RenameColumnInDataArea.png

Deleting the Data Columns

It is possible to delete an inserted column from the Pivot Table using Show Field List or Delete Column option.

Show Filed List

Right click Data Headers and select Show Field List.

PivotTable_DeleteDataColumn.png

In the Data Area, right click on the relevant column name, select Delete Column.

Click the PivotTable_Close.png to close the form, the inserted column has been removed.

Delete Column

Position the mouse over the field until the cursor changes to a hand HandCursor.png. Right click and select Delete Column option.

DeleteColumn.png

Hiding Columns

It is possible to hide fields within the Pivot Table.  E.g. you may have added an additional column for the purposes of adding two existing columns together using the expression editor.  Once the additional column is present, there may not be any reason to display the original two columns (but they are required to remain for calculation purposes):  Right click Data Headers and select Show Field List.

PivotTable_DeleteDataColumn.png

If the fields are present within either of the Filter, Column or Row Areas, drag the fields into the Hidden Fields box.  Where the fields are present within the Data Area, first drag the fields to the Filter area and then onto the Hidden Fields box.

Click the PivotTable_Close.png to close the form, the relevant columns are now hidden from the report.

 

Page statistics
575 view(s) and 16 edit(s)
Social share
Share this page?

Tags

This page has no custom tags.
(not set)

Comments

Attachments

 

 | Cookie Policy | Copyright | Privacy Policy Terms of Use | Contact Us |