Frank Davis' Blog: OBIEE ONE

The current global go script I have not only produces one ‘go’ button for all prompts on a page; but, it also has code to create required fields and required field validations. The one thing that was brought to my attention was that my current script treated the prompt collective on the page to have dashboard scope. To read about prompt scope, read here. So whether or not you had one or all set for page scope the global go button disregarded and made it act like dashboard scope. So that any selections on the page would carry over on other dashboard pages. So I have updated my script so that it will use the scope from the first prompt as the entire pages scope for its prompts.  I have added and updated the script on my 2nd blog posting which had the new required field validation, here. For the instructions, on how to setup look here.

-Cheers

Read Full Post »

Some may think that the scope of the prompts do not work and are broken; but, they do work and are not broken! You just need to know what it really does.  So when you specify a dashboard prompt as scope-> ‘page’, what this does is say that any selections made for this prompt will not be used to set other prompts within the dashboard.  When you specify a dashboard prompt’s scope-> ‘dashboard’, this says that any selection of this prompt will be reflected in other prompts within the dashboard.  I think people get it confused and think that the ‘page’ scope shields the prompt from receiving selections from other ‘dashboard’ scope prompts; but, it does not.  The scope is only there to specify if the selection within the prompt will be local (page) or global (dashboard).

So for any issues where your prompt is still being set by other page prompts within the dashboard, the reason is due to the other prompt having scope-> ‘dashboard’.  If you do not want this behavior within the dashboard, set all the prompts’ scope to ‘page’.  So with this knowledge you will know when to set a prompt to ‘dashboard’ or ‘page’.  ‘Overview’ or ‘Summary’ dashboard pages are a good example of using ‘dashboard’ scope for its prompts, while the other pages in the dashboard are set for  ‘page’ scope.

Read Full Post »

I came across this bug/issue that some may encounter.  The issue is with the page controls for the table view and them not working.  If you come across it that the page controls do not work or navigate to the next page of records, then the first thing to look at is your compound view which is the default view set within dashboard pages.  This only happens in compounds views!  Look to see if there are any other views that have been deleted but not deleted in the compound view like here:

Now within the compound view, if you delete a view it just deletes it from the compound view and not the actual view itself.  It also stands that if you delete the actual view it does not remove the reference within the compound view and thus what you see above may result and paging within table views will no longer work for those dashboards using this compound view.  Reference is the key as compound views just store a reference to the views that compromise the compound view itself.  So when a view is deleted, make sure that if it was referenced in the compound view, that the reference is also deleted.

Read Full Post »

Calculating grand total column or % of grand total is easy in pivot table view.  But how can I create the same in a table view?  Well in table view you can create a grand total row with the table view options!  But, that is not what we want.  We want it so that you either have grand total as a column or % grand total.  To do this I will be using sample sales to demonstrate.

First we create the base report.  This base report will have the Month column and the monthly revenue.

The result of this base report is:

Now we want to include the grand total as a column for each row.  To do this we create a new column called grand total.

With expression:

See what we did!  We wrote the expression formula to sum() the metric.  This metric is already defined in the metadata or BMM layer as a metric with aggregation rule of sum.  So by wrapping or using this metric within a sum() within the report, we are telling the BI Server we want the sum of the aggregated sum(s) or pretty much the Grand total.  Here are the results:

Now let’s say we do not want to show the Grand Total column per row but the actual % of Grand Total.  Here we change the Grand Total columns column heading and expression:

Note:  We also needed to change the column properties Data Format to properly show the data in the correct format.

Here are the results.

So there is how you create a “Grand Total” or “% grand total” in table view.

Read Full Post »

I have been seeing a lot where people take paint or samplesales webcat and either cloning it or modifying it to make a new webcat.  This has its issues and problems.  For one, you inherit all the users and privileges that are there, especially if you were using and modifying this before you decided that it was not a good idea and that you should have a webcat unique for your area or application.

Why do all that work? There is a faster and better way to make a new webcat.  Fastest and easiest way to make brand new webcat:

1. Modify ORACLEBIDATA_HOME/web/config/instanceconfig.xml. Change location of webcat to that of the new webcat.  For my example, I changed from samplesales to testCat.

E:/OracleBIData/web/catalog/testCat</CatalogPath>

2. Restart Presentation Services

Thats it! Simple right.  Well this is what is happening when you restart presentation services.  Presentation Services looks at configuration and see that the webcat to use is testCat.  It looks for webcat in the location specified to load.  If it does not find it, it creates one for you.

Read Full Post »

It has been a long time, couple of months, since I last posted.  But busy beaver that I am, took me away from blogging.  Anyways, I got a breather and will add-on.

