topic Re: Support sheet for admins - how to see all users? in QlikView

Support sheet for admins - how to see all users?

Mon, 10 Nov 2014 07:47:28 GMT

Hi,

I have a working section access.

I would like to have a support sheet that will be reachable on the Access Point with a separate Bookmark. My question is now, how can I create the table in the data model, with the current implementation the NTNAME gets reduced to only show my own user. But I would like to click on any user's name to see what he is able to see when logged in.

Is that possible?

Thanks.

Re: Support sheet for admins - how to see all users?

Tue, 11 Nov 2014 09:05:18 GMT

Anybody who can help?

Re: Support sheet for admins - how to see all users?

marcus_sommer — Tue, 11 Nov 2014 09:55:33 GMT

It's not quite clear what do you want to do. Create a separate application with an overview to the access of all other applications and users or integrate this into each single application? Want you see the access to the applications and/or restrictions per section access? Are you using NT or DMS as authentification mode?

Generell are there these data available you could restrict the access only to you. Maybe these tools especially the user manager within the powertools are helpful for you:

http://community.qlik.com/blogs/technicalbulletin/2013/12/05/qlikview-governance-dashboard-11-available-for-download

Governance Materials.zip

PowerTools 1.1 for QlikView

- Marcus

Re: Support sheet for admins - how to see all users?

Tue, 11 Nov 2014 10:26:23 GMT

I would like to have a copy of the invisible section access table in the data model of my single application. I'm using link tables, as I'm reducing on two fields during section access, and I would like to connect this copied table with the link tables.

I'm using NTNAMES for authentification.

Re: Support sheet for admins - how to see all users?

marcus_sommer — Tue, 11 Nov 2014 11:31:35 GMT

I have done something similar by loading the section access table twice. First as normal section access and then as section application again and use there another key to my link table. This meant my link-table had two keys. One for the section access and one for my visibility.

The access to this table isn't strict restricted then everybody by us knows the main-logic how the restrictions worked and it's no secret. Nevertheless this table is on a hidden sheet with osuser() = OnlyMe. This is enough and the most user would never try to search for data which are independent to their business (fields are generally available). But I think it would be possible to link these visible table also to the section accessSection Access

Alternatively you could read the section access from all your applications in a special applications so that you could have a central overview ...

- Marcus

Re: Support sheet for admins - how to see all users?

Fri, 12 Jun 2015 11:26:35 GMT

Hi Marcus,

I still had no luck solving this. By reloading the section access table in the application part, I either have the situation that my Admin still cannot see everything (at least not other users than himself) or that the normal users can see everything, so reduction does not work.

I attached my script with working section access, it is only a model of the real application.Could you please help me? What has to be done to load the acces information in the application?

Many thanks

Jens

Re: Support sheet for admins - how to see all users?

marcus_sommer — Sun, 14 Jun 2015 05:52:11 GMT

In your way you have two identically tables - one within the section access and one within the section application. This will create synthetic keys and circular references between those both tables and your table with the linked dimension. But if you used a qualify-statement for the table within the section application - it should work. To see how the datamodel looked like within the tableviewer you could comment the section access statement.

An alternatively could be to store these table as qvd - with an additionally documentname() - and then drop the table and load the qvd in a special admin-app. This had the advantage of a central overview to all section access restrictions in your environment.

- Marcus

Re: Support sheet for admins - how to see all users?

Mon, 15 Jun 2015 05:39:13 GMT

Thanks for your response Marcus.

I will give the "Qualify" solution a try - see what's happening .

The special-admin app is not really an option, the application I'm currently working on will remain the only one on that QV installation. So I think it is easier to have the overview in the same appliction on a separate sheet.

Jens

EDIT:

After trying the Qualify solution. I don't get the idea behind it, to be honest. What I was planning to do to drop the table Access_TMP (which is my preparation table for the SA). Then I have the Section Access table itself with the fields ACCESS, NTNAME, PARTY_AUTONUM and TERRITORY_AUTONUM. I would reload the Access_TMP as Access (before dropping it of course) with ACCESS_TMP, NTNAME_TMP, PARTY_AUTONUM_TRY and TERRITORY_AUTONUM_TRY.

The attached screen is from the Qualify solution, I think i'm missing something. Help me, please .

Re: Support sheet for admins - how to see all users?

marcus_sommer — Mon, 15 Jun 2015 06:38:48 GMT

Your datamodel looked quite right - if you loaded the Access_TMP within the section access part it should work.

- Marcus

Re: Support sheet for admins - how to see all users?

Mon, 15 Jun 2015 07:43:35 GMT

The section access itself works fine this way.

The problem is, that the admin user can only see himself in the published dashboard as NTNAME.

So I added the following to the section application:

LOAD

NTNAME as NTNAME_TMP,

PARTY_AUTONUM,

TERRITORY_AUTONUM

Resident Access_TMP;

drop table Access_TMP;

This (nearly) works. The admin can then see all users and all related PARTY_IDs, he sees all TERRITORY_IDs, but not the related users.

