Using Foxfire!’s Programming Hooks

By Mary Hintermeier

Multi-Pass Reporting

Foxfire! uses Structured Query Language (SQL) to extract data from databases. SQL is a powerful language for querying databases, but still has a few limitations. Some of the querying limitations come from the SQL language, and some are due to how the particular database that you are reporting off of has been designed, making it difficult to write meaningful SQL to extract the desired data.

One of these limitations is due to the fact that SQL only makes a single-pass through the database when extracting records. For example, you can ask for “All Red Cars and their Purchase Price” and the SQL engine will make a single-pass through the Cars table and return all Red Cars and their Purchase Price. But what if you wanted to get “All Red Cars, their Purchase Price, and the percentage of the total Purchase Price for each Car”? A calculation such as a percent calculation requires that you first know the total Purchase Price of all Cars in order to make percentage calculations on individual Red Cars.

To create this type of report you need to make two passes at the database, one to get the total Purchase Price and then a second pass to actually compute the percentage of for Car returned in the first pass. We call this Multi-Pass Reporting.

Foxfire! doesn’t have any magical solutions for solving multi-pass reporting problems, but does provide a framework that makes it easier to accomplish and manage. We’ll use the AFTER SELECT hook for all Requests that require more than one pass.

As we learned in Chapter 1, The AFTER SELECT hook is called by Foxfire!’s main processing engine just after the SQL query has been executed and a result set has been returned from the database. Therefore, a first-pass will have just been completed and there is an open result set (a cursor) with the records returned based on the criteria specified in the Request.

Let’s look at some examples:

ØExample 1: The Quartile Rank Report

Quartiling is an example of a simple calculation that requires a second pass through the data. You can’t split the result records into four groups until you know how many there are, and how they are sorted. The code that makes the second pass (to assign quartiles, based on the results derived in the first pass) must be invoked after the first pass has been performed but before the actual report is run.

The code (in FFCONFIG.PRG) transforms the Request’s result cursor into a table, opens it in the same work area that the cursor formerly occupied, then calculates the quartile standings and writes an appropriate value into each record for proper sorting. The combination of this code with the “native” first pass yields a “two-pass reporting” result.

1. In FoxPro, open FFCONFIG.PRG

2. Search for the string “QUARTILE”

3. Review the following code already located in the AFTER SELECT phase.

*only run the quartile second pass if the data item with a

* result field name of ”QUARTILE” is present in the result cursor

IF TYPE(“QUARTILE”) = “N”

*display a wait window just to indicate that the

*second pass is indeed being executed.

WAIT WINDOW “Foxfire! sample query: “ + ;

“calculating quartiles “+;

“(SEE FFCONFIG.PRG) “ NOWAIT

*declare temporary variables

PRIVATE lcWasAlias,lcTempName

*save the name of the result cursor so that we

*know what to name the cursor

*after the second pass has been performed

lcWasAlias = ALIAS()

*get a unique temporary file name for temporarily

*copying the result cursor

lcTempName = sys(3)

*copy the result cursor to the temporary file

COPY ALL TO (lcTempName) && Exchange cursor for datafile

*Note: we use a DBF rather than a second cursor since

*FoxPro cursors may or may not have a DBF header on file,

*which is required to perform a further SELECT.

*open the temporary file that contains the result set

USE (lcTempName) ALIAS TEMP_QUART EXCL && same work area

*Calculate quartiles based on the current order. Put the

*result of the calculation into the placeholder data

*item named “quartile” which came from the data dictionary

REPLACE ALL quartile WITH CEILING(recno()/ recount()*4)

*Put all the records from the temporary file back

* into cursor named from the variable that

*contains the saved original cursor name

SELECT * FROM TEMP_QUART INTO CURSOR &lcWasAlias

*close the temporary file

USE IN TEMP_QUART

*delete the temporary file

DELETE FILE (lcTempName + “.dbf”)

ENDIF

Because this type of activity imposes an additional processing step, the code is written in such a way that is only activated if the “Quartile” data item is used in the Request. Alternatively, the code could have been invoked from the AFTER QUERY hook of the Request. Depending on your requirements, it may be better or more maintainable to keep code at the global level (in FFConfig.prg) or the Request level (in the Output Special Processing options).

