Search:

Sites

CC Pivot 2.0

Getting Started

Using CC Pivot

Change Log

CreatePivot

To create a pivot, the simple explanation is to click the "New" button, select your table, and fill out the info. Similarly editing a pivot is accomplished by selecting the pivot to edit and clicking the edit button. Perhaps, however, a more detailed explanation would be helpful

Creating / Editing a Pivot

Explaining the features and functions of CC Pivot

All of the functions of a pivot are explained below, and while we go through them in a semi-orderly fashion, they can generally be edited or entered in any order.

1. Table

This is a simple pop-up menu with all the table occurrences you have integrated in the system. If you do not see the table occurrence in the list, you may need to go to the settings layout via the "Settings" button at the upper right corner of the home screen and click the update button on the "Layouts" tab. Selecting your data table is an important and necessary step as that will determine the context for the rest of the pivot table.

2. Style

Another basic pop-up menu, this will determine the style of your pivot table. There are a couple things to note, however. First, unlike in the prior versions of CC Pivot you will have to refresh the pivot to see the effect of changing the style. This was done to improve speed. Second, the styles that are included by default are only meant as a starting point. You as a developer have the power to add as many styles as you want. This is done on the CSS & Javascript tab of the settings screes. The styles are simple cascading style sheets (CSS) and if you are unfamiliar with this form of formatting, there are many fantastic resources online to learn more.

3. Totals

This checkbox will determine weather grand totals are generated for all data fields and slicers reported on. It is simply a toggle, however be aware that including totals can increase the time a pivot takes to generate.

4. Record Tracking

This is a checkbox to include record tracking. This is the feature that allows you to click on a cell in the pivot table and go to a layout with the records that make up the found set determining the content of that cell. This feature requires that a ID field and layout be set for the table the pivot table is based on in the setting screen.

5. Slicer Fields

This is simple list of the "slicers" or summarization fields that your pivot table will use. You can drag any field from the "Fields Available For Pivot" to an empty row to add it to the slicer list. Once a slicer is added or clicked on for selection, the slicer detail will be highlighted in the slicer detail section mentioned later.

6. Data Fields

Like slicers, this is simple list of the "data" or reporting fields that your pivot table will use. You can drag any field from the "Fields Available For Pivot" to an empty row to add it to the slicer list. Once a data field is added or clicked on for selection, the detail will be highlighted in the data detail section mentioned later.

7. Filter Fields

Filters are like a built in find for your data, You can drag any field from the data portal to the filed sell to set up a filter for that field, just like slicers and data fields Again this is simple list of the "filter" or search fields that your pivot table will use. You can drag any field from the "Fields Available For Pivot" to an empty row to add it to the filter list. Once a data field is added or clicked on for selection, the detail will be highlighted in the data detail section mentioned later.

8. The "Update" button.

Whenever you change any of the detail of the pivot table, you will need to update your pivot table to display the changes. There are two sorts of update that the button could perform, either a display update or a data and display update. We will attempt to determine the correct one for you when you click the button. If you have only changed settings that would affect the display of the pivot table, like the format of a data field for example, then we will only re-calculate the format portion of the pivot table. We will not go and fetch the data over again first. However if you change the data level settings, like adding a field or a filter, then we will fetch the data again before re-drawing the pivot table. If you ever want to update both the data and the display in spite of what the system believes needs done, just hold down the "Shift" key when clicking the update button.

9. The "Save" button.

This button saves your pivot and stores the current pivot report under the name entered in the title to the right.

10. The "Print" button.

The "Print" button will open the pivot table in your default browser for printing. Since we are using a web data viewer to display the pivot table, this is the most straightforward way of printing. This also let's us provide the pivot table in a standard HTML table, which means you can easily take the pivot table from your browser and copy and paste it into any other tool like Excel, for further editing.

11. The "Excel" button.

We have a simple button that you can use to export your pivot to Excel. Simply click this button on the bottom bar and type the name of the file you want to create. Click Ok and an excel file will be created on your desktop.

Slicer Detail Section

12. Slicer Title

This is the title used in the pivot for the column or row header as a label. While we will attempt to make a guess at a useful title, you can change this to anything you wish.

13. Slicer Position

This is simply a number field that determines the sort order for the slicers. Be aware that sort order is also order or evaluation and therefore changing the sort order can produce very different results. All the sales grouped by state, then salesperson is very different that sales grouped by salesperson then state.

14. Slicer Column / Row

This is a button to click that determines weather the slicer is a column or a row in the pivot table. All rows and columns need not be grouped together in the sort order, the pivot tool will handle getting them together on processing.

15. Slicer Sorting

There are three options for sorting the data in your rows and columns, ascending descending and custom. Ascending and descending should be relatively straightforward, but custom could use a little more explanation. When you click to select custom you should see a pop up window that looks like this:

Custom Sort Dialog

On this screen there are two areas, the first is a list of stock sort lists, Days of the Week and Months of the Year, for the most common and predictable custom sort options based on dates. The other section is a field to enter a custom sort list. This will be have just like a sort based on a value list in FIleMaker. Enter your sort criteria return separated in the field and the values will sort in that order. You could for example enter all the regions in your sales area, all the salesmen in your organization, or any other list you can think of. Additional values in the data that are not part of the custom sort list will sort in Alpha order at the end of the custom values.

16. Slicer Advanced Options

The slicer advanced options has a few new features well worth looking at. First of all you will see one of two screens when you select advanced options based on the field type of the slicer you are working on.

If you select a date field you will see a screen that looks like this:

Custom Sort Dialog

On this screen there are two sections, "Grouping & Formatting" and "Required Entries".

The Custom Grouping field in the first section is to summarize data by a subset of the data in the slicer field and is only available in fields of type date. Often with dates, you do not want to organize your data day by day, but rather by day, month or year. WIth custom grouping you can now accomplish that easily. Select month from custom grouping and all your data data will be grouped by month. Now you do not need to create fields just for organizing data by categories of data data. CCPivot does all that work for you.

Custom formatting is for for formatting the display of the text shown in the pivot table. The format field is for formatting the slicer data for display, any format supported by the java formatter specification is acceptable. To read more on this specification go here.

Required entries has two sections as well, one is a drop down allowing you to choose from a stock list, the other is a text field allowing you to enter any list you choose. Both however accomplish the same thing. And values entered (or chosen) will have column or rows generated for them, even if no data exists in the found set. Sometimes reporting on the missing values is the most important thing you can do. Enter all the sales region for a sales region slicer, and even sales regions with no sales for the time reported will show up. Enter days of the week and you will not miss that there were no sales on Friday just because the row was missing.

Text and number fields have all these same options as date fields, except the custom grouping.

Data Detail Section

Much like the slicer detail section, if you add or click to select a data field, you will see the data detail section which looks like this:

Data Detail

* Title

This is the title used in the pivot for the column or row header as a label. While we will attempt to make a guess at a useful title, you can change this to anything you wish.

* Position

This is where you can enter a number to change the sort order of the data or display in the pivot table. Data display order need not contain sequential numbers and can contain decimals, so you can easily re-position data fields in any order.

* Operation

This is the performed on the data to aggregate it for the pivot table. Currently the system supports SUMmary, AVErage, COUNT, MINimum and MAXimum.

* Custom Formatting

Custom formatting is for for formatting the display of the text shown in the pivot table. The format field is for formatting the data for display, and any format supported by the java formatter specification is acceptable. To read more on this specification go here.

* Language

This is the language used to evaluate the formatting of the data field.

Filter Detail Section

Much like the slicer and data detail sections, if you add or click to select a filter field, you will see the filter detail section which looks like this:

Data Detail

* Operator

The operator is the relationship the expression will have to your data. This uses standard SQL matching so you have the standard options like "=" or "≥" and whatnot. but you also have options such as "Like".

A full list of the operators is:

=, >, >=, <, <=, <>, LIKE, NOT LIKE, IS NULL, IS NOT NULL

* Expression

Expression is similar to the find criteria in FileMaker. If you choose "=" as the operator and then "20" for the expression, CCPivot will find all records where the data in your selected field is 20. Remember field types matter, and SQL is user to perform the find on your data so SQL matching rules apply. The part of the SQL query being built here is the "where" section.

For "IS NULL" and "IS NOT NULL" the expression is ignored.

If you need help understanding how the "where" part of a SQL query works there is a pretty good reference here:

http://www.w3schools.com/sql/sql_where.asp

* Logic

Logic makes it so you can choose to have your filter criteria function like nested finds of a sort. All the "And" criterial will be grouped together, then all the "Or" criterial will be applies in their own finds.

17. Fields Available For Pivot

In this section there is a list of fields in the base table and all related tables for dragging into any of the above concepts: slicers, data fields or filters. If you do not see one of your fields here ensure your relationships are correct and that the tables show up in the settings screen.

[Previous] [Next]

Edit - Print - Search
Page last modified on March 14, 2013, at 08:25 AM