Friday, September 23, 2011

OBIEE11g Blocking Analyses Based on Criteria

Yes if done this for 10g (see:http://obiee101.blogspot.com/2008/06/obiee-blocking-request-based-on.html). Just wanted to check if this still works in 11g.

Start by locating your answerstemplates.xml. If you don’t have a CustomMessages diretory create one and place it there (or take a change on loosing your stuff during a update Knipogende emoticon . )

There are several blogposts with nice “documentation” copies. For this article I checked every function personally. (Including the “one” I couldn’t get to work…..)

Find the webmessage for kuiCriteriaBlockingScript:

image

Since we don’t want to edit our template al the time forcing a restart we put in an external reference to a javascript called: mycriteriablocking.js

<WebMessage name="kuiCriteriaBlockingScript" translate="no">
    <HTML>
        <script type="text/javascript" src="fmap:mycriteriablocking.js" />
    </HTML>
</WebMessage>

image

in the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\analyticsRes directory we place a javascript (.js) file called mycriteriablocking.js.

just a warning. this is very very sensitive on typo’s Knipogende emoticon

Start with a simple formula:

// This is a blocking function. It ensures that users select what
// the designer wants them to.
// http://obiee.blogspot.com
function validateAnalysisCriteria(analysisXml)
{
   return true;
}

image

- Force the usage of a specific “Subject Area”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);
// Only use Sample Sales Lite or Sample Targets Lite
if (    tValidator.getSubjectArea() != "Sample Sales Lite" &&
        tValidator.getSubjectArea() != "Sample Targets Lite" )
      return "You can only use Sample Sales Lite or Sample Targets Lite!";
//We come here if everything checks out
   return true;
}

- Force the usage of a specific ”Table”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Table must exists
if (    !tValidator.tableExists("Time")     )
        return "Each report must have a Time Dimension";
//We come here if everything checks out
   return true;
}

- Force the usage of a specific “Column”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Column must exists
if (     !tValidator.columnExists("Time", "Per Name Year")   )    
        return "Each report must have a Year column from the time dimension";
//We come here if everything checks out
   return true;
}

- If one Column is used the other is compulsory

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// If we use one column then also the other should be there
if (    !tValidator.dependentColumnExists("Time", "Per Name Qtr" , "Time", "Per Name Year"))
        return "A quarter with out a Year is pretty useless!";
//We come here if everything checks out
   return true;
}

- If one Column is used a specific column is compulsory

!!! According to the documentation this should work?? I wasn’t able to create a working example (yet)….. Bedroefde emoticon… !! (If you have one lying around for 11g please let me know……)

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// If we use a random column from a table a specific column should be there
if (    !tValidator.dependentColumnExists("Base Facts", "", "Time", "Per Name Year"))
        return "Facts should always be presented together with a year";
//We come here if everything checks out
   return true;
}

- A column should be at least in the filter:

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

if (    !tValidator.filterExists("Time", "Per Name Year"))
        return "A Year filter must be applied";
//We come here if everything checks out
   return true;
}

- If a column is used the other should be in the filter:

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

if (    !tValidator.dependentFilterExists("Base Facts","Revenue" , "Time", "Per Name Year"))
        return "Revenue needs a year filter";
//We come here if everything checks out
   return true;
}

- A minimum numbers of selection should be in the filter

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Count the number of elements in the filter var n = (tValidator.filterCount( "Time", "Per Name Year")); if ((n <2))
    return "Please select at least 2 years in your filter";
//We come here if everything checks out
   return true;
}

Till Next Time

5 comments:

Muhammad Waseem Shahzad said...

We have a report that has around twenty "OR" criterias and ten "AND" criterias. All the filters are logically correct but report is returning invalid data. However if we reduce some filters then report works fine.

Any ideas of increasing filters limit for a report or reason of the issue.

John Minkjan said...

@Muhammad Waseem Shahzad
Can't you port part of the filters to the RPD? or use saved filters?

regards

John

Nachiket said...

Hi John,

I used the below function.
function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);
if ( !tValidator.filterExists("Time", "Per Name Year"))
return "A Year filter must be applied";
//We come here if everything checks out
return true;
}

Observation:

1> When I click on Product column and press Results tab , I get no warning message.
2> When I included a column year and put a filter on it still I was not able to see any data after pressing Results tab. There is no response from OBIEE after pressing Results tab.

Thanks
NM

John Minkjan said...

@Nachiket,

Are you sure you put the filter in the right place?

regards

john

Anonymous said...

Can we do a row Count using the validator function?