Advanced Stored User Data

Related Articles...

Suppose you wanted to store viewer data based on multiple keys.

1. Dynamics NAV Cronus Example

   Let's say you want to insert a columns replicator to your report to make new budget columns for each department and add a filter for departments. The department information is stored in the Dimension Value table since it is Global Dimension 1 by default. So you could insert a column F and add the columns replicator above the rows replicator and reference that value under the Department header.

   

   You also have to add a filter for the department (Global Dimension 1) to the GL function returning budgeted amounts.

   

   Since departments are being replicated across columns, the unique key for the department must appear in the column where the viewer data will be stored, in this case column H, cell H6. Then you must type "Key" in column A in the cell that intersects "Data" with the unique key value, in this case cell A6.

   

   Since you can have multiple unique keys to identify your data, each key must have a name. In this case, you can name the key "department", which you can type into cell C6.  You must also type "Keyname" into cell C1 so that the key name appears at the intersection of "Keyname" (column C) and "Key" (row 6). Finally, row 6 can be hidden since it is not necessary to view it in Report Mode.

   

   Now if you run this report, you will see a new set of green columns for each department, and any information the user types into the Projected Budget columns will be stored for each department.

   

2. Dynamics GP Fabrikam Example

   Let's say you want to insert a columns replicator to your report to make new budget columns for each period and add a filter for periods. The period information is stored in the Period Setup (SY40100) table. So you could insert a column F and add the columns replicator above the rows replicator and reference that value below the Period ID header.

   

   You also have to add a filter for the period to the GL function returning budgeted amounts. You can filter the Start Period and End Period arguments by the same period number to get just the budget for that period.

   

   Since periods are being replicated across columns, the unique key (period number) must appear in the column where the viewer data will be stored, in this case column H, cell H6. Then you must type "Key" in column A in the cell that intersects "Data" with the unique key value, in this case cell A6.

   

   Since you can have multiple unique keys to identify your data, each key must have a name. In this case, you can name the key "Period ID", which you can type into cell B6. You must also type "Keyname" into cell B1 so that the key name appears at the intersection of "Keyname" (column B) and "Key" (row 6).  Finally, row 6 can be hidden since it is not necessary to view it in Report Mode.

   

   Now if you run this report, you will see a new set of green columns for each period, and any information the user types into the Projected Budget columns will be stored for each period.

   

3.  SQL Example (using Access Northwind)

   Let's say you want to insert another rows replicator into your report to break down each category by product. You could insert a column D and return rows for products like the following.

   

   You also have to change the filter for the sales totals to sum by product instead of category.

   

   Since you are returning a unique list of product names for each category, the product name field will serve as the second key. Since Products are also being replicated down rows, you must type "Key" in row 1 in the cell that intersects "Data" with the product names, in this case cell D1.

   

   Notice that since "Keyname" in cell A4 already intersects with "Key" from cell D1 at cell D4, "Product" is the name of the second key for the stored viewer data. Now if you run this report you will see a list of products for each category with stored data for projected sales. Notice that if you edit the data in column F under Projected Sales and re-run the report, any changes you make will remain.