US20110191665A1 - System and method for dynamic data access in a spreadsheet with external parameters - Google Patents

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

Info

Publication number
US20110191665A1
US20110191665A1 US12/855,282 US85528210A US2011191665A1 US 20110191665 A1 US20110191665 A1 US 20110191665A1 US 85528210 A US85528210 A US 85528210A US 2011191665 A1 US2011191665 A1 US 2011191665A1
Authority
US
United States
Prior art keywords
workbook
spreadsheet
parameter
data
output
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Abandoned
Application number
US12/855,282
Inventor
Robert Handsaker
Gregory Rasin
Andrey Knourenko
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Individual
Original Assignee
Individual
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Family has litigation
First worldwide family litigation filed litigation Critical https://patents.darts-ip.com/?family=26888595&utm_source=google_patent&utm_medium=platform_link&utm_campaign=public_patent_search&patent=US20110191665(A1) "Global patent litigation dataset” by Darts-ip is licensed under a Creative Commons Attribution 4.0 International License.
Application filed by Individual filed Critical Individual
Priority to US12/855,282 priority Critical patent/US20110191665A1/en
Publication of US20110191665A1 publication Critical patent/US20110191665A1/en
Priority to US13/604,085 priority patent/US20130013994A1/en
Abandoned legal-status Critical Current

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F40/00Handling natural language data
    • G06F40/10Text processing
    • G06F40/166Editing, e.g. inserting or deleting
    • G06F40/177Editing, e.g. inserting or deleting of tables; using ruled lines
    • G06F40/18Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets

Definitions

  • 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.
  • 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.
  • worksheets 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.
  • 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.
  • 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.
  • an external data source such as a query to a database.
  • the user of the program can control how often the external data source should be checked for changes.
  • 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.
  • APIs application programming interfaces
  • 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 program's 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.
  • 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.
  • 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.
  • the method further comprises defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet.
  • 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.
  • the method further comprises defining a format for the result.
  • the method further comprises associating a type with the parameter, the type defining a range of values.
  • 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.
  • 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.
  • the method further comprises automatically calculating a result associated with the spreadsheet without opening the spreadsheet by using the stored intermediate value.
  • the method further comprises distributing calculations of the spreadsheet among a plurality of computing devices.
  • 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.
  • 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.
  • the invention in another aspect, 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.
  • 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.
  • the system further comprises a list of results associated with the spreadsheet.
  • 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.
  • 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.
  • 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.
  • the invention in another aspect, 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.
  • 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.
  • the method further comprises referencing the virtual workbook in a created spreadsheet as a substitute for a second spreadsheet in the plurality of spreadsheets.
  • 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.
  • 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.
  • 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.
  • 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.
  • the method further comprises automatically calculating a result associated with the first spreadsheet without opening the first spreadsheet by using the stored intermediate value.
  • the method further comprises distributing calculations of the first spreadsheet among a plurality of computing devices.
  • 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.
  • 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.
  • 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.
  • the system further comprises a list of results associated with each of spreadsheets in the plurality of spreadsheets.
  • 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.
  • 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.
  • 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.
  • 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.
  • the invention in another aspect, 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.
  • 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.
  • 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.
  • the method further comprises automatically calculating a result associated with the reference spreadsheet without opening the reference spreadsheet by using the stored intermediate value.
  • the method further comprises storing a list of outputs associated with the reference spreadsheet.
  • 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.
  • the method further comprises storing version data associated with the reference workbook.
  • the location comprises a network address.
  • the method further comprises distributing calculations among a plurality of computing devices based at least in part on one or more stored associations.
  • the method further comprises distributing calculations of the reference spreadsheet among a plurality of computing devices.
  • 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.
  • 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.
  • 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.
  • the method further comprises generating an output based at least in part on the results of the data query.
  • 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.
  • the method further comprises generating a data query based at least in part on a parameter associated with the spreadsheet.
  • the one or more cells within the template contain formulas.
  • the formulas are written in spreadsheet formula language.
  • the method further comprises replicating one or more cells within the template.
  • the method further comprises preserving relative cell references.
  • the method further comprises replicating formatting of the template cells.
  • 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.
  • the method further comprises performing special processing on the output when the data query returns no associated values.
  • 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.
  • the method further comprises associating a formula language name with the output.
  • the method further comprises automatically updating the output when a change is detected.
  • the change comprises a change to i) template cell formulas, ii) template cell formatting, iii) template cell values, or iv) data query parameters.
  • 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.
  • the method further comprises rendering the output range using HTML.
  • the method further comprises allowing the user to sort columns within the output range using a user input.
  • the method further comprises allowing a user to interactively expand and collapse a hierarchy using a user input.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • the present invention may be used to apply user developed spreadsheet models to optimize the efficiency of a manufacturing facility or a transportation network.
  • the invention relates to systems and methods for spreadsheet data processing that applies parameter controlled spreadsheet workbooks to specific data.
  • 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.
  • 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).
  • 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.
  • FIG. 5 is screen shot of an illustrative embodiment of a report generated using a virtual workbook in accordance with the invention.
  • FIG. 1 is a block diagram of an illustrative embodiment of a complex model 100 comprising a first parameterized workbook 105 a , and optionally, a second parameterized workbook 105 b and an nth parameterized workbook 105 n .
  • the parameterized workbooks 105 a , 105 b . . . 105 n are referred to generally as 105 .
  • the first parameterized workbook 105 a 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 .
  • 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 105 b (represented by path 140 ) to model complex calculations.
  • 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 software program e.g., a set and/or a sub-set of processor instructions and the like
  • a hardware device e.g., ASIC, FPGA, processor, memory, storage device and the like.
  • a creator 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.
  • a storage module 315 FIG. 3
  • 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.
  • 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.
  • 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 .
  • the system includes built-in primitives for describing constraints based on roles or hierarchies (e.g. Person where Person HasRole SalesManager and Person.Tenitory 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 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 .
  • a particular workbook 105 a 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 105 a 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.
  • the system receives an XML file to create a parameterized workbook 105 .
  • the XML file can be defined as follows:
  • the file names the parameterized workbook 105 “Rep_ProductLineCommissionCalculations.”
  • the parameterized workbook 105 has two additional attributes, “created by” and “description.”
  • 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 ProductLineCommissionCalculations “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.
  • 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.
  • the spreadsheet Rep_ProductLineCommissionCalculations.xls contains formulas that use the parameters person and period in them.
  • 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 .
  • the system can store this value so that the other workbooks can receive this value without having to re-instantiate the Rep_ProductLineCommissionCalculations workbook 105 .
  • 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.
  • 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.
  • 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.
  • parameterized workbooks 105 a user does not have to vary cell values in order to perform different calculations, including what-if scenarios.
  • a system can perform these variable calculations non-interactively (e.g., without intervention by an user).
  • 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.
  • a parameterized workbook 105 calculation may be initiated in response to a user request from a web browser or another user interface device include cell phones, personal digital assistants, etc.
  • 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.
  • 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.
  • a model 100 can simultaneously instantiate a parameterized workbook 105 a multiple times with different values for the parameters 110 .
  • parameterized spreadsheets 105 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 105 a that depends upon values calculated by subsidiary workbook instantiations (e.g., 105 b . . . 105 n ). 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 .
  • 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 .
  • 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., 105 a ′ . . . 105 n ′) 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++.
  • a parameterized workbook 105 a creates a set of outputs 120 ( FIG. 1 ) based on a set of input parameters 110 ( FIG. 1 ).
  • the parameterized workbook 105 a 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., 105 a ′ . . . 105 n ′, 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 .
  • a virtual workbook 205 can represent monthly commission payments to sales employees.
  • 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.
  • 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.
  • URLs uniform resource locators
  • 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.
  • 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).
  • 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.
  • the system receives an XML file to create a virtual workbook 205 .
  • the XML file can be defined as follows:
  • the file in this example names this virtual workbook 205 “CommissionEarnedYTD” and defines the type of the virtual workbook 205 as “ByDecisionTree.”
  • the selection module 215 uses the two rules of the decision tree to select the appropriate concrete parameterized workbook 105 ′. Both rules examine the parameter 110 ′ named “person” and the value of its attribute “position.” According to the first rule, if the value of the position attribute is sales rep, district manager or regional manager, the selection module 215 selects the parameterized workbook 105 ′ Rep_ProductLineCommissionCalculations. The associated output 120 ′ returned after this selection is the value from the cell in the selected workbook named Commission_YTD.
  • the selection module 215 selects the parameterized workbook 105 ′ SAM_ProductLineCommissionCalculations.
  • the associated output 120 ′ returned after this selection is the value from the cell in the selected workbook named Commission_YTD.
  • FIG. 3 illustrates an embodiment of a system 300 to generate and use parameterized 105 and virtual 205 workbooks in accordance with the invention.
  • the system 300 includes an application server node 305 , a calculation module 310 , a storage module 315 and a client node 320 .
  • the system 300 can optionally include models 325 and miscellaneous Web services 330 .
  • the application server node 305 , the calculation module 310 and the storage module 315 communicate with each other via a network 340 a using communication channels 345 a , 345 b , and 345 c , respectively.
  • the client node 320 communicates with the application server node 305 via a network 340 b using communication channels 335 d and 335 e , respectively.
  • the networks 340 a and 340 b can also be part of the same network 340 as shown with optional connection 340 c . If optional connection 340 c is included, then one of the application server node's 305 communication channels 345 a or 345 e can be eliminated.
  • the models 325 and the miscellaneous Web services 330 communicate with the network 340 b using communication channels 335 f and 335 g , respectively.
  • the networks 340 a , 340 b and 340 c are generally referred to as 340 .
  • the communication channels 345 a , 345 b , 345 c , 345 d , 345 e , 345 f and 345 g are generally referred to as 345 .
  • the networks 340 and the communication channels 345 can include and/or be part of an internal bus, a local-area network (LAN), such as a company Intranet, a wide area network (WAN) such as the Internet or the World Wide Web and/or the like.
  • the networks 340 and the communication channels 345 represent, for example, standard telephone lines, LAN or WAN links (e.g., T1, T3, 56 kb, X.25), broadband connections (ISDN, Frame Relay, ATM), wireless connections (cellular, WLAN, WWAN, 802.11) and/or the like.
  • connections can be established and data can be exchanged using a variety of communication protocols and languages (e.g., HTTP(S), TCP/IP, SSL, PPTP, HTML, XML, SOAP, IPX, SPX, NetBIOS, Ethernet, RS232, direct asynchronous connections, VPN protocols, a proprietary protocol, a proprietary language and/or the like).
  • HTTP HyperText Transfer Protocol
  • SSL Secure Sockets Layer
  • PPTP HyperText Transfer Protocol
  • HTML HyperText Transfer Protocol
  • XML HyperText Transfer Protocol
  • SOAP IPX
  • SPX SPX
  • NetBIOS NetBIOS
  • Ethernet RS232
  • direct asynchronous connections e.g., VPN protocols, a proprietary protocol, a proprietary language and/or the like.
  • VPN protocols e.g., HTTP(S), TCP/IP, SSL, PPTP, HTML, XML, SOAP, IPX, SPX, NetBIOS, Ethernet, RS232, direct asynchronous connections, VPN protocols, a proprietary protocol
  • Each of the servers and modules 305 , 310 and 315 can be any computing device capable of providing the services requested by the other servers and modules or by the client node 320 . Particularly, this includes generating and processing parameterized workbooks as described herein.
  • FIG. 3 depicts server node 305 as an entity separate and distinct from modules 310 and 315 , and each node 305 , 310 and 315 can independently be in communication with the network 340 a , representing that the nodes 305 , 310 and 315 are logically independent.
  • nodes 305 , 310 and 315 can also be implemented individually or in any combination, for example, on a single server, distributed on portions of several (i.e., more than two) servers, and/or as part of a single server node or server farm in communication with the network 340 b through, for example, a single Web server (not shown).
  • the client node 315 can be any computing device (e.g., a personal computer, set top box, wireless mobile phone, handheld device, personal digital assistant, kiosk, etc) used to provide a user interface to access the application server 305 .
  • the client node 320 can include a browser module 350 .
  • a user can use the system 300 , inter alia, to generate and process parameterized 105 and virtual 205 workbooks.
  • Two objectives of the present invention are to simplify the creation and maintenance of large spreadsheet-based computation models and to allow those models to be computed without manual intervention.
  • the system 300 facilitates these objectives by storing tracking information about workbooks, including their parameters 110 , parameter types and dependencies in the storage module 315 .
  • the system 300 stores this information as a workbook information database.
  • the workbook information database tracks and controls the names and storage locations of all parameterized 105 and virtual 205 workbooks used in the system 300 .
  • the database records information about the parameters 110 and parameter types and outputs 120 and output types of each workbook (e.g., 105 , 205 ) and the dependencies between workbooks that exist when one workbook refers to another workbook using a formula. Each workbook instantiation may depend on one or more instantiations of the same workbook or of other workbooks.
  • the dependency information allows the system 300 to perform workbook computations without manual intervention, for example in an application server 305 environment, because the system 300 can determine all dependent workbooks, open them, instantiate them and resolve all linkages between the workbooks appropriately.
  • Table 1 represents an embodiment of parameter and where-used data stored in the workbook information database.
  • the database stores the name, the type and any restrictions of the parameters 110 associated with the represented workbook.
  • the names of parameters associated with the represented workbook are person, period and monthindex. Each parameter has a defined type. Class person and class period are class types that define the attributes associated with the respective parameter. Enumeration is an index type that associates an index with a corresponding calendar month.
  • the restrictions data notes any restrictions on the parameters associated with this workbook and in this example, there is a restriction on the value of the parameter period to be stated as a year.
  • the database also stores the name, the type and reference information on all defined outputs of the represented workbook.
  • the names of the defined output shown is TopRank, which is mapped to a cell named MaximumRank within the workbook.
  • the database also stored data on dependencies of the workbook on other workbooks or external data sources. There are two dependencies shown. One dependency shows that the represented workbook calculation depends on the output of a calculation from another workbook, the YTDCommissions output of the CommissionCalculations workbook. The second dependency indicates that the represented workbook uses a data range that depends on a named data query PeopleByManager.
  • the workbook information database also tracks multiple versions of workbooks (e.g., 105 , 205 ) as they are created and modified over time.
  • This version information provides a historical record of changes, allows changes to be rolled back, and can provide for stability of prior computations (changes to a workbook do not necessarily have to affect existing computations, which can continue to use the previous version of the workbook at the user's discretion).
  • the system 300 can also use version tracking of workbooks to limit the visibility of certain workbook versions to particular people. For example, a new workbook version might be visible only to a set of “approvers” as part of a formal approval process. Once the new version has been approved, it is then made visible globally and affects the computations seen by everyone.
  • Table 2 represents an embodiment of version data stored in the workbook information database.
  • the Table 2 example stores the version data for the represented workbook, including the branch, the version number the state of the version, the effective date of the version and any associated labels of the version.
  • the branch values represent named branches within the version graph of the workbook.
  • the version number is an ID for each version relative to its branch. Thus, a particular workbook version is uniquely identified by a combination of its branch name and version number.
  • the state values represent whether the cached data associated with that workbook version has been published (e.g., available to users to view and/or edit).
  • the effective value represents the time frame in which the version is valid.
  • the labels values represent user-defined names that can be assigned to individual workbook versions.
  • the workbook information database also tracks information about the data sources used by each workbook (e.g., 105 , 205 ). This information is used to detect when workbook results 120 are out of date and to efficiently recalculate the workbooks. For every reference in a workbook to external data, the system 300 records information in the workbook information database that represents a predicate describing the set of external data upon which the workbook computation depends. This predicate may be based, in part, on the values of workbook parameters 110 . In other words, the results of different instantiations of the workbook may depend upon different sets of external data.
  • the system 300 may monitor each data source to detect changes to the data source, using the information in the workbook information database to determine which workbook instantiations are affected by any change. Several different means may be used to monitor the data source.
  • Data sources can include both internal and external data sources.
  • An internal data source is a database (or other data storage mechanism) that is accessed only through the system 300 . All changes to data in an internal data source are made through interfaces provided by the system 300 and these update interfaces can perform the necessary monitoring of the data source.
  • An external data source is one that is not under the exclusive control of the system 300 and can be updated through other interfaces.
  • the system 300 can use known techniques such as database triggers, database polling and/or data timestamps to provide similar levels of monitoring functionality.
  • the application server 105 is the active agent responsible for monitoring the external data source.
  • the system 300 can use mechanisms used to monitor external data sources to monitor internal data sources as well.
  • the system 300 can in many cases optimize the data source monitoring required.
  • a particular workbook e.g., 105 , 205
  • Analysis of the workbook computation may reveal that an instantiation of the workbook only depends on data for the month specified by the parameter.
  • the data source monitoring subsystem can then optimize by remembering which months have had data updates and only recalculating workbook instantiations that depend on those months. If data updates to prior months are rare, this avoids many unnecessary calculations when referring to historical data.
  • system 300 can use tracks data source dependencies and dependencies of one workbook instantiation on other workbook instantiations by observing and recording the data requests made by the referencing workbook and the results of these data requests. The system 300 compares this recorded data against newly computed data in the future to determine whether a workbook instantiation needs to be recalculated.
  • a third method the system 300 can use allows the user to supply surrogate tests for determining when a workbook instantiation needs to be recalculated. For example, the user may specify that instantiations of particular workbooks for prior periods should only be recalculated when specifically requested by the user and should be treated as up to date in all other situations.
  • a server processing component (not shown) or an auxiliary agent processing component (not shown) can perform data source monitoring.
  • these components are always active (or are started on demand by other components, such as a database server, which is always active). This allows the system 300 to monitor data source changes without the need for human intervention, unlike existing spreadsheet applications.
  • the system 300 supports distributing the computational load of evaluating a spreadsheet model (e.g., 100 , FIG. 1 ) across multiple servers (i.e., calculation module 110 represents workbook calculations on multiple servers).
  • calculation module 110 represents workbook calculations on multiple servers.
  • the system 300 can schedule the execution of different workbook instantiations on different server computers.
  • Distributed processing of workbook computations depends on the ability of the system 300 to separate the calculation of a referenced workbook from the use of a result from the referenced workbook by the referencing workbook.
  • the system 300 accomplishes this by utilizing special formula primitives to allow one workbook to refer to a result of another workbook, although other mechanisms are possible.
  • the system 300 can utilize dependency information recorded in the database for efficiency, but does not need to.
  • the system 300 can also schedule distributed executions of workbook computations dynamically as the computation proceeds. For example, if one workbook (e.g., 105 , 205 ) references outputs 120 from a number of subsidiary workbooks (e.g., 105 b . . . 105 n ), the computation of the subsidiary workbooks can proceed in parallel as long as the parameter 110 values for one instantiation do not depend on the output 120 of a different subsidiary workbook instantiation.
  • the system In addition to distributed computing, the system also utilizes caching algorithms and a database of cached computation results to optimize the performance of evaluating large computational models, especially models created as a network of interrelated workbooks.
  • the system 300 stores results 120 and/or other intermediate calculations in the storage module 115 so that these values are not only available after the workbook file has been loaded into the memory of the spreadsheet program and opened. By caching the results in, for example a database, the system 300 can provide faster access and improve the performance of multi-workbook computations.
  • the system 300 may cache values for many different instantiations of the same workbook with different parameter values.
  • the system 300 can also distinguish result values of workbooks that are the values of intermediate computations from subsidiary workbooks performed in the process of instantiating some other workbook and may also cache these result values in the database.
  • result values or aggregations of these result values can then be queried and manipulated using standard database tools.
  • result values whether cached or not, can also be accessed through application programming interfaces provided by the system 300 , including network-based interfaces such as web services.
  • the system can perform workbook calculations either on demand (e.g. when a particular result is requested by an end user through a web browser or some other user interface) or automatically by pre-calculating and caching the results of the pre-calculations, using for example a process on the server 305 , before they are requested.
  • Automatic pre-calculation improves the interactive performance of the system 300 when users subsequently request these computations.
  • the system 300 can perform these automatic pre-calculations at times when the servers 305 , 310 , 315 would otherwise be idle.
  • the system 300 uses information about workbook parameter types and workbook dependencies to determine the set of computations to pre-calculate.
  • Parameter type information determines the set of allowable values for each parameter, and thus the set of all possible instantiations that may be requested for a particular workbook.
  • the set of allowable values for a parameter may depend not only on the parameter type, but also on the state of some data source. So, for example, if a new user is added, then some workbook parameters may now have additional legal values.
  • the data source monitoring of the system 300 detects this change to the data source and may pre-calculate and cache new results for the newly possible instantiations of these workbooks.
  • the system can perform automatic pre-calculation and caching for intermediate results (e.g. the output of one workbook instantiation, which is used by another workbook instantiation), for results that may be displayed for the user, and/or for a set of aggregated result values that are cached so that they can be efficiently queried.
  • aggregated result values might include time series or multi-dimensional data, such as daily gross profit broken down by region and product line.
  • the system 300 can use several different factors to decide which values should be precalculated and cached.
  • One factor for example, is information available within the system 300 about dependencies between workbooks and the size of individual computations. As described above, this information can be stored in the storage module 315 , in for example a database. The system 300 uses this information to estimate the potential cost savings for pre-calculating particular computations.
  • Another factor for example, is historical access patterns and frequency of data accesses (e.g., including web page hits), which provides predictive information about the demand for particular data values or workbook instantiations in the future.
  • a third factor for example, is user specified instructions about which workbook instantiations to pre-calculate and which data values (including aggregated result values) to cache.
  • FIG. 4 illustrates an embodiment of a screen shot 400 of a specific example of parameterized workbook 105 ( FIG. 1 ) that the system 300 ( FIG. 3 ) can generate and process in accordance with the invention.
  • the screenshot 400 includes a first portion 405 indicating the name of the workbook containing the associated spreadsheet formulas and a second portion 415 indicating an exemplary formula used for producing descriptive text based on the workbook parameters 110 and a third portion 420 showing the resulting descriptive text displayed in a cell.
  • the screenshot 400 also includes a fourth portion 425 illustrating tabular data calculated using a data range.
  • the table 425 includes a prototype range 430 and five columns 435 a , 435 b , 435 c , 435 d and 435 f .
  • the screenshot 400 also includes a fifth portion 450 illustrating results 120 in a graphical format.
  • the name of the parameterized workbook 105 represented by this screenshot 400 is DM_Sales_Rank, representing a ranking of all people reporting to a particular district manager who is identified by a person parameter of the workbook.
  • the system 300 instantiates workbook module 115 named DM_Top_Performers.xls, as indicated by the first portion 405 .
  • the equation in the second portion 415 uses the parameters 110 “person” and “period” as part of the formula.
  • the system 300 instantiates the workbook, the system 300 binds the value of the parameters person and period sent with the call to generate that instance.
  • the result of the formula is a descriptive text string based on the workbook parameter values, shown in a cell in the third portion 420 .
  • the system 300 generates the table 425 using a data range.
  • a data range as used herein, is a spreadsheet extension that associates a data query with a variable sized set of workbook cells.
  • the features of the data range of the system 300 provide facilities for performing automated, data-driven spreadsheet calculations based on variable input data.
  • a data range of the system 300 associates a parameterized data query with a range of workbook cells.
  • Each data query parameter is identified by name and/or position.
  • Each query parameter has a type that constrains the allowable values for the parameter. Queries are expressed in a textual syntax that a user and/or administrator creates.
  • a data range can also refer by name to a pre-defined query;
  • a data range defines a set of query parameter value expressions using the spreadsheet formula language. There is one expression for each required query parameter in the query. The system 300 uses the results of these expressions as the values of the query parameters when the system 300 evaluates the data range query.
  • a data range can include a prototype range, a replication direction, an output range, an output range name, a sorting specification and a row insertion specification.
  • the prototype range specifies a set of “template” cells that the system 300 replicates once for each set of results (or row) the query returns.
  • the prototype range is usually one cell high (or alternatively one cell wide), but it can also be rectangular.
  • the replication direction indicates whether the cells from the prototype range are replicated downwards or to the right.
  • the output range is the set of cells that are populated after the system 300 evaluates the data range by replicating the formulas from the prototype range once for each set of results (or row) from the data range query.
  • the output range is of variable size, depending on the query results, which in turn depend on the values of the query parameters, which in turn usually depend, directly or indirectly, on the parameters to the workbook containing the data range.
  • the creator of a data range does not provide formulas for cells in the output range, the formulas for these cells and the resulting cell values are computed by the process of replicating the formulas from the prototype range.
  • the prototype range When the prototype range is replicated, formatting information is replicated as well as cell formulas. Therefore, the prototype range controls the visual presentation of the data range as well as the calculated values.
  • the system 300 binds the output range name to the output range of the data range after each time the data range is modified or re-evaluated. This mechanism facilitates writing formulas that extract selected data from the data range without depending on the size of the data range, because formulas can refer to the range through this name rather than referencing a fixed set of cells that can change over time.
  • the output range name is optional.
  • the sorting specification lists one or more columns (or rows) of the data range and for each column whether the column should be sorted in ascending or descending order. The sorting takes place after the system 300 evaluates the formulas for each cell in the output range and is based on the result shown in each sorted cell of the output range.
  • This post-evaluation sorting is independent of, and in addition to, any pre-evaluation sorting performed by the query associated with the data range.
  • the sorting specification is optional.
  • the row insertion specification controls the behavior when the output range grows and shrinks. The row insertion specification determines whether cells are either (a) inserted and deleted as the range grows and shrinks or (b) overwritten and cleared as the range grows and shrinks. In addition, in case (a), the row insertion specification indicates whether the entire set of rows or columns outside of the data range is replicated when the data range is evaluated or just the cells within the data range itself.
  • the data range can include other aspects. These other aspects can include options for handling special cases, such as when the query returns zero rows or one row. For example, when a query returns zero rows, the prototype range can be hidden (so that it occupies no space in rendered output) or the prototype range can display as blank fields independent of the values produced by the formulas in the prototype range. Other aspects can include options for formatting the data range as a whole, such as borders to apply to the entire data range or striping effects created by alternating the background colors on each row of the output range as shown in the table 425 in FIG. 4 . Other aspects can also include options for special rendering of the data range in particular output formats. For example, the data range might render in HTML in a manner that allows the user to sort or pivot the data range or otherwise interact with and/or change the data range or how it is displayed in a web browser 350 .
  • the system 300 evaluates each parameter value expression for the data range query.
  • the system 300 then evaluates the query itself.
  • the query expression may depend on the parameter value expressions, which may in turn depend on other cells in the workbook and (directly or indirectly) on the workbook parameter values.
  • the query evaluation returns a set of rows, where each row contains multiple values. Each value in a row is identified by a name (the same names are used for the corresponding values in each row).
  • the values may have a variety of data types, including, for example, numbers, strings, dates and references to complex objects.
  • the system 300 For each row the query returns, the system 300 replicates the prototype range, either downward or to the right, depending on the replication direction. To replicate a cell, the system 300 evaluates the formula from the prototype cell in the context of the new cell, with the same behavior as if the user had copied the cell. In addition, formulas within a data range can refer to the named values of the current row by using the names as defined workbook names for the duration of the evaluation of the formula. When replicating a cell in a data range, the system 300 also replicates the formatting associated with the cell.
  • the data range for the table 425 is associated with a named query, for example, a PeopleByManager query, which is a predefined query taking as parameters a person and a time period.
  • the parameter expressions for this data range simply pass the corresponding workbook parameters as the parameters values for the query.
  • This query returns a set of rows with two values in each row, an “EMPLOYEE” value and a “POSITION” value.
  • the “EMPLOYEE” value is a unique identifier for a complex person object reporting to this manager, which is Abraham Lincoln as indicated in portion 420 .
  • the “POSITION” value is a string indicating the person's position within the organization (e.g. “Sales Rep”).
  • the prototype range 430 consists of cells A 19 to F 19 , which are formatted to be green in this example.
  • this formula refers to both a workbook parameter (i.e., period) and a named value in the row (i.e., EMPLOYEE).
  • the system 300 displays the corresponding value for each selected employee within the data range in column 435 b .
  • This formula references a value calculated, in this case, as the output of another parameterized workbook 105 .
  • the system 300 displays the computed value for each selected person within the data range in column 425 e .
  • a creator of a workbook can reference the system formula language extensions (e.g., PersonName and NVQuantity) in any cell in a parameterized 105 or virtual 205 workbook, not only in cells within a data range.
  • the process of evaluating a data range or the process of evaluating a formula containing a system formula language extension may cause the system 300 to perform further processing.
  • a reference to a quantity such as CommissionEarnedYTD, which is implemented as the output 120 of another workbook (e.g., 105 , 205 ), may cause the system 300 to instantiate other workbooks (recursively) as a consequence of instantiating the original workbook.
  • the system 300 instantiates the workbook unless a cached value for the required output 120 is available and up to date.
  • the system 300 searches the storage module 315 to determine if such a cached value is available. If so, then the system determines whether any dependencies of the cached value have changed since the value was cached.
  • Dependencies of the cached value include the workbook that calculated the value, any queries or external references, such as NVQuantity expressions made during the instantiation of the workbook that produced the cached value, and any outputs from other workbooks that were consumed by the workbook that calculated the value.
  • the system 300 can use various methods to circumvent full dependency checks, including, for example, user specified rules defining how often to update cached values.
  • a workbook output 120 may be specified by cell coordinates (e.g. Sheet “Summary”, cell B 10 ) or more typically by referring to a named workbook quantity within the workbook (e.g. “YTDCommission”).
  • the named quantity typically refers to a predefined cell within the workbook that contains the desired output value, but it may also refer to a range of cells or other kinds of system formula language expressions.
  • Each workbook may specify several different outputs, and the system cache maintenance module may choose to cache multiple outputs from the workbook instantiation even though only one output from the instantiation was originally requested. The system 300 may need these other cached output values for other system calculations.
  • the data range in the DM_Top_Performers workbook requests year-to-date commission numbers for each employee reporting to the specified manager (i.e., Abraham Lincoln). This results in a variable number of workbook instantiations.
  • the system may find some of the requested outputs cached in the storage module 315 and may have to recompute others.
  • the creator of the CommissionEarnedYTD quantity can implement that quantity by a set of virtual workbook rules that selects different workbooks for different sets of input parameters.
  • the system 300 may calculate commissions for sales reps by one workbook while calculating commissions for district managers by a different workbook.
  • the application server 305 schedules the calculation of the workbook.
  • the calculation of workbooks may be spread over multiple calculation server processes 310 running on multiple machines.
  • the system 300 utilizes the type system to guide precalculation.
  • the CommissionEarnedYTD quantity has a person parameter and a month parameter. It is possible to run an automated precalculation of this quantity for all people (or alternatively for Sales Reps in Europe) for the current month.
  • the system can precalculate and cache the sales credits for each employee for each year.
  • the entries for this quantity are shown in Table 3.
  • the system 300 stores the data using the ID attribute of a person type parameter.
  • FIG. 5 illustrates an embodiment of a screen shot 500 of a specific example of virtual workbook 205 ( FIG. 2 ) that the system 300 ( FIG. 3 ) can generate and process in accordance with the invention.
  • a manager e.g., Harvey Mackay 505
  • the manager typically specifies the period of interest 515 , or, in one embodiment, the period defaults to the current year.
  • a virtual workbook 205 implements the report 510 .
  • a virtual workbook 205 selects a particular concrete workbook 105 ′ based on rules associated with the definition of the virtual workbook.
  • the rules are based on the parameters of interest, in this case, the manager 505 and the period 515 for which the report is requested.
  • the rules can also utilize globally available information, such as the identity of the user who is logged in, the current date, and the like.
  • the application server 305 processes the rules defining the virtual workbook and selects a particular concrete workbook, for example, RegMgr_MonthlySummary.
  • the concrete workbook is then instantiated by the calculation server 310 and the print area, or an alternatively specified output range, of the specified worksheet within the workbook is rendered as, for example, HTML and returned to the network browser 350 .
  • the system 300 can also render this returned data in many different formats besides the standard HTML. To allow the user to organize this returned data by user preference, the system 300 can make this data interactive.
  • the summary report 510 includes a table portion 512 representing a data range, similar to the data range of table 425 ( FIG. 4 ) described above.
  • the system 300 renders a data range in different output formats (e.g., HTML) in such a way as to provide partial interactivity.
  • Table portion 512 illustrates an example of an HTML rendering of a data range that is interactive.
  • the creator of a spreadsheet containing a data range e.g., table 425
  • the creator of the spreadsheet can define the data range 512 to have certain interactive features when a user views that data range 512 .
  • These interactive features allow some operations on the data, but not necessarily all of the operations available to the creator of the data range, since the creator is working through a different interface, for example the interface shown in FIG. 4 .
  • the system 300 can allow sorting of columns of the data range 512 when rendered in a network browser 350 , for example by clicking with a mouse, using keystrokes and/or other user input. This allows the viewer of the information to sort the table in ascending or descending order based on the alpha/numeric entries of a selected column.
  • the rows of a data range might be grouped in a hierarchy, for example, a geographical hierarchy of continents, countries and states or provinces. The creator can define the data range so that the system 300 allows a user to interactively expand and collapse the hierarchy using, for example, mouse clicks, keystrokes and/or other user input.
  • the calculation server 310 binds the workbook parameters to their corresponding values (e.g. “person” to “Harvey Mackay” and period to “2002”). Then the calculation server 310 evaluates all formulas in the workbook to ensure they are up to date.
  • the system 300 implements the workbook parameters 110 ′ as named quantities within the workbook and workbook formulas can refer to these names directly.
  • a user can also change parameter values using the pull-down menus 525 a and 525 b .
  • the system 300 will re-evaluate the virtual workbook 205 , selecting a concrete workbook 105 ′, which may be the same as the prior concrete workbook (RegMgr_MonthlySummary) or may be different.
  • the concrete workbook is then instantiated and rendered as described above using the changed parameter values.
  • the system 300 allows exposure to quantities via web services 330 .
  • One web service 330 can be defined, for example, to return a matrix of monthly commission payments for a particular year for a certain set of people, based on the person's role and the territory structure of the company. An invocation of this web service 330 might return all commission payments for 2002 for Territory Managers in North America.

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.

Description

    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.
  • BACKGROUND
  • 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 program's 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.
  • DESCRIPTION OF DRAWINGS
  • 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 105 a, and optionally, a second parameterized workbook 105 b and an nth parameterized workbook 105 n. The parameterized workbooks 105 a, 105 b . . . 105 n are referred to generally as 105. The first parameterized workbook 105 a 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 105 b (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.Tenitory 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 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 105 a 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 105 a 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_ProductLineCommissionCalculations.” 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 ProductLineCommissionCalculations “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_ProductLineCommissionCalculati-ons.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_ProductLineCommissionCalculations 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 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 105 a 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., 105 a, 105 b . . . 105 n). 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 105 a that depends upon values calculated by subsidiary workbook instantiations (e.g., 105 b . . . 105 n). 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., 105 a′ . . . 105 n′) 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 105 a (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 105 a 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., 105 a′ . . . 105 n′, 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>
       <NVQuantityImpl type=“ByCellName” valueType=“Real”>
        <NVCell name=“Commission_YTD”
        workbook=“SAM_ProductLineCommissionCalculations”/>
       </NVQuantityImpl>
      </NVRule>
     </NVDecisionTree>
    </NVQuantity>
  • The file in this example names this virtual workbook 205 “CommissionEarnedYTD” and defines the type of the virtual workbook 205 as “ByDecisionTree.” As defined in this example, the selection module 215 uses the two rules of the decision tree to select the appropriate concrete parameterized workbook 105′. Both rules examine the parameter 110′ named “person” and the value of its attribute “position.” According to the first rule, if the value of the position attribute is sales rep, district manager or regional manager, the selection module 215 selects the parameterized workbook 105′ Rep_ProductLineCommissionCalculations. The associated output 120′ returned after this selection is the value from the cell in the selected workbook named Commission_YTD. According to the second rule, if the value of the position attribute is strategic account manager, the selection module 215 selects the parameterized workbook 105′ SAM_ProductLineCommissionCalculations. The associated output 120′ returned after this selection is the value from the cell in the selected workbook named Commission_YTD. Although the exemplary embodiment above uses two rules and a decision tree, it is understood that the system can employ other selection rules and mechanisms to select a concrete workbook 105′ from the set 220.
  • In broad overview, FIG. 3 illustrates an embodiment of a system 300 to generate and use parameterized 105 and virtual 205 workbooks in accordance with the invention. The system 300 includes an application server node 305, a calculation module 310, a storage module 315 and a client node 320. The system 300 can optionally include models 325 and miscellaneous Web services 330.
  • The application server node 305, the calculation module 310 and the storage module 315 communicate with each other via a network 340 a using communication channels 345 a, 345 b, and 345 c, respectively. The client node 320 communicates with the application server node 305 via a network 340 b using communication channels 335 d and 335 e, respectively. The networks 340 a and 340 b can also be part of the same network 340 as shown with optional connection 340 c. If optional connection 340 c is included, then one of the application server node's 305 communication channels 345 a or 345 e can be eliminated. If the models 325 and the miscellaneous Web services 330 are included in the system 300, they communicate with the network 340 b using communication channels 335 f and 335 g, respectively. The networks 340 a, 340 b and 340 c are generally referred to as 340. The communication channels 345 a, 345 b, 345 c, 345 d, 345 e, 345 f and 345 g are generally referred to as 345.
  • For example, the networks 340 and the communication channels 345 can include and/or be part of an internal bus, a local-area network (LAN), such as a company Intranet, a wide area network (WAN) such as the Internet or the World Wide Web and/or the like. The networks 340 and the communication channels 345 represent, for example, standard telephone lines, LAN or WAN links (e.g., T1, T3, 56 kb, X.25), broadband connections (ISDN, Frame Relay, ATM), wireless connections (cellular, WLAN, WWAN, 802.11) and/or the like. The connections can be established and data can be exchanged using a variety of communication protocols and languages (e.g., HTTP(S), TCP/IP, SSL, PPTP, HTML, XML, SOAP, IPX, SPX, NetBIOS, Ethernet, RS232, direct asynchronous connections, VPN protocols, a proprietary protocol, a proprietary language and/or the like). In one embodiment, the servers 305, 310 and 315 and the client 320 encrypt all communication when communicating with each other.
  • Each of the servers and modules 305, 310 and 315 can be any computing device capable of providing the services requested by the other servers and modules or by the client node 320. Particularly, this includes generating and processing parameterized workbooks as described herein. For clarity, FIG. 3 depicts server node 305 as an entity separate and distinct from modules 310 and 315, and each node 305, 310 and 315 can independently be in communication with the network 340 a, representing that the nodes 305, 310 and 315 are logically independent. It is to be understood, however, that the nodes 305, 310 and 315 can also be implemented individually or in any combination, for example, on a single server, distributed on portions of several (i.e., more than two) servers, and/or as part of a single server node or server farm in communication with the network 340 b through, for example, a single Web server (not shown).
  • The client node 315 can be any computing device (e.g., a personal computer, set top box, wireless mobile phone, handheld device, personal digital assistant, kiosk, etc) used to provide a user interface to access the application server 305. The client node 320 can include a browser module 350.
  • A user can use the system 300, inter alia, to generate and process parameterized 105 and virtual 205 workbooks. Two objectives of the present invention are to simplify the creation and maintenance of large spreadsheet-based computation models and to allow those models to be computed without manual intervention. The system 300 facilitates these objectives by storing tracking information about workbooks, including their parameters 110, parameter types and dependencies in the storage module 315. In one embodiment, the system 300 stores this information as a workbook information database. The workbook information database tracks and controls the names and storage locations of all parameterized 105 and virtual 205 workbooks used in the system 300. The database records information about the parameters 110 and parameter types and outputs 120 and output types of each workbook (e.g., 105, 205) and the dependencies between workbooks that exist when one workbook refers to another workbook using a formula. Each workbook instantiation may depend on one or more instantiations of the same workbook or of other workbooks. The dependency information allows the system 300 to perform workbook computations without manual intervention, for example in an application server 305 environment, because the system 300 can determine all dependent workbooks, open them, instantiate them and resolve all linkages between the workbooks appropriately. Table 1 represents an embodiment of parameter and where-used data stored in the workbook information database.
  • TABLE 1
    Workbook: Name: DM_Sales_Rank
    Location: /workbooks/DM_Top_Performers.xls
    Associated Parameters:
    Name: Type: Restriction:
    Person Class Person none
    Period Class Period type = Year
    MonthIndex Enumeration[1 . . . 12] none
    Associated Outputs:
    Name: Type: Cell:
    TopRank Number MaximumRank
    Dependencies:
    Dependency Type: Description:
    Workbook Output CommissionCalculations, output
    YTDCommission
    Data Query PeopleByManager
  • As shown in Table 1, the database stores the name, the type and any restrictions of the parameters 110 associated with the represented workbook. The names of parameters associated with the represented workbook are person, period and monthindex. Each parameter has a defined type. Class person and class period are class types that define the attributes associated with the respective parameter. Enumeration is an index type that associates an index with a corresponding calendar month. The restrictions data notes any restrictions on the parameters associated with this workbook and in this example, there is a restriction on the value of the parameter period to be stated as a year.
  • In the Table 1 example, the database also stores the name, the type and reference information on all defined outputs of the represented workbook. The names of the defined output shown is TopRank, which is mapped to a cell named MaximumRank within the workbook.
  • In the Table 1 example, the database also stored data on dependencies of the workbook on other workbooks or external data sources. There are two dependencies shown. One dependency shows that the represented workbook calculation depends on the output of a calculation from another workbook, the YTDCommissions output of the CommissionCalculations workbook. The second dependency indicates that the represented workbook uses a data range that depends on a named data query PeopleByManager.
  • In one embodiment, the workbook information database also tracks multiple versions of workbooks (e.g., 105, 205) as they are created and modified over time. This version information provides a historical record of changes, allows changes to be rolled back, and can provide for stability of prior computations (changes to a workbook do not necessarily have to affect existing computations, which can continue to use the previous version of the workbook at the user's discretion). The system 300 can also use version tracking of workbooks to limit the visibility of certain workbook versions to particular people. For example, a new workbook version might be visible only to a set of “approvers” as part of a formal approval process. Once the new version has been approved, it is then made visible globally and affects the computations seen by everyone. Table 2 represents an embodiment of version data stored in the workbook information database.
  • TABLE 2
    Versions:
    Branch: Version: State: Effective: Labels:
    Main 1 Published Anytime None
    Main
    2 Unpublished Aug. 01, 2002 None
  • The Table 2 example stores the version data for the represented workbook, including the branch, the version number the state of the version, the effective date of the version and any associated labels of the version. The branch values represent named branches within the version graph of the workbook. The version number is an ID for each version relative to its branch. Thus, a particular workbook version is uniquely identified by a combination of its branch name and version number. The state values represent whether the cached data associated with that workbook version has been published (e.g., available to users to view and/or edit). The effective value represents the time frame in which the version is valid. The labels values represent user-defined names that can be assigned to individual workbook versions.
  • The workbook information database also tracks information about the data sources used by each workbook (e.g., 105, 205). This information is used to detect when workbook results 120 are out of date and to efficiently recalculate the workbooks. For every reference in a workbook to external data, the system 300 records information in the workbook information database that represents a predicate describing the set of external data upon which the workbook computation depends. This predicate may be based, in part, on the values of workbook parameters 110. In other words, the results of different instantiations of the workbook may depend upon different sets of external data.
  • The system 300 may monitor each data source to detect changes to the data source, using the information in the workbook information database to determine which workbook instantiations are affected by any change. Several different means may be used to monitor the data source.
  • Data sources can include both internal and external data sources. An internal data source is a database (or other data storage mechanism) that is accessed only through the system 300. All changes to data in an internal data source are made through interfaces provided by the system 300 and these update interfaces can perform the necessary monitoring of the data source. An external data source is one that is not under the exclusive control of the system 300 and can be updated through other interfaces. For external data sources, the system 300 can use known techniques such as database triggers, database polling and/or data timestamps to provide similar levels of monitoring functionality. In the case of external data sources, the application server 105 is the active agent responsible for monitoring the external data source. The system 300 can use mechanisms used to monitor external data sources to monitor internal data sources as well.
  • By analyzing the computations and data dependencies within each workbook (e.g., 105, 205), the system 300 can in many cases optimize the data source monitoring required. For example, a particular workbook (e.g., 105, 205) may have a parameter 110 that is a time period (perhaps a calendar month). Analysis of the workbook computation may reveal that an instantiation of the workbook only depends on data for the month specified by the parameter. The data source monitoring subsystem can then optimize by remembering which months have had data updates and only recalculating workbook instantiations that depend on those months. If data updates to prior months are rare, this avoids many unnecessary calculations when referring to historical data.
  • An alternative or additional method the system 300 can use tracks data source dependencies and dependencies of one workbook instantiation on other workbook instantiations by observing and recording the data requests made by the referencing workbook and the results of these data requests. The system 300 compares this recorded data against newly computed data in the future to determine whether a workbook instantiation needs to be recalculated.
  • A third method the system 300 can use allows the user to supply surrogate tests for determining when a workbook instantiation needs to be recalculated. For example, the user may specify that instantiations of particular workbooks for prior periods should only be recalculated when specifically requested by the user and should be treated as up to date in all other situations.
  • In the system 300, a server processing component (not shown) or an auxiliary agent processing component (not shown) can perform data source monitoring. In one embodiment, these components are always active (or are started on demand by other components, such as a database server, which is always active). This allows the system 300 to monitor data source changes without the need for human intervention, unlike existing spreadsheet applications.
  • The system 300 supports distributing the computational load of evaluating a spreadsheet model (e.g., 100, FIG. 1) across multiple servers (i.e., calculation module 110 represents workbook calculations on multiple servers). By utilizing the workbook dependencies and data source dependencies stored in the storage module 115, as well as other workbook database information, the system 300 can schedule the execution of different workbook instantiations on different server computers.
  • Exploiting this parallelism improves the speed with which large models can be calculated, either for on-demand calculations or for automatic pre-calculations. Without the ability to perform true distributed processing, computations involving large networks of interconnected spreadsheets can be impractical. Complex models using parameterized 105 and virtual 205 workbooks typically exhibit a large amount of potential parallelism.
  • Distributed processing of workbook computations depends on the ability of the system 300 to separate the calculation of a referenced workbook from the use of a result from the referenced workbook by the referencing workbook. In one embodiment, the system 300 accomplishes this by utilizing special formula primitives to allow one workbook to refer to a result of another workbook, although other mechanisms are possible.
  • To perform the distributed workbook calculation component, the system 300 can utilize dependency information recorded in the database for efficiency, but does not need to. The system 300 can also schedule distributed executions of workbook computations dynamically as the computation proceeds. For example, if one workbook (e.g., 105, 205) references outputs 120 from a number of subsidiary workbooks (e.g., 105 b . . . 105 n), the computation of the subsidiary workbooks can proceed in parallel as long as the parameter 110 values for one instantiation do not depend on the output 120 of a different subsidiary workbook instantiation.
  • In addition to distributed computing, the system also utilizes caching algorithms and a database of cached computation results to optimize the performance of evaluating large computational models, especially models created as a network of interrelated workbooks. The system 300 stores results 120 and/or other intermediate calculations in the storage module 115 so that these values are not only available after the workbook file has been loaded into the memory of the spreadsheet program and opened. By caching the results in, for example a database, the system 300 can provide faster access and improve the performance of multi-workbook computations. The system 300 may cache values for many different instantiations of the same workbook with different parameter values. The system 300 can also distinguish result values of workbooks that are the values of intermediate computations from subsidiary workbooks performed in the process of instantiating some other workbook and may also cache these result values in the database. These result values or aggregations of these result values, which may come from many different workbooks and workbook instantiations, can then be queried and manipulated using standard database tools. These result values, whether cached or not, can also be accessed through application programming interfaces provided by the system 300, including network-based interfaces such as web services.
  • The system can perform workbook calculations either on demand (e.g. when a particular result is requested by an end user through a web browser or some other user interface) or automatically by pre-calculating and caching the results of the pre-calculations, using for example a process on the server 305, before they are requested. Automatic pre-calculation improves the interactive performance of the system 300 when users subsequently request these computations. The system 300 can perform these automatic pre-calculations at times when the servers 305, 310, 315 would otherwise be idle.
  • The system 300 uses information about workbook parameter types and workbook dependencies to determine the set of computations to pre-calculate. Parameter type information determines the set of allowable values for each parameter, and thus the set of all possible instantiations that may be requested for a particular workbook. The set of allowable values for a parameter may depend not only on the parameter type, but also on the state of some data source. So, for example, if a new user is added, then some workbook parameters may now have additional legal values. The data source monitoring of the system 300 detects this change to the data source and may pre-calculate and cache new results for the newly possible instantiations of these workbooks.
  • The system can perform automatic pre-calculation and caching for intermediate results (e.g. the output of one workbook instantiation, which is used by another workbook instantiation), for results that may be displayed for the user, and/or for a set of aggregated result values that are cached so that they can be efficiently queried. Aggregated result values might include time series or multi-dimensional data, such as daily gross profit broken down by region and product line.
  • The system 300 can use several different factors to decide which values should be precalculated and cached. One factor, for example, is information available within the system 300 about dependencies between workbooks and the size of individual computations. As described above, this information can be stored in the storage module 315, in for example a database. The system 300 uses this information to estimate the potential cost savings for pre-calculating particular computations. Another factor, for example, is historical access patterns and frequency of data accesses (e.g., including web page hits), which provides predictive information about the demand for particular data values or workbook instantiations in the future. A third factor, for example, is user specified instructions about which workbook instantiations to pre-calculate and which data values (including aggregated result values) to cache.
  • FIG. 4 illustrates an embodiment of a screen shot 400 of a specific example of parameterized workbook 105 (FIG. 1) that the system 300 (FIG. 3) can generate and process in accordance with the invention. The screenshot 400 includes a first portion 405 indicating the name of the workbook containing the associated spreadsheet formulas and a second portion 415 indicating an exemplary formula used for producing descriptive text based on the workbook parameters 110 and a third portion 420 showing the resulting descriptive text displayed in a cell. The screenshot 400 also includes a fourth portion 425 illustrating tabular data calculated using a data range. The table 425 includes a prototype range 430 and five columns 435 a, 435 b, 435 c, 435 d and 435 f. The screenshot 400 also includes a fifth portion 450 illustrating results 120 in a graphical format.
  • In this example, the name of the parameterized workbook 105 represented by this screenshot 400 is DM_Sales_Rank, representing a ranking of all people reporting to a particular district manager who is identified by a person parameter of the workbook. To generate this parameterized workbook 105, the system 300 instantiates workbook module 115 named DM_Top_Performers.xls, as indicated by the first portion 405.
  • The equation in the second portion 415 uses the parameters 110 “person” and “period” as part of the formula. When the system 300 instantiates the workbook, the system 300 binds the value of the parameters person and period sent with the call to generate that instance. The result of the formula is a descriptive text string based on the workbook parameter values, shown in a cell in the third portion 420.
  • In this example, the system 300 generates the table 425 using a data range. A data range, as used herein, is a spreadsheet extension that associates a data query with a variable sized set of workbook cells. The features of the data range of the system 300 provide facilities for performing automated, data-driven spreadsheet calculations based on variable input data.
  • A data range of the system 300 associates a parameterized data query with a range of workbook cells. Each data query parameter is identified by name and/or position. Each query parameter has a type that constrains the allowable values for the parameter. Queries are expressed in a textual syntax that a user and/or administrator creates. A data range can also refer by name to a pre-defined query; A data range defines a set of query parameter value expressions using the spreadsheet formula language. There is one expression for each required query parameter in the query. The system 300 uses the results of these expressions as the values of the query parameters when the system 300 evaluates the data range query.
  • In general overview, a data range can include a prototype range, a replication direction, an output range, an output range name, a sorting specification and a row insertion specification. The prototype range specifies a set of “template” cells that the system 300 replicates once for each set of results (or row) the query returns. The prototype range is usually one cell high (or alternatively one cell wide), but it can also be rectangular. The replication direction indicates whether the cells from the prototype range are replicated downwards or to the right. The output range is the set of cells that are populated after the system 300 evaluates the data range by replicating the formulas from the prototype range once for each set of results (or row) from the data range query. The output range is of variable size, depending on the query results, which in turn depend on the values of the query parameters, which in turn usually depend, directly or indirectly, on the parameters to the workbook containing the data range. The creator of a data range does not provide formulas for cells in the output range, the formulas for these cells and the resulting cell values are computed by the process of replicating the formulas from the prototype range. When the prototype range is replicated, formatting information is replicated as well as cell formulas. Therefore, the prototype range controls the visual presentation of the data range as well as the calculated values.
  • The system 300 binds the output range name to the output range of the data range after each time the data range is modified or re-evaluated. This mechanism facilitates writing formulas that extract selected data from the data range without depending on the size of the data range, because formulas can refer to the range through this name rather than referencing a fixed set of cells that can change over time. The output range name is optional. The sorting specification lists one or more columns (or rows) of the data range and for each column whether the column should be sorted in ascending or descending order. The sorting takes place after the system 300 evaluates the formulas for each cell in the output range and is based on the result shown in each sorted cell of the output range. This post-evaluation sorting is independent of, and in addition to, any pre-evaluation sorting performed by the query associated with the data range. The sorting specification is optional. The row insertion specification controls the behavior when the output range grows and shrinks. The row insertion specification determines whether cells are either (a) inserted and deleted as the range grows and shrinks or (b) overwritten and cleared as the range grows and shrinks. In addition, in case (a), the row insertion specification indicates whether the entire set of rows or columns outside of the data range is replicated when the data range is evaluated or just the cells within the data range itself.
  • In other embodiments, the data range can include other aspects. These other aspects can include options for handling special cases, such as when the query returns zero rows or one row. For example, when a query returns zero rows, the prototype range can be hidden (so that it occupies no space in rendered output) or the prototype range can display as blank fields independent of the values produced by the formulas in the prototype range. Other aspects can include options for formatting the data range as a whole, such as borders to apply to the entire data range or striping effects created by alternating the background colors on each row of the output range as shown in the table 425 in FIG. 4. Other aspects can also include options for special rendering of the data range in particular output formats. For example, the data range might render in HTML in a manner that allows the user to sort or pivot the data range or otherwise interact with and/or change the data range or how it is displayed in a web browser 350.
  • In general overview, to evaluate a data range, the system 300 evaluates each parameter value expression for the data range query. The system 300 then evaluates the query itself. The query expression may depend on the parameter value expressions, which may in turn depend on other cells in the workbook and (directly or indirectly) on the workbook parameter values. The query evaluation returns a set of rows, where each row contains multiple values. Each value in a row is identified by a name (the same names are used for the corresponding values in each row). The values may have a variety of data types, including, for example, numbers, strings, dates and references to complex objects.
  • For each row the query returns, the system 300 replicates the prototype range, either downward or to the right, depending on the replication direction. To replicate a cell, the system 300 evaluates the formula from the prototype cell in the context of the new cell, with the same behavior as if the user had copied the cell. In addition, formulas within a data range can refer to the named values of the current row by using the names as defined workbook names for the duration of the evaluation of the formula. When replicating a cell in a data range, the system 300 also replicates the formatting associated with the cell.
  • Referring back to the FIG. 4 example, the data range for the table 425 is associated with a named query, for example, a PeopleByManager query, which is a predefined query taking as parameters a person and a time period. The parameter expressions for this data range (not shown) simply pass the corresponding workbook parameters as the parameters values for the query. This query returns a set of rows with two values in each row, an “EMPLOYEE” value and a “POSITION” value. The “EMPLOYEE” value is a unique identifier for a complex person object reporting to this manager, which is Abraham Lincoln as indicated in portion 420. The “POSITION” value is a string indicating the person's position within the organization (e.g. “Sales Rep”). The prototype range 430 consists of cells A19 to F19, which are formatted to be green in this example. Cell A19 contains the formula=PersonName(EMPLOYEE). This formula uses a system accessor function (PersonName) to return the full name of the employee object in each row. The resulting names fill column 435 a within the data range. Cell B19 contains the formula=NVQuantity(“Quota”, EMPLOYEE, Year(period)). This formula uses a system spreadsheet extension (NVQuantity) to return a calculated value (i.e., the current employee's quota) for the year containing the period parameter to the workbook itself. In this case, the system 300 retrieves the Quota quantity by retrieving a value from a database. It is noteworthy that this formula refers to both a workbook parameter (i.e., period) and a named value in the row (i.e., EMPLOYEE). The system 300 displays the corresponding value for each selected employee within the data range in column 435 b. Cell E19 contains the formula=NVQuantity(“CommissionEarnedYTD”, EMPLOYEE, period). This formula references a value calculated, in this case, as the output of another parameterized workbook 105. The system 300 displays the computed value for each selected person within the data range in column 425 e. The formula in cell F19 contains the expression=E19/C19. This expression calculates the ratio between the person's year to date commission and their total sales. This formula uses relative references to perform this calculation. As the system replicates this formula in the output range, the system preserves the relative references so that the system 300 calculates the ratio correctly in each row.
  • It is understood that a creator of a workbook can reference the system formula language extensions (e.g., PersonName and NVQuantity) in any cell in a parameterized 105 or virtual 205 workbook, not only in cells within a data range. The process of evaluating a data range or the process of evaluating a formula containing a system formula language extension may cause the system 300 to perform further processing. For example, a reference to a quantity such as CommissionEarnedYTD, which is implemented as the output 120 of another workbook (e.g., 105, 205), may cause the system 300 to instantiate other workbooks (recursively) as a consequence of instantiating the original workbook.
  • When an output 120 from another workbook (e.g., 105, 205) is needed, the system 300 instantiates the workbook unless a cached value for the required output 120 is available and up to date. The system 300 searches the storage module 315 to determine if such a cached value is available. If so, then the system determines whether any dependencies of the cached value have changed since the value was cached. Dependencies of the cached value include the workbook that calculated the value, any queries or external references, such as NVQuantity expressions made during the instantiation of the workbook that produced the cached value, and any outputs from other workbooks that were consumed by the workbook that calculated the value. The system 300 can use various methods to circumvent full dependency checks, including, for example, user specified rules defining how often to update cached values.
  • If a given quantity has no cached value or the cached value is not up to date, then the system 300 instantiates the target workbook. After the target workbook is instantiated, the system 300 retrieves the designated output 120 from the workbook instantiation and then caches the updated result if caching is enabled for the specified system quantity. A workbook output 120 may be specified by cell coordinates (e.g. Sheet “Summary”, cell B10) or more typically by referring to a named workbook quantity within the workbook (e.g. “YTDCommission”). The named quantity typically refers to a predefined cell within the workbook that contains the desired output value, but it may also refer to a range of cells or other kinds of system formula language expressions. Each workbook may specify several different outputs, and the system cache maintenance module may choose to cache multiple outputs from the workbook instantiation even though only one output from the instantiation was originally requested. The system 300 may need these other cached output values for other system calculations.
  • In the example, the data range in the DM_Top_Performers workbook requests year-to-date commission numbers for each employee reporting to the specified manager (i.e., Abraham Lincoln). This results in a variable number of workbook instantiations. The system may find some of the requested outputs cached in the storage module 315 and may have to recompute others. The creator of the CommissionEarnedYTD quantity can implement that quantity by a set of virtual workbook rules that selects different workbooks for different sets of input parameters. In the example, the system 300 may calculate commissions for sales reps by one workbook while calculating commissions for district managers by a different workbook. For each workbook that needs to be instantiated, the application server 305 schedules the calculation of the workbook. The calculation of workbooks may be spread over multiple calculation server processes 310 running on multiple machines.
  • To further improve interactive performance, it is often useful to precalculate sets of system quantities that are frequently referenced. For example, it can be useful to precalculate some quantities at night after daily sales transactions have been posted. The system 300 utilizes the type system to guide precalculation. For example, the CommissionEarnedYTD quantity has a person parameter and a month parameter. It is possible to run an automated precalculation of this quantity for all people (or alternatively for Sales Reps in Europe) for the current month.
  • For example, as described above, cell C19 of the table 425 contains the formula=NVQuantity(“SalesCredits”, EMPLOYEE, period). The system can precalculate and cache the sales credits for each employee for each year. The entries for this quantity are shown in Table 3. As shown in Table 3, the system 300 stores the data using the ID attribute of a person type parameter.
  • TABLE 3
    Period
    ID 1998 1999 2000 2001 2002
    nfasth 1000000 1500000 1700000 1200000 1200000
    acabrera 1200000 1900000 2200000 1500000 1500000
    jolazabal 1900000 2900000 3400000 2400000 2400000
    ascott 1200000 1900000 2200000 1500000 1500000
    jhaas 2200000 3300000 3900000 2800000 2800000
    sflesch 1500000 2300000 2600000 1900000 1900000
    checkman 500000 800000 900000 700000 700000
    bmay 1800000 2600000 3100000 2200000 2200000
    ksutherland 1100000 1700000 2000000 1400000 1400000
    sstricker 1500000 2300000 2600000 1900000 1900000
    fcouples 1900000 2800000 3300000 2300000 2300000
    sames 1900000 2800000 3300000 2300000 2300000
    xcarter 1700000 2500000 2900000 2100000 2100000
  • FIG. 5 illustrates an embodiment of a screen shot 500 of a specific example of virtual workbook 205 (FIG. 2) that the system 300 (FIG. 3) can generate and process in accordance with the invention. Using the network browser module 350 (FIG. 3) (e.g., a web browser) on a client device 320 (FIG. 3), a manager (e.g., Harvey Mackay 505) can log in to the application server 305 (FIG. 3) and request a summary report 510, including a table portion 512. The manager typically specifies the period of interest 515, or, in one embodiment, the period defaults to the current year. A virtual workbook 205 implements the report 510. A virtual workbook 205 selects a particular concrete workbook 105′ based on rules associated with the definition of the virtual workbook. The rules are based on the parameters of interest, in this case, the manager 505 and the period 515 for which the report is requested. The rules can also utilize globally available information, such as the identity of the user who is logged in, the current date, and the like. The application server 305 processes the rules defining the virtual workbook and selects a particular concrete workbook, for example, RegMgr_MonthlySummary. The concrete workbook is then instantiated by the calculation server 310 and the print area, or an alternatively specified output range, of the specified worksheet within the workbook is rendered as, for example, HTML and returned to the network browser 350. The system 300 can also render this returned data in many different formats besides the standard HTML. To allow the user to organize this returned data by user preference, the system 300 can make this data interactive.
  • For example, as illustrated in FIG. 5 the summary report 510 includes a table portion 512 representing a data range, similar to the data range of table 425 (FIG. 4) described above. The system 300 renders a data range in different output formats (e.g., HTML) in such a way as to provide partial interactivity. Table portion 512 illustrates an example of an HTML rendering of a data range that is interactive. The creator of a spreadsheet containing a data range (e.g., table 425) is not necessarily the same person who might view an instantiation of the spreadsheet (e.g., table portion 512) through a network browser 350. Using system 300, the creator of the spreadsheet can define the data range 512 to have certain interactive features when a user views that data range 512. These interactive features allow some operations on the data, but not necessarily all of the operations available to the creator of the data range, since the creator is working through a different interface, for example the interface shown in FIG. 4.
  • For example, the system 300 can allow sorting of columns of the data range 512 when rendered in a network browser 350, for example by clicking with a mouse, using keystrokes and/or other user input. This allows the viewer of the information to sort the table in ascending or descending order based on the alpha/numeric entries of a selected column. As another example, the rows of a data range might be grouped in a hierarchy, for example, a geographical hierarchy of continents, countries and states or provinces. The creator can define the data range so that the system 300 allows a user to interactively expand and collapse the hierarchy using, for example, mouse clicks, keystrokes and/or other user input.
  • To instantiate the RegMgr_MonthlySummary workbook, the calculation server 310 binds the workbook parameters to their corresponding values (e.g. “person” to “Harvey Mackay” and period to “2002”). Then the calculation server 310 evaluates all formulas in the workbook to ensure they are up to date. In one embodiment, the system 300 implements the workbook parameters 110′ as named quantities within the workbook and workbook formulas can refer to these names directly. As illustrated, a user can also change parameter values using the pull-down menus 525 a and 525 b. In response to changing the parameter values and clicking on the “GO” button 530, the system 300 will re-evaluate the virtual workbook 205, selecting a concrete workbook 105′, which may be the same as the prior concrete workbook (RegMgr_MonthlySummary) or may be different. The concrete workbook is then instantiated and rendered as described above using the changed parameter values.
  • It is also possible to access system quantities through interfaces other than by requesting reports through a web browser 350. For example, the system 300 allows exposure to quantities via web services 330. One web service 330 can be defined, for example, to return a matrix of monthly commission payments for a particular year for a certain set of people, based on the person's role and the territory structure of the company. An invocation of this web service 330 might return all commission payments for 2002 for Territory Managers in North America.
  • A number of embodiments of the invention have been described. Nevertheless, it will be understood that various modifications may be made without departing from the spirit and scope of the invention. For example, processing can be distributed in many different configurations, additional parameters can be defined and additional types of computations can be modeled. Accordingly, other embodiments are within the scope of the following claims.

Claims (19)

1. A method for utilizing a spreadsheet, the method comprising:
defining a parameter associated with the spreadsheet; and
generating a data query based at least in part on the parameter.
2. The method of claim 1, further comprising:
generating an output based at least in part on the results of the data query.
3. A method for utilizing a spreadsheet, the method comprising:
defining a template within the spreadsheet; and
generating an output based at least in part on the template.
4. The method of claim 3, further comprising:
generating a data query based at least in part on a parameter associated with the spreadsheet.
5. The method of claim 3 wherein one or more cells within the template contain formulas.
6. The method of claim 5 wherein the formulas are written in spreadsheet formula language.
7. The method of claim 3 wherein generating an output further comprises:
replicating one or more cells within the template.
8. The method of claim 7 wherein replicating one or more cells further comprises:
preserving relative cell references.
9. The method of claim 7 wherein replicating one or more cells further comprises:
replicating formatting of the template cells.
10. The method of claim 7 wherein replicating a cell 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.
11. The method of claim 10, further comprising:
performing special processing on the output when the data query returns no associated values.
12. The method of claim 10, further comprising:
automatically sorting the output based at least in part on the associated values of the one or more cells in the output.
13. The method of claim 3, further comprising:
associating a formula language name with the output.
14. The method of claim 3, further comprising:
automatically updating the output when a change is detected.
15. The method of claim 14, wherein the change comprises a change to:
i) template cell formulas
ii) template cell formatting
iii) template cell values, or
iv) data query parameters.
16. A method for utilizing a spreadsheet, the method comprising:
defining an output range within the spreadsheet;
rendering the output range; and
allowing a user to modify the rendered output range.
17. The method of claim 16, further comprising:
rendering the output range using HTML.
18. The method of claim 16, wherein allowing a user to modify the rendered output range further comprises:
allowing the user to sort columns within the output range using a user input.
19. The method of claim 16, wherein allowing a user to modify the rendered output range further comprises:
allowing a user to interactively expand and collapse a hierarchy using a user input.
US12/855,282 2001-07-13 2010-08-12 System and method for dynamic data access in a spreadsheet with external parameters Abandoned US20110191665A1 (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
US12/855,282 US20110191665A1 (en) 2001-07-13 2010-08-12 System and method for dynamic data access in a spreadsheet with external parameters
US13/604,085 US20130013994A1 (en) 2001-07-13 2012-09-05 System and method for dynamic data access in a spreadsheet with external parameters

Applications Claiming Priority (4)

Application Number Priority Date Filing Date Title
US30521701P 2001-07-13 2001-07-13
US10/193,015 US7251776B2 (en) 2001-07-13 2002-07-11 System and method for efficiently and flexibly utilizing spreadsheet information
US11/818,852 US20080028287A1 (en) 2001-07-13 2007-06-18 System and method for dynamic data access in a spreadsheet with external parameters
US12/855,282 US20110191665A1 (en) 2001-07-13 2010-08-12 System and method for dynamic data access in a spreadsheet with external parameters

Related Parent Applications (1)

Application Number Title Priority Date Filing Date
US11/818,852 Continuation US20080028287A1 (en) 2001-07-13 2007-06-18 System and method for dynamic data access in a spreadsheet with external parameters

Related Child Applications (1)

Application Number Title Priority Date Filing Date
US13/604,085 Continuation US20130013994A1 (en) 2001-07-13 2012-09-05 System and method for dynamic data access in a spreadsheet with external parameters

Publications (1)

Publication Number Publication Date
US20110191665A1 true US20110191665A1 (en) 2011-08-04

Family

ID=26888595

Family Applications (6)

Application Number Title Priority Date Filing Date
US10/193,015 Expired - Fee Related US7251776B2 (en) 2001-07-13 2002-07-11 System and method for efficiently and flexibly utilizing spreadsheet information
US11/818,856 Abandoned US20080034281A1 (en) 2001-07-13 2007-06-18 System and method for dynamic binding of a spreadsheet with external parameters
US11/818,852 Abandoned US20080028287A1 (en) 2001-07-13 2007-06-18 System and method for dynamic data access in a spreadsheet with external parameters
US12/855,282 Abandoned US20110191665A1 (en) 2001-07-13 2010-08-12 System and method for dynamic data access in a spreadsheet with external parameters
US12/959,525 Abandoned US20110276869A1 (en) 2001-07-13 2010-12-03 System and method for dynamic binding of a spreadsheet with external parameters
US13/604,085 Abandoned US20130013994A1 (en) 2001-07-13 2012-09-05 System and method for dynamic data access in a spreadsheet with external parameters

Family Applications Before (3)

Application Number Title Priority Date Filing Date
US10/193,015 Expired - Fee Related US7251776B2 (en) 2001-07-13 2002-07-11 System and method for efficiently and flexibly utilizing spreadsheet information
US11/818,856 Abandoned US20080034281A1 (en) 2001-07-13 2007-06-18 System and method for dynamic binding of a spreadsheet with external parameters
US11/818,852 Abandoned US20080028287A1 (en) 2001-07-13 2007-06-18 System and method for dynamic data access in a spreadsheet with external parameters

Family Applications After (2)

Application Number Title Priority Date Filing Date
US12/959,525 Abandoned US20110276869A1 (en) 2001-07-13 2010-12-03 System and method for dynamic binding of a spreadsheet with external parameters
US13/604,085 Abandoned US20130013994A1 (en) 2001-07-13 2012-09-05 System and method for dynamic data access in a spreadsheet with external parameters

Country Status (3)

Country Link
US (6) US7251776B2 (en)
AU (1) AU2002318298A1 (en)
WO (1) WO2003007118A2 (en)

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20110276870A1 (en) * 2007-10-29 2011-11-10 Microsoft Corporation Calculation of spreadsheet data
US10140280B2 (en) 2017-03-07 2018-11-27 Microsoft Technology Licensing, Llc Asynchronous dynamic functions for calculation engines
US10846469B2 (en) 2017-03-07 2020-11-24 Microsoft Technology Licensing, Llc Dynamically registered functions for calculations engines
US11556529B2 (en) * 2019-08-13 2023-01-17 Sigma Computing, Inc. Top frequency worksheet filtering

Families Citing this family (129)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
GB0028434D0 (en) * 2000-11-22 2001-01-10 Decision Curve Ltd Methods and apparatus for automatically producing spreadsheet-based models
US6826729B1 (en) * 2001-06-29 2004-11-30 Microsoft Corporation Gallery user interface controls
US7251776B2 (en) * 2001-07-13 2007-07-31 Netview Technologies, Inc. System and method for efficiently and flexibly utilizing spreadsheet information
US6857118B2 (en) * 2001-07-25 2005-02-15 The Mathworks, Inc. Function values in computer programming languages having dynamic types and overloading
US20040044954A1 (en) * 2002-08-29 2004-03-04 Hosea Michael E. Data-bidirectional spreadsheet
EP1540488A4 (en) * 2002-08-29 2008-01-16 Press Sense Ltd End user customizable computer spreadsheet application based expert system
US7370271B2 (en) * 2002-10-30 2008-05-06 Actuate Corporation Methods and apparatus for generating a spreadsheet report template
US7680818B1 (en) * 2002-12-18 2010-03-16 Oracle International Corporation Analyzing the dependencies between objects in a system
US7039874B2 (en) * 2003-01-14 2006-05-02 Mirant Intellectual Asset Management And Marketing Interface for modifying data fields in a mark-up language environment
US7017112B2 (en) * 2003-02-28 2006-03-21 Microsoft Corporation Importing and exporting markup language data in a spreadsheet application document
US7392475B1 (en) * 2003-05-23 2008-06-24 Microsoft Corporation Method and system for automatic insertion of context information into an application program module
US7197696B1 (en) * 2003-06-05 2007-03-27 Pavan Vidyadhar Muzumdar System, method and computer program product to populate data into spreadsheets and execute functions and programs within the same
US9715678B2 (en) 2003-06-26 2017-07-25 Microsoft Technology Licensing, Llc Side-by-side shared calendars
US7707255B2 (en) 2003-07-01 2010-04-27 Microsoft Corporation Automatic grouping of electronic mail
US8799808B2 (en) 2003-07-01 2014-08-05 Microsoft Corporation Adaptive multi-line view user interface
US7716593B2 (en) 2003-07-01 2010-05-11 Microsoft Corporation Conversation grouping of electronic mail records
US7392249B1 (en) * 2003-07-01 2008-06-24 Microsoft Corporation Methods, systems, and computer-readable mediums for providing persisting and continuously updating search folders
US20050021504A1 (en) * 2003-07-22 2005-01-27 Charles Atchison Methods, systems, and computer program products for querying a database for employee data and organizing the obtained data
US7233956B2 (en) * 2003-08-12 2007-06-19 International Business Machines Corporation Method and apparatus for data migration between databases
US10437964B2 (en) 2003-10-24 2019-10-08 Microsoft Technology Licensing, Llc Programming interface for licensing
US8892644B2 (en) * 2004-01-22 2014-11-18 Securesheet Technologies, Llc Method of enabling access to data structure
US20050197845A1 (en) * 2004-03-02 2005-09-08 Kai Wachter Business workbook precalculation
US7555707B1 (en) 2004-03-12 2009-06-30 Microsoft Corporation Method and system for data binding in a block structured user interface scripting language
US7664804B2 (en) * 2004-06-01 2010-02-16 Microsoft Corporation Method, system, and apparatus for exposing workbook ranges as data sources
US20060090156A1 (en) * 2004-06-08 2006-04-27 Richard Tanenbaum Program / method for converting spreadsheet models to callable, compiled routines
US7991804B2 (en) * 2004-07-30 2011-08-02 Microsoft Corporation Method, system, and apparatus for exposing workbooks as data sources
US8578399B2 (en) 2004-07-30 2013-11-05 Microsoft Corporation Method, system, and apparatus for providing access to workbook models through remote function cells
US7703036B2 (en) * 2004-08-16 2010-04-20 Microsoft Corporation User interface for displaying selectable software functionality controls that are relevant to a selected object
US8255828B2 (en) 2004-08-16 2012-08-28 Microsoft Corporation Command user interface for displaying selectable software functionality controls
US8117542B2 (en) 2004-08-16 2012-02-14 Microsoft Corporation User interface for displaying selectable software functionality controls that are contextually relevant to a selected object
US9015621B2 (en) 2004-08-16 2015-04-21 Microsoft Technology Licensing, Llc Command user interface for displaying multiple sections of software functionality controls
US7895531B2 (en) 2004-08-16 2011-02-22 Microsoft Corporation Floating command object
US8146016B2 (en) 2004-08-16 2012-03-27 Microsoft Corporation User interface for displaying a gallery of formatting options applicable to a selected object
US7925658B2 (en) * 2004-09-17 2011-04-12 Actuate Corporation Methods and apparatus for mapping a hierarchical data structure to a flat data structure for use in generating a report
US7747966B2 (en) 2004-09-30 2010-06-29 Microsoft Corporation User interface for providing task management and calendar information
US9047266B2 (en) * 2004-10-07 2015-06-02 International Business Machines Corporation Methods, systems and computer program products for processing cells in a spreadsheet
US8276150B2 (en) * 2004-10-12 2012-09-25 International Business Machines Corporation Methods, systems and computer program products for spreadsheet-based autonomic management of computer systems
US7716231B2 (en) * 2004-11-10 2010-05-11 Microsoft Corporation System and method for generating suggested alternatives for visual or audible submissions
US9009582B2 (en) 2004-11-19 2015-04-14 Google Inc. Converting spreadsheet applications to web-based applications
US7647551B2 (en) * 2004-12-15 2010-01-12 Microsoft Corporation System and method for formatting a cell in response to data stored in a separate location
US8458003B2 (en) * 2005-02-23 2013-06-04 Christopher Conigliaro Systems and methods for efficient delivery of financial advisory services
US7747939B2 (en) * 2005-05-31 2010-06-29 Microsoft Corporation Generating free form reports within a data array
US7886290B2 (en) 2005-06-16 2011-02-08 Microsoft Corporation Cross version and cross product user interface
US20060288284A1 (en) * 2005-06-20 2006-12-21 Michael Peters System and method for visualizing contextual-numerical data of source documents as corresponding transformed documents
US20070055556A1 (en) * 2005-07-06 2007-03-08 Frank-Backman Elizabeth G Spreadsheet Generator
US8239882B2 (en) 2005-08-30 2012-08-07 Microsoft Corporation Markup based extensibility for user interfaces
US8689137B2 (en) 2005-09-07 2014-04-01 Microsoft Corporation Command user interface for displaying selectable functionality controls in a database application
US9542667B2 (en) 2005-09-09 2017-01-10 Microsoft Technology Licensing, Llc Navigating messages within a thread
US7739259B2 (en) 2005-09-12 2010-06-15 Microsoft Corporation Integrated search and find user interface
US8627222B2 (en) 2005-09-12 2014-01-07 Microsoft Corporation Expanded search and find user interface
US8370317B2 (en) * 2005-09-22 2013-02-05 Microsoft Corporation Synchronizing shared resources in a collection
US7685152B2 (en) * 2006-01-10 2010-03-23 International Business Machines Corporation Method and apparatus for loading data from a spreadsheet to a relational database table
JP4866090B2 (en) * 2006-01-12 2012-02-01 株式会社アイ・エヌ情報センター Chart creation device, program
JP4140919B2 (en) * 2006-03-14 2008-08-27 インターナショナル・ビジネス・マシーンズ・コーポレーション System and method for performing processing such as spreadsheet
CN1821993A (en) * 2006-03-24 2006-08-23 无锡永中科技有限公司 Electronic form data processing method based on time sequence
US8307119B2 (en) 2006-03-31 2012-11-06 Google Inc. Collaborative online spreadsheet application
US9727989B2 (en) 2006-06-01 2017-08-08 Microsoft Technology Licensing, Llc Modifying and formatting a chart using pictorially provided chart elements
US8605090B2 (en) 2006-06-01 2013-12-10 Microsoft Corporation Modifying and formatting a chart using pictorially provided chart elements
US8028270B2 (en) * 2006-11-10 2011-09-27 International Business Machines Corporation Data dictionary file based source code generation method and system
US8072467B2 (en) * 2007-01-31 2011-12-06 Microsoft Corporation Request-driven on-demand processing
US20080222510A1 (en) * 2007-03-08 2008-09-11 Huy Nguyen Method and system for generating a history log associated with a spreadsheet
US20080243823A1 (en) * 2007-03-28 2008-10-02 Elumindata, Inc. System and method for automatically generating information within an eletronic document
US8484578B2 (en) 2007-06-29 2013-07-09 Microsoft Corporation Communication between a document editor in-space user interface and a document editor out-space user interface
US8201103B2 (en) 2007-06-29 2012-06-12 Microsoft Corporation Accessing an out-space user interface for a document editor program
US8762880B2 (en) 2007-06-29 2014-06-24 Microsoft Corporation Exposing non-authoring features through document status information in an out-space user interface
US10133719B2 (en) * 2007-09-28 2018-11-20 Microsoft Technology Licensing, Llc Spreadsheet workbook part libraries
US7836100B2 (en) * 2007-10-26 2010-11-16 Microsoft Corporation Calculating and storing data structures including using calculated columns associated with a database system
US8407668B2 (en) * 2007-10-26 2013-03-26 Microsoft Corporation Model based spreadsheet scripting language
WO2009114615A1 (en) * 2008-03-11 2009-09-17 Virtual Agility, Inc. Techniques for integrating parameterized information request into a system for collaborative work
US8812950B2 (en) * 2007-12-31 2014-08-19 Sap Ag Spreadsheet software services
US20090228776A1 (en) * 2008-03-05 2009-09-10 Microsoft Corporation Dynamic formulas for spreadsheet cells
US9558172B2 (en) * 2008-03-12 2017-01-31 Microsoft Technology Licensing, Llc Linking visual properties of charts to cells within tables
US9588781B2 (en) 2008-03-31 2017-03-07 Microsoft Technology Licensing, Llc Associating command surfaces with multiple active components
US9189478B2 (en) * 2008-04-03 2015-11-17 Elumindata, Inc. System and method for collecting data from an electronic document and storing the data in a dynamically organized data structure
US9665850B2 (en) 2008-06-20 2017-05-30 Microsoft Technology Licensing, Llc Synchronized conversation-centric message list and message reading pane
US8402096B2 (en) 2008-06-24 2013-03-19 Microsoft Corporation Automatic conversation techniques
US20090327213A1 (en) * 2008-06-25 2009-12-31 Microsoft Corporation Document index for handheld application navigation
US8290971B2 (en) 2008-09-09 2012-10-16 Applied Systems, Inc. Method and apparatus for remotely displaying a list by determining a quantity of data to send based on the list size and the display control size
US8938672B2 (en) * 2008-11-04 2015-01-20 International Business Machines Corporation Amending the display property of grid elements
US8799353B2 (en) 2009-03-30 2014-08-05 Josef Larsson Scope-based extensibility for control surfaces
US9053260B2 (en) * 2009-04-13 2015-06-09 Honeywell International Inc. Utilizing spreadsheet user interfaces with flowsheets of a CPI simulation system
US10095678B2 (en) * 2009-04-13 2018-10-09 Honeywell International Inc. Database user interfaces with flowsheets of a simulation system
US8645911B2 (en) 2009-04-20 2014-02-04 Exigen Properties, Inc. Systems, methods and machine readable mediums for defining and executing new commands in a spreadsheet software application
US9046983B2 (en) 2009-05-12 2015-06-02 Microsoft Technology Licensing, Llc Hierarchically-organized control galleries
US10462206B2 (en) * 2009-10-16 2019-10-29 Real Innovations International Llc Bidirectional networked real-time data exchange using a spreadsheet application
US10498796B2 (en) 2009-10-16 2019-12-03 Real Innovations International Llc System and method for providing real-time data
US8302014B2 (en) 2010-06-11 2012-10-30 Microsoft Corporation Merging modifications to user interface components while preserving user customizations
US9009617B2 (en) * 2010-07-28 2015-04-14 Sap Se Decision aiding user interfaces
US8799453B2 (en) 2010-10-20 2014-08-05 Microsoft Corporation Managing networks and machines for an online service
US8386501B2 (en) 2010-10-20 2013-02-26 Microsoft Corporation Dynamically splitting multi-tenant databases
US8417737B2 (en) 2010-10-20 2013-04-09 Microsoft Corporation Online database availability during upgrade
US8219575B2 (en) * 2010-11-12 2012-07-10 Business Objects Software Ltd. Method and system for specifying, preparing and using parameterized database queries
US8850550B2 (en) 2010-11-23 2014-09-30 Microsoft Corporation Using cached security tokens in an online service
US9721030B2 (en) 2010-12-09 2017-08-01 Microsoft Technology Licensing, Llc Codeless sharing of spreadsheet objects
US10546057B2 (en) * 2011-10-28 2020-01-28 Microsoft Technology Licensing, Llc Spreadsheet program-based data classification for source target mapping
US8799760B2 (en) * 2011-12-08 2014-08-05 Xerox Corporation Smart macros using zone selection information and pattern discovery
CN104285209B (en) 2011-12-29 2018-01-30 比博实验室公司 Suitable for the programming language based on electrical form of report generation
US20140081903A1 (en) * 2012-09-17 2014-03-20 Salesforce.Com, Inc. Methods and systems for displaying and filtering business analytics data stored in the cloud
US20140129513A1 (en) * 2012-11-08 2014-05-08 Callidus Software Incorporated Subset calculation by identifying calculated values with modified parameters
US20140129546A1 (en) * 2012-11-08 2014-05-08 Callidus Software Incorporated Incremental calculation by auto detection of changes to data
US9715576B2 (en) * 2013-03-15 2017-07-25 II Robert G. Hayter Method for searching a text (or alphanumeric string) database, restructuring and parsing text data (or alphanumeric string), creation/application of a natural language processing engine, and the creation/application of an automated analyzer for the creation of medical reports
US9466138B2 (en) * 2013-05-30 2016-10-11 Oracle International Corporation Attribute-based stacking for diagrams
US9384236B2 (en) 2013-06-14 2016-07-05 Sap Se Method and system for operating on database queries
US9026897B2 (en) 2013-07-12 2015-05-05 Logic9S, Llc Integrated, configurable, sensitivity, analytical, temporal, visual electronic plan system
US9116931B2 (en) 2013-07-12 2015-08-25 Logic9S, Llc Integrated, configurable, analytical, temporal, visual electronic plan system
US9483457B2 (en) * 2014-04-28 2016-11-01 International Business Machines Corporation Method for logical organization of worksheets
US9552348B2 (en) * 2014-06-27 2017-01-24 Koustubh MOHARIR System and method for operating a computer application with spreadsheet functionality
US11170165B1 (en) 2015-03-15 2021-11-09 Sigma Sciences Limited Data processing in spreadsheet worksheets
US10509858B1 (en) * 2015-03-15 2019-12-17 Sigma Sciences Limited Data processing in spreadsheet worksheets
US10735504B2 (en) * 2016-01-06 2020-08-04 Oracle International Corporation System and method for distributed workbook storage
US10268833B2 (en) * 2016-04-07 2019-04-23 International Business Machines Corporation Method for conditional permission control in a digital data sheet based on a formula
GB2549316A (en) * 2016-04-14 2017-10-18 Ge Aviation Systems Llc Transferring data from a first data environment to a second data environment
US10191897B1 (en) * 2016-04-19 2019-01-29 Workday, Inc. Data flow view for a spreadsheet
US10558744B2 (en) * 2016-11-20 2020-02-11 Real Innovations International Llc Bidirectional networked real-time data exchange using a spreadsheet application
US11726753B2 (en) 2016-12-03 2023-08-15 Thomas STACHURA Spreadsheet-based software application development
US10216494B2 (en) 2016-12-03 2019-02-26 Thomas STACHURA Spreadsheet-based software application development
US10540153B2 (en) 2016-12-03 2020-01-21 Thomas STACHURA Spreadsheet-based software application development
US11354494B2 (en) 2017-07-10 2022-06-07 Adaptam Inc. Methods and systems for connecting a spreadsheet to external data sources with formulaic specification of data retrieval
US11699032B2 (en) 2017-11-03 2023-07-11 Microsoft Technology Licensing, Llc Data set lookup with binary search integration and caching
US11016986B2 (en) * 2017-12-04 2021-05-25 Palantir Technologies Inc. Query-based time-series data display and processing system
US10705805B1 (en) 2017-12-12 2020-07-07 Amazon Technologies, Inc. Application authoring using web-of-sheets data model
CN108153724A (en) * 2017-12-30 2018-06-12 中铁广州工程局集团有限公司 A kind of method, apparatus and storage medium of the establishment measurement data based on EXCEL
US11586603B1 (en) 2018-05-23 2023-02-21 Amazon Technologies, Inc. Index sheets for robust spreadsheet-based applications
US11429558B2 (en) * 2018-10-11 2022-08-30 Dealvector, Inc. Mapping tests of spreadsheets in server-browser environments
CN111199145A (en) * 2019-12-30 2020-05-26 航天信息股份有限公司 Method, device, storage medium and electronic equipment for generating value-added tax report
US11836444B2 (en) 2020-06-26 2023-12-05 Adaptam Inc. Methods and systems for constructing a complex formula in a spreadsheet cell
EP4315150A1 (en) * 2021-03-31 2024-02-07 Microsoft Technology Licensing, LLC Dynamically limiting the scope of spreadsheet recalculations
US20220318232A1 (en) * 2021-03-31 2022-10-06 Microsoft Technology Licensing, Llc Dynamically limiting the scope of spreadsheet recalculations
WO2023249898A1 (en) * 2022-06-20 2023-12-28 People Center, Inc. Systems, methods, user interfaces, and development environments for a data manager

Citations (93)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US4912657A (en) * 1986-10-30 1990-03-27 Synthesis, Inc. Method and systems for generating parametric designs
US5033009A (en) * 1989-03-03 1991-07-16 Dubnoff Steven J System for generating worksheet files for electronic spreadsheets
US5293615A (en) * 1990-11-16 1994-03-08 Amada Carlos A Point and shoot interface for linking database records to spreadsheets whereby data of a record is automatically reformatted and loaded upon issuance of a recalculation command
US5303146A (en) * 1993-03-11 1994-04-12 Borland International, Inc. System and methods for improved scenario management in an electronic spreadsheet
US5317686A (en) * 1990-09-10 1994-05-31 Lotus Development Corporation Data processing apparatus and method for a reformattable multidimensional spreadsheet
US5319777A (en) * 1990-10-16 1994-06-07 Sinper Corporation System and method for storing and retrieving information from a multidimensional array
US5371675A (en) * 1992-06-03 1994-12-06 Lotus Development Corporation Spreadsheet program which implements alternative range references
US5396621A (en) * 1991-05-10 1995-03-07 Claris Corporation Sorting a table by rows or columns in response to interactive prompting with a dialog box graphical icon
US5437006A (en) * 1993-01-27 1995-07-25 Microsoft Corporation Spreadsheet command/function capability from a dynamic-link library
US5475836A (en) * 1987-04-01 1995-12-12 Lotus Development Corporation Interface for providing access to external data sources/sinks
US5590259A (en) * 1992-04-08 1996-12-31 Borland International, Inc. System and methods for improved spreadsheet interface with user-familiar objects
US5664182A (en) * 1995-09-18 1997-09-02 Actuate Software Corporation Persistent storage of report objects
US5682538A (en) * 1994-08-12 1997-10-28 Wall Data Incorporated Automatic adaptive computer screen generation
US5721847A (en) * 1994-10-21 1998-02-24 Microsoft Corporation Method and system for linking controls with cells of a spreadsheet
US5832532A (en) * 1995-06-16 1998-11-03 I2 Technologies, Inc. Model-independent and interactive report generation system and method of operation
US5890174A (en) * 1995-11-16 1999-03-30 Microsoft Corporation Method and system for constructing a formula in a spreadsheet
US5893123A (en) * 1995-06-22 1999-04-06 Tuinenga; Paul W. System and method of integrating a spreadsheet and external program having output data calculated automatically in response to input data from the spreadsheet
US5915257A (en) * 1994-10-11 1999-06-22 Brio Technology, Inc. Cross tab analysis and reporting method
US5926822A (en) * 1996-09-06 1999-07-20 Financial Engineering Associates, Inc. Transformation of real time data into times series and filtered real time data within a spreadsheet application
US5933818A (en) * 1997-06-02 1999-08-03 Electronic Data Systems Corporation Autonomous knowledge discovery system and method
US5956031A (en) * 1996-08-02 1999-09-21 Autodesk, Inc. Method and apparatus for control of a parameter value using a graphical user interface
US5966716A (en) * 1996-06-06 1999-10-12 Microsoft Corporation Automatic spreadsheet forms
US5983268A (en) * 1997-01-14 1999-11-09 Netmind Technologies, Inc. Spreadsheet user-interface for an internet-document change-detection tool
US6003012A (en) * 1997-12-05 1999-12-14 Square D Company Methodology and computer-based tools for design, production and sales of customized switchboards
US6009455A (en) * 1998-04-20 1999-12-28 Doyle; John F. Distributed computation utilizing idle networked computers
US6012057A (en) * 1997-07-30 2000-01-04 Quarterdeck Corporation High speed data searching for information in a computer system
US6023691A (en) * 1998-12-22 2000-02-08 Ac Properties B.V. Goal based stimulator utilizing a spreadsheet architecture
US6055548A (en) * 1996-06-03 2000-04-25 Microsoft Corporation Computerized spreadsheet with auto-calculator
US6085184A (en) * 1998-12-22 2000-07-04 Ac Properties B.V. System, method and article of manufacture for a dynamic toolbar in a tutorial system
US6122649A (en) * 1996-05-30 2000-09-19 Microsoft Corporation Method and system for user defined and linked properties
US6157934A (en) * 1995-10-24 2000-12-05 Ultimus, L.L.C. Method and apparatus for using distributed spreadsheets in a client/server architecture for workflow automation
US6208339B1 (en) * 1998-06-19 2001-03-27 International Business Machines Corporation User-interactive data entry display system with entry fields having distinctive and changeable autocomplete
US6256651B1 (en) * 1997-06-20 2001-07-03 Raja Tuli Time management workflow software
US6269377B1 (en) * 1998-09-21 2001-07-31 Microsoft Corporation System and method for managing locations of software components via a source list
US6292811B1 (en) * 1997-09-19 2001-09-18 Modernsoft, Inc. Populating cells of an electronic financial statement
US20010032214A1 (en) * 2000-04-14 2001-10-18 International Business Machines Corporation Method and system in an electronic spreadsheet for handling absolute references in a copy/cut and paste operation according to different modes
US6317750B1 (en) * 1998-10-26 2001-11-13 Hyperion Solutions Corporation Method and apparatus for accessing multidimensional data
US6327592B1 (en) * 1997-07-04 2001-12-04 Nec Corporation Data calculator performing automatic recalculation based on data structure network
US20020010743A1 (en) * 2000-02-11 2002-01-24 Ryan Mark H. Method and system for distributing and collecting spreadsheet information
US20020055954A1 (en) * 2000-08-01 2002-05-09 Matthias Breuer Methods and systems for inputting data into spreadsheet documents
US20020059261A1 (en) * 2000-06-23 2002-05-16 Microsoft Corporation Method for providing web-based services to an application program module
US6411959B1 (en) * 1999-09-29 2002-06-25 International Business Machines Corporation Apparatus and method for dynamically updating a computer-implemented table and associated objects
US20020082899A1 (en) * 2000-09-29 2002-06-27 Aley Fredrick J. Methods and systems for integrating marketing, production, and finance
US6418400B1 (en) * 1997-12-31 2002-07-09 Xml-Global Technologies, Inc. Representation and processing of EDI mapping templates
US20020095399A1 (en) * 2000-08-04 2002-07-18 Devine Robert L.S. System and methods providing automatic distributed data retrieval, analysis and reporting services
US6449620B1 (en) * 2000-03-02 2002-09-10 Nimble Technology, Inc. Method and apparatus for generating information pages using semi-structured data stored in a structured manner
US20020133497A1 (en) * 2000-08-01 2002-09-19 Draper Denise L. Nested conditional relations (NCR) model and algebra
US20020161799A1 (en) * 2001-02-27 2002-10-31 Microsoft Corporation Spreadsheet error checker
US20020169799A1 (en) * 2001-01-17 2002-11-14 Voshell Perlie E. Systems and methods providing dynamic spreadsheet functionality
US6490600B1 (en) * 1999-08-09 2002-12-03 Cognex Technology And Investment Corporation Processing continuous data streams in electronic spreadsheets
US20020188629A1 (en) * 2001-05-21 2002-12-12 Burfoot Daniel C. System, protocol, and methods for the creation of distributed spreadsheets
US20030110191A1 (en) * 2001-07-13 2003-06-12 Robert Handsaker System and method for efficiently and flexibly utilizing spreadsheet information
US6581062B1 (en) * 2000-03-02 2003-06-17 Nimble Technology, Inc. Method and apparatus for storing semi-structured data in a structured manner
US20030149934A1 (en) * 2000-05-11 2003-08-07 Worden Robert Peel Computer program connecting the structure of a xml document to its underlying meaning
US6613098B1 (en) * 1999-06-15 2003-09-02 Microsoft Corporation Storage of application specific data in HTML
US6626959B1 (en) * 1999-06-14 2003-09-30 Microsoft Corporation Automatic formatting of pivot table reports within a spreadsheet
US20030188259A1 (en) * 2002-03-28 2003-10-02 International Business Machines Corporation System and method in an electronic spreadsheet for displaying and/or hiding range of cells
US6631497B1 (en) * 1999-07-19 2003-10-07 International Business Machines Corporation Binding data from data source to cells in a spreadsheet
US20030195762A1 (en) * 2002-04-12 2003-10-16 David Gleason Automated workflow
US6691281B1 (en) * 1999-06-15 2004-02-10 Microsoft Corporation Publishing/republishing data tables in HTML documents while maintaining formatting and functionality for restoring back the data tables
US6701485B1 (en) * 1999-06-15 2004-03-02 Microsoft Corporation Binding spreadsheet cells to objects
US20040044753A1 (en) * 2002-02-27 2004-03-04 Lucida, Inc. Method and system for dynamic business management of a network
US20040060001A1 (en) * 1998-12-31 2004-03-25 Microsoft Corporation Extension of formulas and formatting in an electronic spreadsheet
US20040103365A1 (en) * 2002-11-27 2004-05-27 Alan Cox System, method, and computer program product for an integrated spreadsheet and database
US20040103366A1 (en) * 2002-11-26 2004-05-27 Microsoft Corporation User defined spreadsheet functions
US6754677B1 (en) * 2000-05-30 2004-06-22 Outlooksoft Corporation Method and system for facilitating information exchange
US20040128147A1 (en) * 2002-12-26 2004-07-01 Sundar Vallinayagam Method and system to implement complex pricing rules
US20040133567A1 (en) * 2001-06-20 2004-07-08 Oracle International Corporation Run-time optimizations of queries with SQL spreadsheet
US20040133568A1 (en) * 2001-06-20 2004-07-08 Oracle International Corporation Compile-time optimizations of queries with SQL spreadsheet
US6772156B1 (en) * 1999-11-29 2004-08-03 Actuate Corporation Method and apparatus for creating and displaying a table of content for a computer-generated report having page-level security
US6779151B2 (en) * 2001-01-05 2004-08-17 Microsoft Corporation Storing objects in a spreadsheet
US20040237029A1 (en) * 2003-05-22 2004-11-25 Medicke John A. Methods, systems and computer program products for incorporating spreadsheet formulas of multi-dimensional cube data into a multi-dimentional cube
US20050039114A1 (en) * 2003-07-16 2005-02-17 Oracle International Corporation Spreadsheet to SQL translation
US20050039113A1 (en) * 2003-08-12 2005-02-17 International Business Machines Corporation Method and apparatus for data
US6859805B1 (en) * 1999-11-29 2005-02-22 Actuate Corporation Method and apparatus for generating page-level security in a computer generated report
US20050049906A1 (en) * 2003-09-02 2005-03-03 International Business Machines Corporation Provisioning of software components via workflow management systems
US20050081141A1 (en) * 2003-10-09 2005-04-14 Einfalt Ehf. Visual programming system and method
US20050086193A1 (en) * 2003-10-21 2005-04-21 American Express Travel Related Services Company, Inc. Test strategy system and method for accounts held direct at-fund
US20050097146A1 (en) * 2003-08-21 2005-05-05 Konstantinou Alexander V. Methods and systems for autonomously managing a network
US20050108052A1 (en) * 2003-11-03 2005-05-19 Omaboe Nortey J. Proces for diagnosic system and method applying artificial intelligence techniques to a patient medical record and that combines customer relationship management (CRM) and enterprise resource planning (ERP) software in a revolutionary way to provide a unique-and uniquely powerful and easy-to-use-tool to manage veterinary or human medical clinics and hospitals
US6898760B2 (en) * 2001-01-05 2005-05-24 Microsoft Corporation Reporting status of external references in a spreadsheet without updating
US20050149482A1 (en) * 2003-12-19 2005-07-07 Thales Method of updating a database created with a spreadsheet program
US20050149422A1 (en) * 2004-01-06 2005-07-07 Edouard Van Lier System for optimizing the return of an investment portfolio, using a method of multiple share combinations
US6920443B1 (en) * 1999-09-21 2005-07-19 International Business Machines, Corporation Method, system, program, and data structure for transforming database tables
US20050182709A1 (en) * 1999-02-05 2005-08-18 Babcock & Brown Lp, A Delaware Limited Partnership Automated financial scenario modeling and analysis tool having an intelligent graphical user interface
US20050188352A1 (en) * 2004-02-17 2005-08-25 Bruno Jager Method for generating source code in a procedural, re-entrant-compatible programming language using a spreadsheet representation
US6988241B1 (en) * 2000-10-16 2006-01-17 International Business Machines Corporation Client side, web-based spreadsheet
US6993513B2 (en) * 1999-05-05 2006-01-31 Indeliq, Inc. Interactive simulations utilizing a remote knowledge base
US20060024653A1 (en) * 2004-07-30 2006-02-02 Microsoft Corporation Method, system, and apparatus for exposing workbooks as data sources
US7013246B1 (en) * 1999-05-24 2006-03-14 Parametric Technology Corporation Parametric exchange of data between a modeling system and an external application program
US20060064428A1 (en) * 2004-09-17 2006-03-23 Actuate Corporation Methods and apparatus for mapping a hierarchical data structure to a flat data structure for use in generating a report
US7155667B1 (en) * 2000-06-21 2006-12-26 Microsoft Corporation User interface for integrated spreadsheets and word processing tables
US20070130503A1 (en) * 2001-01-16 2007-06-07 Voshell Perlie E Systems and methods providing dynamic spreadsheet functionality

Family Cites Families (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7185279B2 (en) * 2000-01-07 2007-02-27 Master Mine Software, Inc. Data mining and reporting
WO2002006999A2 (en) * 2000-07-13 2002-01-24 Oracle International Corporation Performing spreadsheet-like calculations in a database system

Patent Citations (99)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US4912657A (en) * 1986-10-30 1990-03-27 Synthesis, Inc. Method and systems for generating parametric designs
US5475836A (en) * 1987-04-01 1995-12-12 Lotus Development Corporation Interface for providing access to external data sources/sinks
US5033009A (en) * 1989-03-03 1991-07-16 Dubnoff Steven J System for generating worksheet files for electronic spreadsheets
US5317686A (en) * 1990-09-10 1994-05-31 Lotus Development Corporation Data processing apparatus and method for a reformattable multidimensional spreadsheet
US5319777A (en) * 1990-10-16 1994-06-07 Sinper Corporation System and method for storing and retrieving information from a multidimensional array
US5293615A (en) * 1990-11-16 1994-03-08 Amada Carlos A Point and shoot interface for linking database records to spreadsheets whereby data of a record is automatically reformatted and loaded upon issuance of a recalculation command
US5396621A (en) * 1991-05-10 1995-03-07 Claris Corporation Sorting a table by rows or columns in response to interactive prompting with a dialog box graphical icon
US5784545A (en) * 1992-04-08 1998-07-21 Borland International, Inc. System and methods for improved spreadsheet interface with user-familiar objects
US5590259A (en) * 1992-04-08 1996-12-31 Borland International, Inc. System and methods for improved spreadsheet interface with user-familiar objects
US5883623A (en) * 1992-04-08 1999-03-16 Borland International, Inc. System and methods for building spreadsheet applications
US5371675A (en) * 1992-06-03 1994-12-06 Lotus Development Corporation Spreadsheet program which implements alternative range references
US5437006A (en) * 1993-01-27 1995-07-25 Microsoft Corporation Spreadsheet command/function capability from a dynamic-link library
US5303146A (en) * 1993-03-11 1994-04-12 Borland International, Inc. System and methods for improved scenario management in an electronic spreadsheet
US5682538A (en) * 1994-08-12 1997-10-28 Wall Data Incorporated Automatic adaptive computer screen generation
US5915257A (en) * 1994-10-11 1999-06-22 Brio Technology, Inc. Cross tab analysis and reporting method
US5721847A (en) * 1994-10-21 1998-02-24 Microsoft Corporation Method and system for linking controls with cells of a spreadsheet
US5832532A (en) * 1995-06-16 1998-11-03 I2 Technologies, Inc. Model-independent and interactive report generation system and method of operation
US5893123A (en) * 1995-06-22 1999-04-06 Tuinenga; Paul W. System and method of integrating a spreadsheet and external program having output data calculated automatically in response to input data from the spreadsheet
US5664182A (en) * 1995-09-18 1997-09-02 Actuate Software Corporation Persistent storage of report objects
US6157934A (en) * 1995-10-24 2000-12-05 Ultimus, L.L.C. Method and apparatus for using distributed spreadsheets in a client/server architecture for workflow automation
US5890174A (en) * 1995-11-16 1999-03-30 Microsoft Corporation Method and system for constructing a formula in a spreadsheet
US6122649A (en) * 1996-05-30 2000-09-19 Microsoft Corporation Method and system for user defined and linked properties
US6055548A (en) * 1996-06-03 2000-04-25 Microsoft Corporation Computerized spreadsheet with auto-calculator
US6430584B1 (en) * 1996-06-03 2002-08-06 Microsoft Corporation Computerized spreadsheet with auto-calculator
US5966716A (en) * 1996-06-06 1999-10-12 Microsoft Corporation Automatic spreadsheet forms
US5956031A (en) * 1996-08-02 1999-09-21 Autodesk, Inc. Method and apparatus for control of a parameter value using a graphical user interface
US5926822A (en) * 1996-09-06 1999-07-20 Financial Engineering Associates, Inc. Transformation of real time data into times series and filtered real time data within a spreadsheet application
US5983268A (en) * 1997-01-14 1999-11-09 Netmind Technologies, Inc. Spreadsheet user-interface for an internet-document change-detection tool
US5933818A (en) * 1997-06-02 1999-08-03 Electronic Data Systems Corporation Autonomous knowledge discovery system and method
US6256651B1 (en) * 1997-06-20 2001-07-03 Raja Tuli Time management workflow software
US6327592B1 (en) * 1997-07-04 2001-12-04 Nec Corporation Data calculator performing automatic recalculation based on data structure network
US6012057A (en) * 1997-07-30 2000-01-04 Quarterdeck Corporation High speed data searching for information in a computer system
US6292811B1 (en) * 1997-09-19 2001-09-18 Modernsoft, Inc. Populating cells of an electronic financial statement
US6003012A (en) * 1997-12-05 1999-12-14 Square D Company Methodology and computer-based tools for design, production and sales of customized switchboards
US6418400B1 (en) * 1997-12-31 2002-07-09 Xml-Global Technologies, Inc. Representation and processing of EDI mapping templates
US6009455A (en) * 1998-04-20 1999-12-28 Doyle; John F. Distributed computation utilizing idle networked computers
US6208339B1 (en) * 1998-06-19 2001-03-27 International Business Machines Corporation User-interactive data entry display system with entry fields having distinctive and changeable autocomplete
US6269377B1 (en) * 1998-09-21 2001-07-31 Microsoft Corporation System and method for managing locations of software components via a source list
US6317750B1 (en) * 1998-10-26 2001-11-13 Hyperion Solutions Corporation Method and apparatus for accessing multidimensional data
US6085184A (en) * 1998-12-22 2000-07-04 Ac Properties B.V. System, method and article of manufacture for a dynamic toolbar in a tutorial system
US6023691A (en) * 1998-12-22 2000-02-08 Ac Properties B.V. Goal based stimulator utilizing a spreadsheet architecture
US20040060001A1 (en) * 1998-12-31 2004-03-25 Microsoft Corporation Extension of formulas and formatting in an electronic spreadsheet
US20050182709A1 (en) * 1999-02-05 2005-08-18 Babcock & Brown Lp, A Delaware Limited Partnership Automated financial scenario modeling and analysis tool having an intelligent graphical user interface
US6957191B1 (en) * 1999-02-05 2005-10-18 Babcock & Brown Lp Automated financial scenario modeling and analysis tool having an intelligent graphical user interface
US6993513B2 (en) * 1999-05-05 2006-01-31 Indeliq, Inc. Interactive simulations utilizing a remote knowledge base
US7013246B1 (en) * 1999-05-24 2006-03-14 Parametric Technology Corporation Parametric exchange of data between a modeling system and an external application program
US6626959B1 (en) * 1999-06-14 2003-09-30 Microsoft Corporation Automatic formatting of pivot table reports within a spreadsheet
US6613098B1 (en) * 1999-06-15 2003-09-02 Microsoft Corporation Storage of application specific data in HTML
US6691281B1 (en) * 1999-06-15 2004-02-10 Microsoft Corporation Publishing/republishing data tables in HTML documents while maintaining formatting and functionality for restoring back the data tables
US6701485B1 (en) * 1999-06-15 2004-03-02 Microsoft Corporation Binding spreadsheet cells to objects
US6631497B1 (en) * 1999-07-19 2003-10-07 International Business Machines Corporation Binding data from data source to cells in a spreadsheet
US6490600B1 (en) * 1999-08-09 2002-12-03 Cognex Technology And Investment Corporation Processing continuous data streams in electronic spreadsheets
US6920443B1 (en) * 1999-09-21 2005-07-19 International Business Machines, Corporation Method, system, program, and data structure for transforming database tables
US6411959B1 (en) * 1999-09-29 2002-06-25 International Business Machines Corporation Apparatus and method for dynamically updating a computer-implemented table and associated objects
US6772156B1 (en) * 1999-11-29 2004-08-03 Actuate Corporation Method and apparatus for creating and displaying a table of content for a computer-generated report having page-level security
US6859805B1 (en) * 1999-11-29 2005-02-22 Actuate Corporation Method and apparatus for generating page-level security in a computer generated report
US20020010743A1 (en) * 2000-02-11 2002-01-24 Ryan Mark H. Method and system for distributing and collecting spreadsheet information
US6449620B1 (en) * 2000-03-02 2002-09-10 Nimble Technology, Inc. Method and apparatus for generating information pages using semi-structured data stored in a structured manner
US6581062B1 (en) * 2000-03-02 2003-06-17 Nimble Technology, Inc. Method and apparatus for storing semi-structured data in a structured manner
US20010032214A1 (en) * 2000-04-14 2001-10-18 International Business Machines Corporation Method and system in an electronic spreadsheet for handling absolute references in a copy/cut and paste operation according to different modes
US20030149934A1 (en) * 2000-05-11 2003-08-07 Worden Robert Peel Computer program connecting the structure of a xml document to its underlying meaning
US6754677B1 (en) * 2000-05-30 2004-06-22 Outlooksoft Corporation Method and system for facilitating information exchange
US7155667B1 (en) * 2000-06-21 2006-12-26 Microsoft Corporation User interface for integrated spreadsheets and word processing tables
US6632249B2 (en) * 2000-06-23 2003-10-14 Microsoft Corporation Method for providing web-based services to an application program module
US20020059261A1 (en) * 2000-06-23 2002-05-16 Microsoft Corporation Method for providing web-based services to an application program module
US20020055954A1 (en) * 2000-08-01 2002-05-09 Matthias Breuer Methods and systems for inputting data into spreadsheet documents
US20020133497A1 (en) * 2000-08-01 2002-09-19 Draper Denise L. Nested conditional relations (NCR) model and algebra
US6944662B2 (en) * 2000-08-04 2005-09-13 Vinestone Corporation System and methods providing automatic distributed data retrieval, analysis and reporting services
US20020095399A1 (en) * 2000-08-04 2002-07-18 Devine Robert L.S. System and methods providing automatic distributed data retrieval, analysis and reporting services
US20020082899A1 (en) * 2000-09-29 2002-06-27 Aley Fredrick J. Methods and systems for integrating marketing, production, and finance
US6988241B1 (en) * 2000-10-16 2006-01-17 International Business Machines Corporation Client side, web-based spreadsheet
US6898760B2 (en) * 2001-01-05 2005-05-24 Microsoft Corporation Reporting status of external references in a spreadsheet without updating
US6779151B2 (en) * 2001-01-05 2004-08-17 Microsoft Corporation Storing objects in a spreadsheet
US20070130503A1 (en) * 2001-01-16 2007-06-07 Voshell Perlie E Systems and methods providing dynamic spreadsheet functionality
US20020169799A1 (en) * 2001-01-17 2002-11-14 Voshell Perlie E. Systems and methods providing dynamic spreadsheet functionality
US20020161799A1 (en) * 2001-02-27 2002-10-31 Microsoft Corporation Spreadsheet error checker
US20020188629A1 (en) * 2001-05-21 2002-12-12 Burfoot Daniel C. System, protocol, and methods for the creation of distributed spreadsheets
US20040133567A1 (en) * 2001-06-20 2004-07-08 Oracle International Corporation Run-time optimizations of queries with SQL spreadsheet
US20040133568A1 (en) * 2001-06-20 2004-07-08 Oracle International Corporation Compile-time optimizations of queries with SQL spreadsheet
US20030110191A1 (en) * 2001-07-13 2003-06-12 Robert Handsaker System and method for efficiently and flexibly utilizing spreadsheet information
US20040044753A1 (en) * 2002-02-27 2004-03-04 Lucida, Inc. Method and system for dynamic business management of a network
US20030188259A1 (en) * 2002-03-28 2003-10-02 International Business Machines Corporation System and method in an electronic spreadsheet for displaying and/or hiding range of cells
US20030195762A1 (en) * 2002-04-12 2003-10-16 David Gleason Automated workflow
US20040103366A1 (en) * 2002-11-26 2004-05-27 Microsoft Corporation User defined spreadsheet functions
US20040103365A1 (en) * 2002-11-27 2004-05-27 Alan Cox System, method, and computer program product for an integrated spreadsheet and database
US20040128147A1 (en) * 2002-12-26 2004-07-01 Sundar Vallinayagam Method and system to implement complex pricing rules
US20040237029A1 (en) * 2003-05-22 2004-11-25 Medicke John A. Methods, systems and computer program products for incorporating spreadsheet formulas of multi-dimensional cube data into a multi-dimentional cube
US20050039114A1 (en) * 2003-07-16 2005-02-17 Oracle International Corporation Spreadsheet to SQL translation
US20050039113A1 (en) * 2003-08-12 2005-02-17 International Business Machines Corporation Method and apparatus for data
US20050097146A1 (en) * 2003-08-21 2005-05-05 Konstantinou Alexander V. Methods and systems for autonomously managing a network
US20050049906A1 (en) * 2003-09-02 2005-03-03 International Business Machines Corporation Provisioning of software components via workflow management systems
US20050081141A1 (en) * 2003-10-09 2005-04-14 Einfalt Ehf. Visual programming system and method
US20050086193A1 (en) * 2003-10-21 2005-04-21 American Express Travel Related Services Company, Inc. Test strategy system and method for accounts held direct at-fund
US20050108052A1 (en) * 2003-11-03 2005-05-19 Omaboe Nortey J. Proces for diagnosic system and method applying artificial intelligence techniques to a patient medical record and that combines customer relationship management (CRM) and enterprise resource planning (ERP) software in a revolutionary way to provide a unique-and uniquely powerful and easy-to-use-tool to manage veterinary or human medical clinics and hospitals
US20050149482A1 (en) * 2003-12-19 2005-07-07 Thales Method of updating a database created with a spreadsheet program
US20050149422A1 (en) * 2004-01-06 2005-07-07 Edouard Van Lier System for optimizing the return of an investment portfolio, using a method of multiple share combinations
US20050188352A1 (en) * 2004-02-17 2005-08-25 Bruno Jager Method for generating source code in a procedural, re-entrant-compatible programming language using a spreadsheet representation
US20060024653A1 (en) * 2004-07-30 2006-02-02 Microsoft Corporation Method, system, and apparatus for exposing workbooks as data sources
US20060064428A1 (en) * 2004-09-17 2006-03-23 Actuate Corporation Methods and apparatus for mapping a hierarchical data structure to a flat data structure for use in generating a report

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20110276870A1 (en) * 2007-10-29 2011-11-10 Microsoft Corporation Calculation of spreadsheet data
US10140280B2 (en) 2017-03-07 2018-11-27 Microsoft Technology Licensing, Llc Asynchronous dynamic functions for calculation engines
US10846469B2 (en) 2017-03-07 2020-11-24 Microsoft Technology Licensing, Llc Dynamically registered functions for calculations engines
US11556529B2 (en) * 2019-08-13 2023-01-17 Sigma Computing, Inc. Top frequency worksheet filtering

Also Published As

Publication number Publication date
AU2002318298A1 (en) 2003-01-29
US20030110191A1 (en) 2003-06-12
WO2003007118A2 (en) 2003-01-23
US20080028287A1 (en) 2008-01-31
US7251776B2 (en) 2007-07-31
US20110276869A1 (en) 2011-11-10
US20130013994A1 (en) 2013-01-10
US20080034281A1 (en) 2008-02-07
WO2003007118A3 (en) 2003-04-17

Similar Documents

Publication Publication Date Title
US7251776B2 (en) System and method for efficiently and flexibly utilizing spreadsheet information
US20210209157A1 (en) System and method for non-programmers to dynamically manage multiple sets of xml document data
US20230222109A1 (en) Method and apparatus for converting heterogeneous databases into standardized homogeneous databases
US20220092059A1 (en) Dynamically materialized views for sheets based data
US11755606B2 (en) Dynamically updated data sheets using row links
US6189004B1 (en) Method and apparatus for creating a datamart and for creating a query structure for the datamart
US7673282B2 (en) Enterprise information unification
CA2570266C (en) Spreadsheet user-interface for an enterprise planning system having multi-dimensional data store
US20120011118A1 (en) Method and system for defining an extension taxonomy
US20090319542A1 (en) Systems and methods to automatically replace content in a range of spreadsheet cells with information from a database
US20060229922A1 (en) Association and visualization of schematized business networks
US20100169759A1 (en) Systems and methods to create a multidimensional expression calculated member in a spreadsheet cell
US20050288956A1 (en) Systems and methods for integrating business process documentation with work environments
KR20150042867A (en) Visualizing relationships between data elements
CA2603974A1 (en) Automatically moving multidimensional data between live datacubes of enterprise software systems
KR20080043327A (en) Designating, setting and discovering parameters for spreadsheet documents
US8239299B2 (en) Type-driven rules for financial intellegence
US20070294631A1 (en) Apparatus and method for embedding and utilizing report controls within an online report
US7461076B1 (en) Method and apparatus for creating a well-formed database system using a computer
US8656269B1 (en) Providing a functionality by loading logic into a document
Coles et al. Spreadsheet modelling for management decision making
JP7339628B2 (en) Online report creation system using Excel tools
Saint-Paul et al. Data services in your spreadsheet!
Arkhipenkov et al. Oracle Express OLAP
US20050060309A1 (en) Query objects

Legal Events

Date Code Title Description
STCB Information on status: application discontinuation

Free format text: ABANDONED -- FAILURE TO RESPOND TO AN OFFICE ACTION