I am trying to separate data that would fall into a query. What I am trying to do might not be possible but needed to ask. I started with a cross table that has departments as the columns and users and hours as the breakdown on the rows. We do a count of the reports the users do per hour in each department. The only problem is only certain users work in certain departments so I end up with a big old cross tab report with very little data in it.
I am guessing a cross table isn't the way to do it but I can't think of another way(and maybe there isn't a way). I would like to see columns of the departments but only have the rows of users that actually wrote reports for that department and the hours they did them in as the rows. So you shouldn't necessarily see a perfect "box". Some departments are going to have more users and hours so some lists will be longer and some shorter.
I could write a query for each department and just separate the lists but I am also trying to make this dynamic so when new departments are added I will get a new column with the department.
That being said, can you do that with a cross table? If not, is there another way to handle this dynamically?
Thanks for any help provided.