EPM Hyperion Workspace Repository Items SQL

Advertisements

[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]

Advertisements

3 Comments

Add a Comment

Your email address will not be published. Required fields are marked *