Before Simplify:

(…continued for Companies 3 & 4)

After Simplify:

So we went from many reports down to 5, and there are actually MORE options in the After shot because any company can run all reports. Here’s the gist:

• We ended up with 1 balance sheet, 3 flavors of P&L, and a trial balance—and any company can run any catalog.
• If all companies use the same chart of accounts, they can probably share catalogs.
• By default, FRx won’t print an account with a zero balance, so your row format can include accounts from multiple companies.
• You can change a report’s company by using the Company dropdown box just below the Catalog ID.
• Or you can add a tree and run all companies at the same time.
• You can also add a departmental tree as needed. Or not.
• There’s usually no benefit to adding the year in the catalog (as shown in the Before picture above).

When should you not combine catalogs?

• If you’re using FRx Launcher, then you might have to have separate catalogs. Test before making changes.
• If the companies are in different industries, all bets are off: bite the bullet and use separate building blocks and maintain separate reports.
• Maybe if you’ve got lots of FRx users…just depends on circumstances.

You can also streamline and reduce the number of reports by effective use of reporting trees. I’ll post on that soon!

In this post I’ll give some real world examples of simplifying FRx totals and calculations. I’m using Before and After screenshots, so these are very practical examples. Without further ado, let’s dive in!

Simplifying FRx Totals

This first example is a balance sheet from the FRx Demos. Yes, even it can be simplified. The original is on the left and my simplified version is on the right. I couldn’t resist getting rid of that dreadful blue (and the italics), but other than that, two formulas are all I changed.

Here’s what I simplified: my TOT formulas include other TOT formulas in their range. This is not a very well known point, but by design TOTs ignore other TOTs. So that means your formula can be just one long range and you don’t have to worry about picking the subtotal row codes one by one. Granted, there’s not a huge benefit in this particular example, but this is a nice timesaver if you’ve got a long row format. I use it every day.

Faster, simpler, easier.

When to be careful? Well, if you’ve got a CAL in the middle of the range, the TOT definitely will pick that up.

Be careful on a P&L too. It’s likely that you’re flipping signs in column E Normal Balance, and TOTs and CALs work with a row after it has had its sign flipped. So if your range includes both C sign flipped rows and normal balance rows, you shouldn’t use a TOT range including both types. For example, if you’ve got sign-flipped revenues and normal balance cost of goods, then you wouldn’t want to use a TOT to pull in the entire range for gross profit. Instead, the TOT formula should be revenue minus cost of goods. Easy enough.

Simplifying FRx Calculations

This next example shows the user jumping through a lot of hoops to get to a total row: first a nonprinting TOT row, then a CAL calculation row based on the totals, then another CAL row based on the first CAL. (And the formulas go way off the page!)

It hurts my head just looking at it—and I do this every day. Can you imagine a new staff person trying to follow this logic?

As it turned out, all that was needed was just a very simple, very easy TOT row.

BTW, the technique used in rows 1063 and 1065 is called ‘column placement’. Sometimes you need to, but don’t do it if you don’t have to!

Here is another FRx calculation simplification. The user in the Before shot is dividing gross profit by a sales subtotal (that’s also being calculated in the formula), and placing the results in columns B and C (more column placement). My tipoff that this could be simplified was that the formulas are the same except for the column letters (B and C).

In the After example, I added a single row to subtotal sales, and decided to use an alpha row code SALES because it makes calculation formulas easier to read. I used the @ sign preceding the row code: this tells FRx to put this calculation in every column.

So there you have it—a few Before and After simplifications. As you can tell, the Afters are so much easier to deal with! There are so many ways to get downtown in FRx. These Befores did give valid answers, but at what price? I think the moral of the story is to ask yourself if there’s an easier way. I hear all the time from people who just instinctively know that “there’s got to be a better way!”

PS. Oh! One more thing I see is using CAL formulas (instead of TOT) to do a simple add or subtract. It’s way simpler to just use a TOT.

