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

Advertisements

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.