Advanced Crosstabs for Studio Reports

Here we will discuss some more imaginary examples where crosstab of Intellicus can be stretched to its limits. This chapter lists examples where summary fields are actually having character fields and even having multiple fields, use of conditional formatting as well as display field, etc.

Use of character field as summary field

This example represents availability of employees in shift schedule in a hospital:

Figure 35: Character Field as Summary Field

The names in bold letters are all over in-charge of the department.

All you need to have to get this report, is a record-set having fields:

• Department

• Shift

• EmployeeName

• InCharge (a field indicating whether the employee is an in-charge or not)

The record-set should be ordered by Facility.

General steps in studio would be:

1. Set the appropriate connection, create an SQL and refresh fields.

2. Create condition and format to be applied for department In-charge.

3. Place crosstab component preferably on Report Header.

4. Place Department on row header. Set its alignment as Center.

5. Place Shift on column header.

6. Place EmployeeName in “summary field”.

7. Apply conditional formatting on summary field (Conditions, formats and conditional formats needs to be set in advance).

8. Set background color for row header, column header and summary field.

Use of multiple summary fields

You may need to give multiple pieces of information within one column.  For example, this crosstab shows planned expenditure versus actual expenditure made on Services.

Figure 36: Shows Expenditure on Services

To get this crosstab, you need to get following fields in the record-set:

• ExpenditureCode

• ExpType

• PlannedExp

• ActualExp

• Branches (US, NZ, AU)

The SQL query should get Services related records only.

General steps to create this crosstab in Studio would be:

1. Set the appropriate connection, create an SQL and refresh fields.

2. Place crosstab component preferably on Report Header.

3. Place Branches on row header. Set its alignment as Center.

4. Place ExpType on column header.

5. Place PlannedExp in “summary field”.

6. Also place ActualExp in “summary field”.

7. Apply conditional formatting on summary field.

8. Set background color for row header, column header and summary field.

9. Hide column summary field and row summary field.

Different summary functions on summary field and row summary

Here is an example of crosstab showing number of staff in different departments and their role.  The row total and column total fields have total expenditure on salary.

Figure 37: Shows Expenditure on Salary

This is what we have done here:

Placed two fields as summary fields: EmployeeCode (Count) and Salary (sum).

Hide summary field Salary and hide row summary field EmployeeCode and column summary field EmployeeCode.

Record-set for this crosstab should have following fields:

• DeptCd

• RoleCd

• EmployeeNo

• Salary

General and essential steps to create this crosstab would be:

1. Set the appropriate connection, create an sql and refresh fields.

2. Place crosstab component preferably on Report Header.

3. Place DeptCD as column header.

4. Place RoleCd as row header.

5. Place EmployeeNo as summary field. Set its summary function as Count.

6. Place Salary as summary field. Set its summary function as Salary.  Set its Visible as clear (unchecked).

7. Select row summary field EmployeeNo and set its Visible as clear (unchecked).

8. Select column summary field EmployeeNo and set its Visible as clear (unchecked).

Multiple values in Summary field

The following example is a crosstab report showing product sales summary by region.

This report has two fields in summary field: Name of sales executive, sales figure and a sign indicating the figure is below target (a negative sign) or above target (positive sign).

Figure 38: Multiple Value in Summary

To get this report the record-set needs to have fields:

• ProductCode

• ZoneCode

• SalesRepName

• TargetedSales

• ActualSales

Here are the general steps to get this report:

1. Set the appropriate connection, create an SQL and refresh fields.

2. Create a character type formula (for example ‘M_Sign’ that gets value ‘–‘ if TargetedSales is more than ActualSales and gets the value ‘+’ if TargetedSales is less than ActualSales. This will be a logical formula written using java script syntax.

3. Create a character type formula field (calculated field) that should concatenate SalesRepName, ActualSales (you need to convert it in character), M_Sign.

4. Place crosstab component on Page Header.

5. On crosstab, place ZoneCode on row header, ProductCode on column header and M_Sign on summary field.