Microsoft Dynamics SL, Spitfire Project Management

My Case Study

2009-05-28T10:32:00.000-07:00

I had a Case Study done by Microsoft several months ago, here is a link to it:

http://www.synergybusiness.com/files/PDF/case_studies/construction/Alpha_Case_Study.pdf

John

Moving Data From Dynamics SL to MS Office - Part 1

2009-05-28T09:47:00.000-07:00

Not to sound trite, but probably the most common method of moving data from Dynamics to the MS Office Suite is just the old fashioned manually copying the data.

I have 2 screens and it's amazing how often I use this method. I open Excel in 1 screen and Dynamics in another and then I just manually copy the data into Excel.

It's not eloquent, nor is it difficult, or even esoteric; but it works in the real world.

For instance, there's lots of times I need the correct spelling of an employee's name in a document. Open the document, open Dynamics, and just type the correct spelling. It works.

So as we begin to build a toolbox of ways to get data from Dynamics SL into the MS Office Suite, oftentimes manually copying the data is the preferred method.

John

Dynamics SL User Group Meeting

2009-05-27T14:57:00.000-07:00

I'm back. It's been over a year since I've posted something on this blog, but now I've got a good reason to start posting again - I'm looking for work!

Last Week I attended the Dynamics SL User Group Meeting Sponsered by Synergy Business Solutions. If you've never been to one of these meetings, I recommend that you go (if for no other reason: FREE LUNCH).

Synergy had 5 of their luminaries including Donna Hurst, Cindy Smith, Margo Dame, Michael Camp, and Rob Lloyd. It was great to put names with faces. In addition Microsoft had Jigisha Deb and Eldita Ginger. So if you weren't there, you missed an opportunity to meet some prominent personalities in the Dynamics community (not to mention, they're all great people).

The group expressed interest in learning about some of the following topics:

Integration between Dynamics SL and Excel
Upgrading to Version 7.0
Feature Pack 1 vs. SP 1&2
Moving Customizations from 6.5 to 7.0

We touched on all those topics except moving data from Dynamics SL to Excel. So stay tuned to this blog, where I will try to expose some of the ways to get data into the MS Office Suite.

Donna Hurst talked about Application Server and it's uses. (Note to Donna, don't demo live software). All I can say, if you have Application Server (which I don't), you should be using it.

We also looked at the Convergence Live site at: http://vepexp.microsoft.com/convergence09

I'm warning you, it takes a little while for this site to load, even with a smokin' hot internet connection.

John

Automatic Report Delivery

2008-08-14T17:28:00.000-07:00

Does anyone know of the best way to automatically export different SSRS reports to a single PDF file on a daily basis?

Question from the SQL Server Reporting Services Forum

For all of you that would like to get a report delivered automatically to you by e-mail, then this tip is for you. In SQL Reporting Services, you can subscribe to a report.

All you've got to do is navigate to the report you want to review in Report Manager, and then click the Subscription button and fill out the details. It's that easy.

How I customized Dynamics SL (and made my life easier)

2008-07-29T14:46:00.000-07:00

If you want my opinion, SQL Reporting Svcs (SRS) is the great salvation of Dynamics SL. With SRS, you can do almost anything.

Dynamics SL does a lot of great and wonderful things right out of the box. GL - works wonderfully. AP - pays vendors just like you want it to. AR - Ok, we wish it would go break the knees of those that owe us money (but 2 out of 3 ain't bad).

Also, Dynamics does a great job of warehousing data. If you've got information, Dynamics will be happy to put it you SQL Database and save it forever (or until your server crashes without back-ups). Dynamics does an ok job of retrieving data right out of the box; but with SRS, you can put your Dynamics installation on steroids!

I customized my version of Dynamics so that I could do project management better.

Here are some of the things that I did using SRS to make my life happier:

1.) A CPA-Style WIP schedule that shows jobs vertically instead of horizontally. I always hated those CPA versions that were 2 pages wide. Now, I can look at a single column on a page and see everything I need to know about a job.

2.) A way cool Job Cost Overview that shows the billing information, the job-to-date hours, the original budgeted costs, the revised budgeted costs and the actual costs. It also shows the posted transactions as well as the unposted transactions. Another cool feature is that it shows the Under/Over Billings per phase and for the entire job.