Again, the data reduction is working fine. User 'A' is reduced to his territory 'ABC' and can only see his data. But the admin does not see the relationship between 'A' and 'ABC'.

Re: Support sheet for admins - how to see all users?

marcus_sommer — Mon, 15 Jun 2015 08:23:25 GMT

Bei mir sieht das so aus. Am AUTHKEY hängt die Section Access Tabelle und am MATCHKEY die zusätzlich parallel geladene und damit sichtbare Autorisierung der User gegenüber dem Datenmodell. Wenn Du diese Tabelle in eine Tabellenbox packst, siehst Du die definierten Zugriffe.

- Marcus

Re: Support sheet for admins - how to see all users?

Mon, 15 Jun 2015 10:52:08 GMT

I found out that the problem comes from this piece of code:

//Adding 'VIEW ALL' users and concrete PARTY_IDs

concatenate (Access_TMP)

LOAD

'USER' as ACCESS,

NTNAME as NTNAME,

if(PARTY_ID_all = 1, 'ALL', PARTY_ID) as PARTY_AUTONUM,

if(PARTY_ID_all = 1, 'ALL', 'PLACEHOLDER') as TERRITORY_AUTONUM

FROM [..\InputData\99_REF\ACCESS_TABLE_PARTY_bigger_model.xlsx]

(ooxml, embedded labels, table is Tabelle1)

where NTNAME<>'*';

If I use my model and load the smaller Excel input, it works. If I use the big QVD files, The admin cannot see the user to territory relationship. Still need to figure out what makes the difference.

Re: Support sheet for admins - how to see all users?

marcus_sommer — Mon, 15 Jun 2015 11:40:17 GMT

You will need to load all your matching-values from this qvd and compare and check them against the values from your excel. Are there any abnormalities? This could be quite difficult - maybe you used at first some where-filter on the qvd-load to minimze the amount of values which are to check.

I have had last week a similar case with section access which won't matched with the section application area because in this structure data was a special char - chr(8203) - which you couldn't see and which won't be removed from trim(). I have it then removed with purgechar().

But first you need to find it. For this you could use something like:

ord(mid(YourValue, 1, 1))

- Marcus

Re: Support sheet for admins - how to see all users?

Mon, 15 Jun 2015 12:25:10 GMT

Thanks, good hints.

When I load the same data from the QVD that I load from the Excel file, it works. As soon as I load more data from the QVD, the error happens.

I also tried to analyze the data with ord() and right(), still didn't find the issue. Seems I have to dig deeper

Re: Support sheet for admins - how to see all users?

Wed, 17 Jun 2015 12:34:31 GMT

I'm one step closer to the solution. There is a certain user that causes the trouble. Loading him in the section access and the problem occurs, keeping him out with a where clause leads to a successful application.

Problem is, that I don't know what the difference is between him and the rest,,,

Re: Support sheet for admins - how to see all users?

marcus_sommer — Thu, 18 Jun 2015 14:40:42 GMT

Maybe there are some special chars into that user. I would try to check the length and each single char to detect any abnormalities. Maybe it's related to: Search - But what shall you find?

- Marcus

Re: Support sheet for admins - how to see all users?

Fri, 19 Jun 2015 08:00:14 GMT

Thanks for your patience to still look into this thread 😉

I checked the user's data, but I cannot find anything that looks strange.

What I've done in the meanwhile: Generally I'm reducing two fields, which, as they do not correlate, is not a problem. I found out that if I do not load that specific user's section access for PARTY_AUTONUM, everythings works fine. TERRITORY_AUTONUM load does not cause trouble.

So I changed my load, to only load certain PARTY_AUTONUMs and see what happens. This is a numeric fields up to 8 digits by the way:

concatenate (Access_TMP)

LOAD

'USER' as ACCESS,

NTNAME as NTNAME,

PARTY_ID as PARTY_AUTONUM,

'PLACEHOLDER' as TERRITORY_AUTONUM

FROM ..\QVD\Access_Table_Party_Debug.qvd (qvd)

where NTNAME='user.name@company.com' and wildmatch(PARTY_ID, '10*', '11*');

Loading this with only '10*' or with '11*' in the wildmatch will work. As soon as I load both, the problems with the visibility of TERRITORY_NUM appear again.

Re: Support sheet for admins - how to see all users?

marcus_sommer — Fri, 19 Jun 2015 09:23:07 GMT

It's not clear what's going wrong in this case and the following ideas are only trial and error:

- bug in QlikView --> upgrade to a newer version

- app is corrupt --> create a new one

- username contained a Key-Word/Key-Sign like CALL or $ and qv tried to interpret it not as a simple text --> text(NTNAME)

- problems with blanks or wildcards or statements like STAR is *;

Maybe you could find here some more hints: Section Access.

If this all don't worked you should rather build a simplified datamodel from this case and make a request by the qlikview support.

- Marcus

Re: Support sheet for admins - how to see all users?

Fri, 19 Jun 2015 10:19:30 GMT

Ok thanks Marcus,

As I don't know how long further research / trial&error will take, I'll mark your last post as Correct Answer.

I'll give an update once I found the solution.

Thanks a lot & have a nice weekend

Jens