Calulcating percentage in OBIEE11g
hurtnOct 14 2011 — edited Oct 14 2011Hi gurus, We are having trouble calculating the percentage of two columns shown in our report in OBIEE11G. One of our dimension tables has a 1 or 0 flag and we are trying to find the percentage of records in the fact table with 1.
We can display the count of the each column individually but when trying to calculate a percentage by sum(rows in dimension flag)/count(rows in dimension flag) we either get a percentage of 0 or 100 because OBIEE is not dividing the two aggregates but instead dividing each value row by row. So it seems that OBIE11g does not divide the total of two counts but the divides the row value row by row.
When we define the dimension flag columns as aggregates in the business model and try to use these columns we get the error Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request. This was to try and force OBIEE to calculate the totals first (count of columns with flag 1/count of columns with flag 0) before performing the division.
There are no dimension hierarchies defined on either fact or dimension and there is a 1 to 1 relationship between the fact and dimension.
Can anyone assist?
Thanks.
Edited by: hurtn on 13-Oct-2011 23:26