A hairy java bug in Business Objects XIR3

Java has always been a source of bugs in Business Objects. I still remember, with version 1.2.4, the amount of errors we received at a customer. Business Objects XI R3 seems to suffer from a nice little bug too.




If you want to solve this problem, you can go to Control Panel, Java and select this option :

Hide Block feature in Business Objects

A very obscure feature in Business Objects is the "Hide Object" Checkbox. As far as I know, there is no course, where you show that particular feature. Here's how it works.

Or maybe I should start by saying : don't just switch this feature on without filling in the condition window that's right next to it. If you do, the object, on which you check it, will just disappear, which is a bit daft. In case you did that, go to View>Structure, to locate the item, on which you activated it and switch it back off.

This feature only works well, when you also enter a condition. That condition should return a boolean.

Double-click on the block, on which you wish to activate the feature, and go to “Appearance”. Check the checkbox and enter a function, under which condition you wish to hide the block. e.g. : =Sum(<Sales revenue>) < 5000000

Notice, this function will return a true or false. It can be necessary to use contexts to calculate the right number.

The result is that the sections where I wish to see the table, will show the table, the others will show up collapsed, because there’s no content in them. Nice feature – probably underused.

 

Binabik

Signing Powershell scripts

Phew. I did it. Thank you Bruce Payette for the great book you wrote.

If you want to read the book it’s on Amazon.com.

Three reasons why you would want to get it.

1) It’s very well written (which is rare among books about programming)

2) It’s a good and (at times) a funny read. (which is next to impossible to find)

3) This book is written by the person who helped design Powershell.

 

But let me get to the code-signing bit :

You’ll need the Windows SDK, because it contains ‘makecert.exe’

I just copied the makecert file in my Powershell directory to make the commands a bit shorter.

If you’re under Windows 7, make sure you start Powershell ‘as Administrator’. Otherwise, some commands will fail.

in your Powershell window, type :

./makecert –n “CN=PowerShell Local Certificate Root” –a sha1 –eku 1.3.6.1.5.5.7.3.3 –r –sv root.pvk root.cer –ss Root –sr localMachine

 

This will pop up a dialog to enter passwords. And we have just created a Local Certificate Authority.

 

Next, we ened to make a signing certificate :

./makecert –pe –n “CN=¨PowerShell User” –ss MY –a sha1 –eku 1.3.6.1.5.5.7.3.3 –iv root.pvk –ic root.cer

 

great. that’s that. Now, all we need to do is sign our scripts with that key. Here’s how :

First change the Execution policy to allsigned :

set-executionpolicy AllSigned

Next, we load our certificate into a variable :

$cert = @(Get-ChildItem cert:\CurrentUser\My -Codesigning)[0]

 

then we sign the file :

Set-authenticodeSignature test-script.ps1 $cert

you should be able to execute your script now.

 

Binabik

Being careful in Business Objects (DeskI)

When you’re running a query, one of the things you should consider is that you might forget a filter and get way too much data from the database.

Secondly, that query might run for multiple hours and you don’t have multiple hours because you want to get to the part where you layout the report before running the query and returning all the data.

Here’s a few things you can do :

1) Create the query, save the query but don’t run it yet.

In the query panel, instead of blindly hitting Run, Hit Save and Close. This will save the query, but you won’t get data. Next, you can start modifying the layout of the report (use View>Structure) and when you’re ready, refresh the report to get your data (e.g. before leaving the office)

 

2) Limit the amount of data that can be returned by the query

If you don’t know in advance, how much data you’re going to get, maybe it’s a good idea, at first run, to limit the returned data to the first 10 or 100 lines, in stead of getting the lot. Especially, if you’re in doubt which kind of data will be returned by each of the fields.

When you click the options button, you get to choose how many lines you would like to get from the database:

this will of course return ‘Partial results’.

 

There you go folks – for the sake of prudence, don’t get all your data at once.

 

Binabik

A Document Map in Reporting Services

This topic deals with Report Builder 2.0 – it’s also possible to do this through Visual Studio, but that will have to wait for later.

Instead of setting all sorts of filters, you can choose to get all the data from the server and make it more accessible by creating a table of contents for it.

So, when you click the table of contents in the left column, you jump to that particular part of the report.

That’s what this particular article is about. How do you create a ‘Document Map’.

 

 

 

 

 

 

First, you need to insert a ‘list’

Next, rightclick the left bar of the list, change the tablix properties to make the list refer to the right dataset.

Subsequently, rightclick the left bar and change the group-properties

 

