Report on Data Cell Detail using UD8 dynamic calc (DataCellDetail)

[MarcR]

Hi Guys, 

I'm at a project where we use Data Cell Detail, we have created classifications and added 2 lines of test data.

I can find the data via a data adapter Method query:

But now i want to add it via a BRApi, i've found a post here but what ever I try i cannot get it to work.

I have a cube view with 2 columns and the only difference is the UD8 member, so i first want to get the current data cell POV and set UD8 to None:

Dim objMbrScrBldr = New MemberScriptBuilder()
'Apply current POV to memberscript and override UD8
api.Data.ApplyDataCellPkToMemberScriptBuilder(objMbrScrBldr, api.pov.GetDataCellPk(), True, True, True, True)
objMbrScrBldr.SetUD8("None")

Then I call the datacell containing the amount: 

'Return the sum of the DataCells that contain the selected Classification
	Dim objDataCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, api.Pov.cube.name, objMbrScrBldr.GetMemberScript())

and check if the data has DataCellDetail:

If objDataCell.DataCellEx.DataCell.CellStatus.StorageType = DataCellStorageType.DataCellDetailYTD OrElse objDataCell.DataCellEx.DataCell.CellStatus.StorageType = DataCellStorageType.DataCellDetailPeriodic

This is all working fine, so i can see in my log that the cellamount is found and the storagetype is DataCellDetailYTD

As soon as i try to use the DataCellDetail component i get a "Object reference not set to an instance of an object" error.

I tried all possible options e.g. to see if there are any LineItems:

If objDataCell.DataCellEx.DataCellDetail?.HasLineItems 

but they all return the same. 

When i checked the database table I saw the OriginId is set to Forms (-30) instead of BeforeAdj (-28) that i call so i updated that as well in my script:

objMbrScrBldr.SetOrigin("Forms")
		objDataCell = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, api.Pov.cube.name, objMbrScrBldr.GetMemberScript())

I've double checked all my intersections and they all match the table dimensions. The only dimension that is in my memberscript but not in the database table is View, i've tried YTD and Annotation but both with no luck.

Off course i can query the database and read the XML but I really want to stick with this approach as much as possible. 

So: does anybody has a working BRApi to retrieve Data Cell Detail and can explain me what i'm missing, that would be great. Thanks in advance.

Marc

[FredLucas · Accepted solution]

Hi @MarcR,

My guess is that you are missing to define that you want that GetDataCell function to include the cell detail.

You'd have to use this version of the function instead and use the options parameter to define that you want the cell info to include that type of detail.

Dim objDataCellInfoUsingMemberScript As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, wfClusterPk, povDataCellPk, cubeName, memberScript, options)

However, because you are on a finance rule I'd actually use an api function instead of a Brapi as that's going to be more performant. I've not tested it myself, but feel free to give the sample code a try:

Dim dcPK As New DataCellPK(api.pov.GetDataCellPk())
dcPK.UD8Id = DimConstants.None
dcPK.ViewId = DimConstants.Forms
Dim dispOptions As New DataCellDisplayOptions(True,True)
Dim objDataCellEx As DataCellEx = api.Data.GetDataCellEx(dcPK, dispOptions)

objDataCellEx.DataCellDetail.HasLineItems

[MarcR]

Thanks @FredLucas, that was exactly what i needed. I hadn't figured out the DataCellDisplayOptions.

Have a great day!