System and method for dynamic data access in a spreadsheet with external parameters   

Abstract: In one aspect, the present invention relates to utilizing a spreadsheet by defining a parameter external to the spreadsheet and associating the parameter with the spreadsheet to define a parameterized workbook. In one embodiment, this utilization further comprises storing a location of the spreadsheet and storing the name of the parameter in the same storage module as the location. In another embodiment, it includes defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet. In another embodiment, this utilization further comprises receiving a value for the parameter and generating the result based at least in part on the value and the spreadsheet. In another embodiment, it further comprises associating a type with the parameter. The type can define a range of values or attributes associated with the parameter.

CROSS-REFERENCE TO RELATED APPLICATIONS

This application is a divisional of parent U.S. patent application Ser. No. 10/193,015, filed Jul. 11, 2002, entitled “System and Method for Efficiently and Flexibly Utilizing Spreadsheet Information,” the entirety of which is incorporated herein by reference.

TECHNICAL FIELD

This invention relates generally to computer-based systems and methods for data processing, and more particularly relates to systems and methods for manipulating data, for example, spreadsheet application programs.

Generally, spreadsheet application programs are used to automate numerical and symbolic calculations for business, financial and scientific uses. Spreadsheet programs are the tools of choice for many business and analysis tasks because they combine a very usable graphical interface with a simple formula language that allows non-programmers, within the limits of the simple formula language, to create computational models.

Spreadsheet programs visually present numeric and non-numeric data in a two-dimensional grid for easy assimilation by the reader. Each element of the two-dimensional grid is referred to as a cell. A cell can contain either a data value, or it can contain a formula that calculates a new value based on the values of other cells. Spreadsheet cells that contain formulas are automatically recalculated when there are changes to the other cells that the formula depends upon. This mechanism allows a spreadsheet user to perform what-if scenarios only by modifying cell values and viewing or saving the effects of the changes.

Individual two-dimensional spreadsheets can be organized into a larger entity known as a notebook or workbook. The terms spreadsheet or worksheet will be used interchangeably herein, as will the terms notebook or workbook.

