Writing a StorageType=Journal cell to cube

[rhankey]

When writing DataBufferCells to a cube, does anyone know how to create a DataBufferCell having a StorageType=DataCellStorageType.Journals?

[Henning]

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]

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]

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]

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

[rhankey]

Not 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.

[FredLucas]

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.

[Henning]

Hi, I agree with @FredLucas. It seems likely that copying the actual journals is what would be typically done in such a case.

Other than using rules, from 8.0 onwards, journals can be copied using native functionality:

Journal Copy tool - OneStream Community (onestreamsoftware.com)

The reason why is because DataCellStorageType exists to identify with certainty the source of the data in the system, also in an audit sense. Journal storage types should always come from real journals, not from data calculated that is based on journal data. This is then a "Calculation" storage type to ensure data integrity. If it needs to be a journal storage type, it should be coming from a journal. This can be copied by using rules or the copy functionality. Journals can also be created in a business rule and posted directly from there, which would be another (but not ideal) way.

Other customers typically solve this via extra members in a UD, e.g. "Ux#NonRecurring" and "Ux#Recurring" if a separation is necessary. Then, only the journals on Ux#Recurring get copied to the next period. Some customers are also pragmatic in a sense that they just copy over the data on the respective member. Though, I agree that copying the journals provide more details and makes it easier for the end user to explain the source data in each period.