Forum Discussion

rhankey's avatar
rhankey
Contributor
7 months ago

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?

 

Finance
  • Henning's avatar
    Henning
    Valued Contributor II
    7 months ago

    Hi,

    I do not believe this is possible. This is - in my view - not a function to be used to set a storage type in a data buffer, but to filter on one when needed. You can use this e.g. in drill down rules, to drill down to data based on storage type such as this:

    Dim result As New DrillDownFormulaResult()  

If args.DrillDownArgs.RequestedDataCell.CellStatus.StorageType = DataCellStorageType.Calculation Then 
	'drill down to calculated data

Else If args.DrillDownArgs.RequestedDataCell.CellStatus.StorageType = DataCellStorageType.Journals Then 	
	'drill down to journal data	
	
Else If args.DrillDownArgs.RequestedDataCell.CellStatus.StorageType = DataCellStorageType.Input Then 
	'drill down to input data 

End If  

Return Result

    A result data buffer cell should always (automatically) be of storage type calculated. Only the source data buffer storage type should vary.

    Happy to learn something new if there is a way to save data from a data buffer in the cube as a journal storage type cell. 

  • FredLucas's avatar
    FredLucas
    Contributor III
    7 months ago

    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

     

    • rhankey's avatar
      rhankey
      Contributor
      7 months ago

      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.

      • FredLucas's avatar
        FredLucas
        Contributor III
        7 months ago

        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)
Next