FRxBuzz

Waiting for View Subtotals? Here’s how to create a Pivot from Management Reporter

Jan Harrigan CPA — Thu, 09 May 2013 16:30:09 +0000

Looking for Subtotals in MR? Me too. While we’re waiting, here’s how to create a pivot in Excel that’s a lot more work, but a half decent substitute.

Here’s a visual on what Subtotals give you

Currently when you drill down in Management Reporter, you get a list showing the full account structure. Just like what’s shown on the left.

Subtotals functionality will give you the option to see, in a couple of clicks, what’s shown on the right. Want to ask Microsoft to add this? Please vote here.

Meanwhile, I’ll show you how to create an Excel pivot table that looks like this

Using data from a Management Reporter report.

This is the Management Reporter row for this report

I’ve used a wildcard to pull all accounts, but I’ve backed out 9010 which is the account for square footage. Nothing quite like square footage to screw up a trial balance. Segment2 is the natural account in this database.

This is the Management Reporter column for this report

Settings to change in the report definition

Be sure to set the detail level to Financial & Account.

Make sure you mark these two.

Generate and Download to Excel

I’m on the latest rollup, RU5, so the report automatically opens in the Web Viewer. Download to Excel.

Results in Excel

If you don’t have an ‘-A’ tab (that stands for Account by the way), it means your detail level wasn’t set to Financial & Account.

BTW, I’m using Excel 2007 today.

Use the View tab to set the Normal view

And space the columns so you can actually see them.

Add 3 blank columns

…to prepare for the next step.

Parse the Data

I’m going to use Data>Text to Columns to put each segment of the account structure in a separate column. Highlight the data. Click Text to Columns.

Switch to Fixed Width

Aside to AX users—I’ve had lots of trouble trying to parse using Fixed Width because of varying dimension lengths. And your Main Account is in the middle of the string because it’s sorted alphabetically for crying out loud. You’ll probably have to experiment with Delimited instead.

Use the mouse to create break lines

Here I’ve created 6 lines that will break everything into separate segments. Sort of like the scores in a Hershey bar.

Tell the wizard to skip the 3 unwanted columns

Set the hyphen and the blank columns to Do not import column (skip).

Set the columns with the accounts to text

If you set it to text here, Excel won’t think it’s a number and try to foot it or count it.

Results after Text to Columns

Add column headers

Add another column that marries the Account number with its Description

Do that with the concatenate function. Use it to combine the Account in cell B3 with its description in D3. Add 3 spaces in between in double quotes.

Copy that function all the way to the bottom

Delete the blank row or rows at the top

Click anywhere in the data and Format as Table

I picked Light 9. Be sure to check the My Table Has Headers checkbox.

Results of Format as Table

Insert PivotTable

Let this default

Results

Don’t freak out. This is easy.

Don’t drag anything anywhere. Just check 2 boxes.

I know this looks dreadful. But not for long.

At the bottom right, drag the Amount tile over to the Values section

I know—it shows the count and not the amount. I’ll fix that next.

Use Value Field Settings

Select Sum, then the Number Format button

Make these 3 changes using the Number Format button

Results—Account list in a pivot

You can lose the Row Labels in cell A3 by clicking the Field Headers button at the top right if you want.

This looks great, but don’t get too excited yet.

Unfortunately the Account Description field doesn’t quite sort correctly

For instance, take a look at account 6180 Rent Expense. There are two of them. Here’s what to do.

Click the Account checkbox and move its tile above Account Description

Voila. NOW it’s sorted correctly.

Now the fun begins—monkey around with the Pivot

Move tiles around. For instance, put Department in the column.

Experiment. Uncheck +/- and Field Headers buttons. See what you get.

You can also turn off Subtotals on the Design tab

So much more you can do with this. I also like to fool with the Field Settings, found on the dropdown triangle on each tile—you can lose the outline and go tabular, control subtotals, and lots more. So incredibly powerful.

Honestly, though, there’s really no substitute for the look of Subtotals that could be built into Management Reporter. It was beautiful functionality in FRx—lots of oooohhhs and aaahhhhs when I taught it in class. If you’d like to ask Microsoft to add the Subtotals feature to Management Reporter, so you can do pivottable-esque things on the fly, please vote here. You won’t get 100% of the functionality of a real pivot, but it’s a truly helpful feature that’s really needed.

CHEERS! Jan

BEER and View Subtotals

Jan Harrigan CPA — Tue, 23 Apr 2013 21:12:42 +0000

BEER. Now that I have your attention…

It’s KILLING me that the FRx feature View Subtotals continues to go missing from Management Reporter.

This is such an important feature, not only for analysis but also for fixing out of balance reports.

It will make your Management Reporter lives sooo much easier.

Without it, you have to export to excel, parse data, and create a pivot in order to see a summary of your natural accounts.

Here’s a subtotals timeline:

At Convergence 2007, Microsoft said that in 2009, Sierra (what would become Management Reporter) “will have the functionality of FRx 6.7 today”.
At Convergence 2008, Subtotals were in the ‘Not Planned, Looking for Feedback’ list. I gave plenty of feedback :)
At Convergence 2010, Microsoft suggested not migrating to Management Reporter if you needed output to subtotals.
At Convergence 2011, things had deteriorated to the ‘suggestion database’.
In April 2012, in an unpublished document, “We have this as a scenario we are prioritizing for a future release.” Excellent!
At Convergence 2013, I was surprised that things were back to ‘we’re tracking this’ and ‘please use the suggestion database’.

So let’s take a look at the suggestion database. I did some legwork re the Connect site. That’s what Microsoft uses to track suggestions.

Unfortunately I found 4 different entries that discuss subtotaling, and the total number of votes is 37. But this is the one entry of the four that has the most votes:

Vote for Subtotals!

Anybody interested in this—please go vote!

You’ll need a Windows Live id to get into the site. You can only vote once, but as Mark Polino says, “you can enlist your co-workers”.

How cool would it be to band together and see this added to the product.

And I’m not above a bribe—go vote and I’ll buy you a beer at Convergence 2014.

Cheers! Jan

How to List Your MR Report Definitions in Excel

Jan Harrigan CPA — Thu, 18 Apr 2013 17:33:12 +0000

I got this question from my friend and fellow MR consultant recently

“Is there a way in Management Reporter to print a list of report definitions or download to excel or copy/paste to excel? I could create an SRS report, but I haven’t looked at the SQL tables yet.”

Bottom line—no need to monkey with SQL tables.

Here’s what I came up with:

Anywhere in MR, do a Control-O or click the Open icon (love this!)
This opens the Open dialog box to the Report Definitions tab
Highlight all Report Definitions using Shift-Click
Control-C to Copy
Paste into Excel

Management Reporter’s Open dialog box

You can do this on the other tabs as well in order to list the rows, columns and trees.

If there’s one takeaway from this that might be even better than creating lists in Excel, it’s Control-O (that’s a letter, not a zero).

It’s an underutilized FRx-style interface to the MR building blocks.

If you scroll right, you can see lots of information on the report definitions: the date generated and by whom, and the date modified and by whom. Great information when you’re doing some cleanup.

Cheers! Jan

PS. Control-O is the shortcut for File>Open.

Scheduling Reports in Management Reporter—How Easy Is It?

Jan Harrigan CPA — Mon, 15 Apr 2013 18:27:42 +0000

Report Schedules allow you to schedule either groups or individual reports

There was a poll taken in one of the Management Reporter classes at Convergence last month. Turns out, not many people were using Report Schedules.

I’d love to see more of this because it’s so easy and it can keep you from having to wait around while a report generates. Set it up once and your reports just run unattended. Here’s how.

To create a Schedule, go to the Report Schedules section and simply click New.

You can schedule an Individual report or a Report group.

In this example, I’ve created a new Report Schedule for the Monthly Close group. It’s set to run on the first day of every month at 5am.

Save it and give it a name. That’s all there is to it.

Set a schedule to run daily if you want.

Scheduling reports in Management Reporter—turns out it’s pretty easy.

Cheers—Jan

PS. This post is excerpted from Chapter 6 Other Things You Must Know About Management Reporter of my training manual Creating Reports in Management Reporter I—How to Create 4 Foundation Reports.

Why I’m Celebrating the Second Edition

Jan Harrigan CPA — Thu, 11 Apr 2013 20:01:28 +0000

Second edition of Creating Reports in Management Reporter II Released

Last week I finished updating Creating Reports in Management Reporter II—Step by Step Instructions on a Dozen More Reports.

Celebrating in Austin

It took forever, mostly because I just kept adding content in addition to updating every single screenshot imaginable.

So it’s the second edition—now updated through Management Reporter 2012 Rollup 4. I added three new chapters:

Three Report Distribution Options
Using the Report Viewer
Using the Web Viewer

I also expanded the tips & tricks (now there are 22) and added lots of stuff I learned at Convergence as well.

I’m celebrating because it took forever, and now I’m finished. At least until the next Rollup is released.

Cheers—I’m off to celebrate! Jan

My Top 5 MR Takeaways from Convergence

Jan Harrigan CPA — Wed, 10 Apr 2013 21:33:34 +0000

Well it’s been almost 3 weeks since Convergence 2013 in New Orleans ended

I spent lots of time in the French Quarter. In between catching up with old friends and meeting some new friends and listening to music and eating gumbo and beignets and drinking café au lait and sazeracs and wandering around gazing at the architecture and riding a bicycle through the garden district looking for Archie Manning’s house, I somehow found time to attend all of the Management Reporter classes.

French Quarter view from Muriel’s balcony

To condense it, here are my top 5 Management Reporter takeaways:

Emphasis on the new Web Viewer—Microsoft didn’t show the MR Report Viewer at all. Not once. Interesting! Furthermore, I was ~~bitching~~ whining to the folks at the tech support booth about one particular ~~bug~~ issue where a column header doesn’t line up correctly, and was told that it won’t be fixed in the Report Viewer because it doesn’t happen in the Web Viewer. Furthermore, looks like most of the new features coming are Web Viewer related. Device can be PC, phone, tablet, so you’re able to see reports on your iPad. How cool is that. So…the Web Viewer is the future.
Collapse Details—I was drooling over this one. In the Web Viewer you can collapse data between TOT and DES rows to provide a summary and detail report from a single row definition. Wow. I started looking for this, but found that it’s not exactly here yet. It’s supposed to be in Rollup 5 due out late this month. I cannot wait!
Everybody’s going to the Datamart—from talking to people behind the scenes, it sounds like the legacy provider (the direct connection to the GL) will be eventually be phased out and everyone will end up on a datamart. When I raised an “are-you-kidding-me” eyebrow over that, and asked about all the AX datamart issues, I found that AX on RU4 is solid: there are no known issues. GP on the datamart needs RU4 plus a hotfix that was released 3/15/13. So—heads up on this eventual architecture change. Don’t quote me on the timing, but once the datamart has every feature that the legacy provider has, the legacy provider might be phased out within a couple of quarters. Significant—there won’t be bug fixes for the legacy provider.
SharePoint distribution—I saw lots of report links generated to SharePoint document libraries. Click a link with a late model browser and up pops the Web Viewer. Sweet. The user manages the email alerts; he/she sets up and chooses type and frequency.
Report Design ideas—really interesting things like using color shading for different sections of a detail report (like blue for revenue, green for operating expense, etc). So much easier on the user. I saw a really cool one page combination Balance Sheet and P&L. And a fabulous board reporting package that combined MR, Excel, Powerpoint to create a table of contents, budget highlights with a graph, text. There was a Rolling 12 report for 17 entities with varying year ends. Last but not least, there was a customer list with the main account in the columns plus a tree rollup by region.

My biggest disappointment—View Subtotals continues to go missing from Management Reporter. Not to be overly melodramatic or anything, but this one is killing me.

Cheers—Jan

MR User Guide vs. My Training Manuals—Friends or Competitors?

Jan Harrigan CPA — Mon, 01 Apr 2013 21:15:30 +0000

Hey Microsoft released its Management Reporter User Guide in March 2013.

From what I can discern, it’s the PDF format of the Help content that you get when you hit F1 (or Help>Management Reporter Help).

It’s available for download. Nice reference to have, especially since it’s free.

Want to know how this compares to my two MR training manuals? Which aren’t free?

Fair question.

I show you how to write specific reports. Using pictures.

This is the summary Table of Contents from Creating Reports in Management Reporter I:

Big difference from the User Guide. The User Guide is more of a reference and Creating Reports is more of a tutorial.

Sort of like a cookbook—the user guide explains everything about all the ingredients and tools, and Creating Reports gives recipes. Nice to have both.

So, bottom line, these are complimentary products and you can benefit from having both.

I’d say they’re Friends.

Here’s more information on the Creating Reports in Management Reporter series.

Cheers—Jan

Heading to New Orleans for Convergence?

Jan Harrigan CPA — Mon, 11 Mar 2013 14:15:37 +0000

I am! It’s one of my favorite cities in the entire world.

There are lots of Management Reporter sessions to boot. You can go to the Convergence 2013 website, to the session catalog, and do a search on keyword ‘management reporter’. That will pull up the latest schedule.

I took this with my iPhone last April. I love the architecture!

But for a change of pace, I thought I’d tell you about what I love about New Orleans. I started going when I was about 20. Almost didn’t come home! Honeymooned with my husband there. And have been back many times. I always experience something new about this place. The art, history, music, architecture, food, and people make me fall in love with the city every single time I go.

These are some of my favorites. All but one is in the French Quarter. I walk everywhere, and most of these are casual.

Muriel’s—on a corner overlooking Jackson Square. Snag a flyer on the history of the building. It was built sometime between 1743 and 1762 as a private residence, and was partially burned in a fire that swept the French Quarter in 1788. We’re talking 1700′s! After an extensive renovation, it opened as Muriel’s in March of 2001. I think the architecture is amazing! Be sure to go upstairs and check out the bordello room. I usually grab a drink at the bar and head upstairs to the balcony to enjoy the view. 801 Chartres (pronounced ‘charters’ in New Orleans)

Stanley—also overlooks Jackson Square. Small place, chef-driven. One of my favorites for gumbo. Right across from Muriel’s at 547 St Ann

Fritzels—this is the one place I never miss. New Orleans-y Jazz. My brother describes it as cartoon music from the 20s. If Richard Scott is playing piano, don’t miss—words can’t even describe this world class talent. Every single time, I sit there mesmerized, not believing that I get to hear this live. This is a video of Richard playing Jelly Roll Morton’s Black Bottom Stomp from 1925. 733 Bourbon

Evangeline—These guys are sort of new. The classy one of the bunch with brick walls, gas lights, great food, and it’s quiet enough to hear a conversation. We (Harrigan and I) stumbled across them, newly opened, in late 2011. I was chatting with the bartender/owner, asked him about his dogs and cats, and found out he had FIVE border collies! Well, then I knew I had to introduce my border-collie-aficionado brother who was meeting us the next day. I did, and now his art hangs on their walls. If you see gorgeous watercolors of French cafe scenes from the 1920s on the walls, the artist is my brother Greg! 329 Decatur

Café Soulé—These guys are very new; I just found them on my last trip. Parisian crepes and other French in a casual setting. They’re right across the street from Antoine’s. 720 St Louis

Café du Monde—Another can’t miss that’s open 24 hours. Beignets and café au lait. My secret—ask for the café au lait “strong”. It’ll taste like Paris, sort of. 800 Decatur

LaFitte’s Blacksmith Shop—lit by candlelight, this may be the oldest building in the country used as a bar. Music is hit or miss but when it’s on it’s REALLY on (1948 Steinway piano in back for singalong); when it’s bad it’s really bad; don’t order a drink til you know what’s up :) 941 Bourbon

Café Amelie—eat outside in their gorgeous courtyard or inside in the historic brick carriage house. This place has great food and is probably the most expensive of the bunch. I’m not big on reservations, but it’s harder and harder to get in here, so I bite the bullet. 912 Royal

