Sunday 30 December 2012

Searching a Text in Page

If your report is too large and you want to search your desired record, if you know the value.
Then add the below code in the text with 'HTML' tag enabled.

<script type="text/javascript" language="JavaScript">

var OtherBrowser = (document.getElementById);
var IE4 = (document.all);
var NS4 = (document.layers);
var win = window;
var n = 0;


function findInPage(str) {
var txt, i, found;
if (str == "") {
alert("Enter some thing to search");
return false;
}

else if (IE4) {

txt = win.document.body.createTextRange();

for (i = 0; i <= n && (found = txt.findText(str)) != false; i++) {
txt.moveStart("character", 1);
txt.moveEnd("textedit");
}
if (found) {
txt.moveStart("character", -1);
txt.findText(str);
txt.select();
txt.scrollIntoView();
n++;
}
else {
if (n > 0) {
n = 0;

findInPage(str);
}
else
alert("Sorry, we couldn't find.Try again");
}
}
else if (OtherBrowser) {
if (!win.find(str)) {
while (win.find(str, false, true, false, false, false, true))
n++;
}
else if (win.find(str)) {

n++;
}
}


if (NS4) {
if (!win.find(str)) {
while (win.find(str, false, true, false, false, false, true))
n++;
}
else if (win.find(str)) {
n++;
}
}
return false;
}

</script>



</head>
<body>
<div style="padding-top: 5px; padding-right: 10px; float: right;">
<form name="Search" action = "" onsubmit="return findInPage(this.string.value);">
<p align="center">
Keyword Search:
<input style="width: 200px; border-right: #666666 1px solid; border-top: #666666 1px solid;
font-size: 10pt; border-left: #666666 1px solid; border-bottom: #666666 1px solid"
onchange="n = 0;" size="16" name="string" />
&nbsp;<input style="border: #fff 1px solid; font-family: Tahoma; color: #FFF; background-color: #2e6ebd"
type="submit" value="Search"/>
</p>
</form>

Result-


Comments are always welcome...
 

Saturday 29 December 2012

Adding Bookmark Link in OBIEE

To add 'Bookmark' link in OBIEE, you need to pull a text object in Dashboard and add the following code with 'HTML' check box enabled.

<a href="javascript:void(0);" onclick="if(document.all) window.external.AddFavorite(window.document.location,window.document.title);">Bookmark Page</a>
Save and Run the dashboard page. A pop-up box opens to save the page.

Result-


Comments are always welcome...
 

Monday 10 December 2012

Refreshing a report Everytime it runs

In cases, where we need to refresh a report surpassing the Cache everytime, we can create a report with a column hidden and making the column formula as-
CURRENT_TIMESTAMP
This will make the report run every time, for every change in second.

Comments are always welcome...

Sunday 18 November 2012

Edit Analysis and navigate to Criteria Tab-11g

Always when you edit the analysis, you would be navigated to the 'Results' tab by default and then again you need to click on the 'Criteria' tab, to edit.
There is an option to go to criteria tab when clicked on edit analysis.
This feature is available now in 11.1.1.6 in 'My Account' section.

People might have noticed this, but might not have tested the change.

 
Next time when you click on edit analysis, the system will take you to criteria tab.

 

Thursday 8 November 2012

11g Removing the Gray Header from Reports


In 11g, the user can right click on the pivot table report and do sorting excluding the column etc.. also a little grey header on the column heading appears.
This feature is good to some extend and worse in most of the cases. Supposing the user wants to disbale all this, then there should be a way out :)

To remove the gray color tab appearing above the column headings in the pivot table.

Just Edit your analysis and then click on "Analysis Properties dialog" then select Interactions tab here you uncheck all the Interactions link then save it and test it.
 
 If you want to disable enitre report just add below content in your instanceconfig.xml file then restart bi presentation services then test it out.

