69 comments:

Dan Gerena
said...

1) Can you show an example (a walkthough with screenshots) of how to use the Axis Scaling/Edit Scale Markers/Advanced Options dialog box when you want to create a trend bar graph that compares the last 12 months AVG results to yesterday's results, for instance. Specifically by using the "SQL Query" option.2) How about some examples of when you would use the "Advanced SQL Clauses" on the Advanced tab in Answers? Specifically, how would we use the "prefix" and "postfix" options?

Hi John,Thank you for creating a separate post for questions, I have a requirement where we are using Direct Database Request and want to pass a few parameters from Report A to Report B, but Direct SQL does not provide as many functions especially filters are not available. So, right now the only thing that I can imagine doing is manually setting presentation variables from JavaScript but cannot find the solution.

Right now I have added my code in RTNav i.e. when user clicks on a navigation link this gets called, I have access to all the data in the row that I want to pass, and I have somehow found about accessing prompt text fields but even setting them does not update the presentation variables because OBIEE seems to only set it when we click the GO button. So if you have any ideas, please share or point to some good resource of Direct SQL. Thanks.

Love the site got a OBIEE questionor request, first can we use nativedrivers instead of ODBC and the second is the real question Can weembed Crystal Reports in the Dashboard and how would you recommend doing this... I saw a section in the DOC about embeddingMicrosoft Office is it the same procedure...

I have OBI set up with a front end so users can access data. The users constitute of management teams & certain senior management people.

This is an out-of-box deployment. The web security assessment for the application revealed that improper query strings result in breaking the report/code & displaying the detailed error messages. Although the error messages do not show physical db details, the presentation layer metadata attributes does get on the screen.

I understand that the underlying info may be accessible using OBIEE Answers. Hence, I need your opinions & suggestions on the following concerns:

1. How could these messages be suppressed completely?2. What attack vectors do I need to be cover in case there is no solution for error suppression?

Q1: Yes you can use native drivers instead of ODBC, is even recomend.Q2: Why do want to do that anyway? You could put an HTML link to the report. The mocrosoft integration in the docs is a VBA based connector to the BI-server. See http://obiee101.blogspot.com/search/label/MS-OFFICE

"that improper query strings result in breaking". Do you allow direct SQL input? If all databases are availble and the reports have been well tested, they shouldn't lead to any of these error messages.

I'm not very familiar with HTML/CSS and wondering would it be a lot of work to do some icon customization in OBIEE.Let's say I have three possible values (1,2,3) in val column and would like to present it as an icon only depending on the value.Is it possible/How to do embed the appropriate gif image in the place?