The key to making this work is to have a Data Item named “Quartile” in your request. This data item is used as a placeholder only and is not filled in with the actual data until the code in the AFTER SQL or AFTER QUERY hook has been executed.

1. In Foxfire!, locate the Quartile Report Variable data item in the Data Item Editor.

The function of this data item is simply to provide a placeholder in the result cursor that will subsequently be filled with the quartile calculation.

The Data Item Name is what will appear to users in the Request Editor.

The Data Item Expression is three zeros which insure that the column generated in the result set is a numeric type.

The File and Alias fields are left empty because this data item is table independent.

The Result Field Name is QUARTILE, which is the name that will be given to the column in the result cursor, the existence of which is tested for in the code (see above snippet).

Now we’ll create a Request that will demonstrate the use of this data item.

Business specification:

Create a request that shows all the cars in the database ranked by their gross profit. Show Make, Model, Color and Gross Profit group by gross profit quartile (i.e. of all the cars in the database, show the highest 25% in gross profit together, then the next 25% in gross profit, then the next 25%, then the bottom 25%)).

1. From the Foxfire! Request Manager toolbar, choose NEW o create a new Detail Request.

2. Name the Request QUARTEX with a description of “Quartile Report”

3. Select the following data items

Cars Make

Cars Model

Cars Color

Gross Profit

Quartile Report Variable

4. Sort by:

Quartile Report Variable

Gross Profit (descending)

5. Group by:

Quartile Report Variable

6. The Request should look like this:

7. Preview the Request

The output shows Quartile 1 first because it is sorted by Quartile first, and the first Quartile contains the top quarter (25%) Gross Profits (descending), because Gross Profit was second in the sort order.

Note that because the Quartile data item always has a value of 0, we could have just sorted by gross profit, but then the request would not have had a group header for the quartile.

ØExample 2 – Percentage Calculations (no groups)

The approach taken for creating Requests with percentages is similar to that used for a quartile calculation. We’ll create a placeholder data item which, when included in a Request, will trigger the required code in the AFTER SELECT phase of FFCONFIG.PRG to perform the necessary second pass calculations and hold the resultant percentage value.

The type of percentage calculation we want to produce is illustrated in Figure 1‑1 below. The right-most column contains the percentage calculation that is controlled by a new custom data item that we will create called Percent of Purchase Price.

This calculation will calculate for each make, what the percentage of the total purchase price paid for all cars on the report was.

Figure 1‑1 Example Percentage Calculation

First, we’ll create the new data item.

1. Open the Data Item Editor and Add a new data item call Percentage of Purchase Price.

The important points with this data item are the same as the Quartile data item. The purpose of the Data Item Expression is to set the data type and size of the column in the result cursor. The Result Field Name controls the name of the column in the result cursor. The Data Item Name controls what the user will see in the Request Editor. The File and Alias are left blank since this item does not need to refer directly to a particular database table.

2. Save this data item.

Note that when you choose Save, you will receive a warning that there is no Alias specified. This is Foxfire! ‘s way of reminding you that you normally need an alias associated with each data item. Just press OK

Now we need to add some code to FFCONFIG.

1. Open up FFCONFIG, and cut-n-paste the ‘Example of Percentage Calculation for Cars Purchase Price’ snippet from APPENDIX A into the AFTER SELECT phase (after the code for the QUARTILE calculation).

2. Save and Close FFCONFIG

3. You will also have to exit Foxfire! and then get back in to make the changes take effect.

Now we will create a new Request that uses the Percent of Purchase Price data item.

1. In Foxfire!, create a new Summary Request.

2. Name the Request, PERSAMP1 with a description of “Percent Summary of Cars Purchase Price by Make”.

3. Select the following Data Items

Cars Make

Sum of Cars Purchase Price

Percent of Purchase Price

4. Filter by Cars Color is Like “Yellow”

5. Your request should look like this:

6. Preview the Request

7. You should get Request output similar to the output shown in figure x

8. Now, remove the Sum of Cars Purchase Price data item from the request.

9. Preview the Request

