New Page 1

Form_PivotTableSimulated
Explanatory Notes
Updated On 26-Apr-2012: Cell-wise graded collapse or expansion by double clicking incorporated.

This sample db demonstrates a simulated pivot table, displayed on access subform in datasheet view. It is based purely upon native access queries and VBA, getting rid of traditional dependence upon OWC i.e. Office Web Components (slated for deprecation).

    2 - Incorporation of features typical of excel pivot table:
    --------------------------------------------------------
    An attempt has been made to incorporate basic features considered typical of an excel pivot table as listed below:

2.1 - Being a datasheet, column headings remain in perpetual view, affording convenient vertical scrolling.

2.2 - Suitable number of grouping columns at left are frozen so as to facilitate convenient horizontal scrolling.

2.3 - Uncluttered display (suppressing duplicate contents in grouping columns).

2.4 - Full choice of filter fields (equivalent to page fields in excel)

2.5 - Appropriate cross tab columns if a field is selected as column field. (Only one field can be selected as column field)

2.6 - Toggling of data orientation between column and row styles as per user choice.

2.7 - Manipulation of ordinal position of columns - as desired by user - by clicking up or down command buttons in field list.

2.8 - Run time insertion of unlimited number of calculated field columns - as desired by user. Calculated field names and the underlying expressions can be edited as and when desired. The results of any such action get displayed promptly in the pivot table.

2.9 - Run time setting / editing of formatting for different data fields - as desired by user. The results of any such action get displayed promptly in the pivot table.

2.10 - Run time hiding / un-hiding of grouping and data fields as desired by user (By clicking Hide check box in fields list).
2.11 - Fields List and Filter Check-List can be hidden un-hidden via command button. The filter list gets displayed only if IsFilter check box against one or more fields is in selected state.
2.12 - If desired by the user, filter field columns get displayed in the pivot table as well.
2.13 - Cell-wise graded collapse / expand action in the pivot table can be performed by double clicking on a column PRIOR to the last one amongst grouping columns.

    Note (see item 2.5 above):
    --------------------------
    Column field is meant to be the last one amongst grouping fields. In the fields list, when the user selects IsColumn check box, the field in question, if not already the last one, moves to last position amongst non-data fields and gets highlighted in maroon color. If any other field has its IsColumn check box in already selected state, the same becomes de-selected.

    3 - Some Interesting Extra Enhancements:
    ------------------------------------------
    While building the simulated pivot table certain interesting enhancements have been incorporated as follows, so as to make it still more versatile and user friendly:

    3.1 - Generic Data Source:
    ---------------------------
    The arrangement for depicting the pivot table is completely generic. The developer is not required to drag any fields on to the form. All that needs to be done is identification of data source (which can be the name of a table or saved query). This is done conveniently via look-up list on a combo box. On selection of new data source, corresponding pivot table gets displayed via unbound controls on the subform. Adequate number of such controls have been provided so as to suit up to 300 columns.

    3.2 - Column-wise auto-expand and auto-collapse:
    ---------------------------------------------------
    Step by step sequential auto-expand or auto-collapse of pivot table, column-wise, can be carried out by clicking pertinent command button. (Non-sequential random hiding / un-hiding of pivot table columns can be carried out by clicking Hide check box in fields list).
    Note: Cell-wise graded collapse or expansion can be done by double click (See item 2.13 above).

    3.3 - Flexible Run Time Grouping By Date Type Fields:
    --------------------------------------------------------
    Source data originally entered as simple dates can be depicted in various grouping styles, e.g. ByYear, ByQuarter, ByMonth etc. The user can play with date grouping style, selectable via combo box at bottom right. The results of any such action get displayed promptly in the pivot table. (For this feature to be in force, Calc column against date type field in fields list subform should carry the value "DtGrp")

    3.4 - Color Highlights For Pivot Table Rows Depicting Sub-totals:
    ------------------------------------------------------------------
    For added convenience, sub-total rows in pivot table are highlighted in color. Wherever more than one such rows happen to be adjacent, alternate color highlights are provided - for improved legibility.

    3.5 - Color Highlights In Fields And Filter List:
    -----------------------------------------------
    In fields list, distinct color highlights are provided for fields identified for hiding, filtering or to serve as column field. In filter check list, excluded items are highlighted.

    3.6 - Reminder Content In Outer Columns:
    ----------------------------------------------------
    During vertical scrolling in traditional pivot tables, the topic displayed in outer-most column at left can go out of view, depending upon the range of contents in grouped columns at right. For better convenience, pivot table in this sample db provides for repeat display of outer column content, whenever there is change of content in last but one grouped column.