Business Intelligence – Oracle

Archive for the ‘OBI EE Plus’ Category

Oracle BI 10.1.3.3/2 – Pagination in Pivot Tables

Posted by Venkatakrishnan J on January 17, 2008

Another common request that usually comes up is how do we enable Pagination in Pivot Tables. Though this is not available out of the box, lets look at one approach today to have pagination in pivot tables. Lets start with a simple pivot table report as shown below containing 2 columns, DIMENSION_KEY and SALES ( ihave taken this report just as an example).

      

Now our aim is to add pagination to this report. In order to do this lets go back to the criteria tab and add one more column to this report(any column). Go to the formula window of this column and enter the formula shown below

CASE WHEN RCOUNT(1) < 11 THEN ’1-10′
WHEN RCOUNT(1) < 21 THEN ’11-20′
WHEN RCOUNT(1) < 31 THEN ’21-30′
ELSE ’30+’ END

So, what the above code basically does it creates repeating rows 1-10, 11-20 etc. You can modify the above to suit your needs.

      

      

Once this is done, drag and drop this column into the Pages section of your pivot table. Now you can paginate through your pivot table report.

      

Very simple but can be pretty handy in a lot of situations.

Posted in All Posts, OBI EE Plus | 7 Comments »

Oracle BI 10.1.3.3/2 – Selecting Reports from Dashboard Prompts and Guided Navigation Sections

Posted by Venkatakrishnan J on January 17, 2008

While i was away on vacation, a set of pretty good questions came up as comments in the Contact Me page of this blog. Though i never expected this page to get any comments at all, it looks like i was wrong. So, apologies to everyone who have been waiting for a reply from my end. I plan to make a reply as soon as a comment is made from now on. Among those comments, one user had a question on how to go about creating a dashboard prompt to list down specific report names and while selecting the report names the user wanted to show those corresponding reports within the dashboard. I have had this question asked before. Though it is not pretty straight forward to achieve this, lets look at one of the possible approaches today. In order to do this, lets start with a very simple example. You can modify this to suit your needs. In our example, our aim is to make the dashboard prompt to show two report names ‘Brand Sales’ and ‘Region Sales’. Based on what we select in this dashboard prompt we would be showing either the Brand Sales Report or the Region Sales report in the dashboard. Lets start with creating the dashboard prompt. For now we shall be hardcoding the values in the dashboard prompt.

So, create a dashboard prompt on a column (though this column will not be actually used. This is just to trick the BI Server) and in the formula enter the below case statement.

      CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END

As you see the idea is not to filter on the actual column but instead on a dummy value of Brand Sales.

      

Once this is done, go to the Show drop down and go to SQL Results. In the SQL Results enter the below SQL. Also, make the prompt to set a presentation variable say Report

      SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Region Sales’ END FROM SH2 UNION ALL SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END FROM SH2

      

As you see above, what we are basically doing is making the dashboard prompt to print our Report Names and then we are making the presentation variable to accept these report name values. Now the next step is to create 2 reports that each will return rows for one of the above selections and zero rows for the other selection so that we can use this as a source for our guided navigation section.

      

      

      

What we are basically doing above is creating a report that just has a column with a dummy value Region Sales. And a filter is applied on this report with the presentation variable Report so that this report will return non-zero rows when we choose Region Sales value in the dashboard prompt. Similarly this report will return zero rows if we choose Brand Sales value in the dashboard prompt. Create another report as shown above but in this case make it to return non-zero rows when Brand Sales is chosen.

Now go to the dashboard and include the dashboard prompt that we created in a normal section. Now included 2 other sections below this dashboard prompt and each of these sections would be a guided navigation section. Include the region sales report in GN section and Brand Sales report in the other GN section. Go to the GN section properties containing the Region Sales Report and in the source, reference the report that we created above and make it to show only when it returns some rows. Similarly, for the GN section containing the brand sales report, reference the second report that we created above and make it to show only when it returns non-zero rows.

      

      

Now if you go choose Brand Sales in the Dashboard prompt, you would see that the Brand Sales report would be listed. Similarly if you choose Region Sales, then the Region Sales report would be shown.

      

      

The idea is pretty straight-forward. But this can be used especially if you want to allow your users to choose multiple reports.

Posted in All Posts, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.3/2 – Input box data validation using Guided Navigation Sections

Posted by Venkatakrishnan J on January 12, 2008

Couple of days back we had seen how to go about achieving input box data validation using custom Java scripts here. One of the drawbacks as mentioned there was that it applies the validation to all your edit prompts. Though you can still further modify the java script to make it work only for your dashboard prompt but again that would entail lot of custom coding from our end which sometimes is a overkill for such a small feature. So, lets look at another approach today which would use Guided Navigation sections. Lets start with an example wherein we have a dashboard prompt on AMOUNT_SOLD column (using between). Below the dashboard prompt we have a report containing PRODUCT_NAME and AMOUNT_SOLD(this contains a filter on AMOUNT_SOLD with is prompted clause). So, first lets check whether the filter works by entering proper values.

      

The problem with this prompt is if we enter some char values you would find that this results in an error which many users would not like.

      

So our aim is to hide this report whenever wrong data is entered rather than this error. In order to achieve this go to the dashboard edit page and ensure that you have both the prompt and the report in different sections (if you have more reports this report alone should have a seperate section). Now go to the section properties(the section that contains the report) and click on Guided Navigation. In the Guided Navigation properties, reference the source request and reference the same report that we have used above. So, basically our aim is to hide this section when it does not return any data or any error.

      

      

Lets test it out by giving some character data.

      

As you would see this would not show the report at all if we enter any wrong data which is better than the error message.

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables

Posted by Venkatakrishnan J on January 10, 2008

I had a couple of emails referring to a particular new features guide here. It has been said in this new features guide that one can achieve highly formatted financial layouts and also an example is given in the form of screenshots. A couple of users wanted to know how to go about achieving the same. Now lets start with exactly the same pivot table as shown in the guide above and try to achieve the same.

      

In the final layout, we only need the BalanceGroup1, AccountDesc and Amount columns. So lets hide the remaining 2 i.e BalanceGroup2 and BalanceGroup3. We are not removing these columns since we need sub group sums.

      

Once this is done, if you look at the final layout required, there is a line break between BalanceGroup1 and Account Desc. So, we would have to move the BalanceGroup1 column to the Sections area.

      

If you notice, we still have to position the Assets to the left and then remove the tabular headings. In order to achieve this, go to the Section Properties and insert Page Break on the BalanceGroup1 column. After that go to the Measure Labels section and hide the headers.

      

      

Now, we need to include to the sub-totals for both BalanceGroup2 and BalanceGroup3.

      

Next is to left Align the Assets value. Go to the Section Properties and change the Horizontal Cell Alignment to Left.

      

Next we need to change the label names to something Total Assets etc. In order to do this we shall use “Total @”. @ will bring the value of the column.

      

Now the next step is to add indentation to all the Totals and subtotals. Go to the hidden BalanceGroup2 total Label properties and add a left indentation of 25. Similarly add the left indentation for BalanceGroup2 to 50 and AccountDesc values to 75.

      

      

Our result so far will be like this.

      

Now the next step is to remove all the border lines. In order to do this go to all the Columns and Section properties and change the Border Position to None. For example go to AccountDesc Format Values and change the position to None as shown below.

      

      

The following pic shows all the places that you need to change the Border Position to None.

      

The next step is to change the background color of the subtotals and the AccountDesc to #FFFF (that will give a white background). And lastly if you want total values to have 2 horizontal bars on the top just go to border position of the sub total and include the top two borders.

      

And this should give you the desired Financial Template Layout.

      

I know some of the above is pretty trivial. But this should give you an idea of how to achieve very good formatting with pivot tables.

Posted in All Posts, OBI EE Plus | 16 Comments »

Oracle BI EE 10.1.3.3/2 – Dashboard Prompt Edit box input validation

Posted by Venkatakrishnan J on January 8, 2008

Just a Word of Caution: The example that we are going to try today would affect all your edit box prompts. Just use caution and modify the below example according to your needs.

