Posts Tagged ‘Report Writer’
Printing a different company name and address on your checks based on the location assigned to the checkbook – report writer fun
The default check stub form in GP assumes that you have purchased pre-printed check stock with your company’s information such as name and address pre-printed on the checks. But, let’s say the check stock your bank sent you doesn’t have this information? And let’s for a moment also say that you have multiple checking accounts and you want to print different information on each check based on which account you are printing checks from. For example, one account might be for your LLC, and another might be for a trust account, and you want that information to print on the check, i.e. “Fabrikam LLC Operating Account” and another say “Fabrikam LLC Trust Account”
This seems like a simple idea, but it can get a little complicated. GP assumes that you purchased pre-printed check stock with that information printed on the check, and so you have to actually customize the check form to print the company name and address information assigned to a specific checking account.
I’m going to show you how, so roll up your sleeves and take a deep breath as I walk you through the process. There are a lot of moving pieces here, so pay close attention, if you miss a step then it may not work as intended.
First, we’ll go through the setup part on the GP side. You’ll go into your checkbook in GP and assign an address to it, by going to Cards –> Financial –> Checkbook. Open up the checkbook you want to assign an address to. Notice the Company Address ID is missing:
Click on the hourglass next to company address ID, and select an address ID that you want to assign to this checkbook. If you do not have one setup yet, you will click on the blue underlined company address ID label, which will take you to the address window:
Fill in the new address information, note that this information will be how it prints on the check so make sure you name the company name and address exactly how you want it to appear on your new checks and click Save, and close the window. Then, select that new account in the Company Address ID field on the checkbook maintenance screen to assign it to your checkbook, and click Save on the Checkbook Maintenance screen to save your changes.
Phew! Now we’re done what next? It’s time to go to Report Writer and customize that report!
Click on the Microsoft Dynamics GP Logo and click on “Tools” then “Customize” then “Report Writer” (you can also press Alt-F9 to open Report Writer from GP).
Once you click on the reports button, you will see all your reports that you use in GP. If you’ve never customized any reports, the modified reports side should be blank. Scroll down through original reports, and locate the “Check With Stub on Top and Bottom – Graphical” and click on the “Insert” button to copy it over to Modified Reports:
Now, you’ve copied the form over you can edit it by clicking on the form and clicking open. When you open it, you still don’t see the report, you see this report definition box pop up:
Before we go look at the layout, we need to add the company address fields to the check report. To do this, we need to link the table since the company address master table is not normally part of the check report, so click on “Tables” to open up the table window:
This window shows you the tables that are included by default on the report. We need to add a few more tables so we can get that address information we added to the checkbook earlier. So, click on New, and you will add the CM Checkbook Master table by clicking highlighting the CM Checkbook Master and clicking OK:
Now you’ll see we have the checkbook on our report which has the “Address ID” field in it, but we will also need that Company Location Master to get the name and address to print on the check. Click on CM Checkbook Master and click “New” which will open up the related tables again:
You can go ahead and close this window by clicking close and we’ll move on to the next step of the process, the exciting part! Adding these new fields to your report! To do this you will click on the “Layout” button on Report Definition to pull up the layout of your check form:
Oh no! It’s a big scary window with all this stuff, what do I do? Don’t panic! You’ve made it this far, we’re getting closer. If you look at the report layout screen you will see this is the default layout for printing a check, with the various fields that go on the check stub as well as the check. Off to the right of that, we have our “Toolbox” which is where we can add fields to the report, or add a text box to the report by clicking on the “A” font button. Behind the toolbox there is a properties window. When you click on a field on your report, the properties window pops up which allows you to edit your fonts various attributes of the field.
Before we go ahead and add that company name and address to the report, there is one more housekeeping item. Because you’re going to add a customized address to the report, rather than the standard one, you will need to setup a City-State-Zip “Calculated field” so that the City, State and Zip can print perfectly on the form, otherwise you’ll have a bunch of blank spaces in between the 3 fields because the system will interpret whatever field size, so if City has a max size of 40, and your city is only 20 characters, you would have 20 blank characters. To fix this we do as follows:
- Give it a name such as “Calc City-State-Zip”
- Change the Result type to “String”
- Expression type “Calculated”
- You need to trim the address, so first you will go to the functions tab, select system-defined, and locate the STRIP function, click add
- Now go back to the fields tab, select the Resource “Company Location Master”, and select the “City” field and click Add
- Then in the calculated box where it’s adding your fields, click the mouse pointer just off to the right of the ) so it looks like this:
- Then click on CAT to add the CAT symbol (#) which will tell it to concatenate the fields together into one
- Then click on the constants tab, change type to String, and in the constant box type , and a space and click Add
- Then click on the CAT to add another (#)
- Then add the strip function again under the functions tab, and click on fields and select the State field and click Add
- Click your mouse pointer just to the right of the ) like we did in step 6
- Click on the CAT to add another #
- Click on Constants tab and select type of string and type a space in the constant box and click add
- Click on the CAT to add another #
- Click on the Functions tab, and add the strip function again, then go to the fields tab and add the field Zip Code from Company Location Master. When you are done it should look like this:
In the Report Layout box, you’re going to look at section F2 – Check & Hdr – this is where you want your company name and address to print, probably in the top left corner of the check, much like a return address on an envelope. Locate your toolbox with all your fields, and you’re going to click on the little down arrow next to the list of tables, and select “Company Location master”:
Scroll down the list until you see “Location Name” , and drag it onto the report under check & hdr:
Go back to the toolbox, and select the field “Address 1″ and drag it under location name…
Lastly, go back to the toolbox, and click on the drop down where company location is and change it to “calculated fields”, and select the Calc Company City-State-Zip field that we created earlier and drag it onto the report. When you are finished it should look like this:
Now that you are done editing your report, you’re going to return back to GP by going to “File – Microsoft Dynamics GP” … Important NOTE: Make sure when prompted to save the report layout that you click Save or you will lose all this work! It may ask you twice, if so, click save both times…
Back in GP, you will now need to give your users access to the report. You will need your system password for that. Click on Microsoft Dynamics GP –> Tools –> Setup –> System –> User Security and enter your system password.
In the security setup window, select user sa, and select your company from the company drop down, and then at the bottom, click on the “Alternate/Modified Forms and Reports ID” blue underlined text to bring up the report screen. You will need to enter your system password again.
In this window, you will select Reports Under type and it will bring up a list of reports. Expand the Purchasing by clicking the Plus next to Purchasing. Then go to the “Check With Stub on Top and Bottom – Graphical” and click on the plus next to it. From there, you will see there is a modified report, so click the radio button next to the modified one so it’s selected, and click save at the top.
Once you click save, the window clears, go ahead and close the Alternate/Modified forms and reports window, and also close the security setup window.
You are officially done! Now you can go to print your checks. NOTE: Make sure when you print your checks that the stub you just customized is the one that is selected in the Check Format section, it should say Stub Top/Bottom – Graphical like this:
And here is my pretty check with the company location name and address printed on it!
I hope you have enjoyed this lesson in Report Writer and customizing the checks!
Microsoft Dynamics Great Plains uses a reporting tool called Report Writer. I’ve given it somewhat of a bad rap in the past. I hope I haven’t hurt anyone’s feelings with my disparaging words.
One of the most common errors I see people referring to is the “Unable to open customizations dictionary” error when trying to import a new report into the reports.dic file. The resolution to this error is talked about in tech doc 919440 and 869323. Nine times out of Ten the resolution includes getting everyone out of GP then trying to import the file again. (Tools>>customize>>customization maintenance).
I suppose if you don’t have much to do, or have more than a few users and those users don’t have that much to do, it’s not really a problem to kick everyone out of the system. But often I’ll hear “Oh fiddle sticks, I’ll have to wait till tonight to do this” as there’s no way to kick everyone out of the system during the day.
When I have the patience and energy I use the following steps to bring in the new reports so that users can still be in the system and the import is still successful.
- Make a backup of the Reports.dic file
- Save your package file where you can browse to it from Dynamics
- Change the path to the reports.dic file in the dynamics.set file to a new location. (By default the Dynamics.set file is in the GP folder where Dynamics is installed. Open with notepad and the reports.dic file is the 3rd line down where the paths start to all the products.) Change the path to a new location or simply change the reports.dic file path to end with reports2.dic instead of reports.dic
- Launch Dynamics and open Customization Maintenance (Tools>>customize>>Customization Maintenance)
- Import the new report from the package file to the new reports2.dic file
- Exit out of Dynamics
- Change the reports2.dic path back to what it started with in step 3, in our case ending with reports.dic
- Launch Dynamics and open report writer (Tools>>Customize>>Report Writer)
- Choose Import on the right
- Browse to the Reports2.dic dictionary file, highlight the report and press insert (You may be asked to overwrite the current report if you already have that report in the system), then press “Import” at the bottom to bring in the report.
Not the easiest but works without getting everyone out of the system.