Configurable ODBC Group Resolution not working

[#1]

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

[#2]

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.

[#3 · Accepted solution]

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.