The example that we are going to try today is to add some simple validation criterion to the edit box in the Dashboard Prompt i.e. allow users to enter only numeric values. If they enter anything else then pop up an error message asking them to try again (without refreshing the whole page). This example deals with modifying the underlying Java Scripts. The steps are as follows

1.   Go to {OracleBI}\web\app\res\b_mozilla\prompts. There you would find a file called as globalfilterprompt.js. All the methods that make the dashboard prompts work are present in this file.

2.   Now open this file in a text editor and search for GFPBuildFilter(). This is the function that builds the filter for Dashboard Prompt. Replace this function with the one below.

function GFPBuildFilter()
{
var tExpr = XUICreateElement(saw.xml.kSawxNamespace, ‘expr’);

var tArgs = GFPBuildFilter.arguments;
var tF = new GFPQueryFilter(tArgs[0],tArgs[1]);

var tSQLExpr = XUICreateElement(saw.xml.kSawxNamespace, ‘expr’);
tSQLExpr.setAttribute(“xsi:type”, “sawx:sqlExpression”);
XUISetElementText(tSQLExpr, tArgs[0]);
tExpr.appendChild(tSQLExpr);

var bNoVal = true;
var sNewOp = tF.sOp;

// MBH6 shows some errors where GFP still has eq for some reason. Most surgical fix
// is to uncomment this check.
if (sNewOp == “eq”)
sNewOp = “in”;

if (sNewOp == “bet”)
sNewOp = “between”;

var sType = tArgs[2];

var tValues = new Array();
if (sType == “multi”)
tValues = GFPParseArgs(tArgs)
else
{
for (var i = 5; i < tArgs.length; ++i)
{
tValues[tValues.length] = GFPRightTrim(tArgs[i]);
}
}

// is a variable to be set?
var sSetVariable = tArgs[4];

//convert datetime; sTimeZoneOffset is ’0′ if data type is not datetime or type is ‘drop’
var sTimeZoneOffset = tArgs[3];
var nOffset = parseInt(sTimeZoneOffset)
if (nOffset)
{
var tDTP = new DateTimeParser();
for (var i = 0; i < tValues.length; ++i)
{
tValues[i] = saw.tz.parseAndAdjustTimeZoneOffset(tValues[i], nOffset);
}
}

// if more arguments, then values
for (var i = 0; i < tValues.length; ++i)
{
var sVal = tValues[i];

if ((sVal == ksDropDownNone || !sVal) && sNewOp != “between”) //if sVal is empty, it is consider the value is not specified. We should not change the operator to NULL in this case.
return;
else if ((sVal == ksDropDownNone || !sVal) && sNewOp == “between”)
continue;
else if ((sVal == ksDropDownAllChoices || sVal == ksEditBoxAllChoices) && sNewOp == “between”)
{
//treat all choices in between op as blank but always apply the filter
bNoVal = false;
continue;
}

if (sVal == “(All Choices)”)
sVal = ksDropDownAllChoices;

// check for null or just whitespace
/*if (sVal == ksDropDownUnspecified)
{
sNewOp = “ignore”;
bNoVal = false;
}*/

if (sType == “edit” && sVal != “”)
{
var validation = /^[0-9]+$/;
if (!sVal.match(validation))
{
alert(“Only Numbers Allowed Here”);
return;
}
}

if (sType == “edit” && sVal == “”)
return;

// if all choices
else if ((sVal == ksDropDownAllChoices) || (sVal == ksEditBoxAllChoices))
{
sNewOp = “prompted”;
bNoVal = false;
break;
}

// if null
else if (sVal == ksDropDownNull || !sVal)
{
sNewOp = “null”;
bNoVal = false;
}

// if they specified a value
else if (sVal != null && (sVal.search(/^\s*$/) == -1))
{
bNoVal = false;
// check for operators we allow user to override
if (sType == “edit” && ((tF.sOp == “in”) || (tF.sOp ==”like”)))
{
// Check for operator
if (sVal.search(/^=/) != -1)
{
sNewOp = “in”
sVal = sVal.replace(/^=\s*/,””);
}
else if (sVal.search(/^/) != -1)
{
sNewOp = “notIn”;
sVal = sVal.replace(/^\s*/,””);
}
else if (sVal.search(/^<=/) != -1)
{
sNewOp = “lessOrEqual”;
sVal = sVal.replace(/^=/) != -1)
{
sNewOp = “greaterOrEqual”;
sVal = sVal.replace(/^>=\s*/,””);
}
else if (sVal.search(/^</) != -1)
{
sNewOp = “less”;
sVal = sVal.replace(/^/) != -1)
{
sNewOp = “greater”;
sVal = sVal.replace(/^>\s*/,””);
}
}
tF.AddPredArg(tExpr, sVal);

}
}

if (bNoVal)
{
// JPR 8/05 – this check is causing problems with between since it was forcing to prompted.
// We can’t determine why this sType != “drop” was ever necessary, so we’re returning in
// all cases when there is no value
// if (sType != “drop”)
return;

// JPR 8/05 – the comment below makes no sense to us, assuming old logic and returning above now
// Change to prompt as ignore prevents users from blanking out an entry.
//tF.sOp = “prompted”;
//sNewOp = “prompted”;
}

/* if (tF.sOp == “in” && tF.vValues.length == 1)
sNewOp = “equal”;
*/
if (tF.sOp == “cany”)
sNewOp = “containsAny”;
if (tF.sOp == “call”)
sNewOp = “containsAll”;
if (tF.sOp == “bwith”)
sNewOp = “beginsWith”;
if (tF.sOp == “ewith”)
sNewOp = “endsWith”;

// between must have two values
if (sNewOp == “between”)
{
var sVal = tValues[0];
var sVal2 = tValues[1];

// var bVal = (sVal && (sVal != ksDropDownAllChoices) && (sVal != ksEditBoxAllChoices));
// var bVal = (sVal2 && (sVal2 != ksDropDownAllChoices) && (sVal2 != ksEditBoxAllChoices));
if (sVal == ksDropDownAllChoices || sVal == ksEditBoxAllChoices || sVal == ksDropDownNone)
sVal = “”;

if (sVal2 == ksDropDownAllChoices || sVal2 == ksEditBoxAllChoices || sVal2 == ksDropDownNone)
sVal2 = “”;

if (!(sVal && sVal2))
{
if (!sVal && sVal2)
tF.sOp = “lessOrEqual”;
else if (sVal && !sVal2)
tF.sOp = “greaterOrEqual”;
else
tF.sOp = “prompted”;
}
}
else
tF.sOp = sNewOp;

GFPGetOpType(tExpr, tF.sOp);
//tExpr.setAttribute(“xsi:type”, sType);
tExpr.setAttribute(“op”, tF.sOp);

if (null != sSetVariable && sSetVariable != “”)
XUISetAttributeString(tExpr, “setVariable”, sSetVariable);

return tExpr;
}

