I've had an Excel vision and don't know if anything like this currently exists. If it does, I can't seem to find it online. I'm looking for a highly configurable way to automatically generate pivot tables and pivot charts based on one or two mapping tables or configuration files.
My current sketch looks at using a population of mapping tables stored in an Access DB that can be called by Excel and selected by a user through a user form. Once the mapping (configuration) is selected, the parameters within are accessed by the VBA to generate my analysis.
This solution would allow for highly configurable analysis based on different mapping tables.
The mapping table would follow something of the following structure and would contain the parameters that would be passed to the VBA procedures and functions to auto-create all the pivots and pivot charts I want in a few seconds.
<tbody>
</tbody>
Is there a better way to arrive towards my final solution of repeatable analysis given that my data will be frequently changing in both structure and field names? I've considered a more SQL heavy, Access focused approach, but I'm not familiar at all with creating dynamic SQL queries through the use of a config file.
I've already created an Excel solution for the end user to map conformed field names to field names from a data import, so half of my work is already done. I just need to develop the auto-generating pivot and chart solution to get what I want.
I also want an open-source solution.
My current sketch looks at using a population of mapping tables stored in an Access DB that can be called by Excel and selected by a user through a user form. Once the mapping (configuration) is selected, the parameters within are accessed by the VBA to generate my analysis.
This solution would allow for highly configurable analysis based on different mapping tables.
The mapping table would follow something of the following structure and would contain the parameters that would be passed to the VBA procedures and functions to auto-create all the pivots and pivot charts I want in a few seconds.
| Worksheet | ID # | Name | Type | Used How | Field Name | Parameter 1 | Parameter 2 | Parameter 3 | Error Message |
| Wksht 1 Name | 1 | BndPrem | Pivot | Report Filter | Bound? | Filter Value | |||
| Wksht 1 Name | 1 | BndPrem | Pivot | Row Label | Bound Date | Width? | |||
| Wksht 1 Name | 1 | BndPrem | Pivot | Column Label | |||||
| Wksht 1 Name | 1 | BndPrem | Pivot | Value | BoundPrem | Summarize as | Show as | ||
| Wksht 1 Name | 1 | BndPrem | Pivot | Calced Field | formula here | ||||
| Wksht 1 Name | 10 | BndPremChrt | PivChart | PivotName | Chart Type | Chart Title | Chart Legend | ||
| Wksht 2 Name | 2 | Pivot | Report Filter | ||||||
| Wksht 2 Name | 2 | Pivot | Row Label | ||||||
| Wksht 2 Name | 2 | Pivot | Column Label | ||||||
| Wksht 2 Name | 2 | Pivot | Value | ||||||
| Wksht 2 Name | 2 | Pivot | Calced Field | ||||||
| Wksht 2 Name | 11 | PivChart |
<tbody>
</tbody>
Is there a better way to arrive towards my final solution of repeatable analysis given that my data will be frequently changing in both structure and field names? I've considered a more SQL heavy, Access focused approach, but I'm not familiar at all with creating dynamic SQL queries through the use of a config file.
I've already created an Excel solution for the end user to map conformed field names to field names from a data import, so half of my work is already done. I just need to develop the auto-generating pivot and chart solution to get what I want.
I also want an open-source solution.
Last edited:
