Report Writer Series: Toolbox Tips

I’m a bit behind in my blogging lately, time to catch up a little bit!  Today I’m digging into some of the lesser-used items on the toolbox, and finishing this topic up next week.  How can I tell these tools are not used much?  The answer is simple: I’ve seen a lot of ugly reports – fields formatted poorly or not lined up with the header fields they match up to, etc.

I do cringe when I see simple things like a block of address fields (for instance) not aligned, when there are tools there for you to eliminate this issue forever.

So far, I’ve gone through a number of things relating to Report Writer, most of which are aimed at an audience of new Report Writer users.  This is likely the last two posts of the “starter” topics that I plan to cover unless something interesting comes up!  There are lots of blogs out there on FAR more advanced topics, which I will point people to shortly, instead of re-inventing the wheel and writing articles on topics already out there.

Today’s Focus

Most of this article is focussed on two areas: the Arrange tab of the toolbox, and then next article will be some notes on Drawing Options.

The “Arrange” tools

There are a few tools on this tab.  If you can’t find it, it is the second tab on the toolbox when you are in the Layout view of Report Writer. CTRL+B opens the toolbox if you have closed it inadvertantly.  By default you always see the Layout tab which contains the fields & tables, plus the most common toolbox items like shapes and text.


Aligning Fields

First, alignment… it’s not an exaggeration to say I use these four tools ALL THE TIME.  It is a rare day if I am modifying a report, that I don’t have to use this to line something up to another field.

What is it for?  Lining up two or more fields together along one of their “sides”.  There are four types, aligning left edges, right edges, top edges or bottom edges.  Note: This does not affect the “alignment” of the text within a field, which is what I refer to as “justification”.  To alter that requires editing the properties of a text field itself in Drawing Options or applying a format in Report Field Options.  More on this later…

How to use it?  First, select two or more fields, using the CTRL key to select multiple text objects.  Second, click on the Align field of choice, which will align all of the selected fields to the left/right/top/bottom-most object you selected.  In other words, if I am trying to line up four fields that are the titles of some data, I might use the Top or Bottom align to ensure they are on the same horizontal plane.  If I have moved some data fields, I might use the Left or Right align tools to line up the title fields to the data fields below.  Since there are often title fields in the Page Header (PH) and Report Header (RH) this makes those little tweaks a little easier.

Unlike other report writing tools, the Align features are all based on the left/right/top/bottom-most field of your selection, and the order in which you select your fields does not matter.  In some other tools, the Align is based on the first or last field you have selected, which does not apply here.

Re-sizing Fields

The Size tools are useful in a few circumstances.  Typically I use these tools if there are fields where the default size is too big to fit on the report.  Often currency fields are very wide, and may not need to be that big.  Making them a little smaller width-wise, may allow you to fit more information on a page, or improve the readability of a report.

What is it for?  Making two or more fields the same size, either by height or width, OR resetting a field to it’s default size based on the font size it is.

How to use it?  Similar to the above Align tools, the first step is select two or more fields.  Then, click on whichever Size tool you wish to perform.  If you hover your mouse over the buttons, a tool-tip will display to show you what each one means.  Each tool will resize based on the size of the smallest/largest/tallest/shortest field you select.  In other words, if you are resizing to make fields the same size width wise, first determine if you want them smaller to match the least-wide field you chose, or wider, to match the widest field you chose.

Similarly to the Align tools, it does not matter which order you select your fields in, as it re-sizes based on the smallest/largest/tallest/shortest field you select.

The “resize to default” option, the middle button of the Size tools, is useful if you have changed font sizes but the field did not automatically resize itself.  In Drawing Options, when you change a font size or style, there is a tickbox called “Resize To Default”.  If that is unchecked, the field size itself says the same, and the text is resized.  Why does this matter?  Well, it doesn’t really, depending on the change but a properly sized field to the text within it is easier to align with other fields.  Sometimes if you increased the size of a font, and didn’t allow Resize To Deafult, when you print the report you will see the text is cut off.  So, this option is there to quickly and easily resize it without having to change the font size or style.  Simply select a field, and click the middle button on the Size palette, and it will be resized.

Tiling Fields

This tools is the hardest to explain, and I haven’t used it often.  However there are times when it really is useful to quickly space items out, in a columnar type of report layout for instance.