I can look at the job to date, or choose a date range for this information. I live by this report.

3.) Several different project timecard reports. I got tired of trying to manually calculating daily overtime and weekly overtime (our Unions require OT after 8 hours per day). So I created reports that show me where I need to make adjustments in payroll so that my overtime is correct. I also use this report as an attendance report, because it can show which employees don't have time on projects.

4.) B'zillion budget variance reports. I can compare labor, materials, subs, equipment, or any other cost account to budget.

5.) Daily revenue earned report. This baby shows me how much revenue I earned that day, then subtracts the costs associated with it, and then subtracts my overhead to show whether we made or lost money that day.

As you can see, if you're creative, SQL reporting services can change the way you manage your company.

John Wheeler

SRS & Microsoft Dynamics

2008-07-28T12:18:00.000-07:00

If you're like me, you hated most of the reports that came with MS Dynamics. Either, they don't give you the information that you want, they're just plain ugly, or they're cumbersome. I still hate flexible columns, and really haven't ever got them set up to my satisfaction.

So how did I fix the proplem? SQL Reporting Services (SRS) is the solution. I've created almost every report I need in SRS and when I want to run them, I just use the report manager. Now I get good looking reports that show information in a way that makes sense to me. No more of this putting up with whatever Microsoft decided to ship with the product.

One of the other advantages to SRS is that you can subscribe to reports and have them automatically delivered to your Inbox in the morning. So if you want your A/R person to look at the A/R Aging once per week, just create the report and schedule it to be delived weekly. Then forget it about it and go to the Caribbean (at least that's the theory).

So SRS is a big part of how I customized my version of Dynamics to meet my needs. Now I've got functionality that Microsoft didn't even dream of out of the box.

If you want to know how I customized Dynamics, feel free to e-mail me.

John

SQL Reporting Services

2008-07-24T10:30:00.001-07:00

SQL Reporting Services is the greatest thing since sliced bread. I haven't moved to Dynamics 7.0, but I use SRS all the time.

For instance, this morning I have an insurance auditor here. He's looking for some information that's not on a standard report.

THE SOLUTION

1.) jump into Visual Studio
2.) copy a current report
3.) change the query in the data pane
4.) publish the report to the Report Manager
5.) Export the Report to Excel
6.) E-Mail the Report to the Auditor

How long did this take? Five minutes or less

THE RESULT

I look like Superman (without the underwear on the outside of my pants, of course).

MS Project Server Update

2007-03-23T12:15:00.000-07:00

I'm almost done with the MS Project Server connector setup.

All I can say is, "WOW!!!!"

Information from Solomon flows seemlessly into Microsoft Project - it's really cool. I uploaded a picture of MS Project with the profit pane. Hopefully I've done the upload correctly and you'll see a screen shot.

All of the actuals for the profit pane come directly from Dynamics SL, so it's really a great tool.

Later today, I'm going to add some graphical indicators that will show whether we're on-budget at a glance.

Here are a couple of Microsoft Websites you might find helpful:

Planning your Enterprise Project Management Solution Implementaion
http://office.microsoft.com/en-us/help/HA011537871033.aspx

Creating Enterprise Custom Fields
http://office.microsoft.com/en-us/help/HA011537871033.aspx

Project Server 2003 IT Documentation
http://office.microsoft.com/en-us/projservadmin/HA101714291033.aspx

Next Step: Project Server/Dynamics Integration

2007-03-17T11:31:00.000-07:00

The next step we're going to take is to fully integrate MS Project and MS Dynamics SL. Starting tomorrow, we'll be setting up the integration on my IIS server. I'll keep you posted on how it goes.

Thanks Synergy & Steve Toth

2007-03-17T11:27:00.000-07:00

My initial implementation of MS Dynamics/Spitfire is complete. It's a huge project - trust me, and I'm glad that it's over.

When I got to the end of the project, some of my people were still not comfortable with the software, so Steve Toth made a special stop by here, just to make sure that everything is going well. Steve did a little training, and put in place some fixes that we needed.

So I just wanted to say thanks publically to Synergy and especially Steve Toth for making sure that this project worked.

I love BFA Part 2

2007-03-02T11:48:00.000-08:00

This morning, I had a conversation with Dennis Stroud of Spitfire Project Management about BFA. Dennis is the gentleman that did the design work for BFA. I asked him about the difference between Budgeting, Project Analysis, and Forecasting. He summarized it in the following way:

Project Analysis

A snapshot of where you are today, that is current and created everytime you ask for a project analysis.

Budget

In the budget, the actuals are frozen on the date that you create the budget.

Forecasting

Forecasting is predictive. It takes the Actual vs. The Budget and makes some basic projections.

Stay tuned, because I'll talk a little about some of the very cool stuff I've been able to do in BFA.

You're going to love BFA!!!! Part Uno

2007-03-01T16:14:00.000-08:00

What is BFA you ask?

Well, we could probably come up with all sorts of creative uses for the initials BFA, but in the parlance of Spitfire, BFA stands for Budgeting, Forecasting, and Analysis.

In Spitfire Documentation, BFA is referred to Budgeting, Project Analysis, and Forecasting; but you're goind to have to talk to someone smarter than me to find out why it's called BFA and not BPAF, or just BPF. But whatever you call it, it works SLICK!!!! BFA is one of the best reasons to purchase Spitfire - which is saying a lot, because the program has lots of cool features.

As the Controller/Project Account/Chief Cook & Bottle Washer here at work, I never have enough time to do the things I need to do. So one of the things that's always fallen by the wayside is keeping up project budgets and forecasts - it's just one of those non-essential duties (unlike payroll) that can be overlooked.

With BFA it's easy to keep up with Revised Budgets and Forecasts

BFA is probably the easiest thing I've ever seen for Budgeting. What I love about BFA is that it's written in MS Excel! Which is really cool for me cause I have some high and exalted Microsoft Excel Professional Certification - something like Grand Pumba of All Things Excel (or some similarly silly Microsoft conferred title). But it's also really cool for everybody because now that MS Office is the de facto standard, it means that almost everbody knows Excel. Which translates into smaller learning curves - which means I have to do less training!!!

The next cool thing about BFA is that you can link your BFA worksheet to other worksheeets. There's a technical whitepaper on that process, but I'll explain it in Part II.

Spitfire Watchdog Alerts

2007-02-22T15:27:00.000-08:00

Hey Everybody!!!!

Yippee it's sunny here in Tacoma, which makes me HAPPY. Today I've spent most of the day working in Spitfire. Even though I'm the Controller here and in charge of all of the accounting functions, I find it tremendously useful to use Spitfire as a dashboard that gives me a look at most of my accounting functions.

For instance, today was Payroll day here, and I look at my Watchdog Alerts to see how things are progressing with payroll. This works really great!

Unfortunately, it generates at least 40 Watchdog Alerts, so your friend John is going to pass along a little tip to get rid of those nasty Watchdog Alerts that's faster than deleting them one at a time.

To Delete communications from the Project System which show up in Watchdog alerts, delete the lines in PJCOMMUN table in your Solomon application database, or xsfalert in the Spitfire Tables.

How to keep Spitfire Updates from changing your screens

2007-02-15T11:21:00.000-08:00

When you do an upgrade to your Spitfire Software, I recommend that you turn off the Screen Customizations, so that Spitfire won't update those. To do that,

1.) Log into your IIS Server
2.) Go to Start/Programs/Spitfire
3.) Open the Configuration Tool
4.) Select the Dynamics/SL Tab on the tool.
5.) In the box labeled, "Screen Customizations," make sure that "Publish" is unchecked.

