Extracting the Layout of all the Data Forms from the Relational Database

Today I came across a question from one of our clients that: "what members are used on each data form WITHOUT having to go through the report generated out of our Planning app". We worked with client on this and reached to a simple query.

All the form related information is stored in the following tables:

HSP_FORM
HSP_FORMOBJ_DEF
HSP_FORMOBJ_DEF_MBR 
HSP_FORM_ATTRIBUTES 
HSP_FORM_CALCS 
HSP_FORM_DV_CONDITION 
HSP_FORM_DV_PM_RULE 
HSP_FORM_DV_RULE 
HSP_FORM_DV_USER_IN_PM_RULE 
HSP_FORM_LAYOUT 
HSP_FORM_MENUS 
HSP_FORM_VARIABLES

If we want to retrieve just the members included, we can concentrate on:

HSP_OBJECT to get the Object_ID for form, Object_Type is 7 for forms. (Ex: Select * from HSP_OBJECT where OBJECT_TYPE = 7) 
HSP_FORMOBJ_DEF Find the OBJDEF_ID for a particular form 
HSP_FORMOBJ_DEF_MBR Use the above OBJDEF_ID to find the members: 
Here the Mbr_ID is the Id of the member and Query_Type is the Function like Idesc, Level0 etc and Sequce is you sequence,

And the final table we can use is HSP_FORM_LAYOUT: 
Layout_Type: 
0->Pov 
1-> Page, 
2->Row, 
3->Col, 


DIM_ID is the dimension ID and Ordinal is position.

Here is the Query:

SELECT HSP_OBJECT.OBJECT_NAME AS 'Form',  
HSP_OBJECT_2.OBJECT_NAME AS 'Dimension',  
HSP_OBJECT_1.OBJECT_NAME AS 'Member',  
HSP_FORMOBJ_DEF_MBR.QUERY_TYPE

FROM  
<DatabaseName>.dbo.HSP_FORM_LAYOUT HSP_FORM_LAYOUT,  
<DatabaseName>.dbo.HSP_FORMOBJ_DEF HSP_FORMOBJ_DEF,  
<DatabaseName>.dbo.HSP_FORMOBJ_DEF_MBR HSP_FORMOBJ_DEF_MBR,  
<DatabaseName>.dbo.HSP_MEMBER HSP_MEMBER,  
<DatabaseName>.dbo.HSP_OBJECT HSP_OBJECT,  
<DatabaseName>.dbo.HSP_OBJECT HSP_OBJECT_1,  
<DatabaseName>.dbo.HSP_OBJECT HSP_OBJECT_2

WHERE  
HSP_OBJECT.OBJECT_ID = HSP_FORMOBJ_DEF.FORM_ID AND  
HSP_FORMOBJ_DEF_MBR.OBJDEF_ID = HSP_FORMOBJ_DEF.OBJDEF_ID AND  
HSP_MEMBER.MEMBER_ID = HSP_FORMOBJ_DEF_MBR.MBR_ID AND  
HSP_OBJECT_1.OBJECT_ID = HSP_MEMBER.MEMBER_ID AND  
HSP_OBJECT_2.OBJECT_ID = HSP_MEMBER.DIM_ID AND  
HSP_FORM_LAYOUT.DIM_ID = HSP_MEMBER.DIM_ID AND  
HSP_FORM_LAYOUT.FORM_ID = HSP_FORMOBJ_DEF.FORM_ID AND  
((HSP_OBJECT.OBJECT_TYPE=7))

ORDER BY HSP_OBJECT.OBJECT_NAME 

Concentrate on Test1 data form and Actual Layout of it as follows:

Corresponding Query_type for few of the functions: 
9  for Idesc, 
3  for Ancestors, 
-9 for ILvl0Des, 
8  for Desc, 
4  for IAncestors

Its just a basic idea you can do lot on the basis of this.

Cheers..!!! 
Rahul S.