I have a question about the 'new calculated columns' in a pivot table. Apparently I am not able to make more than one calculated column because we run on an aix machine. Every column gets the same exact name, metadata said it is an AIX issue. I was wondering if there is another way to create calculated columns I want to do something like the following: dimension column/facta/1b/2(calculated columns)c = a+b(3)d = a/b(1/2)(this is the main one I want. I want the percentage of one dimension column value based on another dimension's value.)

I am struggling to understand when OBIEE will excute SQL functions on the database versus on OBIEE server. For example, I have a logical table and when I create a column and use the AVG aggregation rule, OBIEE does the avg function on SQL Server- however if I create a column with StdDev aggregation rule, then OBIE pulls all the data from SQL server and does the aggregation on the OBIEE server.

Any pointers with how to "force" OBIEE to use SQL functions on database?

Hi, Is there any way to store the output of all the columns in the report in separate variables in OBIEE?We want to access the output value of a column in 2 reports and use them in a 3rd report to calculate the difference between the two. Any help on this regard will be really appreciated.Regards,Niket

Thanks for your postings its really helpful for developers.could you pls give any hint ..i am running into one issue with grand totals . how to show grand totals for direct data base request queries. i am getting all the columns from simple sql and at the end of the report pages i have to disply all columns totls .like adding a new Grand Total Row at the bottom.

A user will be logged off after some idle time and forced to log in again. This can be achieved with LogonExpireMinutes. But how do i display an alert message saying "You have been Logged Off. Kindly Log In again"?

Can anyone help me with a metric calculation in RPD where the expression is:

$Amount/(Grand Total $Amount)

Here $Amount is per each store; and (Grand Total $Amount) is the total based on whatever set of stores the user select on Answers. I am not sure how to grab the (Grand Total $Amount) which is dynamic based on users interest.

Have you tried to collapsed a section in a dashboard with a map with it? It seems that there's a bug or issue when we expand again the section. The bounding box was set to minimum resulting top incorrect map display... Hope you can help me. Thanks...

simple question, write back, I have read the documentation and see - "The user interface does only minimal validation of input" - i.e. numeric in a numeric field, cannot exceed field size etc... but no pick list / LOV functionality for foreign key values....

Which is my question, is there anyway to 'hang' a LOV on a write back column, to limit the user to good data - and require selection (no nulls)?

And yes, I know I could use database triggers, but error messages are not the mopst friendly of ways to deal with end users...

Thanks for your input - very impressive blog - how do you find time??!

Hi John, first off, thanks for all the answers I've gotten from your blog. I heard of it from another developer whose also a subscriber.

I was wondering if you or your readers have run into an accounting/financial phenomena called Trailing Twelve Months calculations (TTM) where measures (in our case: hours) are displayed as the sum of the period plus the previous 11 months. So, shown in a pivot, the period of Jan 2010, is actually a sum of the periods between Jan 2010 and Feb 2009.

With that said, I was wondering if you have done something like that in OBIEE or more specifically in the BMM?

I want a table report with dynamic column names based on the prompt selection. I successfully did it using venkat's blog by creating three separate requests and joind it using "Union" operator. But my problem is the column headers appear at the first row(as expected) but when the user navigates to next page the header is not coming since it is there in the first row. Is it possible to display the headers in all the pages by any means?

I am working on Trend Analysis of ETL Mappings,I am using AGO Function to get the Previous day Elapse Time of a mapping but while generating the report its behaving in a strange way.likeAGO("BI Performance"."Map Val Fact"."Elapse Time Minutes","BI Performance".ETLStartDim."Day",1) -- Day Ago Elapse timeAGO("BI Performance"."Map Val Fact"."Elapse Time Minutes","BI Performance".ETLStartDim."Day",2) -- Two Day Ago Elapse time

For example, in database there is date likeDate Elapse Time2nd June 10 min1st June 11 min30th may 10 min

My date in Report should be like

Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time2nd June 10 11 10

But the date coming over here is

Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time2nd June 103rd June 104th June 10

I am trying to leverage the Web Services Interface in OBIEE to load about 1200 users into our system. (At this point I could have typed them in manually but that is no fun).I found a few undocumented services that seem like they would work just fine. SecurityService.createAccount() Works just fineSecurityService.joinGroup() does not work and does not throw an exception just does nothing.

If I can get the joinGroup to work I can finish my script but it seems like literal no-one knows about this stuff.

I have the PDF "Oracle® Business Intelligence Web Services GuideVersion 10.1.3.2"

Is there anyway to link the Data Models directly to answers? I have created new models and exported them as .html files. I just need a way to link them into answers so i could click on a link or something and the .html file will pop up with the ERD. any ideas?

When I drill into Table 1, i will be redirected to a report which is X (fact) by Y (dimension)I have a column selector to dynamically change the dimension (Y ; Z)...i.e. X by Y table or X by Z table

When I drill into Table 2, i will be redirected to a report which is X (fact) by Z (dimension)I have a column selector to dynamically change the dimension (Y ; Z)...i.e. X by Z table or X by Y table

I'm thinking of creating only 1 report for both.

How can I set a default to a column selector, such that when I drill into Table 1, column selector of the report will default to Yand when I drill into Table 1, column selector of the same report will default to Z

John, Im running into an addition/sub total issue that is kind of stumping me, and I want to see if you have any ideas to steer me. I created a line item report for sales in Answers. On it I placed item, price, quantity, and line total. Price and quantity are aggregates and line total is a calculation (price * quantity). The issue occurs when we subtotal by store, line total does as it should and multiplies the subtotal of price times the subtotal of quantity and I end up with a multiplication. I placed a screenshot here to illustrate this (http://bit.ly/9wW5tq). If you look at it, store 1074T shows $35 ($17.5 * $2) versus $17.5 (the sum of $15 + $2.50). Any ideas on how this is done correctly in OBIEE? Thanks.

@richhi,your final view is pivot table???1.you hava make in the creteria tab a calculated column price * quantity.ok.(NOT sum(price) * sum(quantity))2.put in the calulated column in creteria tab (in the edit formula),aggregation rule->sum.It does not show the sub-total correct?I have tested in a test,it works fine.

I looked at the log and the SQL issued is using partition by clause causing the wrong result. When I disabled the DB feature ROWNUM_SUPPORTED and RANK_SUPPORTED, then the SQL generated is correct but partitioning is then being done by BI so the result is still wrong.

Hello John,One question.With the new release,11G,is it possible if we have -let's say- a table with 2 collumns,one is year(dimension) and other is measurement(qty),when we drill down to year,our table still shows the other years in our report?meaning....2009--152010--20when i drill to 2010,i will show my half-years...but still the year 2009?--->2009 --152010--2010_H1--102010--2010_H2--10

hello,tnks for your quick reply.I have a hiearachy in time...year-->semester...and so on.When i drill down everything is ok. My issue is here.----------------------------------2009--152010--20--->2009 --152010--2010_H1--102010--2010_H2--10----------------------------------when i try to dril to 2010,bi filter on that...and in the reportit returns 2010--2010_H1--102010--2010_H2--10and not this-->2009 --152010--2010_H1--102010--2010_H2--10,which is the desired.It makes a kind of filter,is it possible to avoid it?and to drill in a value,but still retain the others??

I am trying to replace the white background of the dashboard with a jazzy image. So that no whote space can be seen between two views. I have tried modifying the portalcontent.css file but no results. Could you please suggest what changes need to be done.

I have an OBIEE 11 analysis which uses a filter based on results of another analysis. The problem is that in the other analysis I'm using some variables in the Prefix field from the Advanced tab and when I try to run the "master" analysis I get the error:

I have to make a report having 2 measure values(for example Till date sales, days sales). The report is to be run for a date duration(From date and to date). The report should give us the till date sales values for the two dates(to date and from date), and show sum of the days sale for the date duration excluding the from date.Can this be done in OBIEE other than the use of direct database request?

My Site's

Subscribe To

Followers

Disclaimer

Opinions expressed are entirely my own and do not reflect the position of my employer, Oracle or any other corporation. I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog. And Yes I'm human, so I tend to make a misstake every now and then