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.
You will create an EASAP that interacts with an existing Microsoft® Excel spreadsheet. In this case, the EASAP will be a basic mortgage calculator tool.
In this section, we will create an EASAP with basic input and output defined by the underlying spreadsheet.
As you build this application, think about how this approach could be applied to key spreadsheets in use at your organization.
The resulting EASAP 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 a tree with objects on the left and associated 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.
For the first part of this tutorial, please complete the Using the Excel Range Wizard steps to generate the initial interface to the Excel workbook. When done, return to this page to add features by completing the additional steps below.
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.
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.
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 EASA→Author→Authoring→Testing.
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 object.
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.
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 variables 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”)
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.
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.
This will create an HTML link to the PDF.