Building a table-based transformation

The following example shows how to create a last year transformation based on a lookup table in MicroStrategy Tutorial, which pairs each year with the previous year. This transformation is used in the report displayed below, which compares revenue for this year and last year.

Creating the transformation metric and the report are discussed in the Transformation metrics section in the Metrics chapter of the Advanced Reporting Guide.

To create a last year transformation based on a table

1 Log in to the project source that contains your project in MicroStrategy Developer and expand your project.
2 From the File menu, point to New, and select Transformation. The Transformation Editor opens with the Select a Member Attribute dialog box displayed.
3 Double-click Time to open the folder, then double-click Year. The Year - Define a new member attribute expression dialog box opens.
4 Select the LU_Year table from the Table drop-down list. The table's columns appear in the Available columns list. Notice that this table contains a previous year column, which maps this year to last year.
5 Double-click the PREV_YEAR_ID column to place it in the expression box.
6 Click OK.
7 Click Save and Close on the toolbar. Name the transformation Last Year (Table).

You have now created the transformation. A report designer can now use the transformation in a revenue metric to calculate last year’s revenue, then create a report using that transformation metric to obtain last year’s revenue.