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.

Reporting Guidelines: Requesting Reports

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

If you are going to become a business intelligence ninja, start at the beginning. You need to develop standard procedures for report creation. They include:

  • Who can request reports?
  • Who approves their creation?
  • Who assigns priority of your work?
  • How are reports requested?

These questions are entirely determined by your corporate culture, however I would suggest writing up clearly defined procedures and sticking to them. As to how my users request reports, I use a standardized form for doing so. Mine is accessed from our intranet site. I can’t provide the page itself as it is owned by my employer, but it includes the following questions:

Please specify:

Report Title
Priority – 1 to 5 with 5 being most urgent
Please state purpose of report and give a brief description.
Can you provide an example? If so, please specify.
What file format does your data need to be? Printed Report, Excel, PDF, Web Page?
Report Layout: Portrait or Layout
Should the report have a chart or graph? Pie Chart, Bar Chart, Line Graph, etc?
Should the report be scheduled to run automatically? If so, please specify.
List the data fields, in the column order, for your report. Eg. Customer Name, Customer Number…
Does this report need to be sorted or grouped? If so what is the criteria?
What calculated fields do you wish to include in your report. Please be specific and include the formula for performing the calculation. Totals, subtotals?
Do you have any other comments or special instructions for your report?

Feel free to steal these and modify them in any way that suits you. The list of questions may seem exhaustive, but if you really think about it, this is the bare minimum of information one needs to know to create a basic report. Even with all of this information, all but the most trivial of reports will require further questions.

If at all possible, go directly to the source with those questions. If the VP of Finance wants the report, but asks the Accounting Manager to task you, try to ask your questions of the VP. I’ll expound on this Wednesday.

Be absolutely specific with your questions and assume very little. For example, when asking for the difference between two dates always ask whether they want calendar days or working days.

Users will often find this process laborious. Be polite and remind them that a thorough interview process will result in a better report in less time.

Do your report request procedures differ from mine? Any suggestions?

Tomorrow, I’ll discuss report layouts.

Basic Reporting Guidelines - Week of Articles

A good percentage of my day is spent writing reports. The report formats could be SQL Server Reporting Services (SSRS), Crystal Reports, M2M VFP reports, or ODBC Excel Spreadsheets. The latter three methods usually involve T-SQL, which I write by hand using TOAD for SQL Server or the SQL Server Management Studio (SSMS). In the past, I have searched the web for report design information, and didn’t find much. Anyway, each day this week, I will offer guidelines and suggestions I’ve learned over the years because I know many of you write reports as I do. For the most part, these will be general and not pertain to any specific report medium. Also, keep in mind that these are simply my guidelines for report creation. Your mileage may vary.

Day One – Requesting Reports
Day Two – Standardize Your Layouts
Day Three – Know What Your User Wants and Your Target Number(s)
Day Four – Exporting Reports
Day Five – Report Approval and Deployment

Lamest Computer Related Scenes Ever Filmed

The other night I was working on the blog and my girlfriend was watching TV, specifically NCIS. One of the characters, who happens to be a ridiculously attractive medical examiner assistant, is looking up data on her computer. Anyway, her computer “gets hacked” by a super hacker and the screen goes nuts. She begins typing furiously trying to fight the hacker and her screen is rapidly pixelating as if it’s some kind of lame video game that she is clearly losing. A male agent helps her by typing on the left side of her keyboard while she furiously types on the right side. Picture Paul McCartney and Stevie Wonder playing “Ebony and Ivory” on the piano, only incredibly stupid. Unfortunately I can’t find a clip of this on the net as this by far had to be one of the lamest computer related scenes ever. Then I thought back on other lame scenes and decided to come up with the following list, just for fun. I managed to find vids of some of them on YouTube.

Independence Day

When the earth is nearly destroyed by a race of aliens light years ahead of the earth in technology they call on Jeff Goldbloom, who plays a satellite communications geek, to save the day. Apparently he does so by interfacing a Mac Book Pro (I think) to a crashed alien ship, which flies on auto pilot up to the mother ship. He is then somehow able to “negotiate with host” and upload a virus using completely alien technology with which he has around 2 days of experience. This virus knocks out the alien’s shields and enables the earth’s forces to destroy them. Hella-lame. However, I did enjoy the mini-scene where the laptop is opened and says, “Good Morning Dave…”

Jurassic Park

