EspressReport supports five basic report types: simple columnar, summary break, crosstab, master & details, and mailing label. Each report type has slightly different mapping options and can be used to generate a different style of report.
The simple columnar report is the most basic report type supported by EspressReport. It displays columnar data in a single table without any grouping or breaks.
Simple Columnar Report
The first step in data mapping for this type is to select the columns you want to include in your report from the Set Report Mapping window in the Report Wizard. The left side of the window lists all available columns from your query or data file and the right side lists columns that will be added to the report. Click on a column name to select it (Shift+Click for multiple selections) and press the or buttons to add it or to remove it from the report. The order in which you select columns in this window is the order in which they will appear in your report. You can change the order of the selected columns by highlighting the column you want to move and clicking the or buttons. Fields can also be moved by clicking and dragging the selected field.
The second step for report mapping is to set the column options. For simple columnar reports there is only one available option. The window displays a table showing all the columns you have selected for the report and all available options. The first field displays column names, the second one displays data type, and the third one displays a check box called Visible. Checking or un-checking this box will make the column visible or invisible within the body of the report (All columns are visible by default).
Simple Columnar Report Mapping
You can also create a Top N report using the columnar format. A Top N report will order and show you the highest set of values based on a particular column in the report. For example, you want to show top five customers based on total sales. To do this, check the Top N Report checkbox at the bottom of the data mapping window. You can then specify which column you want to use as the measure (for the above example, it would be total sales column), the number you want to retrieve (i.e. 10 or 20, etc), and whether you want to show the columns in ascending or descending order. The columnar report will then return the number of records specified sorted by the highest value for the specified column.
Like the simple columnar report, the summary break report type takes columnar data and presents it in a tabular form. However, unlike the simple columnar report, it allows you to break data into sections and insert summary fields.
Summary Break Report
The first step in data mapping for this type is to select the columns you want to include in your report from the Set Mapping window in the Report Wizard. The left side of the window lists all available columns from your query or data file and the right side lists the columns that will be displayed in the report. Click on a column name to select it (SHIFT+Click for multiple selections) and press the or buttons to add it or remove it from the report. The order in which you select columns in this window is the order in which they will appear in your report. You can change the order of the selected columns by highlighting the column you want to move and clicking the or buttons. Fields can also be moved by clicking and dragging the selected field.
The second step in data mapping is to set column options. There are five column options available for summary break reports. The window displays a table showing all of the columns that you have selected for your report and all available options. The first field displays column names, the second one displays data type, and the third one displays a check box marked Visible. Checking or un-checking this box will make the column visible or invisible within the body of the report (All columns are visible by default). The fourth field is the row break field. Checking this box indicates that the report will break and insert column summaries every time the selected column field changes. By default, the first column selected for the report is the break field. The fifth field allows you to select aggregation. If the Perform Column Aggregation checkbox at the bottom of the window is checked, the selected aggregation will be performed on the entire column and the report will only contain summarized data. If the box is not checked, the report will display all data and only insert aggregations as summaries after each row break.
The drop-down menu allows you to select the aggregation operation to be performed for that column. The aggregation operations that can be performed are: none, sum, maximum, minimum, count, average, first, last, sum of squares, variance, standard deviation, and count distinct. If you select to perform column aggregation, you must select an aggregation option for each column. Columns selected as row break fields cannot be aggregated.
For example, suppose we have the following data table:
| Order# | Product | Quantity |
|---|---|---|
| 12 | Chair | 2 |
| 12 | Table | 3 |
| 14 | Cabinet | 2 |
| 14 | Table | 5 |
Setting Order # as the row break field and the aggregation on Quantity to Sum without checking Perform Column Aggregation will produce the following report:
| Order# | Product | Quantity |
|---|---|---|
| 12 | Chair | 2 |
| Table | 3 | |
| 5 | ||
| 14 | Cabinet | 2 |
| Table | 5 | |
| 7 |
However, selecting Perform Column Aggregation and changing the Product aggregation to Count will produce the following report:
| Order# | Product | Quantity |
|---|---|---|
| 12 | 2 | 5 |
| 14 | 2 | 7 |
| 2 | 5 |
The sixth field allows you to select whether to repeat a break field or not. By default, a row break column will only print each distinct value in the column once (i.e. once for each group). Selecting this option will cause the break fields to repeat for each row of data in the group.
Summary Break Report Mapping
Summary Break Report Mapping
If you want to keep the data ordering specified in the data source, select the Keep Data Source Order option. To learn more about this feature, see Section 1.4.9 - Keep Data Source Order.
You can also create a Top N report using the summary break format. A Top N report will order and show you to highest set of values based on a particular column in the report. For summary break reports, you can also specify to show the highest values for each group. For example, you may want to show the top five customers in each region based on total sales. To do this, check the Top N Report checkbox at the bottom of the data mapping window. You can then specify which column you want to use as the measure (for the above example, it would be total sales column), the number that you want to retrieve (i.e. 10 or 20, etc), and whether you want to display the columns in ascending or descending order. The summary break report will then return the number of records specified (or number of records specified for each row break) sorted by the highest value for the specified column.
A crosstab report is a report format that shows and summarizes columnar data in a matrix-like form. Crosstab reports often resemble spreadsheets. Both rows and columns are summarized, allowing multi-dimensional data to be displayed in a two-dimensional format.
Crosstab Report
The actual report is only constructed during running or preview. Since the crosstab table is essentially constructed from scratch every time the report is run, it is easier to create a smoothly collapsing and expanding crosstab table.
Report mapping for crosstab reports is more complicated than other report types and may require some planning to be able to execute it correctly.
The first step in data mapping for this type is to select the columns you want to include in your report from the Set Report Mapping window in the Wizard. The left side of the window lists all available columns from your query or data file and the right side lists the columns that will be displayed in the report. Click on a column name to select it (Ctrl+click for multiple selections) and press the or buttons to add it or remove it from the report. The order in which you select columns in this window is the order in which they will appear in your report. Once you select the columns, click the button.
The second step in data mapping is to set column options.
The top of the column options window displays a table showing all of the columns that you have selected for your report and all available options.
The first field displays the Column Name.
The second field displays the Data Type.
The third field displays a checkbox marked Visible. Checking or un-checking this box will make the column visible or invisible within the body of the report.
The fourth field is the Row Break field. Selecting a column as the row break field will cause the report to insert a new crosstab row for each unique entry in the selected column.
The fifth field is the Column Break field. Selecting a column as a column break field will cause the report to create a new column for each unique entry in the database.
The sixth field is the Column Break Value field. Columns that you select as column break values become the fields that are summarized in the report.
The seventh field allows you to select column Aggregation. A drop-down menu allows you to select the aggregation to be performed for that column. The aggregation operations that can be performed are: sum, maximum, minimum, count, average, first, last, sum of squares, variance, standard deviation, and count distinct. Columns that have been selected as Row Break or Column Break fields cannot be aggregated.
The eighth field Order allows you to specify ordering for the column break column. This order will determine how the crosstab columns are drawn from left to right. Ordering options are not sorted, ascending, and descending.
![[Note]](../../../images/note.png) | Note |
|---|---|
The aggregation that will be performed depends on the aggregation of the Column Break Value column(s). For example: if you use the MAX aggregation, the greatest value from the row will be displayed in the Grand Total column. |
- Keep Data Source Order:
If you want to keep the data ordering specified in the data source, select this option. To learn more about this feature, see Section 1.4.9 - Keep Data Source Order.
- Show Column Aggregation Grand Total:
If this option is enabled, a column will be added into the report. Each row of the column will aggregate all values from the corresponding row into a single value.
- Column Aggregation Subtotal:
Subtotal column is very similar to the Grand Total column, except it doesn't aggregate the whole row, but each one of the Column Break group. This option allows you to disable the subtotal column or set its position (right or left to the Column Break group).
- Row Aggregation:
This is also very similar to the Grand Total column. The main difference is that this option doesn't aggregate rows, but columns. Also, it doesn't add a column to the report, but it adds a row instead. Use this options to disable the Row Aggregation row, or to adjust it's position (on top of the table or below the table).
- Align Column Break Value:
If you select more than one Column Break Value columns, use this option to choose whether the values will be aligned in rows or columns.
For example, assume we have following crosstab mapping:
Crosstab Fixed-Field Mapping
Setting CATEGORYNAME and PRODUCTNAME as Row Break fields, PRODUCTID as a Column Break Value field with an aggregation of SUM, and CATEGORYNAME as a Column Break field with the Show Column Aggregation Grand Total option enabled will produce a report from the beginning of the Crosstab Report section.
You can easily change the crosstab settings using the Change Data Mapping option under the Data menu. This will open the crosstab report mapping dialog again. To learn more about this feature, see Section 1.4.8 - Change Data Mapping.
The following table shows various formatting in crosstab reports:
| Column Aggregation Subtotal | Row Aggregation | Align Column Break Value | Template | |
|---|---|---|---|---|
| Report A | Right | Footer | Horizontal | Template A |
| Report B | Right | Header | Horizontal | Template B |
| Report C | Left | Footer | Horizontal | Template C |
| Report D | Left | Header | Vertical | Template D |
| Report E | Left | None | Vertical | Template E |
The crosstab report also provides a unique feature that allows users to create a transposed report. Data transposition allows users to create a report presentation where the input data is essentially rotated by 90 degrees. Columns become rows and rows become columns. For example, you have a following set of data:
| Region | Forcast | Sales |
|---|---|---|
| East | 24100 | 25050 |
| Midwest | 23110 | 22400 |
| South | 22300 | 26500 |
| West | 18750 | 19220 |
The data transposition feature can be used to create a report that will look like this:
| East | Midwest | South | West | |
|---|---|---|---|---|
| Forcast | 24100 | 23110 | 22300 | 18750 |
| Sales | 25050 | 22400 | 26500 | 19220 |
To transpose the data, click the Transpose Data option at the bottom of the result screen that first appears when you select a data source for the report. The following dialog shows the initial result set from the sample data in the example above.
Data Table Dialog
When you check the option at the bottom, a new dialog will appear allowing you to select the columns that you want to transpose. In this example, you would select the Forecast and Sales columns. Note that in order to perform transposition, the selected columns must have the same data type.
Select Transpose Dialog
After making your selections, click the button to apply the changes. You will see the transposition in the data table dialog. The Sales and Forecast headers are transposed into a column called ColumnLabel and their values are merged into a column called Value. Now you can map this result set using a crosstab report where ColumnLabel is the Row Break, Region is the Column Break, and Value is the Column Break Value to create the final transposed report layout.
Data After Transposition
A Master & Details report is a set of tabular data that is grouped according to a master field. This report type is most commonly used when you have fields in your data table that have a one to many relationship. A good example of this is an invoice. For each order number in a database there will be customer information and several items with pricing information. A Master & Details report would be used to group the information according to order number.
Master & Details Report
The first step in data mapping for this type is to select the columns you want to include in your report from the Set Mapping Window in the Report Wizard. The left side of the window lists all available columns from your query or data file and the right side lists columns that will be displayed in the report. Click on a column name to select it (Ctrl+click for multiple selections) and press the or buttons to add it or remove it from the report. The order in which you select columns in this window is the order in which they will appear in your report.
The second step in data mapping is to set the column options. There are three column options available for Master & Details reports. There is a drop-down menu at the bottom of the column options window. It is labeled primary key. The drop-down menu contains all of the columns that you have selected for the report. Selecting a column as the primary key will group the report according to that column. A new group will be created every time the value in the selected column changes.
The top of the column options window displays a table showing all of the columns that you have selected for your report and the available options. The first field displays column names, the second one displays data type, and the third one displays a checkbox marked Visible. Checking or un-checking this box will make the column visible or invisible within the body of the report. The fourth field is the Master field field. Selecting a column as a Master field will place the column value in the column header instead of the data section of the report.
A checkbox at the bottom of the data mapping window allows you to change the layout of the report. Checking the Side-By-Side Layout box will arrange the report so that the master section is displayed next to the details section rather than above it.
For example, suppose we have the following data table:
| Order # | Customer Name | Product | Unit Price | Quantity |
|---|---|---|---|---|
| 12 | Paul Campbell | Chair | $24.95 | 4 |
| 12 | Paul Campbell | Table | $127.50 | 1 |
| 14 | Sally Hayes | Cabinet | $227.25 | 2 |
| 14 | Sally Hayes | Chair | $24.95 | 2 |
| 14 | Sally Hayes | Table | $127.50 | 1 |
Setting Order # as the primary key and Customer Name as a Master field without using side-by-side layout will result in the following report:
| Order # | 12 | |
|---|---|---|
| Customer Name | Paul Campbell | |
| Product | Unit Price | Quantity |
| Chair | $24.95 | 4 |
| Table | $127.50 | 1 |
| Order # | 14 | |
| Customer Name | Sally Hayes | |
| Product | Unit Price | Quantity |
| Cabinet | $227.25 | 2 |
| Chair | $24.95 | 2 |
| Table | $127.50 | 1 |
However, the same report with side-by-side layout would look like this:
| Product | UnitPrice | Quantity | ||
|---|---|---|---|---|
| Order #: | 12 | Chair | $24.95 | 4 |
| Customer Name: | Paul Campbell | Table | $127.50 | 1 |
| Order#: | 14 | Cabinet | $227.25 | 2 |
| Customer Name: | Sally Hayes | Chair | $24.95 | 2 |
| Table | $127.50 | 1 |
Master & Details Report Mapping
Master & Details Report Mapping
If you want to keep the data ordering specified in the data source, select the Keep Data Source Order option. To learn more about this feature, see Section 1.4.9 - Keep Data Source Order.
You can also create a Top N report using the Master & Details format. A Top N report will order and show you the highest set of values based on a particular column in the report. For Master & Details reports you can also specify to show the highest values for each group. For example, you may want to show the top five customers in each region based on total sales. To do this, check the Top N Report checkbox at the bottom of the data mapping window. You can then specify which column you want to use as the measure (for the above example it would be total sales column), the number that you want to retrieve (i.e. 10 or 20, etc), and whether you want to display the columns in ascending or descending order. The master & details report will then return the number of records specified (or number of records specified for each row break) sorted by the highest value for the specified column.
A mailing label report is similar to a simple columnar report; however, it pre-arranges data in a way that allows you to create mailing labels. Data is arranged within the data table vertically and is wrapped automatically, making it easy to create a report showing an address list for mailing labels.
Mailing Label Report
Data Mapping for this type is exactly the same as for simple columnar report. The first step is to select the columns you want to include in your report from the Set Report Mapping window in the Report Wizard. The left side of the window lists all available columns from your query or data file and the right side lists columns that will be displayed in the report. Click on a column name to select it (SHIFT+Click for multiple selections) and press the or buttons to add it or remove it from the report. The order in which you select columns in this window is the order in which they will appear in your report.
The second step for data mapping is to set the column options. For mailing label reports there is only one available option. The window displays a table showing all of the columns you have selected for the report and all available options. The first field displays column names, the second one displays data type, and the third one displays a checkbox marked visible. Checking or un-checking this box will make the column visible or invisible within the body of the report (all columns are visible by default).
Mailing Label Report Mapping
Mailing Label Report Mapping
After you finish specifying mapping options, you can dismiss the Wizard and begin editing/modifying the report. To exit the Wizard, click the button in the last data mapping window. This will take you back to the main designer window where a blank (unformatted) report will be generated based on your mapping specifications.
Instead of generating an unformatted report, EspressReport also provides several additional options in the Wizard that allows you to automatically place some elements in the report, as well as provide a default style for the report elements. To continue on in the Wizard, click the button in the last data mapping window. This will bring up a dialog prompting you to add several elements to the report.
Add Elements Dialog
This dialog allows you to specify a report title and a logo for the report header. If you select to add a report title, you will be able to type in the title text. If you specify to add a logo, you can specify the location of the image file either using a file or URL path. You can also specify whether to place the logo in the upper right or upper left corner of the page.
You can also specify to add page numbers and date to the page headers and footers. You can select format and position of the elements either in the page header or footer and align them to the right, left, or center of the page.
After you finish specifying the elements, you can again click the button to dismiss the Wizard and begin editing/modifying the report with the added elements. If you click , you will be taken back to the last dialog in the Report Wizard. This dialog allows you to select a style for the report.
Report Style Dialog
From here you can pick a pre-defined style or a custom style you want to use for the report. Selecting a style will apply formats to the report elements, as well as change the default attributes of new report elements. Below are examples of each of the pre-defined report styles when applied to the same summary break report:
Block Left-Align
This style sets text alignment to the left and draws the report on a gray background with blue headers.
Break Right-Align
This style sets text alignment to the right and draws lines to demarcate the headers.
Center Table
This style centers text and draws table borders around the report cells.
List Left-Align
This style sets text alignment to the left and draws alternating color for each row.
List Break-Left
This style sets text alignment to the left, adds lines to demarcate column headers, and draws alternating color for each row.
Report styles will only effect the appearance properties of the report elements and will not change the report type or data mapping options that were selected earlier. Once you specify a style, click the button to dismiss the Wizard and continue to the main designer window.
In addition to the five pre-defined styles included with EspressReport, users can create their own style definitions. Style definitions are special versions of report templates that are saved with a .stl extension. Users can create a style definition using any report template.
The first step in creating a custom style is to set the global formats. This allows you to control the default look and feel for all elements the user inserts into the report. For more information about global formats, see Section 1.5.9.1 - Global Formatting.
The second step in creating a custom style is to select the look and feel for the elements in each section of the report. To do this, select the report element whose formats you want to apply to all elements in the section when the style is applied and select Set Attributes as Section Style from the pop-up menu.
Once you finish setting the attributes, save the report template as a custom style by checking the Create Style option in the save as dialog.
To select a custom style when creating a report, check the Use Custom Style option in the report style dialog and then specify the style (.stl) file that you want to use. When the style is applied, the global formats will be applied and the attributes defined for each section will be applied to the elements in the corresponding section in your new report. In addition, any lines and/or images defined in the style will be applied to the new report as well.
Often when you finish with the Report Wizard, your created report will be wider than the default page width (8.5"). When this happens, you will be presented with a warning.
Auto-Fit Columns Dialog
The dialog gives you an option to shrink the report to fit within the page. If you select , all columns will automatically shrink to fit the width of the page. The page boundary is indicated by a vertical bar within the design window. If you select to keep the columns overlapping the page boundary, the overflow will be printed on a new page when you preview the report. You can auto-fit the columns anytime you are editing the report by selecting Auto Fit Columns from the Format menu. The page width can also be adjusted by selecting Page Setup from the Option menu.
Once you complete the data mapping, a rough version of the report will be generated. If it is not correct, you can change the data mapping by selecting Change Data Mapping from the Data menu, or clicking the Change Data Mapping button on the toolbar. This will take you back to the Report Wizard (starting with data mapping), allowing you to go back to change the report type and data mapping.
Change Data Mapping window
When you change the data mapping, there will be two checkboxes at the bottom of the last screen in the Report Wizard. One is marked Apply Template and the other Apply Formula and Script. Checking the first box will retain the formatting of your report; however, the labels and formulas may not be correct. Un-checking this box will create a blank report with the new data mapping. If you did not format lot of the report objects, it is recommended that you un-check this box. This will ensure that the new data mapping is completely accurate. The second box allows you to include functions and scripts placed in the data table section to be applied (formulas and scripts in other sections are always applied), when you select the Apply Template option. Please note that if you have changed the number of columns or the data types of certain columns, the functions and/or scripts may no longer work.
Summary Break, Cross-tab and Master&Details reports use memory-optimized query processing which can unfortunately change the order of the data. In other words: the data in the report may be displayed in different order than the data in the data source. To prevent this from happening, choose the Keep Data Source Order option in the report mapping dialog. This will disable the memory-optimized query processing which means that the data will be passed to the report without any optimalization (i.e. the original data ordering will be preserved).
Keep Data Source Order option
| Note |
|---|---|
If you choose the Keep Data Source Order option, the Top N Report option for Master&Details and Summary Break reports will be disabled. |