02-02-2023 05:15 PM - last edited on 05-24-2023 12:01 PM by JackLacava
Hi Experts,
i am trying to read the line items from celldetails. I think i get the sytax of BR functions it compiles fine but getting
"Object reference not set to an instance of an object." error so something is wrong with script? I am able to read Cellamount just fine using the Member Script. Requirement is to get the cell detail in the report along with amount.
Below is the line from the code:
LineItem = BRapi.Finance.Data.GetDataCellUsingMemberScript(si,"AUM","Cb#AUM:E#RevAccComments:C#USD:S#RevAccRecon:T#"+ FYQ +": A#MGMTFEES:F#CQ_MGMT_FEE_REV:O#Top:I#None:U1#None:U2#None:U3#None:U4#"+ Product.Member.Name +":U5#None:U6#None:U7#None:U8#None").DataCellEx.DataCellDetail.LineItems.LineItems.Item(0).Classification
any ideas on why this is not working?
Solved! Go to Solution.
03-20-2023 04:08 PM
I am able to do it like below in a BR.
Example code:
Dim MyDataSet As DataSet
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
MyDataSet= BRApi.Database.ExecuteMethodCommand(dbConnApp,XFCommandMethodTypeId.DataCellDetail, "{AUM}{E#RevAccComments}{RevAccRecon}{T#"+Year+quarter+"}{}{}{}{}{}{}{}{}{}{}{}{UD4='"+row("Product")+"' and UD6='"+FeeType+"'}", "MyDataCellDetail", Nothing)
Dim dt As DataTable = MyDataSet.Tables("MyDataCellDetail").Copy
02-03-2023 05:30 AM
It looks like you have not specified a text-type view in the member script. e.g. V#Annotation, (unless my eyes are playing tricks on me)
Text is stored in completely separate tables from numerical amounts, so although the text and numeric amounts appear 'packaged' together in a cell in a Cube View, that is not the way they are stored in the application.
There is a common myth that cell-text is bound to a numerical intersection. They are completely separate.
Also you should test if the DataCellEx and DataCellAnnotation are valid objects (not nothing) , otherwise you will almost certainly get errors like "object not set to a reference". Try breaking it out , like this:
Dim cell As dataCellInfoUsingMemberScript = brapi.Finance.Data.GetDataCellUsingMemberScript(si,"AUM","V#Annotation: .... rest of member filters...")
Dim cellText As String = String.Empty
If cell.DataCellEx IsNot Nothing then
cellText = cell.DataCellEx.DataCellAnnotation
end if
02-03-2023 09:49 AM
Thanks Chris! I understand what you mentioned. I do have working line for annotations. My issue is with DataCellDetail function as I am trying to read the line items and add to the report. I did try with and wihtout View and did not work. Will continue to play with it. My aim here is to se someone has used it before and has some pointers on how exactly it works.
Working annotation reading code:
PrepComments = BRapi.Finance.Data.GetDataCellUsingMemberScript(si,"AUM","Cb#AUM:E#RevAccComments:C#Local:S#RevAccRecon:T#"+ FYQ +":V#Annotation:A#MGMTFEES:F#CQ_MGMT_FEE_REV:O#Forms:I#None:U1#None:U2#None:U3#None:U4#"+ Product.Member.Name +":U5#None:U6#None:U7#None:U8#None").DataCellEx.DataCellAnnotation
02-03-2023 10:36 AM - edited 02-03-2023 10:38 AM
Could you show a screen capture of what sort of text / "line item" information you want to retrieve, as shown in the OneStream client when you right-click on a cube view.
Do you mean Data Attachments, which is loosely what HFM used to call "line item detail"?
You also don't mention whether this needs to be done in a Finance BR, or an Extender rule ( the difference being whether we can use the Finance API methods or not ).
Assuming it is not in a Finance BR, then then you should be able to retrieve the text from the cell attachments list, like this:
Dim strMemberScript As String = String.Format("Cb#AUM:E#RevAccComments:C#Local:S#RevAccRecon:T#{0}:V#Annotation:A#MGMTFEES:F#CQ_MGMT_FEE_REV:O#Forms:I#None:U1#None:U2#None:U3#None:U4#{1}:U5#None:U6#None:U7#None:U8#None", FYQ, strU4ProdName)
Dim sbCellTexts As New Text.StringBuilder()
Dim lstAttachements As DataAttachmentList = brapi.Finance.Data.GetDataAttachments(si, strMemberScript, False)
For Each attachment As DataAttachment In lstAttachements.Items
If Attachment.Text IsNot Nothing Then
sbCellTexts.AppendLine(Attachment.Title & " / " & Attachment.Text)
End If
Next
BRApi.ErrorLog.LogMessage(si,"Retrived all the text items from cell:", sbCellTexts.ToString() )
02-03-2023 11:17 AM
Here is the screenshot: user input cell details using this feature
Below screenshow the lineitems when you select "Celldetail" option. I am trying read this detail and add to the report.
hopefully this helps understand the requirement.
03-20-2023 02:52 AM
Hi Ashok,
I am trying to do the same thing and I think there is a method type that we can use to pull the cell details in a dashboard.
The method type is called DataCellDetail and it uses this syntax:
So if I am trying to pull these cell details:
Then I use the DataCellDetail syntax to pull them in a data adapter like this:
From there, you can attach this data adapter to compatible dashboard components to further process the retrieved cell details.
Hope this helps.
03-20-2023 04:08 PM
I am able to do it like below in a BR.
Example code:
Dim MyDataSet As DataSet
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
MyDataSet= BRApi.Database.ExecuteMethodCommand(dbConnApp,XFCommandMethodTypeId.DataCellDetail, "{AUM}{E#RevAccComments}{RevAccRecon}{T#"+Year+quarter+"}{}{}{}{}{}{}{}{}{}{}{}{UD4='"+row("Product")+"' and UD6='"+FeeType+"'}", "MyDataCellDetail", Nothing)
Dim dt As DataTable = MyDataSet.Tables("MyDataCellDetail").Copy