Creating Financial Reports in General Ledger Version

Contents

Overview

A financial report is a document that captures, structures, and summarizes general ledger account data for a given purpose. Examples of financial reports are income statements, cash flow statements, balance sheets, summary trial balance sheets, and other documents created for a hospital's particular needs. You can create, edit, and copy financial report definitions, run and output reports in several formats, and send the output to a printer.

This topic is a reference guide to Financial Report Writing. This topic contains several "how-to" sections, but it is not intended as primary training material. This topic has a companion document Financial Reports - Advanced Topics which describes stored row/column definitions, repeating a report run for different departments, overriding a defined account mask, and copying a report definition.

Security

Users of the Financial Report Writer must have the role object General Ledger Report Writer set to Create as a minimum. The setting must be All if a user is both creating and deleting reports. The users' data profiles must include the appropriate organizations and departments.

ProSheet security has been aligned with users’ Data Profile settings.

Data Profile Security for the Financial Report Writer and ProSheet

Data Profile settings for allowed organizations and departments control the account data that you can access using the application's account inquiry, the Financial Report Writer, and ProSheet’s account retrieval and downloading functions. You can turn off data profile checking. Instructions are in the ProSheet Security section below.

You need to be aware of the following:
The organizations/departments whose account data you retrieve using Account Inquiry, the Financial Report Writer, custom reports, or ProSheet must all be included in your Data Profile. If organizations/departments are not included in your Data Profile…
- The downloaded data will be different than downloaded data that included the missing organizations/departments.
- The results of any calculations will be different.

This security also applies to account masks, and to account selection templates called in to ProSheet, account inquiry, or used with the Financial Report Writer. Accounts identified by a mask are not available if the organizations/departments “wildcarded” in the mask are not in your Data Profile.

You can restrict financial reports to specific organizations. You can also restrict account selection templates (see Using Account Templates) to particular organizations. Restricted account selection templates cannot be used to create custom financial reports, account inquiries, row/column definitions, and ProSheet reports by users who are not authorized for the relevant organizations (in Data Profile settings).

Make sure that your Data Profile contains the organizations/departments whose data you need to work with. If you need to have organizations/departments added to your Data Profile, contact your site’s System Administrator. For questions or problems, the  Premier Solutions center is available.

Restrict a Financial Report by Organization

By (optionally) restricting financial reports by organization, you can limit access to them based on users' data profiles.

When financial reports or selection templates have been restricted by organization, reports can only be displayed/edited by users with data profile access to at least one of the restricted organizations. This feature helps to avoid one organization accidentally reading or editing another's financial reports.

Setup: Financial Report Restriction by Organization

Restricting a financial report by organization is made up of two main sets of tasks:

• Selecting the Restrict Financial Reports/Selection Templates by Org field in System Values.
• Identifying restricted organizations for one or more financial reports.

To set the System Values field:

1. From the main Contents, select Work in Administration > Administration > System Values.
   The System Values list appears.
   
2. Select Menu > Edit.
   The options panel appears.
3. Click the Other tab.
4. Check the box next to Restrict Financial Reports/Selection Templates by Org (Figure 1) to select it.

Figure 1 - The System Values "Other" Panel with the Restrict Financial Reports Options

The initial setting for the Restrict Financial Reports... field is "off" — financial reports are not restricted. Once you enable the option, a warning message appears if a user tries to disable it. The warning states that existing restrictions will not be enforced when the flag is disabled.

• Confirm the action by clicking either OK or Cancel.

Note: The ability to change the Restrict Financial Reports... field on the System Values Other tab is controlled by the role object "GLFINRptWtr". This role object must be set to All.

When the system value Restrict Financial Reports/Selection Templates by Org field is enabled:

• The menu option Restricted by Org is available on the list of Financial Report Definitions.
• On the list of Financial Report Definitions, the sortable column Restricted by Org appears (Figure 2). This field is audited.

Figure 2 - List of Financial Reports

The Restrict By Org menu option opens a list of organizations already available to the user based on his/her data profile. From the list, you can select organizations that have access to the financial report. For example: In Figure 3, clicking the box next to 21620 – St. Anne's Clinics would only allow users with St. Anne's Clinics in their Data Profiles to access the report "Income Statement for St. Anne's Clinics." In contrast, a user logged in to ERP with the Current Settings of Organization set to 21610 would not be able to access the report because the report does not include 21610 as one of the restricted organizations.

• When a user has a restricted organization in their data profile, the associated financial report is available to the user even if they are not logged into that specific organization.
• If a report is not available to a user because the user does not have the restricted organization in his/her data profile, the user will not even see the report in the list of Financial Report Definitions.

Figure 3 - Organizations for Selection as Restricted

To identify the organization(s) for restriction:

1. Open the list of financial reports (GL main Contents > Financial Report Writer > Financial Report Definitions), shown in Figure 2.
2. Locate the report or reports of interest on the list.
3. Click Menu > Restrict by Org next to the report of interest.
   A selection list of organizations appears (Figure 2).
4. Click the box in the Select column next to the organization(s) to set restrictions (Figure 3). To select all organizations, click the down arrow next to Select Action and choose Select All.
   • You can also go back and Unselect all of the organizations.
   • Update List refreshes the list with your selections (deselections).
5. Select Update List.
6. Click Submit. The restrictions are in place for the report.

Important: If you add additional organizations to a data profile, you must use this feature to restrict any of the necessary added organizations, even if you used Select All in your initial setup. Select All only marks the organizations that are currently in the target data profile when you use this feature. Adding new organizations to the Data Profile turns the restriction off.

Note on Consolidated Financial Reports: You can also restrict account selection templates by organization. Any consolidated financial report based on a restricted account selection template is only available to users with the organizations allowed by the restriction in their Data Profiles.

General Ledger Reporting Features

General Ledger provides several report-generating tools accessible from the main Contents:

• The Interactive Financial Report Writer. This tool is available from the Financial Reports menu (see Figure 4).
• The ProSheet Excel spreadsheet add-in.
  • ProSheet links directly to your general ledger data. With ProSheet, you can access data from your chart of accounts in Excel to create reports in spreadsheet form.
  • You can download ProSheet from the Financial Reports menu (Figure 4).



Figure 4 - General Ledger Main Contents with Financial Reports Definition List

• The pre-defined reports in the list of Report Definitions (Figure 5, red arrow and box).
  These report definitions collect many types of data, including materials management and accounts payable data.



Figure 5 - Report Definitions

Note: This topic describes how to use the Interactive Financial Report Writer and ProSheet to generate reports.
See Creating Custom Reports for information about report definitions supplied by the system (Figure 5) and how to customize them.

Which should I use: ProSheet or the Financial Report Writer?

The method that you use to create a financial report depends on the type and quantity of data in the report, and on how the report is used.

ERP Financials use three types of report writers:

1. Financial Report Writer
2. ProSheet
3. Ad-Hoc Report Writer

• The Financial Report Writer is useful for reports such as statements of cash flows, balance sheets, income statements, tax schedules, and any reports that contain large amounts of data.
  - Another good use of the Financial Report Writer is generating departmental reports. You can set up a report definition that covers the same categories of data for all your departments. Users running the report enter a department number (and perhaps a period and year), and get data for only their department -- other departments' data is protected.
  - Financial report definitions output as Adobe .pdf files can be formatted to adjust the font (typeface) size and style, column width, and other display elements, giving you ways to make a report more compact.
• ProSheet is good for summary-type reports and for presentation reports that you enhance visually with graphics, charts, and images -- elements available with Excel.
• The Ad Hoc report writer is used for all kinds of reports, not just financial reports. Access it from the Reports > My Report Definitions menu.

How to choose which report writer to use when building a new report?

Table 1 has some questions that will guide you in deciding which report writer to use.

Table 1 - Report Writer Decision Questions

The Financial Report Writer contains an interactive design panel -- the Report Writer Editor -- that lets you build and view the contents of your report. You can create row and column definitions on the fly using the Report Writer Editor. Also, you can establish and save standard definitions for row and column contents, and use the definitions over and over in different reports. Similarly, you can create standard account templates and masks to define particular accounts that you want to report on, and use the templates in various reports.    

Financial Report Writing Tips

1. Use control totals.
2. Build highly summarized balancing reports using the account class/type filters.
3. Reuse row and column definitions in the Financial Report Writer when possible.
4. Centralize and protect your ProSheet reports on a secure server.
5. Share the custom reports you build with colleagues, during organization and department meetings, for example.
6. When you build Ad Hoc reports, consider building two: save one as a .pdf file, and the other as an Excel file.
7. Consider giving ProSheet to your organization's financial staff.
8. Consider giving ProSheet to your company's financial officers.

Prerequisites

To get the most out of this topic, you need to be familiar with your hospital's Chart of Accounts and the various financial reporting needs of different organizations at your hospital. If you have ever written custom report definitions in the system for other areas, such as accounts payable, that experience will be helpful.

For ProSheet, you will need a slightly-better-than-basic understanding of Excel. This topic reviews some Excel capabilities such as absolute and relative referencing, but does not attempt to teach Excel.

Ideally, you should have completed a course in Financial Report Writing during General Ledger implementation at your hospital.

Security Settings

Users of the Financial Report Writer must have the role object General Ledger Report Writer set to Create as a minimum. The setting must be All if a user is both creating and deleting reports. The users' data profiles must include the appropriate organizations and departments.

About General Ledger Accounts

Account Codes and Account Code Masks

The Chart of Accounts for a hospital is set up when General Ledger is implemented.

You can add accounts, change existing accounts, and delete accounts using the GL Account Import feature.

To view the chart of accounts:

• From the General Ledger main Contents, select Account Management > GL Accounts.
  (See Figure 6.)

Figure 6 - Part of a Hospital's Chart of Accounts

Masks

Account codes have a segmented structure. For each medical center, the account codes' segments are defined in the System Values. A pattern for an account code or group of account codes is a mask. A more detailed discussion of account masks is in the short topic "Using an Account Code Mask."

Figure 7 is an example of an overall account mask (marked in red) for a medical center. This medical center's account codes have four segments. The first segment contains three numbers (or letters), and each of the other segments contains four. This medical associates the first segment with an organization (which could be a hospital, or other facility) and the second segment with a department.

Note: Figure 7 displays a record that is created during implementation. The figure is here only for general information -- you cannot edit or change this System Value record.

Figure 7 - System Values Record for a Sites Account Code Mask

Date Values for Multiple Organizations Selected with a Mask

You can use wildcards in a mask to have the application select multiple organizations for a report. For example, 00?-1234-5678 would select organizations 001, 002, 003, and any others in that pattern. When a medical center's organizations use different calendars, and you use a mask to specify the organization account segment, the period beginning/ending date values are always for the first organization that the application retrieves.

Detail Accounts and Account Summary Groups

Detail accounts are normal accounts set up to track expenses, assets, liabilities, and so on. For example, a department's salary expense account is a detail account.

Summary group accounts ("summary accounts") summarize the data from a collection of detail accounts. The collection is identified in a summary account code mask. The mask may contain numeric or alphabetical values, wildcard characters, and operators (such as ":" to indicate a range). For example...

Suppose each department in a hospital's organization 001 has a materials expense account code in the form: 001 [dept. no] 2010 2710.
The account code is the same for every department, except for the department number.

- Department 6300 would have the account code 001 6300 2010 2710 
- Department 6700 would have the code 001 6700 2010 2710,

and so on with other departments.

The mask for the summary group of materials expense accounts for all departments departments is: 001 ???? 2010 2070.

The ???? are wildcard characters that the system interprets as "any value." This mask creates a summary group account that collects the materials expense accounts' data for all departments. (You can create and use account masks with wildcards and other operators when you define financial reports. See Using an Account Code Mask.)

When a new account is added to the Chart of Accounts, the system evaluates all summary groups to determine which need rebuilding. If the new account matches a summary group's mask it will be included. The summary account rebuild process runs each night as part of the regular scheduled maintenance job. Alternately, General Ledger users can run the summary account rebuild process themselves, if needed.

To run the summary account rebuild process:

From the General Ledger table of contents, select Financial Reports > Submit Rebuild for New Accounts.

Note: The Financial Report Writer and ProSheet will not run during the summary account rebuild processing.

Generating Reports with the Financial Report Writer

From the list of Financial Report Definitions (Figure 5), you can edit and run any report. The next few paragraphs take a look at report output to give you a context for how report definitions are set up. A detailed discussion on interpreting output and using drill down is in the section Running and Interpreting Output.

Suggestion: If you are new to writing financial report definitions, you may wish to create your first report by copying and then editing an existing report (if any are available). The instructions for adding rows to a new report are the same as the instructions for editing an existing row; similarly, for columns. Copying and editing a report may help you "get your feet wet" without starting completely from scratch. The short version of the copy process is below. For detailed information, see Copying a Financial Report Definition Created with the Report Writer.

To copy an existing financial report:

1. On the Financial Report Definitions list next to the report, select Menu > Copy.
   The GL Report panel appears.
2. On the GL Report panel, you can change the name or other general elements of the report, or click Save and Submit to make changes later.
   The copy utility does not copy the row and column definitions; so, you need to either define new rows and columns, or select stored definitions. See the detailed instructions for copying reports.

To view financial report output:

1. From the General Ledger main Contents, select Financial Report Writer > Financial Report Definitions.
   The system displays the list of existing definitions that you can access (Figure 8).

Figure 8 - Accessing a Financial Report's Output

2. Click Menu > Completed Reports next to the report definition (Figure 8). If the financial report has run, the output is listed with the most recent output first.
3. Click View Report. The report is displayed.

The hospital that owns the report definition "Training Expenses by Type, Department " (Figure 8) has several departments with significant training expenses. Each department has three training expense accounts. The account code mask for this hospital has the structure:

AAA BBB CC DDDD

where AAA is the organization number, BBB is a sub account code, CC is the training type account, and DDDD is the department number;
e.g., 001-017-11-6100 contains conference travel (11) expenses for department 6100 in Organization 001.

Figure 9 is output for "Training Expenses by Type, Department " for year 2022, period 7 for Organization 001. Figure 9 shows normal output; that is, the report is formatted for viewing on a computer monitor. You can output reports as .pdf files, text files, or spreadsheets as well.

Figure 9 - Output for a Financial Report on Training Costs for Several Departments

This report displays travel expenses by the type of expense for each department, with a total for all departments. On the bottom of the report, totals by department for all types of travel expenses appear.

To create a financial report definition, from the Financial Reports list:

1. Click New.
   The GL Report Definition panel appears (Figure 10).

Figure 10 - GL Report Definition Panel

4. Click Submit when you are finished entering information on this panel's tabs. The Financial Report Definition Editor appears.

Using the Financial Report Definition Editor

The Financial Report Definition Editor lets you create and edit report rows and columns, write the report title, enter column labels, and specify other elements of a report. With the Editor, you can see a report definition evolve as you work with it.

Figures 11 and Figure 11A each display the Editor panel completed for the financial report: "Training Expenses by Type, Department." Figure 11 shows the report without annotations. Figure 11A is annotated. The paragraphs below discuss how to create each part of a financial report definition using the Editor.

Figure 11 - Financial Report Editor: Training Expenses Report Definition

Figure 11A - Financial Report Editor: Training Expense Report Definition- Annotated

Each set of instructions below is numbered to match the same numbered location on Figure 11A.

Step 1. Edit a report's general characteristics

Once you are working on a report definition, you may wish to make changes or specify additional characteristics of the report, such as the output format, the type face, and so on.

To change the report name, output format, type face, base year, etc.:

1. At the very top of the report definition in the Editor, next to Financial Report - "report-name", click Edit. (See Figure 11A, 1)
   The GL Report Definition panel appears (Figure 10).
   
   
2. Enter the new information in the appropriate fields.
   The tabbed fields are discussed in the Advanced Notes on GL Report Definition Tabbed Panels box.

Note: If you wish to change the output type of the report; for example, changing from Normal to Spreadsheet, this GL Report panel is where you make the change.
- Click the Output tab to change the output type.


3. Click Save and Submit when you are finished.

Step 2. Enter a Report Header or Title

Enter a title for your report by clicking in one of the cells under Report Headers (see Figure 11A, 2) Six cells are available for title information, so that you can create a title in the center top of the report, a sub-title in the center as well, and right and left headers, if you wish. You can also use run-time substitution text to pull in values such as the current period and year, the date, an organization or department number, and others.

To enter report titles:

1. Click in a Report Header cell on the editor (Figure 11A, 2).
   The Report Title panel appears.
2. Enter the header or title in the Title field.
   - Use substitution text as needed.
3. If you wish, you can specify a type (font) size and style, how the title should align on the page, bold, italic, or underline.
4. When you are finished, click Save and Submit.
5. Refresh the report editor, and the title appears.

Run-Time Substitution Text

Run-time substitution text inserts information -- such as the current date -- in a report title, footer, or row/column header. For example, in Figure 11 and Figure 11A, the text for the report title is:

Training Expenses by Type, Department, @Period Period, @Year

The substitution codes @Period and @Year generate the current period number (7 with "th") and the year (2022) when the report runs:

Training Expenses by Type, Department. 7th Period, 2022

Step 3. Specify Columns

Using the report Editor, you identify new columns for a report by entering column definitions.
(You can also specify a stored column definition on the initial GL Report Definition panel.)

The instructions below are for the Report Editor.

To enter new column definitions for a financial report:

1. Click New next to the word Columns. (see Figure 11A, 3)
   - To edit an existing column, click the column letter.
   
   The Report Col Detail panel appears. Figure 12 is the panel for the Training report for Column D . The small, inner diagram shows the fields for the Balance Type drop box.

Figure 12 - Financial Report Column Detail Panel - For Column D in the Training Report

2. Complete the fields in the panel, as described below.

Data Type

When the Type field is GL Data, Data Type indicates whether the amounts in the column are derived from a Budget or from Actual data.

Budget Type

If the Data Type is Budget, the Budget Type field opens for you to identify the type of budget. Select the budget type used at your site for
data in the column. (This value is used in Column F of the "Training Expense Report" - Figure 11 and Figure 11A) Options are:

Budget 1 (Operating), Budget 2 (Flexed), Budget 3, Budget 4, Budget 5.

An account for a department can be included only once for budgets of the same type for the same year.

Formula

If the Type field is Formula, cells in the column contain a formula. The Formula field opens for you to insert a formula. See the discussion of formulas for row definitions.

Column Precedence

Column definitions take precedence over row definitions. So, for example, if you create a column definition that specifies three decimal places (0.000) for its data, you cannot create an intersecting row that has only one decimal place (0.0). The column setting would prevail for the intersecting row. You can, however, override a column definition with a row definition.

Step 4. Enter Column Headers

Entering a label for a column is similar to entering a report title. As you create columns, the financial report Editor builds a place for you to enter one or two headers. In Figure 11A, the title headers are: Description, Account Code, Period Activity, YTD Activity, and YTD Budget.

To enter column headers:

1. Click in a Column Header cell on the editor (Figure 11A, 4).
   The Column Title panel appears.
2. Enter the header or title in the Title field.
   - Use substitution text as needed.
3. If you wish, you can specify a type (font) size and how the title should align on the page, bold, italic, or underline.
4. When you are finished, click Save and Submit.

5. Refresh the report editor by clicking the Refresh icon in the upper left corner to display the headers you entered over the columns.

Step 5. Specify Rows

Rows typically contain either detail or summary account data, formulas, text, and formatting elements such as underlines and page breaks. In the report editor, you identify new rows for a report by entering row definitions. (You can also specify an established row definition on the initial GL Report panel.)

To define new rows for a report:

1. Create new rows by clicking New next to the word "Rows." (Figure 11A, 5).
   - To edit an existing row, click the row number.
   
   The Report Row Detail edit panel appears. Figure 13 is the panel for the Training report for Row 100. The small, inner diagram shows the fields for the Row Type drop box.
2. Complete the fields in the panel, as described below.
   

Figure 13 - Financial Report Row Detail Panel - For Row 100 in the Training Report

Ordinal POs

Enter the row number for the row in the Ordinal POs field.
Hint: You may wish to number rows in increments of ten or 100 (or some other value). This approach lets you insert rows later by specifying a row number smaller than each increment. For example, if rows are numbered 100, 200, 300, you can later insert a row 110, 150, or 275, etc.

Row Type

Select the row type from the drop box. Rows can contain data, formulas, text, or formatting (line, space, page break).

Concept: A row built in the Editor doesn't necessarily correspond to a single output row. For rows, what you are really defining is "report elements in the horizontal direction." You could create a single row definition containing an account code mask that pulls in many, many accounts. This situation is common, and occurs in the Training Expense report.

In Figure 13 (blue arrow), for example, notice that the Mask for the Department segment contains a range of values that indicate multiple departments' accounts. Physical rows for the accounts appear on the report output (Figure 9), even though only a single "row" is defined in the Editor.

Keep this concept in mind since it can also come into play for formula row entries.

• If the row will contain data, the options are:

Detail - A detail row shows information for one or more individual accounts. You can specify the account(s) using a template or a mask.

Summary - A summary row shows information for one or more summary accounts. You can specify the account(s) using a template or a mask.

• A row can contain a formula. In this case, select Formula.

Formula - The values in the cell are the result of applying a formula to account data. The rows containing the account data are specified in the formula. Enter a formula in this field. See Using Formulas.

• For a row that will contain text or format information, select one of these options:

Text - The row contains text. (See the discussion about Text rows.)

Single Underline - The row contains only a single underline. (See rows 125, 225, and 325 in Figure 11A.)

Double Underline - The row contains only a double underline.(See rows 400 and 1125 in Figure 11A.)

Line Space - The row is blank, and used as a spacer. (See rows 175, 275, 375, and 575 in Figure 11A.)

Page Break - The row triggers a page break.

Print

When the Print field is selected, the row prints on the report. A report may contain rows that you do not wish to output on the report. In that case, do not select Print for those rows.

Override Column Segments

In the report writer, you can override column account segments (if any -- columns do not usually specify GL account segments) with row segments. Check the box if the account segment(s) for the row override the segment(s) specified for columns.

Account Selection Tab The Account Selection tab is used to identify the account codes for reports' rows. In specifying account codes, you can select a stored row definition, select an account code template, or write a new mask to identify accounts. The mask is a shorthand that tells the system which account codes to include in the report. In Figure 13, for example, the mask contains: Organization    001 Account 017 Loc 11:13 This shorthand with a colon : between two values indicates a range. In this case, the mask element means: all Loc segments from "11" through "13." Department 6100:6600,7011 This mask element contains another range (6100:6600) and an additional department (7011) that is outside of the range.

Advanced Notes on Rows Templates A hospital sets up templates to identify account codes when the same group of accounts is used often for various general ledger activities. Account templates contain masks. You can create and store templates by selecting Account Management > Account Selection Templates from the GL main Contents. Tabbed Panels Besides the Account Selection tab, other tabs on the Report Row Detail edit panel let you specify other row parameters. Below is a summary. (Help provides details.) Account Filters - Accounts for the rows are filtered according to one or more Account Classes, which you can select: Balance Sheet, Profit and Loss and Statistical. You can also filter accounts by selecting the GL Account Type: Asset, Expense, Liability, etc. Font - Contains settings for typeface, size, etc. Numeric Format - Contains settings for the number of decimal places in data, the currency symbol, etc. General Format - Contains a field to specify indent amount (in points) for rows. (An inch has 22 points.)

3. Save your work.
   
   
4. Click Submit when you are finished defining a row.

Step 6. Use Formulas

If Row Type is Formula, the system uses the formula that you specify to calculate a value. Formulas can contain the standard operators (+, -, /, x) and the sum function.

The sum function is:

sum(row:row) for a range of rows.
       or
sum(row, row, row, ...) with rows separated by commas, e.g., 100, 121, 131, 150.

For example, row 150 in the report (Figure 11A) was built using the definition in Figure 14:

Figure 14 - Entering a Formula in a Row Definition

Formula Examples:

- In Figure 14, notice how the sum formula is written for row 150. (Similarly, in Figure 11A, look at rows 250, and 350.) The account mask for each of these rows specifies a range of departmental accounts. However, the formula uses the syntax sum(100:100); sum(200:200); etc. If you entered just the row number; e.g., sum(100), ProSheet would generate an error.

- In Figure 11A, row 500, the formula is simple addition of the three intermediate values calculated in rows 150, 250, and 350.

- Row 1150 (Figure 11A) sums the accounts in rows 600 through 1000.

Step 7. Use Text Rows

A report can contain text in a cell whose Row Type is either Text or Formula, and whose Column Type is Description.

• For rows, enter the text in the Text field on the Report Row Detail panel (Figure 15; also, Figure 14).

Figure 15 - Defining a Formula Row Including a Text Notation

The cell where the row intersects the Description column contains the text. See Figure 11A, Rows 150, 250, 350, 500, and 1150 at Column A.

Step 8. Include Report Footers

You can enter report footers if you wish.

To enter report footers:

1. Click in a Report Footer cell on the editor (Figure 11A, 8).
   The Report Footer panel appears.
2. Enter the footer in the Title field.
   - Use substitution text as needed.
3. If you wish, you can specify a type (font) size, how the title should align on the page, bold, italic, or underline.
4. When you are finished, click Save and Submit.
5. Refresh the report editor, and the footer appears at the bottom of the report.

Rebuilding a Financial Report

If you are editing an existing report definition, when you finish, you must rebuild the report.

To rebuild a financial report definition:

• From within the Financial Report Editor,
  - Click F5.
  The report rebuilds and the Editor refreshes the display.

• From the Financial Reports Definition list,
  If you have edited a report definition, but not rebuilt it, the list contains Yes in the Needs Rebuild column (Figure 16).
  - To rebuild the report from the list, click Menu > Rebuild next to the report definition.

Figure 16 - Financial Reports List With a Report Definition that Needs to be Rebuilt

Running and Interpreting Financial Report Output

The system can generate output for financial reports formatted for immediate online viewing, "running while-you-wait." For short or uncomplicated reports, this approach can provide quick information. (Notice, on the Financial Reports list, Figure 5 and Figure 16, the Average Run Time column contains the average time that it takes each report to run.)

The system can also generate financial reports "running in the background," and store them in Completed Reports and My Archived Reports for later viewing and printing.

Running a Financial Report for Immediate Viewing

You can run a report from the Financial Reports List or from inside the Financial Report Editor.

To run a non-repeating report from the financial reports list:

Important: Repeating reports cannot be run using View Online (as described in the next steps) unless you override the repeating account segment(s). Otherwise, you must run a repeating report using the Print method)

1. Next to the report definition, click Menu > View Online.
   The View Financial Report selection panel appears (Figure 17).
   

Figure 17 - Report Output Selection Panel

2. Enter or change any fields as needed. For example, you may need to enter a different Base Year and Base Period.
3. Select the Output Type.
   Normal formats the report for display, and lets you drill down to detailed data.
   Spreadsheet creates an Excel spreadsheet for the report.
   PDF Document outputs the report as an Adobe Acrobat .pdf file.
4. If needed, click the tab Override Accounts With to run the report for accounts that are different from the account codes in the report definition.
   Financial Reports - Advanced Topics discusses overrides in detail.
5. Click Submit.
6. To get the report output, on the Financial Reports list next to the report, select Menu > Completed Reports.
   (The output is also available from My Archived Reports.) You can view the output immediately, view it later, or print it.
   

To run a repeating report from the Financial Reports list:

1. Next to the report definition, click Menu > Print Report.
   The View Financial Report Selection panel appears (similar to Figure 17).
2. From the Output Type drop box, select the output type. For printed reports, the options are Spreadsheet or PDF Document.
3. Click Submit.
   The system runs the report and puts the output in My Archived Reports. You can view the output and print it.

To run a report from within the Editor:

1. Click either the spreadsheet icon (for Excel output) or the PDF icon (for an Adobe .pdf file) at the top of the report (Figure 18).
   You are asked if you want to Open the report, or Save the report.

Figure 18 - Output Tools within the Financial Report Editor

2. Click Open to open and view the report.
   The report appears.
   Note: The report is not automatically saved in Completed Reports, but you can save it to a local network file before you close the report window.
3. Click Save to save the report, if you wish.
   The system prompts you for a network location in which to save the file.
   Enter a path and file name.

Drilling Down on Financial Report Account Values

When you view a report online, the system enables drill down for account figures. Using drill down, you can get more information about a value in a report.

Drilling down on detail account values:

1. Select the account value that you wish to get information about.
   When you move the cursor over the value, the system highlights the numbers in pink. (Figure 19).

Figure 19 - Drilling Down on a Financial Report Detail Account Total

2. Click in the highlighted area.
   Journal entry information appears (Figure 20).

Figure 20 - Drill Down Journal Entry Information from a Detail Account Value

• From the journal entry information, you can look at the entire journal voucher that contains the journal entry.
  To do so, click Menu > Journal Voucher (Figure 20).

Drilling down on summary values:

The Training Expense report contains the sum of several accounts. You can drill down on these values and view data for the accounts that are summed.

1. Select the summary value that you wish to get information about.
   When you move the cursor over the value, the system highlights the numbers in blue (Figure 21).

Figure 21 - Drilling Down on a Summary Value

2. Click in the highlighted area.
   Data for the detail accounts included in the summary appears (Figure 22).

Figure 22 - Values for Detail Accounts Included in a Summary

• You can drill down further on detail account values, and get journal entry and journal voucher information, as displayed in Figure 19 and Figure 20.

Also, next to the description of a summary value, you can open to the accounts that are summarized.

To open a summary value:

Opening a summary value provides the same information as drill down, except that if the summary value crosses several account segments; e.g., Department, Location, etc., you can open data for totals by each segment.

1. Move the cursor over the description of the summed values.
   A plus sign appears, along with an active icon for the summarized account segment(s).
   For example, in Figure 23, the values for Department 6100 are sums across accounts by the third account segment, indicated by "L".

Figure 23 - Opening a Summary Value to View the Detail Accounts Included in the Total

2. Click the plus sign to view included accounts.
3. Click the segment icon (L, in this case) to see detail accounts ordered by that segment.
   In the data for the Training Expense Report (Figure 23), values are totaled only by one account segment, so clicking "+" or "L" produces the same result.

Working with ProSheet

ProSheet is an Excel add-in that provides real-time, interactive access to General Ledger account information. Using ProSheet, you can create highly formatted, spreadsheet financial reports. ProSheet lets you upload/download data to the system. To use the ProSheet add-in, you first must download it from General Ledger to a network location where you can access it from Excel. Then, you install it on Excel (using an install wizard) and enable macros.

The latest version of ProSheet uses Excel 2010. If you need to install it, follow the instructions below. If you are currently using a previous version of ProSheet, you will need to disable it (by unselecting it) before proceeding with the install. The instructions below explain how to install, enable, and disable ProSheet.

Download and Install the Excel ProSheet Add-In

To download and install the Release 6.0.3 Excel ProSheet on your local system:

Caution: By default, the add-in file is named ProSheet.xlam. The download process overwrites any existing file with the same name. If you have an existing file with the same name, you may wish to rename it so that its data are preserved.

These instructions download and install ProSheet for Excel 2010. This version supports the  sign-in process.

Important:  members accessing the ERP General Ledger application through  must use the new 6.0.3 version of ProSheet.

Users working with a version of Excel earlier than 2010 can continue to work with the older version of ProSheet, as long as they do not need to sign on through .

1. From the General Ledger main Contents, select Financial Report Writer > ProSheet Excel Add-In.
   The Download panel appears (Figure 24).

2. Click "Click here to download this file."
   

Figure 24 - Excel ProSheet Download Panel

The Windows File Download panel appears. Depending on your Internet Explorer Security Settings, a verification panel may appear.

3. If the verification panel appears, click Run.
   The installation wizard panel appears (Figure 25).

Figure 25 - The First Panel of the ProSheet Install Wizard

4. Click Next.
   Follow instructions on the install Wizard, clicking Next as you move through the panels.
5. The Destination Location panel appears (Figure 26).
   On the Destination Location panel, the Destination Folder specifies where the add-in will be installed. If you wish to install it in another location, click Change and specify the location.

Figure 26 - Destination Location for ProSheet Add-In

6. Click Next.
   The Start Installation panel appears (Figure 27).

Figure 27 - The Start Installation Panel

7. Click Install.
   The system installs ProSheet in the location specified, and sends you a message when installation is complete (Figure 28)

Figure 28 - Finish ProSheet Installation Panel

8. Click Finish. ProSheet is installed and available on your local system from the location you specified. You are returned to the system. Next, you need to enable the add-in.
   

Enabling/Disabling the ProSheet Excel Add-In

The steps for enabling an Excel add-in (once you have installed it) are the same as the steps to disable an existing Excel add-in (before you install a new version). The difference is that to enable the add-in, you select the box next to the add-in in the steps below (Figure 32). To disable the add-in, you unselect the box.

Note: You only need to perform the enable step one time.

To enable an add in

1. On the Excel ribbon, click File. The Excel Menu appears (Figure 29).
   

Figure 29 - Opening the Excel Options Panel

2. Click Options (Figure 29).
   The Options panel appears (Figure 30).

Figure 30 - The Excel Options Panel

3. Click Add-Ins.
   The Add-Ins panel appears (Figure 31).

Figure 31 - Accessing the Excel Add-ins List

4. At the bottom of the panel next to Manage: Excel Add-ins, click Go. (Figure 31).
   The list of Available Add-ins appears (Figure 32). (Note: Your list may contain more, or fewer add-ins.)

Figure 32 - List of Available Add Ins for a User

5. Select the  ProSheet Add-In.
   If this add-in does not appear on the list...
   
   • Click Browse (Figure 32, right side).
   • Open the location where you saved the download. Figure 33 is an example.

Figure 33 - Using the Browse Button to Find the JV Add-In File

• Select ProSheet.xlam.
  The add-in appears on the Available Add-Ins list (Figure 32). Select it.
6. Click OK. The system enables the add-in on Excel.

To disable an add in

• Follow steps 1 through 4 in "To enable an add-in."
• At step 5, click the box for the selected add-in so that it is unselected.
• Click OK.

Sign On to General Ledger for Uploading/Downloading ProSheet Data

ProSheet provides several tools for selecting account data and creating reports in Excel. The first step is to sign on.

1. Open Excel with ProSheet enabled (see previous section).
2. Click the , Inc. tab. The Sign On icon and icons for other tools appear (Figure 34). The tools icons are grayed out, but become active when you sign on.

Figure 34 - The Sign On Icon for ProSheet

3. Click the sign-on icon.
   The sign-on panel appears (Figure 35).

Figure 35 - The Sign On Panel for an Excel Add-In Feature

The first time you sign on to the Excel Add-In, you will need to select a host.

4. Click Host Setup (Figure 35) to identify the host location (Premier/SSO or Aperek) that you are accessing.
   The Host Setup panel appears (Figure 36).

Figure 36- Host Setup Panel for Excel Add-Ins

5. Select either Premier/SSO or Aperek.

- Premier/SSO is for users who access ERP/SCM via Premier Connect as single sign-on (SSO) users.
- Aperek is for users who access ERP/SCM directly via the ERP application URL for their site.
- The default is the last option selected.

When you select Premier/SSO, the panel changes to resemble Figure 37.

Figure 37 - Host Panel for SSO

6. Click Save. The sign on panel appears again.

Signing on with the "Aperek" host option

• Enter your User Name (if not already entered) and password.
• Click Sign On (Figure 35).
  - If there are no errors, you are signed on to the Add-In.

Signing on with the "Premier/SSO" option

• Enter your email address/User Name (Figure 38).
• Click Sign On.
  

Figure 38 - Excel Add-In SSO User Sign On

Your site's network/application login panel appears. Your site administrator can set you up with the correct URL and other parameters.

• Enter your normal login credentials, and sign on.
  

For SSO, the sign on proceeds as follows:

- The Excel Add-In directs control to the Premier Connect page.
- The Premier Connect page looks at the email address and decides if you are an SSO user or not.
- In case of incorrect credentials, the user will get invalid login message.
- Otherwise, the Sign On panel disappears and you are signed on.

Creating Financial Reports with ProSheet

Using ProSheet to create reports has some similarities to using the Financial Report Writer. You will need to plan the layout for your spreadsheet, including a title, headings for columns, and labels for calculated values. All of Excel's functions are available; plus additional account-related functions supplied with ProSheet.

You use Excel's features to perform activities such as:

• Creating a title for the report, entering horizontal lines to show totals, and creating blank rows and columns to separate areas of the report.
• Including graphics, such as charts, images, and color highlights for rows and columns.
• Formatting rows and columns.
• Entering and formatting text in cells for labels and headers.
• Writing formulas.

ProSheet's capabilities let you...

• Select the accounts that you wish to use in the report.
• Retrieve data for the selected accounts. ProSheet pulls in data by Organization, Period, and Year, and by account Balance Type and Data Type with the same options that the Financial Report Writer supplies.

The ProSheet Tools

Once you sign in, the , Inc tab contains icons for the tools that you will use to retrieve data and build Excel worksheets. See Figure 39.

Figure 39 - ProSheet Tools

Signs you off ProSheet. The other tool icons are grayed out when you sign off.

Configuration sets up drill down and message options for your ProSheet session. The options available are on Figure 40.
- You can specify a message to be displayed when ProSheet cannot find a requested account.
- You can require the system to open a new browser window when the report user drills down on a summary account.

Figure 40 - Configuration Options for ProSheet

Clears Cached data in ProSheet. This feature is explained in a subsequent section.

Drills down from a summary account to separate accounts.

Retrieves account data from your site's General Ledger database.

Assists you in building formulas. Click Formula Help for instructions (also repeated below).

Sample ProSheet Report

Figure 41 is a sample spreadsheet report built with ProSheet. The report compares year-to-date actual training expenses to the training budget for each of several hospital departments. Each department has three training accounts: Conference Travel (11), Training Fees (12), and Speaker Fees (13). Account data is displayed by individual account, and summed across all three accounts for each department.

The sections below dissect the report so that you can see how ProSheet is used.

Note: The Accounts worksheet (red circle, at the bottom of Figure 41) was created by ProSheet. Its use is described in the next section.

Figure 41 - A Sample ProSheet Report

Selecting Accounts for the Report

To select accounts for a report, you use a mask that identifies the accounts. You can filter the accounts selected with the mask for Account Type and whether the account is active or inactive.

Note: In this step, ProSheet just gets a list of accounts. The actual data for accounts depends on the period, year, and type of balance. The actual data is the essence of the report, and how to retrieve and use account data is the topic of further sections.

To select accounts for a ProSheet report:

1. From the Excel ProSheet toolbar, select Retrieve Data > Retrieve Accounts (Figure 42).
   (Notice that the panel also gives you the option of retrieving JVs.)

Figure 42 - Clicking the Retrieve Data Link

The Retrieve Accounts panel appears (Figure 43).

Figure 43 - Retrieving Accounts for a ProSheet Report

2. Enter a mask for the accounts that you wish to use in the report.
   Figure 43 shows a mask for the training accounts for several departments -- the same accounts used to create the financial report in Figure 9.
   You can also select one or more Account Type filters, if desired, and an Account Status, if you wish.
3. Click Retrieve Accounts.
   The system calls in the accounts identified by the mask and filters, creates an Accounts worksheet (Figure 44), and puts the list of accounts on the worksheet.

Figure 44 - Accounts Worksheet in ProSheet

You may not wish to display all the information in each account line on your final report. From the Accounts worksheet, you can selectively copy account information to another worksheet. In the Sample Report (Figure 45), the Org, Acct, Loc, Dept, and Description columns are copied to Sheet 1. The columns have been sized, formatted, and labeled.

Sections below discuss the next steps in writing the report: retrieving account data and calculating values.

Figure 45 - Building a ProSheet Report: Formatted Account Descriptions

Relative and Absolute Cell Referencing

The "Training Accounts" example in this section contains formulas that use both absolute and relative cell referencing.

Excel spreadsheet cells are addressed (or "referenced") by their column and row numbers, column first. For example, in the spreadsheet below, the top left cell is A1 and contains "1951," and the bottom right cell is D5 and contains "1972."

Suppose you created a formula that set the value in C1 to equal the A1 value. You enter " = A1" in cell C1. "A1" in the formula (=A1) is a relative cell reference.

When a formula containing a relative cell reference is copied to a different row and/or column, the cell reference changes to accommodate the new column, row, or both.

If you copy the formula " =A1" from cell C1 to cell D1, you will notice that the cell column reference changes. The table below shows how the column reference adjusts so that the formula now contains "B1," not "A1".

To prevent this change from happening, you need to use an absolute cell reference: precede the column letter with $. You would enter " =$A1" in the cell C1. Then, when you copy "=$A1" to cell D1, the column reference (A) will not change.

An absolute reference has either a column element or a row element (or both) which does not change when the formula containing the reference is copied. A $ precedes the element(s) that cannot change.

In the Training Report, you will see how an absolute reference is used for a row. For example, in the spreadsheet above, the format of an absolute row reference would be: =A$1.

Finally, you will probably run into situations where you need an absolute cell reference for both the row and the column: e.g., =$C$5. In this case, when the formula is copied to a new row and/or column, neither the row or column reference changes.

The section below on defining ProSheet rows and columns revisits cell referencing so that you can see how it works in a spreadsheet.

Using ProSheet Functions to Retrieve Account Data

For ProSheet reports, you will need to write formulas that retrieve and run calculations on account data. Formula Help provides the structure of three key ProSheet functions:

AccountData – returns the account balance for the given criteria. You specify the criteria using the function's variables.

AccountDescription – returns the description of an account.

SegmentDescription(SegmentOffset) – returns a segment description. Two functions are currently available:

• SegmentDescription(SegmentOffset) – returns the segment offset, a value between 1 and 9.
• DepartmentDescription(Organization,Department) – returns a description of the department.
  For example, =DepartmentDescription("001","6100") returns the description of Department 6100 -- Hospital Administration -- for Organization 001.

Click Formula Help on the toolbar to display the account functions (Figure 46). The ProSheet report being built will use the AccountData function with the Actual and Operating Data Types, (Figure 46, red arrow) and a YTD Balance Type (Figure 46, blue arrow).

Figure 46 - ProSheet Account Function Help

Using the Account Data Function

The Training Report (Figure 41) contains data for several detail training accounts. The report compares each account's year-to-date Actual balance to the year-to-date Operating Budget balance for a particular year and period. The report also totals the accounts for each department, and then performs the same comparison on the totals.

Refer to Figure 41:

Rows 1 through 5 at the top of the report contain the values used in the report for Organization, Period, Year, Data Type, and Balance Type in columns G and H.

Each detail account code is listed on a separate row, by segment, in columns A through D.
The account description is in column E.
The Actual detail account figures are in column G. A total is also calculated.
The Budget detail account figures are in column H. A total is also calculated.

The bottom of the Training Report contains a row for each department's total across all three of its training accounts.
Each department total is listed on a separate row by department -- the description (in column E) has been shortened to just the Department Name.
The Actual department totals are in column G.
The Budget department totals are in column H.

To create the ProSheet report:

The list below outlines the basic strategy to use in creating the report. Subsequent sections explain the details.

• Put the values for Organization, Period, Year, Balance Type, and Data Type in separate cells (rows 1-5, columns G and H) where they can be accessed by formulas.

For the detail account portion of the report...

• Write a formula that uses the AccountData function to get the G column values. Put the formula in the first account row of the G column, and then copy it to the other rows.
• Write a formula that uses the AccountData function to get the H column values. Put the formula in the first account row of the H column, and copy it to the other rows.
  
  Note: Cell referencing only allows you to write one formula, which you can copy to every detail account cell for an actual or budget value.
  
  
• Total the values in the G column and the H column for all the detail accounts.

For the department totals portion of the report...

• Write a formula that adds the G column values for each department.
• Write a formula that adds the H column values for each department.

You can enter the AccountData function in a spreadsheet cell either by keying it into the formula box or by using the ProSheet Formula Builder (which is much easier).

To use the Formula Builder:

The example below writes a formula in cell G12. The formula contains the AccountData function and is written so that it can be copied to other cells.

1. Select a cell on the spreadsheet e.g., G12.
   Figure 47 shows a formula that uses the AccountData function being entered into cell G12.

2. Click Formula Builder on the ProSheet toolbar to use the AccountData function.
   The Formula Builder panel appears as displayed in Figure 47 (without the blue and green annotations, of course).

Figure 47 - Entering Cell References into the Account Data Function Formula Builder

The Formula Builder contains fields for entering the account data criteria and the account mask. You can enter either options, such as "YTD" for Balance Type, or you can enter cell references from the spreadsheet. The Formula Builder panel in Figure 47 contains only cell references. The cell references are explained below.

You can further specify your accounts with filters. The filters are in the box at the top of the Formula Builder (Figure 47, yellow box).

You can also use a stored account selection template instead of a writing a mask to specify accounts. (The topic Using Account Templates explains how to create and save account selection templates.) The list of stored account selection templates that you can work with are in the Selection Templates list on the right side of the Formula Builder (Figure 47, red arrow). All your stored account selection templates are available for you to pull into ProSheet, except those that may be restricted to particular organizations. A discussion of how to set up account selection templates and how to restrict templates to particular organizations is in the advanced topic: "Restrict an account selection template by organization."

Note: If an account selection template is restricted to particular organizations, you can only access the template and use it in ProSheet if the organization is included in your Data Profile. The account selection template will not appear in the Formula Builder Selection Templates box.

3. Complete the fields on the Formula Builder panel.
4. When you are finished, click OK.
   ProSheet enters the AccountData function into the formula for the selected cell.
   - Figure 48 shows the resulting calculated value in cell G12.
   - The completed AccountData function is in the formula box.

Figure 48 - Account Data Formula Shown in Formula Box and Cell Value Calculated

Copying a Formula to Other Cells

To copy a formula from one cell to a group of other cells, use the Excel copy utility. Refer to Figure 48.

For the G column:
- Select the cell G12 and drag its fill handle (in its lower right corner) down over the cells in the rest of the G column to row 29.
The column cells populate with dollar values.

For the H column:
- Select the cell G12 and drag its fill handle right to the H column.
Cell H12 populates with a value.
- Select the cell H12 and and drag its fill handle down over the cells in the rest of the H column to row 29.
The H column cells populate with dollar values.

If you like, select any of the cells in the G or H column and look at the formula in the Excel formula box. You can see how Excel changed the cell references for rows and columns. For example,

Cell G21 has this formula:

=AccountData(G$1,G$4,G$5,G$2,G$3,$A21,$B21,$C21,$D21)

Cell H16 has the formula

=AccountData(H$1,H$4,H$5,H$2,H$3,$A16,$B16,$C16,$D16)

If you have not done so, now is a good time to Format the G and H columns for dollar data, with two decimal spaces.

Totaling Column Values

At the bottom of the G column, create a labeled cell (in this case, G31) for the sum of the column values. Similarly, cell H31 will contain a sum.

• In the cell for the G column, enter the sum function for rows 12 to 29:

=sum(G$12:G$29)

Excel populates the cell with a total value.

Drag the formula from the G column to the H column. Excel populates the cell. The formula for the H column total contains:

=sum(H$12:H$29)