What is it for?  This is used to space fields out by a certain number of characters, either horizontally or vertically.

How to use it?  Again, select two or more fields.  Next, type in the amount of space between the fields you want to have, with zero being “no space between the fields”.  Then, click on the appropriate align button to space things out horizontally or vertically.  For instance, let’s say you have a report with five “columns” of information all dollar fields, all approximately similarly sized bits of data in them.  They don’t look right on the page right now and you want to try to space them out evenly.  Select all of those fields, and play with the Space value to get them spaced out correctly.  A tip: the fields say multi-selected when you use this tool, and often it is a guessing game to get the right spacing value.  I’m often putting a number in, clicking on (in this example) Tile Horizontally, and seeing the results, changing the number, clicking it again, etc. until I find the right value for my spacing needs.

Hopefully that helps some users out there!  Next report writer post will be about Drawing Options and wrapping up the series…

By | 2017-08-13T11:46:31+00:00 February 22nd, 2011|How To & Tips, Report Writer|3 Comments

Report Writer Series: Don’t settle for ugly reports! Part 2

Picking up from Part 1, now we are getting into tips and tricks for modifying reports. I am walking through a specific example of taking a report that was in text format (General Posting Edit List), converting it to a graphical report, then modifying it to make it better looking!

The starting point

Here is where I left off last post, with the report layout simply with the Text Report checkbox unchecked.  The fields that were on the right side of the layout before now appear off the page.


Altering fonts & moving fields

My first step was to select all (CTRL+A) and then go into Drawing Options (CTRL-D), to change the font from Courier 11pt to Calibri 9pt.  I figured that 9pt gave me a reasonably-sized font for reading the report and fitting things into a portrait page layout.

Second, I dragged all of the fields that were “off the page” to within the page boundaries as well as making a few field size changes.  Notice even though I’ve moved my fields, there are lots of fields that aren’t in their final spot yet, such as Page Number and User ID fields in the headers.


Fine-tuning the layout

Next, I began to fine-tune things.  I’m taking advantage of the Arrange tab on the Toolbox.  I’ll describe these tools in more detail in my next post.  What I use all the time are the Align and Size tools.  If you are editing reports in Report Writer and manually lining fields up, you are wasting your time… seriously!  Keep reading and use the Align tools and Size tools to quickly and easily line things up and make field-size changes easier by matching other related field-sizes.

So, I started to fine-tune by re-arranging the fields to fit things into where I wanted them.  I moved some of the “legend” fields in the headers to make room for the Page # and UserID.  Using Align features, I lined up the fields with the other Date/Time stamp fields.  I re-arranged the H1 fields (first header, below the page header) to present the information in more of a tabular format (headings above the data).  I also made the titles of these fields Bold to make them visually different from the data.  Re-arranging the data this way allowed me to save a bit of space and reduce the overall size of the H1 section.  This doesn’t make a huge difference but overall reduces paper in the long run, with more information fitting on the page.

I added a black thin line under the primary heading titles (Journal Entry #, Transaction Type, Date, etc.) to visually separate the body from the headers.  I also added a medium grey colour thin line at the bottom of the F2 section, which is the end of one journal entry.  This will visually separate the individual transactions without it seeming too much like a big grid of lines.

On the titles for the body data, the original report had two text fields for a two-row heading/title.  I preferred to save space and came up with shorter titles and eliminated one row of unnecessary text fields.

Last, I changed the error message fields to italic – that way if and when they do appear they may stand out a little more than if they are the same font style as everything else.

Here is what the layout looked like after the fine-tuning:


A caution on removing fields and sections from a report

On many reports you may want to modify, often there may be LOTS of unnecessary fields for your business.  The default reports have a lot of data on them, primarily because every business has slightly different needs.  Since modification is fairly easy, it’s up to the organizations to tweak reports as needed for their specific uses.

One thing to avoid is a wholesale deletion of fields you don’t need.  Why?  Well, there are two reasons I can think of.

  1. The field may be required by Report Writer for some reason.
  2. You never know when you need that field back!

If you take a close look at a sample of reports, you will see a lot of already-invisible fields and some yellow invisible fields plus other legend-type fields that only appear when certain criteria are met.  Often these are calculated fields.  If there are fields that are purely data fields that you want to remove, first try to simply hide them by going into Field Options (CTRL+F) and making them invisible.  Some fields are safe to remove, some are not and unfortunately it’s something you get a feel for over time after modifying many reports.  This is my simple guideline (not a hard-and-fast rule though!):

  • Text fields are usually safe to delete.  By “text field” I mean a field that you can edit with the “A” text editor tool.
  • Data fields are also generally safe to delete BUT if I don’t know what it is, I don’t delete it.  By “data field” I mean a field from a table or global field that was dragged onto a report.
  • Calculated fields I often watch out for, normally are there for a reason!  If I don’t know or can’t figure out the reason behind a calculated field, I leave it alone.  Better safe than sorry.  If a field on the report is ALREADY invisible, don’t delete it – again: it is there for a reason.

Now what do I do?

Start with making a field invisible and hiding it behind another field in the same section.  Don’t drag a seemingly-unnecessary field from one section to another section; if it’s a meaningless field that won’t matter but if that field is used for some reason, moving it to a different section of the report could alter how the report generates and cause other issues.

Sections work similarly, if there is a report that contains a lot of sections that you want to remove, make a backup of the report before taking a section out first so you can go back to a previous iteration if necessary.  Many times the sections may not make sense to you, but they are grouping, totalling, or otherwise summarizing data at a certain level that you may need.  Most data on reports falls into a “one-to-many” table relationship scenario so moving a field from one section to another may not result in the right result.

When removing a field or section you don’t know about, try to back up your dictionary or export your report to a package file first, then delete the section you think you don’t want, then test it.  If all seems ok at that point, then proceed with your changes.  If you delete a bunch of things then continue making other changes, and realize there is an issue later, it’s harder to narrow down exactly which field removed caused the problem.

Ready for testing

Once I feel I am nearly done a report, it’s time to test it out.  I test at different times, depending on the kinds of changes I am making.  On a report such as this where the majority of the changes are cosmetic, I will make the majority of changes first before switching back to GP to take a look.  It’s a different story if I am creating a lot of formulas, adding a lot of new fields or otherwise changing more than fonts and styles; in those cases, I don’t wait too long before testing a formula out.  I don’t want to get too ahead of myself if something isn’t working, the further ahead I go, sometimes the harder it is to determine what went wrong.

Testing highlights a lot of things that you can’t see in the report writer interface, such as text field alignment.  Here are some examples of what you may find if you are close to done.  I’ve highlighted a few issues with field alignment, where it’s hard to see what the field is for, it is so far removed from the title.


A quick note on security for testing

If you are following my recommendations so far, you have made backups AND are editing reports either without others in the system or on a copy of the live reports (with no one else accessing them). If you are re-modifying an existing report, there is less of an impact because security, in theory, has already been set up and the “production” dictionary already has “a” modified version of the report in it.

The challenge is when you are modifying a report for the first time, you need to be careful how you set security and test. In this scenario what I recommend is to grant security only to yourself on this newly modified report. Until a report modification is in the current, active production dictionary, don’t give security to other users otherwise they may get errors if they try to run that report before you are done modifying it. If you forget to copy the report into production, and YOU get the error, you won’t freak out because you will know what the issue is. The ultimate I believe is to give yourself access to this modified report only in a test company so that scenario doesn’t come up. If you end up going back into a production company, you can do your regular work and not be impacted.

The report is complete!

After making some of the highlighted changes and a few other minor tweaks, my report modification was complete.  The last step then is to arrange to bring it into my production reports dictionary and grant security to the appropriate users.  All told, this was a simple report modification and it took probably an hour or so to complete the changes.  Here is the final report output:


Next post I will dive more into some of the specific tools in Report Writer and continue to “demystify” Report Writer!

By | 2017-08-13T11:46:31+00:00 January 24th, 2011|How To & Tips, Report Writer|0 Comments

Report Writer Series: Don’t settle for ugly reports! Part 1

Now that most of the payroll year-end tax update season is behind us, it’s back to the Report Writer series of articles.

You’re not the only one who is sick of looking at ugly plain-text edit lists and searching for the information you want to review. Have no fear, more useful and attractive reports are a few mouse clicks away!

Lots of users have gone to other reporting tools to get better looking reports and I don’t blame them. Crystal and SQL Reporting Services give us a lot of great tools to improve reporting in terms of look and feel as well as flexibility of layout and many other benefits. As much as some of us would like Report Writer to die a slow death, the reality is for posting journals and edit lists and anything with temp tables, Report Writer is “it”.

So, you have two choices: you can put up with the out-of-the-box ugly reports or you can roll up your sleeves and make some reports more visually appealing, enhancing the fields which are of importance to you. The next 2-3 articles will be focussed around how to spruce up your reports, using an example of modifying the General Posting Edit List, converting it from text to graphical and cleaning it up.

First, if you are new to report writer, take a moment to review my background posts to get up to speed.

Begin with the end in mind

With a nod to Stephen Covey, who I believe coined this phrase, best practises dictate that you have a goal in mind when you start a project. If you don’t, one can argue, where exactly are you going? Well, with report writing, the same principle applies: don’t start until you know what you want to end up with. This usually means “mocking up” a report.

What are mock-ups?

By “mocking up”, I mean printing out a sample of a report you want to edit. It’s best to ensure your mock-up represents the typical information that is printed on the report. I.E. ensure you are printing samples where you are typing descriptions or filling in all of the fields you may use in the course of ordinary transactions. Also, don’t limit yourself to “one” of anything – try to have a large enough sample that your report is more than one page. Why? The Page Header often is not displayed on page 1 of a report and you don’t know what the report fully looks like until you print a two-page or more report.

Example of a mock-up

Pictured below is a mock-up of the report I plan to modify. I’m circling fields I want to move or edit in some way and drawing in new items that I want added. In this example I am not removing any fields however that is very common, and putting a big “X” through the fields and field titles you don’t want is an important part of the mock-up exercise. Usually the client or end-users are doing the mock-ups and you should ensure you clearly understand their scribbles and notations if anything needs further explanation.


Some people take this a step further and draw these mock-ups up a lot neater than mine, or even cut and paste the fields on a blank page the way they want them to appear. That’s ok too… the goal is to be clear on what the desired output looks like. Try to avoid the vague “can you just pretty this up for me” approach to report modification! What I find looks great, may be quite unreadable to someone else!

Here is my process when modifying a report, after I have a mock-up ready, and launching GP pointing to my own copies of the reports dictionaries.

Step 1: Check for previous customizations

This may sound obvious, but stranger things have happened. Do a quick check before starting a modification of a report by checking if that report already exists in your reports dictionary. A quick look at the Customization Maintenance window should tell you that, assuming you are in an environment where you can see everything that is currently modified.

If, by chance, you see the report you plan to modify is already listed, you may want to do a little digging to find out what the modified version looks like (if you are seeing the original) and who, if anyone, is using it (i.e. who has security access to the modified version).

Step 2: Modify the right report!

There is a saying, “Good Judgement comes from Experience. Experience comes from Bad Judgement.” I used to modify a report by going into Report Writer then finding the report I wanted and inserting it my reports list. That seemed to work just fine until one day I spent a great deal of time modifying a posting journal only to find out the client uses the multicurrency version of the posting journal – an entirely different report. I still remember the dull thud sound of my head hitting the desk. Ugh!

Learn from my “experience” and instead of that approach, find the report you want in GP and print it to screen, then click on Modify to get into Report Writer. Remember, this approach does not work if there are any reports that will print after the one you want to modify though, but at least in the title bar, it shows you the exact name of the report, to find it in Report Writer!

TIP: Don’t go by the name in the Report Destination window! (it’s not the real name, and doesn’t tell you if you are using a multicurrency version for instance!)


Use the name of the report in the Title Bar instead!


Step 3: Convert from text to graphical

The layout of a text report, for this report, looks like this:


To change it to graphical report, exit the Layout screen, and unmark the Text Report option on the Report Definition screen.


After doing that, the layout will initially look something like this:


Step 4: Start to make your changes!

The first thing I usually do is to mass-change the fonts on all of the fields. If you do this first, you can easily select all fields (CTRL+A) and then go into Drawing Options (CTRL+D) and change everything to a more suitable font than Courier 11pt.   If you start adding graphical elements first, then when you “select all” you will be grabbing graphical items as well as text and the shortcut to mass-change fonts won’t work.

I generally stick with plain fonts for readability – Arial, Helvetica, Calibri work for me. I also try to get away with small font sizes to make things fit better – 9 or 10pt for most text is often a nice size to work with as long as people can read it.  This is purely preliminary – I mass-change to what I want the majority of the font sizes to be and as I get closer to the report being final, I spot-change titles or other fields to different sizes as required.

Next, start moving the fields into place. Moving from text to graphical throws the fields off the page so-to-speak. Consider changing the layout to landscape if your report has more information width-wise than length-wise. Use some of the techniques described in my previous posts about selecting multiple pieces of text to move things around.  I usually focus first on getting things to the right general area before finalizing the exact location and aligning things precisely.

Next post, I will continue with this example and show tips on how to use the Toolbox to arrange fields and make your report a thing of beauty! Until then, here is a sneak peak at the end result.


By | 2017-08-13T11:46:31+00:00 January 17th, 2011|How To & Tips, Report Writer|0 Comments

Report Writer series: Formatting Text

If you have modified many Dynamics GP Report Writer reports in the past, you will have perhaps found that dealing with text fields is sometimes a little tedious.  Here are a few simple tips to help you modify those reports a little faster.

Mass change the font on text-only reports

If you are editing a “text” report, or changing a report that was text-based to graphical, and if you want to quickly and simply change the majority of the font sizes or styles, use the Select All command (CTRL + A) to select all text then select Drawing Options (Tools – Drawing Options or CTRL + D) to edit the text.  I find this useful when making a text report graphical as I can at least change the majority of the fields to a font and style and then go back and edit individual fields that need different styles, sizes etc.

If you are not sure if you are dealing with a graphical or text report look here on the Report Definition window for the text option to be ticked or not.


Selecting multiple fields to edit text at once

The method above will not work if your report is graphical and if you already have graphical elements on the report – lines, boxes, images etc. since the “select all” will select items that will make Drawing Options unavailable to you.  Sometimes using the “lasso” and/or “CTRL-click” methods to select multiple pieces of text is the next best solution.  Once you have multiple pieces of text selected then you can continue to editing them as described above (using Drawing Options).

Lasso: this means clicking somewhere outside of the boundaries of an item, and clicking your mouse and drawing a rectangular area around the pieces of text you want to select.  Note that different programs work differently with this type of mass-item-selection.  Report Writer requires you to completely encircle the item(s) you are selecting for the lasso to “grab” them.  Some programs as long as you “touch” the item, it is included but that is not how Report Writer works.  Another small quirk is you can only lasso in one section of the report at a time.  To get around this, lasso one section, then hold down your CTRL key and continue to lasso around other sections to keep the selections and add to them.

CTRL-Click: this means clicking on an item, then holding the CTRL key as you select additional items.  The Shift key also works the same way in Report Writer.

Copying a format from one text field to another

This tip is courtesy of David Musgrave, who, to many GP consultants is a guru with an endless knowledge of GP technical and development subjects.  See the full post here that I clipped this from.  There is no specific “format copier” feature like Format Painter in the Microsoft Office products.  However, thanks to David’s recent post, he reminded me that you can in fact copy a format from one text field to another.

What you do is first select the text field that you want to copy FROM, i.e. the field that has the font, size, style, etc. that you want other fields to have.  Then you select other fields using CTRL or SHIFT key ensuring the first field is still selected.  When you go into Drawing Options, the properties of the first field are already there, so you only need to click OK for the properties to then be applied to all of the selected fields.  Voila!  Formats are copied!

Changing the “default” font style

This tip is handy if you are adding new text fields to a report and want to avoid the hassle of placing all the text fields and then going back to edit their styles or fonts.  Without clicking on anything, go to Drawing Options (CTRL + D) and set what you want the default font, size, style, colour etc. to be.  From then on (in that session only), any next text items placed on the report will use that style.  Note I said in that session only: unfortunately this doesn’t persist between sessions so as soon as you close the layout window that default is gone.  However, this little tip does make adding large amounts of text quicker as you can set your styles up front painlessly!


Until next post… happy report editing!

By | 2017-08-13T11:46:32+00:00 November 22nd, 2010|How To & Tips, Report Writer|0 Comments

Find the right G/L account with Aliases

Do you have trouble finding the right G/L account to use when you enter transactions?  Do you frequently use the same G/L accounts on your day to day transactions and have the key them in or look them up each time?  Have you recently changed your G/L account numbering and can’t remember the new account numbers?  Aliases may be useful to you if you answered yes to any of these questions!

What is it?

Aliases are a little-used field on the Account Maintenance window meant for an abbreviation or shortcut to a G/L account.  During implementations they are often ignored and left blank.


How to use it

On any window where you can select a G/L account (i.e. where there is a lookup field), there is also a handy little expansion button that looks like this: Pic_ExpansionButtonArrow.  In older versions of Dynamics GP the icon was different – it looks like two small overlapping boxes if memory serves correct.  The alias field is inside the expansion window (called the Account Entry window).  Once you type in an alias, and tab off that field, the Account Entry window auto-closes and the G/L account it is associated with appears in your G/L account field.


  • Click on Expansion Button (blue arrow) or use CTRL-Q in transaction windows (see Things to keep in mind below for a caveat)
  • Type in your alias (case sensitive!)
  • Tab off the field
  • Voila!  Your G/L account appears

Reasons to use it

  • Aliases are ideal for entries that don’t have default G/L account distributions – payables transactions, receivables transactions and journal entries commonly don’t have defaults.  I wouldn’t recommend creating aliases for control accounts such as Accounts Payable or Accounts Receivable since any good configuration will default these in based on the company or vendor/customer setup.  However in any situation where users are looking up G/L accounts, those can be updated to include aliases.
  • Aliases can speed up data entry if your account numbers are long (i.e. multiple segments).
  • Aliases can also reduce data entry errors from selecting incorrect accounts if you rely on numbering instead of mnemonics.
  • Aliases can increase the adoption rate of converting to a new G/L account structure by using old account numbers as part of the alias until users get to know the new account numbers.

Things to keep in mind

  • Aliases are case sensitive.  Before implementing this feature, poll your users to see how they would key in shortcuts.  Do they keep their CAPS LOCK button on all the time?  Would they want to have spaces in the alias shortcut? Consider keeping all aliases similar – all lowercase, all uppercase or in some kind of pattern so everyone knows how to key them in.
  • The Alias field is 20 characters long, alpha-numeric.
  • The simpler your aliases are, the more likely your users will adopt it.  Don’t make the aliases too complicated or they may find it just as easy to look the accounts up manually!
  • Aliases must be unique.  See Tips below for how to handle multiple departments or other areas where it’s hard to create unique aliases.
  • The CTRL-Q shortcut ONLY works in certain transaction windows.  This shortcut does not work in windows like Account Maintenance and it also does not appear to work in ALL transaction windows.  I.E. it does not work in the Miscellaneous Cheque window for some reason.  However, it does work in the vast majority of common transaction entry windows!

Tips for implementing

Tip 1: Export your chart of accounts to Excel using Smartlist and review the accounts to trim it down to accounts where aliases would be useful.  (Delete whatever you don’t need it for).  Then create aliases in your spreadsheet and import them back into Dynamics GP using Integration Manager or your choice of integration tool.

Tip 2: Make the aliases as short as possible – the goal is speeding up data entry, not typing in 20 character phrases!  Abbreviate common distinguishing features.

  • Do you have accounts in multiple currencies?  Consider using either full currency ISO codes or abbreviations of those for currency differentiators.  Example for Prepaid Expenses in say Canadian Dollars and US Dollars, “PPD C” and “PPD U”.
  • Do you have lots of departments or projects in your G/L Account structure?  Perhaps use a mix of alpha and numeric – if department numbers are well-known throughout your user base, then use alpha shortcuts for common expenses and keep the numerics for departments.  Example for Office Supplies “offsupp 100” for department 100, “offsup 200”, “offsup 300” etc.

Tip 3: Create cheat sheets for your staff.  Don’t print a list of all 2000 G/L accounts with their alias but instead look at the patterns you used and create cheat sheets to identify the patterns.  Your users are smart enough to put the patterns together to enter complete aliases!  Example: one list is the common expense codes with aliases like “offsup” = Office Supplies Expense.  If you have 40 office supply accounts because of different departments, projects etc., just list it once with a separate sheet listing the shortcuts for the departments or projects.

I hope this is useful information for some of you!

By | 2017-08-13T11:46:32+00:00 November 1st, 2010|General Ledger, How To & Tips|0 Comments