查看Account 维度成员

SELECT    O.OBJECT_ID,
    O.OBJECT_NAME  "Member name",
    k.object_name  "ALIAS",
    PO.OBJECT_NAME "Parent",
    --    There are up to five Plan Types and operators
    --    can vary across Plan Types, so you will need to repeat
    --    this block if > 1 Plan Type.
    CASE M.CONSOL_OP1
        WHEN 0 THEN '+'
        WHEN 1 THEN '-'
        WHEN 2 THEN '*'
        WHEN 3 THEN '\'
        WHEN 4 THEN '%'
        WHEN 5 THEN '~'
        WHEN 6 THEN '^'
    END "Operator",
    CASE M.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
    END "Storage",
    CASE M.TWOPASS_CALC
        WHEN 0 THEN 'NO'
        WHEN 1 THEN 'YES'
    END "Twopass",
  --    The parent object type is either 2, which is the Account

  --    dimension or 32, which is a Account member   

   --    Comment this out it doesn't really add anything to the

  --    query.   

   --    CASE PO.OBJECT_TYPE,
    CASE AA.USE_445
        WHEN 0 THEN 'None'
        WHEN 1 THEN '445'
        WHEN 2 THEN '454'
        WHEN 3 THEN '544'
        ELSE ''
    END "Spread Type",
    CASE AA.TIME_BALANCE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'First'
        WHEN 2 THEN 'Last'
        WHEN 3 THEN 'Average'
        ELSE ''
    END "Time Balance",
    CASE AA.SKIP_VALUE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Skip missing'
        WHEN 2 THEN 'Skip zeroes'
        WHEN 3 THEN 'skip missing and zeroes'
        ELSE ''
    END "Skip Value",
    CASE AA.ACCOUNT_TYPE
        WHEN 1 THEN 'Expense'
        WHEN 2 THEN 'Revenue'
        WHEN 3 THEN 'Asset'
        WHEN 4 THEN 'Liability'
        WHEN 5 THEN 'Equity'
        WHEN 6 THEN 'Statistical'
        WHEN 7 THEN 'Saved Assumption'
        ELSE ''
    END "Account Type",
    CASE AA.VARIANCE_REP
        WHEN 1 THEN 'Expense'
        WHEN 0 THEN 'Non Expense'
        ELSE ''
    END "Variance Reporting",
    CASE AA.CURRENCY_RATE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Average'
        WHEN 2 THEN 'Ending'
        WHEN 3 THEN 'Historical'
        ELSE ''
    END "Currency Rate",
    --    Bitmask indicating the cubes that use the account
    --    1=revenue
    --    2=net income
    --    4=balance sheet
    CASE AA.USED_IN
        WHEN 1 THEN 'Revenue'
        WHEN 2 THEN 'Net Income'
        WHEN 3 THEN 'Balance Sheet'
    END "Where used",
    CASE M.DATA_TYPE
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non Currency'
        WHEN 3 THEN 'Percentage'
        ELSE ''
    END "Data Type",
    CASE AA.SRC_PLAN_TYPE
        WHEN 0 THEN 'NA'
        WHEN 1 THEN 'Revenue'
        WHEN 2 THEN 'Net Income'
        WHEN 4 THEN 'Balance Sheet'
    End "Plan Type"
 FROM HSP_OBJECT O
INNER JOIN HSP_OBJECT PO ON
    PO.OBJECT_ID = O.PARENT_ID
INNER JOIN HSP_MEMBER M ON
    M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_ACCOUNT AA ON
    AA.ACCOUNT_ID = O.OBJECT_ID
INNER JOIN (   
SELECT OA.OBJECT_NAME,A.MEMBER_ID  FROM HSP_ALIAS A
    INNER JOIN HSP_OBJECT OA ON OA.OBJECT_ID = A.ALIAS_ID where A.aliastbl_id=14) k on
    K.MEMBER_ID = O.OBJECT_ID
 
WHERE O.OBJECT_TYPE = 32

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7477027/viewspace-703301/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7477027/viewspace-703301/