EPM Hyperion Workspace Repository Items SQL
[ad#ad-post]
I had the need to look up all Financial Reporting documents in workspace. I compiled the following list of SQL commands to run against the Oracle database repository. The last command is the winner.
http://hyperion-consulting.blogspot.com/2011/09/in-which-tables-reporting-objects-are.html
http://hyperion-consulting.blogspot.com/2011/09/in-which-tables-reporting-objects-are.html (posted by Thigulla Krishna)
Financial Reporting Report:
select * from v8_container where meta_type_uuid = 'ID200'
Financial Reporting Snapshot Report:
select * from v8_container where meta_type_uuid = 'ID201'
Financial Reporting Batch:
select * from v8_container where meta_type_uuid = 'ID204'
Financial Reporting Book:
select * from v8_container where meta_type_uuid = 'ID202'
http://www.network54.com/Forum/58296/thread/1354673858/pull+the+list+of+workspace+reports-folders+into+flat+file-excel (posted by Celvin Kattookaran)
select a.name, b.folder_full_path from v8_container a, v8_folder b where a.parent_folder_uuid = b.container_uuid;
http://www.network54.com/Forum/58296/thread/1354673858/pull+the+list+of+workspace+reports-folders+into+flat+file-excel (posted by VK)
Re: pull the list of workspace reports/folders into flat file/excel
No score for this post December 6 2012, 10:49 AM
This sql extracts all FR reports and Books under Workspace root directory with full path listing. If you need get additional objects just change META_TYPE_UUID
SELECT A.CONTAINER_UUID, A.CREATION_DATE , A.LAST_MODIFIED_DATE , A.OWNER_LOGIN , A.META_TYPE_UUID, A.NAME, A.DESCRIPTION, B.FR_PATH FROM EPMBIPLUS.V8_CONTAINER A, ( SELECT EPMBIPLUS.V8_CONTAINER.NAME , PARENT_FOLDER_UUID, CONTAINER_UUID, CONNECT_BY_ISLEAF "IsLeaf", SYS_CONNECT_BY_PATH(EPMBIPLUS.V8_CONTAINER.NAME,'/') AS FR_PATH FROM EPMBIPLUS.V8_CONTAINER WHERE \--CONNECT_BY_ISLEAF = 0 AND META_TYPE_UUID = 'ID03' \-\- and PARENT_FOLDER_UUID = NULL CONNECT BY PRIOR CONTAINER_UUID=PARENT_FOLDER_UUID START WITH CONTAINER_UUID='REPORTMART') B WHERE A.PARENT_FOLDER_UUID = B.CONTAINER_UUID AND A.META_TYPE_UUID IN ('ID202','ID200') ORDER BY A.META_TYPE_UUID ASC, FR_PATH ASC
[ad#ad-post]
I’m getting an error for V8_container, says invalid object name. please advise.
I’m getting an error for V8_container, says invalid object name. please advise.
The right SQL is this one:
— ———————
— Root Work Repository
— ———————
SELECT A.CONTAINER_UUID,
A.CREATION_DATE,
A.LAST_MODIFIED_DATE,
A.OWNER_LOGIN,
A.META_TYPE_UUID,
A.NAME,
A.DESCRIPTION,
B.FR_PATH
FROM hypra.V8_CONTAINER A,
( SELECT NAME,
PARENT_FOLDER_UUID,
CONTAINER_UUID,
CONNECT_BY_ISLEAF “IsLeaf”,
SYS_CONNECT_BY_PATH ( NAME, ‘/’ ) AS FR_PATH
FROM hypra.V8_CONTAINER
WHERE 1=1
AND CONNECT_BY_ISLEAF = 0
AND META_TYPE_UUID = ‘ID03’
CONNECT BY PRIOR CONTAINER_UUID = PARENT_FOLDER_UUID
START WITH CONTAINER_UUID = ‘REPORTMART’ ) B
WHERE A.PARENT_FOLDER_UUID = B.CONTAINER_UUID
AND A.META_TYPE_UUID IN (‘ID202’, ‘ID200’)
ORDER BY A.META_TYPE_UUID ASC, FR_PATH ASC
/