Use Stored User Data in Reports
Overview
Suppose you want to allow Report Viewers to create and edit data on reports. Normally any changes made in Report mode will be lost when you go to Jet | Report to re-run the report. However, with the stored user data feature, it is possible to designate sections of a report where users may enter and change data which will be stored and kept by Jet when the report is run.
-
Dynamics NAV Cronus Example
Suppose you wanted to create a simple list of G/L expense accounts with current budget information. You could do that like the following.
Say you wanted to create new budget amounts based on the current amounts with a projected 10% growth in each. You could add a column to do that like the following.
However, what if you didn't want to increase budgets by 10% all across the board, but wanted to play with the numbers a little and increase some by less and some by more. Normally, any changes of this kind made in report mode would be lost when the report was re-run. However, with the stored viewer data feature they can be kept. Start out by typing "Data" in column A left of the cell(s) where users will enter stored data and row 1 above the cell(s) where users will enter stored data, in this case G1 and A8.
This tells Jet what cell(s) to store data from. But what if you decided to change the filters to bring back a different list of G/L accounts? Then the budgets stored might not correctly match the G/L accounts. Because of this, Jet must have a unique key for each piece of data stored. In this case, the G/L account number uniquely identifies the table records, so it can be used as the unique key. Type "Key" in column A or row 1, depending on which intersects the key values with the "Data" keyword. In this case, type "Key" in cell D1.
In some cases, more than one key may be necessary in order to uniquely identify the data items. For example, if you were creating budgets by department, you might need a key for the account number and a key for the department in order to uniquely identify your data values. Because of this, each key needs a name. In this example, "No.", the name of the field in the G/L Account table, will serve for the key name. Type "Keyname" in column A or row 1, depending on which intersects the key name with the "Key" keyword. In this case, type "Keyname" in cell A7.
Now when you run the report, you can tweak the values under Projected Budget in Report mode (as seen below) and the data will be stored when you run the report again or enter design mode.
Finally, you want to be sure to have Auto+Hide+Values in cell A1 so that Report Viewers can use the report. You can also add Hide and Fit keywords to row 1 like the following.
Notice that even if you add options for filters that change the G/L accounts returns, the projected budgets entered by the user will stay with the correct account.
-
Dynamics GP Fabrikam Example
Suppose you wanted to create a list of G/L accounts for a specific Great Plains budget. You could do that like the following.
Say you wanted to create new budget amounts based on the current amounts with a projected 10% growth in each. You could add a column to do that like the following.
However, what if you didn't want to increase budgets by 10% all across the board, but wanted to play with the numbers a little and increase some by less and some by more. Normally, any changes of this kind made in report mode would be lost when the report was re-run. However, with the stored viewer data feature they can be kept. Start out by typing "Data" in column A left of the cell(s) where users will enter stored data and row 1 above the cell(s) where users will enter stored data, in this case G1 and A8.
This tells Jet what cell(s) to store data from. But what if you decided to change the filter to bring back accounts from a different budget? Then the budgets stored might not correctly match the G/L accounts. Because of this, Jet must have a unique key for each piece of data stored. In this case, the G/L account number uniquely identifies the table records, so it can be used as the unique key. Type "Key" in column A or row 1, depending on which intersects the key values with the "Data" keyword. In this case, type "Key" in cell D1.
In some cases, more than one key may be necessary in order to uniquely identify the data items. For example, if you were creating budgets by department, you might need a key for the account number and a key for the department in order to uniquely identify your data values. Because of this, each key needs a name. In this example, "No.", the name of the field in the G/L Account table, will serve for the key name. Type "Keyname" in column A or row 1, depending on which intersects the key name with the "Key" keyword. In this case, type "Keyname" in cell A7.
Now when you run the report, you can tweak the values under Projected Budget in Report mode (as seen below) and the data will be stored when you run the report again or enter design mode.
Finally, you want to be sure to have Auto+Hide+Values in cell A1 so that Report Viewers can use the report. You can also add Hide and Fit keywords to row 1 like the following.
Below is the final output you will see after adding the hide and fit keywords.
-
SQLExample (using Access Northwind)
Suppose you wanted to create a report of sales amounts by category. You could do that like the following.
Then say you wanted to add a column for projected sales with a 10% increase in each category. You could add a column to do that like the following.
However, what if you don't want to increase all the projected sales by 10% but wanted to play with the numbers a bit for some for some categories after running the report? Normally, any changes made in report mode are lost when the report is re-run. However, with the stored viewer data feature they can be kept. Start out by typing "Data" in column A and row 1 above and left of where you want viewers to be able to enter stored data, in this case A5 and E1.
This tells Jet what cell(s) to store data from. But what if you decided to change the filters to bring back a different list of categories? Then the projected sales data stored might not correctly match the categories. Because of this, Jet must have a unique key for each piece of data stored. In this case, since we are retrieving a unique list of categories, the category can be used as the unique key. Type "Key" in column A or row 1, depending on which intersects the key values with the "Data" keyword. In this case, type "Key" in cell C1.
In some cases, more than one key may be necessary in order to uniquely identify the data items. For example, if you wanted to show individual products for each category, you might need a key for the category and a key for the product in order to uniquely identify your data values. Because of this, each key needs a name. In this example, "Categories", the title above the field, will serve for the key name. Type "Keyname" in column A or row 1, depending on which intersects the key name with the "Key" keyword. In this case, type "Keyname" in cell A4.
Finally, you want to be sure to have Auto+Hide+Values in cell A1 so that Report Viewers can use the report. You can also add Hide and Fit keywords to row 1 like the following.
Notice that now whatever values you type into column E under Projected Sales in Report mode will be saved when you re-run the report.
it is not possible for 'Conditional Hide' formulas to include areas containing Stored Viewer Data