2 Comments

  1. Hi Vijay –
    The join for currencies is against the CURRENCIES table with a bit of math:

    SELECT e.label, e.defaultvalueid, c.label
    FROM hfm_entity_item e LEFT JOIN hfm_currencies c
    ON e.defaultvalueid = 17 + (c.itemid * 3)

    The 17 roughly represents default members of the Value dimension (in the VALUE_ITEM table). After that, there are three virtual members for each currency (USD, USD Adjs, USD Total), so the currency ID has to be incremented by three. The sole exception to this is the [None] member which has a currency of [None]. If you don’t care about the [None] member, you don’t need an outer join.

    Great post. Hope this helps.

    Regards,
    Keith

Leave a Reply

Your email address will not be published. Required fields are marked *