CrossTab reports let you summarize large amounts of data from both spreadsheets created in Quattro Pro and external databases.
In this section, you'll learn about
creating CrossTab reports
creating CrossTab reports using OLAP data sources
naming CrossTab reports
formatting CrossTab reports
updating and refreshing CrossTab report data
sorting and filtering CrossTab report data
moving and deleting CrossTab reports
customizing CrossTab report fields
hiding field items and details
calculating field summaries in CrossTab reports
performing field data comparisons in CrossTab reports
expanding, filtering, and removing OLAP CrossTab report field elements
making static copies of CrossTab reports
expanding CrossTab reports
Creating CrossTab reports
You can use CrossTab reports to analyze and organize data located in notebooks and databases. For example, you might maintain a database with addresses of customers in different cities, including information on customer sales. The database effectively stores the information but does not allow you to analyze the data in a convenient or precise manner. By building a CrossTab report, you can analyze, organize, and summarize the data into a constantly updated report on area sales. The same database can also be analyzed to determine sales by salesperson for each product that the business sells.
Data does not have to be on the same spreadsheet of a notebook for you to create a CrossTab report. You can connect two or more spreadsheets that contain data and produce a CrossTab report to summarize the data.
Planning and interpreting your data analysis
Before you produce a CrossTab report from a data source like a database, you must plan which data fields you want to analyze. The CrossTab report feature analyzes data by using selected data fields as row or column labels and numeric data fields as the data being analyzed. The fields in the row and column areas are used as selection criteria to determine which values from the database to include at intersections of the row and column field labels. For example, assume a database contains product sales information (data). In the CrossTab report, place the field containing the names of the company's salespeople along the left of the spreadsheet (rows), and the field containing the products you sell along the top of the spreadsheet (columns). The intersection of the labels "Salesperson A" and "Products" shows the total number of products that Salesperson A sold.
To build a CrossTab report
1 Click a spreadsheet cell containing data.
2 Click Tools Data tools CrossTab Report.
3 Drag any fields from the list in the Fields area into any of the following positions of the Layout area:
Rows
Columns
Data
Pages
4 Type the report destination in the Destination box.
Unless you select a destination, the CrossTab report will be placed in A1 of the next available, unprotected spreadsheet.
Notes
Dragging a field to the Pages position is optional. Fields placed in the Pages position appear in the upper-left corner of the CrossTab report as items in a list box so that you can select the desired field item to view. If fields are placed in the Pages position, you can expand the report. For more information about expanding CrossTab reports, see "Expanding CrossTab reports."
You can drag more than one field into each position of the Layout area, but fields cannot appear more than once in each position.
To connect multiple spreadsheets to produce a CrossTab report
1 Click a spreadsheet cell containing data.
2 Click Tools Data tools CrossTab Report.
3 Click the Range picker in the Source data box.
4 Press SHIFT, and click the tabs of the spreadsheets where the data is stored.
You must click the spreadsheet tabs in ascending order.
5 Click Maximize on the CrossTab report title bar.
Note
After you connect multiple spreadsheets of data, the first field in the list in the Fields area of the CrossTab report dialog box is [SHEET]. You can drag this field to the Pages, Rows, or Columns positions of the Layout area.
Creating CrossTab reports using OLAP data sources
CrossTab reports are particularly suited to displaying and exploring data contained in OLAP servers. You can create CrossTab reports from fields in an OLAP server by selecting an OLAP server as the data source. You can also import CrossTab reports created with OLAP software into Quattro Pro.
To select an OLAP data source for a CrossTab report
1 Click Tools Data tools CrossTab Report.
2 Enable the OLAP report option.
3 Click OK.
4 In the CrossTab data sources dialog box, select the server you want.
5 In the Connect to provider dialog box type a user ID and password.
To import OLAP CrossTab reports
1 Click Tools Data tools CrossTab Report.
2 Enable the OLAP report option.
3 Click OK.
4 In the CrossTab data sources dialog box, select the server you want.
5 In the Connect to provider dialog box, type a user ID and password.
6 Select a data source.
7 In the CrossTab report dialog box, click Import report.
8 In the Import templates dialog box, choose a report type.
9 In the Import report dialog box, choose a report.
Note
You must have an OLAP provider running in the background before you can connect to the server.
Tip
You can also click the Import report button in the CrossTab report dialog box to import OLAP CrossTab reports.
Naming CrossTab reports
After you create a CrossTab report, you can change its name from the default. If you generate several CrossTab reports from data, you have to assign a unique name to each report.
To name a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Options.
3 Type a name for the report in the Name box.
Notes
The default name is CrossTab Table 1.
All CrossTab reports must have a name.
Formatting CrossTab reports
Once you summarize data in a CrossTab report, you can align data fields by row or column. You can also display the totals and grand totals for both column and row data. Showing column and row totals is useful because it gives you a better understanding of how each field relates to the total and can help you see an overall picture of the data.
You can also change the appearance of a CrossTab report by changing formatting options, such as the numeric format, and the alignment of labels.
To align data fields in a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Report.
3 Enable one of the following options:
By row-data appears vertically in the report
By columns-data appears horizontally in the report
Note
By default, data fields are aligned by row.
To show column summaries in a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Options.
3 Enable the Show column summaries check box.
Tip
You can also show row summaries by enabling the Show row summaries check box.
To customize the appearance of a CrossTab report
1 Click Tools Data tools CrossTab Options.
2 Enable any of the following check boxes:
Format CrossTab report-applies a preset format to the fields and data, making the report easier to read
Preserve data formatting-maintains the numeric format of the source data, such as currency
Center labels across blocks of cells-centers the field names (labels) across the column of data. (By default, field names are right-aligned).
Updating and refreshing CrossTab report data
CrossTab reports are dynamic, meaning that changes in the source data are reflected in the CrossTab report. You can set Quattro Pro to automatically check the source data when you open the report and update the report if the source data has changed.
To update CrossTab report data
1 Click Tools Data tools CrossTab Options.
2 Enable the Update data on open check box.
Note
You cannot edit or delete data in a CrossTab report. To edit the values in a CrossTab report, you must make a static copy of the report. For information about making static copies of CrossTab reports, see "Making static copies of CrossTab reports."
To refresh a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Refresh.
Sorting and filtering CrossTab report data
You can sort and filter data in a CrossTab report.
To sort CrossTab report data
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Sort and filter options.
3 In the Sort and filter dialog box, choose a field from the Field list box.
4 In the Sort area, choose an option from each of the following list boxes:
Based on-lets you sort data based on the contents of a field or data field
Data field-lets you choose a data field from the CrossTab report
Sort order-lets you sort data in ascending or descending order
To filter CrossTab report data
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Sort and filter options.
3 In the Sort and filter dialog box, choose a field from the Field list box.
4 In the Filter area, choose an option from each of the following list boxes:
Display-lets you choose the items you want displayed after filtering
Based on-lets you choose a data field
5 Type a value in the Quantity box to specify the number of rows available.
Moving and deleting CrossTab reports
After you create a CrossTab report, you can move it to another spreadsheet of your notebook. You can also delete a CrossTab report.
To move a CrossTab report
1 Click Tools Data tools CrossTab Report.
2 Click the Range picker in the Destination box, and drag an area on the spreadsheet where you want to display a CrossTab report.
Notes
You can place one or more CrossTab reports on any spreadsheet of a notebook.
Select a destination range that does not overlap an existing CrossTab report; otherwise, the existing report will be overwritten.
To delete a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Remove.
Customizing CrossTab report fields
A field is a category of information that appears in a database column. Each field in a database contains items-the individual entries that occur in a field. For example, a field named "Country" might have field items that include France, Venezuela, and Kenya.
You can change a field name in a CrossTab report. The field name is changed only in the report; it is not changed in the original data.
You can also change the position of any field. You may want to change a field's position to explore hidden relationships in the data. There are four positions: rows, columns, data, and pages. Row fields are displayed along the left side of the report, column fields are displayed along the top of the report, and pages fields are displayed in the top left corner of the report.
Changing a field's position can significantly change the information that appears in the CrossTab report. Certain fields are more useful in some positions than others. For example, a field with several hundred field items is not an appropriate choice for the pages position of the CrossTab report because you must scroll through several hundred different versions of the CrossTab report to view all of the data.
To change a field name in a CrossTab report
1 Click a field.
2 Click Tools Data tools CrossTab Field options.
3 Type a new name in the Label box.
Tip
You can also change a field name by double-clicking the field.
To change the position of a field in a CrossTab report
1 Click a field.
2 Click Tools Data tools CrossTab Field options.
3 In the Position area, click the position where you want to move the field.
Tip
You can also change a field's position by dragging it to a new position.
Hiding field items and details
Fields can contain multiple field items. For example, a database may have a field named "Region" with four field items: East, West, North, and South. If particular items are not relevant or necessary to the report, or if the CrossTab report is large or complex, you can hide items The hidden item's information is still available, but it is not displayed in the report.
You can hide details about a selected field so that you have the same level of information for data analysis, but fewer details are shown in the report.
For example, assume that a CrossTab report has two fields in its columns: "Year" (with field items 1991 and 1992) and "Quarter" (with field items Q1, Q2, Q3, and Q4). When you hide details of the Year field, the resulting report shows the field items for "Year" (1991 and 1992) without showing the breakdown for the values by quarter.
After you hide the details of a selected field, you can display them again at any time.
To hide field items in a CrossTab report
1 Click a field.
2 Click Tools Data tools CrossTab Field options.
3 In the Hide field items list, enable the check box of the field item to hide .
Notes
You cannot hide all items in a field.
You must have more than one field in the rows or columns of a CrossTab report to hide details.
Tip
You can also change a field name by double-clicking the field.
To hide the details of a CrossTab report field
1 Click the name of a field.
2 Click Tools Data tools CrossTab Hide.
To show the details of a CrossTab report field
1 Click the name of a field.
2 Click Tools Data tools CrossTab Show.
Calculating field summaries in CrossTab reports
You can calculate the field summaries in a CrossTab report to give you an overall look at the data. You can choose from many field summary options, including calculating the sum, average, and standard deviation. Only fields in the data position of CrossTab reports can be summarized, because they are the fields that have numeric values.
To calculate a field summary in a CrossTab report
1 Click a data field.
2 Click Tools Data tools CrossTab Field options.
3 Enable any of the following check boxes in the Summary list:
Sum-calculates the sum of items for numeric field items
Count-calculates the number of occurrences for label field items
Average-calculates the average
Max-displays the maximum value
Min-displays the minimum value
StdDevp-calculates the population standard deviation
StdDevs-calculates the sample standard deviation
Varp-calculates the population variance
Var-calculates the sample variance
CountNonBlank-counts non-blank cells for a field
Notes
You cannot drag field summaries because they are superficially generated on the report.
You can display an enormous amount of information about your data if you enable all of the summary options; including only those options which are most relevant makes the report easier to read.
Performing field data comparisons in CrossTab reports
You can compare field data, and Quattro Pro lets you choose from many field data comparison calculations. Field data comparisons are useful to summarize and analyze the data. Only fields in the data position of CrossTab reports can be compared, because they are the fields with numeric values.
To compare field data in CrossTab reports
1 Click a data field.
2 Click Tools Data tools CrossTab Field options.
3 Click Compare>>.
4 Choose one of the following from the Data comparison list:
Difference from-displays all data in the data area as the difference between the value for the specified field and field item
% of-displays all data in the data area as a percentage of the value for the specified field and field item
% difference from-displays all data in the data area as the difference between the value for the specified field and field item but displays the difference as a percentage of the base data
Running total in-displays the data for successive items as a cumulative total
% of row-displays all data in each row as a percentage of the total for each row
% of column-displays all data in each column as a percentage of the total for each column
% of total-displays all data in the data area as a percentage of the grand total of all of the data in the CrossTab report
Index-displays the result using the following calculation: ((value in cell) x (grand total of grand totals)) / ((grand row total) x (grand column total))
Note
You can display an enormous amount of information about your data if you enable all of the data comparison options; decide which options are most relevant and include only them.
Expanding, filtering, and removing OLAP CrossTab report field elements
When a database is large or complex, the field elements have many levels. Expanding a CrossTab report field element reveals all hidden details associated with a field. Expanding (or "drilling down") is a navigational technique that lets you explore levels of data ranging from the most general (up) to the most detailed (down).
For example, assume your database contains information about the Olympic Games. If you drill down "Olympics," details display that contain information related to the Olympics: whether it was a winter or summer games, the country that hosted the Olympics, and the city where the games were held. Because some countries have hosted more than one Olympics, there are more details associated with those countries.
Drilling down is useful for analyzing data or comparing the details of one element with other elements. It takes you from the most general detail of an element to the most specific.
Because OLAP source data is often very large, it is important to be able to minimize the amount of information that appears in your CrossTab reports. Level filtering lets you select specific field elements from the level to display in the CrossTab report, rather than displaying all elements in a level.
You can also remove elements from an OLAP CrossTab report to minimize the amount of data.
To expand an OLAP CrossTab report field element
1 Click a field.
2 Click Tools Data tools CrossTab Drill down.
Note
The Minus button in the element cell indicates that the element has been drilled down.
Tip
You can also expand the OLAP CrossTab report field elements by clicking Plus in the element cell.
To collapse OLAP CrossTab report field elements
1 Click an expanded field.
2 Click Tools Data tools CrossTab Rollup.
Tip
You can also collapse the OLAP CrossTab report field elements by clicking Minus in the element cell.
To filter OLAP CrossTab report field elements
1 Double-click the name of a field.
2 Click Filter.
3 In the Element selection dialog box, choose the elements from the Source list that you want to move to the Filtered list.
4 Click Add.
Note
If you open the Element selection dialog box and all of the field elements are in the Filtered list, remove all field elements to the Source list.
Tips
To move all of the elements from the Source list to the Filtered list, click Add all.
You can drag elements either from the Source list to the Filtered list or from the Filtered list to the Source list.
To remove an OLAP CrossTab report element
1 Click an element.
2 Click Tools Data tools CrossTab Remove element.
Making static copies of CrossTab reports
You can make an exact copy, called a static copy, of a CrossTab report. It is called a static copy because it is no longer connected to the dynamic source data, so any changes made to the source data are not reflected in the static copy. The static copy is placed on the next available, unprotected spreadsheet of the notebook.
To make a static copy of a CrossTab report
1 Click a cell in a CrossTab report.
2 Click Tools Data tools CrossTab Copy static.
Note
You can determine whether a report is dynamic or copied from the appearance of the field names. In dynamic CrossTab reports, the field names are grayed.
Expanding CrossTab reports
Expanding CrossTab reports lets you open a CrossTab report onto several different spreadsheets of a notebook. You can expand a report only if you have a field in the Pages position of the CrossTab report. For example, suppose you generate a CrossTab report, which is placed on spreadsheet B that has a field named "Year" in the Pages position with two field items, 1990 and 1991. When you expand the CrossTab report, the first field item (1990) and the data associated with it are placed on spreadsheet C, and the second field item (1991) and the data associated with it are placed on spreadsheet D.
To expand a CrossTab report
1 Click a cell on a CrossTab report.
2 Click Tools Data tools CrossTab Expand.
Note
The report is expanded to the next available, unprotected spreadsheet of the notebook.
