Crosstab Report Setup

First, if you use Microsoft Access, do not confuse Crosstab Report in ASP.NET Report Maker with crosstab query in Access. ASP.NET Report Maker does not load crosstab queries in Access database. They have nothing to do with each other and they work differently.

Note: The current version of ASP.NET Report Maker supports Crosstab Report for Microsoft Access, SQL Server, MySQL, PostgreSQL and Oracle databases only.

The generated report page is a pure ASP.NET script, no server-side components is required.

A Crosstab Report is created from one existing Table, View or Custom View. If you need to display data from more than one table or view, join them first using a view your database (preferably) or using a Custom View in ASP.NET Report Maker.

After loading the database, the database objects (tables, views, custom views and reports) will be shown in the left pane (the database pane). To create a report, right click the database pane and select [Add Report (Crosstab)]. Alternatively, you can click [Edit] in the main menu and then select [Add Report (Crosstab)].

The Crosstab Report Setup window will show up:

The Crosstab Report Setup window has 4 tabs. Go through these tabs one by one to setup your report.

General

ASP.NET Report Maker will give the new Report a temporary name, Report<n>, where n is an integer. If you want to change the name, enter a new name in the [Report name] edit box. Then you can select a table, or a view, or a Custom View form the [Table or View] combobox.

This settings in this tab are mandatory.

Row Headings

Select the field that you want display the field values as row headings. You can optionally select up to 6 fields. Select fields in the order you want information displayed in the row headings. For example, you could sort and group values by Country and then State.

You can sort the values of the row heading fields in either ascending or descending order. After selecting the fields, press the buttons next to the comboboxes to change the sort order.

Check Show summary if you want to show sumary for each group.

Check Show empty rows if you do not want to skip rows with empty data.

Column Headings

Select the field that you want display the field values as column headings. For example, you would select Employee Name to see each employee's name as a column heading.

You can sort the values of the column heading field in either ascending or descending order. After selecting the fields, press the buttons next to the comboboxes to change the sort order.

If the field is of date/time type, the [Grouping Interval] property sets the interval for the field values, possible value are:

• Year
• Quarter
• Month
• Date
• Date/Time

If the field is of date/time type, and the [Grouping Interval] is Quarter or Month, the [Year filter] setting become available. If unchecked, quarters or months of different year in the report will be displayed together. If checked, a combobox for the available years will be added automatically to the report so user can select which year's data to display.

Note: If you use Date or Date/Time, make sure the distinct values of date or date/time in the source table are not too many or the number of columns may exceed that your database can support. For example, if you have orders each day in a year and you should not use Date as grouping interval or there will be 365 columns! In addition, even if your database can support it, the user will still need to scroll horizontally to see the report.

Value

Select the [Field] and the [Summary value] that you want to calculate for each column and row intersection. For example, you could calculate the sum of the field Order Amount for each employee (column) by country and state (row). Possible values of summary value are:

• AVG
• COUNT
• MAX
• MIN
• SUM

If you want to include sums for each row, select [Includes row sums] for the report.

Finally, you can choose to [Show page summary] and/or [Show grand summary] for all records at the end of the report. If [Show page summary] is enabled, the summary for the records in the current page will be displayed at the end of the page. If [Show grand summary] is enabled, the summary for all the records in the report will be displayed at the end of the page. These options are enabled by default.

Press [OK] to finish setup. When a report is created, the field settings will be copied from the source table. But from then on, the Report has its own field settings and is much like other table in ASP.NET Report Maker.

To edit a Report, right click the Report in the database pane and select [Edit Report]. Alternatively, you can select the Report first, then click [Edit] in the main menu and then select [Edit Report]. Note that the source table of a report cannot be changed.

Important Notes on Report

1. After a report is created, a report works independently from the source table it based on. Changing the structure of the source table does not change the report. If you delete a field in the source table that is used by a report, the report will fail. Similarly, if the report is based on a Custom View and you change the SELECT statement of the Custom View, the report may fail if a field in the report is missing. However, in the latter case, when a Custom View is edited, ASP.NET Report Maker will check the validity of the SQL. If the SQL fails, both the Custom View and reports that based on the Custom View will be displayed with a cross in the icon, like and . Also, a Report has its own field settings, changing field settings in the source table does not change the field settings in related Reports.
   
   
2. Report supports User ID and User Level. (See Security)

Also See:

Custom View Setup
Security Setup
Tutorial - Crosstab Report