M
Mia_placidus
I work for a fairly large organization. While the
organization itself does not change all that frequently,
personnell do, or change department. etc. Then of course
there is the musical chairs problem with people using
desks in other Depts. etc. (Yes, it's a government
organization.) Therefore it is a challenge to keep track
of who is currently in what dept. and who reports to whom.
I have an organization table that lists the various
departments and numbers the level in the org, similar to a
WBS. That is, the CEO's office is 1.0, the three VP's are
1.1, 1.2, 1.3, the first three directors are 1.1.1, 1.2.1,
1.3.1, etc.
I have developed two reports. One answers the
question "Who are the current members of dept "X"?" and
the other answers the question "Who reports to "John
Doe"?" and these work OK.
Now I'd like to make an emergency phone tree report that
is presented in a format similar to an Org Chart. Now, I
can draw the tree diagram on a blank report and populate
the branches with text boxes. Then if I put a dlookup in
the box that says
dlookup("[Name]","Organization","[Level = '1.3.1'")
then I'll get the name of the first director reporting to
the 3rd VP. To get is phone number I could conjoin another
text box with a similar dlookup for the phone number.
If I use a similar approach with a list box I can get both
the director and his assistants and the phone numbers in
one box. Likewise I can get a dept. head and all his
direct reports in one box. That works OK except a list box
doesn't resize as required, so there is a certain amount
of maintenance required as department staff increases.
Likewise if (when) the organizational structure changes.
The first three levels will fit on a one page report.
Lower levels will require separate reports because once
you get down to the department level all the staff in one
department are at the same level and the list boxes get
large. You may then have a director, and three or four
departments he controls, on one page.
All the same info is available on the "Reports to" report,
but it is useful for this application to present just
limited info(phone numbers) on a semi-graphical report.
So, a few questions:
How come a list box works on a report but a combo box with
the same input doesn't? I realize the functionality of a
combo box is meaningless on a report, but I don't see whiy
it would work just like a list box.
How would I go about automatically resizing the list
boxes, recognizing that I might still have to adjust their
position on the page manually? I suppose I could create
the whole report in code, but that may come later.
Anybody got a better approach? I thought of exporting data
to an org chart diagram in PPoint, but the org chart tool
there is too rudimentary.
organization itself does not change all that frequently,
personnell do, or change department. etc. Then of course
there is the musical chairs problem with people using
desks in other Depts. etc. (Yes, it's a government
organization.) Therefore it is a challenge to keep track
of who is currently in what dept. and who reports to whom.
I have an organization table that lists the various
departments and numbers the level in the org, similar to a
WBS. That is, the CEO's office is 1.0, the three VP's are
1.1, 1.2, 1.3, the first three directors are 1.1.1, 1.2.1,
1.3.1, etc.
I have developed two reports. One answers the
question "Who are the current members of dept "X"?" and
the other answers the question "Who reports to "John
Doe"?" and these work OK.
Now I'd like to make an emergency phone tree report that
is presented in a format similar to an Org Chart. Now, I
can draw the tree diagram on a blank report and populate
the branches with text boxes. Then if I put a dlookup in
the box that says
dlookup("[Name]","Organization","[Level = '1.3.1'")
then I'll get the name of the first director reporting to
the 3rd VP. To get is phone number I could conjoin another
text box with a similar dlookup for the phone number.
If I use a similar approach with a list box I can get both
the director and his assistants and the phone numbers in
one box. Likewise I can get a dept. head and all his
direct reports in one box. That works OK except a list box
doesn't resize as required, so there is a certain amount
of maintenance required as department staff increases.
Likewise if (when) the organizational structure changes.
The first three levels will fit on a one page report.
Lower levels will require separate reports because once
you get down to the department level all the staff in one
department are at the same level and the list boxes get
large. You may then have a director, and three or four
departments he controls, on one page.
All the same info is available on the "Reports to" report,
but it is useful for this application to present just
limited info(phone numbers) on a semi-graphical report.
So, a few questions:
How come a list box works on a report but a combo box with
the same input doesn't? I realize the functionality of a
combo box is meaningless on a report, but I don't see whiy
it would work just like a list box.
How would I go about automatically resizing the list
boxes, recognizing that I might still have to adjust their
position on the page manually? I suppose I could create
the whole report in code, but that may come later.
Anybody got a better approach? I thought of exporting data
to an org chart diagram in PPoint, but the org chart tool
there is too rudimentary.