First, here are a few settings in the catalog, then I’ll show you the column layout. In the catalog, make sure the Detail Level is set to Financial & Transaction. It’s also a good idea to change the orientation to Landscape (on the Page Setup subtab) and make the margins as narrow as you can get them (.25).

As soon as you generate a report and drilldown on a few accounts to test, you can figure out which of these codes you like and which don’t do a bit of good. Then you can edit the column layout and delete the ones you don’t need. But be aware that the information they provide changes with the type of account you’re drilling into, so it’s good to test on several accounts before you start deleting.

Oh and by the way, these are called T-codes since they, ah, start with the letter T.

Here’s the column layout to use to start with:

Just so you know, the T-codes give different data depending on what general ledger you’re using. And on the finished report, they don’t show up until you drill on the account level data, so don’t freak out when you don’t see them on the report just after it finishes generating. You have to, you know, drill down to get to them.

I like to set up one report in FRx specifically for transaction level drilldown. Transaction level reports take longer to process and heaven help you if you send the whole thing to the printer. For most other reports I leave the Detail Level set to Financial & Account. Give this a shot—hope it saves some time.

So when you generate your report, if you’re missing accounts and activity after what you think is a Page Break, PB is probably the culprit. It looks like FRx is buggy, but it’s designed to work this way. Switch it to PI and you should be good to go.

PB is meant to be used only in a report that contains both a balance sheet and an income statement.

Lamentably, there’s not an out-of-the-box way to add a vertical column break in FRx. You can accomplish the same thing with multiple catalogs but it’s messy and hard to maintain. I usually just export to Excel and breathe a sigh of relief at the print capabilities there. (For what it’s worth, Management Reporter does have a column break.)

Note: If you don’t use Microsoft Dynamics (either GP, AX, SL, or NAV), you can skip this post.

Carried over in migration to Management Reporter

• Company Information, including Company Code, Company Name, Specification Set, Regional Options, and Period Descriptions
• Specification sets, including Report definitions, Rows, Columns, and Trees
• Account sets
• Font styles

Not carried over in migration to Management Reporter

• Company configuration information
• Generated reports (.frd files)—after migrating report definitions, you can regenerate any historic reports that you want to be available in the report library
• Report definitions that were created by using the Microsoft FRx 6.7 Dimension wizard
• Security configuration, including user names and passwords
• E-mail addresses tied to a report definition
• Data related to Microsoft FRx 6.7 features that are not available in MR

My Comments

• The DrillDown Viewer (.frd) files are not migrated, but you can either create PDFs or retain the DrillDown Viewer program to view the files. (Or regenerate in MR as mentioned above.) This is a link to my post on creating PDFs.  And this is my post on creating a PDF binder from FRx reports if you want to put multiple reports together.
• Pre-migration is a perfect time to clean up those spec sets and get rid of or archive reports that you no longer use. Cheaper, faster, cleaner, more efficient for the migration AND down the road. This is the 1st step at any migration I’m involved in.
• Migrate data before you create new building blocks.
• This sort of goes without saying since I’m preaching to the choir here, but you’ve got to generate the new reports and compare them side by side to the old FRx reports to ensure the numbers are identical. Depending on the situation, I often do this in Excel to ensure nothing gets missed. It’s less dramatic and exciting than having hard copies with tick marks, but it’s still nice to have an electronic proof.
• There most likely will be new reports that will need to be created due to some functionality that’s in FRx but not in Management Reporter. It may just be a redesign is needed, hopefully making the report simpler and easier to follow.
• Last but not least—hang on to your FRx installation for a while!

(Launcher was technically designed to allow non-designer users to run reports. Most folks don’t want the CFO in there fooling around with the designs, right? Give them Launcher and allow them to generate reports all day long without touching the FRx Designer.)

But another great use is having it run multiple reports for you. Here’s how. Open Launcher—it is a separate program that installs in the FRx program group, so go to Start>Programs>FRx 6.7 and you’ll see Report Launcher.

