I'm having trouble finding any best practices for a report designer when you have some data elements which repeat multiple times within a single report. Every report design best practice seems to assume that every element repeats a single time or that they all repeat together.
For example, say the report is about managers and their employees with everyone's salary. On each row, you have a manager, their salary, and then a repeating list of each employee with their salary.
The standard format doesn't really work so well.
The reason why is that manager information cannot be sorted alongside employee information. Here are two sample reports.
In this report, everything seems to work well. But when I sort Employee Id to right under Manger Id...
The behavior changes. In this case, Employee Id and Employee Salary now repeat independently instead of repeating as a group, because Manager Salary is now in the way.
So the best option seems to be to lock down the sorting. You can sort Manager information and you can sort Employee information, but the two cannot be sorted with each other.
Here, the line stops the sorting (but I doubt a line is the best way to present these are not being able to be independently sort it).
But what if I want all the Employee data (with Id and Salary repeating as a group) before all the manager data. How would you present an option to the user to switch Employee data with Manager data? What if you had more types of repeating data other than Manager and Employee (say we added in assets where Managers and Employees could each have multiple assets along with the value of the asset)?
As far as I can tell (and talking with a few UX folks where I work), this is a really abnormal report and there are no best practices or accepted designs for handling this. I fear coming up with a custom way to handle this will confuse the end users.
P.S. The users are the ones insisting we have a single row per manager format and we don't have the political power to resist.