Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP

Automatic assigning/removing User and Document Calc in QMC

Not applicable

Automatic assigning/removing User and Document Calc in QMC

Hello,

Tired of manually mapping users and assigning document calcs? Try this:

Introduction:

I wanna share my thoughts on creating users and assigning document calcs automatically. I am working on a BI project where we daily have new users who should have access to their own document in Qlikview.

Setup:

We have made a Qlikview solution where we create a masterdocument, which contains all my data for all users. The document is Incremental loaded every night.

Next step is distribute the masterdocument out to every user, so they have their own document to access with their own related data based on a UserID. The document is accessed on a website using Webticket to authenticate the user.

Reason:

I was getting tired of creating and mapping the users manually (maybe there is a smarter way, please do tell if so), so I started reading blogs/documents/community posts about Qlikview API on how to dealing with this issue.

I found alot of examples, but a lot of them with errors in it, or not suitable for my solution.

Since I work mostly with BI and we have a large BI setup, my primary thoughts were to build an automatic SQL table with all my users and reportnames due to i always has this information in my BI backend.

Therefore i created a table containing all information about my users and which documents they should be able to access.

As a sidenote I also created a BOOL/BIT field called IsActive, so that i could delete the calc and username from the report if it was inactive.

UserNameDocumentNameIsActive
ADocumentA.qvw1
BDocumentB.qvw1
CDocumentC.qvw0

Next step was to code a program so it could automatically assign a user to the document and assign a Document CAL.

Code walkthrough:

This is only the code for creating and deleting - If you want to connect to the API there is some settings that needs to be done first. If you have any troubles in this, you can contact me, and I will try to help you

// I have downloaded a (dapper) to create this class.

class UserReport

    {

        public string UserName { get; set; }

        public string DocumentName { get; set; }

        public bool IsActive { get; set; }

    }

    class Program

    {

//I have downloaded something called Nlog to log when i create and delete a user - Hereby i get the information when the user/calc was assigned - I use it to report to Qlikview how many calc's we have used.

          static Logger logger = LogManager.GetCurrentClassLogger();

         static void Main(string[] args)

        {

        

            //List();

            //CalInfo();

           //ListDMSUsers();

            //RemoveDMS();

            AddCAL();

            Console.ReadKey();

        }

public static void AddCAL()

        {

            try

            {

                // Initiate backend client

                var backendClient = new QMSClient();

                //retrieve a time limited service key

                ServiceKeyClientMessageInspector.ServiceKey = backendClient.GetTimeLimitedServiceKey();

               //clear the QMS's QVS object cache from user document listings - so that the any new user documents can be discovered

                backendClient.ClearQVSCache(QVSCacheObjects.All);

               // get a list of QVS services

                List<ServiceInfo> qvsServices = backendClient.GetServices(ServiceTypes.QlikViewServer);

                // get a list of all documents on the server

                List<DocumentNode> userDocuments = backendClient.GetUserDocuments(qvsServices[0].ID);

              

   // Connect to my SQL table - Reference to my class UserReport - Adding my usernames,documentnames and isactive info to a list

               string connectionString = "Data Source=XXXXXXX;Initial Catalog=XXXXXX;User Id=XXXXXX;Password=XXXXXX;";

                List<UserReport> userReports;

                using (var Sqlconnection =new SqlConnection(connectionString))

                {

                    userReports = Sqlconnection.Query<UserReport>("SELECT UserName, DocumentName, IsActive FROM                     usernames").ToList();

                }

                var documentInfos = userDocuments

                    .Select(x =>

                        new {

                         //Set all to lower

UserReport = userReports.FirstOrDefault(y => y.DocumentName.Equals(x.Name,StringComparison.InvariantCultureIgnoreCase)),

                            Document = x,

                            //Retrieve documents metadata

                            MetaData = backendClient.GetDocumentMetaData(x, DocumentMetaDataScope.Licensing)

                    })

//Making sure of that i only match and check by reports from the server with the same name as in my table

                    .Where(x => x.UserReport != null)

                    .ToList();

// Assign 1 document calc to a document - Makeing sure that my IsActive field is 1 = true and that the document has no calcs allocated

                foreach (var documentInfo in documentInfos.Where(x => x.UserReport.IsActive && x.MetaData.Licensing.CALsAllocated == 0))

                {

                    // Setting calcs allocated to the document to 1

                    documentInfo.MetaData.Licensing.CALsAllocated = 1;

                    //Adding the username to the report - Using GetNamedCal from a Method (see buttom of code)

                    documentInfo.MetaData.Licensing.AssignedCALs.Add(GetNamedCal(documentInfo.UserReport.UserName));

                     //Creating the log file using Nlog - Also writing it out to console

                    var log = documentInfo.UserReport.UserName + " added to document - " + documentInfo.UserReport.DocumentName;

                    logger.Info(log);

                    Console.WriteLine(log);

 

                    backendClient.SaveDocumentMetaData(documentInfo.MetaData);

                }

// Remove document calc and user - Makeing sure of that my IsActive is 0 = false and that the document has a calc assigned

                foreach (var documentInfo in documentInfos.Where(x => !x.UserReport.IsActive && x.MetaData.Licensing.CALsAllocated > 0))

                {

                    var cal = documentInfo.MetaData.Licensing.AssignedCALs.First(x => x.UserName.Equals(documentInfo.UserReport.UserName, StringComparison.InvariantCultureIgnoreCase));

                    // Remove the username

                    documentInfo.MetaData.Licensing.AssignedCALs.Remove(cal);

                    //Set the allocated calc to 0

                    documentInfo.MetaData.Licensing.CALsAllocated = 0;

                    //Writing to log again

                    var log = documentInfo.UserReport.UserName + " removed from document - " + documentInfo.UserReport.DocumentName;

                    logger.Info(log);

                    Console.WriteLine(log);

                   //Save all the metadata back to the documents so they are updated in QMC

                    backendClient.SaveDocumentMetaData(documentInfo.MetaData);

                }

//Clearing all cache so that I make sure that all my fields are updated (If this is not cleared, you can have trouble in seeing how many calcs there are in use)

                if(documentInfos.Any())

                    backendClient.ClearQVSCache(QVSCacheObjects.All);

            }

            //Retrieving any erros there might be

            catch (System.Exception ex)

            {

                Console.WriteLine(ex.ToString());

            }

            Console.WriteLine("Done!");

        }

        //Creating a method for assigning a username

        private static AssignedNamedCAL GetNamedCal(string username)

        {

            var namedCal = new AssignedNamedCAL();

            namedCal.UserName = username.ToLower();

            return namedCal;

        }

Please let me know if you have any ideas or thoughts on how you would do it

//Thomas

Labels (3)
Comments
Not applicable

If anyone has issues in solving their problems or they want the program, feel free to write to me. It can be a bit hard to understand some lines of code without a context

0 Likes
Not applicable

Hello,

I want to try to delete a Document CAL. I used your example but i had the following exception:

System.Exception: Too many document CALs assigned

This is my code:

Meta = client.GetDocumentMetaData(allDocs, DocumentMetaDataScope.Licensing);

Meta.Licensing.CALsAllocated = 0;

Meta.Licensing.AssignedCALs = Meta.Licensing.AssignedCALs.Where(val => val.UserName.ToUpper() != userId).ToArray();

client.SaveDocumentMetaData(Meta);

Any ideas?

Thanks,

Luca

0 Likes
Contributor III

Hi Thomas,

please, can you share your C# project?

Can you explain how to use it in Qlikview Server?

Best regards

Luca Jonathan Panetta

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-06-05 07:39 AM
Updated by: