8 Replies Latest reply: Feb 22, 2017 4:09 PM by S Khan RSS

    Data modeling Section Access, but also applicable totals?

    Dennis Norton

      I am using Qlik Sense and having difficulties determining the most efficient solution and how many applications this would take:

       

      As a user, I want to log in to a website and view KPI's associated with only me. For security reasons, section access must be applied to the application this information pulls from and will be the only application I/other users access. However, I also at a high level on this website, want to view the same KPI's, but for my entire company, and my entire country (but no other country or company totals). This would take into account users other than myself within my company, but the Section Access I have applied has already reduced data within the application down to myself.

       

      How would I be able to implement section access at the individual user level, but also allow them to see the total company and country numbers applicable to them? Does this require separate apps, each with different section access'?

        • Re: Data modeling Section Access, but also applicable totals?
          Chandrasheker Gompa

          Hi Dennis..

           

          Can you paste your section access logic and the source data load script..

           

          Thanks

            • Re: Data modeling Section Access, but also applicable totals?
              Dennis Norton

              I'll simplify the concept as it's pretty lengthy. Concept of this application is, when users enter this application, they only see their information due to SALESPERSON field in section access. If I want them to see their company totals and their country totals, it's excluding the other users amounts. Do I need a separate application(s) to mix/match these numbers properly while maintaining the section access security for individual users?

                                       Sales:               Should Be:

              Salesperson      $5,000               $5,000

              Company          $5,000               $25,000

              Country             $5,000               $175,000

               

              Section Access;

              List:

              LOAD

                   ACCESS,

                   USERID,

                   GROUP,

                   SALESPERSON

              FROM [lib://Excel/AccessList.xlsx]

              (ooxml, embedded labels, table is SalesAccess);

               

              Section Application;

               

               

              SalesFact:

              LOAD

                   TRANSACTIONKEY,

                   SALESPERSONKEY,

                   COMPANYKEY,

                   COUNTRYKEY,

                   SalesAmount,

                   DiscountAmount

              FROM ( [lib://Excel/SalesList.xlsx]

              (ooxml, embedded labels, table is Sales);

               

               

              SalesPersonDimension:

              LOAD

                   SALESPERSONKEY,

                   SALESPERSON,

                   SALESFIRSTNAME,

                   SALESLASTNAME

              FROM SalesFile;

               

              CompanyDimension:

              LOAD
                   COMPANYKEY,

                   CompanyName,

                   CompanyCity,

                   CompanyState,

                   CompanyZip

              From CompanyFile;

               

              CountryDimension:

              LOAD

                   COUNTRYKEY,

                   CountryName,

                   CountryAbbrev

              From CountryFile;

                • Re: Data modeling Section Access, but also applicable totals?
                  Chandrasheker Gompa

                  Hi Dennis..

                  Ideally one app should be enough .. but you have to build a little complex section access logic either by creating active

                  directory group with user list of having access to which company code and to which countries or add the list to your excel for testing and the section access logic could look like

                  Section Access;

                  List:

                  LOAD

                       ACCESS,

                       USERID,

                       GROUP,

                       SALESPERSON,

                      COMPANY,

                      COUNTRY

                  FROM [lib://Excel/AccessList.xlsx]

                  (ooxml, embedded labels, table is SalesAccess);

                   

                  PLEASE CHECK THE ORDER COUNTRY,COMPANY,SALESPERSON OR the vice versa..

                   

                  Thanks

                  • Re: Data modeling Section Access, but also applicable totals?
                    S Khan

                    Please find the attachment.

                    UserId: ADMIN

                    Password: ADMIN

                    USERID     PASSWORD

                    ALEXABCD
                    JOHNBCDE
                    SALLYQWER

                    You can login as individual user and check...

                    You need to link each salesperson with their respective companies/countries. I did not include OMIT field in SA but you can do that too...

                     

                    Loaded 3 KPI's for each user and data is visible for logged in user only for their company/country.

                     

                    Same can be replicated in Qlik Sense, except you need to add USER, INTERNAL\SA_SCHEDULER,*, in Inline or the SA will not work. And the ADMIN will also come as USER and * will be his values for all the fields

                      • Re: Data modeling Section Access, but also applicable totals?
                        Dennis Norton

                        Hi Aehman,

                         

                        In this example, if I login as Alex and John and Sally are both in my company and country, is there a way to see our combined sales when viewing Company and Country, but to never be able to view the sales #'s tied to John or Sally?

                          • Re: Data modeling Section Access, but also applicable totals?
                            S Khan

                            I'm not sure if you looking for this but it was really challenging task for me who is also new to Qlik.

                            Find the attachment.

                            What I did to accommodate your requirement is created 2 fields with same values for SalesPerson but different name.

                            and I added OMIT for the first field name

                            Star is *;

                             

                             

                            SECTION ACCESS;

                             

                             

                            LOAD * INLINE [

                                ACCESS, USERID, PASSWORD, COMPANY, COUNTRY, SALESPERSON, OMIT

                                ADMIN, *, ADMIN, XYZ, *, ALEX

                                USER, ALEX, ABCD, *, *, *, SALESPERSON

                                USER, JOHN, BCDE, *, *, *, *

                                USER, SALLY, QWER, *, *, *, *

                                USER, , BCDE, BCD, UK, *

                                ADMIN, *, ADMIN, ABC, USA, JOHN

                                ADMIN, *, ADMIN, BCD, *, SALLY

                            ];

                             

                             

                             

                             

                             

                             

                            SECTION APPLICATION;

                             

                             

                            LOAD * INLINE [

                                SALESPERSON, COUNTRY, COMPANY, SALES, TOTAL, PERSON

                                ALEX, USA, ABC, 10000, 35000, ALEX

                                JOHN, USA, BCD, 25000, 66500, JOHN

                                SALLY, UK, XYZ, 5987, 11000, SALLY

                                ALEX, USA, ABC, 11278, 35000, ALEX

                                ALEX, USA, ABC, 98217, 23213, ALEX

                                ALEX, USA, ABC, 73767, 123213, ALEX

                                JOHN, USA, BCD, 25000, 66500, JOHN

                                JOHN, USA, BCD, 8798, 768886, JOHN

                                JOHN, UK, BCD, 12312, 34232, JOHN

                                JOHN, UK, BCD, 23987, 2354, JOHN

                            ];

                            ALEX, JOHN and SALLY will see total Sales and all Companies plus all countries but individual sales and will not see individual sales from JOHN or SALLY.

                             

                            So for the Individual KPI's you will need to give if condition

                            Each KPI are designated to show their own total sales.

                            Ex: In condition for ALEX KPI you'll need to give

                            if(osuser()='DOMAIN\ALEX',1,0)

                            This will show or hide the KPI with regards to NTNAME.

                            check the app and you'll understand.

                            Other way is creating hidden sheets for each user based on NTNAME again but if users are more than 100 then it will be issue hence better show/hide the kpi using NTname.

                            If you doing this in Qlik Sense then everything will be same but slight change in script plus you'll need a show/hide container to show or hide KPI.

                             

                            Check the script it's hidden

                            password for hidden script is admin

                    • Re: Data modeling Section Access, but also applicable totals?
                      S Khan

                      I'm not sure if I understood you correctly, perhaps your SA script might help.

                       

                      1. User1 should see KPI related to his role only (that's understood)

                      2. User1 should also see all country/company KPI? (please explain more, excluding User1 all KPI data?)

                       

                      You can use OMIT or Reduction in Section access to limit user to only few fields or values.

                      As KPI's contains measures, maybe you need to add measure to inline load

                      look for the example below ( and use that field name to omit)

                      Re: How to handle single quotes in Inline Load

                      Or

                      You can also create a custom security rule in QMC to show/hide a sheet (which can consists all KPI's) based on user name/NTNAME.