Custom Office/Crystal Tips and Tricks:
Adding User Security to Individual MAS 90 / MS 200 G/L Financial Reports

 

By Dan Burleson

Sage MAS 90 / MAS 200 Authorized Consultants Program

 

Adding my Crystal trick security feature will allow specified users to secure individual financial reports. My trick provides security (for the standard MAS 90 / MAS 200 General Ledger Financial Reports) that is not currently available to authenticate a particular financial report.  Example: Currently in MAS 90 / MAS 200, rights can only be assigned to all financial reports or none.  That can be altered by creating a Custom Office User Define Table (UDT) and by using it with minor changes to the General Ledger financial reports in version 4.0 and above.

 

This is a small enhancement, but requires knowledge of Custom Office and Crystal reports formulas and variables. It can be performed to multiple reports in just a few minutes.

 

Overview of Procedure

 

1.                  Step 1: Create a User Defined Report Security Table Using Custom Office

A.     Create a field to hold the titles of financial reports to be secured.

B.     Create a field to hold user logon codes of those permitted to run a particular financial report.

C.     Enter the list of report titles with the user logon codes permitted to run them.

2.                  Step 2: Add to the Financial Report - 3 Formulas and 1 Sub Report Consisting of 1 Formula

A.     Add an empty sub report based on the Report Security table created above.

B.     Add a supplied formula to the sub report detail section’s “suppression conditional” formula area.

C.     Add a supplied formula to the main report header section’s “suppression conditional” formula area.

D.     Add a supplied formula to the Group Selection formula of the main report that enforces security.

E.      Add a supplied formula to the Page Header section that displays an unauthorized message.

3.                  Step3: Set the Option to Print User Logons on Reports

 

A follow-up article will demonstrate how to incorporate additional security based on company code and account groups by expanding on this same technique.

 

Step 1: Create a Report Security Table Using Custom Office

 

  1. Open the Custom Office module.
  2. Select the Main menu.
  3. Select User Defined Field and Table Maintenance program.
  4. Click the button on the right side of the dialog box indicated below (Figure 1).

 

 

 

Figure 1 – Adding a User Defined Table (UDT) to the Common Information module

 

 

  1. Name your table “REPORT_SECURITY” and name the Key Field “SEQUENCE_NUMBER”. (Figure 2).

 

 

                                

 

Figure 2 – Adding the “Report_Security” Common Information User Defined Table

 

 

  1. Add a UDF called “REPORT_TITLE” to your UDT. Note that MAS 90 / MAS 200 will add the prefix “UDF_” to your field names (Figure 3).

 

 

 

Figure 3 – Adding the User UDF to the REPORT_SECURITY UDT

 

 

  1. Add a UDF called “USER” to your UDT (Figure 4).

 

 

 

Figure 4 – Adding the REPORT_TITLE UDF to the REPORT_SECURITY table.

 

  1. Once the above UDF dialog is closed your resulting summary dialog box should be identical to the one below (Figure 5).

 

 

                 

 

Figure 5 – The resulting REPORT_SECURITY table User Defined Fields

 

 

 

  1. Add User and Report data to your UDT by using the Maintain UDT Data program.

    Enter your user login codes and the report titles that they are permitted to run by clicking the icon indicated in Figure 6.

 

 

 

Figure 6 – Adding User and Report Data to the Report Security UDT

 

 

  1. After adding your report titles and permitted users, your table should look similar to the one shown in Figure 7. Values in the Sequence Number field are unused, but must be unique.

 

 

 

Figure 7 – The resulting UDF containing report names with their authorized users

Step 2: Modify the Financial Report

 

In this example, the Income statement will be used, but the same technique can be used for all of the financial reports.

 

A.      Add the Sub Report called “ReportSecurity” to the Report Header

 

  1. Select the Insert menu.
  2. Select the Subreport command.
  3. Choose “Create a subreport with the Report Wizard”.
  4. Specify ReportSecurity and the “New report name:”.

                               

 

 

Figure 8 – Inserting the Sub Report in the Report Header

 

 

When the Report Wizard begins, choose to base the sub report on the CI_UDT_REPORT_SECURITY table (added in step 1) as shown below.

 

 

 

Figure 9 – Base the Sub Report on the CI_UDT_REPORT_SECURITY table

 

Add no fields to the sub report, remove any fields already on the sub report, and remove the “Report Header b” and “Report Footer b” if they existpro. Your report should look similar to the following example:

 

 

 

 

Figure 10 – The Added Sub Report has not fields and Only the Details Section is Unsuppressed

 

