During the planning process, there will be activities that require to copy data from the previous year or another version. There are multiple ways to achieve it via ETL but in this article, we are going to show you how to do it via hyperlink.
Copying using native copy/like function
If you ever attended any Jedox training you have probably learned splashing commands like Copy and Like. Both of them can be found on the knowledge base and this is good pre-knowledge for the following segment.
Copying using hyperlink function
Copy and Like function could be converted into the hyperlink and do the same job.
In the cell where you would like to create a hyperlink copy the following code.
=HYPERLINK(“SELF”,”Copy Data”,”Copy Data”,”Copy “&F$5,$G6)
This code is another way to represent “Copy 2017” and G6 is a cell to where we want data to be applied.
Copying using hyperllink in the button
A similar thing is done in the next example but only instead of the hyperlink, we are using the button.
In case you would like that your copy functionality has a bit different design, there is a way to display a picture for a copying action.
These types of alternatives are good replacements for ETL. Some pros and cons
- Less possibility for ETL job to fail or be queued
- Minimize the number of ETLs in case the same functionality is needed in other places
- Copying data on a high level where a number of dimensions and elements are large could produce some spreadsheet problems.
- Could be mixed with some another variable and make coping more dynamic
There is no specific rule when the copy function should be replaced by ETL but if the time is exceeding 20 or 30 seconds maybe the ETL alternative is a better one.
A spreadsheet of all examples can be found here.