When worksheets are grouped together to form a workbook, the workbook and all of its worksheets are stored together as a single file (i.e. the workbook becomes the unit of storage and transfer when moving data between the program\'s memory space and disk storage). Formulas stored in worksheet cells can reference other cells that are in worksheets in the same workbook and/or cells that are in worksheets in a different workbook. When formulas refer to a different workbook, however, known prior art spreadsheet programs cannot automatically recalculate formula values unless that other workbook has also been loaded into the program\'s memory space. These prior art spreadsheet programs do not allow multiple copies of the same workbook to be loaded into memory simultaneously and they do not allow multiple what-if scenarios to be calculated simultaneously.

In existing spreadsheet programs, data values can be stored in cells either by user input (directly or through a user-input formula) or by the user associating the cells with an external data source, such as a query to a database. When cells are associated with an external data source, the user of the program can control how often the external data source should be checked for changes. However, no mechanism exists to automatically detect when data referenced from the data source has changed and to recalculate the spreadsheet if, and only if, such a change has occurred.

Spreadsheets in prior art spreadsheet programs perform at most a single calculation with fixed inputs to arrive at a fixed set of answers. Changes in external data sources can affect the results of computations performed by a spreadsheet, but the computation itself is fixed. Prior art spreadsheet programs make the user vary cell values if the user wants to perform different calculations, including what-if scenarios.

Some prior art spreadsheet programs support various facilities for programmatic control over the spreadsheets to automate spreadsheet tasks. Some of these programs implement a macro recording and playback facility, which allows repetitive tasks to be automated by recording the user\'s actions and later replaying them. Some programs publish application programming interfaces (APIs) that allow computer programs to be written to manipulate the spreadsheets or to extend the user interface of the program. Because programming skills are required to use these application programming interfaces, they are not used by most spreadsheet users.

Some prior art spreadsheet programs cache internally the results of intermediate calculations (cell values) to improve the speed of recalculation. Prior art spreadsheet programs support re-execution of external data queries either when manually requested, on a periodic basis while the workbook is loaded into the programs memory, or whenever the workbook is opened. None of these methods are optimal, and none of these methods are effective when the spreadsheet program is not running.

SUMMARY

In one aspect, the present invention relates to a method for utilizing a spreadsheet. The method comprises defining a parameter external to the spreadsheet and associating the parameter with the spreadsheet to define a parameterized workbook. In one embodiment, the method further comprises storing a location of the spreadsheet and storing the name of the parameter in the same storage module as the location. In another embodiment, the method further comprises defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet. In another embodiment, the method further comprises receiving a value for the parameter and generating the result based at least in part on the value and the spreadsheet. In another embodiment, the method further comprises defining a format for the result. In another embodiment, the method further comprises associating a type with the parameter, the type defining a range of values.

In another embodiment, the method further comprises associating a type with the parameter, the type defining attributes associated with the parameter. In another embodiment, the method further comprises defining a formula within the spreadsheet using the parameter. In another embodiment, the method further comprises binding a value of the parameter to an instance of the associated spreadsheet. In another embodiment, the parameterized workbook is a first parameterized workbook, and the method further comprises defining a second parameterized workbook and referencing a result from the second parameterized workbook in a formula in the first parameterized workbook. In another embodiment, the method further comprises storing, separate from the spreadsheet and after the spreadsheet closes, an intermediate value used in a calculation for the spreadsheet and associating the intermediate value with the spreadsheet and a value of the parameter used to calculate the intermediate value.

In yet another embodiment, the method further comprises automatically calculating a result associated with the spreadsheet without opening the spreadsheet by using the stored intermediate value. In another embodiment, the method further comprises distributing calculations of the spreadsheet among a plurality of computing devices. In another embodiment, the method further comprises distributing calculations of the spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the spreadsheet. In another embodiment, the method further comprises distributing the calculations of the spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the parameterized workbook.

In another aspect, the invention relates to a system for utilizing a spreadsheet. The system comprises a spreadsheet and a list of parameters. The spreadsheet comprises one or more associated formulas. The list of parameters is associated with the spreadsheet. The list of parameters is external to the spreadsheet and one or more parameters within the list are referenced in the one or more associated formulas of the spreadsheet. In one embodiment, the system further comprises a storage module to store a location of the spreadsheet and store the name of each parameter in the associated list of parameters. In another embodiment, the system further comprises a list of results associated with the spreadsheet. In another embodiment, the system further comprises a calculation module to receive a value for a first parameter within the list of parameters and generate a first result based at least in part on the value and the spreadsheet. In another embodiment, the system further comprises an instantiator module to bind a value of a first parameter within the list of parameters to an instance of the spreadsheet. In another embodiment, the spreadsheet is a first spreadsheet and the system further comprises a second spreadsheet having one or more formulas referencing a result from the first spreadsheet.

In another aspect, the invention relates to a method for utilizing a plurality of spreadsheets. The method comprises defining a parameter and defining a set of one or more selection rules to select a first spreadsheet from a plurality of spreadsheets based at least in part on a value of the parameter, thereby defining a virtual workbook. In one embodiment, the method further comprises selecting a first workbook from the plurality of workbooks based at least in part on a value for the parameter and the set of one or more rules. In another embodiment, the method further comprises referencing the virtual workbook in a created spreadsheet as a substitute for a second spreadsheet in the plurality of spreadsheets. In another embodiment, the method further comprises defining a result external to the virtual workbook, the result referencing one or more cells within each spreadsheet in the plurality of spreadsheets.

In another embodiment, the method further comprises receiving a value for the parameter and generating the result based at least in part on the value and the first spreadsheet. In another embodiment, the method further comprises defining a format for the result. In another embodiment, the method further comprises associating a type with the parameter, the type defining a range of values. In another embodiment, the method further comprises associating a type with the parameter, the type defining attributes associated with the parameter. In another embodiment, the method further comprises defining a formula containing the parameter within a spreadsheet in the plurality of spreadsheets. In another embodiment, the method further comprises binding a value of the parameter to an instance of the selected first spreadsheet. In another embodiment, the virtual workbook is a first virtual workbook and the method further comprises defining a second virtual workbook and referencing a result from the second virtual workbook in a formula in the first virtual workbook. In another embodiment, the method further comprises defining a parameterized workbook and referencing a result from the parameterized workbook in a formula in the virtual workbook. In another embodiment, the method further comprises defining a parameterized workbook and referencing a result from the virtual workbook in a formula in the parameterized workbook.

In another embodiment, the method further comprises storing, separate from the first spreadsheet and after the first spreadsheet closes, an intermediate value used in a calculation for the first spreadsheet and associating the intermediate value with the first spreadsheet and a value of the parameter used to calculate the intermediate value. In another embodiment, the method further comprises automatically calculating a result associated with the first spreadsheet without opening the first spreadsheet by using the stored intermediate value. In another embodiment, the method further comprises distributing calculations of the first spreadsheet among a plurality of computing devices. In another embodiment, the method further comprises distributing calculations of the first spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the first spreadsheet. In another embodiment, the method further comprises distributing the calculations of the first spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the virtual workbook.

In yet another aspect, the invention relates to a system for utilizing a plurality of spreadsheets. The system comprises a list of parameters and a selection module. The selection module selects a first spreadsheet from a plurality of spreadsheets. In one embodiment, the system further comprises a list of results associated with each of spreadsheets in the plurality of spreadsheets. In another embodiment, the system further comprises a calculation module to receive a value for a first parameter within the list of parameters and generate a first result based at least in part on the value and the first spreadsheet. In another embodiment, the system further comprises an instantiator module to bind a value of a first parameter within the list of parameters to an instance of the first spreadsheet. In another embodiment, the spreadsheet is a first spreadsheet and the system further comprises a second spreadsheet having one or more formulas referencing a result from the first spreadsheet. In another embodiment, the system further comprises a storage module to store a location of each of the spreadsheets in the plurality of spreadsheets and store the name of each parameter in the associated list of parameters.

In another aspect, the invention relates to a method for storing information associated with a spreadsheet. The method comprises storing a location of a reference spreadsheet and storing a list of parameters associated with the reference spreadsheet. In one embodiment, the method further comprises storing, separate from the reference spreadsheet and after the reference spreadsheet closes, an intermediate value used in a calculation for a reference spreadsheet and associating the intermediate value with the reference spreadsheet and a value of a parameter from the list used to calculate the intermediate value. In another embodiment, the method further comprises automatically calculating many workbook results by enumerating lists of legal parameter values based on type information associated with each respective parameter. In another embodiment, the method further comprises automatically calculating a result associated with the reference spreadsheet without opening the reference spreadsheet by using the stored intermediate value. In another embodiment, the method further comprises storing a list of outputs associated with the reference spreadsheet.

In yet another embodiment, the method further comprises storing an association of the reference spreadsheet to another spreadsheet upon which the reference spreadsheet depends. In another embodiment, the method further comprises monitoring the another spreadsheet to detect a change within an output. In another embodiment, the method further comprises automatically performing a calculation using the reference spreadsheet in response to detecting the change within the output of the another spreadsheet. In another embodiment, the method further comprises storing an association of the reference spreadsheet to a data source upon which a formula in the spreadsheet depends. In another embodiment, the method further comprises monitoring the data source to detect a change within the data source. In another embodiment, the method further comprises automatically performing a calculation using the reference spreadsheet in response to detecting the change within the data source.

In another embodiment, the method further comprises storing version data associated with the reference workbook. In another embodiment, the location comprises a network address. In another embodiment, the method further comprises distributing calculations among a plurality of computing devices based at least in part on one or more stored associations. In another embodiment, the method further comprises distributing calculations of the reference spreadsheet among a plurality of computing devices. In another embodiment, the method further comprises distributing calculations of the reference spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the reference spreadsheet. In another embodiment, the method further comprises distributing the calculations of the reference spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the reference spreadsheet.

In a further aspect, the invention relates to a method for utilizing a spreadsheet. The method comprises defining a parameter associated with the spreadsheet and generating a data query based at least in part on the parameter. In one embodiment, the method further comprises generating an output based at least in part on the results of the data query.

In another aspect, the invention relates to a method for utilizing a spreadsheet. The method comprises defining a template within the spreadsheet and generating an output based at least in part on the template. In one embodiment, the method further comprises generating a data query based at least in part on a parameter associated with the spreadsheet. In another embodiment, the one or more cells within the template contain formulas. In another embodiment, the formulas are written in spreadsheet formula language. In another embodiment, the method further comprises replicating one or more cells within the template. In another embodiment, the method further comprises preserving relative cell references. In another embodiment, the method further comprises replicating formatting of the template cells.

In yet another embodiment, the method further comprises associating values from a data query with the one or more replicated cells by using column names in formulas within the one or more replicated cells and performing calculations using the associated values. In another embodiment, the method further comprises performing special processing on the output when the data query returns no associated values. In another embodiment, the method further comprises automatically sorting the output based at least in part on the associated values of the one or more cells in the output. In another embodiment, the method further comprises associating a formula language name with the output. In another embodiment, the method further comprises automatically updating the output when a change is detected. In another embodiment, the change comprises a change to i) template cell formulas, ii) template cell formatting, iii) template cell values, or iv) data query parameters.

In another aspect, the invention relates to another method for utilizing a spreadsheet. The method comprises defining an output range within the spreadsheet, rendering the output range and allowing a user to modify the rendered output range. In one embodiment, the method further comprises rendering the output range using HTML. In another embodiment, the method further comprises allowing the user to sort columns within the output range using a user input. In another embodiment, the method further comprises allowing a user to interactively expand and collapse a hierarchy using a user input.

In yet another aspect, the invention relates to an article of manufacture comprising one or more computer program portions embodied therein to cause a processor to perform each of the methods above.

Among other advantages, the invention described above allows non-programmers greater flexibility, including allowing the application of spreadsheets to certain kinds of business problems that are not tractable with conventional spreadsheet programs. The present inventions derive, in part, from the observation that currently available spreadsheet systems do not meet the needs of users who would like to use spreadsheet based systems to solve these kinds of problems.

The spreadsheet-based data processing systems efficiently perform large business and financial computations based on a network of inter-related spreadsheets. The systems include spreadsheet modeling mechanisms that work in concert to allow non-programmers to model classes of problems that are intractable using prior art spreadsheet programs. One example of a business problem is the use of spreadsheets to manage sales commission programs. In a typical situation, each salesperson\'s commission plan may be based on several variables, such as sales quota goals or particular commission rates to be paid on certain sales. Each salesperson\'s plan may also vary based on their seniority or the kind of territory they cover. In current practice, compensation specialists often model the commission plans using spreadsheets. Ideally, each salesperson will have a separate spreadsheet customized to their situation. Managers will also have their own tailored commission plans, modeled as a spreadsheet, and these will often depend on the results of the people reporting to them. The result is a computational model that consists of a large web of interdependent spreadsheets, which can number in the thousands for a large sales organization.

