Spreadsheet Techniques With Developer Studio
by Dave Denst
I have found that many of our clients attending Developer Studio training courses get really excited about the Excel 2000 features in Developer Studio. Most knew how to select EXL2K as an output option but were not aware of doing much else. This article will demonstrate some of the features found in Developer Studio for spreadsheets. The examples use EMPDATA which is found in the IBISAMP application.
A Simple Spreadsheet
The following procedure, excel1.fex, was created in Report Painter:
-* File excel1.fex
DEFINE FILE EMPDATA
BONUS/D10.2=SALARY * .02;
END
TABLE FILE EMPDATA
PRINT
SALARY/D10.2
BONUS
COMPUTE SAL_BON/D10.2 = SALARY + BONUS;
BY DIV
BY DEPT
BY LASTNAME
ON DIV SUBTOTAL AS 'Total for'
WHERE DIV EQ 'CORP';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
-* Styling statements omitted for clarity
ENDSTYLE
END
This procedure is using DEFINE for the BONUS and COMPUTE to add the SALARY and BONUS together. The HEADING and FOOTING are turned off, so they do not take up space in the spreadsheet.
When we run this procedure, we get what you see on Screen 1.
Note that the SAL_BON value for employee Lopez is a formula (=$D3 +$E3), because it was created with a COMPUTE instead of DEFINE. The CORP division subtotal is a formula too.
Many students have indicated that they would need the DIV and DEPT values repeated on every row for graphing purposes. From the Report menu pull-down in Report Painter, we can select the Output… option, and select the check box for Display repeated sort values.
Another change the students want is instead of “Sheet 1” as the sheet name, they want to assign a specific name to the sheet. From the Report menu pull-down, we can select the Format… option, and select the check box for Customize worksheet title. Now we can fill in the text box: Corp Employees. Directly underneath is another check box we can select for Define a range name for the data, and enter: CORP_DATA.
Also, the students wanted to specify a column width, so they don’t have to manually adjust the column width when “##########” appears. Back at the Report Painter screen, we multi-select the SALARY, BONUS, and SAL_BON columns using the Ctrl key. When we right-click the SAL_BON column and select Options… from the pop-up menu, in the Custom Layout area, we can set the Width to Wrap from a pull-down list, and specify the Limit to 0.75 inches. Back on the Report Painter screen, the “push pins” for these columns indicate that these columns are now set to a fixed width.
When we run the procedure again with these changes, and specify CORP_DATA in the Name Box, the cells are highlighted (Screen 2).
A Multiple Sheet Spreadsheet
Making a copy of the prior example and calling it excel2.fex, if we remove the WHERE statement and run it, we wind up with multiple divisions on one sheet. My students wanted to know if there is a way to separate the divisions onto multiple sheets. Back in Report Painter from the Report menu pull-down, we can select the Format… option again, but this time we want to de-select the check boxes from the previous example and now select the check box for Create separate worksheets for each primary sort value. Running the procedure with this change, we get a separate spreadsheet for each division.
Multiple Requests in One Spreadsheet
For the final example (excel3.fex), let’s suppose we have a summary of salaries for each division:
TABLE FILE EMPDATA
SUM
CNT.PIN AS 'Nbr of Empl'
SALARY AS 'Salary Budget'
BY DIV AS 'Division'
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
-* Styling statements omitted for clarity
ENDSTYLE
END
We would like to combine this spreadsheet with the one created from the last example (Screen 3).
The “Set COMPOUND OPEN” component will allow us to place multiple report requests into one spreadsheet. The “Report EMPDATA” component is the Division Summary spreadsheet. The “Set COMPOUND CLOSE” component means that after the following report request, you close the compound document. The “Include excel2.fex” is the example of multiple divisions on separate sheets.
When we run all the components in this procedure, we get Screen 4.
Note if one of the report requests is creating separate sheets for each primary sort value, it must be the last request.
Conclusion
After demonstrating these features, some of my students have told me these spreadsheet techniques were “worth the cost of the entire course.” And spreadsheets were only a small part of the course!
