.title "External Consolidation" .id 314120060421100609 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "External Consolidation"] [/* { To do perpetuals on analysis open/closed do not work picking which account in the bracnh database transactions resolution error reporting missing accounts databases that cannot be opened plus equal operation do not use consoldiaion database for accounting can be destroyed at any time easily re-created total versus posting accounts */ }] [img .src [::file join $::qw_program_path object newviews account consolidate_external_figure_1.gif]] [p { The example above captures the essence of an external consolidation. In the example, three NewViews databases are involved: a [qw_term consolidation] database, and two [qw_term branch] databases, but in general there may be any number of branch databases. }] [p { Information is automatically gathered from the branch databases into the consolidation database where it is [qw_term consolidated] and displayed. In the example, the accounts happen to be vendors, but any accounts can be used. Both branches purchase from the same vendors, ADLER, BAKER, and COOPER, and the consolidation displays the total owed to each vendor by the overall organization. }] [p { Red arrows represent the gathering of information into the consolidation database from the branch databases. }] [/* { [p { External consolidation presents consolidated information collected from any number of other, i.e. [qw_term external], NewViews databases. }] [p { For a quick introduction to external consolidation see the [link .id 314120060421132338 {[bold "Tutorial"]}]. }] */ }] [h2 "Terminology"] [p { The term [qw_term "external"] is used because information is gathered from completely separate, i.e. external, databases. This differs from [qw_term internal] consolidation where the accounting entities being consolidated are contained within the same NewViews database. For convenience, we will omit the term external in this documentation when referring to a consolidation, but an external consolidation is always implied unless otherwise explicitly stated. }] [p { The [qw_term source] of the information, i.e. databases from which information is gathered are called [qw_term branch] databases. In practice, branch databases could be used for separate divisions, subsidiary companies, departments, and so on, so the use of the term [qw_term "branch"] is somewhat arbitrary in this respect. }] [p { The [qw_term destination] of the information, i.e. the database into which information is being gathered and presented, is called the [qw_term "consolidation"] database. }] } /314120060427091750 { .title "Features" .id 314120060427091750 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Feature Overview"] [p { Below, we provide a list of features that may help introduce the power as well as the limitations of consolidation. }] [h2 "Branch databases can be operational."] [p { You can gather information from branch databases, even if they are currently open and in use by any number of users. There is no need to shut down a branch database. }] [p { Note that there are still situations where a branch database cannot be opened, and rightly so. For example, a consolidation cannot open a branch database if that database is already open locally (not using a server) with single-user access, unless you are the one who has it open. Or when opening a branch database through a server, the server must be running and offering the database. Finally, the branch database must be the same version as the consolidation database. }] [h2 "You do not need to learn a new human interface."] [p { You use a consolidation database the same way you use any NewViews accounting database. Consolidation information is controlled and displayed using the usual (blue) account tables. }] [h2 "All amount types and periods are available."] [p { You can collect and display any information that is already available in any blue table of accounts. The consequences of this include: }] [ul { [li { [p { You can gather actual, debit, credit, open and closed amounts. }] }] [li { [p { You can gather resolutions including daily, weekly, bi-weekly, monthly, quarterly, yearly, etc. }] }] [li { [p { You can display amounts as perpetual or periodic. }] }] [li { [p { You can gather any number of periods and display them side by side. }] }] [li { [p { You can gather financial, order, and budget amounts. }] }] [li { [p { You can gather quantities and dollar amounts. }] }] }] [h2 "Consolidation databases can be organized independently."] [p { Information is collected into posting accounts in the consolidation database. These posting accounts serve as [qw_term "controlling"] accounts which can then be organized using the total accounts and the total-to structure of the consolidation database, independent from any branch database organization. }] [h2 "Consolidation databases can be opened with multi-user access."] [p { A consolidation database is itself just a NewViews accounting database. Therefore any number of users can simultaneously open and use the consolidation database itself. You add users and grant access just as in any NewViews accounting database. }] [h2 "Consolidation databases can in turn be consolidated."] [p { You may have more than one structural level in your organization and consolidation databases can be organized accordingly. You can have any number of consolidation databases, each consolidating specific sets of branch databases, and then in turn, consolidate them [qw_quoted "upward"] into more central consolidation database. }] [p { There is no limit to the number of levels you can manage or the way you can manage them. }] [h2 "Security is enforced."] [p { Consolidation follows the NewViews security model. A consolidation cannot gather information from any branch database unless you have access to that database. When you run a consolidation each branch database must be accessed with a valid user name and password. In addition, the consolidation cannot access information within a branch database unless the user has been granted access to the information under the security system of the branch database. }] [p { However, consolidation greatly eases this process. The simplest technique is to use the same user name and password used for consolidation database when accessing each branch databases. Or this simple method can be overridden to use individual user names and passwords for any or all branch databases as described in the section on [link .id 314120060424114059 {[bold "Security and Database Access"]}]. Even when a more advanced method is used, you still specify necessary information only once, and you don't have to enter passwords each time you update the consolidation. }] } } /314120060421132338 { .title "Tutorial" .id 314120060421132338 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Consolidation Tutorial"] [p { The fastest way to understand consolidation is to start with a simple but hopefully useful example. More advanced issues are better addressed elsewhere after the basics are understood. }] [p { We assume that you are a NewViews user so we can avoid getting bogged down in the details of operating NewViews. That way we can concentrate on issues specific to consolidation. }] [h2 "The Example"] [img .src [::file join $::qw_program_path object newviews account consolidate_external_figure_1.gif]] [p { Suppose your organization maintains a number of databases, say one per branch office. You have two branch offices, BRANCH_A and BRANCH_B. The branch offices purchase goods from vendors and in many cases some or all branches purchase from the same vendors. Assume they all purchase from vendors ADLER, BAKER, and COOPER. You want to know your organization's total liability to each vendor, i.e. the sum of the liabilities owed by all branches to each vendor. }] [p { One way to do this is to manually open each branch database, visit the table containing vendor accounts, set up the periods for the desired amounts if necessary, write down the amounts, and finally, add up the amounts for each vendor from all of the branch databases. }] [p { The liabilities will of course change as time goes on, so you will have to repeat the entire process on a regular basis, say once a month. With a larger number of branches and vendors, this can quickly become a distasteful and error-prone task. }] [p { The purpose of consolidation is to automate the process. You have to set things up but you only have to set them up once. Then you can run the consolidation script any time you want consolidation information updated. }] [/* { [h2 "Opening the Branch Databases"] [p { The first thing you should do is add a row to you workstation for each branch database that you intend you use in the consolidation. This step serves several basic purposes at the same time: }] [ul { [li { [p { It forces you to check that you really have access to each branch database. }] [p { Knowing that you really have access }] [p { It lets you specify a user and allows the workstation to retain the password. }] [p { }] [p { It sets things up for quick access to the branch database. }] [p { }] }] }] */ }] [h2 "Create the consolidation database."] [p { Consolidated information will be collected and displayed in a separate database which we call the [qw_term "consolidation"] database for convenience. Your first task is therefore to create a new empty database. The steps below are exactly the same that you would follow to create any new database. }] [ol { [li { [p { Insert a new workstation login row and fill in the file field. }] [p { To create a new database insert a new row in the workstation table and manually enter the full folder path and file name of the new database in the file field. Note, you can't use [qw_key F3] to pick a file because the database file doesn't exist yet. But you could pick the folder in which you intend to create the datasbe so that you only have to type in the file name and not the full folder path. }] }] [li { [p { Open the new database. }] [p { Change the login row state to [qw_field_value open]. Because the database doesn't exist, you are asked to confirm that you want to create a new database. After confirming, the database is created and opened under the user ADMINISTRATOR. Notice that the new database will be empty. Basic account and report classes and other classes are there, but there are no instances. }] }] [/* { 2.31.1 [li { [p { Cancel the default window setup. }] [p { Whenever you create a new database you are asked to confirm if you want to perform a default window setup. It is probably best to click cancel in this case, or else go get a coffee while the windows are set up. }] }] */ }] }] [p { The new database, i.e. the [qw_term consolidation] database, is technically just another NewViews database, much like any other database you use for accounting. For example, you should set a password to protect it, you can add any number of users, your operations are audited, and so on. It should all be very familiar. It is only the way we intend to use the consolidation database that is different. }] [h2 "Add accounts to the consolidation database."] [p { In this example we intend to consolidate a number of vendor accounts and we will now add the vendor accounts to the consolidation database. }] [ol { [li { [p { In the left pane, position on the root AP account. }] [p { The vendors will be sub-accounts of ACCOUNT/AP so click on /ACCOUNT/AP in the explorer tree in the left pane. }] [p { Note that although the vendors happen to be sub-accounts of ACCOUNT/AP in this example, in general your vendors might be organized with several levels of sub-accounts. Pick the place you want to add the vendors accounts as apprpriate. }] }] [li { [p { Flip the right pane to the setup view. }] [p { Click on the [qw_tab Accounts] tab in the right pane and select [qw_tab Setup] from the pulldown. }] [p { The table will be blue, indicating that you are on a table of accounts; vendor accounts in this case. The setup view is the best place to add accounts. Since this is a new database, there are no accounts in the table at this time, except for the AP account class itself. }] }] [li { [p { Add the vendor accounts. }] [p { Now add your vendor accounts; in this example three accounts with the names ADLER, BAKER and COOPER. Give each a suitable description, presumably the same description used in branch databases. You don't need to enter any other information on these accounts. The normal balance and normal representation fields are automatically filled in and there is no need to total them anywhere else. Note that they already automatically total to the root AP account. Since the accounts automatically inherit many field values from /ACCOUNT/AP, such as the [qw_field_name "normal balance"], [qw_field_name "normal representation"], and so on, there is nothing else to fill in. }] }] }] [p { This brings up an important point concerning how consolidation works. The account names you enter here are used when information is collected from the branch databases. It is important that the same names are used in both the consolidation and branch databases. If different names have been used in branch databases for the same vendor, then you should change the names to make them the same in each branch database. }] [h2 "Specify the branch databases that are to be consolidated."] [p { The consolidation will need a list of branch databases from which to get the consoldation information. Each element of the list identifies the file of a branch database and optionally a server. }] [ol { [li { [p { Flip to the notes view. }] [p { Click on the [qw_tab Notes] tab to flip to the notes view. Note that the setup view had been positioned on the sub-accounts of /ACCOUNT/AP, thus displaying the vendors. When we flip to the notes views we are actually on the notes of /ACCOUNT/AP. }] [p { }] }] [li { [p { Add a line to the notes for each branch database. }] [p { Each line identifies the file, and optionally the server of each branch database. Note that this is the same information that you would normally set on each row of a workstation login table. The text might look like: }] [qw_code { .server "server_machine_1" .file "d:/nv/branch_a.nv2" .server "555.0.192.3" .file "c:/nv/branch_b.nv2" }] [p { The format for the field values is straight-forward. Each line contains a number of field names and values that are used to identify a branch database. }] [p { The [qw_field_name ".file"] field is mandatory but [qw_field_name ".server"] is optional and is necessary only if the database is to be accessed through a server. You can omit [qw_field_name ".server"] altogether or set its value to empty, i.e. [qw_quoted ""], for local single-user database access. See [link .id 314120060424170320 {[bold "Specifying Branch Databases"]}] for more. }] }] }] [h2 "Security Considerations"] [p { Security will be an issue when attempting to actually access the branch databases. You cannot access a database or any information in it, unless you have access. That means that you must be a user with a valid password in order to access each branch database. In addition, as that user you must also have been granted access to the information you are attempting to collect. }] [p { For the purpose of this example we will keep things simple. We assume you are going to access each database under user [qw_field_name ADMINISTRATOR], and therefore have been granted access to all objects in the database. }] [p { This leaves the issue of the password. However, suppose you are the ADMINISTRATOR for the branch databases and you have used the same password for the ADMINISTRATOR in each branch database. If you use the same password for the ADMINISTRATOR in the consolidation database you are ready to go. }] [p { In general, you will not use the ADMINISTRATOR user. However, it is an acceptable technique for the consolidator to add the same user, say CONSOLIDATOR, to the consolidation database and to each branch database, and set the same password for each. This guarantees that you will be able to open each branch database when using the consolidation database as the CONSOLIDATOR. See [link .id 314120060424114059 {[bold "Security and Database Access"]}] for more. }] [h2 "Running a Consolidation"] [p { At this point we assume you have added several vendors to the consolidation database, and that these vendors exist in branch databases with the same account names. To consolidate these accounts: }] [ol { [li { [p { Flip to the [qw_field_value "Multiple Period Analysis"] view. }] [p { Click on the [qw_tab Accounts] tab and select [qw_tab "Multiple Period Analysis"] from the pulldown. Any view of a blue table of accounts can be used to control which information is to be collected by the consolidation. We recommend the multiple period analysis view only because it is particularly flexible. }] }] [li { [p { Issue the [qw_menu_command "View" "Analysis" "Multiple Column Setup"] command. }] [p { [img .src [::file join $::qw_program_path object newviews account consolidate_external_screen_5_a.jpg]] }] [p { A table will appear as shown above. }] }] [li { [p { Fill in the [qw_field_name "Multiple Column Setup"] fields. }] [p { When performing a consolidation, the representation must be set to perpetual. After the consolidation is performed, you can switch to other representations. }] [p { This is also where you pick the index, date range, amount type, tag, and so on to control what is displayed in the analysis view. Since this is not a tutorial on operating NewViews we will move along. }] [p { In this example we might pick a date range that includes a year, and set the analysis view resolution to monthly for twelve 12 columns, or quarterly for four columns, weekly for 52 or 53, and so on. }] }] [li { [p { Run the consolidation script. }] [p { Issue [qw_menu_command "Tools" "Script Evaluate"] command and pick [qw_directory [::file join c:/ nv nv2.exe object newviews account consolidate_external.qw_script]]. Your path may of course vary, depending on your NewViews installation folder. }] [p { The amounts will be displayed in your analysis view as they are collected from the branch databases. }] [p { If there are problems opening any branch database, or finding specific accounts in a branch database, an error report will be displayed. However, the consolidator keeps collecting and updating, even when problems occur. The problem databases and/or accounts are simply skipped and you are notified in the problem report when the consolidation is finished. See [link .id 314120060426112239 {Problems Encountered While Consolidating}] for more. }] }] }] [p { Steps 1 to 4 are usually performed only once, or from time to time, if you want to change the date range or resolution, for example. Most often you just perform step 4 any time you want to [qw_term refresh] the consolidation to the most recent values of the branch databases. }] } } /314120060427093653 { .title "Setting Up For Consolidation" .id 314120060427093653 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Setting Up For Consolidation"] [p { Without getting into detail, we briefly list the essentials of setting up for consolidation: }] [ul { [li { [p { Create a new database, the consolidation database. }] [p { A consolidation database is just a NewViews accounting database like any other. You can add users to it and you should protect it with passwords. }] }] [li { [p { Add accounts to the consolidation database. }] [p { The key here is to give the accounts the same names as accounts in branch databases. The account names are used when collecting information from the branch databases. }] }] [li { [p { Specify the branch databases that are to be consolidated. }] [p { You use the notes views of reports and/or accounts to list branch database directories and optionally servers. }] }] }] [p { You only need to set up for consolidation once. Adjustments may be made afterward by adding new accounts to the consolidation database. In the example, as new vendors are added to branch databases you will also want to add them to the consolidation database. }] } } /314120060427093808 { .title "Running a Consolidation" .id 314120060427093808 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Running a Consolidation"] [p { After setting up, you run the consolidation script to update the consolidation database. You can run this script any number of times. Again, without getting into detail, we list the essentials of running a consolidation below. }] [ol { [li { [p { Set up the analysis view as desired to control the information gathered. }] [p { Most often you set up the analysis view once and use it repeatedly. But you can change it at any time to change the information that will be collected. }] [p { You can use any view of a table of accounts (blue). We recommend that you use a multiple period analysis view because it is the most flexible. There you can control the date range, resolution (monthly, yearly, etc.), amount type (amount, quantity), tag (financial, order, budget), and so on. You can collect amounts for as little as a single period, or amounts for any number of periods, simply by changing the settings of the view. By controlling the view, you are controlling what information is gathered. }] }] [li { [p { Mark a block of accounts and run the consolidation script. }] [p { When you run the script, the accounts marked in the block are updated from the branch databases according to the analysis view setup. }] [p { If you don't mark a block then just a single row, i.e. the row on which you are currently positioned, is updated. }] [p { The consolidation does not attempt to update text lines or accounts which are total accounts in the consolidation database. See [link .id 314120060427085649 {"Accounts in the Consolidation Database"}] }] }] [li { [p { Watch the consolidated results appear. }] [p { The information is displayed as it is collected. }] }] [li { [p { Slice and dice the information. }] [p { After the consolidation is run, you can change the analysis view settings to display the information in other useful ways as usual. }] }] }] } } /314120060424114059 { .title "Security and Database Access" .id 314120060424114059 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Security and Database Access"] [p { Security is an issue when information is collected from branch databases. A user name and password are needed for each branch database before it can be accessed for the purpose of collecting information. We first describe a very simple way to gain access to the branch databases, and then we follow up with more advanced techniques that offer complete access control. }] [h2 "The Simple Method"] [p { You must have the consolidation database open before you can run an external consolidation. This is clear because you mark a block of accounts in the consolidation database to control the information collected. }] [p { So when you run the consolidation, NewViews attempts to open each branch database with the same user name and password that you used to open the consolidation database itself. }] [p { For example, it you are user GREEN in the consolidation database, and you are also user GREEN in each branch database, and if you have used the same password in these databases, then you are ready to go. }] [h2 "Setting Individual Users and Passwords."] [p { The consolidation takes the following steps in the following order to gain access to any branch database. }] [ul { [li { [p { Uses an open workstation login row, if any. }] [p { Any branch database might currently be open in the workstation. If it is then there is no need to open the branch database at all. It is access under the user in the workstation login row that has it open. }] }] [li { [p { Uses a workstation login row for the specified server and file. }] [p { If the database is not already open, the workstation login table is searched for a match with the server and database file specified in the notes view. Although the workstation might not have the database open, it does retain the password and that password is used to open the database. }] }] [li { [p { Uses the current consolidation database user and password. }] [p { This is the [qw_quoted "simple"] technique described in the previous section. The points above are tried first so this method is used only if the other fails. }] }] }] [p { The previous section indicated that the consolidation attempts to gain access to branch databases with the user name and password used to open the consolidation database itself. This is true but first the consolidation }] } } /314120060424170320 { .title "Specifying Branch Databases" .id 314120060424170320 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Specifying Branch Databases"] [p { You use a notes view to specify which branch databases are to be used for an external consolidation. A typical notes view might look as shown below. }] [hr] [qw_code { .server "server_machine_1" .file "d:/nv/branch_a.nv2" .server "555.0.192.3" .file "c:/nv/branch_b.nv2" .server "" .file "c:/nv/branch_c.nv2" }] [hr] [p { Branch A and branch B will be accessed remotely through a server. The server is specified just as it would be on a workstation's table of login rows. It may be identified by a domain name or ip. }] [p { The server for branch C is empty so the branch C database will be opened locally with single-user access. }] [p { NewViews finds all lines that match the format expected by the external consolidation so you can intersperse consolidation lines with regular notes as shown below. }] [hr] [qw_code { Here are some free-form notes at the top of the notes view. You can add notes and blank lines as usual. But the next line will be used by the external consolidation. \n .server "server_machine_1" .file "d:/nv/branch_a.nv2" \n You can even place notes between lines used for the consolidation. This is especially handy if you need to document something about a branch database, \n .server "555.0.192.3" .file "c:/nv/branch_b.nv2" .server "" .file "c:/nv/branch_c.nv2" \n And here are some further notes at the end of the notes view. }] [hr] [h2 "The Fields"] [p { The fields recognized by the external consolidation to identify and access a branch database are described below. }] [ul { [li { [p { [bold ".file"] [qw_quoted c:/nv/some_branch_database.nv2] }] [p { This is the full path to the database file. Include the drive letter and colon. Use forward slashes ([bold /]) for name separators. If the file contains spaces enclose it in double-quotes. }] [p { This field is required. Any notes line that does not contain this field is treated as a [qw_quoted "regular"] note line and is simply skipped. }] }] [li { [p { [bold ".server"] [qw_quoted some_server_name_or_ip] }] [p { Specifies the server. You only need to specify the server if the database is being accessed through a server. }] [p { Consolidations are often performed on branch databases that are [qw_term operational], i.e. currently open with any number of users actively working on the branch databases through servers. External consolidation not only allows this situation, but treats it as the normal case. }] [p { Absence of a server field or a server field with an empty value, i.e. [qw_quoted ""], are equivalent, and specify that a local database is to be used with single-user access. }] }] [li { [p { [bold ".username"] [qw_quoted SOME_USER_NAME] }] [p { This field is not often used. If you have a workstation login table with several rows for the same branch database, they are usually the same except for the user. If the .username field is specified in the notes it can [qw_quoted "break the tie"] by selecting the row with the same user, if any. Otherwise the first login row that matches the database file and/or server is used. }] }] [li { [p { [bold ".is_disabled"] [qw_quoted 1] or [qw_quoted 0] }] [p { This field can be used to simply skip the database and not use it in the consolidation. Set it to [qw_field_value 1] to skip the database. If this field is omitted the database is enabled by default and is processed. }] }] }] [h2 "Additional Comments"] [ul { [li { [p { You can still keep [qw_quoted "regular"] notes in the notes view. }] [p { Any notes line that does not conform to the structure recognized by a consolidation is simply skipped. So you can enter notes as usual. Any lines recognized by the consolidation are processed, regardless of where they are found in the notes view. }] }] [li { [p { All fields for the same database must be on the same notes line. }] [p { Each set of fields for an individual database must be on the same line. Fields for different databases must be on separate lines. }] }] [li { [p { Field values that contain spaces must be enclosed in double-quotes. }] [p { Enclosing all field values in double-quotes is a good practice in general, but is actually necessary only when a value contains spaces or other so-called [qw_term whitespace]. }] }] }] } } /314120060426161224 { .title "How Consolidation Works" .id 314120060426161224 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "How Consolidation Works"] [p { Consolidation works by adding transactions in the consolidation database as information is collected. These transactions are added to general journals that are automatically added by the consolidation on an as-needed basis. }] [p { The consolidation also adds several accounts on an as-needed basis. These accounts are used as balancing accounts for transactions added by the consolidation as required for double-entry accounting. }] [p { You can run a consolidation many times. The first time a consolidation is run, transactions are typically added. A subsequent consolidation operation will simply modify these now pre-existing transactions to reflect any changes that occurred in branch databases since the last consolidation operation was performed. }] [p { This section covers the journals, accounts, and transactions added by the consolidation and shows how they correspond to the information collected, as controlled by the analysis view. }] [img .src [::file join $::qw_program_path object newviews account consolidate_external_screen_1_a.jpg]] [p { The screen above shows an analysis view of vendor accounts in the consolidation database. The consolidation has just been run and the amounts haven been gathered. The view was set for the four quarters of the year 2004. A small number of columns was used solely so they would fit on the screen. }] [p { Note that when you run a consolidation the amounts gathered must be perpetual. In the screen above this is reflected by the fact that the begin dates are empty. After running the consolidation you are free to change the columns to display periodic amounts. }] [p { The [qw_field_value date] index has been selected so the amounts collected will be the actual account balances. It could have been set to [qw_field_value "date/debit"] or [qw_field_value "date/credit"], and so on, if debits or credits were desired. The tag in each column is set to [qw_term "financial"]. Twelve vendor accounts are marked in the block of accounts to be consolidated. We are ready to run a consolidation. }] [p { We will refer to this analysis view's settings in further examples throughout this section. }] [h2 "Journals added by the consolidation."] [p { The first time a consolidation is run, it automatically adds a general journal with the name [qw_field_name "CONSOLIDATION_EXTERNAL"]. Sub-journals are then added to this journal on an as-needed basis. One sub-journal is added for each tag used in a consolidation, and the tag is used for the sub-journal's name. }] [p { For example, when the consolidation is run for the analysis view as set in the screen above, a sub-journal named [qw_field_value "FINANCIAL"] will be added to the CONSOLIDATION_EXTERNAL journal, if it didn't already exist. Similarly budget or order sub-journals will be added the first time these tags are used. Financial, order, and budget tags come built-in with NewViews but you may add and use additional tags, and if used in a consolidation, corresponding sub-journals will be added. }] [h2 "Accounts added by the consolidation."] [p { The first time a consolidation is run, it automatically adds a general account with the name [qw_field_name "CONSOLIDATION_EXTERNAL"]. Sub-accounts are then added to this account on an as-needed basis. One sub-account is added for each tag used in a consolidation, and the tag is used for the sub-account's name. }] [p { The sub-accounts are added on an as-needed basis as tags are used in consolidations, exactly as described for the sub-journals above. }] [h2 "Transactions added by the consolidation."] [/* { [p { A consolidation is guided by the analysis view from which it is run. We use the analysis view in the screen above to illustrate the transactions resulting from the consolidation. }] */ }] [img .src [::file join $::qw_program_path object newviews account consolidate_external_screen_2_a.jpg]] [p { The screen shot is positioned to show the transactions added after a consolidation was performed on the analysis view shown earlier in this section. The top-right pane is positioned on the transactions of the EXTERNAL_CONSOLIDATION/FINANCIAL journal. }] [p { [bold "The consolidation adds a transaction for each analysis view column."] In the example there are four transactions because there were four columns on the analysis view. }] [p { [bold "The transaction dates line up with the end dates of the analysis view columns"]. In the example, each transaction is dated at the end of a quarter for the year 2004. }] [p { Next we move on to the bottom-right pane where the details of the current transaction in the top pane are shown. }] [p { [bold "The consolidation adds a line item for each account marked in the block when the consolidation was run."] Since there were twelve accounts marked, there are twelve line items on each transaction. Although the details are only shown for one of the transactions, each of the other three transactions will have the same number of line items. }] [p { Note that the line items post to the accounts that were marked in the consolidation, and also to the account used to balance transactions using the [qw_term "financial"] tag, i.e. the account with the name FINANCIAL. }] [p { Also note that [bold "some of the amounts are negative"]. This reflects the way the consolidation works. The consolidation works with perpetual amounts, forcing the perpetual balance of each account to the sum of the branch accounts as at each end date specified by the analysis view. The amounts are therefore the changes to account balances between the dates specified, and the account balances can go up or down between these dates. Negative numbers occur for any period where the balance has dropped since the previous period. }] [img .src [::file join $::qw_program_path object newviews account consolidate_external_screen_4_a.jpg]] [p { The screen above again shows the analysis view after running the consolidation. The blue table is currently positioned on the ALEXANDER vendor account and bottom pane shows ALEXANDER's postings. }] [p { Again, because there were four columns on the analysis view, ALEXANDER has four postings. The same will be true for the other vendors. }] [p { The running balance of the postings corresponds to the analysis view amounts for ALEXANDER. The amounts posted, i.e. the amount on each posting, forces the running balance to these values, and it is these amounts that can be positive or negative, depending on whether the running (perpetual) balance should increase or decrease between periods. }] [h2 "Re-freshing Consolidations"] [p { You can run an external consolidation any number of times, and each time you run it the transaction amounts, and therefore the amounts displayed on account tables, are simply [qw_term "re-freshed"]. }] [p { The consolidation database is not directly connected to branch databases. Users will continue to perform accounting operations in the branch databases and from time to time you may want to refresh the information in the consolidation database. You can do so at any time by simply running the external consolidation script again. }] [h2 "Analysis View Resolution"] [p { Suppose you change the analysis view resolution from quarterly to monthly, thus changing the number of columns from four to twelve, and then you run a consolidation. The number of transactions on the financial consolidation journal will change from four to twelve and the amounts will change correspondingly. }] [p { If you now change the resolution back the quarterly and run the consolidation, the number of transactions will remain at twelve, but their amounts will be adjusted accordingly. }] [p { Generally, we anticipate that you will set up a resolution and re-use it on a regular basis, and change resolutions rarely. }] [h2 "Clearing All Transactions"] [p { From time to time you may want to clean out the transactions in the consolidation database. For example, suppose you usually set a monthly resolution for a single year but then you switch to daily resolution for a period of three months and run a consolidation. As a result, there will be about 90 transactions. When you switch the resolution back to twelve months there will still a large number of transactions in the financial journal. Although this should not cause any problems, you might like to revert to twelve transactions to simplify the reviewing of transaction activity. }] [p { You can simply position on the financial consolidation journal, mark all transactions in a block, and delete them. When you run the consolidation again there will be twelve transactions corresponding to the twelve analysis view columns. }] [p { As mentioned, the consolidation refreshes and you can run it any number to times. }] [h2 "Consolidation Database Audit Trail"] [p { As mentioned, the consolidation database is a NewViews accounting database like any other. Therefore, all accounting activity is audited and will appear in the audit trail. }] } } /314120060427085649 { .title "Accounts in the Consolidation Database" .id 314120060427085649 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Accounts in the Consolidation Database"] [p { A consolidation only collects information for accounts which are posting accounts in the consolidation database. Total accounts in the consolidation database are simply skipped, even when marked in the block when a consolidation is run. }] [p { This is an important concept and it is a feature, not a limitation. One can think of the posting accounts in the consolidation database as [qw_term "controlling"] accounts for the information gathered from branch databases. Then you can use any totaling structure desired in the consolidation database to organize the information, independent from how it may be organized in branch databases. }] [p { Note that when a consolidation is run you will see amounts change for the posting accounts, but the amounts will also change for any accounts they total to, i.e. the total accounts in the consolidation database. So although information is not gathered directly into the total accounts, they are just as [qw_term alive] as the posting accounts. }] [p { It is important to note that you [bold "can"] collect information from [bold "any"] account in a branch database. It doen't matter if the account is a total acount, a posting account, or an account class. Also note that an account with the same name could be a total account in one branch database and a posting account in another. The consolidation imposes no restrictions. }] [p { As a result you have complete flexibility to collect information at any level of summary or detail from branch databases. For example, if consolidating financials such as an income statement, you can choose to consolidate individual revenue and expense accounts or just collect their totals from revenue and expense total accounts in branch databases. }] [h2 "Accounting in the Consolidation Database"] [p { A consolidation database is a NewViews accounting database like any other. You could perform accounting activity in it or keep any related information in it. }] [p { However, we recommend against doing so. We recommend that you keep all information in other databases such as the branch databases and take the attitude that if the consolidation database were deleted, you would lose no information. }] [p { Any information that you do keep in the consolidation database should therefore be redundant information, obtainable elsewhere. For example, if you have a head office, then a separate database should be used for its accounting activity. You can collect information into the consolidation from the head office just as you do for branch databases, but you should not confuse the consolidation database with a head-office database, or use the consolidation database for head-office accounting. }] } } /314120060426112239 { .title "Problems Encountered While Consolidating" .id 314120060426112239 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Problems Encountered While Consolidating"] [p { When running a consolidation a number of problems specific to a consolidation may occur. We are not calling them [qw_term "errors"] here because they are likely to occur on a regular basis in a process which needs to access external databases [qw_quoted "on the fly"]. These types of problems include: }] [ul { [li { [p { A server might not be running or offering the branch database. }] [p { When accessing a branch database through a server, the server must be running and it must also offer access to the required branch database. }] }] [li { [p { A database might be open with single-user access on another workstation. }] [p { If a database is opened with single-user access by another workstation the consolidation will not be able access it. }] }] [li { [p { An account might not be found in a branch database. }] [p { Knowing your databases, you might expect this. But in any case it is reported by the consolidation. }] }] [li { [p { An account might not be accessible under the security system. }] [p { The user with which a branch database was opened might not have been granted to an account in a branch database under the security setup of that database. }] }] }] [p { While the list above is not all-inclusive, it is indicative of the type of problems that are likely to occur. }] [h2 "Error Report"] [p { External consolidation takes the approach that problems are skipped as they occur and are reported in an error report window at the end of the consolidation. The error report appears only if problems were encountered. Otherwise, a notification window reports that the consolidation succeeded. }] [p { An error report lists each problem and provides a description of the problem that should be sufficient to take correcting action. }] [p { The consolidation still gathers and consolidates all amounts for which problems were [qw_term not] encountered. So the consoldiated amounts will not incorporate amounts from any branch database that could not opened, or any account within a branch database that could not be found or accessed. }] } } /314120060423114327 { .title "Frequently Asked Questions" .id 314120060423114327 .tags {tag_manual tag_user tag_workshop_conversion tag_executive_summary tag_workshop_basic tag_workshop_intermediate tag_workshop_payroll} .body { [h1 "Frequently Asked Questions"] [p { Actually, these questions haven't been asked yet. Nevertheless, they may come up occasionally, and reviewing them can be informative. }] [/* { [h2 "What user should you use for each branch database?"] [p { Which user you use for each branch of course depends on who is running the external consolidation and how they normally open the branch databases. }] [p { Let's identify the person who is performing the consolidation as the [qw_term consolidator]. One would normally presume that the consolidator is someone with a high level or authority within you organization, with access to the information in the branch databases. }] [p { blah blah }] */ }] [h2 "What happens if the consolidator already has a branch database open?"] [p { The consolidator is using a workstation that may already have a number of databases open, including branch databases used by the consolidation update currently being performed. This is not a problem in any way. In fact, it makes sense that the consolidator would have some branch databases open to quickly look up more specific information. }] [h2 "What if a branch database was not properly shut?"] [p { When the consolidation tries to access a branch database that was not properly shut the last time it was used, the database is automatically recovered on [qw_quoted "on the fly"]. }] [h2 "What if a branch database has a different version?"] [p { The consolidation database and the branch databases it accesses must have the same version of NewViews. When the consolidation tries to access a branch database that was last used by a different version of NewViews, no attempt is made to convert the branch database. In this case, an error is appended to the error report that is displayed at the end of the consolidation. }] [h2 "What happens if the same user has a database open more than once?"] [p { Consider the following scenario. The consolidator performs a consolidation and during this process a branch database is accessed under user ADMINISTRATOR. But the branch database is already open, with multi-user access, from a different computer under the same ADMINISTRATOR user. What happens? }] [p { The branch database is accessed and the consolidation proceeds as usual. }] [p { This is no different from way the NewViews already behaves. That is, you can already open a database from different workstations under the same user. This is not recommended practice in general but it can be very convenient in situations where it does comes up. }] } }