Reporting in Dynamics GP

I promised my slides from SQL Saturday Jacksonville on Reporting in Microsoft Dynamics GP and here they are. I'll add a permanent link in the Downloads area.

Thanks to everyone who attended both my session and SQL Saturday. I had a blast and I hope you did too.

Don't forget to check out the free SQL Saturday tweener days between the two weeks of Tech Ed.

MSDynamicsWorld Article: Know Thyself and Thy Spending Patterns

My latest column: The Dynamics GP Commandments: Know Thyself and Thy Spending Patterns is up at MSDynamicsWorld.com. I take a look at using Dynamics GP to generate internal metrics to provide objective measurements of volume in your accounting department. This can give you huge insights into your staffing needs and performance along with providing objective measurement for raises or terminations.

If anyone is interested in the underlying scripts that we used, shoot me and email or leave a comment and I'll look at providing them.

SQL Code - SOP Document Lock

Courtesy of Michael at MBSGuru, this script will tell you which users have a Sales Order Processing record locked. In other words, who's in using sales order processing or who has an orphaned record that is still locking a SOP transaction.

l.row_id,
t.dex_row_id,
t.SOPNUMBE,
a.USERID
from tempdb.dbo.Dex_Lock l
inner join SOP10100 t
on l.row_id = t.dex_row_id
inner join DYNAMICS.dbo.Activity a
on l.session_id = a.SQLSESID

Michael was kind enough to post this to the Newsgroup in response to a question and he's letting me share it here with the rest of you. I'll add it to the script downloads on the right as well.

Update: I had the wrong link for Michael's site. It's fixed now. Mea Culpa.

A Whack at My Ego

The Chic Geek's latest post threw me for a loop. She posted about built in views in GP that would allow straightforward queries like:

Select * from employees.

No, it couldn't be. But sure enough, there's a bunch of views in GP 10 with friendly names like ItemQuantities. These views are NOT present in my GP 9 installation but are present in Chic Geek's v9 install. My GP 9 installation has been through numerous upgrades and I can't be sure that whatever would install those views was installed. My GP 10 is a vanilla install of everything on CD1.

The Chic Geek speculates that these may be Smart Tag related and I doubt we installed the Smart Tag parts with our original 7.0 install. Which means we wouldn't have upgraded them through 7.5, 8 and 9. Certainly the views are not Smartlist related or they would be in my v9 install as well.

This is a good thing for basic reporting. Certainly views aren't as fast as Stored Procs but they will make report building easier for lots of users. Also, a view should be faster than doing a bunch of joins from within Crystal or SSRS, both in terms of building and running the report. Perhaps now we will finally be able to tell people how to easily find data for reporting. Wouldn't that be cool!

Microsoft Business Intelligence Virtual Conference

WindowsIT Pro and Microsoft are holding a Business Intelligence virtual conference on October 4, 2007. The conference is completely on line so travel isn't an issue. It is the same day as the GP Roadshow Orlando visit so I'll miss the BI conference but if you have an interest in extending Business Intelligence to your GP solution, take a look at the site.

If anyone makes it, I'd love to hear how it went. It seems to be targeted toward the very technical side not some kind of management overview.

In a wicked twist of fate, the site wants to run a Quicktime (Apple) Active X control. Doesn't MS have a standard "thou shalt not use (or at least expose) our competitors products when we co-sponsor events" clause? You can bet Apple's not using Live Meeting for web presentations!

Weekly Dynamic: Report Lists in V9

Have you ever scrolled through all report windows for a particular module looking for that special report that you know is there but you just can't find it? Something like Historical Stock Status which is under Activity instead of History.

Well in v9 you can now search for a report, including all the options you have set for various reports. Select Reports-Report List and a box will come up with all the reports in the system. You can limit this list to various modules, search for reports and sort the list on the different criteria. From the Actions menu you can create new report options, review the option selections for a particular report and even import/export reports.

Weekly Feature: Account Rollup

I stumbled on this little gem in v 7.5 so I don't even know what version it was added in to. If you need to look at groups of accounts this is the inquiry for you. You can even perform calculations!

What is it?
Account Rollup is an inquiry that you build which allows you to see different GL accounts rolled up together. Additionally, you can perform calculations on those accounts.

We use this for all kinds of things like:

• What's the Fixed Asset total across all divisions?
• What's the commission allocation based on sales?

Those kinds of things. Yes FRx will do this, but freqently, I don't want to launch FRx and run financial statements just to get a breakdown of my FA accounts. So how does this work?
In Dynamics GP, select Inquiry->Financial->Account Rollup
Enter an Option ID, tab and click yes to add the option.
You set the number of columns you want to see up in the right side. If all you want is totals, one column is fine. For calculations, use at least 2 columns.
In the center, you set the types of columns, but we'll come back to that. At the bottom, you add criteria like accounts, departments, divisions, etc. UNLIKE OTHER GP criteria areas, you can multiple independent criteria here. So if you want to see Chicago and New York but not LA. You add Chicago, then add New York.
Save and click redisplay. Controls on the right let you see net change or period balance totals. If you want to see what makes up the total number, select a month and click the blue column header.
Now, to get really fancy, you can ad calculated columns to a rollup. Simply create or modify (with the button) a rollup.
Change the type from Actual to Calculated. In the Selection column, click the More Info button. In v8.0 this is a little arrow.
Back to my commission comment earlier. Let's say, I accrue 8.9% of sale as a commission accrual. My first column is my monthly sales. So I'll pick the only available column in the column box and hit the double arrows (>>) then add the multiply sign (*) then in the constant box I'll add .089 and hit the double arrows (>>).
You formula now looks something like C1 * 0.089. Save and refresh and you get a report with sales and the commission allocation for each month.
This is great for all those estimates you do every month. You can just take the numbers and do a JE.