I am trying to set up account logic via an MDX formula in a DPM cube. I am running into trouble referencing the attributes of the objects. I’ve searched Red Carpet but not found anything specific to attributes.
The gist of what I am trying to do is collect CapEx spending in the CIP account and then zero the balance upon completion of the project when it is moved to FA. I am using an account formula because the CapEx spending has to be gathered via manual template input and I can’t figure out a way to use one account as the input for calculations to target a second account.
Below is the code I am playing with. The DPO field is named “Project” and has an attribute “Project Completion Period” that is populated by the “Time FA” dimension. The rest of the formula works fine. I ASSUME the attributes are member properties but can’t get any type of reference to them to work. Any ideas?
Good morning @frank.martenies
This request is best suited for a Product Adoption consultant to take a look at. I just reached out to your CSM to create a ticket so you should be receiving a link to a consultant’s availability shortly.
@frank.martenies I don’t now the exact answer to your question but I’m interested in the solution. Please post it here if you resolve it offline.
Meanwhile, whether an attribute is also a member property is dependent upon how you set up the definition if that attribute in the DPM.
In the attribute definition screen, click the member property purple ribbon on the lower right. You can then check a box to tell it to create a member property and give it a name.
I tend to make almost all of my attributes member properties just in case I need them later.
(I’m on my phone, not my computer, and working from memory here, so my labels mentioned above are likely to be wrong)
@bob.smiley
The “Project Completion Period” attribute was set up as a member. Same idea, I don’t want to take options away if I don’t have to.
I don’t understand how people are forecasting CapEx without doing this. All of the Academy courses and webinars seem to equate CapEx with Fixed Assets. While the examples give a nice, precise depreciation expense it’s not CapEx forecasting IMO. Surely I am not the only one who has questioned this.
Frank, your IIF is checking the current member of time (which is in format “2022M05”) with the property value. Are you storing the “Project Completion Period” attribute as a date or as a Prophix time value? If the “Project Completion Period” attribute is a date, you’ll need to convert it to a Prophix Time string for the IIF statement to be “true”.
The way I approach this type of thing is very different. Rather than trying to compare a date with a Prophix period (which is very straightforward, so if you get this to work, please let me know how you did it), I take a “flag” approach. By this I mean I introduce a STAT account that will have a 1 (the flag) in the month of project completion. Then, in DPM, I calculate that flag for each month by checking whether the month is the same month as the Project Completion Period attribute. It’s a it of work, but it holds together pretty well.
Then, your IIF statement would be to check the value of the flag in the same month.
IIF( [ProjectCompletionPeriodFlag]=1, SUM(xxx),0)
If you’re interested in this approach I can show you more details. I actually use this approach to map out prospective phases of projects, showing rainbow style gantt charts in Prophix, etc. Once I figure out how to turn a milestone into a flag, a g’zillion opportunities present themselves.
@bob.smiley The attribute is populated with the Time FA dimension, so there should be no data type issues. I’ve trialed and errored every tiny bit of the equation and everything works as expected except the reference to the attribute. The left side will evaluate against a constant, converting both sides to text still fails, etc. I even tried replacing the spaces with underscore (I’m more of a PascalCase kind of guy) and still nothing.
Screenshot of the attribute below. I feel like whatever is not working is either a system limitation or something back at step 1.
If you haven’t been able to tell that, try making this the text value for a calculated account. Create an account called ProjCmplPd or something similar, and make it a calculation including the portion of the formula you’re using to extract the member property text value.
I’m doing this in one of my DPM cubes used for scheduling; the account is actually a text value of the attribute in the DPM cube. See my screenshot below for an example of how I did this (not difficult; just not intuitive since I was originally under the impression that all Prophix intersections had to be numeric; this is not exactly the case, and in cases like this, it helps reveal the underlying text values we are working with…)
@bob.smiley Testing what you have here, the problem is now isolated to referencing custom member properties. I’m able to pull all of the standard ones like key and name. Submitting ticket.
If I understood your issue here, in your IIF statement, are you trying to validate against a member property that has a date value? And if yes, is the date coming from DPM in which the attribute is a date function?
If yes to the above, is it [Project].[Default].CurrentMember.Properties(“Project Completion Period”)?
If it’s a date type member property, then you could try
[Project].[Default].CurrentMember.Properties(“Project Completion Period”,TYPED)
If it’s not a date type member property, then you would need to convert it to a date, and hence could use
CDATE([Project].[Default].CurrentMember.Properties(“Project Completion Period”, TYPED))
I would first try the IIF statement with a simple value for true and false (viz. 1 for True, 2 for False) and see if the statement works before layering on actual formulas / functions in the formula.
The problem is a little further upstream. I can’t refer to the attributes at all to make a comparison. This is the End Period field that is provided by default in DPM, renamed to Project Completion Period. it is populated by the Time dimension.
If I set the formula to a system property and nothing else, it properly pulls through to a test query.
[Project].[Default].CurrentMember.Properties(“Key”)
When I change to a custom property the query returns #Error
[Project].[Default].CurrentMember.Properties(“Project Completion Period”)
I added TYPED and same result.
[Project].[Default].CurrentMember.Properties(“Project Completion Period”, TYPED)
I converted to a date and same result.
CDATE([Project].[Default].CurrentMember.Properties(“Project Completion Period”, TYPED))
Also tried converting to a string, still no dice.
CSTR([Project].[Default].CurrentMember.Properties(“Project Completion Period”, TYPED))
Just to confirm, leaving out the IIF statement, if all your MDX is doing is a reference to the custom member property using (say) [Project].[Default].CurrentMember.Properties(“Project Completion Period”), you are getting an error?
Hmm. No custom properties are showing up. I do have the Member Property box checked in DPM which is how it appears to work on other detailed planning cubes I have set up.
That might be your issue. You are trying to write an MDX to refer to a custom member property that doesn’t exist (going by your screenshot). Can you run a post to the CapEx DPM cube, run a Update Cube (Full), confirm the custom MP shows up in the Project dimension and then try your formula again pls? Cheers!
That’s strange. I have not seen this issue before where one is trying to pass over a custom MP from DPM and it doesn’t make it thru. I see from your screenshot that you have no custom MPs at all for the CapEx DPM cube. I’m guessing the Project Completion Period is the only one you are trying to create from DPM.
In this case, I would recommend you log a Zendesk ticket to arrange a call with a consultant (it’s billable / not depending on whether you have a CSP package) and should it not be resolved via that approach, log a ticket to have our support take a look at it.
, but I’m sorry to hear it’s requiring a support ticket 
. I hope it gets a quick solution for you.
