This tutorial uses information from the Training table in HRT. If you normally do not have access to training data you can not follow this tutorial on your own computer. It also assumes some familiarity with training data as well as SQL knowledge.
The aim of this tutorial is to create a report that
can list people that have followed a specific course but not followed another specific course. This report will give the answer
to questions like "Who has followed Java 1 but not Java 2?".
Step 1. Create a new Report
Our 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 "Training List".
Step 2. Select the Tables we need
As in the previous tutorial, select the required tables by
dragging them from the table selector to the list of selected
tables. For this report we need two tables. First we need the "Persons"
table just as in the previous report. We also require the
"Training" (labelled "Training Enlisted For" in the table
Once you have selected more than one table an entry will show up
in the list of table joins. The database needs to know how the two
tables relate to each other in order to retrieve the data.
Information Center automatically creates a join between both tables
linking them by using the Person Id.
If you add other tables and a join is not created automatically,
please do not forget to create a join manually by dragging the
columns to be joined onto the "Table Joins" component. In this
specific example, this is not necessary.
Step 3. Select the data
Just as in the previous tutorial, we need to select the columns
we want to extract. Let's select Persons.FullName from the Persons
table and Training.Title, Training.Year and Training.EdhDoc from the
Training table using the "Quick Column Selector" which is accessible
using the blue link "Add Multiple Columns" in the list of selected
Step 4 part 1. Add the Filters (Conditions)
So, what do we want to search for? The course code of
course and perhaps the organic unit of the attendees. So, let's add
those conditions. Proceed as described in the main tutorial and drag
the columns Persons.OrgUnit (Organic Unit) and Training.CourseCode
(Course Code) from the column selector to the list of dynamic
conditions. By default, the used operator will be set to "LIKE" so
that there is nothing more to do in this step.
Your conditions should now look like in the following screenshot:
At this point you can already execute this report if you so wish.
It will however not yet answer the question who has followed course
A but not course B. To find that answer, we have to continue with
the next step.
Step 4 part 2. Add the "not" Filter (Conditions)
We said that we wanted to make a report that showed people that had
not attended a course a specific course, too. In order to do this it
is necessary to apply
a second criteria on the course code. To apply multiple dynamic
conditions on the same column, we need to include the same table
twice into our report.
Go to the table selector and drag the Training table to the list
of selected tables once more.
When you have added it you will see that it has now been given the
name "Training1". Two tables can not have the same name so when you
add the same table twice, Information Center will give it an "alias".
You can change the alias by clicking on the
icon to the right.
We change the alias of the table to "TrainingNotAttended" to make
it a bit more clear what this table is used for.
Now we need to do two more manipulations. First of all, we intend
to use this second copy of the Training table for filtering only. We
do not intend to show any data from it. In order to do this we must
make it invisible. By default all tables are "Visible" (this status is
shown to the right of the table names). Click on the word "visible"
for the TrainingNotAttended table and it will switch to "hidden".
Information Center also automatically added a join between
the Persons table and the second Training table. Although this is
normally correct, in this particular case we do not want it. Remove
it by clicking on the
to the right of the join involving TrainingNotAttended and Persons.
Now we are ready to add the additional condition itself.
Start by adding a Dynamic Condition as you did earlier for the
first two conditions. Drag the column "Course Code" from the table "TrainingNotAttended"
to the list of dynamic conditions. Now, we want to enter our own
specific SQL expression here in order to achieve the NOT part of the
query. Do this by editing the condition and switching it to "Free
SQL Expression Mode". Do not forget to also change the label so that
it is obvious what to put in the different search fields once the
report is executed.
We want to write a condition that excludes
everyone that has actually followed the course that was entered in
the form for this field. For this tutorial we have chosen the
not exists ( select 'Yes' from TrainingNotAttended where
TrainingNotAttended.Pid = Persons.PIdn and
TrainingNotAttended.CourseCode like indata || '%' )
This is a normal SQL expression with one exception. Notice the
word "indata" in the expression. This is a place holder where the
text that the user entered in the form at the time of execution will
be injected. Notice also that you can refer to any of the columns in
the tables you have added to your report.
Your report is now executable.
Step 5. Enhance the Request
There is one problem with this report. The Training table
contains all training registered at CERN regardless of whether the
participant has actually followed the course, is still attending
or waiting for it or whether the course was even cancelled.
Below, we will add a static condition in order to limit the shown
results to those course that have actually been attended.
In the Training table there is a column called Status. This
column contains the status (Attended, Waiting, Cancelled, etc) of the
course. We can add a static condition in our report that ensures
that only courses that have been attended are considered.
Drag the "Status" column from the column selector to the list of
static conditions and then edit it by clicking the pencil icon to
its right to make it look like in the following screenshot:
The first half of the problem is now solved. There is a condition on the course we want to check whether it has not
been attended. In the underlying free SQL expression, we would like
to make sure that only attended courses are considered, too.
Simply click on the edit icon next to the dynamic condition on
the column "TrainingNotAttended.CourseCode" and add the same condition to
the existing SQL expression. When you have finished it should look like this:
( select 'Yes' from TrainingNotAttended where
TrainingNotAttended.Pid = Persons.PIdn and
TrainingNotAttended.CourseCode like indata || '%' and
TrainingNotAttended.Status like 'Attended%')
Do not forget to
add the percent at the end of 'Attended' since it is a like
expression. For simple dynamic and static expressions, Information
Center will add this automatically but for free SQL expressions you
have to remember every detail yourself.
You might wonder
why we need a like expression, this is simply because the status field
contains more text, for example 'Attended (100%)', etc.
This of course
leads us to a further problem. What if someone attended to only 20%?
Should the report display this person? Well, there is a Percentage
field in the Training table as well. So you could add a condition on
this field to further enhance this report, but as far as this
Tutorial is concerned, this is as far as we will go. Go
ahead and try out your report.