Writing reports the ISoft way
From ISoft Wiki
These specifications are moderated and defined by Josh Duff. He cares a lot about making report the Right Way. If you have questions on creating or maintaining reports (and if it's not defined in this document, you SHOULD have questions), you should contact him.
Why does it matter? These standards matter because they contribute to two different (and very good) things:
- Similar, easy-to-read reports with similar formatting, and a more professional looking product.
- A suite of reports that doesn't eat up all your time with maintenance
Contents |
How reports should look
Crystal Reports gives report developers a lot of flexibility when it comes to design.
Luckily for you, you don't have to worry much about that, because I'm going to tell you how the reports should look!
Font
With the exception of some super-special-case fonts (barcode fonts, or printer-specific fonts), all reports should use just one font. Widely used, sans serif... that's right, we're going with Arial. It's the default, don't mess with it.
Report header
The name of the report (which should generally match the file name, when it comes time to save) should be printed at the very top of the report, centered in the middle of the page. Use font size 20 (bold) - it should fit most titles on the page without wrapping.
Right below that, you want to give details on the parameters that were the main limiters to the data - which for many reports, are the beginning and ending dates the data falls within.
This font should be larger than the column headers, but not as large as the report name - font size 12.
In the case of the limiting dates, use the phrase "From {?FirstDate} to {?LastDate}".
Page header
Without exception (that my awesome memory can recall), the only fields that should be in this section are column headers.
These should be identifying text for the data below them - in terms that the customer is familiar with.
Now, pay attention here, because the Crystal defaults are not what you are going to want to use! Crystal will create default column headers for you that are
- Named after the column from the query
- Underlined
- Smack dab at the bottom of the page header
You want to change all these things:
- Rename the headers to make them more user-friendly
- Make the text bold instead of underlined
- Position all the headers with a little space between them and the bottom of the section.
Grouping
There is one very important principle when it comes to grouping. NEVER NEVER NEVER group by anything other than a unique key (or a date).
| Don't group by | Group by |
|---|---|
| Tag number | inventoryid (Enterprise) or partnum (Pro) |
| Customer name | customerid/customernum |
| Part type name | inventorytypeid/typenum |
No matter how unlikely you think it is that a specific customer will ever have 2 things of the same name (even in cases where it makes NO SENSE to have two things of the same name), DON'T FUCKING DO IT.
Want to group by a value, but not sure if it's a unique key that is safe to group by? Ask a developer.
Group headers
Most group headers will display only one field - a description of the field that was grouped by (in font size [INSERT APPROPRIATE SIZE HERE], thanks for asking).
Now, what to display? Well, it turns out you want to display whatever it is that I told you NOT to group by. Look back at that table up above - if you're grouping by one of those things in the right column, you should be displaying the thing on the left. Weird, huh?
Occasionally, you will have some other data that is only relevant to the group, but is not a total of the data. In this case, your goal is to try to make it as unconfusing as possible (hopefully keeping the columns in line with the columns in the details section).
This is where totals go! If you don't have any useful totals to display for a particular group, just suppress that section.
For a report with multiple groups, you will want to have a line on the left side of the group footer (left-aligned) saying something like "Totals for [group description]:".
If your detail section has a lot of number values next to each other, the displaying their sums in the footer might get pretty crowded. In these cases, you may want to move every other total field down, so that the totals are overlapping - giving them twice as much space.
PERCENTAGES: Let's try and standardize these with a percentage sign on the totals (if there's room) and only a single decimal place (to make extra sure the reader doesn't get it confused with a dollar amount). Also (yet again in the interest of increasing readability), these should be LEFT-aligned (or at least centered... anything but right-aligned) as long as there's sufficient room.
Detail section
This is where the data hits the fan. Query results go here.
The top of the fields in this section should be aligned to the same point vertically. The sides should match up with the page headers horizontally.
All text and document numbers should be left-aligned.
All quantities and currency amounts should be right-aligned.
All dates should be left-aligned, and displayed in the system short format.
Currency amounts do NOT need a currency symbol on either side (horizontal real estate can be tight in this section!).
To increase readability, detail rows can alternate between white and gray backgrounds by doing the following:
- Go to Menu -> Report -> Section Expert
- Click Details in the Section: box on the left and the Color tab on the right
- Click on the Edit Formula button (X-2).
- Insert the code:
| if RecordNumber mod 2 = 0 then COLOR(228,228,228) else crNoColor |
Group footers can also alternate colors. This is useful if the report has a Summarize option:
| if GroupNumber mod 2 = 0 then COLOR(228,228,228) else crNoColor |
Left aligned against the left-most vertical guideline, there should be a text object containing the special objects {Print Date} and {Print Time} with a space between them.
Right aligned against the right-most vertical guideline should be the special field {Page N of M}.
Keeping reports easy to maintain
Vertical guidelines
Guidelines are the little gray arrows that get created when you click on the ruler in the report editor. Any fields that are attached to a guideline will have a bit of red border on that edge.
What should have a vertical guideline?
Every field in your detail section should have a vertical guideline on each side of it. The headers and summary fields associated with those fields should use the same guidelines.
No other fields should be attached to the vertical guidelines, except for fields on the "right side" or "left side" of a report. Those fields should line up to the left-most or right-most vertical guideline, respectively.
Why?
Let's say you need to insert or delete a column from a report, and resize all the other columns accordingly. It's much easier to perform that operation on this report:
than on this one:
If you look at the original report above (without vertical guidelines) you will see that not all of the fields line up with their headers. That's pretty messed up - but it's the sort of thing that happens easily when you don't use guidelines.
Tempted to not use vertical guidelines because they don't seem that important? Suck it up. Modifying reports without vertical guidelines is the leading cause of high blood pressure in Duffs.
What about when there is a left-aligned field directly to the right of a right-aligned field?
Ah yes, the famous Special Case. In the event that you are displaying a number to the left of a text field, you will need to set up two vertical guidelines, such that the fields do not share a guideline.
In all other cases, your fields should get all chummy about sharing those vertical guidelines.
When to make a custom report
Deciding when to make a new custom report is tricky.
There is a significant cost every time we fork a report into two similar versions. As Crystal Report files are stored in binary format, it is impossible to merge changes from one to another.
Because of this, it takes a LOT of overhead (read: wasted time) to make the same change/fix to multiple versions of the same report. Since it is so easy to focus on fixing the problem for the customer in front of you, this usually results in one version of the report getting fixed, and all the other custom versions getting left behind.
How to consider custom reports
We need to think about custom reports like we think about development in the rest of ITrack. When a change or customization is requested, consider it based on these points:
- Is the customer asking for something that can already be answered by another report, or an existing feature in ITrack?
- Can this change be made to the original report without disturbing any other customers' use of it?
- Is the customer asking for something that justifies a completely new report?
Generally, we want to avoid creating reports that share a large amount of overlap with an existing report.
Before creating a custom report, it is probably a good idea to ask a developer.
Writing queries
If your queries have logical errors in them, your reports will fall on their face eventually. And by your reports, I mean you.
How many queries?
To make your life easier, write simple, easy-looking queries that make it obvious what each row represents - if you can return one object per query, that is the easiest setup to work with.
You certainly can return invoice information and lineitem information in the same query. For some reports, it may be necessary. But if it's not, use teh powerz of the Crystal Database Expert to create TWO queries - one that returns invoices, and one that returns lineitems. After Crystal 9, report-side JOINs are downright reasonable.
The more objects you try to retrieve in a single query, the greater the chance that you will get confused and fuck something up (or another person will fuck something up when they try to modify the report later).
The Crystal Reports Editor will automatically try to link your queries together. It will most likely link them incorrectly.
Make sure to double-check the linking so that it is done on the correct IDs (as if you were JOINing them together).
In the case of unrelated queries, where one of them is returning a single line in the result, make sure to hit "clear links" to get rid of the wrong link that Crystal will try to add.
See also (seriously)
One final, VERY important note: make sure you understand everything about Writing MySQL queries - it all applies to writing reports.
