Reporting on Extender data in Dynamics GP


Dynamics GP Extender is one of those modules that has so much in it, most people don’t use even half of it.  One of the most underused features is Extender Views.  Anyone that has worked on reports using data entered into Extender windows will tell you that getting data directly out of Extender tables is a challenge.  All dates will be stored in one table, all strings in another, all integers in another…  This is where Extender Views can really help.

Extender Views will automatically create a SQL view from your Extender windows and even let you link in other GP tables.  These views make reporting on Extender data significantly easier.  Below are the detailed steps to create an Extender View.  For this example I am using Dynamics GP 10.0 SP 3 and a pre-existing Extender window called Customer Additional Information which links to the Customer Maintenance window on key field Customer Number.  To illustrate, I will create a view with all the fields on the Extender Window and a few fields from the Customer Master table.  You can click on the screen shots to see them larger/clearer. 

~~~~~

Log into Dynamics GP as ‘sa’ and open Extender (Microsoft Dynamics GP > Tools > Extender > Extender).  Click on Views in the list of Extender Objects on the left, click New.  This will open the Extender Views window:

ev01

Choose an ID, Description and SQL Name for the View.  I typically like to use the same ID and Description as the Extender window.  The SQL Name is what you’ll see in SQL and use for reporting, so make it something easily identifiable.  Maybe even put EXT in there, so you know this came from Extender:

ev021

The next step is adding a primary table.  Since we’re only planning on two ‘tables’ it really doesn’t matter which one we add as the primary, but I typically make the GP table the primary one.  Click Add, choose Microsoft Dynamics GP for Type, Microsoft Dynamics GP for Product, Sales for Series, RM Customer MSTR for Table:

ev03

Click Add, this will bring you back to the Extender Views window where you can decide what fields from the Customer Master table you want to add to your view.  While it may be tempting to click Mark All, this is not a great idea as there are over 100 fields in this particular table, most of which you do not need.  In case you’re looking for them, Extender automatically strips out the DEX_ROW_ID and DEX_ROW_TS fields. 

ev04

To add the fields from the Customer Additional Information window click Add, choose Extender Window under Link to Type and choose you Extender window under Window ID.  (Note: even though it’s called Window ID, the list is actually of Extender window descriptions, which is more user friendly.)

ev05

To define the link between the Customer Master table and the Extender window, click on the + next to Link Fields and choose Customer Number for both the From Field and To Field, then click Add:

ev06

Click Add one more time to get back to the Extender Views window which will now have your Extender window linked in under the Customer Master table.  Click on the Extender window description on the left to see the list of fields on the right, then Mark All to add all the fields to your view.  In this case, since I already added the Customer Number from the Customer Master table, I will uncheck it so that I don’t have it being brought in twice:

ev071

There are 2 Options that you may want to look at before finalizing your view.  Click on the Options button at the top to see these:

ev08

By default both of these options are checked, here is what they mean:

Save Lists and Checkboxes as Strings: If you have a checkbox, having this option checked will show ‘Yes’ and ‘No’ as values in the view.  Having this option unchecked will show these as 1 and 0.  For lists, having this option checked will return the actual list value, having the option unchecked will return the numerical position of the value in the list.  Which is typically not useful whatsoever.  My recommendation: if you only have checkboxes – do whatever you prefer.  If you have lists, leave this checked.

Use Display Names in View: This sounded really good to me when I first saw it, I thought I would get all the fields with nice names.  However, the big caveat to this is that it only applies to the Extender fields, not any other tables.  If checked, this will return the Extender column names as they actually appear on your Extender window, so in my example above, Billing Preference will create a column called ‘Billing Preference’.  With this option unchecked, Billing Preference will create a column called ‘CUST_INFO_BillingPreference’ with the spaces stripped out and the Extender window ID added.  My recommendation: both of these have their uses and it depends what you will be using your view for.  If you are going to be creating a SmartList in SmartList Builder, check this option, as it will mean you don’t have to rename these fields once you bring them into a SmartList.  If you are going to be using this for Crystal Reports, uncheck this option, as Crystal is very finicky about spaces in field names.

Once you’ve brought in all your fields and decided on your options, click Save to create the SQL view.  As soon as you’ve clicked Save, you will see the view appear in SQL under the lists of views for your database.  Remember, Extender is GP company/database specific, so if you have the same Extender windows in multiple companies you will have to copy these views.  (You can do this in SQL by creating a script for the view and just running it against your other databases.)

There is a Preview button on the Extender Views window – you can see what your columns and data will look like before you exit Extender.  And finally, if you ever need to add to or change these views, go back to Extender Views, make your changes and click Save.  The changes will be immediate, so be careful with making changes if you already have reports based on these views.

Update (03.10.2009): David Musgrave has a great post on the limitations of creating Extender Views using this built in functionality and some alternatives for creating your own views for Extender data in his Creating SQL Views of Extender Data blog post.

 

22 Responses to “Reporting on Extender data in Dynamics GP”

  1. Victoria, is this only available in GP10 SP3 and higher? We’re in SP2 and I don’t see “views” as one of the options in the extender objects window.

    Like

    • Joe,

      This will only be available is you are logged into GP as ‘sa’. It has been available since at least GP 9.0. Before that the memory starts getting a little fuzzy. 🙂

      -Victoria

      Like

      • Geeze – I really seem to be having a hard time following directions today! Thanks for the fast response and great article. Incredibly useful as always!

        Like

  2. I appreciate the information on your blog.

    Do you have a list of eOne’s eXtender tables?

    Like

  3. Hi Vic
    I need to extend the field size of categories in the inventory control setup.I cannot seem to find the table description in the item master table .Will extender or modifier work better with what i am trying to achieve .I need to report on item categories as well as sub categories example item 1 = Drilling machine .Category = power tool machinery and the sub category will be impact drills .
    Thanks
    Logan

    Like

    • Hi Logan,

      I would not advise changing any fields in the out of the box tables in GP. This can lead to many issues down the road, including not being able to perform updates/upgrades and losing the data stored in changed fields. If you need to track additional information about your inventory items, Extender is a great option.

      -Victoria

      Like

  4. Hi Victoria,
    Thank you for writing such nice articles. I have issue where I hope you can solve. I created a extender window and have made sure that all of our staff can see it but due to some reason some of our staff are not able to see the extender window, niether by the hot keys nor by the Extras>Additional. It is greyed out for some of the staff.
    Kindly advise.

    Like

    • Hi Zafar,

      Have you confirmed whether it’s an issue with GP security (so logging in as ‘sa’ or a power user on the same computer works, but logging in as another user does not) or with the computer/GP install (so logging in as ‘sa’ or a power user also does not work)?

      -Victoria

      Like

  5. I followed the steps above and I was able to create and view the smartlist report, however on the blank fields I am getting this garbled data: aµ. Do you know why? I noticed that this data comes up on the fields assigned as short or long string in the extender window.

    Also I was advised to not use the smartlist button in the extender window because it leaves behind unwanted data in the db and Microsoft does not have a fix for this yet. Is this still the case?

    Like

    • Dean,

      This is something that got broken in GP 10.0. Basically the issue is that SmartList in GP 10.0 does not know how to handle NULL values in the results. So it shows them as the garbled characters you are seeing. This will be the case for any SQL view you create, not just a view created from Extender.

      If you were creating a view with your Extender window only, this would not be an issue. However, as soon as you link to a another table, like Customer Master, if there is not a corresponding Extender record for each customer, you are going to get NULLs in the SQL results and SmartList does what you’re seeing.

      Unfortunately, the only way I know of to fix this is to alter the view in SQL to account for NULLs. I still think it’s better than writing the view from scratch, but it does add an additional step to this.

      For your other question, I suppose if you had created an Extender Window, added it to a SmartList, then deleted that Extender Window, there could be some remnant data that you may not want in the database, however, this should be a very unlikely event, as Extender Windows should not just be added and deleted at will, there should be more planning and testing (in a separate environment) involved. And this extra data in most cases should not be hurting anyone. So, all things considered, I would opt for more functionality.

      -Victoria

      Like

    • Dean,

      Here is an update on this from Microsoft: this is a known issue (quality report 54219), but no timing on resolution yet. One additional way to get around this: in SmartList Builder create a calculated column for each field that is coming in with garbled data. In the formula for the calculated field, just point to the original field. Hide the original field and show the calculated field. Now you will see blanks instead of garbled data.

      Not sure if this is better that altering the view, that might depend on how comfortable you are with SQL. Also not sure how/if having a lot of calculated fields will affect performance of the SmartList for large data sets.

      -Victoria

      Like

      • Hi Victoria, a day after your reply I tried the calculated field but still got the garbled data. I supposed you meant that the value of this calculated field simply equal the value of the original field, right? I decided to add this additional instruction: +” ” to include an additional space to the calculated field and it did the trick. Thanks for pointing me the right direction. Your such a great help to the GP community.

        Like

  6. Hi
    I have an unrelated question about extender – I’ve just been playing around with it – one of the things I see as an issue is:
    I setup additional notes – but a client using GP wouldn’t know the notes are there unless they click on the additional menu. is there any way to have something display on the actual window to show that there are additional notes?

    thanks!

    Like

    • Vic,
      Very good idea – I sometimes forget where we have notes added, so a visual cue would be great. Nothing I am aware of in Extender will have this feature. You could potentially add a window level note (available on just about every GP window) to let users know about the notes, but that seems a bit counter-intuitive. I think the only options to add any visual cue is with Modifier or a Dexterity customization.
      -Victoria

      Like

  7. Great article, and a good, thorough example. I like your approach.

    Like

  8. Another good topic and a simple/complete narration. I considered this as a nightmare before I read this article, but not anymore.

    Thanks
    Vaidy

    Like

Trackbacks/Pingbacks

  1. About Dynamics, Development and Life - August 19, 2010

    Dynamics GP Customizations (Best Practices, Extender, Macros, Modifier)…

    Dynamics GP has a trunk full of features, but there will be cases when you need to customize GP for certain…

    Like

  2. How to use a SQL view in SmartList Builder - Victoria Yudin - April 20, 2009

    […] This blog – Reporting on Extender data in Dynamics GP […]

    Like

  3. Mohammad R. Daoud : Victoria Yudin on Reporting on Extender Data - December 22, 2008

    […] MVP Victoria Yudin, goes to great lengths to explain how to report on Extender data. Extender can be a valuable time saving customization tool, but data storage can be confusing even […]

    Like

  4. Reporting on Extender Data in Dynamics GP - DynamicAccounting.net - December 15, 2008

    […] on Extender Data in Dynamics GP Victoria Yudin has a great piece up covering reporting on Extender Data in Dynamics GP. Extender allows custom fields without having to write customizations but it's a pain to […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: