Archives

M2M Can’t Combine Shippers and Sales Orders on Invoices. Why Not?

Please be sure to read the comments as this post is actually in error. Thanks again Andy. 🙂

Did you know that you cannot invoice from a shipper and a sales order on the same invoice? I was mildly surprised myself when I realized it. Let me take a moment to explain why this is a problem.

Imagine creating a Sales Report to determine the net value of everything not shipped nor invoiced that it is in the Open state. The value of sales orders is actually determined in the Sales Order Release (SORELS) table. The method for doing so is this:

((SORELS.forderqty - (SORELS.fshipbook + SORELS.fshipbuy + SORELS.fshipmake) - SORELS.finvqty) * SORELS.funetprice

In other words, the order quantity minus the quantity shipped and invoiced, times the net value per piece. Makes sense, right?

Problems arise when you have sales orders that contain both shippable and non-shippable items. For example, we have a sales order for Acme, line 1 is a new radar device for one of their Rockets, and line 2 is the R&D cost for adding Roadrunner detection. Line 3 is a different long-term product, which keeps the sales order open after lines 1 and 2 are finished. Therefore, lines 1 and 2 have a quantity of 1 for SORELS.forderqty.

Anyway, your company ships the radar to Acme, and your accounting department invoices them from the shipper. When shipped, M2M updates the SORELS.fshipmake for the radar to the value of 1, and since 1-1=0, that value is removed from your report.

However, since accounting cannot invoice from the sales order on the same invoice, they must do a miscellaneous line item instead. This is transparent to the customer, but it causes problems in the SORELS. Line 2 is never updated to show that the SORELS.finvqty is now 1, and that line item still shows outstanding. The net amount is off by that amount in your report.

Made2Manage’s response is that we should invoice sales order line 1 and 2 separately, but some customers will refuse to pay multiple invoices like that. Also, you could ship the R&D fee from line 2, but it would show up on the customer’s packing list and that is confusing as well.

Has anyone else experienced this? How do you handle it?

M2M Warning. Please Read ASAP

This morning approximately 75% of my users were no longer able to open M2M without an error. The error says “Omsg is not found” and this typically indicates a VBA issue.

The problem appears to be related to a set of updates issued yesterday and is only affecting those machines which have Office 2007 installed. I am running Windows XP and M2M Version 5.6.

I will follow up with details after I have thoroughly tested everything, but I wanted to give a heads up to any of my readers in case they run into the same problem.

PASS. Somewhere Out There..

Fievel

Fievel - An American Tail

All of the Tweeting about PASS prompted me to write this. Sung to the tune of “Somewhere Out There.”

Somewhere out there…
At the PASS Summit…
People are learning SQL and Tweeting about it.

Somewhere out there…
Ozar, Fritchey, and Delaney…
are teaching Clouds, Wait Stats, and High Availability.

And even though I know it’s about learning T-SQL and SQL Server too,
I’m also miffed that I’ll also miss the next tattoo.

And even though I want to learn clustering, indexes, and Integration Services….
I also want to count the number of “it depends.”

Somewhere out there..
I’ll continue to train,
Then next year, I’ll be out there..
Where best and brightest shine.

Original Lyrics:

Fv: Somewhere out there,
beneath the pale moonlight,
someone’s thinking of me and loving me tonight.

Ty: Somewhere out there,
someone’s saying a prayer,
that we’ll find one another in that big somewhere out there.

(*)Ty: And even though I know how very far apart we are,
it helps to think we might be wishing on the same bright star.

Fv: And when the night wind starts to sing a lonesome lullaby,
it helps to think we’re sleeping underneath the same big sky.

Together: Somewhere out there,
if love can see us through,
then we’ll be together, somewhere out there,
out where dreams come true.

Character key:
Fv = Fievel, Ty = Tanya, Tg = Tiger.

Search Meter Surprises

I’ve been making changes to this blog lately including a new theme, plug-ins, and tons of new content. One of my unofficial mentors, Brent Ozar, who was recently made a Microsoft MVP, has been on a tear as of late. This has inspired me to take my blog to the next level as well.

Anyway, one of the new plug-ins I implemented is Search Meter for WordPress. This has been enlightening because I can now track what people are actually looking for. One obvious search that repeatedly showed up was something like, “Subscribe New Posts.” I had not noticed that the Subscribe plug-in had not carried over with the new theme. Oops.

In addition, people are searching for T-SQL terms, M2M screen and report names, as well as Crystal Reports issues.

However, the most entertaining revelation is that some M2M people search the blog for their names. I already knew that many M2M employees read the blog, but I had no idea they would search for mentions of their names. Let me just reassure the folks at Consona that I won’t call anyone out by name, especially in a negative light.

What topics would you like me to cover or discuss in more detail?

Running Twice as Fast

This past Saturday I attended the first of four days of SQL Server Development Training. The instructor, Mike Hotek literally wrote the book on SQL 2008 Database Development. A fellow M2M Administrator asked me why I bothered because we are not yet on SQL 2008 and I thought it would make a good blog post. The question reminds me of Alice in Wonderland.

“A slow sort of country!” said the Queen. “Now, here, you see, it takes all the running you can do, to keep in the same place.
If you want to get somewhere else, you must run at least twice as fast as that!”

Listen, unless you intend to administer Made2Manage the rest of your life, you’d better learn to enjoy learning. While the pace of M2M development has been fairly sedate, the rest of the technological world moves at a blistering pace. If you are putting in your time simply doing a good job, you are not only stagnating, but falling behind. I am studying SQL 2008 Business Intelligence and Database Development because my career is rapidly moving in that direction. I hope to be MCTS certified by the end of the year and MCITP next year.

Why am I telling you this? Well, one reason is this makes my goal public and motivates me to work harder toward it. Also, I want to encourage my readers to avail themselves of every opportunity to educate themselves and further their careers. If necessary, do it in your free time and using your own money; I am. Don’t just keep pace, move ahead.

Oh, and incidentally I finally understand page splitting, clustered vs non-clustered indexes, and B-trees. Luckily Mr. Hotek was patient when I asked a bunch of questions.

What are you doing to further your career?

Suggestions for Prospective Vendors

First, let me get this out of the way, I have great respect for sales people. It’s a dirty job and someone has to do it; it certainly won’t be me. My father was a salesperson of one type or another his entire life.

I can only imagine how difficult this job is considering the current economy. The stress of being commission based must be severe, which motivates many salespeople to stray from the accepted paths. I always try to be courteous and understanding with salespeople because I know what a rotten job it can be.

However, I would like to send a message to prospective vendors regarding some tactics, which simply do not work or are blatantly irritating and offensive. This post is somewhat similar to my suggestions for vendor support.

Cold calling is dead. This is especially true if there is no connection or specific reason for the call. If I filled out a card at a seminar asking for information, please call. If our connection is because my business happens to use computers, forget it. If you sell a M2M related product, I may give you a few minutes. Nobody likes cold calling, especially you (the salesperson). In college, I worked as a market research phone representative asking boring questions about products and such. I hated my life. Near constant negative feedback was so depressing. Stop cold calling, it doesn’t really work.

Spam is evil, even if I already buy from you. If you have some revolutionary new product, send me an e-mail. However, if I spammed every week with some past product that you dusted off to try to re-sell me, I get annoyed. I didn’t buy it two years ago, and presumably I still know about it, so why would I want to buy it now? This is especially irritating when current vendors send the e-mails directly from the salesperson’s account. I want my account manager’s e-mails to come through in case something important happens with my account, but not the spam. I cannot block the account without losing all of the e-mails.

Bogus Twitter Followers are annoying as well. I will not elaborate much on this because Brent Ozar already covers it.

Your internet presence is troubling. If I am interested in doing business with you, you can bet that I will Google your company. I suspect nothing less about prospective future employers. If they Google “David Stein SQL” and they come across a bunch of negative comments, I will not get the job. Customer service is particularly important in this age of easy information.

Unsolicited appointments are a scourge of marketing. It works like this. A Salesperson gets my e-mail address and sends me an Outlook Meeting Request. Rather than actually asking for a meeting, I am scheduled for it. Often I have never even spoken with them. In fact, the meeting requests usually don’t ask for a meeting either, they simply say something like, “If this time isn’t convenient, when would you like to reschedule?” Umm… How about never? I will never do business with you. How do I handle these situations? I simply leave the appointment on my calendar, and then smile when I get a follow up e-mail complaining about missing the appointment. If I waste enough of their time slots, perhaps they will stop bothering me.

Concealed sales pitches are common as well. Do not try to entice me with offers of free training, and then waste my time with a sales pitch. I don’t respond well to being tricked and will refuse to do business with you. Certain vendors have a notorious reputation for offering little actual value in local and national user group meetings because they are thinly veiled sales pitches. This is particularly odious when my company actually pays for the event. My company does not want to pay several thousand dollars to send me to Vegas simply to listen to sales pitches for new ERP modules or reporting packages. If you want my time (or for that matter my money) you had better provide actual content. One vendor who does this well is Quest software. Many of their products augment and correct shortcomings of the standard Microsoft products. Their Pain of the Week online presentations are particularly good. They will discuss a problem with SQL Server, and how to fix it with the native tools. However, if their product is easier or more efficient, they will demo that as well. Yes, you often get a sales pitch, but it is typically reserved for the end, after you have received valuable information. Incidentally, I do not have any affiliation with Quest, other then using TOAD.

Be professional in all of our communications. Just because we both have experience in the same ERP system, we are not instantly bosom buddies. Salespeople who over extend themselves to be your instant friend give me a slimy feeling. Also, check your messages to me for spelling and grammar errors before sending them. I received the following message recently, which demonstrates another common problem.

Hello {!Contact:First Name;},

My firm is a Business Objects/SAP Partner. They have asked us to follow up with you to see if you require any additional assistance after your download of the {!Contact:Description;} product. I am available to help you and your company in the following capacities:

• Product Purchase Guidance
• Technical Training
• Consulting Services

I’d like to give you a quick call to introduce myself so you feel comfortable contacting me in the future. What is the best number and time to call?

I realize we all make mistakes, but do you think I’m going to do business with a technical company that doesn’t know how to mail merge?

Practice your product demos and look polished during your presentation. I know that accidents happen, laptops die, windows errors pop up, etc. However, if you want to sell your product, know how to run the product. Earlier this year I met with a potential vendor with a new reporting product that did not go well at all. The two man team (sales and technical) managed to lock themselves out of their own product in the first 10 minutes and then wasted the better part of an hour trying to fix it. After giving up, they spent the remaining ten minutes discussing what the product SHOULD be able to do with screen shots. Do you think I bought this product?

Focus on your product, not the competition. Don’t spend your time with me focusing on negative themes. I know that sounds ironic since most of this post is about negative actions of salespeople. Don’t spend too much time tearing down their product, spend it building your product up.

Get the hint if I don’t respond. This theme relates to most the previous points. My company may not be interested in your product for several reasons. Perhaps it is a budget issue. ERP Software in particular is so expensive that switching to another one can be ridiculously expensive. We may already have a business partner and we are happy. Badgering me with sales pitches is not a productive use of your time or mine.

What do you think?

Report Guidelines: Report Approval and Deployment

This article is day five in a week of reporting articles.

Create and follow standard procedures for report deployment. The actual procedure depends greatly on the report method. Since I often use Crystal Reports Server and SQL Reporting Services (SSRS) for my report presentation, I have a Testing folder where all finished reports go prior to manager approval. Specify who can approve such reports.

In my experience, users have the tendency to request reports as single requests for data. In other words Milton will ask you, “Can I have the last 3 years of invoiced sales for Acme Rockets that shipped to Arizona?” There will be a temptation to fulfill his request as expediently as possible as an ad hoc query. If possible, put in a little extra effort and create a stand-alone report that can be re-used, regardless of whether the user wants it. You may even ask Milton if he will ever need similar information again and he will likely say no. However, next week he will come to you, most likely holding his red stapler and ask, “Now Bill needs year to date invoiced sales for Initech.” To most users these are completely different reports. Think ahead and create more versatile, reusable reports. This will save time for both you and Milton.

Create a watermark (if possible) to differentiate which reports have been approved and which are still being tested. Watermarks are typically semi-transparent logos or large text such as “Draft” shown behind the data. Watermarks “encourage” users not to use a report ad infinitum, which has yet to be approved because their data can be hard to read. Some may balk at this, but trust me if you do not create and follow a similar procedure, reports will become disorganized and never are certified. I’ll follow up with a “How To” article to achieve this with Crystal and SSRS.

Remember, you are (or at least this is directed to) the technical person. At a previous employer, a V.P. affectionately referred to me as the “chip head.” At least I think it was affectionately. You are most likely NOT the decision maker, you write reports for them. It is very unlikely that you know their data and what it means better than they do. Therefore, they must certify that your reports are right.

It goes without saying, but use descriptive and specific report names. Reports are not helpful if the user cannot determine their purpose at a glance. To go along with that, thoroughly document every report. Inevitably, a user will come to you and ask something like, “Does this include part revisions that are….” If you do not document reports up front, you will have to edit the report to find out. When documenting, I always imagine that today may be my last day at that particular job. This is the “What happens if Dave gets hit by a bus?” kind of thinking. If someone needs to take over for you, can he/she do so easily? If not, you are not doing your job.

Report Documentation for Crystal Reports

Report Documentation for Crystal Reports

In Crystal, I document using the Document Properties Window at File – Summary Info. The Comments text is displayed next to the report title on Crystal Server and assists the user in selecting their reports.

So, this was my week of general reporting tips and guidelines. I will follow up soon with a series of videos, which will demonstrate the best ways to extract SQL from M2M reports as well as creating Crystal, and SSRS Reports based on M2M data.

Report Guidelines: Exporting Reports

This article is day four in a week of reporting articles.

Most users want to export reports to Excel so they can manipulate the data. If possible, set up the report for the cleanest exporting possible. This process starts when writing T-SQL. For example, one can extract only the date instead of a datetime value when time is not relevant.

As I mentioned in a previous post, if you are creating a VFP report for M2M only the fields called out in your prg file will export to Excel. Therefore, do not pull them in via the data environment or your export will be missing items.

SQL Server Reporting Services and Crystal Reports will export many formats including PDF, Excel, Word, HTML web pages, and several text based formats. Excel, in my experience is the most common export format followed by PDF. In Crystal, there are two basic options for exporting to Excel, standard and data only. Most often I use standard, but if default options are used the results will often be lacking. The problem is that Crystal will merge columns and rows in illogical ways and this makes data analysis impossible.

The answer is to select a different column width option. Depending on your report, you’ll most often use a Group Header, Footer, or the Details section to determine your column widths.

Set the Export Properties.

Set the Export Properties.

Keep in mind that your users will not remember to change this setting when exporting, so there is an option to save these settings with your report. Choose File –
Export – Report Export Options to change these options. These options are saved with the report and it will default to those settings for the users.

I always create these settings even if the user says they will never export it. Invariably, someone will come to me in the future and complain that the export doesn’t work.

As an aside, I have talked in the past about Excel Hell and often that hell is caused by spreadsheet exports from M2M or other ERP systems. For example, some companies want to keep track of their Sales Order Backlog or Inventory over time, but these figures are a snapshot. Therefore, an employee is assigned the task of running the corresponding report, exporting it to Excel, and keeping track of it on the network. This behavior becomes so ingrained in some users that they have difficulty abandoning it.

For example, I posted code to facilitate Cycle Counting. The code automatically exports cycle count information to a separate table to be kept forever. After I had demonstrated the capability for the user who requested the project, he actually said to me, “Great, so I can export this to excel and….”

Oh, how they love to export. Tomorrow I will discuss deployment.

Report Guidelines: Know What Your User Wants and Your Target Number(s)

This article is day three in a week of reporting articles.

This seems like a no-brainer, but it is not as easy as you would think. Often your users will not know what they want and certainly not where to get it.

Don’t take direction from this guy.

Don’t take direction from this guy.

Let’s say Milton, a purchasing agent, requests a Vendor Performance Report. After you ask him to fill out your Report Request Form (you are using one right?), you read through it and have a few questions. The first should be, “Who needs this data? Who is requesting the report?”

This is the guy to ask. (Unfortunately)

This is the guy to ask. (Unfortunately)

He indicates in a halting, barely audible tone, “Umm… Bill… the Production Manager… He needs it to rate our vendors. He says if I don’t get it… he’ll take my stapler.” If possible, you should go ask your questions of Bill. Bill knows the answers because he is the person requesting the data. However, I often find that Bill has delegated the task to Milton because Bill is swamped with work himself and can save some time. If you are a Bill, listen to me, no you won’t. We will both waste time because Milton will either ask you all of the questions I ask him, or will likely answer some of them incorrectly. Therefore, Bill, when you check the report and find it incorrect, you and I will be meeting anyway. After telling Milton that you will talk to Bill, he mumbles, “…but I could set your desk on fire…”

With some personalities, a different problem arises. Suppose I am speaking with Bill in this case (the responsible party) and ask something like, “Why do you need this report?”

I want to know the reason Bill wants it so I can lead him through the process and recommend options along the way. This speeds up the process and results in a better report. However, some people will misunderstand you and Bill may think you are questioning his right to the report. If Bill becomes defensive, the process is more difficult. Bill will often respond with something like, “Umm… yeah…. Well corporate says they want it… Maybe you could come in Saturday to work on it….”
All joking aside, to avoid a misunderstanding I usually ask this in a different way such as, “Bill, so that I can create the best report as quickly as possible, may I ask the purpose of it? What are you really looking for?” Bill will often explain a little of his job to you and it’s a win-win situation.

In our scenario, when you phrase the question properly, Bill opens up and tells you that he is thinking of dropping Vendor XYZ for poor performance. He wants a report to document that performance. At this point your question should be, “How? What constitutes poor performance?”

Another reason it is difficult to determine what a user wants is they don’t know what you can actually do with the data. Many users have never heard of “drill down,” especially if the only reports they have used are M2M Visual FoxPro reports, as VFP does not support it. They may also not know that SQL Server Reporting Services and Crystal Reports have full charting and graphing abilities or that with conditional formatting you could show any problem vendors in red. Since the user does not know what you can do, they do not know what they can request.

Also, if possible, know what your target number should be. Some users like to play a form of a digital Easter Egg hunt. They won’t tell you that they already know the answer; they want to see if you can come up with it on your own. I cannot tell you how many times I have created a report, only to find out that I didn’t match the user’s number and my number must therefore be “wrong.” A good example of this is in financial reporting. Most M2M financial reports pull from the general ledger rather than the accounts receivable tables for financial reporting. Trying to hit the same number, especially when you have no idea how they are calculating theirs, is practically impossible due to general ledger journal entries.

Therefore, if you are trying to match a report in M2M (or any ERP system), derive the SQL directly from that report. I will follow up with articles and videos as to how I do this in the near future.

Finally, keep in mind that some users do not want your help in automating their reports. Management has ordered them to ask for your help, but they do not really want you to succeed. Often they perceive a loss of power or job security when portions of their job are automated. This is yet another reason why you should be talking to Bill, not Milton.

Tomorrow I will discuss exporting issues.

Reporting Guidelines: Standardize Your Layouts

This article is day two in a week of reporting articles.

Create standard layouts for both landscape and portrait reports and have them approved by management. Many larger companies have Corporate Identity Guidelines created by marketing experts with standards already defined for marketing and sales documents. Adhere to the standards religiously. If creating your own, you should adopt standard fonts, logos, colors, and style as well for all parts of the report. This standardization makes reports easy for the users to scan and makes them look professional. I’ll share some of the specific report guidelines that I use in Crystal Reports and SSRS in a later article. As I mentioned previously, I wish M2M had a way to customize the style of all of their reports in one place.

Remember that you don’t have to have a degree in design to create a good standard layout, but almost all of your reports should conform to that standard. Be sure to get your standard layout approved by management, and be prepared to change reports over time. Executives seem to enjoy changing these standards, and companies sometimes change their logo and branding. Depending on which system you are using, you may be able to change much of that automatically but often not all of it.

Since I’m discussing standardization, I’ll mention T-SQL queries here. Develop standard SQL templates for the basic departments in your database. For example, since incoming sales reports are popular, create a robust select statement, alias it properly, and use it as the base for all of your sales reports. Doing so will save you incalculable time down the road. As time goes on, I will post more and more of mine to this blog.

Tomorrow I’ll discuss hitting your target.

Page 20 of 33« First...10...1819202122...30...Last »