Archives

Well, We’re Movin’ on Up. M2M Migration

The Jeffersons

Movin’ on Up.

In a previous post, I indicated that I would post my migration procedures. Made2Manage offers instructions to set up a test server and basic migration directions, but they don’t offer recommendations of what exactly should be tested during migrations. This is unfortunate, because only they know where the issues are likely to be.

Should I pay for migration assistance?

I’ve performed migrations both ways. The only time I contracted with M2M for migration was my jump from Version 3.6 to 5.51. Migrating from Visual FoxPro to SQL is not something to be taken lightly. If you are in that situation, I would recommend getting assistance with it.

If you are migrating from any 5.XX version, it all comes down to the extent your M2M is customized, and budgetary concerns. If you administer a completely standard Made2Manage instance, then theoretically you could skip the testing process I am about to recommend. In that case having an experienced Made2Manage or third party consultant on-site during migration is beneficial. If something goes wrong during migration, then you have an expert on site with immediate access to all of Made2Manage’s resources. At the Vegas conference, Made2Manage distributed a document advertising an Upgrade and Upsize Service to version 6.0. To give you an idea of cost, they advertised a cost of $6200 for those already on any 5.X version and $11,700 for those on a prior version. These prices do not include weekends, holidays, or travel expenses. Also, the services don’t cover customization migration or the migration of customized reports.

If you have customizations, then the migration consultants will be of considerably less help if something goes awry. In my experience, most of them are not trained to customize Made2Manage. If you have customizations, ideally you should have the programmer participate in the migration process or someone who can substitute for them.

Therefore, if you have customizations and a reasonably competent administrator, you should do the migration and testing yourself. Migration is a great opportunity to learn more about M2M and how your business uses it. The process I use encourages the administrator to communicate with his users about any problems they have with M2M.

Migration Procedure

  1. Go to M2MExpert and get a copy of the CUSTRP Report This report will determine which reports have been customized and in what way, as well as any new custom reports you may have. After you run this report, use Visual FoxPro to check your UTRPSESS table as the sessionid field records the last time each M2M report was run. This includes any custom reports. Use this information to discuss with your employees which customized items they no longer need. I’m a big proponent of deleting things you don’t use anymore, but make sure you keep a permanent back up because users often change their minds. After this process you know which custom report items you need to test after migration.
  2. Assess your customized code. Are you using FastForms, Interactive Form Editor (IFE), VBA, or 3rd party reporting programs like Crystal Reports? If so, are you sure what that customized code actually does? Often documentation is an afterthought and if the customizations are old enough, the customized functions are mistaken for normal M2M function. Take time to investigate your customizations so there are no nasty surprises during testing. Also, you must thoroughly test your custom functionality as it is much more likely to break than standard M2M code.
  3. Set up a test server with an exact copy of your live data and all of associated folders. Keep the database back up files so you have a constant restore point as you will be migrating the data more than once.
  4. Before you migrate, run and print the following reports with the default settings: Sales Order Backlog (RPBKLG), Inventory Evaluation (RPIVAL), Work in Process (RPWIP), General Ledger Trial Balance (RPGLTB), AR Aging/Status (RPARAG), and the AP Aging/Status (RPAPAG). If any of the reports generates an excessive number of pages, print the last 5 pages of the report.
  5. Perform the migration according to the instructions. Assuming you were successful, apply any applicable service packs. You may also need to upgrade any of your optional modules as well.
  6. Make a SQL back up of your migrated data, taking care not to over-write your pre-migrated back up. You may need to restore to that point when doing user testing.
  7. Re-run the reports listed above and make sure that they match the pre-migrated data. If so, you have proven data integrity and you can move on to the next step. If not, you’ll need to investigate the cause of the differences.
  8. Open M2M and do some preliminary checking for correct functionality. Create a sales order, shipper, and invoice to check functionality. Make sure to check areas in which you have customizations.
  9. If you have customizations which actually write to the database, you need two test machines or you can use a test company (with identical data) on your live server for comparison. If your custom code is writing to your database, you not only have to do the first data integrity test I mentioned above, but you also have to perform the exact same actions on both instances, and then integrity test them again. You add, modify, and delete the same records to both to ensure that the end results are the same. Only through this extensive testing can you be sure that your customizations will successfully migrate.
  10. Now you’re ready for user testing. Recruit a person from each department with instructions to perform at least one example of every procedure they perform in Made2Manage. Typically, I create a spreadsheet with a separate page for each department to document each test, the result, and a description of the fix required if there was a problem. I sit with each person while they’re testing and fill out their spreadsheet for them so they can focus on testing.
  11. Obviously the next step is to address any problems with the migration. This includes data integrity, customization behavior, as well as training issues for your users. Often your users will require some training due to additional functionality provided by the migration, and now is the time to address that.
  12. After you’ve addressed any issues, the next step is to perform the migration again. This time document every step, creating a checklist to follow during the real migration. You do not want to find yourself working through a problem at 3am Saturday morning. This is not the time for improvisation.

