Abstract:
A networked spreadsheet template designer allows administrators to easily create and deploy robust reporting and input applications that leverage existing spreadsheet templates and knowledge in an easy to use user interface. Templates are created to operate directly in the particular spreadsheet program environment, such as Microsoft Excel®, and can be created using existing templates and modeling workbooks of that spreadsheet program without the need for any code to be written. A library of dynamic link library (DLL) worksheet functions makes creation of templates a faster, simpler task. The template designer uses these DLL worksheet functions directly on template worksheets to perform specified actions. The DLL functions enable a user to create an interactive, custom application by automating/encapsulating complex tasks. The DLL functions are managed across a computer network through an administrator interface to create the functions and a viewer interface that combines an Explorer® window to select the functions and a spreadsheet window for the actual spreadsheet program that implements the DLL functions.

Description:
RELATED APPLICATION  
       [0001]     The present application claims the benefit of U.S. Provisional Application No. 60/588,689 filed Jul. 16, 2004, which is incorporated herein in its entirety by reference. 
     
    
     FIELD OF THE INVENTION  
       [0002]     The present invention relates to data processing in terms of presentation and cell control processing of spreadsheet documents. More particularly, the present invention relates to a networked system for creating and invoking an interactive and custom library of complex worksheet functions within the existing template function of a spreadsheet program to more effectively provide for template and report generation in a business organization.  
       BACKGROUND OF THE INVENTION  
       [0003]     In today&#39;s business world it is desirable, in order to save time and money, to have spreadsheets customized to a business&#39;s individual needs. One solution to this is creating spreadsheet templates by writing code. However, most business users do not have the necessary programming knowledge and code writing is a time-consuming process. Another option is to create a template within a spreadsheet, such as Microsoft Excel®, by using native Excel® functions. This, however can also be a time consuming process as most Excel® functions can only perform rudimentary commands on the spreadsheet and many commands may be necessary to customize a template to a business&#39;s needs. A third option is to purchase templates from a company that specializes in creating them. However, it may be difficult to obtain a template that meets the exact needs of a business and it may also be difficult to obtain a customized template in a timely fashion if a deadline is approaching.  
         [0004]     Several software applications deal with creating spreadsheet templates. For example, iLeve™ Software allows the creation of templates from various authoring tools, e.g., FrameMaker or Microsoft Word®, while using the commands native to that particular application. Another example is the Actuate® e.Spreadsheet designer, which allows for creation of Microsoft Excel® templates using menu options and wizards in an environment that closely resembles, but is not actually executed as part of, the Excel® application itself. Instead, a report generator program is executed against a database and set of virtual functions created by the template environment to produce an actual, expanded and resolved Microsoft Excel® spreadsheet as described in more detail, for example, in U.S. Publ. Appl. No. 2004/0088650A1.  
         [0005]     There are many patents and published patent applications directed towards spreadsheet creation. For example, U.S. Pat. No. 5,883,623 is directed toward a general spreadsheet, including a method for building custom spreadsheet applications and U.S. Pat. No. 5,966,716 includes a template “wizard.” U.S. Pat. Nos. 6,640,234 and 6,725,422 are directed toward automatically applying formulas and cell addresses to a range of cells in a spreadsheet. U.S. Publ. Appl. No. 2004/0111666A1 describes a report replicator program for a spreadsheet that permits a user to more easily replicate portions of a report that are repeated within a spreadsheet to accommodate the actual data in a final report. U.S. Publ. Appl. No. 2004/0103366A1 describes a user-defined function definition feature extension for the Microsoft Excel® spreadsheet program. There are also several patents directed towards sharing automatically updating spreadsheets on a database as described for example, in U.S. Pat. Nos. 6,341,292 and 5,293,615.  
         [0006]     While there have been substantial improvements in spreadsheets for presenting and managing data, it would be desirable to provide for a more efficient and effective way to create templates for a spreadsheet, particularly in a networked business environment.  
       SUMMARY OF INVENTION  
       [0007]     The present invention is a networked spreadsheet template designer that allows administrators to easily create and deploy robust reporting and input applications that leverage existing spreadsheet templates and knowledge in an easy to use user interface. Templates are created to operate directly in the particular spreadsheet program environment, such as Microsoft Excel®, and can be created use existing templates and modeling workbooks of that spreadsheet program without the need for any code to be written. The present invention includes a library of dynamic link library (DLL) worksheet functions that makes creation of templates a faster, simpler task. The template designer uses these DLL worksheet functions directly on template worksheets to perform specified actions. The DLL functions enable a user to create an interactive, custom application by automating/encapsulating complex tasks. The DLL functions are managed across a computer network through an administrator interface  103  to create the functions and a viewer interface that combines an Explorer® window to select the functions and a spreadsheet window for the actual spreadsheet program that implements the DLL functions.  
         [0008]     In a preferred embodiment, the DLL functions are close in nature to Microsoft Excel® functions, but are more detailed and can perform more complex commands. A single DLL function may encapsulate the equivalent of 20-30 individual Excel® functions, enabling templates to be formatted much faster and easier than if done using Excel® commands.  
         [0009]     Preferably, the DLL function interface is accessed through an administrator console. When a template workbook is opened, the Insert Function icon is located in the middle pane of the administrator console. Double-clicking the Insert Function icon shows the list of available DLL functions. After a function is selected, a menu appears for selection of the parameters that the function will apply to. When the desired parameters have been set, selecting “Paste” will place the command on the template worksheet. More commands can be added by clicking “Back” to return to the main list of functions.  
         [0010]     The present invention allows multiple network users to access the templates that are created with the administrator console. The administrator sends each application and its associated templates to the deployment server where copies are stored. Security options allow the administrator to limit the clients who may access any individual application. Users who are allowed access are then able to view and manipulate the templates through the server using the viewer interface. 
     
    
     BRIEF DESCRIPTION OF THE DRAWINGS  
       [0011]      FIG. 1  illustrates the relationship between the components of the preferred embodiment of the present invention.  
         [0012]      FIG. 2  is a screen shot of the preferred embodiment of the Explorer window of the user interface.  
         [0013]      FIG. 3  is a screen shot of the preferred embodiment of the designer window of the administrator interface.  
         [0014]      FIG. 4  is a table of a application files for a preferred implementation of the present invention referred to as SpreadsheetObjects.  
         [0015]      FIG. 5  is a table identifying the specific deployment source folder location of each application file relative to the SpreadsheetObjects system path (%SSO%).  
         [0016]      FIG. 6  is a deployment impact table describing different scenarios and the impacted application files that must be re-deployed to the server repository to properly update the end user&#39;s machine. 
     
    
     DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS  
       [0017]      FIG. 1  illustrates the relationship between the different components of the preferred embodiment of the present invention, which will be referred to as “SpreadsheetObjects.” The invention includes an administrator  102 , an administrator interface  103 , configuration management system  106 , and a designer module  104 . The designer module  104  enables the administrator  102  to design report templates and applications  108  and store them on a deployment server  105 . A client interface  126  enables a client  110  to view external files  128  and view and manipulate the report templates and applications  108  through the use of an Explorer module  122  and a Viewer module  124 . A SpreadsheetObjects program  114  interacts with a spreadsheet program  112  and optional 3 rd  party spreadsheet add-ins  116  in a Windows®-based operating system  118 . Open Database Connectivity (ODBC) drivers  120  link a database management system through the network  134  to local or remote databases  136 , and a web update system keeps program files current by linking the most current versions of the programs stored in internet files  130  via the internet  132 .  
         [0018]     In this preferred embodiment as shown in  FIG. 1 , an administrator  102  manages the source SpreadsheetObjects applications including the templates or views that are accessed by the clients  110  over a computer network, such as a computer network for a business organization. An administrator console is used to manage version control and deployment to the server repository. This process must be completed before any clients  110  can access the templates. The administrator  102  may also need to manage OLAP security specific to the deployed SpreadsheetObjects applications  108  to ensure that the correct client privileges are in place. The administrator  102  may also need to manage OLAP security specific to the deployed SpreadsheetObjects applications  108  to ensure that the correct privileges are in place.  
         [0019]     The deployment server  105  is the physical computer system that stores SpreadsheetObjects applications that have been deployed by the administrator  102 . The deployment server  105  stores compressed copies of all application files and distributes these application files to client computers on version control settings. The deployment server  105  is typically an existing OLAP or file server.  
         [0020]     Clients  110  with access are then able to view and manipulate SpreadsheetsObjects templates and applications  108  through the Client Viewer Module  124 . The Viewer module  124  is the client software that allows users to view and manipulate SpreadsheetObject templates and applications  108 , and is accessed over the network  134  through a web launch mechanism. This web launch mechanism also automatically provides web updates to the applications  108 . The Viewer  122  operates in client/server mode using Excel® as the user interface and a TCP/IP connection through an Intranet, dial-up connection or secure VPN connection to the OLAP server for data access.  
         [0021]     In a preferred embodiment, SpreadsheetObjects uses Microsoft Excel® as its primary user interface, but augments it with custom dialogs to implement all of the needed user data presentation services. The use of Excel® with custom dialogs offers several advantages over a full custom interface or browser interface. Excel® is by far the desktop tool of choice for data organization and analysis. The majority of users are already very proficient with it and will not require extensive training. Second, Excel® has extensive data analysis, graphics, and formatting capabilities that would be difficult to improve upon, or match, in a custom interface. Excel® also provides a very complete set of support services such as printing and data import/export. The use of Excel® with custom dialogs as provided by the present invention also allows users to integrate their own existing or custom created macros into their analysis, and send data seamlessly to other Microsoft Office® applications for presentation or transport purposes. Excel® is also almost universally installed on user&#39;s machines and is supported by Microsoft. Furthermore, the existing Excel® user base is so large that it will not be obsoleted without considerable advance notice. Finally, Excel® file formats are compatible with the vast majority of other analysis tools.  
         [0022]     The extensions of the present invention that add the custom dialogs to Excel® are preferably implemented as ActiveX Dynamic Link Libraries (DLLs). These ActiveX DLLs are dynamically registered when a SpreadsheetObjects application is started. This allows SpreadsheetObjects to be easily updated or patched without a re-installation or manual registration of DLLs.  
         [0023]     In addition to the use of Excel®, a small application is used to initiate the program and handle user validation. Once the application is initiated, the majority of the interaction with the user is through Excel®.  
         [0024]     The preferred embodiment of the Explorer module  122 , shown in  FIG. 2 , works in the Viewer  124  to give users focused and personalized views in a familiar Excel® setting. The Explorer  122  shields users and clients  110  from complex technology and streamlines their activities. Interactive business processes are fully supported with features such as write-back, cell notes, on-line auditing and automatic database calculations.  
         [0025]     The preferred embodiment of the Designer module  104 , shown in  FIG. 3 , allows the administrator  102  to create business applications without writing a single line of code, instead using SpreadsheetObjects powerful XLerator DLL functions. The Designer  104  is an intuitive, graphical desktop that guides the user through the process of translating business rules into applications. The Designer  104  provides complete access to all required components to assist the administrator  102  with template and application development.  
         [0026]     The Designer module  104  is preferably implemented as a Microsoft Windows application installed on the administrator  102  or user&#39;s computer. The administrator  102  or user creates an Excel® workbook containing presentation and formatting data and uses the Designer module  104  to create control file data, which is integrated with the workbook into a SpreadsheetObjects application  108 . The control files define the source, scope, and structure of the data that will be returned from the database  136  when the application  108  is executed, as well as the deployment information. The report application  108  is created by combining the workbook with the control files.  
         [0027]     The control data imbedded in the application  108  contains information that the client  110  uses to automatically initiate the update of the application  108  if necessary when it is opened. This process allows an administrator  102  to maintain an application  108  and guarantees that the Client  110  will automatically open the latest deployed version.  
         [0028]     With respect to configuration management  106 , both the Client  110  (User Interface) and Designer  104  applications can be classified as desktop applications with all ongoing updates managed through an automatic web/server update process.  
         [0029]     This architecture generally provides a much richer interaction with the user than a thin client or browser-based application. The SpreadsheetObjects web/server update process modifies the conventional desktop architecture in a way that the advantages are maintained while the maintenance drawbacks are eliminated.  
         [0030]     In a preferred embodiment, the initial SpreadsheetObjects installation requires the placement of the User Interface and Designer  104  applications on each client  110  or user&#39;s machine that requires Windows administrator privileges. Once the initial installation has been completed, however, it is automatically updated and maintained without manual action by the client  110  and does not require special Windows privileges. This is accomplished through a modular architecture that features a fixed bootstrap layer that rarely changes after the initial install and changeable layer that contains the logic subject to updates. The changeable layer is also modular so that if only a small subset of the modules needs updating, the update can be applied only to the modules that are out of date. Updates are automatically applied by logic in the bootstrap layer without the user having to log in with administrator privileges or the need to manually register Windows components.  
         [0031]     The bootstrap layer employs logic that, if an internet  132  or network  134  connection is available, quickly compares versioning criteria to determine if the installed SpreadsheetObjects configuration is up to date. If an update is necessary the appropriate modules are downloaded and updated in the background while a status screen is shown to the user to indicate that SpreadsheetObjects is up and running and will be available for use shortly. Routine updates are usually completed within a few seconds and is primarily dependent on the speed of the internet/intranet connection. The remote site with the updated data can reside either on a web server or a network.  
         [0032]     With respect to data access and analysis, SpreadsheetObjects provides very flexible data access and analysis capabilities through the support of a wide variety of data sources such as Hyperion Essbase OLAP, Microsoft OLAP, and/or any ODBC compliant relational database management system. The data sources can be local or remote, or a combination of the two, as long as the user has the necessary network connections and security credentials to access them. The architecture model varies depending on the combination of data sources used.  
         [0033]     SpreadsheetObjects implements data access and updates security be enforcing the native security model for each of the databases  136  involved. Users need to have the proper security credentials to access any of the databases  136  an application references. SpreadsheetObjects presents dialogs as needed and encrypts password data that needs to be store on the local machine. Data access is restricted to whatever access has been set up by a database administrator for the user&#39;s account. This allows users with a variety of security permits to use the same application as any data a given user is not allowed to view or modify is suppressed or restricted in accordance with security profile associated with the login.  
         [0034]     With respect to SpreadsheetObjects&#39;s file system, the client applications must contain the required set of application files in order to run successfully. Most of the application files are generated by the Designer  104  except for the actual Excel templates, which are created by the administrator  102 .  
         [0035]     The table shown in  FIG. 4  defines a number of SpreadsheetObjects application files.  
         [0036]     As far as file regions are concerned, SpreadsheetObjects application files can exist in two different regions on a user&#39;s computer; the admin region and the client region. Application files only exist in the admin region on machines that have installed the Administrator software. The SpreadsheetObjects system path (%SSO%) is set by default, though the software may be installed to any location. The folder structure beneath the default location is managed by the software and should not be altered. The admin and client regions exist beneath the SpreadsheetObjects system path.  
         [0037]     The table shown in  FIG. 5  identifies the specific deployment source folder location of each application file relative to the SpreadsheetObjects system path (%SSO%). The deployment source folder is the location of the application file that will be deployed to the repository and thus is the true production file.  
         [0038]     The folder %SSO%\App contains source application files that are used by SpreadsheetObjects any time a new application is created. For example, if a new application named “Budget App” is created, SpreadsheetObjects will copy these files to the new region for “Budget App” and change the name of each file (excluding extension) to “Budget App”. In this case, “ssoapp.xla” would be copied to %SSO%\AppBudget App\System\Budget App.xla.  
         [0039]     The folder %SSO%\App\AppName contains the Excel template file(s), the property file associated with each Excel template file (*.ini), a temporary view definition file (appname.vws), a version control file (ssover.txt) and a temporary backup/system file (appname.adn).  
         [0040]     The property file is generated by SpreadsheetObjects after the Excel templates are defined in the Designer  104 . The view definition file is managed by the SpreadsheetObjects administrator  102  and contains information related to all worksheets that are defined in SpreadsheetObjects. This view definition file is a temporary work file and is not the view definition file that is ultimately published to the server repository. The view definition located under the “system” subfolder is the actual production file that is deployed to the server repository. The other files are managed by SpreadsheetObjects and should not be altered.  
         [0041]     The folder %SSO%\Appl\AppName\History contains all deployment related history files. Each time a SpreadsheetObjects application is deployed, a copy of the source application files is placed in a history folder with a date/time stamp. In the event that a rollback to a previous version is required, files from any history folder can be copied back into the deployment source folders and re-deployed.  
         [0042]     The folder %SSO%\Appl\AppName\Package contains the package definition file(s) for a SpreadsheetObjects application. The package file is essentially a list of source file path and name information so SpreadsheetObjects can deploy the correct source files to the server repository.  
         [0043]     The folder %SSO%\App\AppName\System contains key SpreadsheetObjects system files including the Excel addin driver file (appname.xla), the view definition file (appname.vws), the toolbar definition file (appname.tbr), the messaging file (appname.msg), “SSO.dll” and a backup view name file (appname.bws). These files are automatically copied and renamed from their source location (%SSO%\App) when a new SpreadsheetObjects application is created. Experienced SpreadsheetObjects administrators  102  that want to customize VBA code or the toolbar definition for a specific application should apply their edits to the xla and tbr files in this folder.  
         [0044]     Application files located in the admin region comprise the source files for any SpreadsheetObjects application  108 . The deployment process to the server repository collects application files from the admin region and posts a compressed copy of these source files on the server repository. Files in the admin region should be handled like production files and backed up on a regular basis for recovery or rollback purposes. To backup the admin region, the entire folder structure is simply copied from %SSO%\App and below to a network location that is regularly backed up or to a storage device such as a CD.  
         [0045]     The client region is created when a user accesses SpreadsheetObjects applications  108  through the client viewer. Each application  108  that is run on the client machine creates a subfolder under the %SSO%\%USER%\Client folder.  
         [0046]     The name of the subfolder is the same as the application description that was defined by the administrator  102  during the package and deployment process. This is usually different than the system name of the application  108  which is the name first assigned to the application  108  when it was created. It is also the same as the application folder name within the admin region on the administrator&#39;s machine.  
         [0047]     The name of the admin region folder name is shown in the top pane. The name of the client region name is shown in the bottom pane. The client region name is set by the administrator  102  during the package and deployment process and can be changed at any time. Typically, the client region name is more descriptive than the admin region name to make the application name intuitive for the end users.  
         [0048]     Unlike the admin region, the client region contains all SpreadsheetObjects application files in a single folder. These files should be considered temporary and will be overwritten any time a new version of a file is published to the server repository.  
         [0049]     All source application files that are currently deployed to the server repository are present in this folder. Additionally, the following files are present: cascade.ini, that contains user view and member runtime selection information; demo.txt which always has the same name as the deployment folder name on the server repository and contains the information required to convert files from their format in the server repository to the format required for the client region; and sso.sec that is the SpreadsheetObjects security file that contains user login information and server repository source information.  
         [0050]     A SpreadsheetObjects administrator  102  typically works on custom VBA code by directly running the xla driver addin from within the client region. This is to aid in the VBA debug process since the application cannot actually be executed from the admin region. It is important to always transfer (copy) any edited source files in the client region back to the admin region for re-deployment so any changes are not lost. Always make backup copies of any source files before any modifications are made. An application  108  should be launched directly from the xla driver file in the client region only by an experienced administrator  102  and only for debugging or customization purposes. In all other instances, applications should be launched using the SpreadsheetObjects Viewer.  
         [0051]     In the event that multiple people are responsible for deploying the same SpreadsheetObjects application  108 , care must be taken to ensure that the latest production files are not overwritten.  
         [0052]     The default SpreadsheetObjects deployment process begins with the source application files that are located on the developer&#39;s machine (e.g., Excel templates, toolbar configuration, addin driver, etc). Upon deployment, these source files are transferred to the server repository. If another developer, from another machine, deploys the same application without putting the source files on their system, the most recent production files may be overwritten on the server repository. To ensure that the developer is always working with the most recent production files in this scenario, the steps as shown below preferably should be performed prior to changing any source files for deployment: 
        Run the SSO Viewer  124  to automatically bring the latest files from the server repository to the administrator&#39;s machine. The files will be located in the client region on your PC (%SSO%\Client\AppName). If there are newer source files located in the client region then it is preferably to copy those files to a safe location prior to running the Viewer  124 . Once the Viewer  124  has downloaded the newest files from the deployment server  105 , the newer source files on the system may be copied back into the client region.     Copy the newest source files from the client region to the admin region using Windows Explorer. The admin region is located at %SSO%\App\AppName. Copy the file(s)to the correct subfolder(s) as specified in the technical reference.     Re-deploy the application using the SpreadsheetObjects Designer  104 .        
 
         [0056]     SpreadsheetObjects packages must be deployed and security must be set before end users can begin using the system. The steps below summarize at a high-level the tasks that must be completed prior to using the system: 
        Create deployment repository     Set security for deployment user     Deploy a SpreadsheetObjects package     Set end user security for deployment repository     Run the client to access the SpreadsheetObjects package        
 
         [0062]     The deployment repository is a centrally located folder that may reside on the Essbase server (default) or any network server. It is important that all end users have a secure connection (e.g., VPN, intranet) to gain access to the server that contains the deployment repository. The deployment repository does not contain any executable programs; rather it serves as a storage location for the required SpreadsheetObjects package files that are ultimately accessed by the end users. SpreadsheetObjects package files are compressed and pushed to the deployment repository when the deployment user deploys or updates a SpreadsheetObjects package. These files are downloaded automatically by end user machines as updates and new versions are made available.  
         [0063]     The OLAP deployment repository preferably resides in an Essbase database folder. This database does not contain traditional Essbase files and does not need to be loaded in memory on the server. Each deployed SpreadsheetObjects package must be deployed to its own database.  
         [0064]     The network deployment repository resides in any standard Windows folder located either on a network drive or a local drive. Each deployed SpreadsheetObjects package must be deployed to its own folder.  
         [0065]     SpreadsheetObjects does not contain its own security layer but instead relies on security settings on the OLAP server to manage client access to the system. There are three different types of security settings: deployment user access, application-level access and member-level access. The different types of security settings are described below. Preferably, the deployment user has special security settings to control the SpreadsheetObjects deployment process.  
         [0066]     The deployment user on Essbase must have a minimum of database designer access to the deployment repository. In this embodiment, the user must also have Essbase “Create/Delete Users, Groups” privilege if you intend to use security filters to change end user dimension views. The deployment user is used occasionally by the client to read security filter contents. This is required when security filters are assigned at the group level and transferred to individual users. This is required because Essbase usernames do not natively have access to read security filters that are inherited from a group level—a limitation of the Essbase API layer. End users are not aware of this deployment user and do not have access to the deployment user&#39;s password. The deployment user on the network must have a minimum of write access to the deployment repository folder.  
         [0067]     The application security layer determines which applications are available to the end user through the client viewer. This security layer allows administrators  102  to grant access by user to specific SpreadsheetObjects application. Setting this security layer differs by the type of deployment repository.  
         [0068]     To grant access to a SpreadsheetObjects application  108  using the OLAP deployment repository, the administrator  102  (or in some cases, the OLAP server administrator) must grant standard Essbase read access to the specific user for the deployment repository (database). This can be done using LumenSoft ServerManager™ or Hyperion Application Manager. Security access can be set at the user or group level. Please refer to Hyperion&#39;s technical documentation on setting Essbase security for related questions.  
         [0069]     To grant access using the network deployment repository, the administrator  102  (or in some cases, the network administrator) must grant standard read access to the specific user for the deployment repository (network folder). End users or clients  110  do not need to have the same drive mappings to use the same network repository. If different drive mappings are an issue, be sure to deploy the package using Universal or Uniform Naming Convention (UNC) (e.g. “servemnamedeployfolder”).  
         [0070]     The member security layer defines which members a user will see when selecting filter options through the client viewer. This security layer is enabled by setting up Essbase security filters for individual users or groups on Essbase. The security filters must contain filter lines that comply with a certain format to be properly read by SpreadsheetObjects. Filter lines must contain a single member specification and one of the following macro commands to work properly: @CHILDREN; @ICHILDREN; @DESCENDANTS; @DESCENDANTS. Security filters contain additional, unrelated filter lines. Any filter lines that do not match the required format for SpreadsheetObjects will be ignored by the system.  
         [0071]     The deployment impact table shown in  FIG. 6  describes different scenarios and the impacted application files that must be re-deployed to the server repository to properly update the end user&#39;s machine.  
         [0072]     Although the present application incorporates by reference certain copyrighted material, including the software source code and the Technical Reference manual, no license to use or produce these copyrighted materials is granted by virtue of this application, other than to the extent such materials are copied pursuant to an examination of a copy of this application. All other rights are reserved.