The code that we have added extra to the actual function is the one below

if (sType == “edit” && sVal != “”)
{
var validation = /^[0-9]+$/;
if (!sVal.match(validation))
{
alert(“Only Numbers Allowed Here”);
return;
}
}

What this does is, it checks whether we are calling this from the Edit box. If yes, then we make the validation of “Only Numbers are Allowed here”. Then save this file.

3.   Copy this file to {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts i.e we need to overwrite the globalfilterprompt.js that is present in this directory so that both the java scripts are in sync.

4.   Restart the presentation services and OC4J. Now if you enter some characters in the dashboard prompt it will throw an error.

      

      

      

Remember this will affect all your dashboard prompts. You would have to add more conditions to narrow it down to a single edit box in a particular page. This would give you an idea of how to modify the underlying Java Scripts.

Posted in All Posts, OBI EE Plus | 6 Comments »

Oracle BI EE 10.1.3.3.2 – New Release – Essbase Support is now available

Posted by Venkatakrishnan J on January 7, 2008

Thanks to Bryan for this info. Somehow Bryan seems to be the first in finding out these new releases :-). Thanks Bryan. The new features that have been added in this release are

1. The long awaited Hyperion Essbase support as a Data Source using XMLA.
2. Listener config for IIS. This is for enabling support for routing BIP and BI Office requests to OC4J directly from IIS (one cannot configure BIP and BI for Office in IIS).

One can download the documentation here and the software here

In addition to that 7.9.4 version of BI Applications has also been released.

Posted in All Posts, General, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Rename My Dashboard

Posted by Venkatakrishnan J on January 7, 2008

Lets look at another simple question today. How do we rename “My Dashboard” for all the users to say “Personal Dashboard”. In order to do this all we need to do is to make a small change to an XML message. The list of steps are given below

1.   Go to {OracleBI}\web\msgdb\l_en\messages. You would see a file called as uimessages.xml.

2.   Copy this file to {OracleBIData}\web\msgdb\l_en\customMessages. If you do not have l_en\customMessages folder then create them. Remember this is a language specific message. Hence we cannot copy this directly to {OracleBIData}\web\msgdb\customMessages as we had done earlier.

3.   Search for the message kmsgUIMyPortal. This is the message that handles the display property of your dashboard. Change it to say Personal Dashboard.

      

      

4.   Restart presentation services. Now you would find that the display of My Dashboard would have changed to Personal Dashboard.

      

Very simple but can be useful in some situations.

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.3/2 – Customizing Download Links

Posted by Venkatakrishnan J on January 3, 2008

Lets look at how to go about customizing the download links of reports today. Basically our aim is to customize the list of links that one would get by clicking on the download link. Some organizations might want to impose strict rules on the users by restricting the possible download options. For example one might not want to have the Download to powerpoint and Download to Excel options. Lets see how to go about achieving this step by step.

1.   First let us see include a report in the dashboard with the Download Option enabled. Our aim is to remove the top 2 download options i.e “Download to Powerpoint” and “Download to Excel”

      

2.   Go to {OracleBI}\web\msgdb\messages folder. You would find a file called as viewscontrolmessages.xml. Copy this file and paste it into {OracleBIData}\web\msgdb\customMessages (if you do not have this folder then create it). We are copying this file to override certain messages that produce the download options.

3.   Open this copied file in a text editor and search for the web message kmsgEVCDownloadLinks. You would find something like this

      

4.   Now arrange this webmessage in a format we can understand like the one below.

      

If you notice you would find messages pointing to each of the download links (the ones that have a class NQWMenuItem attached to itself). Now remove the 2 lines (starting from <a> to <\a> corresponding to kmsgEVCLinkDownloadExcel and kmsgEVCLinkDownloadPowerpoint web messages)

     

5.   Save the file and restart presentation services. You would now see that you would not be having the Download to Powerpoint and Download to Excel

     

Posted in All Posts, OBI EE Plus | 4 Comments »

Oracle BI EE 10.1.3.3/2 – iBot Results within Dashboard Sections and Auto-Refreshing Sections

Posted by Venkatakrishnan J on January 2, 2008

We will be seeing 2 topics today which are quite interesting especially for users who send a lot reports to end-users using iBots. The first topic deals with the display of scheduled reports(what is being sent to end users) within a custom dashboard section (not from the Alerts link). This could come handy especially for administrators who want to see what was sent to users without navigating to the Alerts section. First lets start with understanding how to display all the alerts/reports that are actually sent to different users. In order to get a list of all the ibots that are sent to you(Interactive Dashboard should be part of your Active Delivery Profile) create a section in your Dashboard and rename that section as _Delivers. That will show all the list of ibots.

      

      

Remember, for the above to work you need to have atleast one report within your dashboard. Now you can see the list of reports that have been sent to you within your dashboard. But our aim is to see the actual reports instead of a list. So, now lets find out what is the actual URL for report URLs displayed above. In order to do this you can either use the Firebug extension mentioned here or just review the HTML source of the dashboard page.

      

As you would see the actual URL of the report would be something like the one below(in this example i am taking the URL of the Sample Report shown above),

