Show Validation Rules usage on Webforms


One more post in the Show Usage series 😉
I was trying to see whether I can get a list of all Data Validation rules in a Planning application so that I can get rid of some of them real quick.
Think about all these series as my rants against PBCS (and in some cases ON-PREM)
I can run the below given query to find out what are my existing DV rules and which forms they are associated with.

SELECT     
	B.OBJECT_NAME "Form",     
	A.NAME "DV Rule",     
	A.ROW_LOCATION "Row Location",
	CASE 
	WHEN A.COL_LOCATION >26 THEN
		CHR(ROUND(((A.COL_LOCATION - 1) / 26),0) + 64)||CHR(MOD ((A.COL_LOCATION - 1) ,26) + 65)
	ELSE 
		CHR(64+A.COL_LOCATION)
	END     AS "Column Location",
	CASE 
	WHEN A.ENABLED=1 THEN 
		'YES'
	ELSE 
		'NO'
	END     AS "Enabled",
	CASE  
	WHEN A.ROW_LOCATION=-1 AND A.COL_LOCATION=-1 THEN
		'Grid Rule'
	WHEN A.ROW_LOCATION=-1 AND A.COL_LOCATION<>-1 THEN 
		'Column Rule'
	WHEN A.ROW_LOCATION<>-1 AND A.COL_LOCATION=-1 THEN
		'Row Rule'
	ELSE 
		'Cell Rule'
	END     AS "Rule Type"
FROM   HSP_FORM_DV_RULE A,
       HSP_OBJECT B 
WHERE A.FORM_ID=B.OBJECT_ID ORDER BY 1;
And here is the result.
I don’t know how the Row location is populated, it is not the row that is shown on the Webform, it is not the count of POV Dims+Col Dims + Row number, not the expanded number of Rows. (I did get lost while trying to figure out the ROW_LOCATION logic)

I’ve added a fail safe in the SQL when the column number goes beyond 26 (A~Z).

If you’ve a column number goes beyond 676, the SQL won’t show correct results. (I hope you are not working at a place where you’ve a Webform which goes beyond ZZ!!!!)
If you want to see what each rule looks like, that is stored under HSP_FORM_DV_CONDITION
Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase.

Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I’m an Independent Consultant with “Intekgrate Corporation” based out of Aurora office.

I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader.

I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply