Chapter 7: Populating the Worksheet

This chapter contains the third in a set of five tutorials, which together give a tour of EuroSmart. This tutorial shows how to use the worksheet to store points of interest and to record analysis details.

7.1 Overview

This tutorial demonstrates the worksheet and how you use it to:

Gather monetary points of interest in the worksheet. These points of interest include data items, statements and information on data files. In this way, all the points of interest are recorded in one place so that you can track the analysis and your progress.
Record reasons for including items in the worksheet and to monitor the history of each entry. You can also categorize entries to help break up and size the problem in various ways.
Produce reports on the contents of the worksheet, so that you can track the work and also give your management estimates of the potential work involved in properly implementing euro transactions.

In this tutorial, you:

Add the "Potential monetary data items" set to the worksheet, using Worksheet channel
Add the statements and data items in the "Monetary constants" set to the worksheet, using Worksheet channel
Find and add data file information to the worksheet, using Files and file-related statements from data items
Find and add BMS screen-related data items to the worksheet, using BMS-related data items
Produce a Worksheet status report, using Reports

This tutorial takes about 30 minutes.

7.2 Preparation

If you are running this tutorial for the first time, you need to set up the worksheet with the necessary options, so that your worksheet behaves the same as that in the tutorial, as follows:

If you are continuing immediately from the last tutorial, go straight to the step 2.
Otherwise:
- If EuroSmart is not running, restart it in the same way as before.
- Open the Order project by selecting it from the recent files list at the bottom of the Project menu.
- Check that your analysis tools settings and options are set correctly for the tutorials, as described in the Preparation section of the Finding Sets of Monetary Points of Interest chapter.
Click Open Worksheet on the Euro menu. This opens the worksheet for the Order project, which is empty if this is the first time you are running through this tutorial.
Click Options on the worksheet, and choose the following options:
- Check Show information bar if it is not already checked.
- Uncheck Prefix index in statements if it is checked.
Click OK.
You can now skip to the next section.

If you want to redo this tutorial and start this tutorial from the beginning again, you will have an old worksheet with some information in it. To start afresh, you need to open an empty worksheet. To do this:

Click Close on the Project menu to close the Order project.
Delete the worksheet for the Order project, using Windows. The worksheet file has the same name as the project and so is called Order.mdb, and it is in the project directory, which is Projects\Order.
In EuroSmart, open the Order project by selecting it from the recent files list at the bottom of the Project menu. This opens an empty worksheet so that you can start afresh.
Check that your analysis tools settings and options are set correctly for the tutorials, as described in the Preparation section of the Loading a Project into the Database chapter.
Check that your worksheet options are set correctly for the tutorials, as described in the previous numbered list in this section.

7.3 Adding a Set to the Worksheet

In the previous tutorial, you created the set "Potential monetary data items". In many cases, the data items in this set are not of interest, since one euro might be similar in size and format to the Deutsche Mark or franc that you are converting from. However, for the lire or the peseta, which don't have a lower denomination, such as centimes, you might need to change all the monetary data items so that they handle values with decimals.

For demonstration purposes, this section shows how to add this set of potential monetary data items to the worksheet.

If the Named Sets window is not displayed, click Named Sets on the Euro menu.
In the Named Sets window, double click the set "Potential monetary data items" to display it. This set contains 71 data items.
If the Analysis Tools window is not displayed, click Analysis Tools on the Euro menu.
Drag the "Potential monetary data items" set in the same way as before onto Worksheet channel in Analysis Tools.
In Enter or select a note, there are two fields:
- The top line is a drop down list from which you can select an existing note. There probably aren't any notes listed, since you haven't entered any into the worksheet yet.
- The lower box is an entry field into which you can type a new note. When there are existing notes, this field shows the note selected in the list above.
Click in the lower entry field, delete the existing note if there is one, and type a note such as "Potential monetary data item".
In Select an operation, click Add set and check Update existing items.
Go to the Categories tab and click A-Poss.
Click OK.

All the 71 data items in the set are now added to the worksheet, as described next.

7.4 Exploring the Worksheet

The worksheet is now displayed with its data items duly added, as shown in Figure 7-1.

Figure 7-1: Worksheet

Click the Data items tab if it is not already displayed.
Notice that a count of 71 data items shows in the information bar towards the top of the worksheet.
For explanation of what's in the columns, click Help on the worksheet.
Notice that the Category column shows the category A-Poss that we assigned to this set of items.
Click on the column heading Data item to sort the data items in alphabetical order. You can sort any column similarly.
Widen the Data item column by dragging the divider between its column heading and the next column's heading. You can also press Ctrl + F2 to size all the columns to fit the information in them.
Click one of the data items and click View Source Code at the top of the worksheet. This displays the source view in the bottom pane in the same way as in the set view, so that you can explore the source in the same way. Close the Source View.
Right click one of the data items and click Properties. This shows a two-pane window:
- The top pane is for the entry's history and since the entry is relatively new there is little information.
- The lower pane shows the item was generated and which analysis tools were used to generate it and what their settings were.
Close the Properties page.

7.5 Adding Constant-related Points of Interest

In the previous tutorial, you created a set that contained the data items and statements related to monetary constants, and called this set "Monetary constants". These need to be added to the worksheet since they are of interest and need to be investigated further and might eventually need modification.

