Converting from Management Reporter or FRx to Jet Reports or Jet Basics

Overview

1. Row Definition

   The first step to converting any financial report to a Jet report is defining the rows. In any financial report, each row generally has a name. Behind the scenes in your GP database, this name is represented by one or more account numbers, natural account numbers (i.e., a segment), or GP categories. If you wish to list just one account or category in each row, you can create dynamic lists of accounts in a range. For example, behind the scenes this report dynamically generates a list of revenue accounts assigned in GP to the Sales category:

   

    

   Similarly, this report dynamically generates a list of main account segments in the range from 5000 to 5999:

   

   In both cases, I can hide the account number columns if I wish. Using dynamic rows based on account segments, main account numbers, or full account strings is by far the fastest and easiest way to write reports.

   However, if each line in your report represents more than one account or category, you may need to manually list the accounts, natural accounts, or categories associated with each line. You should separate individual accounts or categories with the pipe symbol: |. You can also list inclusive ranges using two periods, as in 4000..4999. If your chart of accounts (including all your segments) uses only numbers and not letters, the filter range will usually obey a numeric sort order.

   If your GP database chart of accounts is set to allow alphanumeric segments, then your filter ranges will reflect an alphanumeric sort order rather than numeric sort order. If you use only numbers in your chart of accounts and would like to be able to use a numeric sort order, you can contact your GP consultant and ask them to update the data types in your chart of accounts.

    

   If you are using account strings, your list of row conditions may look something like this:

   
   

   After you build the account, you'll hide this column

   If you are using only one segment, it may look something like this list of main account segments:

   

   These data should be entered using the filter syntax that Jet uses, which is described in detail here

    

   Again, if you are filtering on a list of accounts, you should separate each account using the pipe | symbol, not &:

   

    

   Here is an example of converting Management Reporter row definitions into Jet filters.  In the examples shown below, you list each segment separately in its own column:

   

   However, some Management Reporter syntax will require either two lines of calculations in the Jet Excel add-in, or require building the report using full account strings:

   

   Or...

   

   Another solution is to simply list out each individual account on a single line.

   In the above example, that would mean listing something like

   "(000-5100*..000-5170*|100-5100*..100-5170*|200-5100*..200-5170*|300-5100*..300-5170*|300-5100*..300-5170*|..

   Note that you may get an error if you list an account number that does not exist in the database.

    

   Be aware that what might seem like an intuitive range when using the full account string, may not work as expected.  The range 000-1100..100-1170 includes 000-2000*, 000-5000*, on up to 000-9999 before getting to 100-1100:

   

   To avoid this, specify ranges within each Segment 1 value, as below:

   

2. Column Definition

   Once you have defined the rows in your report, you will want to go on to define your columns. Each column commonly represents a date range (month, period, YTD, QTD), an account segment (such as a department or entity segment), or a separate GP company. As with rows, you can list each column manually or generate dynamic columns in response to a Jet query. Our pre-built Jet Snippets include many commonly used pre-built column date definitions.

3. Sheet Definition

   Finally, you may wish to have a report with more than one sheet. As with columns and rows, you can manually create reports on separate sheets or dynamically replicate a “template” sheet for each item in a list. Report writers most often create dynamic sheets for each value in an account segment representing an entity, department, or location. Or they create new sheets for each GP company. Bear in mind that replicating many sheets (more than 5) can slow your report performance.

4. Optimizing for Performance

   Jet Reports are almost infinitely flexible in how they are written. However, any Jet report represents a trade-off between presentation and performance. As you make the transition to a new reporting system, take this opportunity to ask how the structure of your reports can best support the goals of your organization. Instead of blindly recreating prior reports, consider making small or large changes that will speak to how your financial reports are used in practice. For example, do you take a Management Reporter report and then copy and paste information into a custom format? Jet is designed to automate these final steps: embed Jet queries into the final report in the format used when you hand the report off to the report consumer. Equally, do you need to see every department on a new sheet, or do most department heads only need to see their department’s numbers, and possibly a consolidation sheet?

   • Less queries means faster reports, so where possible, don’t build all your financial reporting as one big Excel workbook.
   • Use the Jet Hub to organize reports into folders and schedule them to run automatically.
   • Allow users to select from a list of options and run reports only for the information they need.
   • Where possible, avoid building Jet reports with more than two or three sheets of Jet queries.
   • Re-think query syntax so that you consolidate or eliminate hidden rows.
   • Where possible, use replication rather than building reports that hard-code each line.
   • Update to the most recent version of Jet to take advantage of Jet’s recent speed improvements and “run in background” feature.