Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Subtotals in a report with hierarchy columns

1033148Aug 15 2013

Hi Everyone,

I'm using Essbase as a source with OBIEE 11.1.1.6.2 BP1 and Essbase has two dimensions as below:

Account dimension (four levels):  Total Accounts -> Account Category -> Account Subcategory -> Account Number (Eg: Total Accounts -> Revenue -> Operating Revenue -> 1001, 1002)

Project dimension (three levels): Total Project -> Project Class -> Project Number (Eg: Total Project -> Capital -> 100010, 100011)

I have a report requirement as below:

     Account Dimension         Project Number     Amount

     Revenue                         Total Project          300

       Operating Revenue        Total Project          300     

          1001                         100010                  100

                                          100011                  100

          1001 Subtotal                                         200

          1002                         100010                  75

                                          100011                  25

          1002 Subtotal                                        100

   

I have achieved the required format using hierarchy column for accounts dimension, however, I'm not sure how to achieve subtotals for account number.

Any ideas how to achieve subtotals when using hierarchy columns? Please advise if the report format can be changed to achieve this subtotal functionality.

Many Thanks,
Seetharam

Comments

447579
There are many other ways to do this.
Here is just one:
Travel across the string variable using instr, evaluate every position comparing it to a number (you can use the ascii code if you prefer) and if it is a number you can assign the value of that position to a variable which is holding the result set.

Regards,
Max.
571482
with test as (select column_value col from table
(sys.ODCIVarchar2List('2222jhone','123344Sam','11Tim')))
SELECT REGEXP_REPLACE(col, '[[:alpha:]]','')
FROM test

The important part is REGEXP_REPLACE and [[:alpha:]]
572110
try this

select regexp_replace('12345TTTT', '[A-Za-z]') from dual
572110
also possible


select regexp_replace('12345TTTT', '[^[:digit:]]') from dual;
447579
Your regular expression:
SQL> SELECT REGEXP_REPLACE('max1a23b56','[[:alpha:]]') from dual
2 /

REGEX
-----
12356

Regards,
Max.
DelfinoNunez

one option

with t as
  2       (select '2222jhone' str
  3          from dual
  4        union all
  5        select '123344Sam' str
  6          from dual
  7        union all
  8        select '11Tim' str
  9          from dual)
 10  select regexp_substr(str,'[[:digit:]]*')
 11    from t;

REGEXP_SU
---------
2222
123344
11
599314
Hi,

Thanks to all for prompt responce...All are working fine.
613397
Hi,

I am at oralce 9i,
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

When I executed the solution, it gave me error


SQL> select regexp_replace('12345TTTT', '[A-Za-zfrom dual
2 /
select regexp_replace('12345TTTT', '[A-Za-zfrom dual
*
ERROR at line 1:
ORA-00904: "REGEXP_REPLACE": invalid identifier

Any solution for this?
608922 
SQL> ed
Wrote file afiedt.buf

  1* SELECT REPLACE(TRANSLATE(UPPER('12345abcdABC12345'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' ','') FROM DUAL
SQL> /

REPLACE(TR
----------
1234512345

SQL>
Message was edited by:
user605919
BluShadow
It surprises me that, yet again, this question has been asked.

Just putting the exact same subject of this thread into the search engine gives loads of results with answers for how to do this already...

http://forums.oracle.com/forums/search.jspa?threadID=&q=extract+number+value+from+string&objID=f75&dateRange=all&userID=&numResults=30

I guess people are just too lazy to search.
crystldawn
The very first post of which leads right back here....
BluShadow
The very first post of which leads right back here....
Numpty. :)

It's search results, so of course this thread is in there. Doh!
860907
You NEVER want to use REGEXP_REPLACE when looking to query fields with numbers only because you are actually changing the data by removing or replacing with something that was not originally in the dataset. Use REGEXP_LIKE - SEE BELOW.


SELECT <FieldNames>
WHERE REGEXP_LIKE(<FieldName>, '^[[:digit:]]+$')

--This will only return fields that contain NUMBERS ONLY.
Warren Tolentino
using a TRANSLATE function. 
SQL> select trim(translate(str,translate(str,'1234567890',' '),' ')) number_char
  2    from (select '2222jhone' str from dual union all
  3          select '123344Sam' str from dual union all
  4          select '11Tim'     str from dual);

NUMBER_CHAR
-----------
2222
123344
11

SQL>
1 person found this helpful
BluShadow
Please don't drag up 4 year old threads.

locking this thread
1 person found this helpful
1 - 15
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 12 2013
Added on Aug 15 2013
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee
0 comments
176 views