An organization may use a spreadsheet for various essential operations:

• To run a product pricing model
• To estimate a future budget, revenue, and costs
• To analyze insurance risk and calculate a premium

Several users may need secure access to an up-to-date 'master' version of a spreadsheet. Users may be in different locations; they may use different devices to enter and receive data; they may need to save the results in different formats and save them to different repositories.

Distributing the raw spreadsheet is not an ideal solution in these cases for several reasons.

• New spreadsheet versions may be confused with older ones.
• Proprietary information may be embedded in the spreadsheet.
• An end user might inadvertently modify the spreadsheet and introduce error.
• A user might need to share a final report with many colleagues or store it in a central archive.

The following tutorial demonstrates a turnkey EASA solution to the problems above.

We will create a basic Mortgage Calculator EASAP that derives its user interface and functionality from an existing Microsoft® Excel spreadsheet.

The underlying spreadsheet define's an EASAP's input and output, particular cells, ranges and charts link directly to visible fields in the EASAP GUI and generate media that may be included in HTML or PDF reports.

On the EASAP's GUI Excel-linked values and graphs will change in realtime when a User modifies an input.

As we build this application, think about your organization's key spreadsheets and the potential value created if they could be easily transformed into standalone web applications. (They can be!)

Below is our complete EASAP user interface. The second image is an example of the report output.

Please ensure a compatible version of Excel is installed on the server.

The following tutorial involves many straightforward steps. Below is a broad summary of this process:

1. Launch the New Application Wizard to set basic properties
2. Use the Excel Range Wizard to create a default working application from a spreadsheet
3. Modify the new EASAP in the Builder
   • Add buttons to the GUI that activate events and trigger actions
   • Import live Excel charts to the EASAP GUI
   • Create finished HTML and PDF formatted output with User results

An Author uses the EASAP Builder to create an EASA application without writing any code.

The Builder interface consists of a 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:

1. Create a new object on the Tree with a right-mouse-click
2. Populate that object's parameters in one of two ways:
   • Type in literal text
   • Select one or more values from a drop-down list

There will be no coding involved.

This tutorial contains the following seven sections.

1. Create a new EASAP
2. The Excel Range Wizard
3. EASAP Builder: Add GUI buttons to the Tree
4. Create an EVENT that triggers an ACTION
5. Add charts to the EASAP
6. Create a Report
7. Create PDF output from Excel

• With a browser download the following archive,
  • http://help.myeasa.com/6_0/resources/Excel_Tutorial_Files.zip
• Unzip the archive and save the files locally

• Obtain from the EASA Administrator a user ID with Author privileges.
  • Open a browser window
    • Navigate to the EASA Server URL sent by the Administrator
    • The URL has the following structure:
      • http://domain-name/easa/

domain-name is the computer name or domain name of the EASA Server (often its localhost).

On the EASA Server web page:

