Pivot Reports

Type: User Documentation 22-Sep-2023 | Aleksej Širokij

In this article

Overview

Pivots application is a powerful data analysis tool, which allows users to create and share different layouts of data summarizing it on different levels. It alows to present data in charts, export data to excel and create custom calculated fields. Data Layouts can then be shared between team members or exported to Microsoft Excel file for further data manipulations. The module has a collection of pre-defined pivots (which are constantly being updated) access to which can be configured from any application menu.

  • To initiate initial collection of Pivots (in GlobalTemplate) run astp_Pivots_InitiateSolution stored procedure (Contact Pims Administrator to perform this action). 
    i.e. @CopyCostMgmtPivotWebReports = 1

Setup: Pivots

Setup: Pivots application allows Pivot Web Administrator to easily manage pivot reports within domain or copy pivots and layouts accross the domains. 


List Groups and Access

In order for users to be able to access Pivot Reports first a List Group needs to be created, this is used to group specific module or discipline pivots and manage access by list. Permissions to the List Group can be allocated per user, System Group and/or System Roles.

Access by User Groups

In Pims User Group is also an individual user or Group of users. When allocating permissions to individual user (user needs to be part of PivotWeb Users/PivotWeb Administrators role) only then the user will be available in the drop down, tis ensures that dedicated user has an access to the module.

Access by System Roles

You can also allocate the access to a Pivots List Group of Pivot reports contained within that List to a system role, allowing to easier maintain the permissions). 

  • NB: Permissions allocated to Pivot Report List Groups will allow to access Pivot's default layouts within that group


Reports and Layouts

In this tab Pivot Web Administrators can create new Pivots, configure Parameters and allocate permissions to specific layouts (Default ones or created by the users). Custom user created layouts also can be set to be default, allowing System Role members for defined Pivot List group to access them.


Creating New Pivot

To Create new Pivot report click on a "New Report" button

  • Select a Report Group from a list of available groups, enter report title and select the datsource for the report and click "Ok" button.
  • When new report is created 'Default layout' automaticaly is asigned default group permissions
  • Default layout need to be configured, click on "Layout ID" link in "2. Layout" grid  to start building a layout for newly created Pivot Report.


  • Pims Administrator's note
  • Datasources for the Pivot reports can be views and stored procedures available in Pims database
  • Available datasources criteria should be defined to limit for the specific datasources to be picked by Pivot Web Administrators. Criteria should be defined by Pims Administrator in the [atbl_Reporting_PivotWeb_AvailableDataSources]
  • When creating datasources for pivots containing a lot of data, it is recommended to do it via a stored procedure vs. data view, to avoid timeing out issues
Copying Reports from other Domains

You can copy Pivots from different Domains by clicking on "Copy Reports..."

  • Select Domain and then Reports you want to copy to Current Domain
  • Select Group from the current domain to copy selected reports
  • Tick 'Copy All Layout' if you wish to copy non-default layouts

You can also easily filter out Pivot reports from other domain which have not been copied to your current domain using a filter in "Not Copied" column

Copy Layout Permissions

If you have a lot of custom created non-default layouts and want to allocate custom permissions based on specific layout permissions, you can use Copy Layout Permissions function.