Include the elements and their ancestor elements as appropriate, as shown in the following example:
<ServerInstance>
<Analysis>
<InteractionProperties>
<InteractionPropertyAddRemoveValues>false</InteractionPropertyAddRemoveValues>
<InteractionPropertyCalcItemOperations>false</InteractionPropertyCalcItemOperations>
<InteractionPropertyDrill>false</InteractionPropertyDrill>
<InteractionPropertyGroupOperations>false</InteractionPropertyGroupOperations>
<InteractionPropertyInclExclColumns>false</InteractionPropertyInclExclColumns>
<InteractionPropertyMoveColumns>false</InteractionPropertyMoveColumns>
<InteractionPropertyRunningSum>false</InteractionPropertyRunningSum>
<InteractionPropertyShowHideSubTotal>false</InteractionPropertyShowHideSubTotal>
<InteractionPropertySortColumns>false</InteractionPropertySortColumns>
</InteractionProperties>
</Analysis>
</ServerInstance>

For More Refer Oracle Note:
http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/answersconfigset.htm#BIESG3772
18.3.4 Manually Configuring for Interactions in Views

 Result-


Or to individual pivot table report,  add this code in the static view and append it to pivot table view


<script type="text/javascript">
var tds = document.getElementsByTagName('td');
var lCSS = new Array();
for(var td=0;td<tds.length;td++){
if( tds[td].className != 'PTCC' ){
if( tds[td].className != 'PTCHC0 PT_CPLCHLD PTLC' ){
continue;
}
}
tds[td].style.backgroundColor="#FFFFFF";tds[td].style.display='none';
lCSS.push(tds[td].innerHTML);
}
for(var len =0; len < lCSS.length; len++){
}
</script>

Comments are always welcome...

Monday 5 November 2012

OBIEE 11.1.1.6BP1 not showing graphs correct in Chrome

OBIEE 116BP1 graphs dont appear or looks bad in Chrome.
Despite several logins or disabling browser cache, the issue persists.

Try disabling Flash in Chrome and then install the latest version.

The below link should help if you don't know how to disable Flash: http://support.google.com/chrome/bin/answer.py?hl=en&answer=108086

Comments are always welcome...

Sunday 21 October 2012

OBIEE 11.1.1 - How to use OBIEE Impersonate

If you quickly need to check a user permission issue, you can use the Impersonate parameter on the url. However, you have to issue two requests to get a report, you cannot do this in a single url:

Impersonation is "atomic" with authentication. It's not a "request" level parameter. You have to authenticate in "impersonated mode":


Then


Also, you need to make sure that the user "YourAdministrator" has the right permissions in 11g: oracle.bi.server.impersonateUser and possibly oracle.bi.server.queryUserPopulation
 
Refer Oracle link-

Thursday 18 October 2012

OBIEE 11G- Javascript and CSS locations

In OBIEE 11g, the path for Javascript and Custom Style Sheet is located at-

OBIEE\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\

And for calling them-
<script type="text/javascript" src="res/test.js"></script>
<link href="res/test.css" type="text/css" rel="stylesheet">
Refer- https://forums.oracle.com/forums/thread.jspa?threadID=2453825&tstart=0



 

Possible Changes in Oracle BI Enterprise Edition Appearance and Behavior After Upgrade-10g to 11g

Beautifully documented by Oracle for the upgrade changes from 10g to 11g.

http://docs.oracle.com/cd/E21764_01/bi.1111/e16452/apndx_ui_chngs.htm

 

Wednesday 17 October 2012

Gauge Sub-title removed in 11g

In OBIEE 11g Gauge view, Sub-Title is removed from Gauge Titles tab.
People who have migrated from OBIEE 10g to OBIEE 11g having the Sub-Title view in their reports would have to check it out.
May be this too would go into OBIEE 11g pitfalls :)


Comments are always welcome...

Thursday 4 October 2012

Date Format change based on Locale Setting

 
This piece of information is a very base to locale setting.
 
When a user requested to change the date format, as the user couldnt always make the changes after downloading to excel.

So the change was to be made for the users setting instead of going globally.
 
We can either ask the user to login with the Language which is expected to produce the date format.
 
 
 
 
 
 
Else we ask the user to go the Settings-->My Account and make the below changes and finish the task and check the results.
Below is the default setting found for the user with English Language.
 
And the result would be-
 
After the changes in the My Account Setting-


The result would be-



Comments are always welcome...

 
 
 
 

Friday 7 September 2012

Maximum value from Multiple Columns

One of the user in OTN forum had this question, to display the maximum value with MULTIPLE column values.
For Example-
If you have metric columns 1,2,3

             Metric 1        Metric 2       Metric 3
                 1                         2                   3
                 4                         5                   6
In the above example Metric 3 with value 6 should be shown as the maximum value.
Before going ahead read the post- http://obiee10grevisited.blogspot.com/2012/07/highest-value-in-narrative-view-rank.html

This is probably my way to resolve this, but hope to see comments, arguments and other better ways to resolve this issue.
I built a report with Metric 1 column and then made use of the combine with similar request for the second Metric 2 and again combined with Metric 3 column.

And in the column which has the metric columns make its sorting as descending, so that it shows the max value at the first position.

In the narrative view, use any customized message and in the 'Rows to display' set it as 1. So that it always shows the max first value
 
 
And the result...
 

 
Comments are always welcome...



 

Friday 31 August 2012

Changing the background color on hovering in OBIEE

When there is a requirement to have background color to appear on hovering over the text then the below code will help you out.
Use the below code in the Text inside the section with the HTML check box enabled.

<script type="text/javascript">
function changeColor(id, color) {
element = document.getElementById(id);
event.cancelBubble = true;
oldColor = element.currentStyle.background;
element.style.background = color;
}
</script>
<div id="left1" onMouseOver="changeColor(this.id, '#81DAF5');" onMouseOut="changeColor(this.id, '#FFFFFF');"><font size=2>Sample Text</font></div>

The use of div here is to restrict the color change on the particular section only. If we use Body bgcolor, it will  change the color for the whole page.
And in conditions where there are multiple sections, just add the above code and change the div id values.
Hope this will help many people out there. Thanks to javascript :)

Comments are always welcome...
 

Tuesday 7 August 2012

Changing the Date Format from Presentation Variable

In OTN forum I came across a requirement, where the user wanted to change the date format of a presentation variable value.

Assuming the presenatation variable value is acquired through a prompt, I am proceeding by creating a report with a column which is filtered with this presentation variable.


The column in a report which is having a default value with the presentation variable.

Next is to change the data format of the date column, so that we change the format of the date column on receiving the value from the prompt.



Final result.


Comments are always welcome...


Tuesday 24 July 2012

Vertical Alignment of Text

When there is a requirement to show the values into horizontal or vertical alignment, then this post can do the miracle to the requirement.
All we need to do is we need to put the below piece of code in the Column Properties-->Use Custom CSS Style(checked)

Writing-mode: tb-rl; filter: flipv fliph;


I have also put the below code by removing the flipv and fliph keywords in the above code
Writing-mode: tb-rl; filter: flipv;
Writing-mode: tb-rl; filter: fliph;


.


Comments are always welcome...

Monday 23 July 2012

Right and Left Function in OBIEE

This is a basic report, but still wanted to make this post to show the results compared.
When we would need to use the left side or right side values from a column then we do have String Function-Right and Left Function
So in the column formula we can pull the String Functions-Right and Left function and check the Syntax.

Syntax-RIGHT(Column_name,integer) and LEFT(Column_name,integer)




And the result Comparison.


Comments are always welcome...

Sunday 15 July 2012

Starting OBIEE in Linux and Unix

Startup the BI services

Set the BI_HOME to the Oracle BI Home path e.g. /opt/oracle/product/OracleBI

$export BI_HOME=/opt/oracle/product/OracleBI
#Start the BI sever
$$BI_HOME/setup/run-sa.sh start
#Start the presentation service
$$BI_HOME/setup/run-saw.sh-start
#Start the scheduler
$$BI_HOME/setup/run-sch.sh start
#Start the cluster controller – only required when BI servers clustered
$$BI_HOME/setup/run-ccs.sh start

Shutdown the BI services

$export BI_HOME=/opt/oracle/product/OracleBI
#Stop the cluster controller – only required when BI servers clustered
$$BI_HOME/setup/run-ccs.sh stop
#Stop the BI sever
$$BI_HOME/setup/run-sa.sh stop
#Stop the presentation service
$$BI_HOME/setup/run-saw.sh stop
#Stop the scheduler
$$BI_HOME/setup/run-sch.sh stop

Friday 13 July 2012

Different Types of Dimensions


I have just copied the contents from stackoverflow and guess that it would be helpful to many people searching for different types of dimensions existing. And I also welcome people to add dimensions which they have come across and update this post if anything is missing or found wrong.

Regular dimension
Standard star dimension.

Time Dimension A special case of the standard star dimension.

Parent-child dimension
Used to model hierarchical structures, fx BOM (bill of materials).

Snowflake dimension
Can also be used to model hierarchical structures.

Degenerate dimensions
When the dimension attribute is stored as part of fact table, and not in a separate dimension table. Typically used for high cardinality dimensions like "transaction number".

Junk dimension
 A single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table. Junk dimensions are often created to manage the foreign keys created by Rapidly Changing Dimensions. Typically used for low cardinality, non-related dimensions like gender or other booleans.

Role playing dimensions
For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire".

Mini dimensions
For rapidly changing large dimensions. Typically used for managing high frequency, low cardinality change in a dimension.

Conformed dimensions
Implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. Also seen under the name Shared dimension.

Monster Dimension
 A very large dimension.

Shrunk dimension
Is a subset of a dimension’s attributes that apply to a higher level of summary. For example, a Month dimension would be a shrunken dimension of the Date dimension. The Month dimension could be connected to a forecast fact table whose grain is at the monthly level. Dimension.

Inferred Dimensions
While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.

Static Dimension
It not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.

Multi value Dimension
 Is simply a bridge table between the entities involved in the many-to-many relationship. It is also possible that the many-to-many is between a fact and dimension.

Dynamic dimensions. These can be further divided into 2 groups.

Slowly changing dimension/Rapidly changing dimension
Attributes of a dimension that would undergo changes over time
Slowly Growing Dimension/Rapidly Growing Dimension
 Relates to the growth of records/elements in the dimension.

NB: These can then be combined with the size of the dimension table, resulting in "Rapidly Changing Monster Dimension", "Slowly changing mini dimension" etc.

Some other dimensions-

Data Mining Dimensions
 Virtual dimension
 Demographic Dimensions
 Write-Enabled Dimensions
 Dependent Dimensions
 Independent Dimensions
 Primary Dimensions
 Secondary Dimensions
 Tertiary Dimensions
 Informational dimension
Dimension triage dimension
Non-conforming dimensions from the general ledger
 

Thursday 5 July 2012

Conditional Hyperlink/ Removing Conditional Hyperlink

In OTN forum, I came across a good requirement and I tried the same.
Cases where you need to keep/ remove the conditional underline  of a text while showing navigation can be handled by our user friendly HTML script. This can be played under the impression where customized text is required for the navigation.
Below piece of code shows the text to represent without an underline beneath the data values. And also do note that the pointer shape is replaced with default to remove the 'Hand' symbol.  You also need to change the Data format of the column to HTML




Save and see the result...


Comments are always welcome...

Left Indent on OBIEE 11g

I have seen couple of questions raising for changing the indent style of the data values in the OTN forum and also me myself had a requirement in OBIEE 11g to change the style of the data values to indent 20 from left. We found to add the indent properties to 20 and see the change.
But you can also still trust on css properties. And of course, both the trick plays their role perfectly.

Take a reference of the below screenshot-


Save and check the results...

Also try using 'text-indent:30px' and would produce the same results

Comments are always welcome...

IsNumeric or NOT

I needed to show the column values containing numbers as 'Numeric' and the values without the numbers as 'Alphabets'. I would first validate the column values with the case statement, if it is containing any numeric value. If it doesnt contain then it is named as 'Alphabets'
And on top of it I need to show the custom messages back ground with different colors.
I have tried the below method with HTML and with conditional formatting.

First method approach is with HTML and case statement.

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then '<span style="background-color:Red">Numeric</span>'
else '<span style="background-color:Green">Alphabets</span>'
end



Second method with conditional formatting and case statement.

Here you need to add the below code in the column formula

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then 'Numeric'
else 'Alphabets'
end

and in the column properties-->conditional formatting-->add condition




Save and check the results...

Comments are always welcome...


Scope of Variables

You can reference variables in the following areas:

-Title Views
-Narrative Views
-Column Filters
-Column Formulas
-Conditional Formatting conditions (presentation variables only)
-Direct Database Requests
-Dashboard prompts
 Users will be prompted for a variable value which is then set into a request (session)   variable and passed to the Oracle BI server.
-Chart scale markers.
-Gauge range settings.
-Static text.
-iBot Headlines and text

Prompt with Combined Subject Area

Values produced from a single subject area in the prompt can be used to filter the data in the report. But if we need to combine the results of both the columns in two different subject area then we need to combine both the columns using a UNION request in the SQL results.

Lets start with the creation of the prompt. We first pull the column from the first subject area and in Options-->Choice List Values-->SQL Results. We copy the Logical SQL query from the first prompt.
For Example-SELECT "Time"."Per Name Year" FROM "Sample Sales Lite"

Next, we create a second prompt and as the steps mentioned above try to copy the Logical SQL results from the second prompt with a different subject area.
For Example- SELECT "Time"."Per Name Half" FROM "Sample Targets Lite"



We combine the first Logical SQL query with the first logical SQL query with a union between the two SQL clause.
Now the query looks similar to the below-

SELECT "Time"."Per Name Half" FROM "Sample Targets Lite"
union
SELECT "Time"."Per Name Year" FROM "Sample Sales Lite"

Save and check the results...



Comments are always welcome...

UA.bat hangs in OBIEE 11.1.1.6.2


As a reference-
https://forums.oracle.com/forums/thread.jspa?threadID=2400314&tstart=240

As per Christian-

I did a fresh installation of OBIEE 11.1.1.6 on a Windows Server 2008 R2 Standard 64 bit (I installed the 64bit version) and patched it to 11.1.1.6.2. When trying to upgrade the .rpd file from 10.3.4.1 the Upgrade Assistant simply hangs when clicking on the "next" button after supplying the relevant informations. Setting the ORACLE_HOME environment variable as mentioned in the thread above didn't solve the problem. Also I don't have another oracle home on that machine, so I think I started the correct UA.
When looking at the logfile of UA i get those messages:

[2012-06-11T10:36:58.033+02:00] [Framework] [NOTIFICATION] [] [upgrade.Framework] [tid: 11] [ecid: 0000JVQg5GmBt1YzLofP8A1FpQrf000002,0] Please enter the repository password:
[2012-06-11T10:36:58.051+02:00] [Framework] [NOTIFICATION] [] [upgrade.Framework] [tid: 11] [ecid: 0000JVQg5GmBt1YzLofP8A1FpQrf000002,0] Please enter the 10g Admin password:

I get those log file entries immediately after I press the "next" button. Needless to say I entered the correct admin password and a valid repository password. It also hangs with the same messages in the logfile when I only supply the Admin username but no passwords at all, so I guess even though I enter both passwords UA simply doesn't recognize them and fails to present me a error message.
This worked previously in 11.1.1.6.1 (and also in 11.1.1.5), and there where no changes to the .rpd file so I don't think it's the fault of the .rpd file.

Deepika answered it as-

This is an open bug with oracle, refer to the following bug detail in support web,
Bug 14160867: UA UTILITY HANGS AND DOES NOT PROGRESS AFTER STEP 3 ON 11.1.1.6.2

Comments are always welcome...

Change Cursor Types

We can change the shape of the cursor while pointing to the text in the column. This can be achieved by adding a small css cursor property in the column properties in the style fo the column.

For Example-
cursor:move;



And check the results...

Cursor Types to Try-

-auto
-crosshair
-default
-e-resize
-help
-move
-n-resize
-ne-resize
-nw-resize
-pointer
-progress
-s-resize
-se-resize
-sw-resize
-text
-w-resize
-wait
-url(smiley.gif),url(myBall.cur),auto

Comments are always welcome...


Tuesday 3 July 2012

Log Configuration - 11g

Configure the Business Intelligence log files by choosing what level of message gets logged and when a log file expires. Changes apply to all Business Intelligence log files.

Also refer below for the path of the Log files-







Scaling Oracle Business Intelligence

Scaling is the process of increasing or decreasing the capacity of the system by changing the number of processes available to service requests from Oracle Business Intelligence clients. Scaling out a system provides additional capacity, while scaling in a system reduces capacity. Scaling is also a critical part of configuring a deployment for high availability.

Scaling the Oracle Business Intelligence environment applies principally to resource-intensive system processes and Java components. When you deploy more processes, Oracle Business Intelligence can handle more requests while staying responsive to requests.

Vertical scaling involves adding more Oracle Business Intelligence components to the same computer, to make increased use of the hardware resources on that computer. For example, Oracle Business Intelligence can be vertically scaled by increasing the number of system components servicing requests on a given computer and results in increased use of the hardware resources on a given computer.

Horizontal scaling involves adding more computers to the environment. For example, Oracle Business Intelligence is horizontally scaled by distributing the processing of requests across multiple computers.
You can scale both Oracle Business Intelligence Java components and system components. See Section 1.3.3, "About Java Components and System Components for Oracle Business Intelligence" for more information about these components.

The three system components that support both horizontal and vertical scale-out are Oracle BI Presentation Services, the Oracle BI Server, and the JavaHost.

Oracle BI Scheduler uses Presentation Services and Oracle BI Server processes to perform computationally intense work on its behalf, while the Cluster Controller only manages other components and does not itself do any computationally intense work. Because of this, there is no need to scale out either Oracle BI Scheduler or the Cluster Controller. You can distribute these two processes as needed for high availability deployments, but they do not need to be scaled for capacity.

Monday 2 July 2012

Change the Date Format/ Evaluate Function

OTN forum keeps on getting the requests for changing the default date formats. They need to either pass the presentation variable into the desired date formats or change the date formats in the reports. Many blogs would represent different methods or representing them. In this post I will show three different methods of representing the Date formats and a final screen show casing the final comparison of the three methods.

I am pulling four columns-
First is the original Date column
Second is the Evaluate Date column
Third is the Custom Date column
Fourth is the Cast Date column





First method- EVALUATE Function.





Second method- Changing with custom Date format in the Data format.



Third method- Casting with Date format.





And the final comparison of the above three methods.



And also try the below formatting in the RPD side-

-In the physical layer in the schema general properties-try to change the types after searching for DATE.
-the execute on connect of the connection pool write the below code-
ALTER SESSION SET NLS_DATE_FORMAT = 'MON-YYYY'


Comments are always welcome and please add on if any other methods could do the trick.










Highest value in the narrative view/ Rank

We needed to show the Highest value for a measure column in the narrative view. The report for example will consist of two columns- dimension and measure column.
I placed a dummy column for finding the highest value of the measure by using RANK function in the column formula and calculating its position using javascript.


First will drag a dummy column of the measure and use the rank function as shown below-




Next will be using javascript to calculate the appropriate position of the 1st highest value of the measure column.




<script language="javascript" type="text/javascript">
for(var i=0;i<1;i++)
{
var a=@3;
if(a==1)
{
document.getElementById("Format").innerHTML='Biggest Profit-->@2';
}
}
</script>



And save and view the result...




Comments are always welcome...

Friday 29 June 2012

Thousand separator in Narrative View

To display the thousand separator comma for the measure values in the Narrative view Bill suggested to use the HTML format.
https://forums.oracle.com/forums/thread.jspa?threadID=2393458&tstart=15

Use the below format in the narrative view with HTML enabled.

<td id="Format" style ="vertical-align: top; text-align: right; width: 150px;"> @2 <br>
</td>
<script language="javascript" type="text/javascript">
document.getElementById("Format").innerHTML=number_format(@2, 2, '.',',');;
</script>

