04-01-2022
11:32 AM
- last edited on
05-19-2023
11:05 AM
by
JackLacava
Hello!
I received a request to add a maximum and minimum line to a cube view. The cube view shows entities in columns and a variety of accounts in the rows (not together in a hierarchy, so no .base or .children, just a list separated by commas).
Max/min aren't available in the "getdatacell expressions" area so I emailed support to see if it's possible and they directed me to a ranking BR in GS. I loaded the BR, added a row to my CV and updated the pull but still am getting no data. Honestly, I don't think this BR and pull are the solution for what I'm trying to do but I don't know of any other way.
Do any of you have samples of pulling something similar? Were you able to get a max and min in a CV via CV math or some other way?
Appreciate any help I can get - thanks!
Here's the KB article provided for reference:
And the GS CV is in CVs > XFR Member filters (E) Member list using business rule > XFR Member List (C) Ranked Entity Sales.
04-01-2022 12:17 PM
What is your criteria? Show us the member filter from the cube view for the min and max lines.
04-04-2022 09:35 AM
Hello!
Nothing works but here's what was in the KB article OS support suggested:
Example – need to assign all dimensions in LoopMemberFilter
UD2#Root.CustomMemberList(BRName = XFR_MemberListRanked,MemberListName = Ranked, RankType = Top, RankCount = 10, LoopMemberFilter = UD2#Top.Base, DataCellToRankMemberFilter = Cb#|CVCube|:E#[|CVEntity|]:C#|CVConsolidation|:S#|CVScenario|:T#|CVTime|:V#YTD:A#60000:F#[TopFlows]:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None)
And this is the sample pull suggested in the Golfstream BR:
E#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Top,Bottom,Min,Max), RankCount = X, LoopMemberFilter = E#Root.Base, DataCellToRankMemberFilter = CB#:E#... )
This is what I tried and got nothing (no rows added to the CV):
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Min), RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
And this one gave me an error:
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Min), RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
04-04-2022 09:54 AM
Try this one.
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType =Min, RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
04-04-2022 10:20 AM
Hello!
I noticed those parenthesis as I was copying the formulas, thanks. The line does show up on the CV now but it's not what I actually want anyway. I don't know how to add the account filter to the formula because right now, it's just showing the Assets_Tot line because that's what I'm telling it to show. It's not actually showing the min/max of anything, just one line.
The account filter which I want the max/min of is a bunch of accounts in a list but when I try to add them to the formula, it only picks up whichever account is listed first. Here's what it looks like:
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Assets_TOT, A#CurrentAssets_CA, A#CashPlusEquiv_CA, A#RestCashPlusEquiv_CA, A#MarkSec_CA, A#AllARNet_CA, A#InventoryNet_CA, A#Prepaid_CA,
A#IncTaxReceive_CA, A#IncTaxDeferreds_CA, A#ICRecAll_CA, A#MiscAssets_CA, A#HeldForSale_CA, A#NoncurrentAssets_NA, A#MarkSec_NA, A#PPENet_NA, A#OpLeaseNet_NA, A#GoodwillNet_NA, A#IntangNet_NA, A#IncTaxReceive_NA, A#IncTaxDeferreds_NA, A#ICRecAll_NA, A#ICInvest_NA, A#RestCashPlusEquiv_NA, A#AllARNet_NA, A#InventoryNet_NA, A#MiscAssets_NA, A#LiabsPlusEquity_TOT, A#Liabs_TOT, A#CurrentLiabs_CL, A#AP_Accr_CL,
A#330_500000, A#Debt_CL, A#AccrSettlements_CL, A#IncTaxPay_CL, A#IncTaxDeferreds_CL, A#ICPayAll_CL, A#HeldForSale_CL, A#NoncurrentLiabs_NL,
A#425_250000, A#Debt_NL, A#IncTaxPay_NL, A#IncTaxDeferreds_NL, A#ICPayAll_NL, A#MiscLiabs_NL, A#Accr_NL, A#AccrRestruct_NL, A#AccrSettlements_NL, A#AccrGeneral_NL, A#OthMiscLiabs_NL, A#Equity_TOT, A#EquityWithNCI_SE, A#CapitalEquity_SE, A#PrefShares_SE, A#OrdShares_SE, A#510_300000, A#APICAll_SE, A#510_500000, A#REAll_SE, A#AOCI_SE, A#EquityOther_SE, A#530_100000, A#NetIncNoNCI_TOT, A#NetIncWithNCI_TOT, A#NetIncCont_TOT, A#PreTaxInc_TOT, A#OpInc_TOT, A#GM_TOT, A#RevenuesNet_TOT, A#CoGS_TOT, A#OpExp_TOT, A#OpExpNatural_TOT, A#Impair_TOT, A#110, A#OpExpFunctional_TOT, A#AcqInt_TOT, A#SGA_TOT, A#RD_TOT, A#NonOpItems_TOT, A#InterestAll_TOT, A#OtherItems_TOT, A#780, A#OtherItemsGeneral_TOT, A#790, A#FX_TOT, A#GLSaleAsset_TOT, A#GLInvest_TOT, A#IncTax_TOT, A#810, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
04-04-2022 04:56 PM
It could be because the account members are listed as comma separated.
Try this.
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Root.List([Assets_TOT], [CurrentAssets_CA], [CashPlusEquiv_CA], [RestCashPlusEquiv_CA], [MarkSec_CA], [AllARNet_CA], [InventoryNet_CA], [Prepaid_CA], [IncTaxReceive_CA], [IncTaxDeferreds_CA], [ICRecAll_CA], [MiscAssets_CA], [HeldForSale_CA], [NoncurrentAssets_NA], [MarkSec_NA], [PPENet_NA], [OpLeaseNet_NA], [GoodwillNet_NA], [IntangNet_NA], [IncTaxReceive_NA], [IncTaxDeferreds_NA], [ICRecAll_NA], [ICInvest_NA], [RestCashPlusEquiv_NA], [AllARNet_NA], [InventoryNet_NA], [MiscAssets_NA], [LiabsPlusEquity_TOT], [Liabs_TOT], [CurrentLiabs_CL], [AP_Accr_CL], [330_500000], [Debt_CL], [AccrSettlements_CL], [IncTaxPay_CL], [IncTaxDeferreds_CL], [ICPayAll_CL], [HeldForSale_CL], [NoncurrentLiabs_NL], [425_250000], [Debt_NL], [IncTaxPay_NL], [IncTaxDeferreds_NL], [ICPayAll_NL], [MiscLiabs_NL], [Accr_NL], [AccrRestruct_NL], [AccrSettlements_NL], [AccrGeneral_NL], [OthMiscLiabs_NL], [Equity_TOT], [EquityWithNCI_SE], [CapitalEquity_SE], [PrefShares_SE], [OrdShares_SE], [510_300000], [APICAll_SE], [510_500000], [REAll_SE], [AOCI_SE], [EquityOther_SE], [530_100000], [NetIncNoNCI_TOT], [NetIncWithNCI_TOT], [NetIncCont_TOT], [PreTaxInc_TOT], [OpInc_TOT], [GM_TOT], [RevenuesNet_TOT], [CoGS_TOT], [OpExp_TOT], [OpExpNatural_TOT], [Impair_TOT], [110], [OpExpFunctional_TOT], [AcqInt_TOT], [SGA_TOT], [RD_TOT], [NonOpItems_TOT], [InterestAll_TOT], [OtherItems_TOT], [780], [OtherItemsGeneral_TOT], [790], [FX_TOT], [GLSaleAsset_TOT], [GLInvest_TOT], [IncTax_TOT], [810]), DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
04-04-2022 04:58 PM
Oh, one more thing in your DataCellToRankMemberFilter there is no Account, you need to mention an account in there.
04-06-2022 05:57 PM
Hello!
Thanks so much! I tried the formula you listed above and popped in a random account and I'm getting this error:
I'm not sure I understand what "DataCellToRankMemberFilter" actually means so I just put one of the accounts from my filter in there.
Thanks for the help so far!
04-06-2022 11:32 PM
Click the ellipses and scroll to the bottom; copy and paste the error here. We need to check which function/subroutine is throwing the error.
04-07-2022 08:10 AM - edited 04-07-2022 08:25 AM
Hello!
I'm pretty sure the DataCellToRankMemberFilter is incorrect because I'm not actually sure what it means so I just guessed at what to include. I've asked OS support for a definition so hopefully that helps direct me. Here's how the formula currently looks:
A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Root.List([Assets_TOT], [CurrentAssets_CA], [CashPlusEquiv_CA], [RestCashPlusEquiv_CA], [MarkSec_CA], [AllARNet_CA], [InventoryNet_CA], [Prepaid_CA], [IncTaxReceive_CA], [IncTaxDeferreds_CA], [ICRecAll_CA], [MiscAssets_CA], [HeldForSale_CA], [NoncurrentAssets_NA], [MarkSec_NA], [PPENet_NA], [OpLeaseNet_NA], [GoodwillNet_NA], [IntangNet_NA], [IncTaxReceive_NA], [IncTaxDeferreds_NA], [ICRecAll_NA], [ICInvest_NA], [RestCashPlusEquiv_NA], [AllARNet_NA], [InventoryNet_NA], [MiscAssets_NA], [LiabsPlusEquity_TOT], [Liabs_TOT], [CurrentLiabs_CL], [AP_Accr_CL], [330_500000], [Debt_CL], [AccrSettlements_CL], [IncTaxPay_CL], [IncTaxDeferreds_CL], [ICPayAll_CL], [HeldForSale_CL], [NoncurrentLiabs_NL], [425_250000], [Debt_NL], [IncTaxPay_NL], [IncTaxDeferreds_NL], [ICPayAll_NL], [MiscLiabs_NL], [Accr_NL], [AccrRestruct_NL], [AccrSettlements_NL], [AccrGeneral_NL], [OthMiscLiabs_NL], [Equity_TOT], [EquityWithNCI_SE], [CapitalEquity_SE], [PrefShares_SE], [OrdShares_SE], [510_300000], [APICAll_SE], [510_500000], [REAll_SE], [AOCI_SE], [EquityOther_SE], [530_100000], [NetIncNoNCI_TOT], [NetIncWithNCI_TOT], [NetIncCont_TOT], [PreTaxInc_TOT], [OpInc_TOT], [GM_TOT], [RevenuesNet_TOT], [CoGS_TOT], [OpExp_TOT], [OpExpNatural_TOT], [Impair_TOT], [110], [OpExpFunctional_TOT], [AcqInt_TOT], [SGA_TOT], [RD_TOT], [NonOpItems_TOT], [InterestAll_TOT], [OtherItems_TOT], [780], [OtherItemsGeneral_TOT], [790], [FX_TOT], [GLSaleAsset_TOT], [GLInvest_TOT], [IncTax_TOT], [810]), DataCellToRankMemberFilter = Cb#|CVCube|:E#[|CVEntity|]:C#|CVConsolidation|:S#|CVScenario|:T#|CVTime|:V#YTD:A#780:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U3#|CVUD3|:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)
04-07-2022 08:22 AM
That means you provide the intersection of data to do the ranking. All the members except the one you are looping must point to the correct intersection. The code gets the data using the loop (in your case, it gets data for the accounts), then performs the ranking.
04-07-2022 08:28 AM - edited 04-07-2022 08:31 AM
Please post the full message, with that error you need to at least point to the function that is causing the call. That's all we can check for now.
04-07-2022 03:30 PM
I don't understand. If the DataCellToRankMemberFilter is defining the rest of the dimensions which are then using the LoopMemberToFilter, why would I include one (or any) account in the DataCellToRankMemberFilter?
The wording of the error is pasted in an earlier message but here's a screenshot also:
Thank you!
04-08-2022 03:02 PM
You are right, it is using a MemberScriptBuilder, so even if you don't supply it. I just wanted it to have everything so that we can narrow it down. However, this seems like you need to enable logging inside the GetRankedMembers function to see which line is failing.