You should get a warning (in the upper right hand corner of the screen) that the Cars Purchase Price item needs to be selected, yet the Request will not crash due to the defensive programming.

10. Press [Enter] to clear the message.

ØExample 3 – Percentage Calculations (with Groups)

There is a limitation with the implementation of percentages demonstrated in Example 2. What happens if you create a Detail Request type and choose to Group by one or more data items? The code in FFCONFIG doesn’t account for Request groups or subgroups, and the result is incorrect percentage calculations.

1. In Foxfire!, create a new Request of type Detail

2. Name the Request, “PerSamp2”

3. Describe the Request, “Percent Detail of Cars Purchase Price by Make”

4. Select the following Data Items

Cars Make

Cars Model

Cars Purchase Price

Percent of Purchase Price

5. Filter by Cars Color is Like “Yellow”

6. Group by Cars Make

7. Preview the Request

Notice the percentages calculations don’t take into account the Cars Make group break, they are still showing the percent of the TOTAL purchase price for all makes. We need to enhance the code in FFCONFIG to account for Group Breaks.

Now let’s make the changes in FFConfig to handle this situation

1. Open FFCONFIG and replace the code pasted into the AFTER SELECT phase in Exercise 2 with the Example 3 code from APPENDIX B.

2. Close and Save FFCONFIG.prg

3. Exit Foxfire! and get back in .

4. Now rerun the PERSAMP2 Request.

You should now see correct percentage calculations on a group by group basis. The code in FFCONFIG is flexible enough to handle any level of group break.

APPENDIX A

Code for Example 2, to be placed into the “AFTER SELECT” phase of FFCONFIG.PRG.

* Check if the Percent of Purchase Price data item is in the

* selected data item list

IF TYPE("PERPURCH") = "N"

* make sure either the Cars Purchase Price or Sum of Cars

* Purchase Price is also in the selected data item list

IF TYPE("PURCHPR") = "N" OR TYPE("SM_PURCHPR") = "N"

* declare required local variables

* needed to temporarily hold actual result cursor

* alias name

LOCAL lcWasAlias, ;

lcTempName, ; && used to hold the table name of the temporary result table

lcPurchPrField, ; && used to hold the name of either "purchpr" or "sm_purchpr" field

lnTotalPurchPr && used for holding purchase price total

* save the result cursor name, which is the current alias

lcWasAlias = ALIAS()

*get a unique temporary file name for temporarily copying the result cursor

lcTempName = SYS(3)

*copy the result cursor to the temporary file

COPY ALL TO (lcTempName) && Exchange the cursor for a datafile

*Note: we use a DBF rather than a second cursor since FoxPro cursors may or

*may not have a DBF header on file, which is required to perform a further SELECT.

*open the temporary file that contains the result set

USE (lcTempName) ALIAS TEMP_PER EXCL && same work area

*initialize the Total Purchase Price to zero

lnTotalPurchPr = 0

* we don't know if the user selected Cars Purchase Price or Sum of Cars Purchase Price,

* so we need to test for which one is present and put the value in a temporary variable

* Want to Save the Name of the field so that we can macro substitute it in the SUM command

lcPurchPrField = IIF(TYPE("PURCHPR")="N", "purchpr", "sm_purchpr")

SUM (EVAL(lcPurchPrField)) TO lnTotalPurchPr

* now that we know the total Purchase Price, we replace all placeholder

* fields with the percentage calc which uses the purchase price column and the

* calculated total purchase price.

* Need to multiply by 100 to get a whole number percentage

REPLACE ALL PERPURCH WITH ROUND(EVAL(lcPurchPrField)/lnTotalPurchPr * 100, 1)

*Put temp table back into expected cursor

SELECT * FROM TEMP_PER INTO CURSOR &lcWasAlias

* close the temporary table

USE IN TEMP_PER

* delete the temporary table

DELETE FILE (lcTempName + ".dbf")

ELSE

* warn user that they need to select the Cars Sales Price if they also select the Percent item

WAIT WINDOW "You need to select Cars Sales Price in order to calculate the Percent of Sales Price"

ENDIF

ENDIF

APPENDIX B

Code for Example 3, to replace code used in Example 2.

