Create Metric Account from Specific SQL database pulls

[Tom_R]

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,

[JackLacava]

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]

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]

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.