cancel
Showing results for 
Search instead for 
Did you mean: 

Displaying text whenever there is no value present in Database

Former Member
0 Kudos

Post Author: Sujitha

CA Forum: WebIntelligence Reporting

Hi

My business demands that a text 'Defaulted to Zero' be displayed if for a given combination of columns there is no data present in the Fact table.We use oracle 9i.

I have already used the number format where we put a value for the undefined value to display a text N/A where ever the value is not applicable.But i need to display text "Defaulted to Zero" wherever the value is applicable but NOT present in database!!

Can this be acheived in Bo level,I am using BO XI R2.?

This is a very urgent requirement as my project delivery is scheduled.!!

Many Thanks!!!

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

Suzie,

You can create a new local variable to report (called something like "NVP"), leave it as a "Dimension" and in the formula type:

if([col_a]=0 or [col_b]=0;"NO VALUE PRESENT")

Basically, you are using the "if" function and the syntax is very much like used in excel except you separate the arguments with a semi-colon versus a comma. You start out with "if()" and within the parenthesis is a logic statement followed by when true do this else that (with the "else that" portion optional as shown above). The drawback to using a local variable is that every report you write must be customized with the local variable versus establishing this logic in a "case" statement when building the data mart and thus it becomes resident and usable like any other column of data in your database. In some cases you are forced in using it as a local variable due to variations in the data and the logical outcome is not known until the very end of all computations, and at other times you can get away with embedding the logic at the time of constructing your data mart -- it all depends on how the data is used and sliced and diced. Good luck and write back if you have any further questions.

Former Member
0 Kudos

Post Author: Sujitha

CA Forum: WebIntelligence Reporting

John,

Dats were my problem lies... i dont want to replace the text as you suggest I wanted to make a new entry whilst keeping the N/A intact and display a Defaulted to Zero text when I dont find values in the fact table.

Anyways,we have forwarded thid to the ETL team now,and they are looking into it... but still i want to know if BO suports this or not?

Former Member
0 Kudos

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

Suzie,

I am surprised that you cannot display "Defaulted to Zero" in lieu of the way that you seem to have easily mastered getting it to display "N/A". Nonetheless, have you tried the Replace() function? Suppose that your resultant column is called "x_col", then I would suggest that you use the following formula in the column of data:

=Replace(x_col;"N/A";"Defaulted to Zero")

Good luck!