Friday, October 25, 2013

BPC Member formula ... devil is in the details

I've found a "non working" item in BPC ... it's member formula was something like



IIF([HDCNT]=0,0,[TOT_COST]/[HDCNT])

very nice and workend fine, but ... while cost is currency converted headcount is not ... so it was "not working" ... 

A little revision, rewriting it in order to take into account dimensions brought the desired result



IIF(([HRCOST].[HDCNT],[RPTCURRENCY].[LC])=0,0,([HRCOST].[TOT_COST])/([HRCOST].[HDCNT],[RPTCURRENCY].[LC]))

 

Not that difficult, but I see that BPC people generally don't like to fully qualify things :(

After a while I had to change it again ... adding another bit of MDX to the game, IsLeaf and CurrentMember

IIF(
IsLeaf([TIME].CURRENTMEMBER)
,
IIF(([HRCOST].[FTE],[RPTCURRENCY].[LC])=0,0,([HRCOST].[TOT_COST])/([HRCOST].[FTE],[RPTCURRENCY].[LC]))
,
IIF(([HRCOST].[HEADCNT],[RPTCURRENCY].[LC])=0,0,([HRCOST].[TOT_COST])/([HRCOST].[HEADCNT],[RPTCURRENCY].[LC]))
)