So, let me get this straight, this thirteen year old girl is a master of unix and can instantly run every system in the park; meanwhile velociraptors are trying to break down the door and eat everyone like human hot pockets? Right…


A Cray like super computer learns that Nuclear War is pointless by playing Tic Tac Toe. Oh, and the computer just happens to have a voice everywhere it goes? Once, in his home and then at NORAD? Right.

Mission Impossible

Mission Impossible has many scenes moments which are ridiculous. The way that Ethan (Tom Cruise) finds information about his nemesis on the net in less than 5 minutes is astounding. Also, if my memory serves me correctly he uses a email or usenet address with an ampersan in the main part of the address which is impossible. However, my favorite part is the lame dialog that Luther, the cool computer expert spouts about their upcoming mission:

You’re all kidding yourselves. Even with
top of the line crypto. Cray access.
STU 3’s…..

Thinking Machine laptops, I’m talking
about the 686 prototypes … with the
artificial intelligence Risk chip…

Anyone know where I can get a thinking machine laptop with artifical intelligence risk chips? Awesome.

Dark Knight

The cell phones echolocate everything from the entire city and feed that info into a visor. On top of that, Bruce Wayne is so smart and fast that his brain can process all that information in real time? Are you kidding me? Unfortunately, I couldn’t find a video for this.

The Net

Apparently you can tell that a company is virused by hitting a single key to the left of the keyboard. Sandra Bullock finds the virus in a computer game that way. It looks like she’s playing Wolfenstein, on a Mac of all things. Before I get a snarky comment, yes I know that Wolfenstein was released for the Mac, but can you imagine John Carmack (a true genius of programming) calling Sandra Bullock for help with the port? Oh, and of course her computer talks too. Have you ever met a real hacker who thought making their computer into a Speak and Spell would be cool?

Revenge of the Nerds

Apparently when my T-SQL doesn’t work, it’s because I have bad “stroke technique.” If I arch my fingers, then it’ll work perfectly. Nerds!!!!

Superman 3

Richard Pryor’s character is apparently using a computer for the first time. He gets a low level data entry job and miraculously figures out how to gather all the fractions of a penny from the accounting system and refund it to him as expenses. As if someone wouldn’t notice a guy who normally gets $225 a week getting a check for 85 grand. Did you see the magic highlighting computer pen that looks suspiciously like a black sharpie with a telephone cord attached to it? Impressive.

What about you folks? Can you think of other ridiculous scenes focused on computer use?

Twitter Improves My Writing

Twitter improves my writing because it forces brevity and promotes vocabulary with only 140 chars, Inspires creativity for re-tweets
(Actual Tweet)

Previously, I mentioned that I had joined Twitter reluctantly. I thought of Twitter as merely a recreational outlet, but it has increased my productivity and learning. In my relatively short time with it, I’ve learned about free SQL related resources as well as soft skills such as time management and giving better presentations.

To my surprise, Twitter has made me a better writer for the reasons in the above tweet. I have always been a decent writer, but Twitter has shown me that I’m excessively wordy. Often I begin a tweet and then spend a minute condensing my thoughts and words to fit into the 140-character (or less if you want to be re-tweeted) limit. This exercise has actually changed my mindset and I find that I’m not only writing that way in other outlets such as this blog, but even thinking that way in general.

Twitter encourages the user to increase their vocabulary to substitute expressive words that convey deeper meaning while decreasing character count. Previously, I was verbose, diffuse, and loquacious without adding actual content.

Tweeting also encourages creativity and humor. If others find what your tweets interesting or funny they will rebroadcast them and this can result in more followers. Like nearly everyone, I want to be heard and therefore I write better.

I encourage everyone to give Twitter a try; you may realize similar benefits. I am Made2Mentor on Twitter if you’d like to follow me.

User Management Screen Problems

I’ve been in the process of auditing my user permissions, which is a daunting task when you administer a larger user base. I’ll elaborate on the entire project later.

One glaring omission I’d like to mention is the inability to customize the User Management (USER) screen. FastForms excludes it, and Form Customizer throws a red box error when trying to extend it. The USER screen doesn’t contain any User Defined fields.

The reason I mention this is the USER screen needs a control to save the user’s department such as sales, production, etc. I want to evaluate user’s permissions per department so I can verify that my salespeople cannot edit shippers and vice versa.

I could do this manually with Visual FoxPro and VBA, but I think it’s too much trouble.

Never Underestimate Your User. Expect the Unexpected.

Never underestimate your opponent. Expect the unexpected.

Never underestimate your opponent. Expect the unexpected.

Earlier this week, I was reminded of this post from some time ago. Yes I know I’ve already used that picture, but you can ever have enough Swayze. The company in question uses a 4-4-5 Calendar for accounting and this makes reporting more difficult. For example the month of March started February 28th and ended April 3rd. Something like the following will not work:

sum(CASE month (SOM.forderdate)
                   WHEN 1 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
               AS [Jan],

The code is more cumbersome for a 4-4-5 system, but I welcome the challenge and it makes me a better writer of T-SQL. I’ll post the code for how I do this using an external calendar table in a future article. Anyway, I was asked to create an automatic sales report with the following criteria:

  • Sales orders received during the current month (4-4-5). This is of course month to date (MTD), but cannot be calculated using established formulas because the first day of the month is always different.
  • Sales order line items which are due before the end of the year. The end of the year is always 12/31.
  • Include customer number, name, and a grid that looks like the following:

		Cust		Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	Total
Ordered		Acme Rockets	0	0	0	0	0	0	0	165636	0	0	0	0	165636
Shipping	Acme Rockets	0	0	0	0	0	0	0	100000	1250	9999	54387	0	165636

For simplicity’s sake, I’ve only shown one customer order. The report typically has several hundred records. Anyway, I wrote the code and tested it thoroughly. It was set to run as a scheduled job in Crystal two minutes after the end of the business day every Friday.

Everything ran fine for quite awhile until the CFO brought one of the reports back to me which showed an amount shipping in July (the previous month).

Since the code was fairly complex and difficult to write, I kept looking for mistakes. After looking for close to an hour, I decided to change the query to return every sales order represented rather than the aggregates (sums) per order.

When I isolated the sales order and line item, I found that while the order date for the sales order was in August, the due date was a month prior. Armed with this knowledge, I took the issue to the sales manager, and he told me that the information was correct. Company policy is to use whatever date the customer lists on their purchase order, no matter if it’s realistic or even possible.

What did I do? Well, I asked the CFO for a clarification and with his permission changed the code to include any line items with a date prior to the current MTD to reflect the current month in the report.

I guess my point is that you always have to expect that your users are going to do strange things. Expect it, plan for it, or get called on the carpet when your code is “wrong.”

Have any of you folks run into similar situations when reporting from M2M?

Vendors Can’t be Put on Hold and Other Inconsistencies

As I pointed out in a previous article , various areas of M2M function differently. When you spend enough time administering a M2M database you realize that at one time there were several different individuals or teams working on various modules (sales, invoices, purchasing, production, etc) and they did not communicate enough.

For example, M2M does not allow you to put Vendors on hold. There is no way to prevent a purchasing agent from purchasing from a specific Vendor. This is inconsistent with the customer screen (CUST) which allows you to prevent users from selling to certain customers. M2M’s workaround is to change the vendor name to “DO NOT USE” but that is not a very elegant solution.

Similarly, M2M handles releases differently in the Sales and Purchasing modules. The Sales module has a dedicated table for releases while Purchasing integrates them into the items table. For beginners, it can be confusing to report against.

What other inconsistencies have you noticed?

Psst. Buy SQL Server MVP Deep Dives

I haven’t really ever asked my readership (all 3 of you) to do anything, but I’d like to do so now. Buy SQL Server MVP Deep Dives, a book about most aspects of SQL Server written entirely by Microsoft MVPs. From their site:

QL Server MVP Deep Dives brings together the world’s most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs—53 in all—each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you’ll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

Sounds great, huh? Wait, there’s more. All of the authors have signed over their royalty rights to War Child International, an organization that helps children affected by war.

How can you beat that? You learn a ton about SQL Server, and donate to make the world a better place at the same time. I bought my copy yesterday. 🙂

Multiple Bill To Addresses

I had a pleasant surprise today. I called M2M Support to request another change request to allow multiple bill to addresses for customers. Currently, as of Version 5.6, each customer could only have one bill to address. However, this creates problems when selling to very large companies. Typically, you end up with 10 different customer numbers for Acme Rockets and it makes it difficult to find sales orders, invoices, and such.

Anyway, as with the last article that functionality has been added to Version 6.0.