By Your Command... Scheduler

I got an email from someone regarding this post regarding my preference for Crystal Reports. The question is, “What is Command Scheduler and what is wrong with it?”

First, in this blog I try to emphasize the importance of two way communication. If you don’t understand something, or you think I’m crazy, please post a comment and tell me so. Nobody knows everything about M2M, SQL, or anything else for that matter, so put on the white belt and ask.


Every time I read about Command Scheduler, I think of Battlestar Galactica

What is Command Scheduler?

Command Scheduler, and it’s robotic twin Command Processor, automate various processes in Made2Manage. You can schedule reports, custom programs, queue refreshing, and even re-indexing using these tools. This is especially important for those still using Visual FoxPro as you can re-index your tables every night when nobody is using the system.

Command Scheduler

Command Scheduler

The interface is not very user friendly so Made2Manage offers a class on its use. To demonstrate the flaws with this module, I’ll set up a report to run once a week with it. Suppose your accountant wants the Invoice Sales Report (RPIVSL) automatically run every week and printed to a network printer.

Invoice Sales Report

Invoice Sales Report

I highlighted the Run Later button because advanced users click that to schedule their reports. This triggers the scheduling screen, and the screen shot below is set to run Sunday evening at 10pm and repeat each week at the same time.


Seems pretty easy, right?

What’s the catch?

  1. The Command Processor requires a dedicated license in M2M, unlike the UPS Automation Program, and since licenses cost around $5,000 each, it’s an expensive little task manager. In addition, normally users can open a second copy of M2M on the same machine, though this will tie up two licenses. However, if you try to open up a new M2M instance on the computer which is running Command Processor, it simply opens a second Command Processor. Therefore, a computer dedicated to that role will be required.
  2. The Command Scheduler has limited interval choices and the shortest is once per day. If you are regenerating a que, you may need it to occur more frequently.
  3. Dated reports do not work properly. (see below)

So, you come in Monday and if you set everything correctly, your Invoiced Sales Report for the week ending 11/23/2008 will be waiting for you. Everything seems to be working fine until your accountant checks the printout next week and discovers that it is also the Invoiced Sales Report for week ending 11/23/2008. In fact, if left unchanged the Command Processor could run for the next 10 years, and will ALWAYS output the exact same data out of it. Why anyone would want the same 10 year old sales data exported every week is beyond me, but it made sense to the engineer who wrote this code.

There is a change request for this and it is slated to be fixed in Version 6.1, but nobody knows when that might be released. Notice that the change request is now almost 7 years old and is listed as an “enhancement.”

Now in all fairness, a skilled admin can compensate for this limitation by changing the advanced filter to run for a calculated date range. However, I gave up this route years ago and went with Crystal Reports which makes this whole process so much easier.

I, for One, Welcome Our New .Net Overlords (Reporting)

In a previous post I recommended the use of Crystal Reports and listed some of the advantages of doing so. One of the primary reasons I am looking forward to the Made2Manage .Net future is my fervent hope that VFP Reporting will go away. Visual FoxPro reporting must die. In this article, I would like to elaborate on the reasons why I feel this way. Some of these are inherent to VFP, but the rest have to do with M2M’s implementation of it.

  1. VFP Report creation is counter intuitive. When you try to create a new report in the RPWIZ screen, you have to use a VFP report creation wizard. However, there are two steps of the wizard that you must skip or your report will not work. Each time I create a new report, which I have avoided doing for some time, I must look up which steps to skip. According to M2MExpert, you must skip steps 2 (grouping) and 5 (sort order). There are simply too many steps and you have to remember to do things in certain steps or you cannot continue. “Don’t rename the Query.qpr file.” “Preview the layout to make sure data exists, and then save to your m2mdata\reports\template directory.” Why can’t it default to the correct directory? I can’t tell you how many times I forgot that. The reporting classes teach that you need to map out your report with all the tables and fields you intend to pull in beforehand as it is much more difficult to add them later. This is impossible because users are always asking for more to be added to custom reports.

    The VFP Report Wizard isn’t very polished either. For example, you can’t drag and drop field relationships as you can on nearly every other reporting program in the universe.


    Welcome to a 1990’s interface.

  2. VFP reporting is not WYSIWYG so you can’t develop in the presentation layer. In VFP, you must run the report (or print preview) to see changes and the printed report doesn’t always match what is on screen.

    VFP Layoutl

    VFP is edited in this view.

  3. This is particularly inconvenient when you are trying to perfect font settings and the position of objects on the report.

  4. Speaking of objects, don’t ever change grid line position. If you try moving grid lines on reports such as Purchase Orders, Sales Orders, Invoices, etc you’ll never get lines to join properly again. Good luck trying to make perfect corners. It’s incredibly frustrating.
  5. M2M VFP lacks graphing and charting options. Now, I’ve heard Made2Manage Techical Solutions people (customization coders) say many times over the years that there is nothing you can’t do with VFP if you’re an expert with it. As far as I know, you cannot quickly create graphs or charts with it, and people are visual.
  6. VFP does not offer “Page X of Y” abilities. You can print the page number on your reports, but you cannot indicate the total number of pages. With particularly long reports, this is a source of irritation for users.
  7. M2M offers sorting for a maximum of 3 fields. This can be a problem for complex reports where you are trying to get very granular with the data.
  8. Many existing reports are missing data environments. This makes it much more difficult to add fields and tables to existing reports. This has been an issue for as long as I can remember and it’s never been addressed. A couple of months ago a user needed me to quickly add a field to the Purchase Order Status Report (RPPSTA) to assist her with a project, and I replied that it would be done in a few minutes. Unfortunately, I had a surprise coming when I pulled up the data environment.

    VFP Layoutl

    RPPSTA’s missing data environment.

  9. I was still able to edit the VFP prg file and quickly finish the request anyway, but some of those prg files can be 20 pages or more of code. This is the case with the Sales Order Summaries Report (RPSOSM) which is also missing it’s data environment.

  10. Why can’t you copy and paste Additional Filters on M2M Reports? I’ve had complaints from users who need to share reports and therefore need to use the same filters. In fact, when you highlight a filter you’ve written and hit CTRL-C, it disappears and you must re-enter it.
  11. Can anyone tell me why the parameters reset whenever report selections are changed? For example, you’re using the Sales Order Summaries Report (RPSOSM) and you’ve set your start and end dates. After that, “Open Sales Orders” is selected instead of “All Sales Orders.” Immediately the date controls go blank and need to be set again. A relatively minor problem, but frustrating none the less.
  12. Made2Manage does not have the ability to prematurely stop reports. I think every person who uses M2M has made a mistake in requesting a ridiculously large report. I myself have accidentally started a report, usually omitting a start and end date, which would run for 30 minutes. I’ve seen sales order administrators accidentally print every quote in the system. M2M just churns out quote after quote and there’s no way to stop it without using CTRL-ALT-DELETE. There is a change request to which you can add your company if you’d like them to fix it. It’s been in “awaiting approval” status for over 4 years.
  13. Related to the last issue, large reports take a long time to view each page. For example, when viewing page 400, and the next page is selected, M2M literally has to render all 400 of those pages before it can display page 401. Other programs that I’ve used don’t have this drawback.
  14. My favorite red box error. ‘Nuff said.
  15. User permissions in Made2Manage are generally problematic (more on that in a later post) and that includes report permissions. Basically with every report you create, you must manually assign permissions for each user who accesses it. This isn’t a big deal if two people use it, but when managing hundreds of users across several M2M databases, this wastes a ton of time.
  16. Made2Manage reports aren’t always consistent in font and style, especially between the different modules.
  17. Wouldn’t it be nice if you could globally change fonts on your reports in a similar way that web pages are managed? When a properly designed website requires font or style changes, only a single file (or a few files) need to be changed and the entire website is uniformly changed. I don’t fault M2M for this omission as I’ve never used a reporting product which offered this, but I still think it would be enormously helpful. Do you have any vendors who use M2M? It’s immediately apparent because their reports look like yours. I’d just like to have more control over the look of M2M standard reports.

