Advanced Crosstabs for Studio Reports

Applies to: Kyvos Reporting

This section explores advanced examples that push the boundaries of crosstab capabilities in Kyvos Reporting. These examples illustrate how you can:

• Use character fields as summary fields.

• Display multiple summary fields in the same cell.

• Apply conditional formatting.

• Combine different summary functions.

• Concatenate multiple values in a single summary field.

Use Case 1: Character Field as Summary Field

Scenario:
Display employee availability by shift and department. Department in-charges should appear in bold.

Required fields in the recordset:

• Department

• Shift

• EmployeeName

• InCharge (Boolean or flag indicating in-charge status)

Steps:

1. Create an SQL query that retrieves and sorts the recordset (e.g., by Department or Facility).

2. Create a condition (e.g., InCharge = true) and corresponding format (e.g., bold).

3. Insert a crosstab on the Report Header.

4. Set:

   • Row header: Department (Center-aligned)

   • Column header: Shift

   • Summary field: EmployeeName

5. Apply the conditional format to the summary field.

6. Optionally, apply background colors to headers and summary cells for enhanced readability.

Use Case 2: Multiple Summary Fields

Scenario:
Show both planned and actual expenditure across branches and expenditure types.

Required fields in the recordset:

• ExpenditureCode

• ExpType

• PlannedExp

• ActualExp

• Branch (e.g., US, NZ, AU)

Steps:

1. Write a query to fetch only service-related records.

2. Insert a crosstab on the Report Header.

3. Set:

   • Row header: Branch (Center-aligned)

   • Column header: ExpType

   • Summary fields: PlannedExp, ActualExp

4. Apply conditional formatting as needed.

5. Hide row and column summary totals if not required.

Use Case 3: Different Summary Functions on Data and Totals

Scenario:
Display staff count by role and department. Show total salary in row and column summaries.

Required fields in the recordset:

• DeptCd

• RoleCd

• EmployeeNo

• Salary

Steps:

1. Prepare the SQL query.

2. Insert a crosstab on the Report Header.

3. Set:

   • Row header: RoleCd

   • Column header: DeptCd

   • Summary fields:

     • EmployeeNo (Set summary function to Count)

     • Salary (Set summary function to Sum; mark Visible as unchecked)

4. Hide:

   • Row summary for EmployeeNo

   • Column summary for EmployeeNo

This setup allows you to show the count in the main matrix and the salary totals in the margins.

Use Case 4: Multiple Values in a Single Summary Field

Scenario:
Show sales rep name, sales amount, and a symbol (±) indicating performance versus target.

Required fields in the recordset:

• ProductCode

• ZoneCode

• SalesRepName

• TargetedSales

• ActualSales

Steps:

1. Write an SQL query to fetch the required data.

2. Create a calculated formula (M_Sign) using JavaScript syntax:

   if (ActualSales >= TargetedSales) return "+"; else return "-";

3. Create a second calculated field that concatenates:

   • SalesRepName

   • Converted ActualSales (to string)

   • M_Sign

4. Insert a crosstab on the Page Header.

5. Set:

   • Row header: ZoneCode

   • Column header: ProductCode

   • Summary field: Concatenated field

These advanced examples showcase the flexibility of Kyvos Reporting Crosstab reports in addressing complex reporting needs. You can combine textual summaries, conditional formatting, calculated fields, and multiple summary functions—all within the same component.