Monthly Archives: August 2009

Avoid running report for All Values on Dashboard when using Multi-select Prompt


I found this question many times in the forums.

While using Multi-select as prompt, run the report on Dashboard the report will run for all values initially. But customer’s requirement is: Not to show all the values before running but has to show some custom message, like; Select Prompt values first and Run the Report

One way is: With setting Default values which are not existed in db for prompts. Here, i am explaining the procedure when customer don’t want to see any default values for prompts and still want to see Custom Message before selecting any values from prompts.

Let’s say, your dashboard is containing these 2 objects, Multi-select prompt set and a Report.

So, in my case, there are few multi-select prompts as shown:

Multi-Slct Prmts

And, Report Criteria may look like:

Report Criteria

If you observe Filters part of Criteria, all prompts related to respective columns are set to “is prompted”

Dashboard shows all values in the report when you navigate to particular page containing those 2 Catalog objects (Prompts, and Report)

Instead of showing all values by default, we need to show some custom message which says user to select the prompt values and run the report.

Here is the work around:

1.  Build a report with only those columns used in Prompt set. In this example those are, “Prod Subcategory”, “County Region”, & “Calendar Year”. And it’s filter set should look like:

Changed Filter Set

Observe that, i set a filter condition on all columns to non-existed database values instead of “is prompted”, and used logical Operator OR.

2.  Save this report as Intermediate Report

3.  Click on Results Tab of this Request.

4.  Edit your “No Results” View to show the message initially when you click on particular page of dashboard. Write the message there.

No Results View

5. Save this report

6.  Build a dashboard in such a way that each section contains each catalog object related to this page. Section 1 contains multi-select prompt, Section 2 contains Original Report to be shown, and finally Section 3 contains Intermediate Report.

Edit Dashboard

7.  Mention Guided Navigation properties for Section 2 and Section 3. i.e. Guided Navigation using Conditional Sections type.

8.  Click on Properties of Section 2 then Guided Navigation… and browse the Intermediate Report.

Section 2 GNP

9.  Do the Same thing for Section 3 also. (FYI, here Source Request and Target requests are same). But this time, select if request returns nothing radio button.

Section 3 GNP

10.  Save the Dashboard and see the Results. You will be watching the No Results View Message initially here, instead of showing all values of report. (Here you have not selected any prompt value yet..)

Results on Dashboard2

Here, Source Request i.e. Intermediate Report doesn’t return any rows. So, Section 3 content will be shown on dashboard.

11.  Now, select any prompt value and Run the report. This time, Intermediate report filters are override by the prompt values and it returns some rows. So, Section 2 content i.e. original report will be appeared on dashboard

Note: This will not work in the following Case like, you have set some prompt values and run the report. Clear all the prompt values here and run the report, now it shows all values.

So, it works when and as you click on the particular Dashboard Page.

This can be done easily for prompts with drop-down control. I blog it too soon.. 🙂

Your’s Kishore..!!

Pagination or “Page Contol” in OBIEE Pivot View


Hello Everybody,

There are several blog entries which explains about pagination in OBIEE Pivot

Please, include this entry also in that list. J

Suppose, i want to see 10 rows for every page in pivot.

Apart from the columns selected in the criteria for particular request, select another column and modify it’s functionality to CEILING((RCOUNT(1))/10.0). Now, change it’s column heading as: Select Page Number:  and drag this column into Pages block of Pivot

Pages of Pivot

Results are like:

Pagination Control

Note: In denominator of the formula, use ‘10.0’ instead of ’10’.  If you use 10, page Numbers are populated  from ‘0’ and in the 0th page only 9 records will be shown for this particular scenario.

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 Evaluate function in obiee. Actually, Initcap function in oracle capitalizes every character which comes after space along with 1st character.

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

SyntaxEVALUATE(‘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.

You can find my answer in forums for same question here

Hope it’s helpful to you…

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


Hello Everybody,

today one question ( in OTN, how to fix the following error.

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.

This option is available after you click on sigma button.

Thank You.

Forgot password for OBIEE rpd (or) Resetting Admin Tool password


During my R&D work today, i come across a situation how to reset the password for rpd where I forgot the old password. All existed solutions (like NQSChangepassword.exe command) have associated with mentioning old password to get the new one.

The work around I found:

  1. Open NQSConfig.INI file
  2. Go to Security part and find AUTHENTICATION_TYPE = BYPASS_NQS;
  3. Uncomment (remove #) this part.
  4. Stop the BI Server service
  5. Close Administration Tool, if opened. (Then only the changes are affecting to the Administration Tool)
  6. Open Administration Tool, then rpd which needs password reset, in offline mode
  7. Give any password, it accepts.
  8. Now go to Manage > Security.
  9. From here it’s the normal process how you change password.
  10. Save the changes to rpd
  11. Revert back the changes in NQSConfig.INI
  12. Start BI Server and open rpd with new password.

I am not aware of whether it is this bug or something else. And there is also a way to do this using Import from Repository. But this is deprecated option. Of course, i found that my approach is an easy way and not time consuming process 🙂

This is the work around I found for resetting password of rpd.

The same i posted in Forums too ..

here it is….

BTW, I am using version…

Your’s Kishore…