Alright, I’m done complaining. Do any of you have anything to add?

As God is my witness, I thought turkeys could fly!

Well, Thanksgiving is just a week away. Every year about this time I remember one of the funniest episodes of any television show in history. Of course, I am referring to the WKRP in Cinncinati’s Turkey Drop and I still laugh every time I see it.

Turkey Drop

It’s a helicopter, and it’s coming this way. It’s flying something behind it, I can’t quite make it out, it’s a large banner and it says, uh – Happy… Thaaaaanksss… giving! … From … W … K … R… P!! No parachutes yet. Can’t be skydivers… I can’t tell just yet what they are, but – Oh my God, Johnny, they’re turkeys!! Johnny, can you get this? Oh, they’re plunging to the earth right in front of our eyes! One just went through the windshield of a parked car! Oh, the humanity! The turkeys are hitting the ground like sacks of wet cement! Not since the Hindenburg tragedy has there been anything like this! – Les Nessman

Anyway, since this is primarily a professional blog, I thought I would list those things I am thankful for in regards to my career.

  • I’m thankful that I made the move to Dallas, and have found myself in a challenging work environment which motivated me to take my skills to a new level.
  • I’m thankful to certain folks at Made2Manage who made it possible for me to find this job, even though I have been hard on them at times over the past decade.
  • I’m thankful to Brent Ozar and Jeff Atwood for encouraging me to start my own blog.
  • I’m thankful that I work with El Jeffe and The Nuff, who are the best team members I have ever had.
  • I’m thankful for the hard working and helpful SQL experts at SQL Team and SQLServerPedia as they have made learning SQL so much easier.
  • I’m thankful that Made2Manage has not outsourced their support, and I hope they never do.

So, what are you thankful for?

Having “Fun” With Job Order Numbers

I recently received a call from a company that had “run out” of internal job order numbers.


As an error message, this one is fairly descriptive. However, 9 times out 10 the person who receives it won’t have permissions to modify the numbering system anyway. Some research on M2MExpert determined the cause. Made2Manage only allows 4 characters for the internal job order numbers (5 if you include the mandatory preceding I). If this was a numeric field, then there would only be 9,999 possible job numbers. The work around was to utilize Base 36 numbering in this field. Rather than just 10 options for each digit (0-9), they added another 26 since each digit would jump from 9 to A through Z for a total of 36 different options per digit. Theoretically, this raises the number of combinations to almost 1.7 million. I know M2M’s document indicates 34 options because they say that I and T are skipped, but this isn’t true with Internal Job Order Numbers, which is the subject of this article.

This company was set to Alpha Column 4 and the number indicates which starting digit from right to left can be base 36. For example, the last number this company could allocate was IZ999 since the second digit is the only one which allowed letters. Incidentally, IZ999 is literally the number 36,000. Z represents 35 and since it is in the thousands place, the number is 35,000 + 1000 (999 plus the starting 0) is 36,000.

You might ask, why would someone set the Alpha Number to 4 in the first place? Well, M2M uses the same setting in it’s own educational company, so how would anyone know that they should have their company set differently? So, what is the answer to this problem? There’s only one practical solution which is to lower the Alpha Number setting. To do so choose the Utilities Module, Company Setup, Codes & Tables, System, and Document Numbering (Company Specific) and you’ll be greeted with this comforting message box:


When you see something like this, stop and call support.

Incidentally, you can also type ALT-T and go to the SYSEQU screen. Click the browse button and navigate down to JOMAST.FJOBNOI. The greatest number of job order combinations is available when you select Alpha Number 1. This makes sense because all of the digits switch to base 36.

So, why did I explain all of that? Well, I decided that this would be a fun SQL exercise. Wouldn’t it be interesting if you determine how long you had before you ran out of job numbers so this wouldn’t sneak up on you? No? Well, humor me anyway, I think it’s fun.

To simplify this whole thing, we are going to use a User Defined Function (UDF). Normally, I would not recommend adding one to your M2M Database because it could possibly interfere with migration. Fear not dear readers, we will delete it when we are finished. I think now would be a good time for the disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

First, use the following code to create the User Defined Function.

create function dbo.fn_base36(@mystr varchar(8000))
returns bigint
set @mystr = reverse(@mystr)
declare @len int, @onechar char(1), @pos int, @numvalue bigint
declare @arrystr varchar(100)
set @arrystr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @len=len(@mystr)
set @onechar = ''
set @pos = 1
set @numvalue = 0
while @pos<=@len
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1

return @numvalue

The following code actually checks your tables to estimate when you will run out of internal job numbers. For the sake of simplicity, we are only checking these, rather than all 3 types. After the code, I'll briefly discuss the code. If you'd like further explanation, feel free to ask questions in the comments. Remember that you will have to replace the company number with the company number you wish to test this on. I ran mine on a practice company which was numbered 12.

DECLARE @last_job_no varchar(8000)
DECLARE @alpha varchar(1)
DECLARE @jonos_left AS int
DECLARE @base36 AS varchar(5)
DECLARE @numeric_left AS int
DECLARE @jonos_past_year AS int

SET @last_job_no = rtrim(ltrim((SELECT S.fcnumber
                FROM m2mdata12.dbo.sysequ S
               WHERE fcprompt = 'Next Internal J.O. Number')))

SET @alpha = (SELECT S.fnbase36di
                FROM m2mdata12.dbo.sysequ S
               WHERE fcprompt = 'Next Internal J.O. Number')

SET @base36 = (SELECT LEFT(@last_job_no, 4 - (@alpha - 1)))

SET @Numeric_Left = (SELECT RIGHT(@last_job_no, @alpha-1))

IF @alpha = 1
SET @jonos_left = (SELECT (dbo.fn_base36('ZZZZ')-dbo.fn_base36(@base36)))

ELSE IF @alpha = 2
SET @jonos_left = (SELECT ((dbo.fn_base36('ZZZ') * 10 + 9) -dbo.fn_base36(@base36) * 10 + @Numeric_Left))


ELSE IF @alpha = 3
SET @jonos_left = (SELECT ((dbo.fn_base36('ZZ') * 100 + 9) -dbo.fn_base36(@base36) * 100 + @Numeric_Left))


ELSE IF @alpha = 4
SET @jonos_left = (SELECT ((dbo.fn_base36('Z') * 1000 + 9) - dbo.fn_base36(@base36) * 1000 + @Numeric_Left))


SET @jonos_left = NULL

SET @jonos_past_year = (SELECT count(J.fjobno) FROM m2mdata12.dbo.jomast J WHERE j.ftype = 'I' AND j.fopen_dt >= DATEADD
      (YEAR, -1, getdate()))
IF @jonos_past_year = 0
SET @jonos_past_year = 1

SELECT @jonos_left AS JobNumbersLeft,
       @alpha AS AlphaNumber,
       @last_job_no AS LastNumberIssued,
       @base36 AS base36,
       @numeric_left AS NonBase,
       @jonos_past_year AS JobOrdersPastYear,
       (@jonos_left/@jonos_past_year) AS NumberYearsLeft

Let me preface this by saying that I could have achieved this with less code, but I leaned toward verbosity to make it easier to follow. The first 6 lines of the code declare the variables used. The next 4 groups, set the values for 4 of the variables. The last job number assigned (@last_job_no) and your alpha number (@alpha) are queried from the database with the two select statements. The base 36 portion (@base36) and the numeric portion remaining (@Numeric_Left) are separated from your @last_job_no based on the alpha setting in your database. In the above example of IZ999 the @base36 would be Z (I is ignored because it never changes) and the @Numeric_Left would be 999.

The next 5 sections are if statements and calculate the job order numbers left (@jonos_left) based on your alpha setting. Basically, the value of your last job order number is subtracted from the maximum value for that alpha setting.

Next I set the count of your job orders from the previous year (@jonos_past_year) with the select statement. If you don't have any job orders from the previous year, I set the value to 1, so we don't divide by zero.

The final select statement displays everything for you and divides the number of job order numbers left (@jonos_left) by the job order numbers from the previous year. This estimates the number of years you have left before you run out of numbers. Cool, huh?

Finally, use the following code to remove the UDF from your database.

           FROM   dbo.sysobjects
           WHERE  id = Object_id(N'[dbo].[fn_base36]')
                  AND xtype IN (N'FN',N'IF',N'TF'))
  DROP function [dbo].[fn_base36];

So, how many years do you have left before you run out of internal job order numbers?

What Reporting Methods are You Using?

I’d like to get a better idea of what reporting options my readers are using so I will know which of them I should cover regularly. Feel free to comment on why you use your preferred reporting option(s).

Please check all the reporting options you use.

View Results

Loading ... Loading ...

Quality Reporting on a Budget

Approximately 4 years ago at a National User Conference, a Made2Manage customer gave a demonstration of Crystal Reports. It is my understanding that at the time, Made2Manage was in negotiations with Crystal to license it as a partner to provide a quality alternative to Visual FoxPro reporting. Well, that demo made an impression on me and I embarked on a quest to master Crystal Reports.

Crystal is a better reporting system than Made2Manage/VFP for a multitude of reasons:

  • Faster Development
  • Uses VB and SQL which are “current” languages
  • It is WYSIWYG so you can develop in the presentation layer. In VFP, you must run the report to see changes and the printed report doesn’t always match what’s on screen.
  • CR works with nearly any data source including nearly any modern database (both VFP and SQL), Office Documents, and text files.
  • Report files are easily shared
  • There are several helpful online communities
  • You can create queries using a builder or by pasting SQL in directly
  • Reports tend to run much faster than those inside M2M
  • Report scheduling actually works (unlike Command Scheduler)
  • More supported exporting formats
  • Amazing charting and graphics options
  • Drill down capability


You can’t do this with VFP reporting.

There are several different ways to implement Crystal Reports. They offer several different products which are targeted to small and mid-sized companies. The more robust packages start at around $7,000 for the server software and allow you to deliver the reports via web pages, interface with Active Directory for report security, and include a multitude of scheduling options. I have this type of set up with my current employer, and enjoy the access to data it provides.

However, there are a couple of reasons I am not recommending these options. First, such a complex and comprehensive product requires quality support. When I first started using Crystal years ago, they had the best support I had ever seen. The techs were very knowledegable and would take the time to actually teach you reporting techniques. Well, things have changed. Most of their phone support was outsourced over the years and while the technicians still know their product, you cannot understand most of what they say to you. Also, they are much more hurried and push you towards paid consulting for any reporting questions you may have. Since being acquired by SAP, the support has become even more difficult to use as you must create cases via their website, which is beyond counter-intuitive, and wait for a tech to call you back. Imagine “Web Support Spotlight” day at Made2Manage but with a website that almost seems purposely designed to keep you from successfully creating cases. Anyway, it’s hard for me to recommend their product to new companies when their support is so lacking.

Second, many of us are forced to do more with less in these tough economic times. Therefore, asking the decision maker of your company for over $7,000 (for only 5 concurrent users) right now may be a hard sell.

What do you recommend?

In my opinion, the best value is to use the current version of the Crystal Reports 2008 Studio and use 3rd party programs for desktop viewing. Compared to the other options, the cost is nominal and you still get most of the benefits of Crystal Reports.

How much does it cost?

You can buy the development studio for Crystal Reports 2008 for under $500. In fact, CDW has a special on it right now for $432.99.

As I said, I recommend 3rd party viewers for report distribution. A friend of mine in Indiana, uses Easy View successfully. However, as of now it only supports Crystal XI (the version prior to 2008).

Another option which actually supports 2008 is cView, and you can read a brief review of it and other viewers here. cView can be purchased here with a minimum quantity of 3 licenses for less than $50 each.

What are you folks using for your primary reporting tool?

Reporting Issues When Migrating to Version 5.6

I noticed a couple of reporting issues when migrating from 5.51 to 5.6 earlier this year and I wanted to share them with you.

