Creating a Cross-Tab Report

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Creating a Cross-Tab Report

Previous pageReturn to chapter overviewNext page

 

A Cross-Tab report is one that summarizes information from a database and displays the result in a spreadsheet structure.  Reports of this type are useful for summarizing large amounts of information and identifying trends in data.

 

A classic use of a Cross-Tab report is to sum monthly sales figures by salesman.  Assume that you have a table that contains the following fields: salesman, sale date and sale amount.  Using Cross-Tabs, you could easily generate the following report which shows each salesman's performance.

 

_bm9

 

To create a Cross-Tab report, choose the File, New, Cross-Tab Report option from ReportPro's main menu.  You can also create a Cross-Tab report using the Cross-Tab Report Wizard discussed in the next chapter.

 

The initial process of creating a Cross-Tab report is similar to creating a Standard report in that you must specify a data source.  This process is covered in detail in the previous section.

 

After you define a data source, the Cross-Tab Properties dialog will be displayed.

 

The Cross-Tab Properties Dialog

 

_bm10

 

To define a Cross-Tab, you must specify a row and column data expression as well as a summary function.  The row and column data expressions define what information is used to create the cross-tab.  The summary function defines what action is performed on the data.

 

ReportPro performs the following steps to create a cross-tab report.  First, ReportPro searches through the data source for all unique values returned by the column and row data expressions.  After it has found all the unique values for each expression, ReportPro sorts those values in ascending order.  For each unique value returned by the column data expression, ReportPro will create a column in the cross-tab.  For each unique value returned by the row data expression, ReportPro will create a row in the cross-tab.

 

Next, ReportPro will find all the locations in the database where the values returned by the row and column data expressions intersect on the same logical record.  When it finds a 'hit', ReportPro will perform the specified summary function.  The Summary Function combo box allows you to specify which mathematical operation to perform.  There are five summary operations available.

 

Function

 Description

Average

 Determines the average of all cross-referenced values

Count

 Counts all cross-referenced values

Maximum

 Determines the largest cross-referenced value

Minimum

 Determines the smallest cross-referenced value

Sum

 Sums all cross-referenced values

 

If the summary function is Count, ReportPro will simply count the number of times the row and column data expressions intersect in the data source and place that number in the cross-tab.  If the summary function is other than count, then a summary data expression is required.  In this situation, ReportPro evaluates the summary data expression and performs the summary function on its return value.  

 

The column and row display expressions are used to determine what values are actually displayed in the cross-tab column and row headers.  As mentioned earlier, ReportPro sorts the cross-tab based on the values returned by the row and column data expressions.   In the column data expression shown above, the Month function is used to sum and sort the columns in month order.  Given a date, the Month function returns the month of the date as an integer (1-12).  This works out well for sorting but does not look very professional as a column header.  By using the Num2Cmonth function in the column display expression we can convert the return value of the Month function into the text name of the month (January, February, etc.).

 

Each of the expressions described here can be entered directly into the appropriate edit control or can be entered via the Expression Builder by pressing the _bm11 button next to the edit control.

 

The Picture edit, which is available for each expression, allows you to specify an optional picture clause that is used to format the display expression's return value.  See Appendix A for more information on picture clauses.

 

After you have specified the desired cross-tab information, click the OK button.  ReportPro will create the report and automatically layout the cross-tab field objects.  You can change the size and location of the cross-tab field objects, but they cannot be moved out of their original band.

 

 

Also See:

 

Creating a Report

Creating a Standard Report

Creating a Label Report