10-04-2022 01:52 PM - last edited on 10-05-2022 05:32 AM by JackLacava
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.
10-06-2022 06:49 AM - edited 10-06-2022 06:50 AM
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...?
10-07-2022 10:32 AM
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?
10-10-2022 01:00 PM
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.