Line Items in DataCellDetail - using BR

Ashok
New Contributor III

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? 

1 ACCEPTED SOLUTION

Ashok
New Contributor III

I am able to do it like below in a BR.

  1. Execute the Method query to get the data set
  2. Copy data set into a datable 
  3. use the data table to read the rows 

 

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

View solution in original post

6 REPLIES 6

ChrisLoran
Valued Contributor

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

 

Ashok
New Contributor III

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

ChrisLoran
Valued Contributor

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

 

 

Ashok
New Contributor III

Here is the screenshot: user input cell details using this feature

Ashok_0-1675440916234.png

Below screenshow the lineitems when you select "Celldetail" option. I am trying read this detail and add to the report.

Ashok_1-1675440972627.png

 hopefully this helps understand the requirement. 

amvillaruel
New Contributor II

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:

amvillaruel_0-1679295077481.png

So if I am trying to pull these cell details:

amvillaruel_1-1679295092714.png

 

 

Then I use the DataCellDetail syntax to pull them in a data adapter like this:

amvillaruel_2-1679295116905.png

 

From there, you can attach this data adapter to compatible dashboard components to further process the retrieved cell details.

Hope this helps.

 

Ashok
New Contributor III

I am able to do it like below in a BR.

  1. Execute the Method query to get the data set
  2. Copy data set into a datable 
  3. use the data table to read the rows 

 

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