User friendly warning message (BOXI)

Here, I am showing a very simple way of customizing “No data to retrieve” warning message when queries don't pull any data from database.

Here is how default message looks like.

You can make this warning message more readable by  renaming query tab name. Here is another example with readable texts. 

Escaping ampersand. Getting rid of "Enter Substitution Variable" message.

If you want to bypass "Enter  Substitution Variable" message when there is '&' or '&&' sign on your oracle SQL then all you need to do is add following line at the top of your SQL.


Exmaple ;


This SQL has ampersand and it will prompt to enter a  substitution variable.


select 'dfd&dfd' from dual
where DUMMY ='E&AMPLE';


This issue can be fixed by adding following line into you sql.


SET DEFINE OFF
select 'dfd&dfd' from dual
where DUMMY ='E&AMPLE'


Hope this helps.

GetLovField Error

When you try running DEKi report on Infoview, you might end up getting “GetLovField” Error message.  This issue can occur when you change prompts from mono to multi or from multi to mono. In order to get rid of this issue you will need to remove the existing prompt and re-do it. This should fix your issue.

Missing Msvcr100.dll

When you first try to launch SQL developer on your PC you might run into “Msvcr100.dll is missing” error message. When you encounter above error massage, you will not be able to launch SQL developer.  However; there is a very simple step to fix this issue.

First thing you need to do is find MSvcr100.dll on your machine.  Normally it is available on “\Program Files\Java\jsdk1.7.0_04\bin” folder. Simply copy it and paste it to “\sqldeveloper\sqldeveloper\bin” folder.  This should fix the issue.

Date Functions

I always find date functions difficult to code as they can be confusing. Here are some of the date functions that can come handy while working with dates in Business Objects.

 

Last day of month =LastDayOfMonth(< Month>) 

First day of month =DayNumberOfWeek(< Month>) 

Days in a month  =DaysBetween(< Month> ,<last day of month >)+1 

Weekends =(Truncate((DayNumberOfWeek(< Month>)+<Days in a month >)/7 ,0)) 

Working Days =Floor(<Days in a month>-(<Weekends>)) 

6 working Days =If <first day of month > = 7  Then (< Working Days >+1) Else < Working Days > 

How to prompt for Quarters (Q1, Q2, Q3, and Q4) in BusinessObjects

How to prompt for Quarters (Q1, Q2, Q3, and Q4) in Business objects using designer?

Simple Method…
You can use @ Variable function to prompt for Quarter. Here is one example.

TO_CHAR (CALENDAR_DATE,'Q') =@Variable('Which Quarter?')

But Coding can be little complicated when users want to select Q1,Q2,Q3,Q4 from the list, instead.
In that case Syntax should be something as below

( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Now let’s assume requirement is to select year and quarter and retrieve date up to next 36 months... In this example I am going to use @variable as well as @Prompt.

TABLE.CALENDAR_DATE BETWEEN (SELECT Distinct MIN(CALENDAR_DATE) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
AND
(SELECT Distinct MIN(LAST_DAY(ADD_MONTHS(CALENDAR_DATE,35))) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Fun with Xcelsius

I used my Facebook friends profile to create a simple dashboard using Xcelsius BI reporting tool. The purpose of this dashboard is just for fun and i believe friends privacy has not been compromised ! Have fun !