web
You’re offline. This is a read only version of the page.
close
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) / Great Plains - Help Cr...
Microsoft Dynamics GP (Archived)

Great Plains - Help Creating Invoice Report (Crystal Reports)

(0) ShareShare
ReportReport
Posted on by Rene Orozco-Perez 60
Hello, The company that I work for uses Great Plains Version 9 as the corporate accounting package. I have been assigned the task of creating our corporate Invoice. As everyone is probably aware of (or at least this is the case for me) GP’s SQL Server Database is probably not the easiest database to ‘read’ and the data model not necessarily the easiest to understand either.  I am planning to use Crystal Reports (this is the means that we have chosen corporate wide) as the tool to generate reports. I am familiar with Stored Procedures and Views but in this specific case I would like to use Stored Procedures.  We were told that version 10 of GP has some views that are portable to version 9 so I created a new Virtual Server and installed a fresh instance of SQL Server. After that I installed version 10 of GP followed by the creation of a sample company (TWO) using the “Utilities”. After all of these steps were completed I looked into both DYNAMICS & TWO databases and to my surprise seems that neither have any VIEWs that will encapsulate the notion of Invoice (,InvoiceLineItems, InvoiceHeader, etc neither) records. I have been doing a bit of reading and perhaps there are few things that can be attempted after installing EConnect but this is quite frankly for me unknown territory. Still if this is the way to go I will be more than happy to try to gain a better understanding of the architecture. I would like to be able to get to this data (Invoice, InvoiceLineItems, InvoiceHeader, etc) without having to go directly against the tables if I can avoid it. Any suggestions will be greatly appreciated. Kind regards,

Rene Orozco-Perez

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Rene,

    I'm not saying somebody out there won't have a better way, but I wouldn't know how to do it other than against the tables.  If you find yourself without any better options get a book from Accolade Publishing.  It shows you how data flows through posting, history tables, etc.

    John Chastain

    MMLJ Consulting, LLC

    636-734-7935

  • Rene Orozco-Perez Profile Picture
    Rene Orozco-Perez 60 on at
    Hello John: First of all thank you for your help. I still have to do further research but apparently these two VIEWs will do what I am after ???? Dbo.SalesTransactions – this is the invoice header information (customer name, address, invoice #, invoice date…)Dbo.SalesLineItems – this is the line level (item, quantity, price..) Regards, Rene.

     

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Rene,

    I just looked at these views and there is SO much stuff in there that most people don't need to create invoices that I cannot imagine it being better than either going directly against the tables or writing your own views or stored procedures.  However, they might be really useful for seeing what tables are being pulled in.

    In addition you're most likely going to have an issue with the field names having spaces when you try to use them in Crystal.

  • Rene Orozco-Perez Profile Picture
    Rene Orozco-Perez 60 on at

    Victoria:

    I agree there is lots of stuff in these views that might not be necessary. I don't think however that the empty spaces will be a big deal in Crystal.

    Regards,

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Rene,

    If you end up using these views as they are in Crystal and don't have any issues, please let me know.  We have had so many issues with Crystal and spaces over the years that I don't write anything with any spaces at all anymore if I know it will be used in Crystal. 

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans