SAP Analytics Cloud Planning : Dynamic LookUp Formula/Data Actions

[former_member810379]

Hello All

I am hoping someone could please help me this situation.

I need to copy data from one product &year to another product on a different year for individual dimension members(product).

• Each Year/Product Combination can have different Reference Year/product to copy from.

Goal: Take Orders from a combination of Product/Year to another combination of Product/Year (multiple at a time).

(red to red, blue to blue).

My Solution is to use Data Action to copy from year/product to year/product , which works fine(1 at time).

However, it is a problem when the user has 100s of products as they need to call the data trigger 100 times.

To be able to do a live batch data copy, i have tried 2 things so far:

• Copy the reference data to a separate measure through LOOKUP formula. The reference year and product numbers are input as measures. This would be the easiest way to achieve the requirement since I can do a simple measure to measure Data Action. However, I cannot make the LookUp formula work dynamically by forcing measure values as the year and product dimension members. I am not sure there is a way to do this dynamically.
• Use Data Action Scripting to again try to force the Reference Year and product measures as the year and product dimensions members in order to loop through all products and dynamically do individual copies. Once again , I hit the roadblock of not being able to force a measure as a dimension member (using variables and parameters).

The challenge here is that the user needs to be able to input value for the reference year and product (for a large batch).

Is there any way to dynamically do Data Action or a Look-up.

Maybe the approach is not good but the basis need to be the ability to input reference year/product on the spot and retrieve the proper data to copy. Really hit a wall now 😞

Thank you so much for any help 🙂

[former_member810379]

Sorry, the screenshots were missing. Added them.

[former_member762711]

Hi,

I have been trying to come up with a similar solution to pick up values dynamically with the look up formula.(without defining the "Dimension Member Static Value" but using a variable Prefilled by input or referring to dimension member at line record LIKE Read Statement in SAP).

It seems that SAC is quite restrictive when it comes to dynamic formula.

Anybody else had better luck?

[former_member810379]

Hello. Did you have any luck solving this?

[borabt]

Hi,

This is exactly what I require and to me seems like a very common requirement in planning projects. I would expect to be able to design something based on some property information to dynamically generate some data.

Is there anyone who were able to achieve a similar requirement with some workarounds?

Can we lookup dimension ids within data action based on some property values? nikhil_1486 any idea? You always have a workaround 🙂

[N1kh1l]

borabt,

Kindly open a new thread and tag and I will try my best. The key would be the link between the source product and target product and also the years. Attributes can be explored if the mapping is 1:1. Tried something with attributes mapping and was able to copy data for products from reference product and year. The different color shows the mapping of product and years.

Nikhil