Your organization uses one or more spreadsheets, for example to calculate product pricing, or budgets, or risk analysis. There is a need to ensure multiple users can securely access the current master version of a spreadsheet from any location or device, enter their data, and later retrieve the results.
However, simply distributing the raw spreadsheet must be avoided, to ensure version control, and to ensure information embedded in the spreadsheet is not accessible to general users. Users also need to be able to share results with their colleagues.
We will create an EASAP that interacts with an existing Microsoft® Excel spreadsheet. In this case, the EASAP will be a basic Mortgage Calculator tool.
We will create an EASAP with basic input and output defined by the underlying spreadsheet.
As we build this application, think about how this approach could be applied to key spreadsheets in use at your organization.
Our complete EASAP user interface is shown above, the report is shown below.
Please ensure that you have a compatible version of Excel installed on your server (see Getting Started with EASA and Microsoft Office for Excel Servers). Walk through the steps detailed at the Excel Range Wizard and then open the EASAP in the Builder.
The EASAP Builder is one of the main tools used within EASA to create applications without coding. It consists of the Tree of EASAP objects on the left and their parameters on the right. In this tutorial we will build a fully functioning application by applying iterations of EASA's simple design process:
There will be no coding involved.
First check with your Administrator that you have a user ID with Author privileges.
The URL has the following structure:
where <Domain_Name> is the computer name or domain name of the EASA Server (often its localhost).
The New Application Wizard opens any time we create a new EASAP. Here we set some parameters in the PROPERTIES object.
We now have some options on how we wish to define the user interface of our new EASAP.
The first option is to open the EASAP Builder and begin to add objects to our EASAP manually.
However, in our case we want the EASAP to quickly be able to use the functionality we already have implemented in our Excel spreadsheet. We will use the Excel Range Wizard.
The screen below should open:
The Excel Range Wizard easily allows an Author to reproduce the look and function of our original spreadsheet as part of an EASAP.
Later we may add additionally interface objects to our EASAP.
Finally we upload a copy of our original spreadsheet to act as a logic engine for our EASAP.
Our EASAP will not only mimic the look of our spreadsheet, but will be linked to the relevant cells in the secured master version of the spreadsheet on the server.
The EASAP User will update input cells, the Excel spreadsheet will do calculations based on the updated values, and the EASAP will return and display updated values to the User in real time.
Lets begin using the Excel Range Wizard:
Mortgage Calculator.xls, included with this tutorial.
On the Select Excel Ranges screen, we can select the part or parts of our original spreadsheet which we want to expose to Users in our EASAP. For now, we will add just one range of cells to the EASAP.
Mortgage Calculator.xls file will open in Excel;
The EASAP Builder window will appear and the EASAP Tree will be populated with new objects created by the wizard.
At this time, the NAMED RANGE LIST will be created, or updated if already existed, and dependency calculation will be initiated.
The EASAP Builder is shown below:
We now have a perfectly functional EASAP. By default, the Excel Range Wizard will attempt to replicate the formatting of the Excel spreadsheet. Let's check it by clicking on the Test Web Browser button as shown below:
Once the test EASAP has opened, try changing some inputs, and then exit the test window.
Every new EASAP begins with some existing objects that form a template that is common to all EASAPs. In the Builder this is called the Tree, the highest level branches are always the same and they are referred to in ALL CAPS. In this section we will add some custom buttons to the USER INTERFACE object to help our users save their work and to create reports that can be viewed by colleagues. We will implement good GUI design keeping our web interface simple and intuitive for the user.
This object and its Child objects were automatically created when we ran the Excel Wizard and created the form for the basic input and output.
Now add a SPACER to create some space between the existing form and our new buttons..
Add a second SUB PANE for the buttons:
Let’s add two BUTTON's to this SUB PANE
This will display text inside the rectangular button. Use the 'Enter' key after typing a parameter value to ensure it updates.
We can also add a Tooltip: such as ‘Submit with these inputs and create a report’ that will be visible when the mouse pointer is placed over the button.
We have now defined what happens when the user presses the exit BUTTON. In a moment we will define some actions and associate them with the submit BUTTON.
Let's check our work.
Once the test EASAP has opened, test your new “Exit button”, button_exit.
In this section we will introduce events and associated actions using EVENT PROCESSING. In this case, the actions will extract images from the spreadsheet so that we can include them in a report.
Add an EVENT PROCESSING to the tree.
An EVENT PROCESSING has two Child objects:
We have now defined an event that triggers when our submit button is clicked, next we define the corresponding action that follows.
This submits a run of the EASAP with current inputs and records the timestamped output at:
If you have errors, you will see error messages directing you to problems within your objects that need to be fixed.
In this section we will define a report that will be created when a user submits an EASAP. We do this with the REPORT.
In the Tree:
We are going to create two images from the spreadsheet and write them to the report. We could have simply defined the actions for button_submit by adding Child objects in much the same way as we did for button_exit. However, we are showing the BUTTON PRESSED EVENT to show EVENT PROCESSING functionality which can use events other than button clicks (eg. VALUE CHANGED, RESULTS COMPLETED, APPLICATION OPENED).
On the Tree:
This will create images of the summary region and output data table.
(Cut and paste including the outermost % symbols.)
The Builder will highlight the text between the '%' delimiters in blue and remove the '%' signs, this indicates the Builder recognized the strings as pre-existing scalar variables (eg. “Report for loan amount $cell_Basic_Information_loan_amount_1 for cell_Basic_Information_term_1 years”).
The Builder allows variables (green for lists or blue for scalars) to be entered as parameters for many objects giving the Author the power and flexibility of conventional computer programming as explained in more advanced examples (see Batch Tutorial).
This will insert summary notes in the Results page, so that a user later browsing Results can distinguish one submission from another.
Again, the SCALAR's should be recognized and be highlighted in blue without '%' symbols: (eg. “Report for loan amount $cell_Basic_Information_loan_amount_1 for cell_Basic_Information_term_1 years”)
From the EASA web pages, go to:
This simulates what a user will actually see.
Your EASAP is now perfectly usable, but let’s suppose that, after publishing it for testing by users, they provide some feedback. In this case, they ask you to add the charts (which are already in the spreadsheet) to new tab panes in the EASAP itself. This will enable users to see their results graphically before even submitting the EASAP.
Mortgage Calculator.xls, included with this tutorial. Click Next.
For both SUB PANE's:
Let’s change the default size in pixels of our application to allow for the larger graphical content, otherwise we will get unwanted scroll-bars.
Our mortgage calculator stores the results of each EASAP run in the EASA→Author→Authoring→Testing page as HTML. This allows the user to vary inputs and generate different runs and view the results in the browser. Once an EASAP run meets the user's needs it can be exported to a PDF. Now we will add EXPORT TO PDF functionality to our EASAP.
This will be run after the user clicks “submit” and before the report is generated by the MENU ACTION to follow.
|Parameter Name||Parameter Value|
|Output File Name:||Report.pdf|
|Open in Browser:||FALSE|
The above Ranges: are the summary table and the calculated data output table, respectively.
Now we put a link to our output file in the HTML report. In the Tree,
This will create an HTML link to the PDF.