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:

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.

First check with your Administrator that you have a user ID with Author privileges.

• Log in to EASA by clicking on the link sent to you by your EASA Administrator, by opening your browser and clicking on the EASA link.

The URL has the following structure:

http://<Domain_Name>/easa/

where <Domain_Name> is the computer name or domain name of the EASA Server (often its localhost).

• Select EASA→Set Mode→Author

• Under EASA→Applications, select New and click on the Create button

• A new EASAP is created in EASA→Authoring→My EASAPs with a default name.

• Click on the EASAP Builder link, download the atos.easa file and open it.

• The New Application Wizard will appear.

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: by clicking the folder icon next to it and browsing to it.
• Set the Category: to '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

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:

• Click on the Choose File button
• Select the tutorial Excel file, Mortgage Calculator.xls, included with this tutorial.
• 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 our 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 by clicking and dragging to highlight these cells.
• Click on Finish button to process our selections and exit the Excel Range Wizard.

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

• Click Save to save our work ( ).

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.

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

• Under USER INTERFACE→TABBED PANE LIST→tab_Basic_Information, find one SUB PANE 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
  • Select Sibling→Add SPACER.

Add a second SUB PANE for the buttons:

• Right-click the above SPACER
  • Select Sibling→Add SUB PANE (The default name is fine.)
    • Set: Layout: HORIZONTAL
    • Set: Show Border: FALSE

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

• Right-click the SUB PANE
  • 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
• 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 tab_Basic_Information
  • Select Sibling→Add EVENT PROCESSING

An EVENT PROCESSING has two Child objects:

• EVENT
• ACTION GROUP.

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

• Right-click event_submitted
  • Select Child→Add BUTTON PRESSED.
    • Set: Buttons: parameter 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
  • 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.

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
  • Select Child→Add NOTES
    • Set: 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
  • 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
  • Select Child→Add TEXT OUTPUT
    • Set: Style: Heading 2
    • Set: Text: Report for loan amount $%cell_Basic_Information_loan_amount_1% for %cell_Basic_Information_term_1% years

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

• Right-click the TEXT OUTPUT
  • Select Sibling→Add PLOT
    • Set: Image: summary.png

• Right-click this PLOT and Copy and then Paste (Sibling)
  • 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
• Select Sibling→Add TABBED PANE, twice.
  • Rename one tabbed_pane_Amortization
    • Set Tab: Amortization
  • Rename the other tabbed_pane_Rate_History
    • Set Tab: Rate History

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

For both SUB PANE's:

• Set: Show Border: FALSE
• Set: Visual Compatibility: <blank>

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.

• Select PROPERTIES→APPLICATION near the top of the Tree
  • Set: Width: 650
  • Set: 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
  • 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
  • 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”.