Video examples of the Ultimate Generic Date/Time framework.

Hi Folks! I just released some YouTube video’s showing  the user experience when using a universe with the ‘Ultimate Generic Date/Time framework’ implemented.

Just visit my Youtube- Channel to see the framework in action.

In the YouTube video ‘Super User Defined and Ad hoc LOV’ Example of the Ultimate Generic DateTime Framework, I show you a filter-example combining a user defined input of a begin- and end-period in combination with the ad hoc List-of-Values with predefined date-periods.
The end-user has the possibility  to select from a LOV with predefined periods (date-ranges) or to fill in his own begin- and end-date.

This is extremely user friendly. The end-user only has to make one report, then he can use it on-demand using user-defined periods or use the same report and schedule it for a specific time-interval like previous year, previous month, previous week, … .

I’ll soon reengineer all my previous published frameworks (Oracle, DB2 and MS SQL Server)  with this new super-filter. I’m also working on a MS-Access and Sybase version of the framework. please keep following my blog, and invite your BO-friends.


The Ultimate Generic Date/Time Framework now available for Informix (Dynamic Server 11.5)

A few weeks after the release of the Date/Time framework for MS SQL Server, Romain Gouyet (from Irium France) was so enthusiastic, he immediately started to adapt the framework for Informix.

To my great pleasure and with the permission of Romain, I can announce the availability of this Informix version, ready to download from my public Dropbox Folder. Because we don’t use Informix in our company, I only have the opportunity to do a quick review, but as I can see, lot of time has been invested by Romain to realise this migration. Thanks Romain!

To customize your own universes with this generic Date/Time Framework, simply follow the installation guidelines for MS SQL Server published in my previous post. Or download the PowerPoint presentation from Slideshare.

And Romain, also suggested another very nice tip. In case you want to customize the ‘Ad hoc’ list of values for several languages.

You simply surround the range text-field (Tomorrow, Yesterday, Today,…) with the following do case structure (example provided for MS SQL Server):

select ’00’ as Sort,(CASE CAST(@Variable(‘DOMINANT_PREFERRED_VIEWING_LOCALE’) as char(2))
when ‘fr’ then ‘Demain’
when ‘en’ then ‘Tomorrow’
when ‘de’ then ‘Morgen’
when ‘nl’ then ‘Morgen’
when ‘es’ then ‘mañana’
else ‘Tomorrow’
End) as Date_range,dateadd(dd, datediff(dd, 0, current_timestamp)+1, 0)  as Begin_Date, dateadd(ms,-3,dateadd(dd, datediff(dd, 0, current_timestamp)+2, 0))  as End_Date

Using this new variable DOMINANT_PREFERRED_VIEWING_LOCALE (new in BO XI 3.1 SP2), you can determine the end-user local and customize the LOV in his preferred viewing local.

Again, Romain, thank you very much for your contribution to the SAP – BO designer community.


The Ultimate Generic Date/Time Framework now available for MS SQL Server

Some people asked me, if it’s possible to migrate the framework to MS SQL Server.

The framework is based on the trunc-function in DB2 & Oracle and the existance of a dual-table in Oracle and the sysibm.sysdummy1 table in DB2. It’s possible to substitute the trunc-function with a combination of the dateadd & datediff function. Read my previous post for more information.

And do you need an equivalent for the Dual-table in MS SQL Server? The good news is…, there is no need of Dual table in SQL Server at all.

And now the good news. Below you can download a the Ultimate Generic Date/Time framework for MS SQL Server universe from my public dropbox <<DateTimeDimensionMSsqlTemplate.unv>>

But before you start to implement the framework in your own universe, just take a little bit of time to study the embedded Powerpoint below (Tip: you can maximize the window by clicking in the lower-right corner of the slideshare presentation.)

No trunc-function available in MS-SQL Server!

In a previous post, I showed you how to strip off some parts of a timestamp-field. In fact most of the objects used in the ‘ultimate generic date/time framework‘ are based on the use of the trunc-function.

But some people asked me for a MS-SQL Server version of the framework. To my annoyance, there is no trunc-function available in MS SQL Server!

So is there a workaround? You can do a lot with the cast-function. If you want to strip off the time from a timestamp, then you can write :

cast(current_timestamp As Date)

But the above solution only works for SQL Server 2008. And I also need to strip off other parts, like days, months,…

A combination of two functions dateadd() and datediff(), give us a beautiful solution. Let’s say I want to strip off the time part of a timestamp-field.

With the datediff() function, I can find how many days there are from day 0 to the current timestamp, and adding that many days back to day 0 with the dateadd() function, gives us the desired result.

dateadd(dd, datediff(dd,0, current_timestamp), 0)

It will work no matter how your datetime is stored and no matter what your locale is. In fact it’s complete platform independent.

But what if I want to create my Business Intelligence reporting based on a semester? Indeed, there is no semester part available in both functions!


dateadd(mm, datediff(mm, 0, current_timestamp)-datediff(mm,0,current_timestamp)%6, 0)

And what about the above solution?  Try it out.

Implementing the BatchBOXITools – framework Part 3

In part 3 I’ll show you how to extend your BatchBOXITools-project with three interesting java-classes (download zip with java source) :

  • (deleting objects/instances based on a Query Builder query)
  • (purge all dataproviders of  webi documents based on a Query Builder query or specified cuid)
  • and save a webIntelligence-document based on one or more cuid’s)

    Eclipse Project BatchBOXITools

    Eclipse Project BatchBOXITools

Let’s begin with the first class

Read more…

How to choose the right chart?

Overwhelmed by all those different charts? You shouldn’t!

When it comes to quantitative charts, there are only 5 basic chart forms to choose from.

  • pie chart
  • column chart
  • bar chart
  • line chart
  • dot chart

With the following mindmap, you’ll be able to turn your data into a chart, in a few seconds.
How? Find the clue behind the message you want tell.

Double-click to enlarge

Mindmap on how to select the right chart

(the mindmap was inspired by the table in Gene Zelazny’s classic work Saying it with Charts (p. 27 in the 4th. ed)

Be honest, without message, you don’t need to show something.
1. Study your data and find the comparison

  • Component (part of the total); you are interested primarily in showing the size of each part
  • Item; you want to compare how things rank
  • Time Series; you are interested in how things change over time
  • Frequency Distribution; this comparison shows how many items fall into a series of progressive numerical ranges
  • Correlation; this comparison shows whether the relationship between two variable follows, or fails to follow, the pattern you would normally expect

2. Think about the message, you want to tell.

3. Find the comparison topic clue in your message.

4. Use the suggested chart in the mindmap

Read more…

Implementing the BatchBOXITools – framework Part 2

Create a new Java project and call it BatchBOXITools, add the BO SDK – jar files (for more info, navigate to Quick start using Eclipse and BO XI Java SDK Part 1)

Right-click on the BatchBOXITools project and add the two previous created user-libraries in part 1

  • apache-log4j-1.2.16
  • commons-cli-1.2

Now create a new class called CmdLineParser. This class will be used to parse the command-line

Import the java-source code (download into your own project (change the package name by replacing kbc in com.kbc.boxitools with your own company) and study the code. For more information on how to use the Commons Cli-library, navigate to Apache Commons

Some explanation:

1. The class uses a private instance-variable for every command-line option we want to use in the future.
2. In the constructor we use the addOption-method on the Options object to add a command-line option.
3. Then the parseCMDline method will do the parsing of the command-line.
4. Next two setters, will be used to set the cmd-line options:

  • setCmdParam(param1,param2)
  • setCmdBooleanParam(param1)

5. Then a list of public getters, can be used to read out the command-line options.

Read more…