Open Forum

Expand all | Collapse all

Excel Drill Downs

  • 1.  Excel Drill Downs

    SILVER CONTRIBUTOR
    Posted Feb 16, 2020 11:46 PM
    Having trouble with excel drill downs.  Getting error "A connection Microsoft Dynamics GP could not be established. Be sure you are logged on to the appropriate Company and try again." error when using Microsoft Dynamics GP drilldown functionality"

    Which I found an article -- {{search404Captions.content404Title}}
    Microsoft remove preview
    {{search404Captions.content404Title}}
    View this on Microsoft >


    But when I run DB Maintenance it runs, and completes and provides nothing in the error log, but doesn't resolve the issue.

    The Drill down still doesn't work.  Do we need some IIS items set for Excel drill down?

    Thanks,

    Justin



    ------------------------------
    Justin Ross
    TD Carpenter & Associates, Inc.
    Sarasota FL
    ------------------------------


  • 2.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 17, 2020 08:53 AM
    Hi Justin,

    Can you please provide the GP drill down link that you using?

    I have this link to GP's item Maintenance with this formula:  =HYPERLINK("dgpp://DGPB/?Db=&Srv=YourGPServerName&Cmp=YourGPDatabaseName&Prod=3830&Act=OPEN&Func=ITEMMAINTENANCE&ItemNumber=YourItemNumber", "YourItemNumber")

    Things to look for:
    1. Make sure you have the correct YourGPServerName
    2. Make sure GP is running with the company that uses the database YourGPDatabaseName open

    Good Luck!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 3.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 17, 2020 09:56 AM
    Justin,

    There are a few things I have seen cause this error. You may have already checked some of them, but going to list them all.

    1. Make sure that on the same machine where you are using the Excel report, that GP is logged in and into the correct company.  If you use Terminal Server or anything of that sort, Excel will need to be run in that same connection or it won't find GP.
    2. The Database Maintenance needs to be run as you found the article for already.
    3. Run repair on the Dexterity Shared Components for which ever version you are using of GP.  You can find this listed in Programs & Features (Add/Remove Programs).
    4. Take the Drill Down URL that you are using and paste it in a Browser URL.  Do you get the same error?
    5. If you change the following URL, update it, and use it in a browser, does it work?
      1. Update "SQL_Server_Name" to be your SQL Server instance.
      2. Update "Company" to be your company database name that you are logged into
      3. Update "Customer_Number" to be a Customer Number in your company.

                  dgpp://DGPB/?Db=&Srv=SQL_Server_Name&Cmp=Company&Prod=0&Act=OPEN&Func=OpenCustNmbr&CUSTNMBR=Customer_Number

    Let us know how that goes.


    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------



  • 4.  RE: Excel Drill Downs

    SILVER CONTRIBUTOR
    Posted Feb 17, 2020 11:59 AM
    All,

    Thank you for helping!!

    So the URL is

    Which appears correct for the SQL name and I'm using Fabricam so the company appears correct also...

    GP is open
    Logged into the correct Company
    I get the error when I copy the url into a browser window

    What seems interesting is when I check the server event viewer I see a different server name etc...


    An error occurred during session monitoring: 'System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at https://gp2016.coronais.com:48650/SessionCentralService that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. ---> System.Net.WebException: The remote name could not be resolved: 'gp2016.coronais.com'
    at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context)
    at System.Net.HttpWebRequest.GetRequestStream()
    at System.ServiceModel.Channels.HttpOutput.WebRequestHttpOutput.GetOutputStream()
    --- End of inner exception stack trace ---

    Server stack trace:
    at System.ServiceModel.Channels.HttpOutput.WebRequestHttpOutput.GetOutputStream()
    at System.ServiceModel.Channels.HttpOutput.Send(TimeSpan timeout)
    at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelRequest.SendRequest(Message message, TimeSpan timeout)
    at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)
    at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
    at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at Microsoft.Dynamics.GP.Web.Services.Session.Service.ISessionCentralService.ReportSessions(SessionHostStatus sessionHostStatus, IEnumerable`1 sessionsToReport)
    at Microsoft.Dynamics.GP.Web.Services.Session.Service.SessionService.SessionCentralOperations.ReportSessions(SessionHostStatus sessionHostStatus, IEnumerable`1 sessionsToReport)
    at Microsoft.Dynamics.GP.Web.Services.Session.Service.SessionMonitor.DoCleaningAndReporting(SessionCleaner cleanerInstance, SessionReportingInfo optionalTerminatedSessionReportingInfo)'.

    ------------------------------
    Justin Ross
    TD Carpenter & Associates, Inc.
    Sarasota FL
    ------------------------------



  • 5.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 17, 2020 12:19 PM
    Justin,

    What is the ODBC setup showing for the SQL Server name on this workstation? It may be that you have an alias or something setup on your SQL Server to expose the fully qualified name instead of the actual SQL server name.  We should be able to use whatever GP is using for the SQL Server name.

    One other thing you might want to check is if all three of these scripts return the same servername.

    select srvname from sysservers
    select @@servername
    select serverproperty ('ServerName')



    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------



  • 6.  RE: Excel Drill Downs

    SILVER CONTRIBUTOR
    Posted Feb 17, 2020 12:35 PM
    Nicole,

    ODBC shows the GP-2016 like the url lists..

    And Yes all three scripts return the same...

    J

    ------------------------------
    Justin Ross
    TD Carpenter & Associates, Inc.
    Sarasota FL
    ------------------------------



  • 7.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 18, 2020 11:14 AM
    The easiest way to know if you are using the GP Web Client is to look at how you access GP.  Do you launch a Web Browser and hit a site to get to GP or is it something run locally on your workstation?  The Drill Downs will not work with the Web Client.

    If you are using it locally on your machine and that is where the URL isn't working, there are a couple of other thoughts before I say you may want to contact Microsoft Dynamics GP support.
    1.  You can try to run a repair on your Microsoft Dynamics GP install.  Maybe a registry key/dll/etc. is missing that it needs to call.
    2.  Try another workstation and use the Drill Down there with GP open.  Does it work?  If you have GP installed on your SQL Server, maybe try it there to see if it works.


    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------



  • 8.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 18, 2020 08:35 AM
    Justin, are you running the GP Web Client here on anywhere in this GP Install?
           ...was no endpoint listening at https://gp2016.coronais.com:48650/SessionCentralService that could accept the message....

    ------------------------------
    David Morinello
    Senior Dynamics GP Systems Architect
    Ascend Learning, LLC
    Leawood KS
    ------------------------------



  • 9.  RE: Excel Drill Downs

    SILVER CONTRIBUTOR
    Posted Feb 18, 2020 09:56 AM
    David,

    That's a great question, and I'm not sure... how does one check?

    Thanks,

    Justin

    ------------------------------
    Justin Ross
    TD Carpenter & Associates, Inc.
    Sarasota FL
    ------------------------------



  • 10.  RE: Excel Drill Downs

    TOP CONTRIBUTOR
    Posted Feb 18, 2020 11:00 AM
    The link you shared is pointing to a GP Web Client Session server service, and maybe a dns? Are you opening GP from a browser?

    I am not sure how that ends up showing in an Excel drill down though.






  • 11.  RE: Excel Drill Downs

    SILVER CONTRIBUTOR
    Posted Feb 18, 2020 01:32 PM
    I am not opening GP from a browser.  However I was told if the drill down wasn't working take this link and put it in a browser to confirm it's working.  Which it is not.

    ------------------------------
    Justin Ross
    TD Carpenter & Associates, Inc.
    Sarasota FL
    ------------------------------



  • 12.  RE: Excel Drill Downs

    Posted Feb 18, 2020 04:18 PM
    Edited by Sarah Brewster Feb 19, 2020 11:36 AM
    @Justin Ross, in troubleshooting this same error, I came across this article from the GP Support and Services blog.  It has a couple things to try which I haven't seen elsewhere, so I'm sharing in the hopes it could help you as well.

    ------------------------------
    Sarah Brewster
    Implementation and Support
    ActivReporter by nQativ
    Lubbock TX
    ------------------------------



  • 13.  RE: Excel Drill Downs

    GPUG ALL STAR
    Posted Feb 19, 2020 11:26 AM
    here the proper link. The one Sarah posted was pointing to an invalid page (due to a space at the end)
    https://community.dynamics.com/gp/b/dynamicsgp/posts/troubleshooting-drillbacks-in-dynamics-gp

    Throwing in my 2cts, this looks like a problem with your GP Web services possibly. I'd run a repair on that part too.
    And to correct some misconception here, your title says "Excel Drill-Down" but what you're actually looking for is "drill-back" , since drill-down is a different action (like in MR 2012).
    The URL that is provided typically in Excel reports (out-of-the-box) when running GP data queries, can only work with a local GP client installed, not with the Web client.  The URL you posted says it all : 
    and that "dgpp://" link can only work if your local Dynamics GP client is fully installed with the shared Dexterity components.
    Hope this makes sense.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role