Wednesday, December 27, 2017

String to integer in Oracle

Converting a string object to numeric may result in query errors (not parse errors). Use this code to check if the string contains non numeric characters:

case when translate(FIELD1,'_0123456789 ','_') is null  then to_number(FIELD1) end

Monday, November 27, 2017

Colored projects on a planning report

What if you want to create a planning report where the projects have different colors like this:

And not like this:

First you need to remove the right cell border:
Then create a conditional formatting rule to add a cell border:


Wednesday, October 11, 2017

Concatenating multiple rows


Concatenating multiple rows in webi is not an easy tasks to do. But concatenation multiple rows in Oracle is easy. Creating an object in the universe with the LISTAGG analytical funtion will do the trick.

On SQL server use FOR XML PATH in combination with STUFF.

Saturday, January 14, 2017

The BO security model: object names

In this blog series I will share insights I’ve gathered over the years on how to setup an effective security model: simple, structured, maintainable, flexible, expandable and easy to use.
In this third part of the series I will focus on the names of objects. As stated the goal is to create a security model that is simple and structured. The object names play a big part in this.

·         This blog series is aimed at experienced BO administrators, which means there will be no how-to screenshots
·         This blog series can be used as a guideline, it cannot be used as a manual
·         This blog series only covers the internal BO stuff. No windows AD or SAP roles and no IAM software

User types

Let’s assume there are four types of users on your systems:
  • Endusers
  • Analysts
  • Reporters
  • Designers
Where each type is an extension of the previous one, so there is some kind of structure in these types.

Per type there will be a user group and a CAL.


When I assign these user groups to folders it looks like this:

Although they are sorted alphabetically I don’t like that very much, So I make a little change to the user type user groups, to make use of the alphabetic sort:

After this little name change it’s much easier on the eye. And it’s making the model a bit more structured.

Organisation user groups

Lets add a department to the system, Sales. I create folders which represent the structure of the sales department and corresponding user groups. Theses folders and user groups are used to define access. Both have the same structure but the don't have the same names.

Doing this makes them appear neatly ordered when they are assigned to the folders:

Total user security on folder Sales \ EMAE \ NL now looks like this: