Creating attributes for financial line items

Once you have created the lookup tables and relationship table in your database (see Creating tables to provide hierarchical organization) you can include them in your MicroStrategy project for financial reporting. With the tables included in a MicroStrategy project, you can then assign the values to attributes. You can use MicroStrategy Architect to perform the tasks outlined below. Details on how to use Architect are provided in Creating a Project Using Architect.

For your financial reporting project, each lookup table is represented in MicroStrategy as a single attribute representing that level of elements, with the attribute ID form representing the ID values in the table, and the attribute description form representing the description values in the table. The image below shows the three lookup tables and the one relationship table, from the examples above, included in a project.

To create attributes for financial line items

1 Create attributes for each level, and map the attribute forms to the corresponding columns of data in the level lookup tables.
2 Create the relationships between the level attributes you created, to represent the hierarchical structure of the financial information. The relationships can be simple one-to-many relationships, with the highest level attribute being directly related to the second highest level attribute, the second highest level attribute being directly related to the third highest related attribute, and so on. An example of this is shown below for attributes Level1, Level2, and Level3.

3 Create a hierarchy that represents these attributes as a single, logical group. This hierarchy is used later when creating metrics for the project, as described in Returning financial data with metrics. For background information on hierarchies, see Creating Hierarchies to Organize and Browse Attributes. For a guided set of steps to create a new hierarchy for these attributes, see Creating user hierarchies.

Displaying financial line item attributes in exported results

As part of defining the attributes for your financial line items, you can ensure that these attributes are displayed correctly when your financial reports are exported to Microsoft Excel or as a PDF using MicroStrategy Web.

When a report that includes these financial line item attributes is exported to Excel or as a PDF using MicroStrategy Web, all rows of data are exported. Since these financial line item attributes include empty elements to support the hierarchical structure of the financial data, this causes some of the rows of the exported report to also be empty.

To prevent this, you can identify which of the financial line item attributes include empty elements. These elements are then ignored when reports including those attributes are exported using MicroStrategy Web.

To display financial line item attributes in exported results

1 Determine the ID values for the attribute objects:
a In MicroStrategy Developer, navigate to the attribute, right-click the attribute, and select Properties. The Properties dialog box opens.
b Make a note of the alphanumeric string of characters listed as the ID value. You must determine this ID value for each attribute that includes empty elements. In the example described in Creating attributes for financial line items, this would be Level2 and Level3.
2 Right-click the project that stores your financial data and select Project Configuration. The Project Configuration Editor opens.
3 On the left, expand Project definition, and then select Advanced.
4 In the Project-Level VLDB settings area, click Configure. The VLDB Properties Editor opens.
5 From the Tools menu, ensure that Show Advanced Settings is selected.
6 In the VLDB Settings pane, expand Export Engine, then select the GUID of attributes in profit and loss hierarchy (separated by ‘:’) that has dummy rows to be removed VLDB property.
7 Clear the Use default inherited value check box.
8 In the text field, type each ID value for all financial line item attributes that include empty elements. Use a colon (:) to separate each attribute ID value.
9 Click Save and Close to save your changes and close the VLDB Properties Editor.

When you use MicroStrategy Web to export a report that includes financial line item attributes, the empty elements will not be shown in the exported data.