Cross-Tab Report
A cross-tabulation (or "cross-tab") report is one that summarizes information in your database. It's laid out in a grid, with rows representing one "fact" (for example, country, date, or salesperson), columns representing another, and the intersection of rows and columns containing the summarized information. It's similar to a PivotTable in Microsoft Excel. In fact, you can output a cross-tab report to an Excel PivotTable if you want to do additional analysis of the data in Excel.
For example, you may want to see a breakdown of your sales by country and product. In this case, the rows would be the different countries your customers are in, the columns would be the product names, and the cell at the intersection of a specific row and column would be the total sales for that product in that country.
To create a cross-tab report, select at least three fields you want to appear in the report, and then select where each field goes. A row field is one that has each different value appear in a row in the report. A column field is one that has each different value appear in a column in the report. A data field is one that's summarized and placed in the cells at the intersection of rows and columns.
Here are some notes about how cross-tabs work:
You can have more than one row field. For example, you may want sales by country, and within country, by state. Choose both the country and state fields, and select country as the first row field and state as the second. The report shows each state in each country, with subtotals for each country.
You can specify how the data field is summarized. For numeric fields, the default is to sum the values, but you can use another type of summarization, such as average, or even no summarization at all, which means individual values are charted rather than summarized values. For all other types of fields, you can only have it count the number of values.
If you use a date field as a row or column field, you can specify how the date is displayed. For example, you may want to show the month or year rather than the individual dates.
You can have more than one data field. For example, if you want to show not only the total sales amount but also the count of how many sales there were, select the amount field and some other field, and make them both data fields. The amount field is summed and the other field is counted.
You can have a page field for the report. The page field is like a row field but appears in the page header rather than the rows and there's a page break when the page field value changes.
The report is automatically sorted in ascending order on the row fields, but you can instead sort in ascending or descending order on one of the data fields.
Since there may be a lot of rows and/or columns, depending on the fields you choose, Student Manager Reports automatically horizontally paginates the report for you. This is similar to how spreadsheets like Microsoft Excel print multi-page reports. Student Manager Reports prints a page for the first set of rows and columns, then the next page has the same set of rows but more columns, and so on until all of the columns have been printed. Then it prints the next set of rows and the first set of columns, and continues in this manner until all columns and all rows have been printed.
There are totals for each row and each column, and a grand total at the end of the report.
To create a Cross-Tab Report, follow the steps in the Creating a Report topic, and select the Cross tabulation type.
Step 1 Main Report Information
The first step in this wizard is to enter the following information:
Report name: the name of the report. If you enter the name of an existing report, an error message displays. You can't use a backslash ("\") in a report name; the computer will beep if you press that key. You cannot go to the next step until you have entered a valid report name.
Folder: the folder this report belongs in. The default is the current folder in the Reports Explorer. You can select the desired folder by clicking the down arrow beside the folder name and choosing the folder in the list that appears. To expand a folder, click the + sign in front of the folder name; the + sign changes to a - sign. To collapse a folder, click the - sign in front of the folder name; the - sign changes to a + sign. To select the desired folder, either click it and click the down arrow again to close the list or double-click it.=
Comments: any comments entered here appear in the Reports Explorer when the report is selected.
Step 2 Data Selection
Step 2 allows you to select which fields appear in the report. Note that if you've turned on Advanced layout in Step 5, the options in this step are disabled (with the exception of the Advanced button) and a message appears indicating why this is the case.
The options in this step are:
Table: a drop-down list that displays the names of the tables you can select fields from.
Sort: Student Manager Reports by default displays fields in alphabetical order. This makes it easy to find a field in the list. However, sometimes it makes more sense to display fields in the order they appear in the table. For example, for typical address fields, you would see this order alphabetically:
Address
City
Contact Name
Country
Customer Name
Postal Code
Region
whereas they'd appear like this in table order:
Customer Name
Contact Name
Address
City
Region
Postal Code
Country
Click this button to display fields in table order. Click it again to display them alphabetically. The setting of this button is remembered, so if you edit a report later, it has the same setting.
Find: a long list of fields can make it difficult to find the ones you're interested in. If you know part of the field caption you're looking for (such as a field with "tax" somewhere in the caption), click this button, enter the text, and click OK. If there are any fields that match, the first one is selected. Press F3 to find the next one containing the same text.
Relation: a long list of tables can make it difficult to find the ones you're interested in. Once you've selected fields from one table, chances are you're next going to select fields from a directly-related table. For example, if you chose a field from Customers, you are more likely to choose a field from Orders than from Products. Click this button to reduce the list of tables to only those directly related to ones you've already selected fields from. Click it again to restore the list to all tables.
Available fields: a list containing all reportable fields from the table shown in the Table list that have not already been selected.
Selected fields for this report: a list showing the fields to be included in the report, with the table the field is from shown in parentheses. Fields in this list may be moved up and down to change the order in which the columns are printed on the report; to do this, click the button in front of the field to move and drag the button up or down in the list.
Add: clicking this button adds the selected field in the Available list to the Selected list. You can select several fields by holding down the Ctrl key as you select them; clicking Add adds all selected fields to the Selected list. You can also add the field by double-clicking it in the Available list, selecting it and pressing Enter, or dragging the field from the Available list to the Selected list. The field is removed from the Available list.
If you select a field from a table that is not related in any way to the other tables involved in the report, you will get a warning message.
Add all: clicking this button adds all fields from the selected table to the Selected list.
Remove: clicking this button removes the selected field (or fields if you have selected more than one) from the Selected list. You can also remove the field by dragging it from the Selected list to the Available list. The field is added to the Available list (although you may not see it if the table the field is from is not the selected table).
Remove all: clicking this button removes all fields from the Selected list, and adds them to the Available list (although you only see those fields from the selected table).
New Formula: click this button to define a new formula. The new formula is automatically added to the report.
Edit Formula: click this button, which appears instead of New Formula when you have selected an existing formula, to edit the formula.
Values: displays a list of the unique values in the field selected in either the Available or Selected list, depending on which one was selected last. This is handy if you're not sure what data a field contains.
Step 3 Cross-Tabulation Options
In Step 3, you specify which fields appear in which places in the report. The way you do that is by dragging a field name from the list on the left to the appropriate place at the right.
For example, to use the Amount field as a data field, click it in the list, hold down the mouse button, and move the mouse until it's over the box below the word "Data," then let go of the mouse.
You can have more than one row field and more than one data field if you want; simply drag as many fields as you wish to the appropriate place. The order in which you drag the fields is the order in which they appear in the box and the order in which they appear in the report. Fields may be moved up and down to change the order; to do this, click the button in front of the field to move and drag the button up or down in the list. Fields appear in the Data box with the type of summarization for the field in parentheses. For example, if Total Price is summed, it appears as "Total Price (Sum)."
You can only have a single page field and a single column field.
To remove a field from a place on the report, drag it from the appropriate box back to the list at the left or select it and press the DELETE key.
Fields in the Available list are not used in any way in cross-tab reports.
To change how a field appears in the report, select it and click the Properties button. This displays the Properties dialog. You can also display this dialog by double-clicking the field or selecting it and pressing ENTER.
Click the Advanced button to customize how the report runs.
Field Properties
The Properties dialog allows you to specify how the field appears in the report. You can display this dialog for the highlighted field by clicking the Properties button in Step 3 of the Cross-Tabulation Report Wizard, double-clicking the field, or selecting the field and pressing ENTER.
See the Field Properties topic for more information.
Step 4 Standard Filter Options
Step 4 allows you to specify a filter used by default for this report. You can skip this step if no filter is normally used.
To specify a filter, click the Filter or Exclude button, and enter the filter conditions using the same procedures described in the Filter Records topic.
Step 5 Formatting Options
Step 5 allows you to specify certain options for the report.
The options in this step are:
- Header: text that appears at the top of every page. If you want to use an expression rather than fixed text, such as a user-defined function, add
{and}around field or function names in the text. You can also click the Expression Builder button (the ... beside the text box) to display the Expression Builder, which is a much easier way to create the expression to use.
Here's an example of an expression that displays
Sales for <spelled out month> <year>(such as "Sales for December 2001") in the report header:
Sales for {cmonth(date())} {year(date())}
Here's an expression that uses the built-in GetConditionValue function, which returns the value of a filter condition for the specified field, to display a header showing "Sales for " a particular country the report is filtered on:
Sales for {GetConditionValue('customers.country')}
Expressions can even include fields from the result set for the report.
Footer: text that appears at the bottom of every page. As with the header, you can use an expression for the report footer; click the Expression Builder button to display the Expression Builder, which is a much easier way to create the expression to use.
Orientation: The default choice, "Automatic," means that if the report is too wide to fit in portrait orientation, it automatically switches to landscape. However, you can override this by choosing either "Portrait" or "Landscape" to force the report to be printed in the desired orientation.
Left margin: how many characters to indent the entire report; this is handy if, for example, you want to print a report and punch holes in it so it can be inserted into a binder.
Include filter in report header: if this option is turned on, any filter you've set for the report is displayed in the header of the report.
Include database in header: turn this option on to include the database name in the header of the report.
Run report with no records: turn this option on (the default) to run the report even when there are no records that match your filter condition. This results in a blank report. If this option is turned off, rather than displaying a blank report, Student Manager Reports displays a message that there are no records.
Row totals: turn this option on to display totals for each row.
Allow report to be filtered: turn this option on if someone can specify a filter for the report in the Reports Explorer. This should normally be turned on, but if you specify a filter in Step 5, you may wish to turn this off.
Place subtotals above details: Student Manager Reports normally places subtotals for row fields below any detail rows.
If you want subtotals to appear in the same row as the row field, so they appear above sub-row fields, turn the Place subtotals above details option on.
Note that this option is disabled if Start each group on a new page is turned on for the first row field in the Field Properties dialog, since those two settings can't be used together.
Show percentage for columns: by default, any data field that has the Show percentage of total option in the Field Properties dialog turned on displays each value as a percentage of the row total. Turn Show percentage for columns on if you want the percentages to be of the column totals instead.
Column totals: turn this option on to display totals for each column.
Template: this specifies the overall layout of the report. The default template for a new report is determined by the Default template setting in the Options dialog, but if other templates are available, you may select the desired one from the drop-down list. Templates are created and edited using the Template Editor.
Chart: if this option, which is only available if you have a Premium or Ultimate license, is turned on, you can add a chart to the report by clicking the Edit button beside this option and using the Chart Wizard.
Edit: click this button, which is only enabled if the "Chart" option is turned on, to display the Chart Wizard. The only two steps available in the Chart Wizard are Chart Layout and [ormatting Options because the rest of the steps in the Chart Wizard are used for creating stand-alone charts.
Step 6 Security Options
The options in Step 6 allow you to determine who can access your report and what they can do with it.
The options in this step are:
Available groups: this shows which user groups do not have access to this report.
Selected groups: this shows which user groups have access to this report. By default, the Everyone group has access to a new report, so all users can see it. If you want only certain users to see it, remove the Everyone group from the Selected list and add the appropriate groups from the Available list. If no groups have access to the report, the report is essentially private, available only to you.
Add: clicking this button adds the selected group in the Available list to the Selected list. You can select several groups by holding down the CTRL key as you select them; clicking Add adds all selected groups to the Selected list. You can also add the group by double-clicking it in the Available list, selecting it and pressing ENTER, or dragging the group from the Available list to the Selected list. The group is removed from the Available list.
Add all: clicking this button adds all groups from the Available list to the Selected list.
Remove: clicking this button removes the selected group (or groups if you have selected more than one) from the Selected list. You can also remove the group by dragging it from the Selected list to the Available list. The group is added to the Available list.
Remove all: clicking this button removes all groups from the Selected list, and adds them to the Available list.
In addition to running the report, users in the selected group may modify or delete this report: turn this option on if the selected group in the Selected list can edit or delete this report; if this item is turned off, only you are able to do so.