Creating Department Account Totals

The bottom of the report contains a total for each of the five departments for all training expenses.

To calculate these totals:

1. Look for the three rows containing the department's three accounts. The account have the values 11, 12, and 13.in segment 3.
   For example, Department 6100 has account data in rows 12, 18, and 24.
2. In a G column cell for each department row (e.g., G35), enter a formula that sums the three department account cells (e.g., G12, G18, and G24).
   Use a relative cell reference for the column and an absolute reference for the row.
   
   For example, next to Admin Dept, in column G you would enter:
   
   =sum(G$12,G$18,G$24)
3. Write a formula for each department in the G column.
   Excel populates the cell when you complete the formula (or, you can click Re-Calculate)
4. Drag each G column formula to the right for column H. The column reference will change to H, but the row reference will not change.

Drilling Down on ProSheet Reports

You can drill down to account details from a ProSheet report.

To drill down from a ProSheet report:

1. Select the account value that you wish to examine.
2. With the value selected, click the Drill Down button on the toolbar (Figure 49).
   

Figure 49 - Drilling Down on Account Data from ProSheet

Information appears on the General Ledger Account Inquiry and Analysis panel for the account (Figure 50).

Figure 50 - Results of Drill Down on Account Data from ProSheet: Account Inquiry and Analysis Data for all Periods

Using the Account Inquiry and Analysis panel, you can drill down further to look at journal entries and journal vouchers for account data. When you move the cursor over a detail value, the system highlights the numbers in pink (Figure 50).

• Click the highlighted area to view journal entries.
• From the Journal Entry panel, you can view the journal voucher that contains the journal entry by clicking Menu > Journal Voucher.

Figure 51 displays tools that download account data to ProSheet. The user’s Data Profile applies to account data downloaded via Retrieve Data (accounts or JVs) and Formula Builder. See the previous section on data security.

Figure 51 - The ProSheet Retrieve Data Tool

Retrieving Accounts

Clicking Retrieve Accounts opens the Retrieve Account Codes panel. An example is in Figure 52. (Alternately, you could select Retrieve Data > Retrieve JVs. Data Profile settings would also apply.)

Figure 52 - The Retrieve Account Codes Panel for ProSheet

Since account codes have a segmented structure, to capture multiple accounts, without listing every account, you create a pattern – or mask – for the group of account codes to be retrieved. Typically, one account segment identifies the organization, and another identifies the department.

To capture data in multiple organizations’/departments’ accounts, you enter a beginning organization/department code in the mask and include a wildcard symbol (?). The wildcard symbol instructs ProSheet to pick up other organizations/departments that fit the pattern.

For example, suppose you have an account pattern as follows:
Segment 1 – organization numbered from 001 to 013.
Segment 2 – account identifier: between 01 and 99.
Segment 3 – account sub-identifier: three numbers
Segment 4 – department, numbered from 0100 to 0210.

Figure 52 contains a mask used to download liability and expense account data for...

Organizations 010 to 013
. . .with an account value of  02
. . .and a sub-account value of 070
. . .for departments in each organization numbered 0100 to 0210.

If you are using ProSheet Data Profile Security, you must have Data Profile access to all the departments and organizations specified or covered by a mask. If some of those organizations/departments are not included in the user’s Data Profile, their account data is not accessible, and is not downloaded to ProSheet.

Instead, a zero (0) appears on the spreadsheet line where the account data would otherwise be written.
Zero is the default. However, you can configure the value that appears, if you wish to use something more meaningful to your users; for example “Restricted data” or “Not authorized.” Instructions are below.

In Figure 52, for example, if the user had access to every “01...” department, except for Department 0123, then account data for Department 0123 (which was not in his/her Data Profile) would not be downloaded.

Security in ProSheet Functions

The example in Figure 52 is for the Retrieve Data tool in ProSheet. Your Data Profile also controls data for ProSheet functions that return account data values.  The Formula Builder button (Figure 51, next to Retrieve Data) lets you create formulas/functions. Functions may contain organization and department segments, or masks that identify organizations and departments. (See Using ProSheet Functions to Retrieve Account Data).

In formula/function masks, organizations/departments can be specified literally, or retrieved using wildcards (e.g., “1?10”),  ranges (e.g., “1267:1277”), and other selection shorthand. Templates for accounts defined in the Financial Report Writer can also be called in to ProSheet functions. See Figure 48 for an example of the Formula Builder with the available templates in the lower right.

If the organizations/accounts referenced by any of these methods are not your Data Profile, then account data for those organizations/departments does not download. Instead, the ProSheet lines will contain a zero, or whatever message you have configured.

When you drill down on summary accounts to display detailed accounts for different organizations and departments in ProSheet, once again, you can only display organization/department data that you are authorized to see.

Bypassing the Data Profile Security Feature

 A field has been added to the System Values panel that allows sites to turn off ProSheet security (Figure 53).
- The field is: Bypass ProSheets/I&A/Fin Report Writer Security (Figure below)
- The default setting is “unselected” (ProSheet security is enabled). The system performs the data profile check.
- To bypass (disable) ProSheet, Financial Reporting, and Account Inquiry data profile security, check the field.

Figure 53 - New Field for Turning Off ProSheet Security for a Site

To access the System Values panel, from Work in Administration, select Administration > System Values > edit > Other.

Configuring the Message for Accounts Not Allowed to the User

Note: The latest version of ProSheet is 06.08.007. You do not need to download ProSheet to avail yourself of this security enhancement. But, if you wish to configure a message for unauthorized accounts (other than the default of 0), you do need to download and re-install the latest version.

Usage: To configure a message for account data that is not allowed:

1. On the ProSheet toolbar, click Configuration (Figure 54).

Figure 54 - Click Configuration on the Toolbar

The ProSheet Configuration panel appears (Figure 55).

Figure 55 - ProSheet Configuration Panel for Entering “not allowed” Message

2. Enter a message in the field Account not found message.
3. Click OK.  ProSheet will use the message you specified for accounts that a user’s Data Profile does  not allow.

Clearing the Cache

While you are work with ProSheet, the account data that you are using is kept in ProSheet's local cache (pronounced like Johnny Cash). The cache is just a memory location, similar to the cache of visited files that your browser keeps while you are surfing the web.

If you want to see a page again, your browser, for example, loads the page from its cache, rather than reading the page from the original URL address. Reading from the cache is much faster. The situation is the same with ProSheet: while you are working with ProSheet, when you refresh or re-calculate the spreadsheet, account data is re-loaded from the ProSheet cache.

Of course, your account data in the system may not stay the same over a period of hours, or minutes. If you are working with ProSheet, you may want to periodically clear the cache of stored data. Once you clear the cache, when you refresh or re-calculate the spreadsheet, ProSheet brings in new, updated account data from General Ledger.

• To clear the cache, click the button Clear Cache on the ProSheet toolbar.

Copyright © 2023 by Premier Inc. All rights reserved.