## Adding the solve order column to tables that have ASO formulas

In this recipe, we will include an additional column to our `MEASURES`

table to specify the solve order for the hierarchy. The ASO outline does not have the Two Pass Calc option in its Account dimension; as a result, you will have to specify the solve order by adding an additional column.

### Getting ready

To get started, open SQL Server Management Studio, and add a database called `TBC`

**.** In this recipe, we are using T-SQL, but the PL\SQL equivalent is provided in the examples. The `MEASURES`

** **dimension was created in the recipe *Setting up an Account or Measure dimension with parent-child reference* in Chapter 1. We need to complete step 1 of the aforementioned recipe before we continue.

### How to do it...

Execute the following script to add the

`FORMULA_MDX`

and`SOLVE_ORDER`

columns to the`MEASURES`

table, if it does not exist:--This is the script in T-SQL Alter Table MEASURES Add FORMULA_MDX VARCHAR(4000) NULL; Alter Table MEASURES Add SOLVE_ORDER INT NULL; --This is the script in PL-SQL Alter Table MEASURES ADD FORMULA_MDX VARCHAR2(4000) NULL; Alter Table MEASURES Add SOLVE_ORDER INTEGER NULL;

Execute the following scripts to add the formula and the solve order values to the

`MEASURES`

table:INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOP ASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE,FORMULA_MDX, SOLVE_ORDER) Values(0, 14, 'Ratios', 'Margin %', '', '+', 'T','X', '', '', '', '', 'Margin % Sales;', '', '[Measures].[Sales] / [Measures].[Margin];', 20); INSERT INTO MEASURES(SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX, SOLVE_ORDER) Values(0, 15, 'Ratios', 'Profit %', '', '~', 'T', 'X', '', '', '', '', 'Profit % Sales;', '', '[Measures].[Sales] / [Measures].[Profit];', 20);

Select from the table to see the values that you added:

Select * From MEASURES;

### How it works...

We started this recipe by adding the `SOLVE_ORDER`

column to the `MEASURES`

table. We also added two new rows with the `SOLVE_ORDER`

populated. The objective of this recipe is to show you that the `SOLVE_ORDER`

value has to be higher than its respective components in order for the formula to return the correct values. We should consider the following steps when assigning `SOLVE_ORDER`

:

Set up in

`SOLVE_ORDER`

in increments of tens or twenties for clarity and consistency.When the default is not specified,

`SOLVE_ORDER`

is zero, but it is good practice to always specify the`SOLVE_ORDER`

to remove ambiguity and define the calculation's priority.