Existing spreadsheet programs lack effective end-user automation functionality to deal with models of this scale and complexity. These models may not be able to fit into the memory space of the program, and so must be broken up into multiple workbooks. Conventional programs allow for only manual management of the dependencies between the resulting workbooks, leading to mistakes. The complexity of the models makes them difficult to change without introducing errors in the references between workbooks. The methods and systems described above include the concept of a parameterized spreadsheet, which greatly facilitates the reuse of spreadsheets as building blocks in large computations, and automated parameter-sensitive dependency tracking, which reduces errors caused by unintended sharing of workbooks or the failure to load a dependent spreadsheet into memory or to recalculate it when necessary, for example because the data in a referenced data source has changed.

Moreover, existing spreadsheet calculation algorithms do not work efficiently with large models that may contain hundreds or thousands of workbooks. The methods and systems described above allow large and complex spreadsheet models to be efficiently recalculated and maintained. The ability to quickly perform recalculations in turn makes it practical to perform large what-if scenarios and to deliver on-demand calculations.

The methods and systems described above also allow these large spreadsheet calculations to be kept up to date and to be reported upon without user intervention. The results of the computations can be made available on demand, even in the face of continual changes to the underlying data and the evolution of the spreadsheets themselves. The ability to access these spreadsheet models on demand allows self-service applications to be created for information consumers. For example, using a self-service web site, a salesperson can access their current commission calculations or a chief financial officer can view and download an up to date projection of the commission expenses for the current quarter.

The specification frequently refers to a sales commission model to provide examples of the inventive techniques described herein. It is understood, however, that the present invention is not limited in scope to the provided example of sales commission calculations. The present invention is applicable to many other application domains including, but not limited to, financial services, logistics and process modeling. In the domain of financial services, the present invention may be applied to build, manage and calculate models to determine portfolio valuations or to guide securities or commodity trading based on spreadsheet models developed by the user. In the logistics and process modeling domains, the present invention may be used to apply user developed spreadsheet models to optimize the efficiency of a manufacturing facility or a transportation network.

In some embodiments, the invention relates to systems and methods for spreadsheet data processing that applies parameter controlled spreadsheet workbooks to specific data. When a workbook is applied to data sources and parameters for the workbook are selected (if necessary), the system and method performs the workbook calculations upon the data sources and subsidiary workbooks (if any) to produce calculated results. Such results, in one embodiment, can be manifested in any of several different formats.

Each workbook may make reference to subsidiary workbooks, which may be applied when the parent workbook is applied. Each applied workbook or subsidiary workbook may be controlled by supplied parameters. The parameters may control the selection and application of each subsidiary workbook. Applied workbooks may reference subsidiary workbooks multiple times with different parameter values and may make recursive self references with different parameters. A family of workbooks with similar parameters may be grouped to form a virtual workbook that uses a set of supplied rules to select one member of the family when the virtual workbook is applied.

Workbook parameters may be typed, and the type may limit the supplied values. Workbook and data dependencies are tracked in order to facilitate the maintenance of workbooks and data. The systems or methods may provide caching of intermediate computations across workbooks and data sources and may distribute computations across multiple computers. Specific sets of cached results may be constantly maintained and made available as a multidimensional data source (e.g. as time series data).

The details of one or more embodiments of the invention are set forth in the accompanying drawings and the description below. Other features, objects, and advantages of the invention will be apparent from the description and drawings, and from the claims.

FIG. 1 is a block diagram of an illustrative embodiment of a complex model comprising parameterized workbooks in accordance with the invention;

FIG. 2 is a block diagram of an illustrative embodiment of a virtual workbook in accordance with the invention;

FIG. 3 is a block diagram of an illustrative embodiment of a system to generate and use parameterized workbooks in accordance with the invention;

FIG. 4 is a screen shot of an illustrative embodiment of a parameterized workbook in accordance with the invention; and

FIG. 5 is screen shot of an illustrative embodiment of a report generated using a virtual workbook in accordance with the invention.

Like reference symbols in the various drawings indicate like elements.

DETAILED DESCRIPTION

FIG. 1 is a block diagram of an illustrative embodiment of a complex model 100 comprising a first parameterized workbook 105a, and optionally, a second parameterized workbook 105b and an nth parameterized workbook 105n. The parameterized workbooks 105a, 105b . . . 105n are referred to generally as 105. The first parameterized workbook 105a includes a parameter module 110, a workbook module 115 and a results module 120. The parameter module 110 can include a list of one or more parameters (e.g., external inputs). The results module 120 can include a list of one or more results (e.g., outputs). Results 120 can include, for example, a single value retrieved from a workbook cell, an array of values taken from a region of workbook cells, an HTML rendering of a region (e.g. the print area) of a sheet from a workbook and/or an image or an alternative representation describing a chart. The arrow 125 represents the association of a particular parameter 110 with a particular workbook 115. Similarly, the arrow 130 represents the association of a particular result with a particular workbook 115. The parameterized workbook 105 is a basic building block and as illustrated, can call itself (represented by path 135) and/or a second parameterized workbook 105b (represented by path 140) to model complex calculations. The modules throughout the specification can be implemented as a software program (e.g., a set and/or a sub-set of processor instructions and the like) and/or a hardware device (e.g., ASIC, FPGA, processor, memory, storage device and the like).

A creator (e.g., user, administrator, system and/or automated process) associates the parameter module 110 and the results module 120 with the workbook module 115. This association can facilitate the reuse of workbook calculations in different contexts. A system, for example as described in FIG. 3 below, stores the lists of parameters 110 and results 120, and the types of these parameters 110 and results 120, in a storage module 315 (FIG. 3), for example, a database. By associating parameters 110 and results 120 with each workbook 115, a single workbook 115 can perform a potentially unlimited number of calculations. This allows a parameterized workbook 105 to become a reusable computational building block in constructing a larger model 100.

The creator can also associate type information with each workbook parameter 110 and result 120. The type information constrains the set of legal data values the system can use for a parameter 110 when instantiating a workbook and the set of legal data values that the system can return as an output result 120 from a workbook 105 instantiation.

The parameter 110 and result 120 values of a parameterized workbook 105 can be scalar types, such as strings and numbers, and/or elements of structured data types such as records or objects. Parameter 110 and result 120 values can also be arrays of values derived from ranges of cells, which need not be contiguous. In one embodiment, the types associated with the parameters 110 and results 120 are described by an object-oriented type system that can represent objects with data fields, methods, inheritance and information hiding capabilities.

For example, a parameter can be a person type parameter. A person type parameter can include attributes such as personal information, title, supervisor, and compensation information. The personal information can include the attributes First Name, Last Name, Social Security Number, Login ID, Password, Employee No., Work phone, Home phone and Date of hire. The compensation information can include the attributes Base Salary and Effective Date. For ease of use, the system can employ a user interface for defining the attributes for specific employees of an organization. For example, a user can input a value for the attribute Title using a pull-down menu including the values, for example, Sales Rep, District Manager, Regional Manager, Vice President-Sales, Strategic Account Manager, Director-Strategic Accounts, CEO, and Commission Administrator. The Supervisor attribute can be a pointer to existing instances of person type parameters.

The type system of the parameterized workbook 105 also captures data source mapping information about how object and record types relate to data sources. Object or record types can optionally be mapped to the database schema of a data source, which can be either an internal data source (e.g., part of the system embodying the present invention) or an external data source. The data source mapping information is sufficient to allow the system to perform queries against the data source and to enumerate and access all instances of the object or record type available from the data source. The type system can combine the data source mapping information with the object or record type information to express arbitrary filters or restrictions on the set of object or record types. The type system provides the ability to express data dependent constraints on workbook parameters 110 and return values 120. In one embodiment, the system includes built-in primitives for describing constraints based on roles or hierarchies (e.g. Person where Person HasRole SalesManager and Person.Territory IsIn Europe).

The type information associated with each parameterized workbook 105 can be created from a combination of user input and from a programmatic examination of the internal structure of the computations. The system can employ type inference methods for determining type information based on an a analysis of the static structure of a computation (such as the static structure of a computer program) as is known in the art.

The system uses the type information to constrain the legal instantiations of a parameterized workbook 105. For example, a particular workbook 105a might have two parameters 110. The first parameter 110 is typed as the person object, further constrained to have the job title of telesales representative. The second parameter 110 is typed as a time period object, further constrained to be a calendar month. The workbook 105a might define several outputs 120. One output 120 might be a bar chart indicating sales performance of the sales person for a particular month (the output type might be specified as a GIF image). Another output might be a list of daily sales performance metrics (such as the number of calls or average call duration or product revenue generated). The type of this second output might be an array of numeric values (one for each day of the month).

The system can use parameter type information to avoid a common source of user errors when creating, modifying or accessing computations. For example, based on the available type information for the workbook described above, the system can automatically generate a user interface to prompt the user for a telesales representative (chosen from a list of all such people known to the system) and a month (based on the months for which the system has data). The system can also use spreadsheet parameter information 110 for other purposes, including for example, data dependency tracking and pre-computation and caching of result sets 120, as described below.

In one embodiment, the system receives an XML file to create a parameterized workbook 105. For example, the XML file can be defined as follows:

<NVWorkbook name=″Rep_ProductLineCommissionCalculations”    file=″/workbooks/Rep_ProductLineCommissionCalculations.xls”>   <paramdef type=″Person″ name=″person″/>   <paramdef type=″Year″ name=″period″/>   <attr name=″CreatedBy″ value=″Chris Thompson″/>   <attr name=″Description″ value=″Workbook calculates     earned commissions for all 12 months of a Year     for two product lines.″ />   <output name=″YTDCommission″ cell=″Commission_YTD″> </NVWorkbook> In this example, the file names the parameterized workbook 105 “Rep_ProductLineConmmissionCalculations.” In addition to the name, the parameterized workbook 105 has two additional attributes, “created by” and “description.” In one embodiment, the system can provide a user interface that lists the respective values for these three attributes for all of the parameterized workbooks 105 in the system. The file also defines the workbook module 115 used for this parameterized workbook 105. This workbook is “/workbooks/Rep_ProductLineCommissionCalculations.xls.”

This example file names the parameters 110 associated with the parameterized workbook 105 Rep ProductLineCommissionCaloulations “person” and “period” and defines them as “Person” and “Year” types, respectively. Person and Year have definitions in a data dictionary that describes the object model of the application. In this example, the association 125 of the parameter 110 with the workbook 115 is made in the workbook itself in addition to being described in the file. In other words, the spreadsheet Rep_ProductLineCommissionCalculations.xls contains formulas that use the parameters person and period in them. When the system instantiates the parameterized workbook 105, the system binds specific values for these parameters to the instantiation of the Rep_ProductLineCommissionCalculations.xls spreadsheet.

This example file names the output 120 “YTDCommission.” Other parameterized workbooks 105 can use this name in their formulas and the system can find this value and pass this value to those other workbooks 105. As described below, the system can store this value so that the other workbooks can receive this value without having to re-instantiate the Rep_ProductLineConunissionCalculations workbook 105. In this example, the file defines the association 135 between the output 120 and the workbook module 115. The file associates this output 120 with the cell named “Commission_YTD.”

An output such as “YTDCommission” also defines a family of related output values based on all possible combinations of input parameters to the parameterized workbook 105. Sets or subsets of these families of output values can be used as multi-dimensional data cubes for analyzing trends and data relationships as is known in the art. An example of a subset of values derived from this example workbook is the list of YTDCommission values for all sales managers in Europe for the year 2000.

In one embodiment, the creator associates the parameter module 110 and the results module 120 with a workbook module 115 that includes a workbook (which can contain multiple worksheets). A workbook is used because the workbook is a convenient unit of spreadsheet computation, easily identifiable and manageable by the user. The system, however, does not depend on certain properties of workbooks manifested in current spreadsheet programs, such as the workbook being the unit of file storage and the unit of transfer when moving a spreadsheet computation into main memory for processing. In other embodiments, the workbook module 115 can include any logical unit of spreadsheet computation, comprising one or more spreadsheets and associated formulas, as the unit of parameterization. Therefore, although workbook is used for clarity, the term workbook can be substituted with this logical unit of spreadsheet computation throughout the specification.

