What you have defined is a Boolean expression--an expression that will return 1 (True) or 0 (False) when it is evaluated. It is comparing the value of the field named Purpose to the value of "Sick" & paid_time_off_in_hours. The & operator concatenates (combines) the text "Sick" with the value of the field.
So that's not a calculation likely to produce the results you expected to get.
But you don't actually need any such calculation fields to get the needed sub totals if you set up a typical summary report. I am assuming that each requested leave is a different record. I am also assuming that you have a single field that records the amount of time off requested.
If so, you can define a summary field to compute the total of that requested time off field and you can sort records by Employee, then by Purpose to produce a report that looks like this:
Employee: John Smith
Without pay aa
Employee: Jane Doe
and so forth....
This report is a list view layout with two sub summary layout parts and no Body layout part. The first sub summary layout part contains the Employee Name and has specified that it is "when sorted by Employee". The second sub summary part has the purpose field and is "when sorted by Purpose".
Note that if records are not sorted by a sub summary layout part's "sorted by" field, the sub summary part is not visible. And the order shown for purpose would only be possible by setting up the sort order to use a custom sort order based on your value list.
To limit the report to data for specific employees or specific time periods, you would perform a find that only pulls those records into the layout's found set before sorting them by both employee and purpose.
I am new to reporting and summaries, so bear with me. With your solution above would I be able to build a report that shows a Total hours taken of only Sick leave, and a total hours taken of just Vacation leave, And a total hours taken of just Balance day per employee. The way I have it now, I just get a total of all hours taken and does not break down the sub-totals per Purpose. Although it soes show a list of the hours and which purpose
My goal is to show person "X" took 12 hours sick leave, 40 hours vacation, 12 hours Balance Day, for the calendar year of 2014.
Right now my total would just shows 12+40+12 = Total 64
Yes. That's why you place the summary field inside a sub summary layout part. That's what enables the summary field to display a subtotal--the total for just one purpose for one employee.
OK, I think I am starting to understand the sort in reporting. I created a layout called Simple Report and added the fields you suggested but it does not appear to be giving me the totals for each my value list entries, Sick, Balance days, etc... I posted the file. It does not have any password.
It should be reporting I have taken 24 hours in balance days and 20 hours in sick but my SImple Totals Report is only reporting 8 Balance and 8 Sick.
You didn't use the summary field on your layout.
Replace <<Paid time off in hours>> with your summary field. (No real need to make these merge fields by the way.)
And this summary field cannot be a running total summary field, you'll need either a new summary field or you'll have to clear that option.
And I'd recommend sorting the records by an EmployeeID field that uses a number to uniquely identify each employee, you could get two employees with exactly the same name. You can sort by employee last name, first name to alphabetize your list, but then make the next field in your sort order EmployeeID to make the sub summary layout part visible.
This is working great!!! What a powerful tool!! Phil you are the best!!