saw.dll?CheckDelivery&_scid=QLwHBBnYtHM&EncDelName=UgBlAHAAbwByAHQAIABCAHUAcgBzAHQAMQAgAFoAYQB5AE8AYQBTAEwAdgBzADIAMQBWADkAZQBmACsAVABzADYAMQBuAGcA

CheckDelivery is the command that fetches the ibot and _scid can vary dynamically(this is not required).
EncDelName is a dynamically generated name by OBI EE that remains constant once an ibot is saved first (even if change any parameters of the ibot this name will remain constant).

Lets test out the actual iBot report that was delivered using the below URL

http://localhost:9704/analytics/saw.dll?CheckDelivery&EncDelName=UgBlAHAAbwByAHQAIABCAHUAcgBzAHQAMQAgAFoAYQB5AE8AYQBTAEwAdgBzADIAMQBWADkAZQBmACsAVABzADYAMQBuAGcA

      

You can also enter the NQUser and NQPassword to pass the username and password directly to get the report directly. Now lets put this URL in a embedded content section and see whether we are getting the report inside the dashboard.

      

Once this is done now lets go back to the ibot and change the delivery content to another report(this is to ensure that we are seeing the changes that is being done to the ibot.

      

      

As you would see above the report would change automatically based on what was sent in the iBot. Now lets move on to the second topic which is to enable auto-refreshing of sections within a dashboard. This is one the features that would enable someone to have realtime reporting in BI EE. But again before doing this beware of the excessive overheads that you might incur because of constant refreshing. This is based on iframes and meta tags of HTML. Lets start with a simple example. Our aim is to have a dashboard and a simple report within this dashboard. The dashboard should not refresh but the report has to constantly refresh after certain regular intervals. In order to do this lets start with creating a simple report.

      

Go to the narrative view of this report and enter the following html

 <head>
<meta http-equiv=”refresh” content=”10″ />
<iframe src=”http://localhost:9704/analytics/saw.dll?GO&nquser=Administrator&nqpassword=welcome1&path=/shared/paint+demo/demo+reports/Brand+Sales” width=”320″ height=”300″></iframe>
</head>

The iframe points to the same report of which it is a part of. The meta tag gives the refresh capabilities. The above report basically refreshes after every 10 seconds. As you would see this would refresh only this section and not the entire report. Of course there are certain caveats while using this since this refresh does not clear some cookies. But this can help you in achieving a basic refresh mechanism.

     

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.2/3 – Dual Table Equivalence

Posted by Venkatakrishnan J on December 31, 2007

I had received a couple of emails on how to have a table equivalent to DUAL(of Oracle Database) in BI EE. One of the major advantages of the Dual table in an Oracle Database is that you can supply some values in a select statement and the dual table will print it out for you(of course i am stating a very basic use here. There can be a variety of uses depending on how you use the dual table). Now why do we want a similar one in BI EE. Both the emails stated that in BI EE it is not possible to create a report that will print out some dummy values without including a dummy column and hiding the column. Say for example, i need a report that will just print a presentation variable or a simple dummy value like ‘Test’. If you just enter ‘Test’ or the ‘@{variable}’ in the only column that you have in the report you would get the following error while viewing the results.

      

How do we avoid this error. There are 2 approaches. One is to create a dummy column containing a presentation layer column and hide that column. This will bypass your “Query not referencing any table error”.

      

      

But both the emails suggested not wanting to use this approach. Then in that case use the 2nd approach which is to use a single column formula like the one below

CASE WHEN 1=0 THEN PRODUCTS.TOTAL_NAME ELSE ‘Test’ END

where PRODUCTS.TOTAL_NAME will be a column from the presentation layer. The trick is to reference a column within the query but which will never be used. In the above case, 1=0 will never be true and hence ‘Test’ will always be printed. In this case you dont have to include another column and hide that column. But again this is not an exact substitute to the dual table since the query never hits the dual table. Instead the same query (using the above case statement) will be fired on the PRODUCTS table since we have used one of its columns. But till we get a similar dual functionality(i dont think we ever will :-)) use any of the above 2 mentioned methods.

      

P.S: Advanced New Year Wishes to Everyone!!!

Posted in All Posts, OBI EE Plus | 5 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers