Skip to Content

Split up Department Data by User

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Posted on Feb 28, 2019 at 03:26 PM

    Hi Ben,

    Can you please create some sample data in excel and share image of what the crosstab looks like, and another image for your requirement?

    Thanks,
    Mahboob Mohammed

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 03:53 PM

    Absolutely, the first is what I am getting in the crosstab and the second is what I would like to see or at least something similar.



    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 05:20 PM

    You'll need to do the below:

    Step 1: Create one Cross Table per Department, meaning create as many Cross Tables as the number of Departments you have, and put a filter on each Cross Table to bring data for One Department only.

    1. Filter on Cross table 01: Department = Department A
    2. Filter on Cross table 02: Department = Department B
    3. Filter on Cross table 03: Department = Department C
    4. Filter on Cross table 04: Department = Department D
    5. .... and so on
    6. Filter on Cross table 26: Department = Department Z

    Step 2: Update properties of the Cross tables as below:

    1. Properties of Cross table 01: Right Click on Cross table 01 -> Format Table -> General -> Check the box for Hide when Empty

    2. Properties of Cross table 02: Right Click on Cross table 02 -> Format Table -> General -> Check the box for Hide when Empty, and in Layout -> Relative Position -> Horizontal = 0cm from Right Side of Cross table 01, and Vertical = 0cm from Top Side of Cross table 01

    3. Properties of Cross table 03: Right Click on Cross table 03 -> Format Table -> General -> Check the box for Hide when Empty, and in Layout -> Relative Position -> Horizontal = 0cm from Right Side of Cross table 02, and Vertical = 0cm from Top Side of Cross table 01

    4. and so on

    5. Properties of Cross table 26: Right Click on Cross table 26-> Format Table -> General -> Check the box for Hide when Empty, and in Layout -> Relative Position -> Horizontal = 0cm from Right Side of Cross table 25, and Vertical = 0cm from Top Side of Cross table 01

    Voila.

    Let us know if that helps.

    Thanks,
    Mahboob Mohammed

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 07:17 PM

    I was trying to accomplish this dynamically. There will be new departments that are opened and built into the tables. My query already would pick up new departments, I was just trying to get the display to handle this dynamically as well.

    What you seem to be suggesting would require me to build another table at the time of a new department.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 08:42 PM

    I agree the workaround is not dynamic, I assumed you'll have a limited number of Departments, and that's not a list which grows everyday. Apart from this, I can't think of a different workaround. I guess we'll need something like Vertical Sections to address your request. :-)

    Thanks,
    Mahboob Mohammed

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 09:08 PM

    Thanks for the effort :)

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 28, 2019 at 09:40 PM

    Welcome!

    Thanks,
    Mahboob Mohammed

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.