the *rec instruction in bpc logic

[Former Member]

Hello

i want to use the *rec instruction saying

*rec (account=A1, entity=Entity.parentH1.location)

means i want to writein the account A1 in the entity written in the property location of the parent of the current entity, if i get what i mean

means i don't want to send the current entity's location, but the parent of the current entity's location

how can i write it please? because entity.parenth1.location don't work obviously

thanks in advance for your precious help

[Former Member]

MDX certainly has ways to reference the parent, but these aren't available when you write SQL logic -- which is what the *REC statement implies.

Tim's approach of setting the parent as a property is conceptually correct, and I use this sometimes in reports do so some fancy dynamic expansions.

But in SQL logic it will introduce errors, since the parent, by definition, is not a base member. The logic will in fact complete (without errors) and toss the data into the WB table with the ID that's set up as the "parent" property of the dimension. But you won't see the results in Excel reports, and you'll get errors when you process the application, and you'll need to manually delete these records via SQL.

The records in the fact table must be base members of every dimension. (I think there are some exceptions to this rule in Analysis Services, to allow for pre-computation of aggregated data. But they're not very well supported by BPC, as best as I can tell.)

Any redirection in your *REC statement, where you use dimension properties to indicate a different member ID where the values should post to, MUST use base members as those property values. Otherwise the SQL logic won't work properly, and the application gets corrupted by the results which are posted to the WB table.

I've always thought it would be great to have a validation option, which is evaluated when processing a dimension, whereby a certain dimension property is flagged as "values must be base member of Dimension XYZ". Someday, somehow, somewhere... In the meantime, I use custom SQL scripts to check this manually, since inevitably this is the cause of errors. If there's a better way, I'd be very happy to hear it.

[Former Member]

If I understand you correctly, you want to retrieve the value of the local property from the parent of the selected entity. I think that this is not possible.

What you can do is to fill the local property of the basemembers with the value that is in the parenth, or to create an additional property in the entity dimention and fill this with the desired values. Syntax for the rec instruction would be:

*REC(ACCOUNT=A1,ENTITY=ENTITY.LOCATION)

Or

*REC(ACCOUNT=A1,ENTITY=ENTITY.NEWPROPERTY)

[Former Member]

So there is NO way to use MDX to retrieve a dimension member's parent's value...?

Thanks!