Getting SharePoint data into QlikView

There are already a number of articles on how to do this, but after reading through them and continuing to struggle with it, i decided it was time to also jump in to these waters and try to bring some clarity.

Here is what you are going to need:

  1. The url to use with the owssvr.dll
  2. If you not using the default view, or more than just the default fields in that view, you will also need the views underlying GUID .

The general form of the URL

[sharepoint site url]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[GUID for List]&View=[GUID for view - optional]

The GUID for the list is something that you can scrape out of your browser when you access your sharepoint site. Think of it as the record ID for the Sharepoint display. If you don’t specify the view information, you will automatically get the default view and the default fields. If you need more information, then get the view GUID from your SharePoint admin. In my case there were some additional fields that were needed that are not normally displayed, like some internal record keys that link to a different system. If you are not using the additional view information, just leave off everything after the list, so leave off the &View= segment entirely.

You can test out the URL with any web browser, but my preferred method is to use Microsoft XML Notepad 2007 because it presents the data in a nice XML format. If it returns results, then you can move ahead to using the QlikView Web Source wizard. From the script editor, select “Web Files” and it will ask you for an Internet File – enter the URL here.

When you click Next, it should return some results, but it will try to interpret it as delimited text. Change the file type to XML, and it should render into something usable.
It will provide you 3 tables:
xml
xml/data/row
xml/Schema/ElementType/AttributeType

Click finish. It will write the script to load the three tables.

Now for the fun part – making it usable.
The xml table which loads last is basically just a header record and does not provide anything useful, so you can comment that out or just delete it from the script.

row contains all of your rows of data, and if you want to just run with that, you are fine – except the field names will not match what is displayed in SharePoint.That data is in the AttributeType table that it brought in.

The load for the AttributeType table looks like this – with one piece that I added at the end

AttributeType:
LOAD name,
[rs:name],
[rs:number],
[datatype/dt:type] as [dt:type],
[datatype/dt:maxLength] as [dt:maxLength],
[datatype/dt:lookup] as [dt:lookup],
%Key_xml_98C49F546A18E856 // Key to parent table: xml
FROM [http:[sharepointURL]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[ListGUID]&View=[ViewGUID]] (XmlSimple, Table is [xml/Schema/ElementType/AttributeType]);

FieldNameMap:
Mapping LOAD
name,
[rs:name]
Resident AttributeType;

This creates a map for the underlying field name to the display name.

To rename the fields in bulk turns out to be simple

RENAME Fields using FieldNameMap;

The remainder of the script

DROP Table AttributeType;
RENAME Table row to mySharePointData;
DROP Field %Key_xml_98C49F546A18E856;

Renaming all of the fields with a map will rename any field that it has a translation for, but pass through any of the ones it doesn’t recognize. If there are any fields that you don’t want the map to touch such as a field that will create synthetic keys, all you have to do is tweak the code for row and rename the field on the way in to something that is not in the map.

About escaping the GUIDs:

i did not have to unescape the GUIDs that I got from the SharePoint admin. If you are not familiar with that term, it means restoring the dashes and brackets to the URL. The { character gets converted to %7B, } becomes %7D, and – becomes %2D. I have seen some posts that insist you have to change them back, but that was not my experience.

Advertisements

Creating a clear button for the LookupEdit control on Active Forms

The SalesLogix LookupEdit is a screen control that allows you to look up information from a table, capture the record id, and display the friendly name. It is very useful, and widely used.

The one thing that it is missing is the ability to clear out what is in it. i.e. what if you select something and change your mind and need to “un select” it. Oops… sorry – no can do.

I recently needed to enable people to clear things out, and the form had a number of lookups on it, so I took the extra time to turn it into a function. So… here is how you do it.

Step 1 – dim a variable to be global to the form – put this at the top of the code before any other code

dim FormRef

Step 2 – in AXFormOpen, capture a reference to the form.

set FormRef = Sender

Step 3 – on your form, name your button and your lookup edit the same way. So if you have a lookup edit called lueMyLookup, then name your button btnMyLookup. You can create as many of these pairs as you need, as long as you follow the naming convention of starting your lookup name with lue.

Step 4 – in the code behind for the form, insert this subroutine and attach it to the button(s).

Sub btnClearLookupClick(Sender)
dim controlname, i, oCtl
controlname = "lue" & right(Sender.Name, Len(Sender.Name)-3)
For i = 0 to FormRef.ControlCount - 1
If FormRef.Controls(i).Name = controlname then
set oCtl = FormRef.Controls(i)
oCtl.LookupId = ""
oCtl.Text = ""
exit sub
end if
next
End Sub

What this does is to use the button name to figure out what the lookup control is called, and then runs through the list of controls to find it. Once it is found, it then clears the lookupid and text properties.

See, that wasn’t so bad, was it?

ws

SalesLogix multi-select picklists in QlikView

The multi-select pick list in SalesLogix has long been one of the biggest nuisances when it comes to reporting off of your database. The good news in QlikView / Visual Analyzer is that it is a simple thing to remedy.

As an example, let’s use the USERINFO table in SalesLogix. When you set up a user, you can select multiple departments. (I won’t get into how this breaks down, it just makes a good example for now).

In the QlikView load script:

UserInfo:
LOAD USERID, DEPARTMENT AS SOURCE_DEPARTMENT, USERNAME;
SQL SELECT USERID, DEPARTMENT, USERNAME FROM SYSDBA.USERINFO;

Departments:
LOAD USERID, TRIM(SUBFIELD(SOURCE_DEPARTMENT, ';')) AS DEPARTMENT
RESIDENT UserInfo;

You now have a sub-table off of UserInfo called Departments with a one to many relationship.

Miscellaneous Notes:
TRIM is used to take care of any leading spaces,
Semi colon is the delimiter that SalesLogix uses to separate the selections.

As with any 1:M table – you have to watch out for double counting if you are rolling up by department (or any sub-table for that matter).

Last of all – make sure that none of your picklist values have the semi colon character in them – seems obvious from here…. but we all know how creative people can get here.