In those group properties, on the advanced-tab, choose, which field you would like to have as a document map.

 

Drag the field in the list and drag any other items inside that list. Tables or Other lists will do fine.

Nested lists can also have nested document maps – works great.

 

Enjoy

 

Binabik

Relative positioning in Business Objects

When you’re building reports in Business Objects, sooner or later, you’re going to have a table where the length not fixed. So, one day, you have 10 lines, the next day, you have 50 lines. Tables that are located behind them are then suddely overrun by that table.

The properties of the table allow you to choose how tables behave.

(the name of that table can be configured also in ‘Table format’

 

Cheers,

Binabik

Day of the week of first day current year

I noticed on Google analytics that someone was looking to calculate in Business Objects, what day, the first day of the year was.

Here's how :

=DayName(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

If you want the number of the day

=DayNumberOfWeek(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

Yet someone else was looking for an equivalent of Instring in Business Objects. Look no further.. it’s Pos()

pos(“abcdefg”, “g”) = 7, so, g is located in the 7th position.

 

Easy as pie

Binabik

Showing the average on a chart in Business Objects

In my latest courses, lots of folks had questions about the way Contexts work. So, here’s another way, contexts are used :

You want to show the average of something on a chart, as a horizontal line. That means, you will have to create a column with that average in a table, where the same number is shown in the entire column.

Month	N° visitors	Average
1	25	25
2	100	100
3	72	72
4	97	97
5	93	93
6	103	103
7	111	111
8	23	23
9	87	87
10	98	98
11	39	39
12	111	111
Average	79,9

It’s pretty clear, the formula =Average(<N° visitors>) won’t do the trick. It just returns the number itself, because it operates in the context of the month. To solve this, we can use 2 formulas :
Average(<N° visitors>) in Report
Average(<N° visitors>) forall(<Month>)
The first one is absolute, so it will always return the same number – no matter in which table you put it. The second one is relative – it depends on the dimensions already in the table.
One way to do this is by choosing : Data> Define as variable and choosing Evaluate the formula in its context.



Both of the above will return :

Month	N° visitors	Average
1	25	79,9
2	100	79,9
3	72	79,9
4	97	79,9
5	93	79,9
6	103	79,9
7	111	79,9
8	23	79,9
9	87	79,9
10	98	79,9
11	39	79,9
12	111	79,9
Average	79,9

Now, we turn this into a chart :



Not exactly what we need, but, getting there.

Rightclick the chart and choose “Format Chart”
On the Series tab, click “Add” and make it a line chart, next, you drag the average from the other group to the new group.



And we get :


Cheers folks

Binabik

Calculating average in Business Objects

At a customer, someone asked this particular question : In a crosstab, if certain lines return empties, how do you calculate average while evaluating the empties as 0 ?

As it is, when you calculate the average with the regular function, BO will not take into account the missing information.

	2007	2008	2009
Dog	10	10	10
Cat	10		10
Horse	10
Pony	10		10
Average	10	10	10

where it should be

Average

10(40/4)

2,5(10/4)

7,5 (30/4)

The solution to this problem is : contexts.

First, we need to count the number of animals

Count

4

1

3

performing a regular count will not give us the right result.

Count() will return a different number for each column. So, we force the count with "in" Count() in Report -- that will return 4 in each column.

Count

4

4

4

So, now we have our formula :

Sum()/Count() in Report

Cheers folks

Binabik

three things I would like in RS

1) Sorting Picklists

On RS, when you build a picklist, you select your field and the values don't get sorted, and there is no option to do so. I found a workaround, but it's not very clean.

2) an option to remove the item(Select all) in multivalue parameters

When you set a picklist to multi-value, RS automatically adds the option "Select all"

Since a query is very often limited to 999 inlist values, you run into trouble pretty fast, and it's a big performance issue, if people just select the lot.

Would be nice to have this option.

3) some way to pre-test scripting.. the way it is now, scripting is very under-developped. In fact, it's just a window where you can enter text.. period -- wouldn't mind some intellisense :) .

Binabik

Sorting picklists in RS Reportbuilder 2.0

To sort a picklist in reportbuilder, when working with a model, you can use the following workaround :

* add the field twice

This is pretty daft : you can't just sort the list. In t-SQL, it would be sooo easy, but on a model, your hands are tied. Once you add that same field a second time to a query : disco, it's sorted.

Took me a while to figure out that it was actually the addition of a field a second time that sorted the thing.. and not any function that was applied to the field.

Gotta love RS.

Binabik

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