Extracting the Layout of all the Data Forms from the Relational Database
- by RahulS
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.
http://www.facebook.com/pages/HyperionPlanning/117320818374228
