How to make Date type dimension filtering by time in Query Filter of WEBI report

Document ID : KB000048396
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

If you use Date type dimension as Query Filter in your Web Intelligent (WEBI) report, by default, you can only filter by date, not by date and time. In a scenario when you want to create WEBI report to show alarms that created for 4 hours duration, you cannot easily do that using Query Filter. To achieve the objective you may select "Create On" dimension in "Alarm" folder as Query Filter and set "between" operation and specify the duration. Although you have specified 4 hours duration within the same date, by default the Query Filter won't filter alarms as expected because the time portion is not honored. This KB article shows how you can configure CABI so that it honors specified time in Date type dimension as the Query Filter.

Solution:

Do the following steps to make CABI honors the time portion:

  1. On the CABI machine, navigate to the following directory

    <CABI Install Directory>\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\jdbc

  2. Backup mysql.prm file under the directory before modifying it

  3. Modify the following two lines

    From
    <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd HH:mm:ss'}</Parameter>
    To
    <Parameter Name="USER_INPUT_DATE_FORMAT">{\t 'yyyy-mm-dd HH:mm:ss'}</Parameter>
    From
    <Parameter Language="ja" Name="USER_INPUT_DATE_FORMAT">{!d 'yyyy-mm-dd HH:mm:ss'}</Parameter>
    To
    <Parameter Language="ja" Name="USER_INPUT_DATE_FORMAT">{!t 'yyyy-mm-dd HH:mm:ss'}</Parameter>

  4. Launch Central Configuration Manager and shutdown BOXI Tomcat and SIA and then restart SIA and restart BOXI Tomcat

    Figure 1