It's all about Enterprise Performance Management

Source: http://www.orahyplabs.com/2011/11/show-usage-for-member-in-calc-manager.html

I once received a query where customer asked whether there is an option to find out Member usage in Calc Manager.

So let’s say that member FY10 is used in ‘n’ Calculation Manager Rules, they would like to list out all the rules that has FY10 in it.

I couldn’t find out an way to do that from front end :), so ended up querying the repository

You can get the required information by running one of TWO SQL queries given below.

As you know Calculation Manager rules gets stored under Calculation Manager repository and also under respective Planning repository. (thats the reason why we have two SQL statements 🙂 )

Please use the below given SQL if running from Planning tables

SELECT location_sub_type “Database”, (select ‘SMP_PLN’ from DUAL) “Application”, (select object_name from hsp_object where object_id = hsp_calc_mgr_rules.id) “Rule name”
FROM hsp_calc_mgr_rules WHERE dbms_lob.INSTR(BODY,’FY10′) > 0;

In this table Rule is stored as CLOB in column called BODY.

Since the query is generated from the Planning application repository, I couldn’t find a table which stores Application name. So I decided to show the application name using DUAL. (SMP_PLN should be replaced with your application name)

Please use the below given SQL if running from Calc manager tables

SELECT LOCATIONSUBTYPE “Database”, LOCATION “Application”, NAME “Rule Name” FROM calcmgrrules WHERE dbms_lob.INSTR(BODY,utl_raw.cast_to_raw(‘FY10’),1,1) > 0;

In this table Rule is stored as BLOB in column called BODY.

HTH