3 Replies Latest reply: Sep 18, 2014 1:03 PM by Vlad Gutkovsky RSS

    Distribute Visibility Section Access Loop Reduce Publish

      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.

        • Re: Distribute Visibility Section Access Loop Reduce Publish
          Vlad Gutkovsky

          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:

          REGIONIDRegionTerritoryIDTerritory
          1New England1ABoston
          1New England1BNYC
          1New England1CProvidence


          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:

          MyRegionIDMyRegionMYTERRITORYIDMyTerritory
          1New England1ABoston
          1New England1BNYC
          1New England1CProvidence

           

          Now you can build a section access table as follows:

          ACCESSNTNAMEREGIONIDMYTERRITORYID
          USERUSER111A


          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

            • Re: Distribute Visibility Section Access Loop Reduce Publish

              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.

                • Re: Re: Distribute Visibility Section Access Loop Reduce Publish
                  Vlad Gutkovsky

                  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:

                   

                   

                  ACCESSNTNAMEREGIONIDMYTERRITORYID
                  USERUSER211A
                  USERUSER211B
                  USERUSER211C
                  USERUSER221A
                  USERUSER221B
                  USERUSER221C


                  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