Open it (you don’t have to close FRx Designer), select the appropriate Company you want to view, highlight multiple reports using the Shift or Control keys, and click Generate Report. It will ask you for login credentials; enter these and the reports kick off. There are exceptions, I know, but these generate quickly. And it’s great to run them unattended so you can move on to other things while they are processing!

This is a screenshot of all the balance sheets and cashflows to be generated. I used the Shift key (you can also just highlight and drag). I could have also used the Control key to add additional reports to be generated:

Launcher lists only the reports for that one company, so the list of reports could very well look different from your list of reports in Designer. Repeat for other companies if necessary.

I like to use Launcher to generate every report at the beginning of a day, then when I go into the catalog, each report has already been updated. And using the Drilldown Viewer I can print, or just view, without having to regenerate. As always, it’s a great idea to make sure all your reports have a footer that contains the date and time so you can tell at a glance when the report was generated.

Just as with chaining, there are a few things to pay attention to:

• Report date: all catalogs need to have the same report date
• Output: if output is Printer and your Detail Level is something other than Financial, you’re going to get reams of paper. I like keeping output at Drilldown Viewer and Detail Level at Financial & Account so you can drilldown.
• Tree Options: in the Report Options tab, Tree Options tab, if Select units at run time is selected then your report won’t generate automatically without stopping to ask you which units you want to select. I often pick ‘Include all units’.

Hope this gets you out of the office sooner!

I attended his 50 Tips session at Convergence last year and it was PHENOMENAL! Excellent tips, tricks and shortcuts for GP users. So if the new book is anything like his session, it’s pure gold. Per Mark: “If you have liked any of the 50 Tips Presentations this book is for you.”

This is his post on the book entitled Microsoft Dynamics GP 2010 Cookbook.

And the permanent link to his GP blog can be found on the right hand side of this page under Resources.

Enjoy!

This is what I hear from MBS:
“For New or Existing Customers of Dynamics GP who do not yet have FRx, additional FRx may not be licensed, unless 1) they need a feature not offered in Management Reporter, and 2) they sign a written exception through the business desk by working through the partner’s PAM.”

For AX and SL, the integrations from Management Reporter will be available in the fall, so that’s probably when FRx will be removed from their price lists. The integrations will be for the newest versions of both AX and SL. NAV integration comes later.

Meanwhile, it would be good to put a tickler on your calendar so the deadline for AX and SL and NAV doesn’t slip past. You know how time flies. {smile}

• 64 bit—native
• Back end completely rewritten—brand new .NET and C# code base replaces FRx’s VB6
• SQL Server instead of Access—way more stable
• Improved multi-user capabilities
• Report Library—centralized storage used to view reports and group reports into folders
• Report Viewer—replaces FRx’s Drill Down Viewer
• Undo/Redo—everybody is thrilled with this one
• Formatting and Images—can add logos, and formatting is done from familiar toolbars
• Column Page Breaks—everybody is thrilled with this one too
• XBRL—tag in the rows and generate the XBRL instance document

I am really excited about this product. Safe to say it’s been a long time coming.

May 2010

• Management Reporter version 2 ships in May 2010
• Integrates only with GP
• All new GP 2010 customers receive MR
• Existing customers who upgrade to GP 2010 have the opportunity to stay with FRx or to migrate to MR

Feature Pack 1—planned for 2nd Half Calendar Year 2010

• Integrations to AX and SL
• Currency translation in GP (then MR will pull already translated currency)
• Integration to report on Forecaster data

Feature Pack 2—planned for 1st Half Calendar Year 2011

• Bi-directional text (right to left)
• Report scheduling
• Interactive report viewer
• Drill to Dynamics

Management Reporter 2012

• Adds light budgeting
• Further enhancements

Management Reporter 2012+

• Additional financial reporting, budgeting & forecasting
• NAV integration