With parameterized workbooks 105, a user does not have to vary cell values in order to perform different calculations, including what-if scenarios. Using parameterized workbooks 105, a system can perform these variable calculations non-interactively (e.g., without intervention by an user). As described in more detail below, a system can perform a varying parameterized workbook 105 calculation on a client computer or on one or more server computers. An end user, a formula appearing in a workbook cell, and/or a program using an API can initiate a parameterized workbook 105 calculation either interactively or non-interactively. Other computer systems communicating over a network can also initiate a parameterized workbook 105 calculation. These other systems may use established communication protocols such as, for example, CORBA (Common Object Request Broker, from OMG (Object Management Group)), RMI (Java Remote Method Invocation) or SOAP (Simple Object Access Protocol, from Microsoft). A parameterized workbook 105 calculation may be initiated in response to a user request from a web browser or other user or another user interface device include cell phones, personal digital assistants, etc.

In one embodiment, the creator of the parameterized spreadsheet 105 limits the variable calculations to a subset. Parameterized spreadsheet 105 calculations may limit the variations to a list of specific parameters 110 and to a potentially limited set of possible values for each parameter 110.

The output 120 of a parameterized spreadsheet 105 calculation can take many forms. The software performing the parameterized spreadsheet 105 calculation can format a worksheet or a region on a worksheet for display on some output device. The display format can vary depending on the output device, and can include standardized output formats such as HTML (Hypertext Markup Language, the main document format recognized by web browsers), WML (Wireless Markup Language, similar to HTML but targeted at wireless devices such as cell phones), or XML (extensible Markup Language, used for business to business (or system to system) communication), in addition to device specific formats. Alternatively, the output 120 of a parameterized spreadsheet 105 calculation can be a set of data values, suitable for use in further data processing. Various output formats can be used for representing these sets of data values 120, including document formats such as HTML or XML or formats based on data communication protocols such as CORBA or RMI. Parameterized spreadsheets 105 allow greater control over the course of the computation, including the ability to select different sets of external data upon which to operate.

Parameterized spreadsheets 105 allow computations to be performed and allow those computations to be influenced by external data sources through parameters, data ranges, and other mechanisms as described below. They provide increased flexibility to the user, allowing computations to use varying inputs and allowing the output desired to be specified by the user without having to change the underlying spreadsheet 115. Introducing parameterized spreadsheets 105 as a formal modeling mechanism provides an interface suitable for non-programmers to create reusable spreadsheet-based computational building blocks and it provides programmers with increased flexibility and power.

When a parameterized workbook 105 calculation is performed in the context of a specific set of parameters 110, this can be referred to as a workbook instantiation. As illustrated with path 135, a model 100 can simultaneously instantiate a parameterized workbook 105a multiple times with different values for the parameters 110. This allows a parameterized workbook 105 calculation to depend on one or more calculations from subsidiary parameterized workbooks (e.g., 105a, 105b . . . 105n). Any subset of the subsidiary instantiations, and even the referencing workbook instantiation itself, can be instantiations of the same parameterized workbook 105.

The combination of parameterized spreadsheets 105 and simultaneous instantiation allow many business problems to be modeled naturally by the end user. For example, if a sales manager is paid based on the performance of the salespeople who report to him, and if each salesperson\'s performance is determined by a parameterized spreadsheet 105 calculation, then the manager\'s performance can be modeled 100 by a parameterized spreadsheet 105a that depends upon values calculated by subsidiary workbook instantiations (e.g., 105b . . . 105n). The number of dependent instantiations and the parameters 110 used in each instantiation are controlled by the formulas in the referencing workbook 115, perhaps for example, based on a database representation of the organizational structure of the company. As described in more detail below, FIG. 4 illustrates an exemplary embodiment of a screenshot of a parameterized workbook 105.

In addition to the parameterized workbook 105, FIG. 2 illustrates an exemplary embodiment 200 of another building block for use in complex modeling, the virtual workbook 205. The virtual workbook 205 includes a parameter module 110′, a results module 120′, a virtual workbook module 210 and a selection module 215. In one embodiment, the selection module 215 can be included in and/or as part of the virtual workbook module 210. An instantiation of the virtual workbook 205 selects a concrete workbook (e.g., 105a′ . . . 105n′) to instantiate based on the values of the supplied parameters 110′ and a set of rules 215 associated with the virtual workbook 205. The virtual workbook 205 mechanisms are similar to programming language polymorphism mechanisms, such as virtual methods or function overloading, that are a part of programming languages such as Java or C++.

