Example Using the Solve Order Property

The following example is based on the ASOsamp.Sample database. To remove ambiguity in query results, the example uses the solve order property to specify the required calculation priority.

The spreadsheet query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) retrieves data for the number of units sold and the number of transactions for January of the current year and for January of the previous year. The Variance member shows the difference between the current year and the previous year. The Avg Units/Transaction member shows a ratio of the number of units sold per transaction.

Figure 166. Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)

This image shows the results of a spreadsheet query, as described in the text preceding the image.

Figure 167, ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions shows the database outline for these members and the formulas applied to the Variance and Avg Units/Transaction members.

Figure 167. ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions

This image shows an outline, as described in the text preceding the image.

When calculating the variance of the average units per transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)), the result could be the variance between the two ratios, or the result could be the ratio of the two variances. The result depends on whether Essbase gives precedence to the formula on Variance or the formula on Avg Units/Transaction.

The value of the solve order property, which is attached to the members in the database outline, determines the priority with which Essbase evaluates the formulas. The formula on the member that has the higher solve order takes precedence.

In the example, if the Variance member has a higher solve order than the Avg Units/Transaction member, then the formula on the Variance member takes precedence and the result is the variance between two ratios. This is the case in the ASOsamp.Sample database, because the solve order of the Variance member is 20 and the solve order of the Avg Units/Transaction member is 10. The formula on Variance takes precedence, because the Variance member has the higher solve order. The result for cell C12 of the query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) is the variance between the two ratios, as shown in Table 206:

Table 206. Using the Solve Order Property to Specify the Variance Between Two Ratios

Member

Solve Order

Formula

Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12))

Variance

20

Curr Year - Prev Year

Current year average units/transaction - previous year average units/transaction

0.94894382 (cell C6) - 0.954252111 (cell C9) = -0.005308291 (cell C12)

Avg Units/Transaction

10

Units/Transactions

Alternatively, if you change the ASOsamp.Sample database, and you give the Avg Units/Transaction member a higher solve order than the Variance member, then the formula on the Avg Units/Transaction member takes precedence, and the result is the ratio of two variances, as shown in Table 207 and in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12):

Table 207. Using the Solve Order Property to Specify the Ratio of Two Variances

Member

Solve Order

Formula

Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12))

Variance

10

Curr Year - Prev Year

Variance (current year to previous year) of units / variance of transactions

10585 (cell C10) / 11340 (cell C11) = 0.933421517 (cell C12)

Avg Units/Transaction

20

Units/Transactions

Figure 168. Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12)

This image shows the results of a spreadsheet query, as described in the text preceding the image.