web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How do I add an SQL Ta...
Microsoft Dynamics GP (Archived)

How do I add an SQL Table from a different Database (on the same server as GP) to report on it through Smartlist/Excel Builder? Is there a way to create it as a Data Source? I am on GP2010 R2 and the server is on SQL 2008 R2. We created a custom program

(0) ShareShare
ReportReport
Posted on by Kevin Klunder 175

Is there a way to create it as a Data Source?

I am on GP2010 R2 and the server is on SQL 2008 R2.  We created a custom program to defer revenue for us and I just want to be able to report on that table in GP.  It has key values that will link to other GP Tables.  It would be ideal to take advantage of the reporting capabilities in the Builder’s to report on this.

*This post is locked for comments

I have the same question (0)
  • Brady Riopelle Profile Picture
    Brady Riopelle on at

    Hi Kevin,

    You could try scripting the table out and moving it over to the correct database on the GP server. After you have brought the table over, you will have to add security to smartlist builder in order to add SQL Tables and from there you can create your link and your smartlist. Hope this helps you get started.

  • Ian Grieve Profile Picture
    Ian Grieve 22,784 on at

    Depending on how you're going to do the reportig you could add a linked server in SQL which would alow you to do queries which joined between the two databases.

  • Suggested answer
    Tim Foster Profile Picture
    Tim Foster 8,515 on at

    Why not create a SQL View in the Company database?

    This SQL creates a view of a system Multicurrency table in a Company database.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [dbo].[X_MC40200VW]

    AS

    select * from [Dynamics].dbo.[MC40200]

    GO

    GRANT SELECT ON [dbo].[X_MC40200VW] TO [DYNGRP]

    GO

    You can replace the reference to the Dynamics database and table with the database name and table from your other application database.

    Remember to add the View to SmartList Builder and grant security properly - then you can use it and join it to Dynamics tables.

    Tim

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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans