Creating a new empty Information Centre report
So let's start building a new report. At first go to the
HRT desktop. In the services menu
which is accessible via the small silver wheel in the icon bar on the
upper right side of the window, you find a link to the HRT Information Center:
After having pressed the Information Centre link, the main page will be opened in a new window. Go
to the newly opened window and choose the left most button above the
report list in the button bar:
Now you have the possibility to specify a name and a description for the newly created report. Enter a meaningful
name (e.g. "Absences today") and a description matching the report's actual purpose. When
providing names and descriptions to Information Centre reports make sure that they always match the report's
purpose and that they help you to easily discover the desired report if you have defined a lot of them. After having
entered the name and the description, click the "OK" button to confirm the
Start filling the report with data
At this point we have created a report, but it is still empty and not executable. We have not yet specified
what the report should actually do. In the upcoming steps, we will open the report in edit mode, add the tables
that are necessary to retrieve the absence data and specify the columns that should appear in the the report's
output (i.e. in the email containing your organic unit's absences).
First select the newly created report on the main page by clicking at its name. Then choose the
button from the button bar at the top of the report list to open up the editor in a separate window
(you can also use the entry "Edit" from the context menu which appears
when right clicking your report):
The first step in assembling the report is to find the right tables serving as sources for the data to be
displayed. In this specific example we need information about people
currently being at CERN and their leaves registered at CERN. In order to add tables to the report, go to the table manager and
drop the tables "Leave/Absence taken" (LeaveHist) and "Persons
(Current)" (Persons) to the list of selected tables:
The tables are now added to the report and we can continue adding the necessary columns.
We do not have to take care about how the tables are correctly joined
together. This is done automatically. To add columns, click the button
"Add Multiple Columns" (1) and select the following columns by moving them
from the left hand side list to the right (2) and then confirming with "OK"
Let's select the following columns:
- Persons.FullName - contains the full name of the person being absent
- LeaveHist.Std - contains the first day of the registered absence
- LeaveHist.End - contains the last day of the registered absence
When all the columns are selected, the list of selected columns
should look as in (4) of the above screenshot.
Adding detail information by using free expression columns
At CERN it is possible to take leaves lasting only half a day. That is the reason why our report should
be able to display whether the leave starts or ends in the morning or in the afternoon. This data is contained
in the column 'LeaveHist.StdTime'. We could have added the column directly but since it only contains a
'P' for afternoon and an 'A' for morning, it is preferable to provide more meaningful information to the
report recipients by customising the output. To add a first free expression column, click the button
'Add Free Expression Column'.
Now a label (2) and an expression (3) need to be specified. As label, you may select 'Start Time' and the expression
has to be:
decode( LeaveHist.StdTime, 'P', 'Afternoon', 'Morning' )
The expression uses ORACLE's decode function which in this case looks whether the contents of the column
LeaveHist.StdTime equals 'P'. If this is the case, then the 'P' is replaced by the word 'Afternoon'. Otherwise,
if it doesn't equal 'P', the word 'Morning' is returned. Once you are ready, confirm your changes by clicking
At the moment, the start time of the leave would not appear next to
the start date. Just move the column up using drag/drop so that it
appears right after the column "LeaveHist.Std".
Now we will repeat the same procedure to add the end time to the report as well. To do so, please select
'Add Free Expression Column' again. Specify a label (e.g. 'End Time') and type in the following expression
in the expression field:
decode( LeaveHist.EndTime, 'A', 'Morning', 'Afternoon' )
As for the start date time, this expression looks if the contents of the LeaveHist.EndTime column equals
'A' and if this is true, returns the word 'Morning'. Otherwise the word 'Afternoon' will be used.
In the end, your list of selected columns should look like this:
By the way, when hovering over the free expression
columns, the actual expression is shown in a tooltip as only the label
is immediately visible in the list of selected columns.
Adding conditions to limit the shown absences to today and to your group
In its current form, the report is not usable because all leaves registered at CERN
would be printed out (supposing you have the necessary access rights to really see all of them). Therefore
at least two conditions limiting the number of results have to be applied to our report.
Firstly, it is necessary to specify for which organic unit we want to see the data. Therefore we have to add
a static condition. To add one, drag the column "Organic Unit" from the
column selector onto the list of static conditions (1). When done, we
have to modify the operator and have to specify for which organic unit
we want to see the leaves. This is done by pressing the pencil button
We want to apply a condition to only show the data for a certain organic unit. That's why we choose the
column 'Persons.OrgUnit'. As operator we select 'like' in order to also include all the people that are not
directly attached to e.g. GS-AIS but also to the group's sections. Finally it is necessary to give the name
of the unit we want the absence data for. In this example we are using 'GS-AIS'. Obviously, you should replace
this unit by the unit you are in or responsible for. Once, you are ready, confirm the new column by pressing
After having specified the organic unit we want to see the leave data for, we also have to specify that
only those leaves concerning people that are absent today are shown. To do so we add another static condition
to the report. The condition we are going to add is a bit more sophisticated and therefore it is necessary
to create the condition in free SQL mode. So, click on "Add Free
Expression Condition" and enter the following condition:
to_char( sysdate, 'YYYY.MM.DD' ) between to_char( LeaveHist.Std, 'YYYY.MM.DD' ) and
to_char( LeaveHist.End, 'YYYY.MM.DD' )
This expression means that we only want to retrieve leaves if the current date (sysdate) is between the
start (LeaveHist.Std) and the end date (LeaveHist.End) of the leave. All the dates are encapsulated by a 'to_char'
to avoid considering the time information which is also included in the start and end dates.
When the expression is entered, confirm by clicking 'OK' again.
After you have created both conditions, the list of static conditions
should look like the list in (3) of the following screenshot:
Ordering the absences and saving the report
Especially when your organic unit is quite large and there may be a lot of absences in the same time, it is preferable
to order the absences. In this example we decided to order them alphabetically by name. To do the same,
drag the column "Persons.FullName" from the list of selected columns
onto the ordering selector:
Now the report is ready. Do not forget to save it by clicking the disk icon in the button bar on the
top of the report editor. When you are creating more Information Centre reports, it is advisable to save
the report more often than only once it is finished. After having saved the report, you can close the
report editor window.
Scheduling the report
In order to schedule the absences report you have just finished creating go to the Information Centre
main page and select the report by clicking on its name. Then press the 'Schedule' button out of
the tool bar at the top of the main page or choose the "Schedule" entry
from the context menu:
Now, the Scheduling Settings Editor is opened in a new window. You have to supply some basic settings
in order to be able to activate scheduling. First of all, make sure that the settings are active by
verifying that the 'Active' checkbox is checked. Then you have to provide at least one recipient email
address. You can e.g. use the official mailing list where all members of your organic unit that should
receive the report are entered or an expression like "for all in
'GS-AIS' as myself". In order to just test the report, we recommend that for the first couple
of days you send the report only to yourself. Then when everything is fine and the report is in a good
shape you can start sending it to other people.
If you want to send the report to more than one email address, separate the different email addresses
Apart from the recipient email address, all other settings are optional. You may want to specify
a meaningful email subject and an additional text giving further information to the people receiving
the report's results.
It is not possible to change the email address of the report owner. Your personal CERN address is
automatically fetched and inserted in the settings. This email address will be used when there are any
errors or other problems that make a successful report execution impossible. This address will also be
included in the generated email as contact address for the recipients in case they have questions concerning the report itself or its results.
This email will not be used to send you the results of a successfully run report. So unless you include
your email in the recipients list, you won't get the report's results.
When everything is done, click the 'Ok' button to save the settings and to activate the scheduling
of the report. Changing the execution interval is not necessary. By default, your report is scheduled
to be executed on every working day (from Monday to Friday including official holidays).