This way, when you upgrade Spitfire, it will leave your Dynamics Screen Customizations unchanged, and you won't run into conflict issues like I have.

John

In praise of Spitfire Support

2007-02-15T11:00:00.000-08:00

I hate to sound like a sycophant, but I've gotta tell you this story.

On Tuesday, Steve Sauber (of Spitfire) and I installed an upgrade to Spitefire. It was about 5:00 Eastern Time, so I happened to ask Steve, "so how's the weather?"

To which he replied, "we're expecting a big storm. There's a Noreaster blowing and we're supposed to get snow. In fact it's starting to snow right now. I want to get home before the blizzard starts."

I wasn't that concerned about a snowstorm in the Northeast, because I didn't figure it would affect me. Boy was I wrong.

I get to work on Wednesday morning, and for some reason we can't process payroll. We figure out that it must have something to do with Spitfire customizations, but can't fix it ourselves. So I call Spitfire.

I reach someone at Spitfire, who informs me that they are without power at the office. The computers have already powered themselves off and the phones have less than an hour left on battery backup, so they're limited in what they can do.

I told him, "I've got payroll to run today, and I think that the upgrade we installed yesterday is causing problems. Is there anything you can do?"

He said, "Give me a while and I'll see if I can't get someone to call you back."

Pretty soon, I get a call from Stan York, the Head Programmer at Spitfire, and a guy that I've always wanted to have an opportunity to sit and chat. Stan couldn't get to work that morning so he called me from his phone and was using his laptop to remote into my computer.

Stan quickly diagnosed the problem as a conflict between one of our user's customizations and Spitfire's customization, and he quickly fixed the problem. In the meantime, we got to chat about a lot of great features of the program.

I wanted to relate this story to you, to let you know that the Spitfire Team is very responsive to their customers and their needs, even in the middle of a winter snowstorm and power outage. And they went all the way to the top to get my problem fixed so that we could keep running.

Spitfire Customer Support is The Best

2007-02-09T15:51:00.000-08:00

http://www.spitfireconstruction.com/

I get a lot of phone calls from potential Spitfire customers asking questions about the software. The most asked question, "how is customer support?"

Spitfire probably has the best customer support of any piece of software I've ever used. If you call with a problem, they can usually fix it right there, but if they can't they get right back with you.

One of the nicest things about dealing with Spitfire is that they can easily and quickly remote into your workstation or server and fix a problem if that's what they need to do.

I deal with Dorothy McGovern and Steve Sauber, both of whom are excellent and very knowledgable. And they've done nothing but bend over backwards to make the software work for me.

John

T-SQL Statement to Add Report to Screen File

2007-02-06T11:12:00.000-08:00

Here is the code that will add a report into the Screen Table in your system files.

You will need to change the information in the VALUES statement to your information.

I ran this in the Query Analyser for SQL Server 2000, and it ran fine. Just remember to select your Dynamics System Database from the drop down box on the menu bar.

INSERT INTO Screen(Module, Name, Number, ScreenType)
VALUES('PA', 'Equipment Rates', 'PA997', 'R')

PNB

2007-02-05T08:43:00.000-08:00

Ok, this has nothing to do with Dynamics or Spitfire, or even this blog, but who cares, it's my blog and I'll blog what I want.

Saturday night my wife and I went to the Pacific Northwest Ballet to see Swan Lake. We are season ticket holders and I was really looking forward to seeing a classical ballet.

Saturday's performance was especially special because Patricia Barker danced the dual lead role of Odette/Odelle. As always, Patricia was magnificent! But what made it all so very special is that Patricia is retiring from PNB after 26 years.

It was great to see one of Seattle's greatest athletes and artists in one of her final performances. Thanks so much to Patricia for all of the joy and beauty you have shared during your career. You are loved and will be missed.

Dynamics SL Reporting (where ROI is not ROI)

2007-02-02T11:08:00.000-08:00

