How to create more powerful Chris21 reports using complex selections
Your Chris21 reports will only be as good as the selections you make. Selections tell the report designer what to include in the report output. If your selections are wrong, your report will be wrong.
In many cases, the selections are quite simple. For instance, creating a report to show all employees who started after a specific date.
Of course, not all Chris21 reports are this straightforward. Some can be complex and it can take some thought to make the correct selections.
There are just three connections that can be used in your report selections, AND, OR and BOR. Let’s have a look at a few examples of how and when to use each.
Use the AND connection when two or more conditions must met:
If you want to include employees who are males and who are under 35 years of age:
Gender Equals Male AND
Age Less Than 35
Output: All males who are under 35
Use the OR connection when either of two or more conditions must be met:
If you want to include employees who are males or who are under 35 years of age:
Gender Equals Male OR
Age Less Than 35
Output: All males, plus all females who are under 35
Use a combination of AND and OR when the first two or more conditions must be met (if connected with AND) or the subsequent conditions must be met:
If you want to include employees who are males and who started after 31/12/1998 and are under 35 years of age, along with employees who are female:
Gender Equals Male AND
Joined Greater Than 31/12/1998 AND
Age Less Than 35 OR
Gender Equals Female
Output: All males who started after 31/12/1998 and are under 35, plus all females.
BOR is essentially a bracketed OR. Use the BOR connection when some conditions must be met and there is a choice between other conditions:
If you want to include employees who are males and who are under 45 years of age or over 59 years old, the following conditions appear to describe the task:
Gender Equals Male AND
Age Less Than 45 OR
Age Greater Than 59
Output: All males who are under 45, plus all employees who are over 59.
The output is incorrect. With these conditions the report will show all males who are under 45, but will also show all employees, male or female, who are over 59. This is clearly wrong as the requirement is for males only.
If you want to include employees who are males and who are under 45 years of age or over 59 years of age:
Gender Equals Male AND
Age Less Than 45 BOR
Age Greater Than 59
Output: All males who are under 45 or over 59.
The output is now correct. The BOR tells the report to select only males but those males must be either under 45 or over 59.
Let’s look at a more complex example:
For budgeting purposes, the Finance Director needs to know how many male employees are in higher classifications in one of his areas of responsibility. The report must show all male employees who work in the following Business Units: Financial Reporting (FCB), Financial Management (FCC) or Financial Services (FCD). Of these employees, only those in Classification ASO6 or greater should be shown.
This would be written in your Report Designer selections as:
Gender Equals Male AND
Classification Greater Than ASO5 AND
Bus Unit Equals FCB BOR
Bus Unit Equals FCC BOR
Bus Unit Equals FCD
You can see how BOR can be used to great effect to produce quite complex Chris21 reports with minimal selections. I would be interested to hear if you have any other examples where you have used BOR to good effect.
Hi Reddy,
If I understand your question correctly, you could put a calculation on the date, eg T-15D. This says give records from today minus 15 days. And the other way would be T+16D.
Tony.
Hi Tony,
Thank you so much for the response. The problem here is that the date is not constant. For example it can be 15-Mar or 16-Mar or 17-Mar. I want to run this report on any day in the month. So doing T-15D & T+16D will not work.
When I run the report on any day in the month, it should get the first day of that month and last day of that month.
I agree to do T-15D & T+16D in case if the date is constant (only 15-March). But the date is not constant.
For example consider 3 dates (10-March, 18-March, 25-March). If I run the report on all of these 3 days then it should get the date range as 01-March to 31-March.
Thank you in advance.
Hi Tony, Thank you for the post.
In Chris21 report designer is there anyway we can get the first day of the month and last day of the month for a date. For example, today is 15-Mar. So is it possible to get the 1-Mar (1st day of the month) & 31-Mar(last day of month)?
if the above is possible then can we use it in the selection criteria against any date field.
Thanks in advance.
I think you are trying to get too much from the Chris21 report designer! It’s quite a basic tool compared to other commercial reporting tools. A couple of options: if you use the database option and store your database in SQL I think you would be able to write queries to do what you want, or an Excel macro will do it for you.
Hi Tony,
I am trying to create a report that will show me all the head counts from 2011 to current and I also include in the report the Division/ Company / Entity / Branch and the gender plus including the terminated staff as well.
Hi Tony
Is there a way to not show the data with no value in a field? For example if we have a small data table below, I want to only show Josh and Amy in the report. I can’t work out how to configure the selection in the report designer.
Name Super Policy Code
Josh REST
Ben (no value)
Amy LURC
Regards
Mark
Hi Mark,
Yes, you should be able to add to Selections: Super Policy Code – Not Equal – (leave the Value field blank)
Regards,
Tony.
Hi Tony,
I am trying to validate information from POS record into another form automatically without been manually inputted into the other form.
What will be the process?
regards,
Anne
Hi Tony,
Is there anyway to total (pivot) annual leave taken (LVE) over the last 12 months and also contain the employees current position details (POS). The report I have set duplictaes the position data (POS) for each date of leave taken. Multiple lines with the same position data for each annual leave booking.
When selecting “summary of totals only” it will total the annual leave taken however removes the POS information.
Hi Tony
I need a report of all active employees as at run date and terminations as at a start and end date. eg active employees included staff terminated last month.
I tried different combinations of the 3 fields below but I either end up getting only last months terminated employees.
Thanks in advance
Regards
Hi Tony
I need a report of all active employees as at run date and terminations as at a start and end date. eg active employees included staff terminated last month.
I tried different combinations of the 3 fields below but I either end up getting only last months terminated employees.
Thanks in advance
Regards
Missed the fields in previous post
Date Paid to eq – left a blank BOR
Date Paid to GE 01/12/20 AND
Date Paif to LE 31/12/20
hi
Im trying to run a report designer that will pick up all the lines in POS so that i can calculate employees average hours
please advise