Let me preface this by saying that I am extremely thorough during migration testing. In a future post, I will describe the method I use when I perform a migration. My worst fear is that the migration will be successful at first glance, only to fail at a later time. I don’t want my company entering data into the latest version of M2M only to find out Monday afternoon, or perhaps later that week, that we have to do an emergency restore due to unforeseen problems and have to re-enter all that data. This is entirely possible if you have extensive customizations.

So, during the last round of migration testing, I noticed some oddities of which you should be aware. The first is just a curiosity. The Sales Order Backlog Report (RPBKLG) showed different values between 5.51 and 5.6 data. The totals were the same, but the grouping (which for some reason was by customer name) was different. When I called an implementation consultant, who is a friend of mine, he indicated I was being too paranoid and they just check the totals during a migration. A change request was entered, but most likely will not be addressed.

Secondly, the Material Requirements Planning Report (RPMRP) stopped working in version 5.6. After migration the “DEP Demand” shows 0. Apparently, someone edited the program file of this report for the 5.6 version and did not do so properly. Well, I fixed it by substituting the version 5.6 .prg and .fxp files with those from version 5.2. This is all covered by Change Request 146266 if you’d like to read about it.

Have any of you noticed anything odd when migrating to the current version of Made2Manage?

SQL DBA Quiz – I’ll Play Your Game, You Rogue.

Today I was tagged by SQL Ninja Brent Ozar after he was tagged by Chris Shaw for this brief SQL DBA Quiz. His quiz requests that I name two mistakes I’ve made in my career. As someone who celebrates his mistakes, of course I will have to oblige. The title of my post, is a reference to the Saturday Night Live Celebrity Jeopardy skits where Darrell Hammond plays Sean Connery.

Celebrity Jeopardy

Well, the game is afoot. – Darrell Hammond

Mistake #1.

As I mentioned in an earlier post, I am a recent transplant to Dallas and one of my first assignments with my new company was a Sales Report. As a classic Type A overachiever, it would be a masterpiece. I completed the rough draft, and then spent an inordinate amount of time checking the details. I had created what I hoped would be approved as the standard way we would present reports in the future. Since I was using Crystal Reports rather than M2M’s VFP Reporting, I had a great deal of flexibility in how I presented the report and included several different smart-looking graphs for the executives to choose from. This was the type of report that a Business Intelligence Specialist includes in his portfolio. There’s only one problem which I didn’t discover until it was embarrassingly too late.

I had asked another employee to check the report for accuracy since I didn’t have the slightest idea whether my sales figures were reasonable for my new employer. I then submitted the report to my new superior, and she remarked that it did indeed look like professional work. “The only problem,” she said, “is that the sales figures are around twice as high as they should be.”

The reason for this was that M2M has a quirky way of tracking sales figures in its Sales Order Releases (SORELS) table, and if you don’t remove any Master Release records you end up with higher sales figures than you should. My previous employer never used Multi-Release so I had completely forgotten about that. Moral of the story, always check the accuracy of the reports yourself. For example, try to find an analogous report in M2M with which to compare your figures. For every time you are wrong, especially if you seem confident that you are right, they will question your figures the next 10 times.

Mistake 2:

I recently wrote to my live production database by accident. It’s so easy to do with M2M. I had recently refreshed my data on my test server with back ups from my live server. You are using a test server, right? I restored my Data01, Data02, and M2MSystem databases. I didn’t run createdbc because I had not copied my M2M directory over so I didn’t have to change my UTCOMP table.

This was a big mistake. Imagine me making random changes to my sales order and shipping tables, and checking the results of the triggers I had scripted onto them. I’d make a change, and the triggers (which were only on the databases on the test server) would not fire. I was baffled. Eventually, it dawned on me that even though I was on a test server, running M2M directly on that test server, had my M2M data drive mapped back to my test server, the live data tables were actually being modified on my live server. Luckily I hadn’t made too many changes and hadn’t done anything damaging like deleting sales orders and such.

Now, every time I restore data, I make a single change to a sales order and then check my live data to make sure I am not inadvertently making changes there. What mistakes have you made at work, and what have you learned from them?

You keep using that report… I do not think it means what you think it means.

