Dynamic Data copy script is not working

[praveen_kumar334]

Hi Team,

I am trying to create a dynamic copy Data Action. The Data Action should Copy Actual data from the FromYear to the ToYear of the chosen Plan Version.

I have parameters for TargetVersion, FromYear and ToYear that I want to use for a dynamic copy. Could you please help me with the script code.

[N1kh1l]

praveen.kumar334

I would still prefer the Standard Data Copy Action for any Dynamic copying of records but If you really want to use the script to achieve it you can try the below approach.Adjust the Parameter ToYear to only select leaf member and cardinality one.

Now you can use below script to copy the data. I am just copying one measure "QUANTITY" for example.

MEMBERSET [d/MEASURES] = "QUANTITY"
MEMBERSET [d/Date] = BASEMEMBER([d/Date] ,%FromYear% )

INTEGER @PERIOD

//-----------------------------------------------------------------------------------
//Calculation and Data Writing
//-----------------------------------------------------------------------------------
FOREACH [d/Date]
	@PERIOD=YEAR(%ToYear%)-YEAR([d/Date])
	DATA([d/Date]=NEXT(@PERIOD,"YEAR",[d/Date]))=RESULTLOOKUP([d/Version]="public.Actual")

	
ENDFOR

Data Action Prompts for run. For ToYear, any month of the destination Year has to be selected.

Output

Hope this helps !!

Please upvote/accept if this helps.

Nikhil

[N1kh1l]

praveen.kumar334

You can try setting up the Standard Copy step to achieve the same. If you want to filter few measures to be copied you can add them to the filter as shown below

Define the From Period, To Period and Target version as prompts as below example for from period

When you run the Data Action, you will be prompted to selected the Target version, From period and To Period.

Hope this helps. Please upvote/accept if this helps.

Nikhil

[praveen_kumar334]

Hi Nikhil,

Thanks for the info. But I want to achieve this through script. PFB screenshot of psuedo code, but it looks like it is missing something.

I am able to achieve the same functionality by hardcoding the year as shown below:

DATA([d/date] = "202201") = DATA([d/date] = "202112")

But I don't want to hardcode the year.

[N1kh1l]

praveen.kumar334

Using Script to copy is easier if you want to copy last year actuals to current year plan/budget etc. With Script the challenge will be to get the destination time member dynamically. This is only feasible if we know the lookback/look forward period before hand. So the user parameter have to be change and instead of asking the ToYear, you have to use the look forward period . For e.g 3, This will copy Actual from From Year to Plan of From Year +3. Both DATA() and RESULTLOOKUP only support leaf members in syntax which will make it difficult for dynamic handling. Any reason you don't want to use the standard copy step as its designed to precisely handle these scenarios

Nikhil

[praveen_kumar334]

Thanks for the info Nikhil. So you mean to say that we can use the parameter for source(base level) and use the lookback/look forward for the target year ? In that is the case, I am unable to get the parameter even for the target also. PFB screenshot where the parameter tab is in disabled mode.

Please let me know if i am missing anything.

Thanks.

[N1kh1l]

praveen.kumar334

As I said earlier. Both DATA() and RESULTLOOKUP() can only take single leaf members for dimensions as parameters. So your parameters have to be of type leaf and cardinality one. Once you do that you can use them in your script.

Nikhil

[Mastan]

Hi Praveen Kumar,

In order to enable the Parameters few things we need to check

1) In FromDate Parameter the Dimension Cardinality should be single and the Hierarchy you are using should be the default hierarchy which you made in the model.

2) In ToDate Parameter the Dimension Cardinality is your wish but Hierarchy should be same as you mention in the Model

If you follow the above things properly then the parameters option will be enabled

Thanks and best regards,

Mastan