Building an expression-based transformation

This example shows how to create a last year transformation using an expression rather than a table. The Year_ID is in the format YYYY, so the previous year is simply Year_ID minus one. For example, one subtracted from the year 2005 results in the previous year, 2004.

This transformation is added to the report shown in the table-based transformation example above. The resulting report is displayed below.

Creating the transformation metric and the report are discussed in the Transformation metrics section in the Metrics chapter of the Advanced Reporting Guide.
The performance of reports that use expression-based transformations can be improved in certain scenarios using the Transformation Optimization VLDB property. For information on this VLDB property and how it can improve report performance, see the Supplemental Admin Guide.

To create a last year transformation based on an expression

1 In MicroStrategy Developer, from the File menu, point to New, and select Transformation. The Transformation Editor opens with the Select a Member Attribute dialog box displayed.
2 Double-click Time to open the folder, then double-click Year. The Year - Define a new member attribute expression dialog box opens.
3 Select the LU_Year table from the Table drop-down list. The table's columns appear in the Available columns list.
4 Double-click the YEAR_ID column to place it in the expression box.
5 Type -1 in the expression box. The transformation will subtract one from the Year ID to calculate last year’s ID.
6 Click Validate. The message “Valid expression” appears with a green check mark.
7 Click OK.
8 Click Save and Close on the toolbar. Name the transformation Last Year (Expression).

You have now created the last year transformation. A report designer can now use the transformation in a revenue metric to calculate last year’s revenue, then add it to the report created in the previous example.