If you have a degree in Finance, get ready for the jolt of your life. In Dynamics SL, ROI (Return on Investment) has been changed to ROI (Report Options Interpreter). Microsoft (aka god of all software), has changed what the initials stand for, so don't fight it - just deal with it.

Since Dynamics rollout, I've been working on creating custom reports. In the process, you need to learn about the ROI and Crystal Reports. Personally, I've always hated Crystal, and for the past couple of years, I've avoided working with it; kind of like I avoid jumping off tall buildings.

And if you're like me and are used to a Microsoft Report writer like the one in MS Access, let me forewarn you, Crystal will have you crying like a baby and running to Momma for comfort. Crystal is just not as easy to use as the stuff from Microsoft, which is probably why Crystal is not the "god of all software."

Creating a Custom Report is difficult. Here are the steps:

1.) Create the Report in Crystal (try not to lose your religion by cussing the aggravating program).

2.) When you've got a warm fuzzy that all is good with your report, save it in the Usr_Rpts folder in the Solomon directory.

3.) When you save it, make sure that you follow the naming convention for the module you'll be working with. For instance, I've been creating reports for use in the Project Controller, so my reports begin with PA (i.e. PA994.)

4.) Edit the text file "Delta" in the Solomon folder. BE SURE TO BACK UP THE FILE FIRST before editing it. I don't know what happens if you corrupt the Delta file, but I'm sure it's NOT GOOD. So be safe, and back up first.

5.) When you add the line in the Delta folder, be sure to EXACTLY follow the format of the other lines in the folder. Here are some of the lines I added to my Delta file:

Equipment Rates, ROI.EXE ; PA996
Equip. Costs w/ Detail, ROI.EXE ; PA994
Total Equip Hours & Costs, ROI.EXE ; PA993
Project Costs, ROI.EXE ; PA995

Beware: the lines in Delta start with spaces and NOT TAB stops. Trust me, if you use Tabs instead of spaces, it won't be good for you (I know, I did try this one). Somewhere deep in the documentation, this fact is hidden; but I'm giving you this tip for free.

6.) Now you need to add a line into the Screen Table in the Dynamics System Files. For me, I have application data in a seperate database from the system database, and my databases are named: AlphaApp and AlphaSystem. I can't tell you what yours are named.

I added the following line in the Screen Table:

Module Name Number Screen Type
PA Equip Costs w/ Job Detail PA994 R

You can add this line to Screen Table by using T-SQL and the Query Analyser, or you can be lazy and just open Screen and add the information as a new record. However, you get extra cool points if add it through Query Analyzer.

7.) Now log into Dynamics as an administrator and add your new report to Utilities/Report Control Maintenance (98.300.00).

8.) And finally give users access to the report through Utilities/Access Rights.

John

Rumors of My Death

2007-02-02T11:03:00.000-08:00

To quote Mark Twain, "Rumors of my death have been greatly exagerrated!"

Hi Peeps. I'm still alive.

I've been busy. I did a midyear conversion from Forefront to Dynamics SL. All I can say is, don't do it. A mid year conversion is T O U G H !!!!! What was even worse was that we rolled modules into Solomon over a 4 month period - so the year end reconcilliation was nothing short of hellish.

But it's all good and all finished, so I should have time to fool around blogging now.

John

My Newest Project - An Automated Timecard

2006-10-20T16:31:00.000-07:00

Lookie This People - This is the cool sorta stuff you can do with Dynamics SL/Spitfire.

I'm going to create an automated time card using Excel and VBA.

We use HCSS Dispatcher to dispatch Equipment and Employees, and we use MS Project to create a project schedule. So the first thing this time card is going to do is to query the SQL Tables for both applications and create a time card for the day.

Then we're going to route this time card by e-mail using Spitfire to the job foremen.

After the foreman fills in the time card, then he'll send it back to Spitfire. Because of the power of routings in Spitfire, Spitfire will then send the time card to both the Operations Manager and the Project Manager for approval.

