Quick Excel Tip to roll down formulas in Dynamics GP excel reports

I don’t know about you folks but it used to seriously frustrate me when you got a data set out of Dynamics GP into an excel report, created lots of funky formulas in your columns, then refreshed your data and got extra rows returned and your formulas did not copy down.


With the advent of the excel based reporting and other dynamically updating excel reports in Dynamics GP you come across this more and more.



Well there is a simple tick box in excel which will solve all your problems.

Goto the DATA tab on the tool ribbon in excel.

On the connections section next to the refresh All option you should have connections and under that properties.

Now properties may be greyed out., if it is click your cursor into one of the cells on the report which contains returned data. Properties should now be enabled.

Select properties and this should open the external Data Range Properties Window.

At the bottom of this is a tick box for “Fill down formulas in columns adjacent to data”

Tick this box.



Now when you refresh your data if extra rows are returned your formulas will automatically copy down.

Dynamics GP 2010 word templates (more required fields SOP Blank History Invoice)

Regular readers will now we put up a post in October 2010 detailing which vendorid field was required to allow the word template version of the remittance advice to email correctly.


http://intouchdynamicsgp.blogspot.com/2010/10/dynamics-gp-2010-word-templates-and.html

Well we have found another such required field, this time in the SOP Blank History Invoice.

SOP Blank History Invoice only works if the SOP Number from SOP Transaction Amounts History is the key break field on H1 and F1 in the Dynamics GP report. In other words “SOP Number" needs to come from the SOP HDR History table instead of the SOP LINE History table.

We are generally finding these sort of issues where a report has been modified in a prior version and then upgraded to 2010.

Here are the steps to change this:



1. Open up Report Writer and change Header Dummy 1 to use the Sales Transaction Amounts History table. Make sure the field SOP Number is selected as the break field.

2. Change Footer "Back Order" to use the Sales Transaction Amounts History table. Make sure the field SOP Number is selected as the break field.

3. Save the report.

4. Go back into GP and print the report to Standard > File > XML File. Make sure you select XML file in the file name window as well. Generate the XML File.

5. Reports>Template Maintenance. Pull up their template and click Modify. In Word, go to Field List and choose Remove Source.

6. Then choose Add Source and point it to your XML file from step 4.

7. Save the template.

8. Go back to GP and click the Green plus sign and point it to the newly saved template from step 7. Replace the template.

9. Now go email the report and it should work.