Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Adding Mapping to SSRS...
Microsoft Dynamics GP (Archived)

Adding Mapping to SSRS reports from GP2013 data

(0) ShareShare
ReportReport
Posted on by Gene Levasseur

I did a search for this and did not find any related results... So here goes.

Have you ever generated an ssrs MAP from GP that plotted the top 10 customers by sales etc.

My goal is to generate a bunch of TOP X queries. Then be able to plot the resulting data on a MAP in SSRS that is then drillable to lower levels (layers) Country down to State, to City then ZIP etc.

My thought would be to add Latitude/Long to the RM00101 and 102 tables.
But I may need a table the points to the CUSTNMBR+ADRSCODE with lat/long.

Any help or thoughts you may have on this, would be wonderful to hear!

Thank you in advance,
Gene

*This post is locked for comments

  • Gene Levasseur Profile Picture
    Gene Levasseur on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    Hi Aaron,

    We do not have the Extender tool, but I will look into it.

    For now, I can just create a new table that has a combo key of custnmbr+adrscode and then lat,long and spatial columns as well as a modified on field to know when the address was last geocoded/changed.  I am already using a third party for NCOA'ing our addresses. They provide lat/long, so this data is already in a referential table.  Easy enough to make it do what I want.

    I am more concerned with the MAPS them selves.

    Thanks,

    Gene

  • Jonathan Fear Profile Picture
    Jonathan Fear on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    Aaron is right. If you want something like this use Extender as you can create whatever type of field you want to store information on a window.

  • amb2000 Profile Picture
    amb2000 1,385 on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    Gene - If you have the Extender tool from eOne Solutions, you can add custom fields to the Customer Master Addresses window in GP. The custom fields will appear under a new menu called Additional. You can add many different field types, one of which is a text field or a numeric field.

    Once the fields are added, you can reference the Extender database tables through SQL to add them to your SSRS report.

  • Gene Levasseur Profile Picture
    Gene Levasseur on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    I was not suggesting changing GP's structure.  But rather using USER DEFINED fields to hold longitude and latitude values.  1 problem is they should be floats not string values.

    The next is I do not have enough available fields free and last, it really looks like I would need a spatial data type to actually do this correctly.  So,  I did not plan on altering GP's tables.  I simply wanted to know if anyone had done this?  Follow up questions would be: What was you map source and do you find the SSRS as a valid method for this type of interactive use?  I have been playing with this for a few days and I am questioning if this is a valid technology.  SSRS makes the Map a Chart.  Not what I am really looking to do.  I am looking for more interaction with the Maps.  A browser based map tool plotting data points may be my only hope.  I don't want users to have to DOWNLOAD software to use this tool.

    Thanks for your responses and I agree ( do not change the GP tables. ) 

    As I just re-read my own post, I can certainly see the confusion.

    Thanks,

    Gene

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    Hi Gene,

    Jonathan & John are right again, We cant alter the table structure in GP,

    In Customer master table, User Defined fields available. you can use to save your (Latitude and Longitude) information to this fields.

    Create a custom SSRS report to achieve the same.

    If you are using User Defined fields already then, if you use Extenders then you can save the above information.

    If you dint use Extenders then you need to create a custom table to save the above information through triggers.

  • Suggested answer
    John Lowther Profile Picture
    John Lowther 5,122 on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    Hey Gene,

    First, Jonathan is 1000% correct. Do not modify any tables in any of your Microsoft Dynamics GP system, the system database (Dynamics) or any of your company database(s). That's just begging for problems in the future.

    But, with that said, here is my suggestion on how to get what you want.

    Create a new database on your SQL server.

    Create tables in your new database that resemble the Dynamics tables that you want.

    And whatever other fields in your tables in your database, not the GP database, that you need.

    Then using any number of free data sources you can connect the addresses to the Lat/Long for that address.

    Lastly create a SQL job to runs on some schedule to add new customers to your database-table from the GP tables where they do not already exist in your database-table, or update the address of those in your database-table if it is not the same as the GP Tables.

    Now just write your report to use your database-tables and your done.

  • Jonathan Fear Profile Picture
    Jonathan Fear on at
    RE: Adding Mapping to SSRS reports from GP2013 data

    You want to physically change GP tables? If you do I can promise you the next time we make a change to those tables you change it is going to cause your upgrade to fail. We do not ever recommend changing the columns or structure of our tables.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,309 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans