Dynamic Local Members for Spreadsheet Add-in Formula Maintenance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users face difficulties in efficiently updating and maintaining formulas in spreadsheet applications when columns or rows are changed, leading to errors and incomplete data display when accessing business information from external databases like OLAP cubes.
Innovation Solution
A dynamic local member system is introduced, where the add-in module automatically translates and updates spreadsheet formulas into multidimensional expressions, ensuring that references point to correct data sources and adapt to changes in the spreadsheet structure, using MDX language and metadata models to manage data hierarchies and dimensions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If users manually enter and maintain formulas in spreadsheet cells to display business information, then the spreadsheet can show calculated results, but the formulas become difficult to update and maintain when columns or rows are changed
Solution Approach 1:
The system introduces an intermediary layer (the add-in module with local members) that sits between the user and the spreadsheet formulas. When a user enters a formula, the system automatically translates it into a local member reference. This intermediary handles all the complexity of formula maintenance, updating, and validation, while the user simply interacts with familiar spreadsheet syntax. The local member acts as a mediator that automatically adapts when spreadsheet structure changes, eliminating manual formula maintenance while preserving user-friendly operation.
2Reliability
If users enter identifiers into spreadsheet cells to define business information, then data can be retrieved from external databases, but errors and incomplete data display occur when spreadsheet structure changes
Solution Approach 1:
The system implements dynamic local members that automatically adapt to spreadsheet structure changes. Instead of static cell references that break when rows or columns are moved, the local members are dynamically updated by the system. When the spreadsheet structure changes, the add-in module detects the change and automatically recalculates the local member references to point to the correct data sources. This dynamic adaptation maintains data display accuracy regardless of structural changes, while preserving full spreadsheet flexibility.
3Productivity
If formulas are defined using spreadsheet application to calculate values from multiple cells, then calculated results can be displayed, but it is difficult to update results when columns and rows are changed
Solution Approach 1:
The system performs preliminary action by automatically translating and pre-configuring formulas as local members at the time of initial creation. Instead of requiring users to manually update formulas later, the system prepares the formulas in advance with proper references to data sources. When the spreadsheet is refreshed or reloaded, the pre-configured local members automatically update their references without requiring user intervention. This preliminary setup eliminates subsequent update time while maintaining fast report generation.
Data Source
AI summary
According to some embodiments, a system, method, means, and/or computer program code are provided to facilitate use of a spreadsheet application to access information stored in a multidimensional data source. Some embodiments include determining, at an add-in module, that a local member is to be attached to an attachment element of a report generated based on the multidimensional data source. The add-in module may receive a refresh indication from the spreadsheet application and dynamically update a reference in a spreadsheet cell based on a change associated with the attachment element.