Check "Copy All Permission" if you want to copy Editor and Owner type layout permissions. There are 3 types of layout permissions in Pivots:

  • Owner - creator of the layout, can edit the layout, rename and delete it at any time
  • Editor - same permissions as owner, apart from deleting the layout
  • Reader - can not edit the layout, but can load it and create a copy layout for a custom usage, which users can edit then for temselves (being owner's of the layout copy)
Article Menu Settings

There will be applications available in the system, that support the pivot menu access:

To make specific pivot (default layouts) be available for users to run from a dedicated application, select the Pivot and tick the checkbox "Used in App" column for the available article.


  • Developers' note
  • to enable the article to have a menu for pivots the widget code @Render("ArticleBody", ID: "w-reporting-pivotmenu-byarticleid") needs to be inserted into the navigation bar dedicated HTML block, i.e. as in example below:

Pivots

Pivots can be launched from Pivot menu in articles, from a Pivot: Setup application, by clicking on the "Report ID" link, or by clicking on the "Layout ID" link column. The following main areas are available in Pivot application when data is loaded:


List of available Pivot Reports

The drop down list based on permissions configured in the Setup: Pivots application. The loading of data to display can be limited according to specific Pims module permissions. The list displays the List Group Title and the title of the Pivot report.

List of available Layouts

When Pivot report is selected List of available layouts (based on permissions) will become available for the user to choose from. If report layout is opened from an article menu then dedicated layout will be selected automatically and the Pivot will automatically start loading the data. Default layout will be pre-selected when Pivot report selection is changed.

Loading a Pivot Report Data

To load pivot report data, select required Pivot Report and click on "Load Data" button. If report has been accessed from an article Pivot menu the data will be loaded automatically.

Some Pivot's can contain required parameters before the data can be loaded. Such parameters will be indicated by the red "gauge" icon next to Lauots drop down. To enter parameters click on "gauge" icon and then "Paremeters..." and enter parameters required:


After data is loaded, data loaded time stamp indicator will display a time the data has been requested and button will change to "Reload Data". It is possible to load the new pivot request every time in new window, by ticking on the "Load in new window" checkbox.

If you are loading already configured layout, Pivot data will be displayed. If more layout's available you can change them for the loaded data, without relaoding, by selecting a Layout from the available list.

Configuring Pivot Layout

When creating first Pivot Layout and the Pivot data is loaded, click on on the "Field Chooser" button

You can search for required fields and place them in the corresponding Pivot area by dragging the field. It is also possible to move the fields in the Pivot Layout when field chooser is closed.



Changing format of numbers

When you jave vaue fields in your layout, it is possible to chnage the formatt by right clicking on the number field within the Data area and clicking on "Formatting..." menu

In the pop-up window you can indicate the formatting or create a custom format based on suggested examples.

Number fields also support a summary type to be chosen, when placed in the data area:

  • SUM - Sum of (default)
  • AVG - Average of
  • MIN - Minimum of
  • MAX - Maximum of
  • Count - Count of

Other operations you can do with any field in the Pivot report:

  • Alignment (left, center, right)
  • Rename - renaming of the field within a layout (original field name still remains and is visible from the "rename" pop-up window
  • Width - allows to set width for fields, specified in pixels (just entering number)
Creating calculated field

It is possible to create a calculated field, i.e. based on two other fields in the pivot data source. To create a calculated field right click on any not calculated column header of the Pivot report and click on "Create Calculated field..." menu.

  • Provide a Caption for the field
  • Select field type (string, number or URL)
    • To create URL field you need to build a URL link, i.e. You want to have a link to Changes Detail application by clicking on the Change No field in the Pivot:
  • Drag the fields available and use available operators to create a calculated field.
  • The field format can be specified after adding the field to the layout.
  • Click "Save". Field Chooser will appear allowing you to drag the field into corresponding area of the Pivot Report.
Report Totals

It is possible to control the visibility of the Pivot reports totals


To change visibility of the report totals right click in the Pivot area and then select "Report Totals..." menu:


Selected cells Running totals/average

By selecting cells in the pivot report you can see the summary calculated in the bottom right corner of the Pivot Report.


You can also change the summary type to AVG, by clicking on the "SUM" button.

Conditional Formatting

It is possible to create Conditional Formatting rules on Pivot layout. To create conditional formatting right click on the field located in data area.

in Conditional Formatting modal, click on "+" to add a conditional formatting rule (multiple rules can be added)

Under Expression click on "Add Condition" and specify conditional expression by picking from the available list different parameters. Then select appropriate formatting attributes and click "Save" to save the rule. If you want to keep the conditional formatting in the layout, save currently loaded layout or create new layout.


Expanding, Collapsing and Drill-down

If the layout contains data that can be grouped by a row or column expand and collapse functionality will be available on such fields. Users can individually collapse and expand specific field values by clicking on the "triangal" icon



If you want to expand or collapse all rows/columns values for specific field, right-click on it and select "Expand All" or "Collapse All" correspondingly.

It is possible to drill-down to the details of the data summarized in specific cell, by double-clicking on the cell value, or right-clicking on it and selecting "Drill-down" menu.

You will then see a pop-up with the details of the data contributing to the summarized value:

Layouts menu

Layouts menu is available by right-clicking in the Pivot area or by clicking on the "gauge" button on the right of the Layouts drop down list, and then selecting "Layouts" menu.

Saving New Layout

To save new layout, when you have modified the layout according to your needs, click on "New..." menu button from Layouts menu. In pop-up window specify Layout title, and choose other options:

  • Save current Report Layout parameters in New Layout - if you have loaded a pivot report data with specified parameters and you want to store these parameters within the layout being created (i.e. specifying specific Archive ID to reffer to) then tick this checkbox.
  • Copy Charts - If the layout you are saving, contains charts, or you are creating a new layout from the existing layout which has charts configured you can copy these charts into the newly created layout
Saving/Renaming Current Layout

If you are the owner of the layout loaded (in case of default layouts) or you are a Pivot Web Administrator, or in case of custom layout where you have been given permissions as editor, you will be able to Save the changes to current layout. Also same permission rules will be applied when Renaming the Pivot Layout.

In case of no permissions, an error message will be displayed specifying that you do not have permissions to save changes to current layout.

Restore to Current Layout

If you have loaded a layout, and started to modify it, but then decided to return to initial layout, without relaoding the data, you can click on Restore to Current Layout.

Clearing Layout

It is possible to clear the layout and start creating layout from scratch.

Sharing a layout

Layouts can be shared between team members. To Share a layout click on "Share" menu button from "Layouts" menu. Only owners and Pivot Web Administrators can share layout. Custom Layouts can be allocated to specific user or user groups.

When sharing a layout you can specify Access level: Reader or Editor.

Charts

It is possible to create charts from the selected values in the Pivot Report. You can either select values individualy by clicking in the cells, or clicking on the column or row to select all row or column values correspondingly. When values are selected, you can right click in Pivot area and tick the Checkbox "Show Chart"

There are different options available for the chart. To access Chart options rigth-click in tha chart area:

Adding / Changing Chart Title

To add or edit chart title, click on "Change Title" menu item in Chart options menu. You will be prompted to provide a Title in the browser prompt window. 

Switch Value Axis

This option allows you to swicth value axes so that selected rows or columns data switch to X or Y axis correspondingly, without requiring you to change the layout.

Chart Types

Currently the following Chart types are supported in Pims Pivot Reports module: Bar, Stacked Bar, Line, Pie, Doughnut. To change chart type go to Chart Type menu item and select required chart type in the sub menu.

Copying / Exporting / Printing Charts

It is possible to quickly Copy chart via clip board and insert for example in the email message or word document. Click on "Copy Chart To Clipboard" in chart options menu, then go to dedicated application and click Ctrl+V on your keyboard.

It is also possible to export chart to available formatts or print it. Locate the menu button on the chart right top corner and pick the required format or click on "Print" button. You will be prompted to save the file or presented with a Print dialog in the case when printing is selected.

Saving Charts

It is possible to save charts within the layout and later select them when such layout is loaded. If Layout starts to be modified, the selection of the cells used in a saved chart need to be revized or layout need to be reloaded to initial state. To save a chart in the layout, configure chart options, and then click on Chart menu item and select "New..."

You will be prompted to provide a chart title and then click "Save". It is also possible to reconfigure the chart (i.e. switch value axis, visibility of value labels, change title) and save the new chart configuration into already saved Chart. Also Chart title can be renamed, and chart configuration can be deleted using the corresponding menus.

Loading Saved Charts

If the loaded layout containes saved charts, you can load them by selecting a chart from a drop down and clicking on Load Chart button.


In this article