FRxBuzz

Learn Management Reporter Faster

  • Home
  • Management Reporter
  • FRx
  • Courses
  • About
  • Contact
You are here: Home / FRx / How To Create a Pivot Table from an FRx or MR Report

How To Create a Pivot Table from an FRx or MR Report

June 13, 2011 By Jan Lenoir Harrigan CPA 2 Comments

A Pivot Table is an extremely powerful feature in Excel, one that is probably underutilized in many companies. It’s just so cool to be able to move ’tiles’ around and analyze your data a jillion different ways. Even though pivot tables can be sort of intimidating, they’re not hard, and today I’m going to walk you through how to take an unassuming FRx or Management Reporter report and turn it into a powerful pivot table.

Unlike most posts, I’m not going to take you through every single step. Instead, I’m going to hit the main things I’ve learned that will shortcut your experience and hopefully shortcircuit frustration.

So here’s the unassuming FRx or Management Reporter report:

wpid2884-media_1307987269303.png

These are year to date numbers for the P&L only for this year, last year, and 2 years ago. You can drill down on these numbers.

Believe it or not, I’ll turn the FRx or MR summary report into a pivot that looks like this:

wpid2886-media_1307988548078.png

And the same pivot table can also look like this, as well as have many other views:

wpid2897-media_1308066452948.png

So next I’ll show you how to get these views and how to set up the FRx or MR report.

The row format in FRx:

wpid2882-media_1307987134136.png

In this example I’m using FRx, but the concepts are exactly the same with Management Reporter. In FRx, you’ll see a range of all P&L accounts, a row modifier /Y, and a sign flip in column E. Same thing with MR, only there’s a separate column in MR for the row modifier that allows you to specify this year, last year, etc.

The column layout in FRx:

wpid2883-media_1307987160177.png

Doesn’t get much simpler than this. In Management Reporter, the Type will be FD (for Financial Data) instead of GL.

The catalog in FRx:

wpid2885-media_1307987304270.png

The main thing in the report catalog is to change the Detail level to Financial & Account. If you don’t do this, you won’t have any detail behind those summary numbers.

Export to Excel and the detail raw data will look like this:

wpid2867-media_1307657032610.png

When you export, be sure to select the Account detail option if you’re exporting from the Viewer. Now the edits start.

Edit the data to add headings and the year:

wpid2868-media_1307657168090.png

Here I’ll add a couple of column titles, but my primary change is to add the appropriate year to each row through the end of the data.

There are a few rows that need to be deleted:

wpid2869-media_1307657208374.png
wpid2870-media_1307657228156.png

Delete these rows and the one at the very bottom that totals 2009.

You want to end up with columns of data without any blank or extraneous rows (like headings and totals).

Oh yeah—the F4 key is one of my Excel favorites: it repeats the last edit.

Take a look at the account number and description in column B. There are 4 components in column B, and I’m going to separate each one into its own column:

wpid2872-media_1307658224698.png

The account number in my database is composed of Account, Location, and Department. I want to put each of these in its own column. Same for the account description. Here’s how:

  1. The first step is to insert 3 blank columns to the right of the account-description. The number of columns to insert will depend on the account structure. In this case, I need a total of 4 columns (account, location, department, description), and I already have one (column B), so I’ll add 3 more.
  2. The next step is to highlight the data in column B. This is the data that you’re going to separate.
  3. Select the Data tab.
  4. Choose Text to Columns. This is functionality that lets you separate one Excel cell into separate columns.

The Text to Columns wizard defaults to Delimited, but be sure to choose Fixed width:

wpid2873-media_1307658413876.png

The Fixed Width option will allow you to manually choose where the column breaks should go. In this case, it’s way easier than Delimited.

Use your mouse to click to create column breaks:

wpid2874-media_1307658440998.png

In this example, I’ve used my mouse to click to create 7 lines, giving a total of 8 columns. (I’m not going to ‘import’ all of them though.)

The next step in the wizard allows you to skip columns and to set the type:

wpid2875-media_1307658483610.png

These are the 4 columns that I set to Do not import column (skip). They contain the extra spaces preceding the account number, the hyphens separating the account numbers, and the extra spaces preceding the description. So they are definitely not needed.

By the same token, there are 3 account number columns, and I set them to Text. They default to General. If you change these to Text, you will thank me later when they magically drop into the pivot table in exactly the right place.

I let the description column default to General because Excel will already know that it’s text, and it saves me a click or two.

(An aside to Excel gurus—sometimes I parse this using the Excel functions left, mid, and right, but Text to Columns is easier to understand and less prone to errors for those who’ve never done this before.)

The data after Text to Columns. Cool, huh.

wpid2871-media_1307658094885.png

I love Text to Columns.

Add column descriptions:

wpid2876-media_1307658670753.png

I’ve added the column headings as needed for the 4 new columns.

I like to Format as Table before moving on to create the pivot:

wpid2896-media_1308002499926.png

Click anywhere inside the data, then Format as Table.

Excel automatically chooses the data for you.

wpid2877-media_1307658931802.png

Be sure to check My table has headers. It might be a good idea to check to see that all your data is included, because if there are any blank rows, you need to get rid of them before continuing.

Under Table Tools, Design, Quick Styles, I have None selected. That’s why the rows don’t have alternating shading like tables usually do.

The data formatted as a table. Ready for the pivot!

wpid2887-media_1307991251689.png

Click anywhere inside the table:

  1. Select the Insert tab
  2. Choose PivotTable

I usually let this default and just click OK:

wpid2878-media_1307659002509.png

Your range is already selected, because by default Excel uses the table you just created.

This is where most people get intimidated:

