AutoIT – Exporting QlikView data to Excel

As I showed before QV has a COM interface.

So we can use it in our AutoIT scripting event to extract data from the QV client (or any other object !) and save it to Excel.

It basically means you can manipulate the data in the QV Document and export to Excel or upload to SharePoint is you like.

Even the graphs are exportable.

Here we go for a simple example.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$Qview = ObjCreate("QlikTech.QlikView")

if IsObj($Qview) Then
	MsgBox(0,"",$Qview.OSName & " " &$Qview.OSVersion)
Else
	MsgBox(0,"","NOK")

EndIf


$Qview.MsgBox("QlikView Messagebox Qv_Version " & $Qview.QvVersion )

$Qview.OpenDoc("C:\_\QlikView\SALES.qvw")

WinSetState("QlikView", "", @SW_MAXIMIZE)

$ActiveDoc = $Qview.ActiveDocument

; INSERT HERE and start every call with "QvDoc."
Local $Sheet, $obj, $w, $h

$Sheet = $ActiveDoc.ActivateSheet("DataSheet")

; Right CLick on the object to see the Object ID, don't use the Sheet ID !
$obj = $ActiveDoc.GetSheetObject("TB02")

$w = $obj.GetColumnCount
$h= $obj.GetRowCount

If $h >=  1 Then
	; Export the full object
	ConsoleWrite( $w  & " " & $h & @CRLF)
	$obj.ExportBiff(@ScriptDir & "\QV_Export_Test.xls") 
EndIf

; Get the distinct ItemGroup Field Values
$Values = $ActiveDoc.Fields("ItemGroup").GetPossibleValues 

for $i=0 to $Values.Count-1
  $curVal = $Values.Item($i).Text
  ;Select is like a click selection in the QV Client
  $ActiveDoc.Fields("ItemGroup").Select ($curVal) 
  ; Exports a file for each ItemGroup Field Distinct Selection, see above
  $obj.ExportBiff(@ScriptDir & "\QV_Export_Test" & $curVal &".xls") 
  ConsoleWrite($curVal & @CRLF)
next


ConsoleWrite(@ScriptDir & "\QV_Export_Test.xls" & @CRLF)

$Sheet.FitZoomToWindow

ConsoleWrite($ActiveDoc.GetVariable("MyVarTest") & @CRLF)
ConsoleWrite($ActiveDoc.GetPathName & @CRLF)

;$Qview.Quit


Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
	"err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
	"err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
	"err.number is: "         & @TAB & $HexNumber              & @CRLF & _
	"err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
	"err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
	"err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
	"err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
	"err.helpcontext is: "    & @TAB & $oMyError.helpcontext)
   SetError(1)
Endfunc

Nice to see such a seamless cooperation. A rather new BI technology supporting an old COM interface.

This make me happy

SharePoint – Server High CPU !

Very often you will see a SharePoint (backend) server with High CPU ?

In our case this is not different. Especially when the SharePoint Backend server also functions as a SharePoint Search Server.

We have 1 Virtual Server where the SP Search Services are installed on as well as the Microsoft Search Server 2010 Express.

Every time when the timer job start the Search crawling actions, it hit the roof. In most cases after a reboot of the server it goes to 100% !

To keep the noise down you can apply this trick.

Go and look for all the  Search Processes running.

And set for each one the Process Priority to LOW

As well as the Affinty to 1 CPU Core.

This should keep it down untill a next reboot of course

Or you can apply a PowerShell Script for this

CLS

$ret =  Get-Process mss* | Select-Object ID, name, Description, ProcessorAffinity, PriorityClass

echo $ret | Out-GridView 

This give you a glimp of the current settings.

I will add the Set Affinity / Priority script later.

They can explain it better then I do.

http://blogs.technet.com/b/heyscriptingguy/archive/2010/04/12/hey-scripting-guy-april-12-2010.aspx

Some extra tuning that can be done is to set the EnterpriseSearchService Performance level to REDUCED.

You need to use this PS command.