As described above, a parameterized workbook 105a (FIG. 1) creates a set of outputs 120 (FIG. 1) based on a set of input parameters 110 (FIG. 1). In other words, the parameterized workbook 105a is a function mapping a set of inputs 110 to a set of outputs 120. The outputs 120 may be visual, such as a chart or a report rendered in some format (e.g. GIF, HTML) intended for display or printing. The outputs 120 may also be data values represented in some format (e.g. XML) intended for further processing.

A virtual (polymorphic) workbook 205 maps a set of inputs 110′ to a set of outputs 120′ by selecting a concrete parameterized workbook (e.g., 105a′ . . . 105n′, generally 105′) from among a set 220 of compatible workbooks and then instantiating the concrete workbook 105′. The selection module 215 selects a concrete workbook 105′ based on the value of the parameters 110′ the virtual workbook 205 receives and a set of rules 215 associated with the virtual workbook 205. As an example, a virtual workbook 205 can represent monthly commission payments to sales employees. Different kinds of employees might have different sales plans depending on their job title, seniority, territory, and the like, and the parameterized workbook 105 for different employees might be different for different months, for example, because they were promoted. The selection module 215 selects a concrete parameterized workbook 105′ based on received values of parameters 110′ corresponding to job title, seniority, territory, month, and the like. The virtual workbook 205 provides a uniform mechanism of determining any employee\'s commission payment for any particular month, while the underlying set of concrete parameterized workbooks 220 allow for the differences in the actual calculations for the different employees or different months.

In one embodiment, virtual workbooks 205 (as well as concrete workbooks 105) are associated with uniform resource locators (URLs), which are part of the naming scheme used in the World Wide Web. The particular naming scheme used to refer to workbooks 105, 205 does not matter, however, since the virtual workbooks 205 themselves provide the mechanism for mapping a generic request to a specific workbook 105′ that implements that request for a given set of parameters 110′.

A model can employ a virtual workbook 205 in any context where a parameterized workbook 105 can be used. For example, a virtual workbook 205 may be referenced by formulas within other workbooks (this reference may be based on the URL associated with the workbook or it may be based on a different naming scheme not based on URLs). As a result, a parameterized workbook 105′ may have a dependency on a virtual workbook 205 that is in turn implemented by one or more concrete workbooks 105′, perhaps including the referencing workbook 105′ itself. This mechanism allows the formulas in the referencing workbook 105′ to refer transparently to one of several concrete workbooks 105′ that the virtual workbook 205 selects based on the value of the parameters 110′ the virtual workbook 205 receives. A user or the system can add and/or change over time the set of rules 215 for selecting a concrete workbook 105′ and the set 220 of concrete workbooks. These changes are transparent to a referencing (calling) workbook and so the user or system does not need to change the formulas within the referencing workbook. This transparency makes a virtual workbook 205 a powerful building block to allow end users to create and manage large, flexible spreadsheet-based computations.

Similar to the parameterized workbook 105, in one embodiment, the system receives an XML file to create a virtual workbook 205. For example, the XML file can be defined as follows:

<NVQuantity name=″CommissionEarnedYTD″      type=″ByDecisionTree″>  <paramdef type=″Person″ name=″person″/>  <paramdef type=″Year″ name=″period″/>  <paramdef type=″MonthIndex″ name=″monthindex″/>  <NVDecisionTree>   <NVRule>    <NVRulet parameter_name=″person″>     <NVCondition type=″attr″ attr=″Position″          value=″Sales Rep″/>     <NVCondition type=″attr″ attr=″Position″          value=″District Manager″/>     <NVCondition type=″attr″ attr=″Position″          value=″Regional Manager″/>    </NVRulet>    <NVQuantityImpl type=″ByCellName″ valueType=″Real″>     <NVCell name=″Commission_YTD″     workbook=″Rep_ProductLineCommissionCalculations″/>    </NVQuantityImpl>   </NVRule>   <NVRule>    <NVRulet parameter_name=″person″>     <NVCondition type=″attr″ attr=″Position″          value=″Strategic Account Manager″/>    </NVRulet>

Download full PDF for full patent description/claims.




You can also Monitor Keywords and Search for tracking patents relating to this System and method for dynamic data access in a spreadsheet with external parameters patent application.
###


Other recent patent applications listed under the agent :