restriction on dimensions in advanced formulas

[ypinchuk]

Hello,

I'm working on the app for project planning. Business requirements: there should be a set of general Project Templates, which are used then for creating new planned projects. The relation type between Template and Budget is 1:M

So, I have two models: Template and Budget, which differ in two dimensions highlighted in blue

To feed Budget with Template data I used the approach based on linking two models and using LINK to map Group and Task by the properties, containing IDs of their templates - T_Group and T_Task respectively.

The target scope is restricted:

MEMBERSET [d/TGC_BUD_Project] = %TargetProject%

and the formula is

It works perfectly when I create the first project from the template.

But after creating several projects from the same template the dimension in the Budget model, e.g., ends up like that (below). There are three created projects - A, B, and C - and t01 and t02 in the TemplateTaskId property repeated three times - for each project.

When I created the next projects based on the same template the formula above took all Groups/Tasks from all previously created projects, which gave me a result which is not correct. You can see - projects B and C, for example, should be the same size as A - 180 in August, but it's not the case.

I guess that the formula above (LINK) does not restrict the Task dimension based on ProjectId - this is the cause of the unexpected behaviour - but I don't know how to do this

Please, advise!

Thank you!

[ypinchuk]

Yes, Budget has a Project dimension, Template has a Template dimension, and I mapped Template to Project (1:M)

The source model is below. The Resource is a shared dimension

[N1kh1l]

TGC_TPL_PROJECT is the Template dimension and its getting mapped to TGC BUD Project through template project id? 1:N . Please show TGC BUD Project dimension view. Also could you show the source Template model data looks like . In my understanding if you pass 3 project in your %target project % parameter, each of them will still look Template model for their respective template project and template task and template group.

[N1kh1l]

I cant edit my own comments 😞

It should work

just try adding some dimension filters like date to lnkied model also. Can you also try FOREACH [d/TGC_BUD_Project]

[N1kh1l]

yuripinchuk

You have a project dimension in both model? I cannot see it above . The dimension you show is Task and I see Project as attribute in it which is getting repeated. I can see Template Task Id also as attribute. I am assuming you will also have Group dimension with attribute Template Group id. How does the source model data looks like? Its should be by temp project id /temp task id/temp group id.

Try restricting the LINK Model also. You can replace All Projects with the top node of Temp Project in Template Model.

MODEL [Template]
	MEMBERSET [d/Temp_Project] = BASEMEMBER([d/Temp_Project].[h/parentId], "All Projects")
ENDMODEL

Nikhil

[ypinchuk]

Project dimension has no hierarchy

[ypinchuk]

To get the expected result I have to restrict the Target model to Group and Task dimensions, so the calculation will be done only on the dimension members that "belong" to the target Project. In that case, the relation type becomes 1:1 and the calculation goes well.

I did this restriction by passing the chosen Groups and Tasks through the Data Action parameters (below). I think there is no other way due to the limitations of Advance Formulas syntaxis and set of functions capabilities.