Filters and the ‘is prompted’ operator.  Many might think they know how this works and get shocked when things just don’t quite work out the way they do.  For instance lets say for some reason, you created an answers request which has ‘QTR’ filtered for a static value, lets say ’2007 Q4′.  This report then is pretty static right. It is a report for only that given quarter.  Add this to a dashboard page which has a prompt set for same ‘qtr’ field.  And play around with selecting values and submitting it.  What, the report changes!!! “How can this be, I did not set that field to ‘is prompted’ operator!”  As you scratch your head and ponder what has just happened.  Well it appears that adding static filters to a report and adding to a dashboard with same field filtered does the following.  If no values are selected, the report will always be for ’2007 Q4′.  But when the user selects different quarters, the static (default) value will be overwritten with the users selections.  So the prompted values over rule any static filters added.  Unless of course, how Nag commented below and reminded me,  the criteria filter protection option allows the user/developer to protect the filter from this overwriting rule from dash prompts.

Now lets say we add two filters to same said report for same field ‘qtr’.  One is for static value ’2007 Q4′ and the other is for ‘is prompted’.  You might think that the report will now correct itself, that is in your mind, and filter by the static value ‘AND’ the selected prompted values.  WRONG!  The prompted values will now be in both filter criteria as shown below with the filter view.

Changing the filter criteria logic to ‘OR’ does not help either.  As seen here:

Now lets see if setting the filter criteria to a variable, lets say a repository variable.  What happens now?  Same thing!  The filter’s bound values will be overwritten by selected value(s) from the prompt.

Now how to get around this.  Well, prompts replace the bound values by the fields they are representing.  For our example qtr field.  We can fake this or alter the behavior either by changing the prompt expression or filter expression as easily as:

case when 1=1 then qtr end  = ’2007 Q4′ and keeping the other filter is prompted.  Since the expression for the filter is just not ‘qtr’ anymore it will not be overwritten.  As follows:

This was done using ‘AND’ logic.  We just change the logic to ‘OR’ and we will get the static value and the selected values.

So remember or think about this when you have a filter not working quite like you expect it to.  This will give you more options in your filtering and prompting.  But please keep in mind whatever you use in the filter, like the case statement I used as example goes to the DBMS in the predicate and if that field is indexed, the index will no longer be used for that predicate.

Read Full Post »

I have been asked to create custom functionality whereby certain dashboard prompts are required, and the user should not be able to continue until all required fields are selected before they can query.  So I ventured out and created the following script below to do just that.  This script is in addition to the ‘Global’ Go button, or what I referred to in a previous post as the ‘Custom’ Go button.  What this script does is allow the developer to specify which prompt fields they would like to be required.  If any of the fields are not populated by the user an alert/warning box will popup specifying which fields are required in which they did not populate.  This is done in one alert window instead of multiple in order to not be annoying and done correctly.  The rest of the javascript which pulls the data and posts to the presentation server will not be fired, thus the request is blocked until all required fields are populated.

The script is here and the description/breakdown follows:

<script> function GlobalGo(){
try{
var aElm = document.getElementsByTagName(‘table’);
var tTableArray = new Array();
var k = 0;

//Required Field validation variables
var aSpanElm = document.getElementsByTagName(‘span’);
var aReqFields = new Array();
var oneTimeValExists = ‘N’;
//var oneXXXValExists = ‘N’;
var sCaption;
var errMsg = ”;
var i = 0;
var aDivElm = document.getElementsByTagName(‘div’);
var scopeId;
var scopeIndex;
var sViewId;

//BEGIN ********** DEVELOPER(s): Add Required Fields here
aReqFields[0] = ‘Brand’;
aReqFields[1] = ‘Region’;
//END **********

//Get Captions and Controls to check for required fields/values
for(var a=0; a<aSpanElm.length; a++){

//Get Caption
if(aSpanElm[a].className==’GFPCaption’){
sCaption = aSpanElm[a].firstChild.data;
}

//Check if required value exists
if(aSpanElm[a].className==’GFPControl’){

var aInput = aSpanElm[a].getElementsByTagName(‘input’); // multi-select and text boxes
var aOption = aSpanElm[a].getElementsByTagName(‘option’);  // dropdown

//Check if a required field
for(var b=0; b<aReqFields.length; b++){
if(sCaption==aReqFields[b]){
var hasValue = ‘N’;

//Check if multi-select or text box with caption has value
for(var c=0; c<aInput.length; c++){

if(aInput[c].value.length > 0){
hasValue = ‘Y’;
}
}

//Check if dropdown with caption has value
for(var c=0; c<aOption.length; c++){

if(aOption[c].selected && aOption[c].value.trim().length > 0){
hasValue = ‘Y’;
}
}

//if Value does not exists add to errMsg

if(hasValue==’N'){errMsg = errMsg+’* ‘ + aReqFields[b] +’ is a Required Field!\n’;}
}
} //EndFor required Fields

//BEGIN ********** DEVELOPER(s): Add Time Fields here
//check if time field
if(sCaption==’Fiscal Period’ || sCaption==’Fiscal Week’ || sCaption==’Dates’|| sCaption==’Year’){
for(var c=0; c<aInput.length; c++){

if(aInput[c].value.trim().length > 0){
oneTimeValExists = ‘Y’;
}
}
} //Endif check time field
//END **********

//BEGIN ********** DEVELOPER(s): This is template check for ‘OR’ed fields or (At Least one)
//check if time field
//if(sCaption==’Caption1′ || sCaption==’Caption2′ || sCaption==’Caption3′){
//  for(var c=0; c<aInput.length; c++){
//
//    if(aInput[c].value.trim().length > 0){
//      {Need to add one time exists variable here} = ‘Y’;
//    }
//  }
//} //Endif field
//END **********

} //Endif GFPControl
} //EndFor Caption and Controls

if(oneTimeValExists==’N'){errMsg = ‘* At least one time field is Required!\n’+errMsg;}
//{Add same if block above for each ‘At least’ that you created with errmsg}

if(errMsg != ”){
alert(errMsg);
return;
}

for(var i=0; i<aElm.length; i++){
if(aElm[i].className==’GFPBox’){
tTableArray[k] = document.getElementById(aElm[i].id);
k++;
}
} //EndFor Get Prompt Values to send in Post

//Get the sViewId in order to set the pages prompt scope
 for(var f=0; f<aDivElm.length; f++){
   scopeId = aDivElm[f].id;
   scopeIndex = scopeId.indexOf(“Scope”);
   if(scopeIndex != -1){
      sViewId = scopeId.substring(0, scopeIndex);
      //alert(sViewId);
      break;
   }
 }

GFPDoFilters_samvi(sViewId, tTableArray, true);

}
catch(e){alert(‘XXX ‘ + e);}
}

String.prototype.trim = function() {
return this.replace(/^\s*|\s*$/, “”);
}
</script>

<div align=”left”><a href=”#” onclick=”javascript:GlobalGo();”>Go</a></div>

<div align=”left”><a href=”#” onclick=”return
PersonalizationEditor.removeDefaultSelection(false) “>Clear</a></div>

Here are the notes:

• The section in red: This is where the developer will add the required fields into the array.  Each field added needs to increment the array index or it will over write.  The names put in the required field area are the Dashboard prompt captions.
• The sections in blue: This section is for the time field validation.  This checks that at least one time field is populated with data.  It also highlights the errMsg used.
• The sections in purple: This is where a developer can use as a template to create the scenario like time where ‘at least’ one field needs to be used.
• The sections in green: This is the additional code which sets the prompts’ scope for the page to the first prompt’s scope.  So if the first prompt’s scope is page, all the prompts for the entire page will share the same scope else the entire pages prompts use dashboard scope.

Here is a screen shot of it working within SampleSales.  One of the required fields is selected (Brand).  And as you can see the alert is shown signifying to the user that ‘at least one time field’ and ‘Region’ are required.  The report is not fired for these prompts until all required validation is met.

Required Field Message

Until next time…I am currently working on script to stop the dashboard page from firing off report queries on entry.  Almost complete this should be added shortly.

-Frank

Read Full Post »

There are alot of users that wish that reports within dashboards would not fire automatically once entering a dashboard or dashboard page.  So what is the solution?   There is a pretty good solution to this problem out there that uses the concept of guided navigation; but, it doesn’t work.  On the surface it looks like it works but in reality the report queries are still being fired off, as one would be able to see via the NQQUERY.LOG.  Now keep in mind if caching is turned on, and the report queries are cached you may not see the queries in the log file.  But you most definitely will see that they were fired off if you turn caching off.  Now my solution to the problem is as follows:

You will need Dashboard->”Save Selections”, and Dashboard->”Assign Default Selections” privileges.

1. Put the reports that are required to not fire off within a collapsible dashboard section.  The collapsible property is a section property.  Save dashboard page.

2. Now go to your dashboard and the dashboard page.  Clear out all the dashboard prompts’ values.

3. Collapse the sections you do not want to be fired off.

4. Select “page options”->”save current selections”->”For Others”.   A “Save Current Selections” box will appear like below:

Put in the selection name, check off “Make this my default for this page” and then click “Set Permissions”.  Here in the permissions select all groups you want to apply this default selection to, which most likely is all of them.

And you are finished.

One point to make.  Some may ask, Why did I have to clear out dashboard prompts and Won’t my saved selections now not show the default values of the prompts that are set.  The answer is, we cleared out the values of the prompts so that the selection we are saving only saves that the sections are collapsed and that we are not saving any prompt values.  When the prompts are clear it is not saying to clear out prompts whose default values are set via the dashboard prompts.  On the contrary, you will see when you go back to this dashboard page that the default prompts are correctly set and the sections are collapsed.

This might not be the most elaborate solution since the sections are collapsed and it entails that users uncollapse the sections to see the reports and fire them off.  But for the current release 10.3.4.1 this is the best solution that I could come up with that worked.

Now what this will do is show your dashboard prompts and all the sections.  When a user want to then see a report and fire off the query all they will have to do is click on the collapsible button, which will open it up and fire off all reports contained.

How this works is that all sections that are collapsed are disabled and will not be enabled until opening up.  The guided navigation solution fired reports off since all that it does is hide the section but the section is still enabled.

Read Full Post »