Max/Min Calculation Row in CV
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 .bas...
Forum Discussion
What is your criteria? Show us the member filter from the cube view for the min and max lines.
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)
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)
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)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)