@2 is the position of the fact measure column.

And also as per Nico-
This is a bug:
NLS:NARRATIVE VIEW DOES NOT USE CRITERIA DATA FORMAT
https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=13791979
And it's fixed in the version 11.1.1.7.0 ..

Comments are always welcome...

Pop Up/ New Window

When the user needs to have a new google search window on click on the value and then search the value by default.
We can have the customized html code in the column formula and change the Data format of the column to HTML.
'<a target="_blank" href="http://www.google.com/search?hl=en&q=' ||col_name||'&btnG=Google+Search">'||col_name|| '</a>'






After clicking on the value-




Also follow the below link for opening to a new window within the report-
http://shahin-obiee.blogspot.in/2012/05/normal-0-false-false-false-en-us-x-none.html
Comments are always welcome...

Data Level Security


I know I am re-inventing the wheel for Data level security. But I want to show the obiee beginners a simple guide to implement the same. Hope people out there struggling to implement Data level security will get a hint to build it.

Data Level security is implemented in the case when the user logging in views the data which he/ she has the access to. This includes to include a system session variable 'USER'to capture the user's logging credentials and apply it to the reports query.

I am having three tables for serving their purpose.
1. Dimension Table - DIM2
2. Fact Table  - FACT1
3. Security Table -SECURITY_1

Data present in the above tables is shown below-











Next we need to import the three tables in the RPD and do the below joins-



Joins with Dimension and Security table in physical Layer

"DIM2"."COUNTRYID" = "SECURITY_1"."COUNTRYID"

Joins with Fact and Security table in physical Layer

"FACT1"."EMPID" = "SECURITY_1"."EMPID"

Joins with Fact and Security table in physical Layer

"DIM2"."COUNTRYID" ="FACT1"."COUNTRY_ID"


In the BMM layer for the dimension table we need to make the below changes. This change is used to apply when the user selects only the dimension column. So that the values which he/ she has the access will be shown after making the joins with the security table. For example- On clicking of the Prompt values.



And the below change in the Content Tab -where clause





In the BMM layer for the fact table we need to make the below changes. Data will be restricted after making joins with the security table and joins with the dimension  table. You can try removing the joins with the dimension and security tables which is not mandatory, since we are joining with the fact and security tables.




And the below change in the Content Tab -where clause



Now its time to view our reports in answers. When the user logs in with 100 as empid then the user is allowed to view only his/ her data.


With the Fact column-




When the user with 200 empid logs in-





Prompt value shown for the user with empid 200

Below are the queries generated-

For Dimension Security

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731
  WHERE ( T222728.EMPID = '100'
  AND T222728.COUNTRYID = T222731.COUNTRYID )
  )
SELECT DISTINCT 0 AS c1, D1.c1 AS c2 FROM SAWITH0 D1 ORDER BY c2

With Fact Dimension columns with Security tables joins. With user with login id as 100

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1,
    T222734.SALARY                      AS c2
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731,
    OBIEE_DBA.FACT1 T222734
  WHERE ( T222728.EMPID = T222734.EMPID
  AND T222728.EMPID     = '100'
  AND T222728.COUNTRYID = T222731.COUNTRYID
  AND T222731.COUNTRYID = T222734.COUNTRY_ID )
  )
SELECT DISTINCT 0 AS c1,
  D1.c1           AS c2,
  D1.c2           AS c3
FROM SAWITH0 D1
ORDER BY c2,
  c3

With user with login id as 200

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1,
    T222734.SALARY                      AS c2
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731,
    OBIEE_DBA.FACT1 T222734
  WHERE ( T222728.EMPID = T222734.EMPID
  AND T222728.EMPID     = '200'
  AND T222728.COUNTRYID = T222731.COUNTRYID
  AND T222731.COUNTRYID = T222734.COUNTRY_ID )
  )
SELECT DISTINCT 0 AS c1,
  D1.c1           AS c2,
  D1.c2           AS c3
FROM SAWITH0 D1
ORDER BY c2,
  c3


Comments are always welcome...