Qlik Community

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Configurable ODBC Group Resolution not working

Has anyone gotten group resolution for configurable odbc to work?  I'm trying to add groups to a document and the configurable ODBC doesn't resolve groupid in search string in add users.

Here is an example of my user view:

EntityID                EntityName          descr      Email

2500800000578     2500800000578    UserX      UserX@email.com

Here is the SQL code for the Users view:

select

cast(clientid as bigint)*100000000 + cast(userid as bigint) as EntityID,

cast(clientid as bigint)*100000000 + cast(userid as bigint) as EntityName,

FirstName + ' ' + LastName as descr,

Email

from dbo.Users with (nolock);

Here is an example of my group view:

ID                         MemberID           GroupID

2500800000024     2500800000578    2500800000024


I'm trying to add group 2500800000024 but if I try to search for it when adding users/groups, nothing is resolved.

Here is the SQL code to create the usergroup view:

select

cast(clientid as bigint)*100000000 + cast(usertypeid as bigint)  as ID,

cast(clientid as bigint)*100000000 + cast(userid as bigint) as MemberID,

cast(clientid as bigint)*100000000 + cast(usertypeid as bigint) as GroupID

from dbo.Users with (nolock);

Any ideas as to how this might work is greatly appreciated.

Servers are running QlikView 10 SR2

The ODBC connection is to SQL server.

Thanks,

Jeff G

1 Solution

Accepted Solutions
Not applicable
Author

Ok, so bottomline, the documentation is completely unhelpful.  I worked through the group resolution issue and as far as I'm concerned it's a bug.

The world I'm supporting has many many users, so many that using a bigint datatype for the entityid is required.  When doing user resolution, having the bigint works without issue.

However, try to get a bigint working in the groups table and you are going to find that group resolution won't work.  The reason is that while you can make the memberid and groupid a bigint, the code for reading the fields will only recognize values up to max(int) or approximately 2.1 billion.  My opinion, this is a big ole bug that hopefully will be addressed soon.

View solution in original post

2 Replies
Not applicable
Author

hi Jeffrey

documentations for Configurable ODBC...

---

36.2 Configurable ODBC
The ODBC database has to have two table, or two views, one for entities and one for groups.
The entity table must have the four following fields: entityid, name, descr and email. The fields name,
descr and email must be strings. Entityid must be a unique identifier (suitable for primary key).
The groups table must contain two fields: groupid and memberid. Together these two fields create a
unique identifier.

---

The 'memberid' should contain the 'uid' and the 'groups' info should contain the name on the group.

Thnks.

Not applicable
Author

Ok, so bottomline, the documentation is completely unhelpful.  I worked through the group resolution issue and as far as I'm concerned it's a bug.

The world I'm supporting has many many users, so many that using a bigint datatype for the entityid is required.  When doing user resolution, having the bigint works without issue.

However, try to get a bigint working in the groups table and you are going to find that group resolution won't work.  The reason is that while you can make the memberid and groupid a bigint, the code for reading the fields will only recognize values up to max(int) or approximately 2.1 billion.  My opinion, this is a big ole bug that hopefully will be addressed soon.

View solution in original post