The other day, a Made2Manage user who worked in production called me because he had a problem with one of his jobs, as it was not showing up on the Work In Process Report (RPWIP). The report description is as follows:

The Work In Process Report reflects the current value of charges that have been accumulated per Job Order, but that have not yet been moved to the Cost Of Goods Sold

After I examined the job, I realized why it didn’t show up. The keyword is value. The RPWIP does not list jobs with $0 value. When I tried to explain to the user why the job was not showing up, he swore that the report had been showing $0 value jobs for all of the years he had worked with it. I had to take a look at the actual program (prg file) to verify that I was right.

The Princess Bride is one of my favorite movies of all time, so I actually said in my best Inigo Montoya voice, “You keep using that report… I do not think it means what you think it means.” The user laughed, and I promised to create a new report for him that would show all jobs, when I could make time.

Inigo Montoya

You keep using that word. I do not think it means what you think it means. – Inigo Montoya

Another report that people misunderstand is the Sales Order Backlog Report (RPBKLG). The long and confusing description is as follows:

There are three distinct reports in this selection.
1) Order Backlog:
Order Backlog in Price and Units. The report shows units and dollars in time buckets of days, weeks, or months. The report always displays 12 buckets (columns).

2) Back Order
This report shows open sales order items which have a quantity shipped less than the quantity ordered. The date range available for selection is the Sales Order Release due date.

3) Backlog vs. Quotation Analysis:
This report gets the backlog by looking for open sales orders with items that have an order quantity greater than the shipped quantity. It then reviews open quotes and gets similar information.

1) The purpose of this report is to let you know how many items and dollars are booked for each of the next 12 days, or weeks, or months.

2) The purpose of this report is to show which items have not been completely shipped on open orders.

3) The purpose of this report is to assist in analysis of sales trends.


This is the mental picture I get when I imagine who wrote and documented the standard M2M Reports. Wally the Engineer – Dilbert Comics.

When I asked M2M Support to define the difference between Backlog and Back Order, they simply e-mailed me the exact quote I asked them about. So, it’s no wonder that people don’t understand this report. The most common misperception is that this report represents all available items to be invoiced. This is not true, it only takes into account the items that are shippable. I know of at least one company who continued to use RPBKLG as an “non-invoiced report” even after I pointed out that it wasn’t accurate, simply because they had no other alternative. M2M does not offer a report that returns the dollar value of goods on sales orders that have not been shipped or invoiced. I’ve written a SQL statement which accurately returns this value, and I will share it in a later post, as well as an explanation as to why it works.

So I ask my readers, have you misinterpreted the results of a Made2Manage report and if so, which report?

Some are born geeks, some achieve geekiness, and some have geekiness thrust upon them….

I like to think of myself as an alpha geek. Geekiness is something to be respected, enjoyed, and promoted. Let me share some of my “geek cred” with you.

For example I….

  • Learned BASIC on a Radio Shack TRS80 Model 3 and I still have 5.25″ floppies which contain my early programming efforts.
  • Have seen Star Wars more times than I can count
  • Played the arcade game Asteroids for more than 2 days on one quarter
  • Own a complete in box 1972 Odyssey, the first home video game system.
  • Before I left Michigan, I owned nearly every video game console sold in the US.
  • Have more than 600 Nintendo NES games all complete in box.
  • Have an original Super Mario statue that is nearly 4 foot high. He used to be part of a Nintendo store display from the 80’s.
  • Have a large collection of DragonLance Novels

With such impressive geek fu, it’s not often that another geek humbles me. Well, several months ago while working through some SQL Server performance issues, I came across the blog of Brent Ozar. I read an excellent article he authored with instructions for using Perfmon, and he’s been a great source of information and direction for me since. He’s a SQL Server geek of the highest order.

Anyway, he posted a picture of his Jeep to his blog some time ago, and I was immediately humbled in the presence of a supreme geek.

SQL Plate

Yes, that is his real license plate.

So, if you are as interested in SQL Server as I am, and believe me everyone using the SQL versions of Made2Manage should be, give his blog a read at