It is also possible to save this sub report for re-use with other financial reports by using the “Save Subreport As” command while editing the sub report.

 

B.      Add the Supplied Logic to the Suppression Conditional of the Sub Report Detail section.

 

  1. Select the ReportSecurity tab as shown above.
  2. Select the Reports menu.
  3. Select the Section Expert command.
  4. Select the Details section.
  5. Click the Suppression conditional icon shown below.
  6. Add the logic shown below.

 

The Formula Workshop program will start when the suppression conditional icon is clicked.

 

 

 

Figure 11 – Adding Logic to the Sub Report Details Section Suppression Conditional

 

Enter or copy the following formula in the Formula Workshop as the Suppression Conditional.

 

 

If Shared StringVar strUser = {CI_UDT_REPORT_SECURITY.UDF_USER}

   then

        (

         If Not({CI_UDT_REPORT_SECURITY.UDF_REPORT_TITLE} in Shared StringVar array strReportTitles)

            then (

                 Redim Preserve strReportTitles[Ubound(strReportTitles)+1];

                 strReportTitles[Ubound(strReportTitles)] := {CI_UDT_REPORT_SECURITY.UDF_REPORT_TITLE}

                  );

         );

True //
 

Your formula should look identical to the following in the Formula Workshop dialog box.

 

 

 

Figure 12 – Adding Logic to the Sub Report Details Section Suppression Conditional

 

  1. Save and close the Formula Workshop.
  2. Click OK to the Section Expert.

 

 

C.      Add the Supplied Logic to the Suppression Conditional of the Main Report’s Report Header

 

Using steps similar to those above, add the following logic to the Suppression Conditional of the main report’s Report Header.

1.      Open the Section Expert from the main report.

2.      Turn off the check box for Suppress Section.

3.      Change the syntax to Crystal Syntax if Basic Syntax is selected by default.

 

Shared StringVar strUser := {@PrintedByUser};

False
 

 


Your formula should look identical to the following figure in the Formula Workshop dialog box.

 

 

 

Figure 13 – Adding Logic to the Report Header Section Suppression Conditional

 

 

D.      Add the Supplied Logic to the Group Selection formula of the Main Report

 

Any record selection logic added to a report that is initiated from MAS 90 / MAS 200 will be ignored, but you can add group selection logic that will not be ignored.  The logic added in this step will not be used to eliminate individual groups, since nothing in the formula changes throughout the report.  If the current user has not been permitted to run the report based on the Report Security table, it is used to eliminate all groups. 

 

The string array “strReportTitles” is only filled with the titles of reports that the current user is permitted to run, and the logic below tests these against the title of the current report.

 

{GL_FinancialReportHeaderWrk.ReportTitle} in Shared StringVar array strReportTitles
 
 

 


Your formula should be identical to the following in the Formula Workshop dialog box for Group Selection.

 

 

 

 

Figure 14 – Adding Logic to the Report’s Group Selection Formula

 

E.      Create a Formula with the Supplied Logic that Informs the User When Not Authorized

 

When an unauthorized user runs the report, they should not be presented with a blank report, but rather with a message that informs them of the reason the report is blank.  This is done with a formula called “Unauthorized” and contains the following logic that tests whether any report titles were found to be permitted for the current user:

if Ubound(Shared StringVar Array strReportTitles) = 0

    then "Unauthorized user: " & Shared StringVar strUser

    else ""
 
 

 

 

 


Your formula should be identical to the following in the Formula Workshop dialog box for the Unauthorized formula.

 

 

 

Figure 15 – Creating the @Unauthorized Formula Logic Using Crystal Syntax

 

 

Add the @Unauthorized formula to the Financial Statement main report in the Page Header section “e” (PHe) and make it font style bold as shown below.

 

 

 

Figure 16 – Inserting the @Unauthorized Formula into the Main Report

Step 3: Setting a Required Company Maintenance Option

 

1.      Open the Company Maintenance program from within the Library Master module.

2.      Select the Preferences tab.

3.      Check the “Print User Logon Reports” check box.

4.      Repeat this set for all companies for which financial reports will be printed.

 

 

 

Figure 17 – Setting the Attribute that Will Provoke the User Logon to be Passed to the Report

 

 

NOTICE: This article represents copyrighted material and may only be reproduced in whole for personal or classroom use. It may not be edited, altered, or otherwise modified, except with the express permission of the author.

 

Dan Burleson is an independent Sage MAS 90 / MAS 200 Authorized Consultant located in Philomath, Oregon. He may be reached at info@connexsoftware.com or at (541) 754-7400.

 

© Connex Software. All Rights Reserved