* Example Percentage Calc for Cars Purchase Price

* Check if the Percent of Purchase Price data item is in the selected data item list

IF TYPE("PERPURCH") = "N"

* make sure either the Cars Purchase Price or Sum of Cars Purchase Price is also

* in the selected data item list

IF TYPE("PURCHPR") = "N" OR TYPE("SM_PURCHPR") = "N"

* declare required local variables

LOCAL lcWasAlias, ; && needed to temporarily hold actual result cursor alias name

lcTempName, ; && used to hold the table name of the temporary result table

lcPurchPrField && used to hold the name of either "purchpr" or "sm_purchpr" field

* save the result cursor name, which is the current alias

lcWasAlias = ALIAS()

*get a unique temporary file name for temporarily copying the result cursor

lcTempName = SYS(3)

*copy the result cursor to the temporary file

COPY ALL TO (lcTempName) && Exchange the cursor for a datafile

*Note: we use a DBF rather than a second cursor since FoxPro cursors may or

*may not have a DBF header on file, which is required to perform a further SELECT.

*open the temporary file that contains the result set

USE (lcTempName) ALIAS TEMP_PER EXCL && same work area

LOCAL lnGrpIdx, ; && counter variable for scanning the srt/grp arrays

lcGrpBrk && name of the inner most group field in the result cursor

* the srt_grp array is a FoxFire array stored in the Request record and holds all group break

* names for the Request. This array will always be in scope when the Request

* is run. the srt_cnt Foxfire! variable stores the number of sort/group data items

* We need to find the inner most group break, so loop through the srt_grp

* array to find the name of the data item that is the inner most group break.

* note: there may be no group breaks, in which case lcGrpBrk will remain empty.

FOR lnGrpIdx = 1 TO srt_cnt

* the array srt_grp holds whether or not the sort/group item is a group

IF srt_grp[lnGrpIdx]

* the array srt_name holds the Result Field name of the data item

lcGrpBrk = srt_name[lnGrpIdx]

ENDIF

ENDFOR

* figure out which field we're using

lcPurchPrField = IIF(TYPE("PURCHPR")="N", "purchpr", "sm_purchpr")

IF EMPTY(lcGrpBrk)

* There are no breaks, so just calculate the total for all cars on the report

SUM (EVAL(lcPurchPrField)) TO lnTotalPurchPr

ELSE

* Generate group totals for each group

* this cursor will be scanned below and the GrpTot values used to calculate

* the percentages

SELECT &lcGrpBrk AS GrpVal, SUM(&lcPurchPrField) AS GrpTot ;

FROM TEMP_PER ;

GROUP BY 1 ;

INTO CURSOR curGroupTotals

ENDIF

* now that we have the group break totals for each group break,

* loop through the list of group values and replace all records

* for each group (i.e. make) with the percentage of the group

* total

* calculate the percentage for each item.

* again and fill each placeholder percentage field with the

* percentage calcalation based on the group break total

IF EMPTY(lcGrpBrk)

* No groups specified, so just replace the percentage with

* sale price / total price for all cars * 100

SELECT TEMP_PER

REPLACE ALL PERPURCH WITH ROUND(EVAL(lcPurchPrField)/lnTotalPurchPr * 100, 1)

ELSE

* Scan through the list of groups and just replace the percentage placeholder

* for the records in that group

SELECT curGroupTotals

SCAN

lnTotalPurchPr = curGroupTotals.GrpTot

lcGroupVal = curGroupTotals.GrpVal

SELECT TEMP_PER

REPLACE ALL PERPURCH WITH ROUND(EVAL(lcPurchPrField)/lnTotalPurchPr * 100, 1) ;

FOR &lcGrpBrk = lcGroupVal

SELECT curGroupTotals

ENDSCAN

ENDIF

*Put temp table back into expect cursor

SELECT * FROM TEMP_PER INTO CURSOR &lcWasAlias

USE IN TEMP_PER

DELETE FILE (lcTempName + ".dbf")

ELSE

WAIT WINDOW "You need to select Cars Purchase Price in order to calculate the Percent of Purchase Price"

ENDIF

ENDIF