Pivot Grid is also known as Pivot Table. This tool is used to summarize data. It can automatically sort, count total or give the average of the data stored in one table, displaying the results in a second table showing the summarized data. A pivot grid allows you to extract the result from a large, detailed data set. For example:- Sale Quantity and Net Amount for every Pack size for any Company, Season, Item etc.
New Pivot Creation
Right Click on the Sheet Header (Pivot Grid), then Click on New Sheet Object and then, Click on Pivot Grid. See window below:
Fig 1. Pivot Grid
When you select Pivot grid, a window Pivot Grid Properties will appear on your screen.
You will see two tabs on the top of the window. One is for Dimensions/ Measures and another is for General Properties.
In the middle of the window, there are two sets of arrow buttons. One is Single arrows and another is Double arrows. Single arrow is used to transfer the items from Table Field to Selected Field and Double Arrows are used to transfer it from Selected Field to Table Fields.
Use Arrows to transfer your items from Table Fields to Selected Fields. See the window below:
|
1) You can also Double click on the item to transfer it from one table of Fields to another.
2) You can change the header name of every field and the changed name will be visible in the DB-Design.
|
Fig 2. Pivot Grid Properties
Back To Top
Pivot Grid Properties
Pivot Grid Properties could be of two types. You need to categorize your item as per its properties. These properties are as mentioned below:
Dimensions/ Measures
General
1. Dimensions/ Measures
Dimensions/ Measures could be of three types. You need to select your required features from these properties available. These are as follows:
1) Dimensions
Fig 3. Dimension
Dimensions Properties: Dimensions are the alpha numeric header given to a Field Item for representing quantitative display of data.Dimensions are those things you want to track. Such as: Company Name, Gender or Season.
Header
|
You can change the header name of any field from the first properties of Dimension tab. Select any item from Selected Field and click on the Header Tab.
It can be viewed in the DB-Design View as well.
For Example: Pack Size is changed to Size in the below example:
Fig 4 Header
|
Display Area
|
Logic DB- Design allows you to select the area to display your items or attributes. You can change the display area in four types. First one is Filter Area, Second is Row Area, Third is Data Area and Fourth one is Column Area. Select the type you want to display your field and it will be displayed in your desired pattern.
You can view it during the designing that how your Dashboard will look like!
Fig 5. Display Area
|
Visible
|
You can control the visibility of the item fields through this checkbox. Check on the Visible, if you want to Show it and uncheck the button if you don't want to show it.
For example: Bill Date has been marked invisible from the table.
Fig 6. Visible
|
2) Measures
Fig 7. Measures
Measures Properties: Measures are the quantities you want to measure. Such as: Sale Quantity, Net Amount etc.
Header
|
You can change the header name of any field from the first properties of Measure tab. Select any item from Selected Field and click on the Header Tab.
It can be viewed in the DB-Design View as well.
For Example: NET AMOUNT is changed to Total Amount.
Fig 8. Header
|
Aggregation
|
You have the option to decide the type of Aggregation system to be used while tabulating the report. Aggregation is a function where different values are of multiple rows are grouped together on a certain criteria to form a single value. For example: Average Aggregation is applied on the Net Amount (Total Amount) field in the below window:
Fig 9. Aggregation
|
Display Area
|
Logic DB- Design allows you to select the area to display your items or attributes. You can change the display area in four types. First one is Filter Area, Second is Row Area, Third is Data Area and Fourth one is Column Area. Select the type you want to display your field and it will be displayed in your desired pattern.
You can view it during the designing that how your Dashboard will look like!
For Example: Pack Size (Size) is Changed from Filter Area to Column Area and Total Amount is transferred to Data Area from Column Area.
Fig 10. Display Area
|
Visible
|
You can control the visibility of the item fields through this checkbox. Check on the Visible if you want to Show it and uncheck the button if you don't want to show it.
For example: Total Amount (Net Amount) has been marked invisible from the table.
Fig 11. Visible
|
3) Order
Fig 12. Order
Order Properties: You can change the order of the fields through Order Properties. You can keep the items on your desired number through this option.
Sort By
|
You can sort the properties in two ways; ascending and descending order. Sort By option allows you to sort in the desired order.
Fig 13. Sort By
|
Up & Down
|
Up and Down buttons allow you to change the position of the field items upward and downward.
Select the item you want to move and click on up or down from the Order Properties.
Fig 14. Up and Down
|
Back To Top
2. General
General Tab is used to change the General Settings of the Pivot Grid.
Fig 15. General Properties
Visibility: This tab allows you to control the visibility of the field items. Click on checkbox for the items you want to show and uncheck the items you don't want to be shown in your DB-Design.
Show Column Headers
|
Click on the Show Column Header to display the column header of the Grid. If you don't want to show it in the DB-Design, simply uncheck the box.
Your changes will be displayed in the DB-Design View as well.
For example: Pack Size (Size) is placed at Column Header.
Fig 16. Show Column Header
|
Show Column Total
|
Column Total is the total or sum of a particluar columns in the Column of the table. You have the option to Show Column Grand Total seen below or remove it. If you decide to not tick this option, the column grand total will not show at the corner of the table.
|
Show Row Total
|
Row Grand Total is the total or sum of all the values in the row of the table. You have the option to Show Row Grand Total seen below or remove it. If you decide to not tick this option the column grand total will not show at the bottom of the table.
Fig 17. Show Row Total
|
Drill Down
|
DB-Design feature lets you drill into hierarchy to analyze data details on different levels. Logic helps you navigate to the data you want to see, and acts like a filter when you drill down.
|
Show Row Headers
|
Click on the Show Row Header to display the row header. If you don't want to show it in the DB-Design, simply uncheck the box.
Your changes will be displayed in the DB-Design View as well.
Fig 18. Show Row Header
|
Show Column Grand Total
|
Column Grand Total is the final total or sum of all the values in the Column of the table. You have the option to Show Column Grand Total seen below or remove it. If you decide to not tick this option the column grand total will not show at the corner of the table.
Fig 19. Show Column Total
|
Show Other Options
|
Other options include some options such as Top Values, Others, Export, Expand All, Print Preview, Print etc option on top of the Pivot Grid table. If you tick this option these options will be displayed on your screen and if not, it will become invisible from the table.
Fig 20. Show Other Options
|
Grid Settings: Grid Settings in Pivot Grid means settings for the grid or table showing the data and here the user has the option to modify it. It contains many fields including Foreground (Text color), Border Settings, Font Settings, Record Limits, Row Area Width etc.
Fig 21. Grid settings
|
Header Settings: You can modify the header as per one's requirements and it will be displayed as follows. The options available here are Header Text, Foreground (Text Color), Border Settings, Font Settings and the tick to Show Title or this header text.
Fig 22. Header settings
|
Back To Top
After applying all the changes, the final Pivot Grid will look as follows:
Fig 23. Final Pivot Grid
Back To Top
