Weekly Feature: Macro Basics

In honor of Halloween, our featured item this week is something scary to many people, MACROS. (insert scary Halloween laugh here.)

A macro is a function that allows the automation of certain events in a program. In Great Plains, macros can be used to take out some of the annoying parts. For example, when we transfer money, we always transfer it one way, from Money Market to operating. Everything is the same but the dollar amount. So, I build a macro to fill in everything but the amount. Now I just run the macro, add an amount and I’m done.

Building a macro in GP is easy but somewhat tedious. Remember though, you only have to build it once, you get to run it over and over again. A GP macro works the same way as if you were sitting there keying in the info. That’s important thing to remember as you build macros.

To start, select Tools->Macro->Record. Name the macro TESTand remember where you saved it. Pick Transactions->Financial>Bank Transfers for our example.

Key in everything but the amount to transfer. Click Tools->Macro->Stop Recording. You’ve now recorded all that work.

Push clear and close the window.

Pick Tools->Macro->Play. Find your macro file and click open. Close the box telling you how long the macro ran. You now have bank transfer waiting for your amount. To finish the transfer, enter the amount and click post.

Adding the macro to the shortcut bar is the easiest way to run a macro and it gets rid of the run time message.

There’s more you can do with macros and we’ll cover the advanced stuff another time. You can also learn more in the Great Plains help files.

Dynamics GP 9.0 Partner Tour

Microsoft is launching a partner tour in advance of the Dynamics GP 9.0 release. If you’re a partner (I’m not, just a lowly user), details are here. Even if you’re not a partner, the website gives some clues to what’s in the 9.0 release.

Weekly Gotcha: That Little Security Checkbox

Lots of people know this but it will still get you. You MUST check the Security checkbox in Tools-Setup-Company-Company if you want security to be active. Otherwise, all that security work you did is irrelevant.

What is this good for? Well, you might turn security off for a test company to allow freer testing. Or, you might want security off while you build some initial settings. Generally, though you want this turned on!

Microsoft Business Solutions Getting Closer to Profitability

Via Microsoft Monitor:

Microsoft Business Solutions revenue was $181 million, compared to $156 million a year earlier. The operating loss decreased from $31 million to $12 million, year over year. Sequentially, revenue was $247 million, with an operating loss of $76 million. During the quarter, the division released Office Small Business Accounting and announced the new Dynamics brand. Microsoft’s second-quarter MBS revenue projection is for growth of 11 percent to 13 percent.

Looks like MBS is getting closer to profitability. Certainly it was a nice decrease in the loss quarter over quarter.

SQL Server 2005 Released to Manufacturing

SQL Server 2005 has been released to manufacturing today. The public launch date is November 7. My understanding is that Great Plains SP4 will support SQL Server 2005. Do you want a free copy of SQL Sever 2005? Visit a launch event and get a free copy of SQL Server 2005 and Visual Studio 2005.

Weekly Feature:Excel Subtotals

I’m going to digress slightly with this week’s feature. This is actually an Excel feature, not a Great Plains feature per se but it has huge implications for Smartlist exports, so I’m going to cover it as a feature.

If you’ve never used Subtotals in Excel before, you need to know that I get more jaw dropping looks from people over this feature than for anything else I’ve shown them. Often I get slapped as well because they’ve just finished some long, tedious subtotalling by hand.

So what do subtotals do? When you export Smartlists that are detail lines such as GL transaction, Purchase Transaction Lines Lines, Sales Line Items, Inventory Transactions, etc., you get the details of transaction but often you also need the subtotal of all those lines to trace back to the transaction total. This is where Excel subtotals come in. Figuring out where all those transactions break and subtotaling with a formula is really slow.

I’ll use Purchase Transaction Lines in this example and start by exporting a large number of lines (1000) to Excel. Sort the list by PO Number either in Smartlist before exporting or in Excel after the export. THIS IS AN IMPORTANT STEP. You would actually sort by whatever you want to subtotal on. It could be open vs. closed PO’s, date created, etc. We’ll use PO Number for our example.

In Excel select Data->Subtotals. This pops up a box that you follow through like a wizard.

At each change in: Check PO Number
Use Function: Select SUM
Add Subtotal to: Check only Extended Cost
Click OK

In English: “At each change in PO number, sum the extended cost. Replace any current subtotals and put the summary below the data.”

Your PO’s now have the extended cost for each line subtotaled by PO number. There are little numbers 1,2 & 3 to the left of cell A1. Clicking on these expands and contracts the amount of detail you see. You can see a grand total, a subtotal by PO or the full detail.

Depending on how you sort and filter the Smartlist data you can get different results, for example separating open and closed PO lines from the PO. If you uncheck the Replace Current Subtotals box you can actually create nested heirarchies of subtotals. But that hurts my brain.

Excel’s subtotals overcome one of the few shortcomings of Smartlists. You can’t total stuff in Smartlists, but now you know more about totalling than you ever wanted.

New Toys Coming From Microsoft’s Office Business Applications Group

Via Mary Jo Foley, Microsoft’s Office Business Applications (OBA) group is working on new end-to-end business intelligence strategy. Expect to see the upcoming Office 12 start to really take shape as the BI front end for MBS and other non-MBS ERP solutions.

Also mentioned is Biz#, some type of financial management server, whose specifics are currently under wraps. Plus, due Nov. 1, is Microsoft’s Business Scorecard Accelerator. Another Office family accelerator.

You can read the whole article here: http://www.microsoft-watch.com/article2/0,1995,1876647,00.asp?kc=MWRSS02129TX1K0000535

Website access may be spotty this weekend

I’m thinking of doing some server work on the box that hosts www.dynamicaccounting.net this weekend. Access to the site may be iffy during the weekend and then of course we expect Wilma to visit just south of us on Monday. If the website is down, you can always check the blog at msdynamicsgp.blogspot.com for info.

MBS Scuttles Business Framework

Via Mary Jo Foley and her Microsoft Watch site, Microsoft Business Solutions is scuttling their separate business framework designed to lay over the .Net framework.

In my mind, this is probably a good thing. We haven’t seen great success when the MBS folks stray too far from the mothership. If you remember Business Portal 1.0 before they moved to a Sharepoint framework, it was pretty bad. Yeah, it was a 1.0 product but it was also a technology silo.

Great Plains’ strength in particular is built around developers. 3rd party add ons are available for just about everything. It’s weakness is the underlying Dexterity chasis. Specialized developers can build great apps but the learning curve limits the raw number of developers willing to jump in. Also, you have to maintain and grow the language as well as the app.

From the story, most of the pieces will end up in other MS apps, the new workflow engine, LINQ, etc. The only sad part is the time and energy spent here that could have been used elsewhere, but hey, that’s business.

In case you missed the link up above, the full story is also here – http://www.microsoft-watch.com/article2/0,1995,1875024,00.asp?kc=MWRSS02129TX1K0000535.