Specify Link to Financial Dimensions cell

The Link to Financial Dimensions cell contains links to the financial data to include in each row of a report. This cell contains dimension values, but you can specify cells in an Excel worksheet instead of, or in addition to, segment values or dimension values. For information about how to link to Excel worksheets, including how to change the type of link, see the “Link reports to Microsoft Excel” section in Management Reporter and Microsoft Excel.

This topic contains the following sections:

• Specify a dimension or range

• Display zero balance accounts in a row definition

• Wildcards and ranges in a row definition

• Single-segment or single-dimension ranges

• Multiple-segment or multiple-dimension ranges

• Add or subtract from other accounts in a row definition

• Description of the dimension dialog box

• Add dimension value sets in a row definition

To open the Dimensions dialog box, double-click the Link to Financial Dimensions cell.

To specify information for a row that is already in the row definition, add the information in the Link to Financial Dimensions cell. To add new rows that link to the financial data, use the Insert Rows from dialog box to create new rows in the report definition.

The column title changes depending on how the column is configured, as shown in the following table.

Specify a dimension or range

1. In Report Designer, open the row definition to modify. Double-click a cell in the Link to Financial Dimensions column.

2. In the Dimensions dialog box, double-click a cell under the dimension name.

3. In the dialog box for the dimension, select Individual or range.

4. Enter the starting dimension in the From field, or click to search for available dimensions. To enter a range of dimensions, enter the ending dimension in the To field.

5. Click OK to close the dialog box for the dimension. The updated dimension or range is displayed in the Dimensions dialog box.

6. Click OK to close the Dimensions dialog box.

Display zero balance accounts in a row definition

By default, Management Reporter suppresses the printing of any row that does not have a corresponding balance in the financial data. Therefore, you can create one row definition that includes all natural segment values or all dimension values, and then use that row definition with any of your departments.

Modify zero balance settings

1. In Report Designer, open the report definition to modify.

2. Click the Settings tab.

3. Under Other formatting, select options for the row definition that is used in the report definition.

4. To save these options, click Save on the File menu.

Wildcards and ranges in a row definition

When you enter a natural segment value in the Dimensions dialog box, you can place a wildcard character (? or *) in any position of a segment. Management Reporter extracts all of the values for the defined positions without regard to the wildcard numbers. For example, if the row definition contains only natural segment values, assuming a four-character natural segment, entering 6??? in a row instructs Management Reporter to include all accounts whose natural segment value starts with a 6. Entering 6* would return the same results, but would also include variable width values, such as 60 and 600000.

Management Reporter replaces each wildcard character (?) with the complete range of possible values, including letters and special characters. For example, in the range from 12?0 through 12?4, the wildcard character in 12?0 is replaced with the lowest value in the character set, and the wildcard character in 12?4 is replaced with the highest value in the character set.

Single-segment or single-dimension ranges

You can specify a range of segment values or dimension values. The benefit of specifying a range is that you do not have to update the row definition every time that a new segment value or dimension value is added to the financial data.

For example, the range +Account=[6100:6900] pulls the values from Account 6100 through 6900, inclusively, into the row amount.

When a range includes a wildcard character (?), Management Reporter does not evaluate the range on a character-by-character basis. Instead, the low and high ends of the range are determined, and then the end values and all values between those ends are included.

Multiple-segment or multiple-dimension ranges

When you enter a range by using combinations of multiple dimension values, the range comparison is accomplished on a dimension-by-dimension basis. The range comparison cannot be accomplished on either a character-by-character basis or a partial segment basis.

For example, the range +Account=[5000:6000], Department=[1000:2000], Cost center=[00] includes only the accounts that match each segment. In this scenario, the first dimension must be in the range from 5000 through 6000, the second dimension must be in the range from 1000 through 2000, and the last dimension must be 00. For example, +Account=[5100], Department=[1100], Cost center=[01] would not be included in the report because the last segment is out of the specified range.

If a segment value includes spaces, enclose that value in square brackets [ ]. The following values are valid for a four-character segment: [ 234], [123 ], [1 34]

Dimension values should be enclosed in square brackets, which Management Reporter will do for you.

When a multiple-segment or multiple-dimension range includes wildcard characters (? or *), the low and high ends of the whole multiple-segment or multiple-dimension range is determined, and then all values between those ends are included, inclusively. If you have a large range, such as the whole range of accounts 40000 to 99999, you should specify a valid starting and ending account, when it is possible.

Add or subtract from other accounts in a row definition

To add or subtract monetary amounts in one account from the monetary amounts in another account, you can use the plus sign (+) and the minus sign (−) in the Link to Financial Dimensions cell.

The following table shows acceptable formats for adding and subtracting links to financial data.

Although you can modify the accounts directly, you can also use the Dimensions dialog box to apply the correct formatting to your financial data links. Any of the values can include wildcard characters (? or *). Report Designer cannot select any account, dimension, or field from the Microsoft Dynamics ERP system that includes the following reserved characters: & * [ ] { }.

Add or subtract accounts from other accounts

1. In Report Designer, open the row definition to modify.

2. In the appropriate row, double-click the cell in the Link to Financial Dimensions column. The Dimensions dialog box opens.

3. In the first row of the Dimensions dialog box, follow these steps:

   1. In the first field, select all dimensions (default), or click to open the Manage Dimension Sets dialog box to create, modify, copy, or delete a set.

   2. Double-click the Operator +/- cell, and select the operation plus (+) or minus (−) that applies to one or more dimension values or sets in the row.

   3. In the appropriate dimension value column, double-click the cell to open the dimension dialog box, and select whether this is an Individual or range, Dimension Value Set, or Totaling accounts. For definitions of these fields, see Description of the dimension dialog box.

   4. Enter a segment value in the From column and To column.

4. Repeat steps 2 and 3 to add more operations.

The operator applies to all dimensions in the row.

Description of the dimension dialog box

The dimension dialog box contains the fields described in the following table.

Add dimension value sets in a row definition

A dimension value set is a named group of dimension values. A dimension value set can contain values in a single dimension only, but you can use a dimension value set in multiple row definitions, column definitions, reporting tree definitions, and report definitions. You can also combine dimension values sets in a report definition.

When a change to your financial data requires that you change the dimension value set, you can update the dimension value set definition, and that update applies to all areas that use the dimension value set. For example, if you frequently indicate a range of values to link to your financial data, such as the values from 5100 through 5600, you might assign this range to an account set titled Sales.

After you create a set of dimension values, you can select that set as your financial data link.

In another example, if you have the value range of 5100 through 5600 assigned to Sales and 4175 assigned to Discounts, you can determine total sales by subtracting Discounts from Sales indicated as (5100:5600)-4175.

Create a set of dimension values

1. In Report Designer, open the row, column, or tree definition to modify.

2. On the Edit menu, click Manage Dimension Value Sets.

3. In the Manage Dimension Value Sets dialog box, select the type of dimension value set to create in the Dimension field, and then click New.

4. In the New dialog box, type the name and description for the set.

5. In the From column, double-click inside a cell to open the Account dialog box. Select the account name from the list, or search for the entry in the Search field. Click OK.

6. Repeat step 5 in the To column to design a formula for that operator.

7. When the formula is completed, click OK.

8. In the Manage Dimension Sets dialog box, click Close.

Update a set of dimension values

1. In Report Designer, open the row, column, or tree definition to modify.

2. On the Edit menu, click Manage Dimension Value Sets.

3. In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.

4. Select the dimension value set to update from the list, and then click Modify.

5. In the Modify dialog box, modify the formula values to include in the set.

   Note

   If you add new accounts or dimensions, make sure that you modify the existing dimension value sets to incorporate the changes.

6. Double-click the cell to select the appropriate Operator, From account, and To account.

7. Click OK to close the Modify dialog box and save changes.

Copy a dimension set

1. In Report Designer, open the row, column, or tree definition to modify.

2. On the Edit menu, click Manage Dimension Value Sets.

3. In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.

4. Select the set to copy from the list, and then click Save As.

5. Enter a new name for the copied set, and then click OK.

Delete a dimension set

1. In Report Designer, open the row, column, or tree definition to modify.

2. On the Edit menu, click Manage Dimension Value Sets.

3. In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.

4. Select the set to delete, and then click Delete. Click Yes to permanently delete the dimension value set.

See Also

Row definition