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.
Note
Report Designer cannot select any account, dimension, or field from the Microsoft Dynamics ERP system that includes the following reserved characters: & * [ ] { }.
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.
When this link type is selected |
The description on the Link column changes to |
|---|---|
Financial Dimensions |
Link to Financial Dimensions |
External Worksheet |
Link to Worksheet |
Financial Dimensions + Worksheet |
Link to Financial Dimensions + Worksheet |
Management Reporter Report |
Management Reporter Report |
Specify a dimension or range
In Report Designer, open the row definition to modify. Double-click a cell in the Link to Financial Dimensions column.
In the Dimensions dialog box, double-click a cell under the dimension name.
In the dialog box for the dimension, select Individual or range.
Enter the starting dimension in the From field, or click
.gif "Browse")
to search for available dimensions. To enter a range of dimensions, enter the ending dimension in the To field.Click OK to close the dialog box for the dimension. The updated dimension or range is displayed in the Dimensions dialog box.
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
In Report Designer, open the report definition to modify.
Click the Settings tab.
Under Other formatting, select options for the row definition that is used in the report definition.
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.
Note
The use of wildcard characters should be avoided for the starting and ending accounts in ranges. If you use wildcard characters in either the starting account or the ending account, you might return unexpected results.
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.
Note
Report Designer is unable to select any account, dimension, or field from the Microsoft Dynamics ERP system that includes the following reserved characters: & * [ ] { }. You can add an ampersand (&) only when you are automatically building row definitions by using the Insert Rows from Dimensions dialog box. For more information, see Row definition.
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.
Note
Report Designer is unable to select any account, dimension, or field from the Microsoft Dynamics ERP system that includes the following reserved characters: & [ ] { }. You can add an ampersand (&) only when you are automatically building row definitions by using the Insert Rows from Dimensions dialog box. For more information, see Row definition.
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.
To do this |
Use this format |
|---|---|
Add two fully-qualified accounts |
+Division=[000], Account=[1205], Department=[00] +Division=[100], Account=[1205], Department=[00] |
Add two segment values |
+Account=[1205]+Account=[1210] |
Add segment values that include wildcard characters |
+Account=[120?+Account=[11??] |
Add a range of fully-qualified accounts |
+Division=[000:100], Account=[1205], Department=[00] |
Add a range of segment values |
+Account=[1200:1205] |
Add a range of segment values that include wildcard characters |
+Account=[120?:130?] |
Subtract one fully-qualified account from another fully-qualified account |
+Division=[000], Account=[1205], Department=[00]-Division=[100], Account=[1205], Department=[00] |
Subtract one segment value from another segment value |
+Account=[1205]-Account=[1210] |
Subtract a segment value that includes a wildcard character from another segment value |
+Account=[1200]-Account=[11??] |
Subtract a range of fully-qualified accounts |
-Division=[000:100], Account=[1200:1205], Department=[00:01] |
Subtract a range of segment values |
-Account=[1200:1205] |
Subtract a range of segment values that include wildcard characters |
-Account=[120?:130?] |
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: & * [ ] { }.
Note
To subtract values, you must put parentheses around those values. For example, if you enter 450?-(4509), it is displayed as +Account=[4509]-Account=[450?], and you are instructing Management Reporter to subtract the amount for account segment 4509 from the amount for any account segment that starts with 450.
Add or subtract accounts from other accounts
In Report Designer, open the row definition to modify.
In the appropriate row, double-click the cell in the Link to Financial Dimensions column. The Dimensions dialog box opens.
In the first row of the Dimensions dialog box, follow these steps:
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.
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.
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.
Enter a segment value in the From column and To column.
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.
Item |
Description |
|---|---|
Individual or range |
In the From field, type the name of an account, or click |
Dimension Value Set |
In the Name field, type the name of a dimension value set. To create, modify, copy, or delete a set, click Manage Dimension Value Sets. The formula field is populated with the formula from the Link to Financial Dimensions cell for this dimension value set in the row definition. |
Totaling accounts |
In the Name field, type or browse for a dimension of totaling accounts. The Formula field is populated with the formula in the Link to Financial Dimensions cell for this totaling account in the report definition. |
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
In Report Designer, open the row, column, or tree definition to modify.
On the Edit menu, click Manage Dimension Value Sets.
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.
In the New dialog box, type the name and description for the set.
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.
Repeat step 5 in the To column to design a formula for that operator.
When the formula is completed, click OK.
In the Manage Dimension Sets dialog box, click Close.
Update a set of dimension values
In Report Designer, open the row, column, or tree definition to modify.
On the Edit menu, click Manage Dimension Value Sets.
In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.
Select the dimension value set to update from the list, and then click Modify.
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.
Double-click the cell to select the appropriate Operator, From account, and To account.
Click OK to close the Modify dialog box and save changes.
Copy a dimension set
In Report Designer, open the row, column, or tree definition to modify.
On the Edit menu, click Manage Dimension Value Sets.
In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.
Select the set to copy from the list, and then click Save As.
Enter a new name for the copied set, and then click OK.
Delete a dimension set
In Report Designer, open the row, column, or tree definition to modify.
On the Edit menu, click Manage Dimension Value Sets.
In the Manage Dimension Value Sets dialog box, select the dimension type in the Dimension field.
Select the set to delete, and then click Delete. Click Yes to permanently delete the dimension value set.