Inserting Crosstabs in a Report

You can create crosstabs in a report easily using the crosstab wizard, however, the wizard varies with the data resource type used for the crosstab: business view or query resource. This topic introduces how you can create a crosstab with the crosstab wizard using different data resource.

You can insert crosstabs in the report areas listed in Component Placement. When you insert a crosstab into a banded object in a page report, you can use a data container link to define the relationship between the crosstab and its parent.

Note icon A page report can use either query resources or business views, which is determined at the time when you create the page report by the Create Using Business View option. Once defined, all the data components in the page report can only use the specified data resource type.

This topic contains the following sections:

Creating a Crosstab Based on a Business View
Creating a Crosstab Based on a Query Resource
- Example of Creating a Compound Crosstab

Creating a Crosstab Based on a Business View

Position the mouse pointer at the destination where you want to insert the crosstab.
Do one of the following:
- Drag the Crosstab icon from the Grid category of the Components panel to the report.
- Select Insert > Crosstab.
- Select Home > Insert > Crosstab.
Designer displays the Create Crosstab dialog box, which contains a set of screens for helping you define a crosstab easily. You can use the Back and Next buttons or select the screen name on the screen navigation bar to switch between the screens.
In the Data screen, select the business view in the current catalog using which to create the crosstab.
If you have specified to insert the crosstab into any of the following panels in a banded object: banded header panel, banded footer panel, group header panel, or group footer panel, Designer displays the Inherit from the Parent option. Select it if you want the crosstab to inherit the business view that its parent banded object uses.
In the Display screen, specify the fields to display in the crosstab. You can specify a title for the crosstab in the Title text box.

The Resources box lists the view elements in the specified business view and the dynamic formulas and aggregations that you have created for the business view in the current report. You can use these objects to create the crosstab.
- In the Columns and Rows boxes, add group objects or dynamic formulas used as Group as the column/row fields to display on the column and row headers of the crosstab. You can add an object using either the button beside the target box or by dragging it from the Resources box to the target box.
  For each column/row field, you can double-click in the Label text box and type a name to label the corresponding column/row header, or select the Auto Map Field Name checkbox in the text box if you want to automatically map the label text to the dynamic display name of the field at runtime (the Label text box is blank by default so the crosstab shows no label for the column/row header); double-click in the Color text box to specify its background color (to make the color take effect, you need to edit the Background property of the field to "Transparent" in the Report Inspector after you finish creating the crosstab); select the Sort button to change the sort manner of its values between Ascend, Descend, and No Sort. To adjust the display order of the column/row fields on the column/row headers, select a field and select the Move Up or Move Down button; to delete an unwanted column/row field, select it and select the Remove button or drag and drop it to the Resources box.
- In the Summaries box, add aggregation objects , detail objects , dynamic formulas used as Aggregation , dynamic formulas used as Detail , or dynamic aggregations as aggregate fields to create aggregations in the crosstab. You can add an object by either selecting or dragging it from the Resources box to the Summaries box. When you add a detail object as the aggregate field, double-click in the Aggregate text box to specify the aggregate function for it. If you select DistinctSum, you should select the ellipsis button in the Distinct On text box to specify one or more fields according to whose unique values to calculate DistinctSum using the Select Fields dialog box.
  For each aggregate field, you can double-click in the Label text box and type a name to label the corresponding aggregations in the crosstab, or select the Auto Map Field Name checkbox in the text box if you want to automatically map the label text to the dynamic display name of the field at runtime (the Label text box is blank by default so the crosstab shows no label for the aggregations); select the Comparison Function button to define a comparison function for it. To adjust the display order of the aggregate fields, select a field and select or ; to delete an unwanted aggregate field, select it and select or drag and drop it to the Resources box.
  
  An aggregate field can generate detail aggregations, aggregations for subtotals, and aggregations for grand totals for each combination of the column and row fields.
In the Filter screen, apply a filter to reduce the data to display in the crosstab. You can select a predefined filter of the specified business view from the Filter drop-down list to apply, or select User Defined in the list to define a new filter as required.
In the Layout screen, specify the layout properties of the crosstab.
In the Style screen. specify the style of the crosstab.
If you have specified to insert the crosstab into a banded object, by default, the crosstab inherits its parent's style; to apply another style to the crosstab, clear the Inherit Style option and then select the required style from the Style box.
Select Finish to insert the crosstab in the report.
If you have selected a panel in a banded object as the crosstab destination, after finishing the dialog box, you need to select the mouse button in the destination once again in order to insert the crosstab there.

Creating a Crosstab Based on a Query Resource

Using query resources, you can create compound crosstabs. A compound crosstab contains multiple crosstabs that are mashed up together in a flexible way. You can create aggregations based on any combinations of the row and column compound groups, making more complex analysis possible.

Position the mouse pointer at the destination where you want to insert the crosstab.
Select Insert > Crosstab or Home > Insert > Crosstab.
Designer displays the Create Crosstab dialog box, which contains a set of screens for helping you define a crosstab easily. You can use the Back and Next buttons or select the screen name on the screen navigation bar to switch between the screens.
In the Data screen, select the data resource in the current catalog using which to create the crosstab.

If the predefined data resources are not what you want, you can select the first item in the corresponding resource node to create one in the current catalog to use. When you select a query, you can select the Edit button to modify the query. Designer then automatically creates a dataset based on the selected data resource in the page report.

If you want to use an existing dataset in the current page report to create the crosstab, select the More Options button and then:
- Select the Existing Dataset radio button and select a dataset. You can select the Edit button to modify the specified dataset in the Dataset Editor dialog box, or select the <New Dataset...> item to create a dataset in the page report to use. It is always better to use an existing dataset rather than create a new one. Even when the two datasets are based on the same query, Logi Report Engine still runs the query separately for each dataset.
- If you have specified to insert the crosstab into an object that already applies a dataset, such as a banded panel, Designer enables the Current Dataset radio button. Select it if you want the crosstab to inherit the dataset from the parent object.
In the Display screen, specify the fields you want to display in the crosstab.

The Resources box lists the DBFields in the specified data resource as well as the formulas that are valid to these DBFields in the current catalog. You can create the crosstab using these fields. If the predefined formulas cannot meet your requirement, you can select <New Formula...> in the Formulas node to create the formulas you want.
- In the Columns and Rows boxes, add column/row fields to display on the column and row headers of the crosstab. To add a column/row field, select a DBField or formula in the Resources box and select the button beside the target box, or drag and drop it from the Resources box to the target box. If you want to display compound column/row groups in the crosstab, select the Add Compound Group button at the right bottom corner of the Columns/Rows box to create them, then select each compound group and add the required fields to it.
  For each column/row field, you can double-click in the Label text box and type a name to label the corresponding column/row header (the Label text box is blank by default so the crosstab shows no label for the column/row header); double-click in the Color text box to specify its background color (to make the color take effect, you need to edit the Background property of the field to "Transparent" in the Report Inspector after you finish creating the crosstab); select to change the sort manner of its values between Ascend, Descend, and No Sort. To adjust the display order of the column/row fields on the column/row headers, select a field and select or ; to delete an unwanted column/row field or compound group, select it and select or drag and drop it to the Resources box. You can also adjust the display order of the compound groups by selecting a compound group and selecting or ; to delete a compound group, select it and select .
- In the Summaries box, add aggregate fields to create aggregations in the crosstab. To add an aggregate field, select a DBField or formula in the Resources box and select or drag and drop to the Summaries box. You can also select <New Crosstab Formula...> in the Crosstab Formulas node to create crosstab formulas to use as the aggregate field. When you add a DBField or formula as the aggregate field, double-click in the Aggregate text box to specify the aggregate function for it. If you select DistinctSum, you should select the ellipsis button in the Distinct On text box to specify one or more fields according to whose unique values to calculate DistinctSum using the Select Fields dialog box. If you have created compound column and row groups in the crosstab, you can add aggregate fields for each combination of the compound groups by selecting a row/column group and a column/row group and then adding the required fields.
  For each aggregate field, you can also double-click in the Label text box and type a name to label the corresponding aggregations in the crosstab (the Label text box is blank by default so the crosstab shows no label for the aggregations); select the Comparison Function button to define a comparison function for it. To adjust the display order of the aggregate fields, select a field and select or ; to delete an unwanted aggregate field, select it and select or drag and drop it to the Resources box.
  
  An aggregate field can generate detail aggregations, aggregations for subtotals, and aggregations for grand totals for each combination of the column and row fields.
In the Filter screen, add filter conditions based on the fields that have been added to the crosstab to reduce the data. Select here for how to define a filter.
In the Layout screen, specify the layout properties of the crosstab.
In the Style screen, specify the style of the crosstab.
If you have specified to insert the crosstab into a banded object, by default, the crosstab inherits its parent's style; to apply another style to the crosstab, clear the Inherit Style option and then select the required style from the Style box.
Select Finish to insert the crosstab.
If you have selected a panel in a banded object as the crosstab destination, after finishing the dialog box, you need to select the mouse button in the destination once again in order to insert the crosstab there.

Besides using the wizard, you can also drag a blank crosstab to a page report that is created using query resources. To do this:

From the Components panel, drag the Crosstab icon in the Grid category to the destination in the page report which allows the insertion of a crosstab. Designer creates a blank crosstab.
In the Data panel, select the dataset in the current page report with which you want to create the crosstab from the dataset drop-down list, or select <Choose Data from...> from the list to create a new a dataset for the crosstab.
Drag the required fields from the Data panel to create the column headers, row headers and aggregations in the crosstab.

Example of Creating a Compound Crosstab

Make sure SampleReports.cat is the currently open catalog file. If not, select File > Open Catalog to open it from <install_root>\Demo\Reports\SampleReports.
Select File > New > Page Report.
In the Select Component for Page Report dialog box, select Crosstab and select OK. Designer displays the Crosstab Wizard dialog box.
In the Data screen, select the query WorldWideSales in Data Source 1 of the catalog.
In the Display screen, select in the Rows box and Designer adds two compound row groups in the box.
Select Row Compound Group, drag and drop the formula year and the field Category in the Products table from the Resources box to the compound row group one by one, double-click in the Label text box of each field and type Year and Category to label the row headers.
Select Row Compound Group 1 and add the field Country in the Customers table to it and edit its label to Country.
Select in the Columns box, then add the formula Quarter to Column Compound Group and the field Order ID in the Orders table to Column Compound Group 1, specify their labels as Quarter and Order ID.
Select Row Compound Group in the Rows box and Column Compound Group in the Columns box, drag and drop the field Quantity in the Orders Detail table from the Resources box to the Summaries box as the aggregate field of the compound groups. Double-click in the Aggregate text box and select Sum from the drop-down list that appears, then double-click in the Label text box and type Quantity to label the aggregations.
Repeat the above step to add the following fields with the specified aggregate functions as the aggregate fields for the combinations of the following compound groups. Use the fields' display names as the labels.
- Row Compound Group and Column Compound Group 1: Price, Average
- Row Compound Group 1 and Column Compound Group: Cost, Sum
- Row Compound Group 1 and Column Compound Group 1: Unit Price, Average
Switch to the Filter screen and specify the filter conditions as follows:
Select Finish to create the crosstab.
In the Report Inspector, select Label, Label 1, QUARTER, Label 4, Label 5, YEAR, Label 6, Label 7, CATEGORY, Label 10, Label 11, Label 12, QUANTITY, QUANTITY 1, QUANTITY 2, QUANTITY 3, QUANTITY 4, and QUANTITY 5 at the same time by pressing the Ctrl key on the keyboard, specify the Background property to Lightgray.
Repeat the above step to specify the Background property of Label 2, Label 3, Order ID, Label 15, Label 16, Label 17, PRICE, PRICE 1, PRICE 2, PRICE 3, PRICE 4, and PRICE 5 to Pink; specify the Background property of Label 8, Label 9, COUNTRY, Label 13, Label 14, COST, COST 1, COST 2, and COST 3 to Orange; specify the Background property of Label 18, Label 19, UNIT PRICE, UNIT PRICE 1, UNIT PRICE 2, and UNIT PRICE 3 to Gray.
Save the report.
Select View > Preview As > Page Report Result. You can see the crosstab contains four parts, showing different summary information for different combinations of row compound groups and column compound groups.

Previous Topic Next Topic