Forum Discussion
dynamic index
I have to combine several files from different departments and all the files have the same structure initially. From the facts file, I was thinking of making a dimension table for KPI. The rows/KPIs should be in a specific order and I am thinking of adding an index to get the correct sorting/order.
In the future department A can add an extra line in their file while other files are still the same.
example initially:
KPIs
department A revenue
department A cost of goods
department A net profit
department B revenue
department B cost of goods
department B net profit
dim KPI
1. revenue
2. cost of goods
3. profit
departmentA now adds f.example another line of cost in their file
revenue
cost of goods
cost of X
profit
How can I make sure that the dimension table still have the correct order after the introduction of Cost of X in the new file?
1.revenue
2.cost of goods
3.Cost of X
4.profit
1 Reply
Good luck with that.
Rather than starting your index at 1, start it at a higher number and count down
10. revenue
9. cost of goods
8. profit
Now when Cost of x comes in it would be added at the end.
It will most likely be better to have a business managed table (for example on a SharePoint) with the sort order. You can then modify that file instead of having to touch the report.
