How to build a dynamic matrix type report
Posted on 2006-04-05
I just can't get my head around this and could use some help. I have a dynamic list of tech employees and will be maintained by an admin. The admins which check a Tech checkbox to make an employee show on the tech list. We then have a list of clients, with one tech assigned to each client. The tech assignments will also be maintained by the admin to keep updated.
I need a report that will list the employees across the screen, list the Clients down the left column, with an X in the intersecting cell. At the bottom of the report, each employee will have a total number of client assignments.
Company Employ1 Employ2 Employ3
----------- ----------- ----------- ----------
----------- ----------- ----------- ---------
Total 2 1 1
I can do this report if I hard code the employees and perform an Onformat event to see which column gets the X, and create variables to track how many X's are assigned to a given hard coded cell. But I would like the columns to be dynamic as I know the list of techs will change down the road.
Any idea's how to approach this?