Now, you may be asking yourself, “Does David really follow all these steps that thoroughly?” The answer, dear reader, is yes I do. In fact, in preparation for the last migration I performed, which was from 5.51 to 5.6, I migrated the data 5 times. It was necessary because we were migrating from IFE customizations to VBA/FastForms customizations and there were many issues to deal with.

So, let’s assume this whole process was successful and your company is successfully operating on the new version of Made2Manage. You’re finished, right? Not as far as I’m concerned, you still have to address what I think of as the “Hook” effect.

Hook

Imagining things makes them so.

If you haven’t seen “Hook,” or don’t remember the scene, Peter (Robin Williams) is starving having trained and exercised all day. The kids bring him dinner but all the bowls are empty. Essentially, if they use their imagination the food appears and everything is wonderful. What does this have to do with Made2Manage? Well, I can’t tell you how many times over the years that I’ve had users complain post migration that the new version of M2M either does or doesn’t do something that the previous version did. They will swear up and down that they’re right. In the real world however, believing something does not make it so.

The only way to settle these claims is to have a test machine available which is still running the previous version of M2M. I typically keep this machine available for a few months after a migration for this purpose.

I’m sure many of you have been involved in M2M migrations, how did you do it?

Inquiring Minds Want to Know About your use of FastForms

I’m trying to determine which M2M topics you folks want to read about. We can all use VBA, but since FastForms is an optional module less people may be interested in reading about it.

Please specify your level of interest in M2M FastForms.

View Results

Loading ... Loading ...

Some Quick Topics

Here are some things I thought were interesting this week:

A Few Good DBA’s This is a humorous take on Jack Nicholson’s rant in “A Few Good Men.” I’ll include an excerpt below as the site requires free registration to view the article and I know many of you won’t bother.

Manager: I want the truth!

DBA: You can’t handle the truth! Son, we live in a world that has SQL Servers. And those SQL Servers have to be guarded by SQL DBA’s. Who’s gonna do it? You? You? I have a greater responsibility than you can possibly fathom.

You have that luxury. You have the luxury of not knowing what I know:

And my existence, while grotesque and incomprehensible to you, it protects SQL Servers…You don’t want the truth. Because deep down, in places you don’t talk about at parties, you want me protecting that SQL Server. You need me on that SQL Server.

We use words like honor, T-Sql code and loyalty…we use these words as the backbone to a life spent defending something. You use ’em as a punchline. I have neither the time nor the inclination to explain myself to a man who rises and sleeps under the blanket of the very protection I provide, then questions the manner in which I provide it!

I’d rather you just said thank you and went on your way.

Otherwise, I suggest you pick up an understanding of SQL Server and start writing some code. Either way, I don’t give a damn what you think you’re entitled to!





Second, I came across this Microsoft Site with free SQL videos. They about using SQL Server Express 2005, but that hardly makes a difference. There’s a ton of good, basic SQL information to be had.




Finally, I received a call from a user last week stating that they could not run the Invoiced Sales Report that I mentioned in my last post. The user received the following error:

Date Outside Error

This of course relates to my previous post about the improper use of error messages. For the most part, users don’t read them, and they serve to do nothing more than impede productivity. This one is no different.

I told the user that he should simply hit OK, and run the report again. The second time, the report ran without incident. Now I can understand why the error is triggered, which is most likely to conserve resources and prevent the user from starting an extremely long, unstoppable, and useless report if the wrong dates are accidentally keyed in. However, shouldn’t the error say something like “Are you Sure?” and then run the report if the user answers yes?

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.

Cylon

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.

Schedule

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.

    VFP

    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.

Warning

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:

Warning

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
as
BEGIN
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
	begin
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1
	end

return @numvalue
END

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
BEGIN
SET @jonos_left = (SELECT (dbo.fn_base36('ZZZZ')-dbo.fn_base36(@base36)))
END

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

 END

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

END

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

END

ELSE
BEGIN
SET @jonos_left = NULL
END

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
BEGIN
SET @jonos_past_year = 1
end

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.

IF EXISTS (SELECT *
           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

Crystal

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?