After the time card is approved, then we'll use some VBA code to automatically input the timecard information into Solomon for Payroll Processing.

The final bit of VBA Code will then create a daily cost report that details how the crew performed for that day, and whether or not they made money or lost money.

Finally Spitfire will send the Daily Cost Report to the Foreman, Project Manager, Operations Manager, and the Owner for review.

It's amazing how you can use Dynamics, Spitfire, and Office Automation to deliver reports almost instantaneously and automate data input.

Latest Release of Spitfire

2006-10-20T14:44:00.000-07:00

Yesterday, I went to lunch with Steve Powers of Spitfire. Besides talking about our shared passion for glass art and Dale Chihuly, we talked about the changes in the newest upgrades to Spitfire.

In the newest release of Spitfire, mail that is sent from Spitfire can now be returned to Spitfire without going through Outlook. This is a vast improvement over cutting and pasting from Outlook, and a much better way to handle e-mail than most other Project Management Software.

Way to go!!!

Corrected Contact Info for Jered Cady of Synergy

2006-10-20T14:42:00.000-07:00

Here's a copy of an e-mail I received from Michael Camp.

John,

Hey, checked out your new blog and left a comment. Actually thought the first one didn’t take but it did, so you might want to delete one of them. Also, change Jered’s phone number to 206-859-6505. Thanks!

Michael Camp

This Blog is Created

2006-10-19T17:17:00.000-07:00

Hello Everyone.

My name is John Wheeler, I live in a funny little town by the name of Tacoma, Washington. I'm the Controller at Alpha Development Corporation. I've been a Controller and Consultant for the past decade and a half, and in that time I've run numerous accounting systems.

Synergy Business Solutions

We recently purchased Microsoft Dynamics SL and Spitfire Project Management from Synergy Business Solutions. Synergy http://www.synergybusiness.com/ is a "Winner of the Microsoft Dynamics SL Global Excellence Award for 2005," with offices in Portland, Oregon, and Seattle, Washington.

I chose to use Synergy as my reseller because I have worked with the people from their company on a previous Solomon implementation. I was happy with them during that rollout and Synergy has performed great on my current rollout.

For sales inquiries you can call Jered Cady, President at 206.505.7919 and tell him John Wheeler sent you. It won't get you a discount, but it will help me know who's reading this blog.

Microsoft Dynamics SL

In my opinion, Microsoft Dynamics is the best mid-market accounting system on the market. It is backed by all the brains and talent of Microsoft. Dynamics is highly configurable and runs on a SQL Server Database. Dynamics includes Crystal Reports, FRx, and a Visual Basic Programming Language. Frankly I can't believe that anybody would use anything other than Dynamics.

Spitfire Project Management

The day I walked into Alpha Development over 2 years ago, I wanted to change from the DOS created accounting system they were running to Solomon (now known as Dynamics SL). Unfortunately, changing accounting systems is a huge project and costs a lot of money, so everytime I talked about the change, I was told, "no." And then voila, there was Spitfire.

The company owner is an Engineer, and when he saw Spitfire, he was sold. Spitfire is that rare piece of software that does everything you'd like a program to do. If you've been around computers as long as I have (since the Apple Lisa days), you can remember when you'd say, "Gosh, I wish this program would do this." Well, with Spitfire, it does everything you could ask from a Project Management piece of Software.

Spitfire is absolutely the best of the Project Management Systems out there (and I've used a few). Spitfire is Browser Based Software and it runs in conjuction with Microsoft Dynamics.

For us, Spitfire takes project management information and shares it with stakeholders.

Why this Blog???

Frankly, this blog is to support the accounting community with my advice, anecdotes, and expertise on Microsoft Dynamics SL, Spitfire Project Management System, SQL Server, and Crystal Reports.

I'd love to become a Microsoft MVP. Do you hear that Microsoft??? Besides that, I'd just like to have a place to share my opinions and tips with the world.

So welcome to my little space on the web.

John Wheeler