Category: Uncategorized

Fixing “A REPORT totalling function must be in a leaf query block. (HY000)” error in obiee

today one question (http://forums.oracle.com/forums/thread.jspa?threadID=941347) in OTN, how to fix the following error.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. NQODBC SQL_STATE: HY000 nQSError: 10058 A general error has occurred. nQSError: 42038 A REPORT totalling function must be in a leaf query block. (HY000)

Cause: A Report totalling function, e.g., REPORT_SUM or REPORT_MAX, is included in a non-leaf query block. This is not allowed.

Remedy is: De-select the option Report-Based Total (when applicable) for all grand totals or subtotals you have applied.

Capitalizing the Initial character of a Column’s Data in OBIEE

Let’s say, your database containing the data in small letters for one of the columns.

Now, requirement is like, need to capitalize the first character of every value in that column.

One approach is: If you are using Oracle database, you can use Initcap function with Evaluatefunction in obiee. Actually, Initcap function in oracle capitalizes every character which comes after space along with 1^st character.

You need to write functionality as shown below, to achieve your requirement.

Syntax: EVALUATE(‘INITCAP(%1)‘, Table.ColumnName)

Ex : EVALUATE(‘INITCAP(%1)‘, Products.”Prod Category”)

If it’s not oracle database, you find equivalent and appropriate function for Initcap for your own database and write the same in Evaluate function

Another Approach is: Write the following functionality in one column of answers

upper(SUBSTRING(Products.”Prod Subcategory” FROM 1 FOR 1)) ||SUBSTRING(Products.”Prod Subcategory” FROM 2)

But this shows only first letter as capital, and remaining all in smaller case.

You can write these functionalities in rpd also… and make use those columns directly in Answers.

Show/Pass Request Variable (Session) in section heading in obiee11g

Hope all of you know how to define/use Request variable from Dashboard prompt…

Recently i found one question on OTN.

Requirement is: Show/Pass selected value from drop-down in Dashboard Section heading..

This is quiet easy if prompt is defined with presentation variable, but problem here is: prompt is defined with Request Variable.

Problem with Request Variable is:

Usually, Request variable is session variable defined in rpd and will be override with selected prompt value in front end. Real problem here is: regardless of selected value from the dashboard prompt, it always shows the default value of session variable initialized while creating the variable…

And, this is the way we pass session variable in Section Heading..

@{biServer.variables[‘NQ_SESSION.var_PVar’]}

Work Around is:

I assume Request variable is: var_PVar.

Here are the steps involved to achieve it.

1. Create another prompt with same functionality of main prompt

2. But, this time assign a presentation variable to the prompt, say: var_P.

Note: This prompt should be added to separate Section in Edit Dashboard..

3. Now, call presentation variable directly into Section Heading as shown..

4. Now, hide newly created prompt. Here is the way to hide the prompt..

Year Ago Calculation Without Time Series Functions

This article explains you how to calculate year ago measures with out using time-series function.

Usually, for requirement like: measure, year ago measure want to show.. we’ll go for 2 measures. One is: Measure and YearAgoMeasure calculated through time-series.

This blog entry also avoid to create other column for calculating year ago measure..

Here is the procedure:

I’m assuming you have Year dashboard Prompt, assigned with presentation variable: var_Year

1. Create a filter on year column in this way:

2. And filter should be like this:

3. go to Pivot then arrange columns in this fashion and view Results:

You’ll be noticed that results are giving current/(selected year from prompt) and previous year values ..

Note: Apply descending order on Year column..

You may not like the labels showing year values. Instead of that, you may need to see the labels like: Current and Prior..

This can be done by writing following condition in Year Fx :

CASE WHEN Time.”Fiscal Year” = @{var_Year} then ‘Current’ when Time.”Fiscal Year” = @{var_Year}-1 then ‘Prior’ END

Hope it’ll helpful to you..

Limitation with this approach is: we’re using Advanced filter, which will not appear dynamically changing years in filters view (if you add filter view to compound layout)