Usage of Crosstab Component in canned Reporting

In Open source BI Helical Insight versin 6.1.0.826, the Crosstab component has been introduced in the Canned Report module to display summarized data in a matrix/pivot format. This component allows report designers to organize query results into row and column groups, apply aggregate functions on measures, and generate comparative and analytical views, making it easier to analyze data across multiple dimensions within report.

Steps to create a cross tab visualization in Canned Reporting :

1. Create a data source connection

   Refer the following documentation for this step https://www.helicalinsight.com/data-sources-page-overview-in-canned-reporting/

2. Once the datasource connection is created, select the connection and write the SQL query. Click on Save and Run to execute the query

   The first 10 records of the result set will be displayed in the Data Preview section, this is helpful to understand whether the data being fetched from the SQL is correct or not.

   

3. In the CANVAS tab, you will find the Cross Tab component under the Elements list

   

4. Drag the Cross Tab component onto the canvas, then right-click on it and select the ‘Repeat‘ option as neccesary.

   Mostly ‘By Report‘ will be used.

   

5. Click on the Cross Tab component.

   On the right side, you will see the Cross Tab configuration options/Properties

   

   1. Query
      • Purpose: Specifies the data source for the cross.
      • How it works: While making the datasource connection, we have written query. We can write multiple queries also there. You can select a query (like Query1) which will be used for the table.
   2. Select Fields

      Please select the columns from the query that you want to display in the crosstab.

   

   3. Select Columns

      Select the fields to be used as crosstab columns

   4. Select Rows

      Select the fields to be used as crosstab rows

   5. Select Measures

      Specifies the numeric fields and aggregation functions (such as sum or count) used to calculate values in the crosstab cells.

   6. Measure Aggregate Functions

      Defines how measure values are aggregated across records, such as sum, average, count, minimum, or maximum.

      Select the Aggregate to be used for the selected measure/measures.

      

      We have selected the following fields to generate the crosstab :

      

      • Column Break Offset

        Defines the space (in pixels) between column groups when the crosstab continues into multiple columns.

      • Position :

        Specifies the object position when the report section is affected by stretch.

        • FixRelativeToTop: Crosstab stays fixed relative to the top of the report.
        • FixRelativeToBottom: Crosstab stays fixed relative to the bottom.
        • Float: Crosstab moves freely and can be placed anywhere.

        

   7. Stretch
      • ContainerBottom

        The crosstab expands downward until it touches the bottom of its container (such as a frame or band).

        Useful when you want the text to fill all available vertical space inside its container.

      • ContainerHeight

        The crosstab always matches the full height of its container.

        The height stays the same even if the text content is small or large.

      • ElementGroupBottom

        When the crosstab is inside a group, it stretches until the bottom of the entire group.

        Useful when grouped elements need to align at the bottom.

      • ElementGroupHeight

        The crosstab stretches to match the total height of its group.

        Helps keep all grouped elements the same height and maintain a consistent layout.

        This is usually the most commonly used option.

      • NoStretch

        This is the default behavior.

        The crosstab does not stretch, even if the content is larger than the available space.

      

   8. Print When Expression

      This property is used to display a specific crosstab only when a given condition is true.

      If the condition evaluates to false, the crosstab will not be printed in the report.

      Example

      (($F{meet_cancellation_status}.equals(“Yes”)))

      In this example, the crosstab cell will be printed only when the meet_cancellation_status field value is “Yes”.
      If the value is anything else, the cell will be hidden.

   9. Print Repeated Values – Controls whether repeated values are printed or left blank.
   10. Remove Line When Blank – Removes the crosstab row if there is no data for that row.
   11. Print in First Whole Section – Prints the crosstab only at the start of a section (useful in multi-section reports).
   12. Print When Record Overflows – Controls printing when the crosstab spans multiple pages or sections.
   13. Stretch with Overflow – Lets the crosstab grow if the content is larger than its allocated area.
   14. Blank When Null – If a field is NULL, it shows as blank instead of printing NULL.
   15. Borders – Configure various properties of the border, like border style, width and color.

   

   16. Padding – We can provide the padding values, thus the data within the cells will appear leaving that much space (padding) on side.

   

6. Select required fields from dataset to crosstab and ajust the cross tab position by giving proper width and height



Preview the report (preview option is present at the top blue band).



7. We can rename cross tab static field names and apply field level properties.
8. Save the report and open in new window



9. Once a crosstab is created, we may want to specify the individual cell level properties like thousand separator, alignment, bold, font size etc.

   For doing that, you can click on that individual cell only and then apply the respective properties. We can rename table header field names and apply field level properties. Click here to read more.

Adding Multiple crosstabs in the report :

• Each Crosstab component requires a dedicated query. A single query can be bound to only one crosstab, therefore, a separate SQL query must be created for every additional crosstab used in the report.



• Just as we create Crosstab 1, we should create Crosstab 2 in the same way and align it properly on the canvas.

Important crosstab properties that should be applied to avoid overlap issues:

• We should apply Position > Float for Crosstab 2

We can see 2nd crosstab is present at page no 6.

Note : When working with multiple crosstabs, apply the Position Float property starting from the second crosstab onwards.
If any text fields are placed between crosstabs, the Position Float property should also be applied to those text fields.

If crosstab issues still persist, enable the Repeat by Summary option to ensure proper rendering.

Crosstab with Parameters :

• There is no separate or additional process required to add parameters for an individual crosstab or for multiple crosstabs.
• Parameters are created once and can be mapped to multiple queries, allowing the selected parameter values to be applied to their respective crosstab.

Refer to the documentation below for detailed steps on adding and configuring parameters.
https://www.helicalinsight.com/input-parameters-filter-types-canned-report/

Reach out on support@helicalinsight.com