Writing a StorageType=Journal cell to cube
When writing DataBufferCells to a cube, does anyone know how to create a DataBufferCell having a StorageType=DataCellStorageType.Journals?
Forum Discussion
Hi rhankey,
I've never tried doing that, would be great to understand the use case to see if there could be other ways to achieve what you are looking for or helping you identifying any potential pitfalls or that approach.
Having said that, from a technical perspective, you could maybe try updating the storage type of your DataBufferCells using something like this (I've not tested it myself):
For Each sourceCell As DataBufferCell In retrieveDB.DataBufferCells.Values
Dim journalCellStatus As New DataCellStatus(sourceCell.CellStatus.ExistenceType,DataCellStorageType.Journals)
sourceCell.CellStatus = journalCellStatus
Next
Thanks for the idea. The initial test I tried did not appear to work. I will need to try a more specific test, as your suggestion is similar to setting ResultCell.CellStatus=DataCellStatus.CreateCellStatus(True,True), which can zap any cell. Clearly OS has a way of setting the CellStatus when loading Import, Forms or Journals to the cube. There are a couple more variants to CreateCelLStatus that take different parameters that I've not figured out. What is unknown is if OS exposes the method they use in the API to which we have access.
The use case I am working towards is to support permanent OS journal entries which behave the same as a regular General Ledger where Periodic activity adjustments are posted and automatically rolled and accumulated to the next period in the fiscal year, at which point the year-end roll from EndBal to BegBal occurs. I don't want to be rolling to a different member, which is certainly a possible option, as that requires an extra UD dim to keep the accumulated data from prior periods in the same intersection as the current periodic activity. It's easy to accomplish this without an extra member if I overwrite the OS written StorageType=Journals cell. But changing the StorageType to Calculation then loses the out of box drill back to source document ability, which I do not want to lose. I would like to retain the StorageType=Journals status for cells having activity in that period, even though I may have modified the cube value to accumulate the YTD activity.
Hi rhankey,
I'm not sure if I fully understood your use case but if you post journals in a V#Periodic you'll naturally get the YTD / accumulated amount when reporting on V#YTD. The EndBal to BegBal calculation should also work as expected as long as they take the V#YTD amount.
If instead what you are looking for is a way to set recurring journals you might find this post interesting:
https://community.onestreamsoftware.com/t5/Rules/Copy-a-journal-with-a-business-rule/m-p/2928#M68
You could also update the code on the post to loop through the line items and update the credit and debit amounts for example:
Dim oldJournalObject As JournalEx = BRApi.Journals.Metadata.GetJournalOrTemplate(si, "oldJournalName") Dim journalObjectLineItems As list(Of JournalLineItem) = oldJournalObject.LineItems.Select(Function(x) New JournalLineItem(x.LineItem)).tolist For Each jLineItem As JournalLineItem In journalObjectLineItems jLineItem.CreditAmount = New DecimalAndNoData(1000, False) NextNot all journals are to be permanent. Based on properties of the individual journals, I have to determine whether the journal rolls for the quarter or forever (two shades of permanent), is recurring, or otherwise. So, it's not a simple matter of letting OS out of box functionality YTD the Periodic activity. I have to consider the individual YTD posted journals when figuring out what the correct cube value needs to be.
I just had some time to test the code I posted that attempts to change the DataCellStatus to Journals and that actually updates the cell status as intended, however the api.Data.SetDataBuffer function is overwriting that to Calculation / DurableCalculation which makes sense.
This means that you won't be able to submit data via the calculation api with a Journals CellStatus.
However, based on the use case you described, one option that you might want to consider would be to use the JournalEventHandler Extender rule to check the "properties of the journal" and determine what to do with it, you could have it copying and posting the journal to future periods (using the logic in the post I referenced earlier) if that makes sense?
Just be careful not to cause a "infinite loop" as the new journal you create will trigger the eventhandler too so do make sure you have a way to identify if it's an original journal or an "auxiliary" one.
Hope this makes sense and you are able to get to the solution you need.