Distribute Visibility Section Access Loop Reduce Publish

[#1]

Hello,

Lets say that I wish to Publish a qvw and loop & reduce based on geography for Boston.  Now for a particular view i want to show Boston and its peers - lets say NYC and Providence.  If I were to construct a conditional such that if 'Boston' is selected in a list box then Boston, NYC, and Providence would all appear.  Then if I were to loop and reduce the report would Boston, NYC, and Providence all appear?  The rest of the report would just show Boston information and Boston would remain as the only value in my list boxes throughout the whole report.

If not, how could I achieve this??

I would like to make this desired effect to hold true for section access as well.

Right now certain constraint are are restricting me from testing a proof of concept.

Thank You.

[vgutkovsky]

The way I typically accomplish this is by creating a dimension island. Let's say "Boston" is a "Territory." The way you determine its "peers" is by looking at all Territories in its "Region" (which is one level higher). You would then have 2 tables, Geography and MyGeography. The Geography table would be connected to your main data model, and the MyGeography table would be an island.

The main Geography table would look like this:

REGIONID	Region	TerritoryID	Territory
1	New England	1A	Boston
1	New England	1B	NYC
1	New England	1C	Providence

Note that the first column is named "REGIONID" in caps to be able to use it for Section Access (which requires that both the field name and the field value are upper case).

The MyGeography island table would look like this:

MyRegionID	MyRegion	MYTERRITORYID	MyTerritory
1	New England	1A	Boston
1	New England	1B	NYC
1	New England	1C	Providence

Now you can build a section access table as follows:

ACCESS	NTNAME	REGIONID	MYTERRITORYID
USER	USER1	1	1A

In the UI, you can then use some simple set analysis to catch the reduced value of MYTERRITORYID and use it to restrict TerritoryID in all charts where you do not want to show a peer comparison. The syntax for that would simply be: {<TerritoryID=P(MYTERRITORYID)>}

List boxes would display MyTerritory, rather than Territory.

Regards,

Vlad

[#3]

Thank You very much - this was very helpful.

A few questions though:

-     Would the section access be the exact same as the way the the report would be distributed upon loop and reduce?? In therms of territory visibility, of course.

-     Would the method that I proposed above work??

-     Lets say a user would have access to see all Territories (in their Region) and their Region specifically at a higher level.  So they would be able to see a higher level view and also many lower levels.  How would the SetAnalysis for the UI change for this??  And what would the SectionAccess table look like??

-     We could even add in another higher level - call it US (Country).  And this user would not see a breakout of their country (US), but would see 1 Region (New England) and many Territories (Boston, NYC, Providence).  This person would be the equivalent of a New England Manager.  In this example we would have an AreaID & MyAreaID,

but how would the visibility break out and how would the section access look for this to denote visibility to the higher level view of New England?

Thanks.

[vgutkovsky]

All good questions. Let me address the last part first. It's quite simple to add different levels of users in Section Access. For example, a Regional Manager (whose peers would be other Regions) who have the following records:

ACCESS	NTNAME	REGIONID	MYTERRITORYID
USER	USER2	1	1A
USER	USER2	1	1B
USER	USER2	1	1C
USER	USER2	2	1A
USER	USER2	2	1B
USER	USER2	2	1C

It would be more efficient to actually have 2 section access tables (one to reduce Geography and one to reduce MyGeography), by the way, but this way will work too.

In that case, I would display fields from the MyGeography table in a drill-down list box (MyCountry >> MyArea >> MyRegion >> MyTerritory, or whatever the hierarchy is). I also sometimes find it useful to create another island table with a single field called EMPLOYEELEVEL. This field could be 1 for Territory users, 2 for Regional Managers, 3 for Area Managers, etc. If you add this field to your Section Access reductions, you can then also write different logic for different users if you need to.

Regarding Loop-and-Distribute, with the above solution you wouldn't need to do that. Everything can live in 1 app, and section access will take care of dynamic reductions when users open the app. If you want to use Loop-and-Distribute to create smaller apps (for offline distribution for example), you would have to create a Publisher task for each level. For example, you would create one to reduce on field RegionID; the output of that task would be the app that your territory users would use. You would also have to set NTFS permissions; even though Section Access would automatically control security, you don't want Regional Manager to open the Territory users' apps (for example) or to have them clutter up their AccessPoint.

Regarding your set analysis question, it would not have to change for the most part. The P(MYTERRITORYID) logic would work just fine for higher-level users too, because they would have several values of MYTERRITORYID available. Once they've used the list box to drill to a different level, the set analysis would update automatically to reflect the new P() universe.

Hope this helps,

Vlad