wpid2888-media_1307991424682.png

For the time being, I’m just going to click all 6 Fields and just see where they land:

wpid2889-media_1307991873575.png

Watch where the ‘Tiles’ appear at the bottom when you check each field box.

Remember when I did Text to Columns and marked those account numbers as text? Well this is where that pays off. Excel sees them as text and puts them under Row Labels.

Tip: Most people find it far easier to move Tiles in the bottom right hand corner than dropping data fields onto the pivot table itself.

Notice Excel also sees my Year column, thinks it’s a number, and automatically sums its value. That will be my first change.

In this screenshot, I’ve dragged Sum of Year over to top of the Row Labels:

wpid2890-media_1307992137915.png

So now I’ve got a great start to a pivot table. But there are a couple things that drive me crazy about this, not the least of which is the Total column without any commas. I also don’t like all the subtotals—too messy.

First I’ll turn off subtotals:

wpid2891-media_1307992369972.png

Take a look at PivotTable Tools—on the Design tab, there is a Subtotals icon. I’ll choose the option Do Not Show Subtotals. I also turned off Grand Totals on the icon next door.

Then I’ll turn on commas:

wpid2879-media_1307659606243.png

Click one of the numbers in the Total column, right click and choose Value Field Settings.

wpid2880-media_1307659625581.png

Click the Number Format button.

wpid2881-media_1307659646386.png

I made 3 changes here. I changed from General to Number, checked the box for Use 1000 Separator, and I changed the format for negative numbers.

This is starting to shape up:

wpid2892-media_1307992632827.png

What a relief—the amounts have commas and 2 decimal places.

Note that my example is far more “scrunched up” than normal due to blog space considerations.

Finishing touches—apply a style, and make the first pivot:

wpid2893-media_1307992940624.png

On the PivotTable Tools menu, on the Design tab, in the PivotTable Styles section, you can choose any Style you want. I chose Pivot Style Medium 9 for this one.

Then I grabbed the Year tile from the Row Label section, and moved it over to the Column Label section. And the result is the pivot that I showed at the top of this post.

But the possibilities are mind boggling!

Here’s one more pivot:

wpid2895-media_1307993339104.png

I’ve filtered the year—I only want to see 2011.
I’ve filtered the location—I turned off a couple of locations without data.
I’ve unchecked department—don’t want to see it at all.
I’ve moved the year back to the Rows.
I’ve moved the Location to the Columns.

Voila!

wpid2894-media_1307993308081.png

That’s enough for one day, but I hope you can see the amazing possibilities of how you can drag and drop and look at your data in so many different ways. All from a very simple-looking FRx or Management Reporter summary report with drilldown to account detail enabled.

One other thing—those in IT might wonder why I don’t go straight to the ERP from Excel. I started with FRx (or Management Reporter) because it is already linked to the ERP and all I had to do was just pull the full range of P&L accounts using a very simple report. I don’t have to worry about configuring ODBC, what tables to use, what fields to pull, how to link the tables, right joins, left joins, inner joins, and so on.

Enjoy your pivots. Cheers—Jan

Filed Under: FRx, Management Reporter Tagged With: Excel

Comments

  1. William Kernan says

    December 20, 2011 at 4:36 pm

    Hello Jan,

    Discovering this article made my day! I have been asking our IT dept. to update excel in order to use pivot tables they way you taught in this article.

    Happy Holidays,

    William

  2. Jan Harrigan CPA says

    December 21, 2011 at 8:43 am

    Thrilled to hear it!

Leave a Reply

Your email address will not be published. Required fields are marked *

"I'll teach you the simplest & fastest way to use M-R and still wind up with accurate reports."

Search—try this 1st!

View Cart

View Cart

About

Hey! I'm a CPA and I specialize in Management Reporter and FRx. [Sometimes with a side of snark.] I've been doing this for 22 years (yikes). But when I'm not working I can be found reveling in live music & pizza trailers at home in Austin Texas! —Jan Lenoir Harrigan More

Learn Management Reporter

Why fight with Microsoft's manual? Life is too short as it is.

My first manual—4 foundation reports:
covers-i-2d
My second manual—12 more reports:
covers-ii-2d
My third (and hopefully last) manual—cashflow:
covers-iii-2d

Connect

  • Facebook
  • LinkedIn
  • RSS
  • Twitter

Subscribe by Email

Don't miss a trick—subscribe.

All Hat, No Cattle?

Watch out—a few unscrupulous consultants are lifting copyrighted original content from several sites, this one included, and passing it off as their own. I'm hacked off and I don't mind naming names. Bottom line—be very careful about whom you hire!

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
—Red Adair

Who I Work With

I work with zillions of companies who run Dynamics GP, AX and SL.

And lots of other consultants.

So I work with accountants who need accurate financials to help close the books. Not to mention present to the Board.

I don't sell or install MR. [How smart am I?]

FRx is Toast

FRx is toast—and I hope you're getting rid of it soon—but just in case someone still needs to learn it:

  • FRx in 8 Hours—Introduction 
  • FRx in 8 Hours—Intermediate

BTW, if you're on Dynamics, you can migrate to MR, but you should know there are other options. Choose what's best for you.

New to MR?

Here are 3 essentials to know before you start.

Background—back in the day, FRx was a famous midmarket report writer from a small agile company in Colorado. Then it got acquired. [Sigh]

Now it's owned by Microsoft. They rewrote it and rebranded it Management Reporter.

The two are very much alike and much of the FRx content here also applies to MR.

Copyright © 2025 Jan Lenoir Harrigan CPA · Protected by Copyscape Plagiarism Checker · Do Not Copy