How to set Default Date Prompt Value to system date.

Here my requirement is to set default date prompt value should be system date.

I have two date fields in my prompt level and using between function as highlighted in below screen shot.

By default every day these two prompts WO start Dt. and Oprn Start Dt should pick system date.

I followed below steps to achieve this scenario.

Step 1:

Go to edit dashboard.

Step2:

Edit that particular Prompt.

Step 3:

Select date field WO start Dt.

Go to options and select default selection as SQL results from drop down as shown in below screen shot.

Step 4:

Copy below sql code

SELECT max("Active"."start_date")

FROM "ActiveSchedule" WHERE "Active"."start_date"<=CURRENT_DATE               

Save it. 

And I have done the same modifications to another date filed.

Now go to report and you can see the system date as default value in date prompts as shown in below screen shot.

How to change OBIEE default date format.

To change OBIEE Timestamp in 24 hours format.

Here I have one requirement that the work order was scheduled on 11-06-13 21:55:00, which was not displaying in the report when scheduled date is selecting between 10-06-13 to 11-06-13.

Here timestamp is taking between 10-06-13 00:00:00 to 11-06-13 00:00:00.

By default BI Timestamp will be in AM-PM format, but work order scheduled format was in 24 hours format. Below is my R&D on this issue.

Here time field is scheduled_start_date.

I took this field datatype as TIMESTAMP and in prompt I am using between function.

Now in Prompt level we are able to select date and Time.

But here I faced another problem; the date format was in AM – PM format.

Our client requirement was date format should be in 24 hours format.

I tried so many options to change date format in prompt level, we can change date formats in report level, but here question is how you will change the date format in prompt level.

I tried to change fx formula in prompt level but no use.

Next I thought to do to create one new column in BMM layer to use Evaluate function and I selected that column as prompt but no use.

Evaluate function was working fine but it was showing in drop down like below screen shot.

Now there is only one option to me i.e. Need to change configuration file.

I went to following location to edit localedefinitions.xml.

Installation Folder/Oracle_BI1/bifoundation/web/display/

Open this file and search for localedefinition name ="en"

Before doing these changes please take a backup of original file.

And change the highlighted values as below screen shot.

After doing these changes to xml file save and copy this XML file to original location.

Restart the BI services form EM.

And See the result in below screen shot. TIMESTAMP will be in 24 hours format.

Error: The conditional format is not inside a table and can therefore not be applied to a table row (RTF template work around for this error)

Conditional format in BI publisher 10.1.3.4.1

In above report I need to print two column values (Dept & Backlog WO’s Except SD) in red color if the

Backlog WO’s Except SD count was greater than 20%

So we need to do conditional format in RTF template.

When I am doing conditional format in RTF template I am getting below error

Error: The conditional format is not inside a table and can therefore not be applied to a table row

But this is the bug.

For this issue there was one work around.

1)      Create one dummy table as shown in below screen shot.

2)      In first row insert few fields as shown in below screen shot.

3)      Select any field and click on create conditional format

4)      I already having the percentage field…. So I am giving the condition according to my requirement  as shown in below screen shot.

5)      To check whether conditional format is working or not just create a group as shown in below screen shot.

And load sample XML to check conditional format

And the preview is

Here we applied conditional format only for skill field. So it will display in red.

Now copy the conditional field from dummy table and past in original table like below.

Note: Copy only conditional format i.e     C   

And copy to original table (TO both columns Dept & Backlog WO’s Except SD ) as shown in below screen shot.

Now delete the dummy table and click on preview.

If you want to apply to entire row … there will be one check box we need to check (Apply to entire row) check that box and apply to original table.

Output will be as below.