I have been mulling this issue for a few days, and would like to
respond to a few things.
The approach suggested by Jason is a hack and a workaround, and is not
recommended practice (except maybe for a few experts like Jason who know
exactly what they are doing, and my experience is that most people don't...)
since the risk of data duplication is so high.
Actually, this is neither a hack or a workaround, but an unintended
feature of the UI/model. I am usually quite forward when it comes to
admitting when I hack DHIS. It is supported directly through the
UI, although the UI is inconsistent in places, since it is possible do
add an orgunit to multiple groups through the orgunit group editor,
but not possible through the orgunit editor.
What I failed to mention, and should have, is that assigning an
orgunit to multiple groups which belong to the same group set, will
result in a data integrity violation, which of course, should always
be checked to ensure that this type of situation does not happen.
I have been thinking about why this is actually an issue, and believe
it is actually the structure of the _orgunitgroupsetstructure resource
table. This table is crosstabbed by orgunit group set and therefore
assumes that the group membership is exclusive, at least the way it is
implemented currently. It is sounding more and more like group sets it
may not be, as I think we must sort of wiggle a bit when the
"Services" group set cannot contain "PMTCT" and "ART" and a single
facility can belong to both, depending on the services it offers.
Assuming that it did, the _orgunitgroupsetstructure table SHOULD (but
in fact does not, which I might regard as another bug) contain two
rows for this orgunit. However ,a _orgunitgroupstructure table could
be constructed, whereby orgunits are on the vertical axis, orgunit
groups in the horizontal (cross-tab) axis, with a boolean
representation (TRUE/FALSE) in order to indicate which orgunit group
the orgunit belongs to. This _orgunitgroupstructure table could then
be easily joined to the aggregated indicator/data value tables, and
would not result in a duplicated row, even if an orgunit belonged to
two separate orgunit groups.
I think we all agree that an orgunit can belong to more than one
orgunit groups, but I think the way the _orgunitgroupsetstructure
table has been traditionally used to join to the aggregated data, and
then to pull this into a PivotTable is the underlying problem. So, in
order to resolve that issue, orgunit group sets were made by default,
exclusive, when in fact they may not always be. There are examples
when they are, such as "Ownership".
P.S. Bob, It is also beginning to feel as if orgunit groupsets may in
fact be concepts in disguise.