Pivot rows and pivot columns

Pivot overview

Pivot contents


Settings of pivot rows and pivot columns

"Pivot rows" and "Pivot columns" are used to specify vertical axis and horizontal axis in pivot table. A black right arrow is displayed after entering a display string in pivot rows or pivot columns. Click the arrow to open the settings dialog of the selected pivot row or pivot column to edit the details.

 

Label

Specify the display name of this group in the pivot table.

If the group is the pivot row (vertical axis), this sets the row header of the pivot table. For example, if the below expression is used, the code for the pivot row's object will be used as the row header, in this case "Customer" like in the following picture.

HOLDER.Code

If the group is the pivot column (horizontal axis), this sets the column header of the pivot table. For example, if the below expression is used, the evaluated value for the pivot column's object will be used as the column header like in the following picture.

ME.EvaluatedValue

 

Group key

Specify the group condition expression to divide the original data into groups. It should be specified for both the pivot rows and pivot columns. The grouped direction is decided by the expression.

<Example>

Order table
Pivot rows = ME.Item
Pivot columns = ME.Customer

<Example>

Order table
Pivot rows = ME.Customer
Pivot columns = ME.Item

<Example>

Order table
Pivot rows = StartOfNextDay(ME.LET,0)
Pivot columns = ME.Item

 

Notification for group key

For Asprova SCP, there are many sites which have the same item, or many objects which have the same code and so on. If these objects are grouped in pivot table, the grouped result may not be the same as the expected result.

If the return value of the group key expression in pivot rows is an object, the code of the object will be displayed. In addition, if the displayed strings of group keys are same, the related cells will be merged. So, different objects with same code can be merged.

For example, items and customers are grouped in pivot table. Both are specified with ascending sort, but the sorted result of customers is not available.

 

For example, if that there are many sites which have the same item, the group key of first pivot row is specified with "ME.Item.ObjectID " and the group key of second pivot row is specified with "ME.Order.Customer", then the item shared by many sites can be displayed in pivot table.

 

For example, if you want to group the item with same item code in different sites. The group key of first pivot row should be changed to "ME.Item.Code" as follows.

 

Summary content

 

Specify the summary row of grouped content.
The setting method of "Summary content" is same with the one of "Pivot contents". Please refer to Pivot contents


 

Sort expression

Specifies the sort rules of pivot rows or pivot columns.

 

Show empty data

It is available when the data type of "Group key" is date type. In case of "Yes", the grouped content is displayed as "0" when it is null.

 

Display start time and display end time

It is available when the data type of "Group key" is date type. Some internal functions, StartOfNextDay, StartOfNextWeek, StartOfNextMonth, EndOfNextDay, EndOfNextWeek, EndOfNextMonth can be specified in the group key. Only the grouped result between, specified "Display start time" and specified "Display end time" can be displayed.

 


HelpNo.:740620
©2016 Asprova Corporation, All rights reserved.