New Sample: Form_PivotTableSimulated

Form_PivotTableSimulated

by AD Tejpal

    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 - Super-imposition of a layer of cross tab columns in parental style, if a field is earmarked 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.
  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.

    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 - Step by step auto-expand and auto-collapse:
    ---------------------------------------------------
    Step by step auto-expand or auto-collapse of pivot table can be carried out by clicking pertinent command button.

    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.

    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 their is change of content in last but one grouped column.

Version: Access 2000 file format.

You can find the sample here:  http://www.rogersaccesslibrary.com/forum/topic582.html