Double click the "Monetary constants" set in the Named Sets window to redisplay the set. This set contains 60 entries.
Notice that some of the entries are gray. These are data items that are already in the worksheet. All the statements are in black and this is because they are not yet in the worksheet. Only one data item VAL-P-AND-P-LIMIT is in black and is not yet in the worksheet.
Use Worksheet channel and add this set of statements and data items to the worksheet, in the same way as before. This time do the following:
- In the Notes field, specify "Associated with a constant that might be monetary".
- Make sure that Add set and Update existing items are selected, so that any data items in the set that are already in the worksheet are updated with the new note and are assigned an additional category.
- On the Categories tab, select B-Constant, which indicates that the point of interest involves a numeric currency constant that needs converting.
- Click OK.
Notice that the information bar towards the top of the worksheet now shows that there are now 72 data items, one more than before. Check that the new data item VAL-P-AND-P-LIMIT is now in the worksheet, and check that it has a category of B-Constant.
Notice that the Category column shows the word (more) for some entries, indicating that the item has more categories. If you can't see (more), widen the Category column by dragging the divider between the Category and Notes columns headings.
Confirm that the B-Constant category has been added to the data items that were already in the worksheet. To do this:
Click in the Category column of one of the new data items VAL-P-AND-P-COST and then click the down arrow to see its two categories, A-Poss and B-Constant. If you click B-Constant, it moves to the top of the category list and stays visible, when the list is collapsed.
Go to the Statements tab of the worksheet, and check that the information bar towards the top shows that there are 52 statements in the worksheet.
Right click the first statement, which starts ADD VAL-P-AND-P-COST and click Show contained data items to display just the data items that are used in that statement and that are in the worksheet.
There are 3 data items in the worksheet that are used in that statement. Notice that the information bar towards the top shows that the worksheet is filtered and that are 3 filtered data items.
Right click the data item VAL-P-AND-P-COST and click Show statements containing, to see the statements involving that data item.
Uncheck Filtered at the top to return to the full unfiltered worksheet. There is more discussion on filtering later in the tutorials.
Go to the Data items tab and uncheck Filtered to return to the full unfiltered worksheet.

The worksheet now contains 72 data items and 52 statements.

7.6 Adding Data File Information to the Worksheet

Files that contain data representing monetary values will, potentially, need to have the data converting to the equivalent euro value. Once you have identified monetary data items, you can examine them to determine whether they are contained within file records. Any file that contains a record containing a data item of interest represents a file that might need attention; it is a file point of interest.

In practice, it might be worth initially considering all files of interest and adding them to the worksheet. The worksheet reports can then list all the files and show the ones that contain data items of interest and the ones that don't.

This section shows how to find the data files used by the application and to find the statements where those files are used or their data items are used.

Run Files and file-related statements from data items to find the files used in the application and the statements that involve data items in those files.
This set contains 27 entries, which is a combination of 5 data files and 22 statements.
Add the combination set of files and statements to the worksheet, using Worksheet channel and specify the category A-Poss, and the note, "File related".
Go to the Data Files tab of the worksheet and confirm that there are 5 files in the worksheet. Right click one of the files and click Properties. You can now review the audit trail, which shows how you found these files. Click OK to close the Properties window.
Go to the Statements tab of the worksheet and confirm that the statements involving file items were added to the worksheet. There should be 74 statements (52 plus 22) in the worksheet. You can sort the Category column, so that all the statements with the A-Poss category are together.

The worksheet now contains 72 data items, 74 statements and 5 data files.

7.7 Adding Screen-Related Data Items to the Worksheet

Finally, there is one other type of item that is potentially a problem with financial applications when converting them to handle euros. These are items that are passed to and from screens.

Open the "Potential monetary data items" set in the same way as before.
Drag this set onto BMS-related data items to find the data items that are associated with BMS fields on BMS screens.
Ensure that Show group items only is unchecked and click OK.
The resulting set contains 27 data items.
Click Sort Ascending to sort the data items into alphabetical Order.
You can now see that these are the COBOL data items generated from the BMS screen map. The fields in the BMS screen map might need modifying and so you might need to regenerate the COBOL data item definitions. Alternatively, COBOL data items themselves might need modifying.
Notice that all the data items in the set are in gray, which means they are already in the worksheet, by virtue of being used in the right way and having the right format and name. However, we need to update the categories of these items, so that you can distinguish them as BMS field. To do this:
Add the set of BMS-related items to the worksheet, using Worksheet channel and specify the category B-BMS-Field, which indicates that these data items are related to BMS screen fields. You can also specify note giving the date and your name.
Confirm that the B-BMS-Field category has been added to the relevant data items in the worksheet.
Tidy up by deleting the BMS set containing 27 items.

The worksheet now contains 72 data items, which is the same as before since no items were added.

7.8 Reporting on the Worksheet

This section explains how to produce a report on the current state of the worksheet.

At the worksheet, click Reports.
Choose Worksheet status report.
Make sure that HTML and Full are selected.
Check Launch HTML browser and click OK.
A full report of the contents of the worksheet is then compiled in HTML format and is displayed in your HTML browser.

Note, that if you are reading the online version of this tutorial, the report will be displayed instead of the tutorial in your browser. You can return to the tutorial by clicking the appropriate Back button in your browser.
Review the report, which shows the total numbers of data items and statements of interest in each source file. The report also shows each point of interest, whether a data item, statement or data file, together with all the information on that point of interest, such as its category and its notes.
You can print the report from the browser in the usual way.
Now click Reports again and this time choose Assessment Report. Review the information available in this report.

7.9 Before Continuing

The worksheet is persistent and is continually saved, and so it is automatically up to date if you shut down.

However, it is good practice to save a backup of the worksheet, so that you can go back to a previous copy. For example, you might want to return to the current worksheet, if you make a mistake in a later tutorial.

At the worksheet, click Save to File.
Specify the filename, EndofChap7, for the backup worksheet for this chapter.
If you already have a backup with this name, give a new name such as EndofChap7-1.

You can close EuroSmart, if you want to stop for now. You can then continue with the next tutorial some other time.

Finding Sets of Monetary Points of Interest

Customizing the Analysis Tools