Pivot Report - Step by Step
In this tutorial we will look at creating a pivot or summary
report. We will create a report that allows to count the people
based on different grouping criteria (the country of the institute
they are affiliated with, the department or the gender). You will
learn how to create a report that offers the possibility to group
data using the standard HRT/CET pivot control.
Step 1. Create a new Report
The first step is to create a new report. Proceed as you were
shown in the first tutorial on the main
documentation page. Let's call this report "Institutes":
When the new report has been created, open it in
edit mode by selecting it in the list of reports and clicking the
pencil icon of the toolbar.
Step 2. Select the Tables we need
For this report we need the Persons table. Please remember that there are two versions
of this table. One that contains only persons currently considered
to be at CERN or at least having some recent contact with CERN
(Persons (Current)), and one that contains everyone ever registered
at CERN (Persons (Full)). Lets choose the latter one for this
We also need the Institutes table. To select them drag them from
the "Table Selector" to the list of selected tables next to it. When
selecting the second table, a join will be automatically created. A
join is a relationship between two (or more) added tables. It
indicates how the lines coming from one table are put in relation
with the lines of the other table. In our case, the link will be
mode using the institute code.
For the most common cases, joins are created automatically.
Nevertheless, there may be occasions in which you have to create the
joins yourself. You can also change a join at any time (regardless
of whether it was created automatically or manually).
At the end of this step, your list of selected tables and joins
should look similar to the above screenshot. Next to the join, you
find a small blue link labelled "Institute". This link indicates
which table is the table "driving" the join. To change it to the
other table, just click the link. At the end, when the report is
finished there must be at least one table that is not linked to any
Step 3. Select the data
We now need to select the data we wish to show or summarise
Remember that in a pivot report we are really generating
statistics so we would like to have several groupings, like people
per country, people per gender etc. and one or more criteria to count
by (for instance the number of people).
Let's start by using the Column Selector to select the ones we would like to be able to group by. For
this tutorial we have chosen the following columns:
This gives us three grouping options that can later be combined.
To select the columns, you can e.g. use the quick column selector
that appears when clicking the button "Add Multiple Columns" in the
list of selected columns.
We also need to have some kind of numeric value to display. What
we would like to do is to count the number of people. A single
person is identified by his Person Id at CERN. So lets choose that.
The column can be selected either by dragging it from the column
selector or by using the quick column selector again:
When all the before mentioned columns are selected, your list of
selected columns will look similar to the following screenshot:
We now have one more task to perform on the selected columns. We did say that we wanted to have the number of people as
the value to present. This implies counting the
people. We need to tell Information Center to do that.
As you can see there are a number of Aggregations to the
right of the column name in the list of selected columns. Clicking on the word
COUNT will highlight this word and you can also see the Select
Column Change slightly. This means that we have now asked
Information Center to perform a count of the number of Person Id's
(people) that the query will return.
Your list of selected columns should now look like this:
Step 4. The conditions
In order to limit the amount of data returned, we specify two
dynamic conditions. Dynamic conditions will result in search fields
that are generated in the report form once it is executed. Let's
create two search fields, one for the organic unit and another one
for the person status. For both of them, just drag the corresponding
columns from the column selector on the left hand side to the list
of dynamic conditions on the right hand side:
Once the conditions are added, they appear both in the list (see
figure 1 in the below screenshot). At first, both conditions are
created using a LIKE operator. Such an operator allows to check for
partial matches in the specified column. For the person status we do
not need partial matching and would like to use the better
performing equality operator. To change the operator, click on the
small pencil icon next to the "Status Code" dynamic condition (see 1
A small dialog will be opened. Within the dialog you can change
the label which appears in front of the generated search field and
the used operator. Choose the "equality" operator (2) and close the
dialog with "Ok". The editor will now reload and the chosen operator
will be displayed next to the column name (3).
Step 5. Make it a pivot report
Now, it is time to make the report a pivot report. To do so, tick
the checkbox "Generate a Pivot Report" in the general report
settings area on the bottom side of the left most editor column:
Step 5. The Groupings
To make the report run-able, all the columns not having an
aggregation must be selected as grouping columns:
In order to select grouping columns, just drag the first three
select columns (all the columns not having an aggregation) over to
the list of group columns. Next to each selected group column, two
layout options (X and Y) appear. These layout options indicate which
group columns should be selected by default (when the report is run
without opening the "Grouping Layout" selector) and on whether they
should be used as row (Y) or column groupings (X). In order to make
the report executable, at least one group column must be marked as
having a "Y" layout. To select a layout, just click the blue "x" or
When finished, the list of group columns should look like on the
right hand side of the above screenshot.
Step 7. Run it
You're report is now ready to be executed. Just click on the run
icon at the top of the report editor (the icon with the two wheels
on it). Your report is now ready for execution. By default, the only
group column which is used is the country as it is the only one
marked with a layout in the report editor.
In order to change the grouping columns, just click the button
"Grouping Layout". There by default, you only see "Country" in the
row groupings area. The two other columns are nevertheless available
and can be dragged to row or column groupings and will be used for
grouping once the report is re-executed.
Of course there are many more enhancements that can be done to
this report. The report will, for instance, count anyone that has
ever been at CERN and is attached to a institute, it will count
people attached to inactive institutes, etc. This can be changed by
adding additional static or dynamic conditions. Discovery of these is left as an
exercise to the reader since you have reached the end of this tutorial.