Sazerac Bar—in the newly-reopened famous Roosevelt Hotel. I love a sazerac. It’s the oldest American cocktail and was invented (created?) in New Orleans. The Sazerac Bar is a historic place for sazeracs, and the Roosevelt Hotel is eye-popping; I went just for the architecture. 123 Baronne (not in Quarter)

OK, I’m running out of time, so here’s a quick list of the others:

Lanaux Mansion on Esplanade—in movie Benjamin Button; gorgeous historic mansion bed & breakfast; proprietor Ruth is as fascinating as the history of the house; I haven’t stayed there (yet!)
Acme Oyster House think it’s on Iberville
Coops on Decatur—dive bar with good food
Old Absinthe House at Bourbon and Bienville
Pat O’Briens on St Peter I think (oh there’s an entrance on Bourbon too) the building is so historic and fascinating
Carousel Bar on Royal in the Monteleone Hotel has been there since 1948
Royal Street antique jewelry in shops
Irene’s is where locals go for Italian—side of building is on Chartres
Arnaud’s for dressy Sunday brunch; try milk punch, meow
Arnaud’s French 75 bar
Camellia Grill on Chartres; great food, fun servers, casual

On a more cautious note, New Orleans is not exactly a safe place, and the French Quarter is no exception. Be careful! If you don’t believe me, google ‘french quarter crime’. It’ll knock your socks off, and not in a good way.

There’s so much more here! Virginie Amélie Avegno was born in 1859 and raised in a 3 story house in the Quarter; I think it’s on Toulouse. Who’s she? She’s better known as Madame Gatreau, the subject of John Singer Sargent’s Madame X painting that caused such a scandal in Paris in 1884.

So many more stories like this! I hope you enjoy New Orleans as much as I do!

Cheers—Jan

Management Reporter—Got Day of the Week in your Title?

Jan Harrigan CPA — Tue, 26 Feb 2013 20:35:27 +0000

Got the day of the week showing up in your financials? Here’s how to lose it.

Thursday looks strange, doesn’t it

I’ll show you how to get rid of it.

First I want to look at the header in the report definition

The @DateLong is the culprit. You might think that changing it to something different might be the way to go. That’s not a horrible guess, but there’s a better way.

The best answer is to change the “Long Date” settings in regional options on the server.

On the server, go fool with the Regional settings

Open the Control Panel, then go find the Regional settings. (Yours may not look exactly like this.)

On the Formats tab, take a look at the Long Date. The format (with the day of week) looks exactly like the date in the report header. I’m going to change that by clicking Customize this format.

Choose the Date tab in the Customize dialog

In the dropdown, switch the Long Date to MMMM dd, yyyy.

Now you should be good to go

Back in Management Reporter, when you regenerate the report, the date should look lots better.

Cheers—Jan

Crying my Eyes out over RU4

Jan Harrigan CPA — Fri, 01 Feb 2013 19:15:33 +0000

Late last week I downloaded RU3 (Rollup 3) that updates Management Reporter 2012. I had lots of trouble downloading it. Chrome doesn’t work on the Microsoft File Transfer Manager, but I sort of expected that. I expected IE to work. It didn’t. It took hours of fooling around updating Flash and configuring popup blockers and all that jazz. I nearly called the whole thing off. But I write Management Reporter training manuals and have to keep my internal installation of MR updated. But I finally figured it out—I switched from 64-bit IE to old, slow, long in the tooth 32-bit IE. That finally allowed the file to download. For crying out loud! All in all, an insanely frustrating day. But I digress.

So this past Monday, I bit the bullet and somehow used this download to successfully update my own install of Management Reporter. Trust me when I say I’m no installer, so this was quite an accomplishment. I breathed a sigh of relief. Turns out, it was premature.

On Tuesday, I found out that RU4 had been released on Monday.

I may have sort of cussed a blue streak at first. Then I dried my eyes, downloaded it and installed. Done in a fraction of the time of RU3.

The gist of this post—RU4 (Rollup 4) for Management Reporter 2012 is out! There are bug fixes and performance improvements and new features.

And Microsoft is doing smaller more frequent releases, so I won’t be overly surprised (this time) to see an RU5 pop up sometime soon.

Cheers! Jan