This is a post where you can put all your questions which aren't related to any subject on this blog (Yet). This way we can avoid the clutering of the blog.
But before you ask, if you are in a hurry ask your question on the OTN forum first. (http://forums.oracle.com/forums/forum.jspa?forumID=378) Altough I try, I'm not always able to read the postings from this blog.
Till Next Time.
68 comments:
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?
1) I been trying to look into oracle HRMS rpd desing can some one get me some help on this
@ Dan,
1) Have a look here:
http://obiee101.blogspot.com/2009/04/obiee-using-sql-based-scale-makers.html
2) The pre and post fix is used to set variabele before an after you execute a query. Have a look here:
http://obiee101.blogspot.com/2008/10/obiee-developersprompt.html
Regards
John
@ SJ,
Can't help you there, I haven't worked with that RPD yet.
Regards
John
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.
--
Regards
Adeel Javed
Hi
Love the site got a OBIEE question
or request, first can we use native
drivers instead of ODBC and the second is the real question Can we
embed Crystal Reports in the Dashboard and how would you recommend doing this... I saw a section in the DOC about embedding
Microsoft Office is it the same procedure...
Thanks John.
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?
Best Regards,
KG
@Anonymous,
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
regards
John
@KG,
"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.
regards
John
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?
Hi John,
I am trying to integrate OBIEE and SAP BW. I have imported the cubes to the physical layer. I am facing some issues in the business and presentation model.
Please guide me. How should i go ahead with the building of the presentation and business model.
@Msciwoj Naciagacz,
Have a look at conditional formating option of the column, that should do the trick.
Regards
John
@ Swatiii,
I have very little experiences with that. Have a look at the blog/wike of Nicolas Gerard:
http://gerardnico.com/wiki/dat/obiee/start, I know he has some good experience with that
regards
john
Dear John,
Can you please tell me if there are any tools which help test OBIEE components, Data Warehouse?
Regards,
Megha
hi,
is it possible to "print" narative to pdf.
Tnks
@eejimkos
That depends on the version of OBIEE you are using, there used to be a problem before 10.3.4.x ---
regards
John
That depends on the version of OBIEE you are using, there used to be a problem before 10.3.4.x ---
regards
John
////
tnks for the reply,John...
I use 10.3.4.1(i beleive),i searched at the oracle forum but nothing.(in the case we have a html in narative)..
tnks one more time
@eejimkos
Why don't you log an SR on metalink? (please let us konw the answer!)
Regards
Hi,
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/fact
a/1
b/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.)
Thanks
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
@Patrick,
I don't have any experience on AIX.
sorry
regards
@Phil DeGuzman
It firstly depends on the settings in the connection pool, but still then you can't always control it, OBIEE is kinda random sometimes.
regards John
@Niket,
You can use the result of one report as a filter for the next. Maybe you can work from that.
regards
John
Hi John,
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.
@lax
Why don't you add a total row using an UNION ALL query?
regards
John
@lax
Why don't you add a total row using an UNION ALL query?
regards
John
Hi
can anyone please tell me if its possible to backup a Repository (.RPD)automatically, if can please tell me.
Regards,
Luntu
@luntu,
have a look here:
http://obiee101.blogspot.com/2008/01/obiee-backing-up-repository.html
regards
John
Hi John,
I have a requirement where in,
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"?
Thanks and Regards,
Swati
Hi,
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.
Thanks,
Eric
@Eric,
Play aruound with the BY statement:
SELECT "D2 Market"."M04 Region" saw_0, "D2 Market"."M01 Market" saw_1, "F1 Revenue"."1-01 Revenue (Sum All)" saw_2, SUM("F1 Revenue"."1-01 Revenue (Sum All)" by "D2 Market"."M04 Region") saw_3 FROM "Sample Sales" ORDER BY saw_0, saw_1
Regards
Hi, I have a question on the OBIEE Mapviewer issue we've encounter.
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...
Hi,
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?
Thank you.
Richard
@Jun,
You could hardcode the size of the box in the init function of the map.
Regards
@Robert,
Try filling a presentation variable with a prompt and use the variable as input for you write back.
regards
@FakeBradAnderson / Richard,
You could do that with the ToDate function in the BMM.
Regards
John
Hi john,
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?
Thanks,
Karthick
Hi All I am new to OBIEE,
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.
like
AGO("BI Performance"."Map Val Fact"."Elapse Time Minutes","BI Performance".ETLStartDim."Day",1) -- Day Ago Elapse time
AGO("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 like
Date Elapse Time
2nd June 10 min
1st June 11 min
30th may 10 min
My date in Report should be like
Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time
2nd June 10 11 10
But the date coming over here is
Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time
2nd June 10
3rd June 10
4th June 10
So if any one could help me out of this, it would of great help.
With Regards
Chaitanya
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 fine
SecurityService.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 Guide
Version 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?
Hi,
I need your help, please read below.
Dashboard will have 2 reports (Table 1 & Table 2)
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 Y
and when I drill into Table 1, column selector of the same report will default to Z
Any idea?
Thanks
Janne
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.
Hello,Rich 2 things.
1,in the new column have you set any set of aggregation??
Put in in the instanceconfig.xml file and restart all the OBIEE services the tag,
true.
Does it help??
*ReportAggregateEnabled**
true
*/ReportAggregateEnabled**
where
* = <
and
** = >
@eejimkos: Yes, the line total has a agreggation. Its is aggregate of the sum(price) * sum(quantity). I had already tried report aggregate rule, but I still get the same result.
@rich
hi,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.
Hi John,
We have this issue on reports giving different set of results if the attribute used in formula is included or removed as part of request criteria.
What is causing this issue?
Regards,
Zel
@Zel,
Did you check your log file to see where the sql might differ?
regards
John:
My question is not about this post but in general around Union, Union All. I am running into sum calculation issues.
I have a union between two subject areas. The Result is:
Month
Count 1
Count 2
Until this point everything looks good. However when I perform Count 2 / Count 3 it does not work. The result I get is from 1st set. Union does not work. Any thoughts.
John Responded.....
@bitloo
please post off subject question on OTN
http://forums.oracle.com/forums/forum.jspa?forumID=378
or
http://obiee101.blogspot.com/2009/04/obiee-questions.html
As to your question, did you add the formula on the highest level of your combined request ("Result Columns")?
regards
John
@John:
Yes, tried that it works however it aggregates the result after it divides. I want it to aggregate first and then divide.
@bitloo
First do the aggregate:
sum (saw_1 by saw_0) / sum(saw_2 by saw_0)
regards
John
Hi John,
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.
Thanks
Zel
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--15
2010--20
when i drill to 2010,i will show my half-years...but still the year 2009?
--->
2009 --15
2010--2010_H1--10
2010--2010_H2--10
Tnks in advance.
Dimitris.
@Dimitris
This works great if you drag a hiearchy in a pivot table!
regards
John
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--15
2010--20
--->
2009 --15
2010--2010_H1--10
2010--2010_H2--10
----------------------------------
when i try to dril to 2010,bi filter on that...and in the report
it returns
2010--2010_H1--10
2010--2010_H2--10
and not this
-->
2009 --15
2010--2010_H1--10
2010--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??
tnks in adnance
John,ok..
I find it.My rpd was updated from 10.4 to 11G and it does not have the hieracly.I duplicate some of my business area and make new presentation catalog.And i saw.it works nice.
Tnks again.
HI,
need one help to resolve the follwoing problem.
1> How we can add both grand total and avg of grand total column in a pivot table ?
HI John
I want to eliminate the white space between two reports which are placed vertically in a dashboard section.
Is there any way to eliminate the white space by using html code or modifying the XML...
Please help!
Hello John:
Is there a way to create vertical scale marker in OBIEE 10G. I have a graph which shows 2 years worth of data by months. I wanted to add a verticle marker to separate the years.
Is this possible.
@Bitloo
Create a dummy column with only a value ont 1/1
Hi John,
I have a requirement to display the "Total By" values and "Grand Total" in bar charts of OBIEE.
I did not find any inbuilt way to do this. I feel some workarounds needs to be carried out.
I created the calculated item in pivot table view and now I am able to view the Totals in charts.
But the totals always appear in last position of the chart. How can I bring it to the 3rd or 4th position?
Could you please help?
Thanks,
Janani
Hi John,
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 am using OBIEE 11.1.1.3.
Regards,
Swati
Hi John,
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:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27004] Unresolved table: "SET". (HY000)
So OBIEE doesn't strip the variables from the other analysis and the keyword SET is interpreted as the table name: "...IN (SELECT saw_2 FROM (SET VARIABLE...".
Do you know how to fix this?
Thanks!
PS:
I've also posted this issue here
@mihai,
did you add a ";" after the variable?
regards
Hello John,
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?
Awaiting reply. Thanks in advance.
Post a Comment