• Select EASA→Set Mode→Author (enter an Author's login credentials)
  • From the above local archive we will need the following two files,
    • app_image.jpg to identify our new EASAP
    • Mortgage Calculator.xls with tables, formulas and charts

Under EASA→Applications,

• Select New
• Click on the Create button

We have created a new EASAP.

• Click on the EASAP Builder link
• Download the file atos.easa
• Open the file

The New Application Wizard will appear, below.

The New Application Wizard opens any time we create a new EASAP. Here we set some parameters in the PROPERTIES object.

• Set the Title: to Mortgage Calculator
• Set an icon as the Image:
  • Click the folder icon
  • Browse to app_image.jpg
• Set the Category: → Training
• Click Next

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.

• Select → Use Excel Range Wizard (2.0)
• Click Finish and the screen below should open

The Excel Range Wizard effortlessly reproduces the look and function of an Excel spreadsheet within an EASAP.

Later we add additional interface objects to the EASAP.

Our EASAP will not only mimic the look of our spreadsheet, it directly links 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.

• Let's begin using the Excel Range Wizard,
  • Click on the Choose File button
  • Navigate to and select the tutorial Excel file, Mortgage Calculator.xls from above.
  • Click Next

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 the EASAP. For now, we will add just one range of cells to the EASAP.

• Click on the Add a Range from Excel button ( ),
  • → The Mortgage Calculator.xls file will open in Excel;
• Select cells A1 through G23 → Click and drag to highlight these cells
• Click on Finish button to process these ranges and exit the Excel Range Wizard.

The EASAP Builder window will appear. The EASAP Tree will be populated with new objects created by the wizard.

• Click Save to save our work ( ) → a dependency calculation will be initiated.

EASAP→SPREADSHEET LIST→spreadsheet_Mortgage_Calculator→NAMED RANGE LIST will be created at this time or else will be updated if already existing.

The EASAP Builder with EASAP→SPREADSHEET LIST expanded is shown below:

We now have a functional EASAP.

By default, the Excel Range Wizard replicates the formatting of the Excel spreadsheet.

Let's Test it.

• Click the Open Web Browser Test button as shown below:

Once the test EASAP has opened, below:

• Change a few inputs
• Close the test window.

Every new EASAP begins with some existing objects that form a template that is common to every EASAP.

In the Builder this is called the Tree, the highest level branches are always the same, their names are in ALL CAPS.

Let's add some buttons to the USER INTERFACE to help a User

• Save his or her work
• Create a report for a colleague

We will implement good GUI design and keep our web interface simple and intuitive for a User.

• Expand the USER INTERFACE object by clicking on the “plus box” () icon.
• Find USER INTERFACE→TABBED PANE LIST→tab_Basic_Information→layout_panel

layout_panel and its Child objects were automatically created when we ran the Excel Range Wizard to create the form for basic input and output.

• Minimize layout_panel by clicking on its “minus box” () icon.
  • Add a SPACER to create some space between the existing form and the new buttons.
    • Right-click layout_panel
      • Select Sibling→Add SPACER → the default name is fine
• Add a second LAYOUT PANEL for the buttons,
  • Right-click spacer1
    • Select Sibling→Add LAYOUT PANEL → default name is fine
      • Set → Layout: HORIZONTAL

• Let’s add two BUTTON's to this LAYOUT PANEL
  • Right-click the LAYOUT PANEL
    • Select Child→Add BUTTON, twice.

• Rename the first BUTTON→ button_submit
  • (Modify the rectangular text field to the right of BUTTON: above the parameter table.)
    • Set → Label: Save to Results & Create a Report

The Label: text will be displayed inside the rectangular button.

To be clear this new BUTTON is named, button_submit which we, as Authors, will use elsewhere within the Builder.

button_submit has a Label: which is the text that a User will see enclosed within the button's outline in the GUI.

Press <Enter> after typing a parameter value to ensure it updates.

For the moment button_submit will not do anything.

• Rename the second BUTTON→ button_exit
  • Set → Label: Exit without saving
    • We can also add a Tooltip: that will be visible when the User places the mouse pointer over the button.
      • For example set → Tooltip: Submit with these inputs and create a report

ACTION's may be added as Child objects under a BUTTON, each will be executed sequentially when a BUTTON is pressed.

We now introduce our first ACTION called MENU ACTION which initiates activities common to many EASAP's.

Here we create a way for the User to exit the EASAP gracefully.

• Right-click button_exit
  • Select Child→Add MENU ACTION
    • Rename it → menu_action_exit
      • Set→ Action: Exit

We have now defined what happens when a User presses the exit BUTTON.

In a moment we will define another ACTION and associate it with button_submit.

Since LAYOUT PANEL doesn't add an space around its Child objects, we will add a space between our buttons now.

• Right-click button_submit
  • Select Sibling→Add SPACER
    • Set→Size: 10,10 ← a ten by ten pixel space

• Let's check our work,
  • Save
  • Click on the Test Web Browser () button.
  • Once the test EASAP has opened,
  • → Test the new Exit button, button_exit.

In this section, we will introduce an EVENT and its associated ACTION.

In this case, the BUTTON PRESSED EVENT will be activated and an ACTION triggered when button_submit is pressed.

A BUTTON PRESSED EVENT is an alternative way that a BUTTON can trigger an ACTION

Here we use BUTTON PRESSED to introduce a general EVENT/ACTION model implemented in an EVENT PROCESSING object.

• Let's add an EVENT PROCESSING to the Tree.
  • Right-click tab_Basic_Information
    • Select Sibling→Add EVENT PROCESSING

• An EVENT PROCESSING has two Child objects:
  • EVENT
  • ACTION GROUP

Child objects of an EVENT will trigger any Child ACTION objects under the associated ACTION GROUP.

• Click the 'plus box' () to expand the EVENT PROCESSING to see each Child.
• Rename the EVENT → event_submitted

• Right-click event_submitted
  • Select Child→Add BUTTON PRESSED.
    • Set (ie. choose from the drop-down list) → Buttons: button_submit

When a User clicks on the BUTTON→ button_submit, a BUTTON PRESSED EVENT (Child of event_submitted) is activated.

• Let's define an ACTION that will be triggered as a result.
• Under event_submitted,
  • Select the ACTION GROUP
  • Rename it action_submitted
    • Click 'Yes' to rename the 'other usage' of this ACTION
      • → The Actions: parameter of event_submitted will update to the new name
• Right-click action_submitted
  • Select Child→Add MENU ACTION
    • Set→ Action: Submit

Following a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs and records the timestamped output at:

• EASA→Author→Authoring→Testing

• Click Save ( )
  • → If there were an error, an error message will identify which object to fix.

The EASAP is now perfectly usable, but let’s suppose that, after its Published, a few Users provide feedback. In this case, they ask to include two charts from the spreadsheet and display each of them on new TABBED PANE's in the EASAP.

Let's add these charts to enable a User to see live results graphically even before the EASAP is submitted.

• Re-run the Excel Range Wizard from within EASAP Builder (Tools→Excel Range Wizard).
• Click on the Choose File button
• Select the Excel file from the tutorial .zip archive→ Mortgage Calculator.xls
• Click Next
• On the Select Excel Ranges screen,
  • Click on the Add a Range from Excel button ( ).
  • Add a range for the 'amortization chart' → select a rectangle that entirely includes the chart:
    • Select cells M1 through V23
      • → the Range Wizard will show Basic Information!M1:V23
  • Add a range for the 'rate history chart' → select a rectangle that entirely includes the chart:
    • Select cells M24 through V46
      • → the Range Wizard will show Basic Information!M24:V46
  • If the selection rectangle is too small it will not create an IMAGE reference
  • If the selection rectangle is too large extra SPACER's will show up on the Tree, which we will delete
  • Click Finish

Because these selections were made from the Excel tab ‘Basic Information’, there are two new LAYOUT PANEL's under tab_Basic_Information which contain the 'Amortization' and 'Rate History' charts.

Let's make sure we've captured the Excel-derived objects we wanted.

• Expand each LAYOUT PANEL
• For each insure there is one IMAGE
  • If not delete the LAYOUT PANEL and repeat the steps using Tools→Excel Range Wizard 2.0
• Delete or CTRL-x any SPACER's that may have been captured…. We don't want this extra padding.

We can move these objects wherever we want them in the user interface by changing their location on the Tree.

• Right-click tab_Basic_Information
  • Select Sibling→Add TABBED PANE, twice.
    • Rename each of them, respectively:
      • tabbed_pane_Amortization
      • tabbed_pane_Rate_History
      • Set (for each of them, respectively) →
        • Tab: Amortization
        • Tab: Rate History

• Drag and drop:
  • layout_panel2 into tabbed_pane_Amortization
  • layout_panel3 into tabbed_pane_Rate_History

The Tree should look like the following panel:

• Save the EASAP → click the Save button or type CTRL-s.
• Test the EASAP
• When the application opens, select the new tabs.
  • → The 'Amortization' tab should look something like the following figure:

When a user submits an EASAP, a report is created. We define the content and presentation of this output 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 wanted to demonstrate the BUTTON PRESSED EVENT within the more general EVENT PROCESSING object which can use various EVENT's (eg. VALUE CHANGED, RESULTS COMPLETED, APPLICATION OPENED).

To pull an image from the spreadsheet and put it in a report we use an IMAGE EXCEL ACTION in the PROCESSES branch of the Tree. PROCESSES contains a sequence of tasks that begin when the User 'submits' the EASAP.

• On the Tree,
  • Right-click EASAP→PROCESSES
    • Select Child→Add IMAGE EXCEL ACTION
      • Name it image_excel_action_summary
        • Set,
          • File Name: summary.png (type in this new filename)
            • (then click on a different field→ ENTER will erase new filename)
          • Sheet: sheet_Basic_Information (from parameter dropdown list)
          • Range: A1:L25
          • Queue: TRUE → to avoid running out of free Excel processes

• Right-click image_excel_action_summary
  • Copy and then right-click Paste (Sibling)
    • Rename it image_excel_action_table
      • Set→ File Name: table.png (type in this new filename)
        • (then click on a different field→ ENTER will erase new filename)
      • Set→ Range: A26:L387

These IMAGE EXCEL ACTION's will create images of the summary region and output data table.

• Select and expand the OUTPUT branch.
  • Right-click OUTPUT
    • Select Child→Add NOTES
      • Set→ Value: %format_term% year loan for %format_loan_amount%

The Builder will highlight the text between the '%' delimiters in blue and remove the '%' signs, this indicates the Builder recognized the strings as references to pre-existing SCALAR's (eg. “Report for loan amount $format_loan_amount for format_term years”).

The Builder allows object references (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).

Let's insert summary notes in the Results page so that a User later browsing Results may distinguish one submission from another.

The first step is to format

• the loan amount to be displayed as dollars
• the term length to be an integer number of years

• Right-click OUTPUT
  • Select Child→Add DATA PROCESSING
    • Expand it
      • Right-click its Child SCALAR
        • Select Child→Add FORMAT
          • Rename it format_loan_amount
            • Set,
              • Value: cell_Basic_Information_loan_amount_1
              • Input Type: Currency
              • Decimal Places: 0
              • Currency Symbol: $
• Right-click format_loan_amount
  • Select Copy or CTRL-c
    • Right-click format_loan_amount
      • Select Paste (Sibling) or CTRL-b
        • Rename it format_term
          • Set,
            • Value: cell_Basic_Information_term_1
            • Input Type: Number
            • Thousand Separator: FALSE

• Right-click NOTES
  • Select Sibling→Add REPORT.
• Right-click the REPORT
  • Select Child→Add TEXT OUTPUT
    • Set,
      • Style: Heading 2
      • Text: →
        • Report for loan amount %format_loan_amount% for %format_term% years

Again, the SCALAR's should be recognized and be highlighted in blue without '%' symbols: (eg. “Report for loan amount $format_loan_amount for format_term years”)

• Right-click the TEXT OUTPUT
  • Select Sibling→Add PLOT
    • Set→ Image: summary.png
• Right-click this PLOT
  • Select Copy and then right-click Paste (Sibling)
    • Set→ Image: table.png
• Save the EASAP→ click the Save button () or type Ctrl-S.
• Minimize or close the EASAP.

Test EASAP simulates what a User will see.

• From the EASA web pages, go to:
  • EASA→Author→Authoring→Testing→Test EASAP
    • Change some values, and click on the ‘Save to Results & Create a Report’ button.
  • Refresh the EASA→Author→Authoring→Testing page the test should appear.
    • Check the Notes reflect the values just entered.
    • Check the report by clicking on the icon under Output.

Our mortgage calculator stores the results of each EASAP run as an HTML page at:

• EASA→Author→Authoring→Testing

A User may vary inputs and generate different runs and view the results in the browser. Once an EASAP run meets a User's needs it may be exported to a PDF.

Now we will add EXPORT TO PDF functionality to our EASAP.

• Close the window with the EASAP that we just tested
• Return to the Tree in the Builder

• Expand the EVENT PROCESSING to show event_submitted and action_submitted
• Right-click action_submitted
  • 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 that follows.

For the EXPORT TO PDF

• Set,

The above Ranges: correspond to the summary and the calculated data output tables.

Now we put a link to the output PDF file in the HTML report. In the Tree,

• Right-click OUTPUT→report1
  • Select Child→Add HTML OUTPUT
    • Set → Text: <h2>This report in PDF form: <a href=Report.pdf>Report.pdf</a></h2>
• Save
• Test
• Go to EASA→Author→Authoring→Testing
  • → Confirm there is a link to the PDF below in the HTML report under Output

We have completed the Excel Tutorial.