Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Using Parameters in Ex...
Microsoft Dynamics GP (Archived)

Using Parameters in Excel Queries back to the GP Database

(1) ShareShare
ReportReport
Posted on by Heather Roggeveen 9,142

Hi

Thanks to a great GP shootout session with Mark Polino, Jared Hall and Belinda Allen earlier this year, I learnt how to create parameters to use in the Excel queries back to the database.  I have tried to search my next question but can't see anything on it so hoping someone can point me to the right direction.

I want to have one Excel query that I can have the database as a parameter.  My client has multiple company databases all on the same SQL Server and using the same ODBC.  I have created a query that they want to use in multiple databases - ideally, we would like to do this from one Excel sheet.

Looking for ideas on the best way to do that.

Cheers

Heather

*This post is locked for comments

  • Peter Bailey Profile Picture
    Peter Bailey 80 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Something that works for me is to create SQL views in a separate database, but the same server as the Dynamics company databases, e.g. I have a MC40000 view that a union query of the MC40000 tables for each company database (plus a column that indicates which company the data has come from). I do this for all the major company tables (and for convenience create views for the major tables in the Dynamics database as well).

    As a result, I have a single source that I can use in Excel queries and which accesses all Dynamics GP data (regardless of which company database or Dynamics database). I can pretty much query the resulting views as you would the data in a single company database (but taking care to add additional joins between the columns that have been added to indicate the source company).

    A purist would doubtless be concerned about potential locking/blocking and creating cross-company views only to access data from  a single company - but it seems to work, and work surprisingly well, for me.

  • Kristie McNulty Profile Picture
    Kristie McNulty 977 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Hi Mahmoud - were you able to develop a method for pulling like data from multiple databases in one Excel retrieve?  My goal:  Payables retrieve for one vendor, all of the vendor's activity, across multiple databases.  The vendor has the same vendor ID in all databases.  I can create individual sheets, but the user would like a one page glance at all of the vendor's data, regardless the database, with the option of entering an invoice number as a search parameter.  I'm sure, once I get the user this first set of data, they will want more, but for now, my hurdle is how to pull across multiple databases in one sheet.  Thanks.

  • Heather Roggeveen Profile Picture
    Heather Roggeveen 9,142 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Thanks for the feedback on this - doing it via Stored Procedures and VBA are a bit beyond my current skill level.  We have a new member in our team that will understand this a bit better - I intend taking your suggestions and working with him next week to try and see how I can do this.

    In the meantime, I just replicated the spreadsheet for each database.

    Victoria - I have briefly looked at your Reports Viewer in the past and must revisit it.  Most of our clients don't use SSRS reports (and I know it links into more than that) - would be good to revisit and see if we have a base here in New Zealand that may find it useful.

    Cheers

    Heather

  • Suggested answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Hi Heather,

    The way I have done this in the past was with a SQL stored procedure in one of the company databases that actually has all your individual queries combined in one place and uses the database as the parameter.

    We are also going to be adding support for Excel reports to our GP Reports Viewer product soon (going to beta in a few days) and that will automatically switch the database connection to whatever company database you are logged into. Please let me know if you'd be interested in being a beta tester for this.

  • Heather Roggeveen Profile Picture
    Heather Roggeveen 9,142 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    No problems at all Mahmoud - I figured if anyone, you would be one who might have this sussed - would love to have the details on where you get to once you are there.

    In the meantime, I think it is going to be easier to have separate workbooks.

    Incidentally, the one I was working on at the client site, I was using Trusted Connection - it takes out one of the issues of user and password.

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Hello Heather Roggeveen 

    We must be in the same boat !

    I have been working recently on an Excel-based macro generator, I have almost completed the main building blocks required to make it dynamic and generic, I have stopped on the point of having a dynamics SQLOLEDB connection string. As you have mentioned, the connection string has the following parameters stored as static values ( User ID=sa;Initial Catalog=TWO ) . For me, having a static value goes against the core target of the tool which should be 100% dynamic.

    This will mostly be accomplished through a VBA code which will automatically retrieve the databases' names from a specific cell within excel. As for the cell, it will retrieve all the databases available in (DYNAMICS..SY01500) under (INTERID) field.

    Unfortunately, I am still facing few glitches which shall hopefully be overcome soon. I will provide you with the final code or any other alternatives to get this fulfilled.

    Apologies for not providing a fully comprehensive answer yet :)

  • Heather Roggeveen Profile Picture
    Heather Roggeveen 9,142 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Hi

    In this instance, my only parameter needs to be the database ID.  Ideally I would like to do a drop down box (which I know can be done with some of the tools in Excel but might be a bit beyond me), but the key is to get the database ID into the connection string within the data connection:

    e.g. replace TWO with whatever I need to get it as a parameter.

    DSN=Dynamics GP 2013;UID=SYSTEM;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=W3009;DATABASE=TWO;AutoTranslate=No;QuotedId=No;AnsiNPW=No;

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: Using Parameters in Excel Queries back to the GP Database

    Hi,

    The only way I know how to do this is by using Dynamic SQL. I'm not for sure on what you are trying to do, but I'm imagining that you want one cell to be the database name and the next how ever many cells to be the other parameters for the select statement.

    Does this sound right?  I've never tried to call a stored proc from Excel, but I'm sure others on this forum can chime in because I feel certain you can do it.

    Kind regards,

    Leslie

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans