List field and paths from an RDL file

For a customer, I wrote this particular script. On a report based on a Model, it generates an Excel file listing all the fields and paths that have been used in the RDL file. All you have to do is store the rdl files in a directory somewhere on your c-drive, put the below script in a textfile, alter the path and save the file with a VBS extention :

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

targetfolder = "C:\foldername\"
set fso = createobject("scripting.filesystemobject")
set objfolder = fso.getfolder(targetfolder)
set fc = objfolder.files
set objexcel = createobject("Excel.Application")
objExcel.visible = true
objExcel.WorkBooks.Add
ObjExcel.Cells(1,1).Value = "Fieldname"
ObjExcel.Cells(1,2).Value = "Path"
ObjExcel.Cells(1,3).Value = "Table"
ObjExcel.Cells(1,4).Value = "Report"
z = 2
for each x in fc
set file = fso.opentextfile(targetfolder & cstr(x.name))
dim strLijn
do until instr(strLijn, "/Hierarchies") > 1
strLijn = file.readline
if instr(strLijn, "<Grouping Name=") > 1 then

objExcel.Cells(z,1).Value = mid(strLijn, 28, len(strLijn) - 32)

do until instr(strLijn , "/Path") > 1
strLijn = file.readline
if instr(strLijn, "!--") > 1 then
strpad = strpad & "\" & mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )
strlocatie = mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )

end if
loop
objExcel.Cells(z,2).Value = strpad
objExcel.Cells(z,3).Value = strlocatie
objExcel.Cells(z,4).Value = x.name
z = z +1
strpad = ""
end if
loop
strLijn = ""
next


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

use with caution :)

Binabik

Reporting services Picklists based on models

To force a picklist to have a fixed length :

1) In the picklist-query, add the field a second time (you'll use this one as label on your parameter) -- change the code for that field by right-clicking it and changing the formula (on the query panel)

for the example, I'll use a string-field that should be 7 characters long and should get leading zeroes -- a specific case I used this for.

2) change the formula to :
left("0000000"; 7 - length([nameofthefield])) & [nameofthefield]
if the field is numeric :

left("0000000"; 7 - length(text([nameofthefield]))) & text([nameofthefield])

That also sorts the picklist, but if you simply want to sort, just add the same field a second time.


Binabik

Default values in SQL Reporting

At the moment, I'm building some reports in SQL Reporting Services. A nice task, as always, and it does provide for some unexpected challenges once in a while.

Here's what happens in Reporting services when you use parameters – in fact, I had this particular issue quite a few times – I only just found how to get around it.

Problem description: When using a multivalue report parameter with all values selected by default, in fact, none is selected.

Solution: add the filter IS NOT NULL

I can't begin to explain how hard I hit my forehead with the palm of my hand when finding this one out J

Binabik

 

 

Scripted BO 5 excel export

 

A customer asked me to make the following script -- some time at google and some writing later, here's what I put together. It takes all the files in a given directory and converts all the BO reports into excel files (no charts though) each tab in each report is of course copied into the appropriate excel file.

Use with care..

'----------------------------------------------------------------------

dim busobj
dim strnomFichier
dim objrep
dim objExcel
dim boEditPopup
dim xlworksheet
dim strname
Dim BOApp
Dim strFilename

set BOApp= createobject("BusinessObjects.Application")
BOApp.LoginAs "USER", "PASS", False
BOApp.visible = True

Set fso=Wscript.CreateObject("Scripting.FileSystemObject")
Set f=fso.GetFolder("i:\")
Set fc=f.files

For each file in fc
    strFilename = file.name
    If Right(strFilename,3) = "rep" Then
    Set objrep = BOApp.Documents.Open("i:\"&strFilename)
    Set boEditPopup = BOApp.Application.CmdBars(2).Controls("&Edit")
    Set objExcel = createobject("Excel.Application")
    objExcel.Workbooks.Add
        objExcel.visible = True
        intreports = 1
        startnumber = BOApp.ActiveDocument.reports.count
     For  i = startnumber To 1 Step -1
        Set myrep = BOApp.ActiveDocument.reports.item(i)
        myrep.activate()
        boEditPopup.CmdBar.Controls("Cop&y All").Execute

        Set xlWorkSheet = objExcel.Worksheets.Add()
        strname = BOApp.ActiveDocument.reports.item(i).name
        strname = Replace(strname, ":", "") ' Can't contain this character
        strname = Replace(strname, "\", "") ' Can't contain this character
        strname = Replace(strname, "/", "") ' Can't contain this character
        strname = Replace(strName, "?", "") ' Can't contain this character
        strname = Replace(strName, "*", "") ' Can't contain this character
        strname = Replace(strName, "[", "") ' Can't contain this character
        strname = Replace(strname, "]", "") ' Can't contain this character
        strName = Left(strname, 31)
        xlWorkSheet.Name = strname
        xlWorkSheet.Paste
        Set xlFormatPopup = objExcel.Application.CommandBars(1).Controls("F&ormat")
        Set xlColumnPopup = xlFormatPopup.CommandBar.Controls("&Column")
        xlColumnPopup.CommandBar.Controls("&AutoFit Selection").Execute
     Next
     End If
     If Right(strFilename,3) = "rep" Then
         xlWorkSheet.Saveas "i:\"&strFilename&".xls", True
     End If
     Next

 

'-----------------------------------------------------------------

My new laptop

At the bottom of one of my cupboards, I found my old Toshiba laptop. It isn't much, it's got some ram.. not a lot.. some diskspace, not a lot.. and it's got Windows XP.

As a decent computer user, I prefer to only put legal software on my computer. Of course, that software has to function correctly and give me the features that I need.

I'm not a rich man, so I decided to go for the Open Source software that's around.

The first thing I decided on installing was office. I'm not planning to spend too many of my precious Euro's on office -- I won't be using it much on this laptop -- only to consult files that I receive from people mostly.

Open Office -- of course. A swift download, an install, and I have Spreadsheet, Wordprocessor, Image editing soft -- the lot.

Site : http://www.openoffice.org/

So.. what other software do I need. I develop websites -- so I need a webserver -- a database server --  content management -- blogs.. the choice is quite easy : MoWes (fka WoS)

Site : http://www.chsoftware.net/en/useware/mowes/mowes.htm

Great -- now I have a complete development environment with PHP, MySQL and all the nice stuff that comes with it -- but how will I be writing my webpages..

I searched the web over and found a long list of indivual tools like : NVU, Aptana, Selida -- none come close to a decent webdesign tool -- Aptana is coding -- I'm not planning to code my pages by hand if I can help it. Selida offers layers, but you can't do something simple like drag the layer to the desired location..

Site : 25 webdesign tools

A browser. I love Firefox, but the almost nihilistic interface of Chrome got me. Especially because in chrome, you can save a shortcut to a website on your desktop or in a folder. When you open the icon, you get that website in a window without navigation -- in practice, it looks like a windows program has been opened and not a website.

Site : http://www.google.com/chrome

Now I have my e-mail as an icon on my desktop -- I surfed to GMail with Chrome and saved the site as an icon -- it looks and acts like a windows program, with an important downside -- you have to be online to read your mail.

I did the same with the google calendar, google Picasa and google notepad. To store my office documents in a place where I can always reach them, I have also added Google docs -- easy.

site : www.google.com

I blog. So a decent blogging tool would come in handy : Windows Live. It contains LiveWriter in which I'm writing this article, live mail.. live messenger -- Live mail will come in handy to read those mails that are not accessible over a website.

site : http://get.live.com/writer/overview

Next Month

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",2,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Can’t believe I wrote that function.

=dateadd(“d”, –1, dateadd(“m”, +2, cdate(year(today()) & “/” & month(today()) & “/1”)))

that’s the expression that gives you the last day of the next month in Reporting Services

Calculating Current Month

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",1,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Installing BOXIR3

The installation of Business Objects XI R3 seems pretty straight forward. Double click the setup file.. next next next.. but.

After the installation, when I tried to surf to any of the pages on my Tomcat server, I got Error 404 application not found.

After a bit of surfing, I found an entry on a forum.

It was a discussion between two people that entirely pictured my problem.. what follows is a howto based on their conversation.

When trying to surf to http://servername:8080/CmcApp, I got an error 404 saying CmcApp not found.

1) go to

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\java\applications

and check if the WAR files are there -- if not -- reinstall

2) go to C:\Program Files\Business Objects\Tomcat55\conf and change the tomcat-users.xml file to contain a user (currently logged user f.i.) with rights

like such :

<user username="username" password="yourpassword" roles="admin,manager"/>

don't forget to restart the tomcat server (service)

3) surf to http://servername:port/manager/html (Tomcat Manager) and login with the username and password you just created

4) add all of the war-file (bottom of the page) from the directory you found in step 1.

5) try starting the CMC again

(this worked for me)

Into the blogging thing

I have been blogging a short while now -- just a couple of years. Only now, the whole process seems to be streamlining itself. My first articles, I wrote in the webinterface of the blogging website. harsh -- inspiration does not come at a given time.. so I would gather my stuff before the article and then write it on the site.

Then, I discovered BlogJet -- an easy, straightforward tool to blog. Configure it with login and password.. and go ! A big step forward.

Now, some time later, I'm writing in Live Writer from Microsoft -- and it is a definite step forward. The thing I like most is the fact that you can connect directly to the site -- and it copies the template you are using. this means, that you write the article inside a window -- and it should be what you see is what you get. Furthermore, you can connect to multiple blogs.. just select the right one -- the screen changes to the template of that site -- and go ! the article is published.

I'm a fan of Windows Live Writer.

Automatic BO Refresh Script

Store this in a textfile.vbs and change the folder in which your reports
or shortcuts to reports are stored (yes, shortcuts are ok too)This script will refresh them all (mind the object definition
for a BO 6 system.

download refreshscript

Business Objects Scripted refresh

a student gave me the following script to automate refresh of a report :

Sub RefreshBO()
Dim objBO, objrep
Application.DisplayAlerts = False
'Open Business ObjectSet objBO = CreateObject("BusinessObjects.Application")
' (when update to version 6)' Set objBO = CreateObject("BusinessObjects.Application.6")
objBO.LoginAs "username", "password", False' (fill in username and password)
'open the report
objrep = objBO.Documents.Open("c:\myfile.rep")

'Show BO
objBO.Visible = True

'Refresh Report
objrep.Refresh

'Save Report
objrep.Save

'Close Report
objrep.Close


great !!

SAP buys Business Objects

It has happened -- SAP is buying Business objects. Making them larger than Oracle (the other possible buyer who bought Hyperion instead). SAP will integrate their products with BO and Crystal Reports better -- as it seems. I wonder how this will evolve.

Business Objects Query Drill

Query drill (or apply drill filters) is a somewhat hidden feature of Business Objects. In fact, I rediscovered it when I was teaching BO Webi.

It got me somewhat curious, to find out if it also existed in BO 5 and 6.5 — and in fact, it exists in every single one of those versions.

1) Where do you find it :
In webi, it is located in the document properties — you check “query drill”
In full client, it is located in Tools>Options and on the tab Drill, check “Apply Drill filters on drill through”

2) What does it do
In fact, drilling is a tricky business — you navigate through your data and little by little, you get your data and you extend your query. Start with 10 records, and end up with a query of over 50000 records.

Because — that is what happens, you want more details, so you drill for an extra column using drill through and you end up multiplying the data in your dataset.

Apply drill filters on drill through will actually limit your query to the data you filtered for using drill by setting conditions on the query.
So, you drill down on year and get quarters (2006). Then you drill through to months on Q3 — your query is now adapted and filtered for 2006/Q3.

This results in a number of things :

1) Less data (only the data you drilled for is shown)2) Functions aggregated on the database level are more likely to be calculated correctly3) A speed-up (or a slow down) compared to regular drill will occur (depending on the nature of the fields you filter on !

This last point asks for some extra explanation :

Lets assume you have a date field which is indexed at the database level. On the universe, the designer (made the error and ) created a field with function year(datefield) and now youare drilling for a given year. This will filter on a function applied on a date-field — the result is desastrous for performance. (if the year were a physical (indexed) field on the database, the
performance would be very good)

enough already,

Back to reporting

My favorite functions

In a previous message, I wrote about some functions on time. Here are my favorite functions... much better than the previous ones

function GetLastDayPreviousMonth()
Dim dt
Dim firstDay
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastday = DateAdd("d",-1,firstDay)
GetLastDayPreviousMonth = lastDay
end function
function GetFirstDayPreviousMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayPreviousMonth()),month(GetLastDayPreviousMonth()),1)
GetFirstDayPreviousMonth = firstDay
end function

Sillyness.. absolutely :

=dateadd(“d”, –1, dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”)))

will give you the last day of the previous month.

the first day is easier still :

=dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”))

 

 

 

function convertTime(intMinutes)
dim iTimeInHours, iTimeMinutes,strTimeMinutes, strTimeInHours, strTimeInHoursAndMinutes
iTimeInHours = intMinutes \ 60
iTimeMinutes = intMinutes mod 60
if (iTimeInHours < 10) then
strTimeInHours = "0" & cstr(iTimeInHours)
else
strTimeInHours = cstr(iTimeInHours)
end if
if (iTimeMinutes < 10) then
strTimeMinutes = "0" & cstr(iTimeMinutes)
else
strTimeMinutes = cstr(iTimeMinutes)
end if
strTimeInHoursAndMinutes = strTimeInHours & ":" & strTimeMinutes convertTime = strTimeInHoursAndMinutes
end function
function calculateTime(eDt as datetime, sDt as datetime, eLu as Int32)
dim iTime
iTime = DateDiff("n",eDt,sDt) - eLu calculateTime = iTime
end function

Time in Reporting Services

Putting time in a table is easy... format as hh:MM -- but ! This time I had to put time in a correct format (hours:minutes) and the given data was in minutes. Maybe there is an easy way, but I haven't found it yet - this is my solution :

function convertTime(intMinutes as int32)
'prepare variables
dim arrValues, numMinutes, strNumHours, strNumMinutes
dim numHours, numValue, strTime,strIntMinutes
strNumHours = ""
strNumMinutes=""

'get the time
if intMinutes > 0 then
strIntMinutes = cstr(intMinutes/60)
if instr(strIntMinutes,",") > 0 then
arrValues = split(strIntMinutes ,",")
strNumHours = cstr(arrValues(0))
strNumMinutes = cstr(cint(cint(left(arrValues(1),2))/100*60))
else strNumHours = cstr(strIntMinutes)
strNumMinutes = "00"
end if
else
strNumHours = "00"
strNumMinutes = "00"
end if
if len(strNumHours) = 1 then
strNumHours = "0" & strNumHours
end if
if len(strNumMinutes) = 1 then
strNumMinutes = strNumMinutes & "0"
end if
strTime = strNumHours & ":" & strNumMinutes
convertTime = strTime
end function

put this in the code (properties of the report) and call it with
code.convertTime(Field!whateveritmaybe)

cheers

The kind of stuff people want in RS

What if the date is stored as date/time and you want to offer a prompt that allows a user to select a date. But!! The date has to be equal to the date in the database.

select * from Orders where cast(CONVERT(varchar(8), OrderDate, 112) AS datetime) = '2004-08-25'

this is how.

Reporting services has problems showing the data in the data-tab though.. it only works in the preview.

Dates in Reporting Services

A colleague of mine asked for a parameter on a report. The report had to run from the first of the previous month, until the last day of the previous month. I solved this with this function :

function fnGetLastDayOfMonth(datum as date)
fnGetLastDayOfMonth = Day(DateSerial(Year(datum), Month(datum) + 1, 0))
end function

I then called this function in this way :

startdate

=cdate(iif(month(now())=1,year(now())-1,year(now())) & "/" & iif(month(now())=1,12,month(now())-1) & "/01")

enddate

=cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,12) & "/" & code.GetLastDayOfMonth(cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,1) & "/" & "1")))

can’t believe I wrote that function – this will do the trick just fine.

=dateadd(“d”, –1, dateadd(“m”, +1, cdate(year(today()) & “/” & month(today()) & “/1”)))

from the parameter. It would then feed the first day of the previous month to that function and there you have it.. the last day. Cool.

Linking universes

A customer asked me : how can I link two dataproviders if the common field has an un-equal number of digits.
In one universe, the customernumber is 6 digits, in the other 8 digits - the two digits are two leading zeroes, so the zeroes can be removed without problem.

The formula I used was :
=if(Length(<customernumber>8) then SubStr(<customernumber>,3,Length<customernumber>)) Else <customernumber>

inserted into the field where the customer number was, the leading zeroes would be removed and BO would automatically agregate the data.

there is an other solution, which would involve creating a user defined object on the universe, but this would render the documents unusable to colleagues on different machines.

SSRS MultiValue and Nulls

When you create a multivalue parameter and you also want to include the null values when doing a filter (a select all), you got two options 1) create a union query to include null. Or (like me) -- you can modify the parameter with code. In the report properties, on the code tab, add the code below (it is probably not the most efficient code, but it works):

function addnulltoparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function

next, in the properties of you dataset, click the tab filters, create a filter on a field, choose in for the operator and in the value type :
code.addnulltoparameter(Parameters!name_of_the_parameter.Value) -- make sure you don't leave (0) at the end or you will only send the first element of the array.

The result is that a null value is concatenated into the parameter. If you want, you can also create a Boolean parameter, that allows you the choice to include nulls.

then the code for the filter would be
=iif(parameters!includenull=True,code.addnulltoparamater(Parameters!someparameter.Value),Parameters!someparameter.Value)

Complex tables do not export

At the moment, I'm creating some complex tables in Reporting services (2005).

In fact, the only two tables you can have are vertical tables and matrix tables. It is possible to create a horizontal table, but you have to rework a matrix to get to it.

The table I'm currently building in Reporting services involves nesting a vertical table inside a matrix.

As it seems, the table works fine, it exports well to pdf -- but excel won't work. It seems to be impossible to export complex nested tables to Excel.