Reports - Accessing peer subquery values

I figured I’d post a tip to help others (and myself after I’ve forgotten and am searching [Hi future me! :smile: ]) on how to access peer subquery values in reports.

My datasource is setup like this:
image
The employees query contains a list of employees that worked during the pay period in question (includes name, payroll id, etc.) The empClockLog subquery contains the records of every Clock In, Clock Out and Duration in hours for week. The holidays subquery has a total number of hours worked during days that we pay doubletime for (Sundays and designated holidays).

The report designer has some generic header information at the top of my page, followed by a table group with the following layout:
image
The employees table has its data key set to ‘employees’. This table has only the detail section turned on. I have @employee name@ displayed in a cell, along with static headers for the Clock In/Out log nested in the next table. It also has a variable embedded that I’ll talk more about later.

The empClockLog has its data key set to ‘empClockLog’ and has both the details section and summary section turned on. The details section simply has columns for Clock In, Clock Out and Hours, pulling the information straight from the nested query. The summary section was where I ran into problems. I needed to display 4 calculated amounts. One was the total hours worked. This was simple; @total.duration@ (duration is the name of the ‘Hours’ column. Another was double time hours. This, theoretically, was easy. It’s the sole result of the ‘holidays’ nested query which is a peer, or sibling, query to the nested query I’m currently working in. However, @employees.holidays.doubletimehours@ would only fetch the result for the first row of that dataset. So, the report was displaying the double time hours for the first employee for every employee, instead of properly pulling each individual’s doubletime hours as I assumed it would.

Digging around in the docs, I noticed a few things. One, direct access to query data in other keys is possible, and individual rows are able to be addressed as an index (@employees[0].columnnamehere@ The other thing I noticed is that assignments are allowed for keychain expressions to create temporary variables. The example section for that starts out “For the brave”; guess I’m being brave today :sweat_smile:

What I ended up doing was adding a variable declaration to the first table of the report. @rowPointer = Row-1@. The -1 is needed as data key rows start on 0 and the built-in @Row@ counter starts on 1.

Then, inside the child table, I was able to access the value I needed to retrieve by the following keychain expression: @employees[rowPointer].holidays.doubletimehours@

My other items I needed to display were just calculations based on the total hours worked and doubletime hours worked, so keychain expressions using some math and the above expressions handled the rest.

Hope that helps someone down the road!