donderdag, juli 14, 2016

Metadata update BI4 vs Sharepoint

With the implementation of BI4, our company moved from producing .xls files to .xlsx files. All good and well, but that brings some unexpected consequences as well.


Every day, we produce some 80 reports, that get stored on a SharePoint Server. Every file has some custom properties in SharePoint, like a periodicity and a Group.


With .XLS files, this metadata gets stored on one place only : SharePoint. However, when you work with .XLSX files, you may run into some trouble -- because the Metadata is then stored INSIDE the document. Given the fact that some of the properties are custom, there is no way to update the metadata, until you have actually uploaded the document to SharePoint -- and if that file came from Business Objects, you just overwrote your Metadata with BLANKS.


This article will provide you with a work-around.


=========================


First some VBScript code:


       Set oBook = oExcel.Workbooks.Open(objFile)
       oBook.saveas strDestFolder & Replace(objFile.Name, "$", "\"), 51
       oBook.ContentTypeProperties("Group").Value = sGroup
       oBook.ContentTypeProperties("Period").Value = sPeriod
       oBook.ContentTypeProperties("Description").Value = sDescription
       oExcel.AutomationSecurity = 1
       oBook.save
       oBook.close
       oExcel.quit


Before you implement this, you will be needing to write a script that will copy your Excel documents to SharePoint. If you need help with that part, probably the above code will do you little good.


It took me a good deal of time, to write the code above -- very happy with it. What I'm doing in the rest of the script is run through a text-file to locate the file I'm copying --and I store the current description and group in it.


The script above will then store that onto the SharePoint server, after saving the file to that location.


For you Powershell lovers:


$objFile = $objExcel.workbooks.open($OutFile)
            $builtinProperties = $objFile.ContentTypeProperties
            foreach($builtinProperty in $builtinProperties){
                if ($builtinProperty.Name -eq "Description"){
                    $builtinProperty.value = $Description
                }
                if ($builtinProperty.Name -eq "Period"){
                    $builtinProperty.value = $Period
                }
                if ($builtinProperty.Name -eq "Group"){
                    if($group[1] -eq "B00"){                    
                        $builtinProperty.value = "Bank"
                    }
                }
           }
           $objFile.Save()


I have no good PowerShell editor, so the above code took me even longer to put together.. it does the same as the vbscript.


So the workaround is basically : use VBScript to copy your Excel file to SharePoint. Open it with VBScript, read the metadata from a textfile.. and then, using the code above, to set the metadata.


I hope it helps you -- I'm pretty sure it will help me, if I ever need to figure this out again.


Peter



dinsdag, juli 05, 2016

Of BI 4 and why you should always purge your queries.

Purge is an underused function.


I have explained this to my students. You should always purge your documents. It's easy to do, it's the right thing to do. But now that I have had some months of experience with BI 4, it's even more important.


BI4 will never be my best friend. The Toolbar is very poorly designed.
There are 3 functions EVERY user will use, EVERY time.


1) Refresh
2) Next Page
3) Save as Excel





This is the refresh button. If you cannot locate it .. it's the tiny little thing just to the left of the table icon and just to the right of the edit query button.


Here is my suggestion.. it's ugly, but even this one is better:





The same is valid for the next page and previous page buttons. They're tiny and away from everything else.. put them big and bold next to that refresh button.


And the Save to Excel button.. big and bold next to those.


And PLEASE .. PRETTY PLEASE.. get rid of the Tabbed design. It S***S. Either do it right, or don't do it at all. At least the tabbed design in MS Office makes some sense.
Mostly because it's possible to customize them and put the buttons you use regularly on one tab.


Get a hint from the menu structure from Desktop Intelligence and turn that into a tab-design.


But I'm getting a bit carried away here :)


The most important message I want to give : ALWAYS purge your document.
BI 4 has the nasty habit, of sending the result of the stored query, if refreshing didn't work out.


That's right ladies and gentlemen. You read it right. BI4 will occasionally just send you the result of the STORED data. Not the NEW DATA.


So, PURGE your document -- if your document comes back EMPTY -- you'll know that something went wrong. When the previously stored data comes back, it won't be so clear.


Sorry for the rant. But I thought it was important.



donderdag, maart 24, 2016

BO XI R3 Error 404 when viewing History of schedules

This is an error we ran into as we were migrating to version 4 of Business Objects.


Our old server runs BO XI 3, and our new laptops have Internet Explorer 11. Obviously, IT was not going to patch the old BO server -- but when we wanted to consult the history of schedules, we got Error 404.


Here's the workaround :




Step 1: Click right on a report and select “History”


This will give an error 404


Step 2: Click the back button of the browser


Step 3: Double click the report


Step 4: Close the report with the small x in the top right corner.


 


This will bring you to the report schedule history.




So proud I found that out myself :) Ok ok.. I just got lucky. But happy anyway.

dinsdag, maart 24, 2015

Workdays between in Business Objects

Excel has a nice function : networkdays() -- it will show you the net amount of working days between two dates (excluding weekends).

Business Objects doesn't have this -- it only has "daysbetween()"

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5) + ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

This is the formula I found on
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/

I had worked out a procedure of my own, but this one is way shorter -- although not easier :D

let's break it down:

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5)

First we get the number before the comma, when dividing the number of days between two dates by 7. That gives us the number of entire weeks in the period.

Next, that number is multiplied by 5 and we have the basic number of weeks.

But because we did a Truncate, we lost a few days.

And that's part of the formula I'm taking my hat off for.. wow..

=ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

One day I'll be able to write constructions like these :)

Peter


vrijdag, maart 20, 2015

Verifying Bank account numbers in Business Objects

Today, I was asked to do a data-quality check on bank account numbers -- of course, since I live in Belgium, I will focus on the Belgian bank account numbers, but the system is pretty much the same elsewhere -- and I'll be focussing on the limitations of Business Objects and how to get around them.

The basic calculation function that is being used is Modulo 97.

We'll be considering the following fictious IBAN Bank account number:

BE74857221143209

(I used http://www.mobilefish.com/services/random_iban_generator/random_iban_generator.php to generate a random number)

The first part we need to do, is move the left 4 characters to the right.

[strInvertedBacc]=Substr("BE74857221143209";5;Length("BE74857221143209")-4) + Left("BE74857221143209";4)

Which gives us:

857221143209BE74

Next, we need to replace the letters with numbers.

A = 10
B = 11
C = 12
D = 13
E = 14
....
Z = 35

Replace(Replace([strInvertedBacc];"B";"11");"E";"14")

I know this is a shortcut -- but I'm being pragmatic here -- to take into account all possible countrycodes would make the code way too complex.

So now our number has become:

857221143209111474

Now we perform the function tonumber() on it and..

Here we have the first indication why I'm writing this article. Business Objects makes a mistake and the number displayed is :

857221143209111000

That would be bad.

The solution in Business Objects is to treat the number in two halves. Excel has an even lower treshhold for error on this kind of calculation. There you have to work in groups of 5.


Mod(ToNumber(Left("857221143209111474";9));97)

gives us our left numbers

[leftnumbers] = 36

We turn that number into a string and we stick the right 9 numbers to it

=formatnumber([leftnumbers];"0") + Right(857221143209111474;9)

[newnumber] = 36209111474

which we turn into a number
And we perform another Modulo 97 on that:

= mod(tonumber([newnumber]);97)

= 1

And if the result is 1 -- the number is correct.

The total formula is:

=Mod(ToNumber(FormatNumber(Mod(ToNumber(Left("857221143209111474";9));97);"0") + Right("857221143209111474";9));97)

That's how it's done.

Enjoy,

Peter





maandag, april 28, 2014

VBScript CSV to JSON

'CSVToJSON

'Purpose : Convert an existing CSV file, to a JSON file.

'Method: drag a file over this script. It will parse your textfile and convert it to a JSON file.

set fso = wscript.createobject("scripting.filesystemobject")


if wscript.arguments.count > 0 then

 for each x in wscript.arguments

  iLines = 0

  strOutFile = x

  fso.movefile x, replace(x,".csv", "_input.csv")

  set objInFile = fso.opentextfile(replace(x,".csv", "_input.csv"))

  set objOutFile = fso.createtextfile(replace(x, ".csv", ".json"))

  

  'read header

   objOutFile.writeline"var data={""users"":["

   strHeader  = objInFile.readline

   strHeader = replace(strHeader, """", "")

   arrTitles = split(strHeader, "|")

   
  do until objInFile.AtEndOfStream

   strLine = objInFile.readline

   strLine = replace(strLine, """","")

   arrLine = split(strLine, "|")

   iCount=0

   iLines = iLines + 1

   objOutFile.writeline "{"

   do while iCount <= ubound(arrTitles)

    if iCount < ubound(arrTitles) then

     objOutFile.writeline """" & replace(arrTitles(iCount), " ", "_") & """" & ":" & """" & arrLine(iCount) & """" & ","

    else

     objOutFile.writeline """" & replace(arrTitles(iCount), " ", "_") & """" & ":" & """" & arrLine(iCount) & """"

    end if

    iCount = iCount +1

   loop

   objOutFile.writeline "},"

  loop
  objOutFile.writeline "{"
  objOutFile.writeline """O"":""O""}], ""count"":""" & iLines & """"
  objOutFile.writeline "}"
 next
 msgbox "Done."
else
 msgbox "drag csv files over this script for it to work !"
end if

zondag, februari 09, 2014

Business days between two dates (in Business Objects)

A colleague of mine asked me: is it possible to calculate the amount of working days between two dates. Immediately, I thought : of course, but as it turns out, it's not that easy.

A quick google and I found this formula:


=DaysBetween(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End])))) -Floor(DaysBetween(RelativeDate(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));(DayNumberOfWeek(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin]))))*-1)+1);RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End]))))/7)*2

I'm sure there's an easier way, and I'm working on it, but for the time being, it does the job just nicely. Thanks to this guy's post: http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html

Thanks mate. Loved the article.

Peter