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:

1. Create an EASA object with the mouse
2. Populate that object's parameters by entering text or selecting values from lists.

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.

• Expand the USER INTERFACE object by clicking on the “plus box” () icon.

• Under USER INTERFACE→TABBED PANE LIST→tab_Basic_Information, you should see one SUB PANE object called simply sub_pane.

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.

• Select sub_pane and minimize it by clicking on the “minus box” () icon.

Now add a spacer to create some space between the existing form and our new buttons..

• Right-click sub_pane, choose Sibling→Add SPACER.

Add a second SUB PANE for the buttons

• Right-click the above SPACER and select Sibling→Add SUB PANE. The default name is fine. Set Layout:‘HORIZONTAL’ and Show Border:‘FALSE’.

Let’s add two BUTTON's to this SUB PANE

• Right-click the SUB PANE and select Child→Add BUTTON, twice.

• Rename the first BUTTON button_submit. Set Label:‘Save to Results & Create a Report’.

This will display text inside the rectangular button. Use the 'Enter' key after typing a parameter value to ensure it updates.

• Rename the second button_exit. Set Label:‘Exit without saving’.

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.

• Right-click button_exit select Child→Add MENU ACTION. Rename it menu_action_exit. Set Action:‘Exit’.

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.

• Save your work and then click on the Test Web Browser () button.

• 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.

• Right-click on the tab_Basic_Information and select Sibling→Add EVENT PROCESSING.

An EVENT PROCESSING has two Child objects: an EVENT and an associated ACTION GROUP.

• Click the “plus box” () to expand our EVENT PROCESSING to see each Child. Rename the EVENT, event_submitted.

• Right-click event_submitted and select Child→Add BUTTON PRESSED. Set parameter Buttons: by choosing button_submit from the drop-down list.

We have now defined an event that triggers when our submit button is clicked, next we define the corresponding action that follows.

• Rename the ACTION GROUP action_submitted, then select Child→Add MENU ACTION. Set Action:‘Submit’.

This submits a run of the EASAP with current inputs and records the timestamped output at EASA→Author→Authoring→Testing.

• Save ( ) your work.

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.

• In the Tree select the PROCESSES branch.

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:

• Right-click EASAP→PROCESSES
  • Select Child→Add IMAGE EXCEL ACTION
    • Name it image_excel_action_summary
      • Set: Sheet: sheet_Basic_Information (from parameter dropdown list)
      • Set: Range: A1:L25
      • Set: File Name: summary.png

• Right-click image_excel_action_summary
  • Copy and then Paste (Sibling)
    • Rename it image_excel_action_table
      • Set: Range: A26:L387
      • Set: File Name: table.png

This will create images of the summary region and output data table.

• Select and expand the OUTPUT branch.

• Right-click OUTPUT and select Child→Add NOTES, set its Value:'%cell_Basic_Information_term_1% year loan for $%cell_Basic_Information_loan_amount_1%’ 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).

• Right-click NOTES and select Sibling→Add REPORT.

This will insert summary notes in the Results page, so that a user later browsing Results can distinguish one submission from another.

• Right-click the REPORT and select Child→Add TEXT OUTPUT. Set Style:‘Heading 2’, Text:‘Report for loan amount $%cell_Basic_Information_loan_amount_1% for %cell_Basic_Information_term_1% years’.

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”)

• Right-click the TEXT OUTPUT and select Sibling→Add PLOT. Set its Image:'summary.png'.

• Right-click this PLOT and Copy and then Paste (Sibling), then set Image:'table.png'.

• Save your EASAP by clicking on Save button () or typing Ctrl-S.

• Minimize or close your EASAP.
  
  

• From the EASA web pages, go to EASA→Author→Authoring→Testing→Test EASAP.

This simulates what a user will actually see.

• Change some values, and click on the ‘Save to Results & Create a Report’ button.

• Refresh the EASA→Author→Authoring→Testing page and you should see the test appear.

• Check the Notes reflect the values you just entered.

• Check the report by clicking on the icon under Output.

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.

• Re-run the Excel Range Wizard from within EASAP Builder (Tools→Excel Range Wizard).

• Click on the Choose File button and then select the tutorial Excel file, Mortgage Calculator.xls, included with this tutorial. Click Next.

• On the Select Excel Ranges screen, click on the Add a Range from Excel button ( ).

• Select cells M1 through V24 (i.e. a range which completely includes the amortization chart).

• Add a range for the rate history chart by selecting cells M24 through V46.

• Click Finish. This will create two new SUB PANES containing the charts within the TABBED PANE, tab_Basic_Information, because the selections were made from the Excel tab ‘Basic Information’. However, now these objects are in the EASAP, we can move them wherever we want.
  
  
  
  

• Right-click tab_Basic_Information and select Sibling→Add TABBED PANE, twice.

• Rename them tabbed_pane_Amortization and tabbed_pane_Rate_History. For each set Tab:‘Amortization’ and Tab:‘Rate History’.

• Drag and drop:
  SUB PANE (M1:V24) into tabbed_pane_Amortization and
  SUB PANE (M24:V46) into tabbed_pane_Rate_History.

• Set Show Border:'FALSE' and Visual Compatibility:'<blank>' 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.

• Go to PROPERTIES→APPLICATION near the top of the Tree. Set Width:'650, Height: ‘530’.

• Save your EASAP: click the Save button or type Ctrl-S.

• Test your EASAP. When the application opens, select the new tabs. The window should look something like the following:

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.

• Right-click action_submitted and select Child→Add EXPORT TO PDF.

This will be run after the user clicks “submit” and before the report is generated by the MENU ACTION to follow.

• For the EXPORT TO PDF set:

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, Right-click OUTPUT→report1 and select Child→Add HTML OUTPUT. Set Text:'<h2>This report in PDF form: <a href=“Report.pdf”>Report.pdf</a>'.

This will create an HTML link to the PDF.

• Save. Test. Now go to EASA→Author→Authoring→Testing. Confirm there is a link to the PDF below in the HTML report under “Output”.