Applying Data Security with Account Schedules

Account Schedules use aggregated data in a matrix layout, and data may be presented from different tables depending upon how the account schedule layout is designed. Data Security filters can be applied using the usual setup with the underlying tables, like G/L Account and G/L Entry. This will allow the drilldown forms/pages to be filtered correctly. However, the presentation of Account Schedule Lines and calculated totals cannot be filtered with the standard setup and custom code would be required. This document will attempt to explain what can be done with minimal effort and what cannot be done without considerable development effort.

The following tables are utilized by Account Schedule Overview and can be added to Source Table Setups.

After adding any new tables to Source Table Setups, remember to Implement Changes in Code. For detailed steps see the following FAQ.

How To Add Additional Tables to Source Table Setup in Field Level, Actions and Data Security

Next, Data Security codes can be set up for those tables specified in Source Table Setups. For this example we will exclude all Revenue accounts, so we can filter on "No." in G/L Account and on "G/L Account No." in G/L Entry, G/L Budget Entry, etc.

If using G/L Budgets and/or Analysis Views, those associated objects would need to be set up as the above with matching filters on the G/L Account No. field.
See the following How-To for additional detail regarding Data Security Codes

Field Level and Data Security: Applying Data Security with Account Schedules

With only the above setup in place, revenue accounts will be filtered out in any lookups or drilldowns accessed from Account Schedules.

For example

In the above screen shot, the revenue accounts are filtered out of the lookup. However, the Totaling field is a text field in the Acc. Schedule Line table and can contain column formulas, ranges of accounts, etc. These values are not easily filtered using Data Security setup and would likely not have the intended effect.
On Account Schedule Overview, the rows for the revenue accounts still show up, but the drilldown will have the filter correctly applied as shown below.

The other problematic area pertains to the calculated totals on the Overview. Notice above that the total is not affected by the filter. These values are calculated using CALCSUMS in codeunit 8 - AccSchedManagement. As will be shown in the following example, it is possible set the Data Security filters via code, but an error will occur if a non-key field is used in the Data Security filter, because CALCSUMS requires that the key being used contains the fields in the filter. This will become clearer in the example presented below.

Using our example of excluding revenue accounts, it is possible to add a few minor code changes and have the totals calculated correctly, filtering out those accounts.

It is important to note that this example works because G/L Account No. is a key field, so the CALCSUMS works with the current key being used.

A few changes need to be made in Codeunit 8 - AccSchedManagement to apply the filters.

--- Before ----
MonthText11@1480003 : TextConst 'ENU=November;ESM=Noviembre;FRC=Novembre;ENC=November';
MonthText12@1480002 : TextConst 'ENU=December;ESM=Diciembre;FRC=D�cembre;ENC=December';

PROCEDURE SetParameters@1020000(NewNegativeAmounts@1020000 : Integer;NewNegativePercents@1020001 : Integer);
--- After -----
MonthText11@1480003 : TextConst 'ENU=November;ESM=Noviembre;FRC=Novembre;ENC=November';
MonthText12@1480002 : TextConst 'ENU=December;ESM=Diciembre;FRC=D�cembre;ENC=December';
ESFLADSSetFilters@1000000000 : Codeunit 14123812;

PROCEDURE SetParameters@1020000(NewNegativeAmounts@1020000 : Integer;NewNegativePercents@1020001 : Integer);
---------------

LOCAL PROCEDURE CalcCellValue
--- Before ----
END ELSE BEGIN
AccSchedLine.COPYFILTERS(AccountScheduleLine);
SetGLAccRowFilters(GLAcc,AccSchedLine);
SetGLAccColumnFilters(GLAcc,AccSchedLine,ColumnLayout);
--- After -----
END ELSE BEGIN
AccSchedLine.COPYFILTERS(AccountScheduleLine);
// >> ES Filter Account Schedules
ESFLADSSetFilters.Filter15(GLAcc,8,0);
// << ES Filter Account Schedules
SetGLAccRowFilters(GLAcc,AccSchedLine);
SetGLAccColumnFilters(GLAcc,AccSchedLine,ColumnLayout);
---------------

LOCAL PROCEDURE CalcGLAcc
--- Before ----
ELSE
SETCURRENTKEY("G/L Account No.","Posting Date");
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
--- After -----
ELSE
SETCURRENTKEY("G/L Account No.","Posting Date");
// >> ES Filter Account Schedules
ESFLADSSetFilters.Filter17(GLEntry,8,0);
// << ES Filter Account Schedules
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
---------------

LOCAL PROCEDURE CalcGLAcc
--- Before ----
ELSE
WITH AnalysisViewEntry DO BEGIN
SETRANGE("Analysis View Code",AccSchedName."Analysis View Name");
IF GLAcc.Totaling = '' THEN
--- After -----
ELSE
WITH AnalysisViewEntry DO BEGIN
// >> ES Filter Account Schedules
ESFLADSSetFilters.Filter365(AnalysisViewEntry,8,0);
// << ES Filter Account Schedules
SETRANGE("Analysis View Code",AccSchedName."Analysis View Name");
IF GLAcc.Totaling = '' THEN
---------------

LOCAL PROCEDURE CalcGLAcc
--- Before ----
ELSE
SETCURRENTKEY("Budget Name","G/L Account No.",Date);
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
--- After -----
ELSE
SETCURRENTKEY("Budget Name","G/L Account No.",Date);
// >> ES Filter Account Schedules
ESFLADSSetFilters.Filter96(GLBudgEntry,8,0);
// << ES Filter Account Schedules
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
---------------

LOCAL PROCEDURE CalcGLAcc
--- Before ----
ELSE
WITH AnalysisViewBudgetEntry DO BEGIN
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
--- After -----
ELSE
WITH AnalysisViewBudgetEntry DO BEGIN
// >> ES Filter Account Schedules
ESFLADSSetFilters.Filter366(AnalysisViewBudgetEntry,8,0);
// << ES Filter Account Schedules
IF GLAcc.Totaling = '' THEN
SETRANGE("G/L Account No.",GLAcc."No.")
---------------

Further details on required changes

1: Create a global variable "ESFLADSSetFilters" of the type Codeunit 14123812.
2: Add the code in the specified sections above. Each table set up in Source Table Setup is going to have a function like "Filter15" that applies filters to variables of the table .
The function ESFLADSSetFilters.Filterxx takes 3 parameters
1: The record variable the filter is going to be applied to
2: The object type , Form=2 and Page=8.
3: The object ID.
Items 2 and 3 will match the setup in the Data Security Code. In our example, table 15 has Data Security code EXREV for Page 0. The function call is ESFLADSSetFilters.Filter15(GLAcc,8,0);
GLAcc = record variable to filter
8 = Object type PAGE
0 = Page 0 Object ID

Again, we are using a Data Security Code which filters out Revenue accounts.

If using G/L Budgets and/or Analysis Views those associated objects would need to be set up as the above with matching filters on the G/L Account No. field.

Of course, remember to also assign the Data Security code to the users and use Copy Data to copy the setup from the Easy Security company to the live/production companies.

For more information see the following

How To Create User Security Setup

Note: Easy Security version 40.09 is required in order to specify the FilterGroup to be used for the Data Security filters.

In Setup, select a Filtergroup that does not conflict with others used by base NAV or any existing customizations. This is used by Field Level and Data Security to ensure that any Data Security filters applied will not be overwritten.

With these changes applied, the same Income Account Schedule shown earlier now has the totals correctly filtered for the revenue accounts.

If the Data Security Code had been using a non-key field, an error similar to the following would occur.

Don't Like

Related resources

Download software from Mergetool.com