Forum Discussion

Tom_R's avatar
Tom_R
New Contributor III
3 years ago

Create Metric Account from Specific SQL database pulls

Hello, 

 

I am trying to create a hierarchy for cubeview reporting that is based on a series of metrics that are extracted from the Journal Line Item application database table.  The metric is based on one half of the JE, but it is only applicable if a specific account is used as the other half of the JE. 

 

For example, if I have two journal entries, one using accounts A & B and one using A & C, I need the data from account A, but only for the first entry because the other half of the entry was to account B.  Because the second entry used account C I don't want that value captured in the metric.

 

I have been able to compile the SQL extract for this, but was wondering if there was a function in the business rules that would allow me to populate a dimension member, similar to a Finance member rule or api.data.calculate.getdatacell, that could be used in a loop, based on the row values extracted, each row being a combination of dimension base members.

Thanks,

 

Finance
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    I'm not sure about what you mean by "populating a dimension member". It seems like you already have a way to retrieve the list of members you're interested with; if you want to expose that list to cube views, you can place that logic in a Custom Member List in a Finance Business Rule file, which will generate the list for views or other components. A basic example of a custom list is available here or in Snippets; you then reference it from a cube view with this syntax:

     

    A#Root.CustomMemberList(BRName = YourBRName, MemberListName = YourListName, SomeParameter=SomeValue)

     

    Or this, which is equivalent:

     

    A#Root.[YourBRName, MemberListName, SomeParameter=SomeValue]

     

     There are examples of this also in Snippets.

    Is that what you were after...?

    • Tom_R's avatar
      Tom_R
      New Contributor III
      3 years ago

      Hello, 

      Thanks for the response, but this isn't quite what I am looking for.  I am looking to pull items related to specific journal entries, but only one side of the entry.  The values I am looking for are not the only values in the accounts retrieved from my query, which is why I am trying to isolate it through an SQL and paste it somewhere else as a metric for my users to access.

      I am sure I am not explaining this well.  

      Is it possible to use an SQL query within a dimension member formula instead of a api.data.calculate, have a query isolate a value and all the necessary other dimension members and the dimension member formula stores it?

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        3 years ago

        You can do anything you want in a Member Formula, the question is whether you should (for performance reasons). Look in the Business Rules / Data Helpers section of Snippets for ways to perform SQL calls; where you perform those, is up to you.

        The second part (actually storing the relevant value in an intersection) is trivial - either use a Calculate call where you pass the value in the string formula, or retrieve the DataBuffer and set the value on DataCells manually in the CellAmount property.