CLS

Get-SPEnterpriseSearchService | select PerformanceLevel

# If it shows PartlyReduce you can set it to "REDUCED"

Set-SPEnterpriseSearchService -PerformanceLevel Reduced;

Enjoy!

QlikView – How to Consume a WebService

First get the proper webservice URL.

Example: http://wsf.cdyne.com/WeatherWS/Weather.asmx/GetCityForecastByZIP?ZIP=33139

Open QV go the the Script and press the button WebFile.

Paste your WebService URL

Select the fields you want

And add them to an Object

It’s not getting hot today

AutoIT – using the QlikView COM Interface

Once you get your data source loaded in QV. For example a SharePoint data source.

You can use AutoIT to interact with the QV Client COM interface.

Look for the QV Automation Interface Reference Guide for more information.

Example :

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$Qview = ObjCreate("QlikTech.QlikView")

if IsObj($Qview) Then
	MsgBox(0,"",$Qview.OSName & " " &$Qview.OSVersion)
Else
	MsgBox(0,"","NOK")

EndIf


$Qview.MsgBox("QlikView Messagebox Qv_Version " & $Qview.QvVersion )

$Qview.OpenDoc("C:\_\QlikView\Sales.qvw")

WinSetState("QlikView", "", @SW_MAXIMIZE)

$ActiveDoc = $Qview.ActiveDocument

; INSERT HERE and start every call with "QvDoc."

$Sheet = $ActiveDoc.ActivateSheet("DataSheet")

$Sheet.FitZoomToWindow

ConsoleWrite($ActiveDoc.GetVariable("vCurrentYear") & @CRLF)
ConsoleWrite($ActiveDoc.GetPathName & @CRLF)

;$Qview.Quit


Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
			 "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
			 "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
			 "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
			 "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
			 "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
			 "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
			 "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
			 "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
			)
  SetError(1)  ; to check for after this function returns
Endfunc

This example will open the Client. Show some version information and some other tricks.

And then open a specific Sheet in the QV document.

Of course you could also read data from the sheet and reuse this in your AutoIT application.

On top of this the QV Client supports VBScripts and JScript as a macro language.

How great is this all !

SharePoint List – Extract a MultiLine Text Field (HTML)

Ever wanted to extract a multiline text field from a SharePoint List.

And this in a fast way

CLS

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$WebURL = "http://YourSite/"

#List
$listName = "YourList"

#Activity
$ID = "1"
$TextField = "Your MultiLine Text Field Name"

#Get the Web & Lists using the SPQuery (fast way)
$Web = Get-SPWeb $WebURL

        #$WebUrl = $site.OpenWeb()
        $list = $web.Lists[$listName]

        $query = New-Object Microsoft.SharePoint.SPQuery

        $query.RowLimit = 100

        $query.Query = "<where><eq><fieldref name="ID" /><value type=" Number">"+$ID+"</value></eq></where>"

        $items = $list.GetItems($query)

            foreach($item in $items){

            Write-Host "ID = " $item.ID

            Write-Host "Activity ID : " $item.ID "-" $item.Title -nobr
            Write-Host ""

            Write-Host $Text
  }

Notice the CAML query that grabs the record ID first. That will speed up the process tremendously.

Enjoy!

SharePoint – Adding a Print function to the Ribbon

Sometimes it would be nice to have the print icon at hand from the Ribbon or even the List Item dropdown menu.

Clicking the icon will bring up the Printer pop-up window.

It prints in this case to a PDF file.

To accomplish this you need to open the SPDesigner, go to the relevant List. And add a new Custom Action.

Add this JavaScript code to the button.

javascript:window.print();

Add a nice print ICON rightsMask and Sequence to it and that is it.

Adding a list Item Menu is simular, but you need to start for the SPD menu.

Make sure you add the icon to the 16×16 Button image URL Location.

 

Or you can as well add a print button, attached to a JQuery click event.

<td><button class=”printMe” type=”submit”  style=”font-size: x-small; font-family: Verdana; width: 100px; height: 23px;” >Print</button></td>

$(‘.printMe’).click(function() {
  window.print();
  return false;
  });

<script type=”text/javascript”>
$(document).ready(function() {

$(‘.printMe’).click(function() {
  window.print();
  return false;
  });

});
</script>

Enjoy !

PowerShell – How to rename a SharePoint List URL

Sometime you created a list and afterwards change your mind about the name of the list.

You can easily change the list Title and Description. But is does not change the URL accordingly

So here’s where PowerShell comes into play.

CLS

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$web = Get-SPWeb "http://YourWeb/sites/"

$list = $web.Lists["OLD Name"]

# Display the fields, so to make sure you are grabbing the correct list in the correct site collection
$list.Fields | sort StaticName,Type | ?{$_.CanBeDeleted -eq $true -and ´
              $_.Hidden -eq $false} | FT Title,StaticName,Type

# Check the name name and destination before
# $list.RootFolder

$list.RootFolder.Name
$list.RootFolder.Url

$web.Lists["OLD Name"].RootFolder.MoveTo("/Lists/NEW Name")

# Check the name name and destination afterwards
$list.RootFolder.Name
$list.RootFolder.Url

As you can see it is using the MOVETO method to rename the list.

TIP :

Notice the new name path

It has the “/LISTS/” path in front ! this is importain to locate the new list in the same position as that other native SharePoint lists.

Of course if you don’t like to play with PS then you can still use SP Designer to do the job.

PowerShell – Invoke-RestMethod Error !

When running the PS Invoke-RestMethod, I got the connection closed Error ?

Invoke-RestMethod : The underlying connection was closed: An unexpected error occurred on a send.

Looking on the net there are a lot’s simular posts coming up, providing solutions that had to do with avoiding certificates validation checks when running the script ?

like adding this to the script :

add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@

[System.Net.ServicePointManager]::CertificatePolicy = ´
                        New-Object TrustAllCertsPolicy

Or

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}

But, none of them worked out well?

So the solution is here :

When specifying an URI for the Invoke-RestMethode, don’t pre-fix it with HTTP:// or HTTPS://

Example :

Invoke-RestMethod -Uri "gdata.youtube.com/feeds/api/videos?v=2&q=PowerShell" `
| Select-Object Title, Author, Link

Result :

As simple as that

QlikView – Access Data on SharePoint

QlikView is a nice BI Tool that is lightweighted and fast.

On top of that the Client version is free of charge and gives you access to the full features. There other limitations and that is you can’t share your work using the free goodie !

But here are some examples on how to access SharePoint Data.

Prerequisites :

1. WebClient Service

You need to make sure that you have the WebClient service installed and running on the server and the user has access to the SharePoint library.

To install the service on the server follow these steps:

Start the Windows Server Manager.

In the tree view, highlight the Features node.

In the details pane, click Add Features.

In the Add Features Wizard, check the Desktop Experience box, and then click Next.

Click Install.

When the Add Features Wizard has finished

Click Close.

Click Yes when promoted to restart the computer.

2. Authentication

Adding the SharePoint site to local intranet sites in the security tab in internet explorer on the machine that QlikView Server runs on.

More info…

As I understand, QlikView uses the IE authentication model – So if you can access your SharePoint site without a sign-on then you can also get list data from a script using owssvr.dll method.

Assuming you are using AD authentication to the SharePoint server.

If your PC is on the same domain as that used for SharePoint authentication, just add your SharePoint site to the “Local Intranet” zone in IE.

If your PC is not in the same domain, you will also need to authenticate and make sure you check the ‘Remember Password’. This will store your credentials in the windows Credentials Manager.

Examples :

1. Accessing SharePoint Document Libraries

Open a new QVW Project and open the Script editor.

Press the EDIT Script ICON and press the WEB FILE button.

This will bring up the File Wizard

QlikView accepts 2 different URL formats.

A. WebDav URL

Fill in your WebDav URL to the Doc. Libr.

\\YourSPSite\DavWWWRoot\sites\…\Doc.Libr.Name\FilenName.XLSX

Press next and see the data is available for further processing

B. HTTP URL

Use the URL to retrieve the file.

Use the Browser Copy shortcut of the file to get the URL.

2. Accessing SharePoint Lists

Same procedure as above to bring up the WEB FILE Wizard.

But using a different URL structure. SharePoint supports 2 interfaces :

A. URL Protocol (Remote RPC)

Syntax : http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

This one is the better one because you can filter or use a specific SP View

The following parameters can also be used in the protocol:
• FileDialogFilterValue to set filters for a view and to return the list of all files of a specified type from a document library according to file name extension (for example, *.doc, *.ppt, or *.xls).
• FilterField n to specify the name of a field in the database, where n is an integer that is limited only by the number of fields allowed in the database table or by the length allowed for the URL field.
• FilterValue n to specify the string value on which to filter a field, where n is an integer that is limited only by the length allowed for the URL field.
• SortField to specify the name of the field on which to sort.
• SortDir to indicate an ascending (asc) or descending (desc) sort order.
• Using to specify the relative URL of a virtual file used in exporting an item or list, which can be one of the following values: vcard.vcf, event.ics, query.iqy, or query.bqy.

Example :

B. Web Services API

Syntax : http://your-sharepoint-server/site/ICT/_vti_bin/listdata.svc/ListName

Keep in mind the oDdata or REST protocol has a few disadvantages (no VIEWS can be selected, and it returns only 1000 records max)

You can uses Filters and Sort syntax though.

 

On top this QlikView is COM visible, so you can pick this up in your scripting adventures.

Here is an Example on how to access the QV Client using the COM interface

See also here : SharePoint Interface

Programming skills are not the same as Technical skills ?

Being a Developer does not require that you have good Technical and Protocol skills alone these days.

First of all you need to master many Technologies, that are best fit to support the Business scenario’s and requirements.

Technologies in a Windows Environment are piling up each year.

You should know what is :
– WPF Windows Presentation Foundation
– MCF Windows Communication Foundation
– WWF Windows Workflow Foundation
– WIF Windows Identity Framework
– WMF Windows Management Framework
– WSE Web Service Enhancements
– WEF Windows Entity Framework

Depending if the requirement is a Client Side application or a Web Based Server Side Business Solution.
You would need to pick either one of them or even a mix of both.

You would at least need to learn what CLR (Common Runtime Language) is. And what is IL- CLR- CTS-CLS-and-GIT.

Or DLR is not the same as CLR

Maybe a bit too much all together for scripters.

But let’s have a quick overview of which kind of Technologies we can pick from.

.NET (versions 1 to 4.x) versus Java(Script)

Application Architecture
– Object Oriented Programming
– Service Oriented programming

Web Oriented Architecture
–XML                      – JSON
* XML/A                  – AJAX
* CAML                    – DOM
* XAML (mobile)
– CSS (3)               – SOAP
– HTML (5)             – REST
– Java(Script)         – Web API / ASMX

Enterprise Data Architecture
– SQL (AZURE)
– OLAP
– Odata
– OLEDB / ADO.NET
– LINQ
– SQL / MDX

Server Side Technology
– AD
– ASP.Net
– IIS
– SharePoint

Client Side Technology
– Powershell
– Webmatrix / Razor
– SQLite.Net / LDF databases

Programming Technology
– .Net Framework
* Assemblies / Classes / Objects
* .Net Reflection (late binding)
* .Net Reflection.EMIT ?
* Model View Controller (MV)

– JavaScript Libraries
* SP.Core.js
* SP.js
* JQuery.js
* Node.js
* KnockOut.js
* Angular.js
* …

Most of these Technologies you can access using smart Scripting Techniques.

I am trying to give some examples on in this Blog whenever possible.

Bottom line is that in this ever changing IT world keeping up and learning new Technologies is a necessity.

For some amongst us this is fun for others this is a real challenge 🙂