Tag Archives: Microsoft Dynamics GP

Fun with SSRS

It seems an increasing amount of my time is spent building reports in Smartlist Builder and SQL Reporting Services. I thought it might be fun to share a couple of SSRS reports I’ve been working on lately.

The first is a Customer Statement. If you use GP customer statement forms, you are probably aware of the limitations in modifying the standard report format. Even the Word template format is cumbersome to modify, so using SSRS to print your statements is a great alternative. You can make an SSRS format as simple or complicated as you like.

I usually begin the process of any SSRS report by building a SQL view containing all the necessary information. In the case of customer statements, there is an additional level of complexity if you want to add apply detail, and most of us do! In my case, we needed a report showing all the open invoices along with all applied open payments and any payments applied during the selected date range.  My customer wanted to be able to enter a date range and select which of their customers would receive statements.  The final product is shown below.

This is a statement from my own GP environment. I’ve removed customer details to protect the innocent, but when you open the report in designer, you will see the Customer ID field and the address block. 2014-07-06_1300

As you can see, it’s a pretty simple format. Once you open the RDL in SQL report builder, you can change the logo and make any formatting modifications you like.  Just note at the invoice detail is in the main report and the APPLY detail is contained in the sub report. If you add columns in either place, you will need to adjust the formatting so that everything lines up.

The SQL view I used can be downloaded here.  (Remember to right-click and save as!) In order to use the RDL I  have provided here, you will need to deploy this SQL view to your environment.  You will also need the SQL view behind the apply detail sub report here (thank you, Victoria Yudin!) and the associated RDL for the sub report here.

If you are familiar with deploying SSRS reports and SQL views, you shouldn’t have any trouble with these.  You will need to point to your GP datasource and ensure that the appropriate security is set to the SQL views and the newly deployed reports. TEST, TEST, TEST to make sure the report shows accurate data for you!

The real power of this particular statement format is that you can print them for any date range. You can print a customer statement from the beginning of time until the last month’s cutoff, or you can print a partial month. You can print statements for all customers or for a select few. I’ve added a comment field as a parameter to accommodate one of my customers who needed to be able to enter a dynamic comment to print on all statements in a print run.

One challenge I have found in SSRS reports in general is how multiple pages are rendered, so if you have long statements, make sure you test with several different scenarios, as you may need to adjust the format to get it perfect.

 

 


 

The second SSRS report I want to share is a simple sales document.  This one uses the actual GP tables SOP10100, SOP10106 (header comments) SOP10200, and SOP10202 (line item comments). If you require more customer billing details, you could easily link the RM00101 (customer master) or RM00102 (customer address master) as well. 2014-07-06_1428

Once again, I’ve made this pretty simple, but additional fields can be added easily. For end user ease of use, you could also write the necessary code to render SOPTYPE as a text field. For simplicity, I have left it as a number from 1-5 to represent QUOTE, ORDER, INVOICE, RETURN, and BACKORDER.

Also, additional parameters would make this more usable depending on a company’s business processes for printing or generating invoices. For example, you could add parameters for customer class, customer, city, state, priority code, etc.

Finally, for return document amounts and for fields such as markdown and trade discount, you may want to modify the expression to render these as negative numbers. This is very simple to do using the SSRS expression builder.

The RDL files for this basic SOP document are here (main doc) and here (line detail). Again – right-click and save as to download the RDLs. Otherwise, they will open as XML.

Again, deploying these is a matter of uploading them to your SQL Reporting Services environment and making the necessary changes to the datasources.

Once you’ve begun using SSRS for documents such as these, you begin to see the power of using the same format for historical documents. By simply making a change to the dataset, you could pull all historical invoices and define parameters to print by a date range, batch ID, etc. Posting a batch of invoices prior to printing is no longer a problem – simply use SSRS to print any and all based on your customer parameters.

Well, that’s all I have for now.  I hope you will have fun using these templates and share your own developments!

 

 

 

4 Comments

Filed under Microsoft Dynamics

Getting the Most Out of Dynamics GP

Murphy’s Law states that if something can go wrong, it will. Perhaps the greatest antidote to stave off Murphy (or to lessen the consequences of a problem) is preparation. Q Factor strongly encourages all of its clients to perform regular practices to get the most out of Microsoft Dynamics GP. Dynamics GP takes well to customization, whether in creating accounting reports or allowing changes to the system, and we recommend two habits to help save a lot of headache for every company: making server notes whenever changes are made, and performing regular backups.

Whether your in-house accounting team is one or twenty, it is important that everyone who touches Dynamics GP make note on the server what changes and improvements they have made. The reason to make notations about any system modifications is so that, in case the “improvement” turns out to have unanticipated, or even has unwanted results, you are able to revert to a previous state. Often, you will not realize you made a mistake three steps ago until you’ve moved forward far enough to realize its impact. Without notes to understand what has been tweaked, it becomes a much bigger problem than it has to.

Additionally, if you have a team member leave the company, these notes allow for continuity. Many who are taking over the use of GP may not know the foundation of what comes with the software compared to the changes a team member introduced to accommodate your company’s business processes. Making notes can help your current and new team members understand a colleague’s stopping point. This also makes it far easier to have more members of your accounting team working with Dynamics GP, because they can follow one another’s improvements.

Regularly backing up GP-specific files, your team can be confident about protecting the efforts being made. Backups can also allow you to roll back to the prior state if a recent change interferes with the effectiveness of GP. Typically, the accounting team and the IT team are not working or communicating with each other effectively. If there is no one in the accounting department setting up backups of data, and no one has communicated the importance of such backups to IT, you can assume you’re not getting the backup you need.

Daily backups are critical to protecting your data. As your accounting team works to improve notation making habits, you should also be onboarding IT members to regularly back up the data that sits on the server.

Leave a comment

Filed under Microsoft Dynamics

Modifier w/VBA – an example

I’ve had people ask me why they would ever need the Modifier tool, and what use is VBA in enhancing Dynamics GP? Well, it has come in quite handy over the years.  This is one example of a customer request where Modifier and VBA were the perfect tools for the job.

The Modifier is a tool that allows users to make simple visual changes to Dynamics windows, for example moving fields around or removing unused fields from the user space.  VBA can be used in combination with the Modifier to add to existing Dynamics logic or sometimes stop the logic from occurring, for example when someone clicks a button on the window.

Disclaimer: this article assumes a degree of experience with the tools, or at least an adventurous spirit… so if you’re the type of person who can take an example and run with it, this is for you!

Following is the request I received from my ideal customer (You gotta love a customer who provides screenshots and mock-ups of their desired outcome!):

 Slide1

Slide2

 

As I said, a perfect scenario for a Modifier/VBA solution, so here’s how I addressed it step by step.  This particular customer has a systems analyst who administers their Dynamics GP environment, so these are the instructions I gave him.

1.  Open the IV_Item_Inquiry window in modifier

2.  Add a button for internet info. I named this PB_World, but it can be called anything. Just change the vba references below to your field names.

3.  Doubleclick the Field field in order to open the definition window and click the elipse (…) to set the graphic to the appropriate one (see below)

Capture1

Capture2

4.  Add another button for the backordered link

Capture3

5.  This one will be set to invisible, but leave it displayed until the testing is completed

6.  The Backordered text field can be changed to be blue and underlined using the properties box. This will make it consistent with the other hyperlink fields on the window.

7.  Once those are done, open the modified window in GP and add it to VBA (if it’s already in the VBA project, you only need to add the two new fields that you created) – this example is in version 10, but the window should look the same.

Capture4

8.  Once you’ve added those two fields from the item inquiry window, you’ll need to add the internet information window to VBA too, along with the appropriate fields:

Capture5

9.  Open the sales item inquiry window and add the appropriate fields from there:

Capture6

10. Finally, insert this code in the VBA project:

Private Sub PBWorld_AfterUserChanged()
InternetInformation.Open
InternetInformation.SelectInformationfor.Value = 4
InternetInformation.MasterID = ItemInquiry.ItemNumber
End Sub
Private Sub PushButtonM20_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
SalesOrderProcessingItemIn.Open
SalesOrderProcessingItemIn.Include = 96
SalesOrderProcessingItemIn.Items = ItemInquiry.ItemNumber
SalesOrderProcessingItemIn.Redisplay.Value = 1
End Sub

 

In order to deploy to additional clients, simply copy the Dynamics.vba from that installation to the other workstations and you should be good to go.

Note: I had to experiment with the selection on the sales order processing item inquiry window to get to the 96 value. so it may be necessary to try various values to get the correct selection for your project.

Leave a comment

Filed under Microsoft Dynamics, Modifier, VBA

Demystifying Dynamics GP: Welcome to Dynamics World

A lot of people have been using Microsoft Dynamics GP (formerly Microsoft Business Solutions, formerly Great Plains) for many years, so you’ve already encountered corrupt reports dictionaries.  You know what to add to the Dex.ini in order to create troubleshooting logs.  You can say “been there, done that” when the error dialog box pops up with an ODBC or SQL error. You may even remember hearing “Welcome to Dynamics” when you logged in (it’s been awhile, but I do recall having a customer that wanted to change the voice so it sounded like Antonio Banderas instead of that pleasant woman…)

But for those of you who are new to the Dynamics GP world, this is an introduction to underlying architecture which I hope will explain some of the issues reported every day and help you resolve them.  I like to think of it as the Dynamics rendition of the good, the bad, and the ugly: The DIC, the EXE, and the SET.

Continue reading

3 Comments

Filed under Microsoft Dynamics