Over the last 10+ years we have done a huge amount of reporting on GP data. Most of the time we use Crystal Reports, SmartList Builder, Excel and SRS (probably in that order) to deliver the reports. However, no matter how the report is ultimately delivered, one of the critical steps is knowing where to find the data in the GP tables. I am going to publish some information that we get asked for frequently during reporting. I have a lot of data in various places, so bear with me while I keep adding to this. If there is something you’d like to see, or have additional information you’d like to contribute, please e-mail me.
Pages with Table Information
- Bank Rec Tables
- Company/System Tables - updated Jan 08, 2010
- Fixed Assets Tables
- GL Tables - General Ledger
- Inventory Tables - updated Jan 14, 2010
- PM Tables - Payables Management
- POP Tables - Purchase Order Processing
- RM Tables - Receivables Management - updated Jan 22, 2010
- SOP Tables - Sales Order Processing - updated Jan 08, 2010
GP Reports Viewer News
- May 2009 Announcement: GP Reports Viewer can now seamlessly replace GP’s SOP Reports with Crystal Reports – there is even a demo video of this functionality.
- To get updated news and offers on GP Reports products as well as reporting tips and tricks, sign up for the GP Reports email newsletter.
- If you have Crystal Reports and would like to launch them from GP, check out our GP Reports Viewer.
GP SQL Scripts
- SQL Coding - date differences in SQL
- SQL Coding - searching for strings in SQL Server using LIKE
- View - General Ledger: All Posted GL Transactions
- View - Inventory: Customer Pricing
- View - Inventory: Inventory with Dates
- View - Inventory: Quantites on Hand
- View - Payables: All Posted Transactions
- View - Payables: Apply Information (shows invoices and what checks or credits were applied to them)
- View - Payables: GL Distributions - updated Jan 22, 2010
- View - Payables: Multicompany Open Invoices
- View - Payables: Multicompany Open Invoices (for Crystal Reports)
- View - Payables: Payment Apply Detail (shows payments and what invoices they were applied to)
- View - Payables: Posted Payments
- View - Payables: What check paid a Purchase Order
- View - Receivables: All Posted Transactions
- View - Receivables: All Posted Transactions (abbreviated)
- View - Receivables: Unapplied Transactions
- View - Setup: Security Details in GP 10
- View - Setup: Security Roles and Tasks in GP 10
- View - SOP: All Line Items
- View - SOP: Items with SOP-POP Link
- Useful SQL Scripts series on the Developing for Dynamics GP blog
Working with Crystal Reports
- Adding watermarks to Crystal Reports
- Alternating shading for lines in Crystal Reports
- Crystal Reports book recommendation
- Crystal Reports service packs and versions
- Dynamically change decimal places in Crystal Reports
- How to get a definition of your Crystal report
- How to get next Monday’s date in Crystal Reports
- How to get rid of the blank page at the end of a Crystal report
- Running Totals in Crystal Reports
- Selective Highlighting in Crystal Reports
- Suppressing blank lines in Crystal Reports
More Online Resources for GP Reporting
- David Musgrave’s blog – David has a comprehensive post on Finding Table and Field Information in Microsoft Dynamics GP.
- Mark Polino’s blog – Mark has a section for GP downloads where you can find lists of all the GP tables in Excel format.
- Mariano Gomez’s blog – detailed post on how to update an existing SmartList in SmartList Builder after adding column(s) to the SQL view the SmartList is based on.
- Steve Gray’s blog – Steve has lists of tables for selected modules under the Dynamics GP Table Structures section.
a
This page has the following sub pages.
Hi Victoria,
I need to add sub groups in the HATB report in GP.
Eg:
The report needs to be like this,
or Customer A:
Invoice0001
CreditMemo0001
Department 1
Department 2
Here Invoice0001 is the Document Number.
CreditMemo0001 is the Applied Credit Memo for the Invoice.
Department 1, Department 2 are the corresponding linked departments for the CreditMemo0001.
Is it possible to do like this in the GP Report Writer? Or it is possible in the Crystal Reports?
Please Suggest.
Thanks,
Prakash
Hi Prakash,
How are the departments liked to the Credit Memo? That answer may determine whether this is possible or not.
In general, the HATB is a pretty difficult report to recreate in Crystal. Doable, but a lot of work. So if you can achieve what you want with Report Writer, that may be a lot easier. However, I do not work with Report Writer much, so I would recommend asking Report Writer questions on the Dynamics GP newsgroups.
-Victoria
No this is not a custom smartlist . Yes it occurs on all workstations and the version of GP is 10.00.1301. I logged in as SA and it is the same problem. It won’t display the account description.
Larry, sorry, I have not come across this particular issue before. If you are seeing this on all workstations then it sounds like that SmartList got ‘corrupted’, although I cannot imagine how. Are there any customizations in the mix? Has anyone been changing things directly in tables? I would recommend talking to GP Support about how to reinstall all SmartLists or just that particular SmartList.
-Victoria
When i go to the smartlist under financial-account summary
the account description doesn’t display any data. Is there a way i can reimport this smartlist?
Larry,
I am not aware of a way to ‘reimport’ just one of the default SmartLists. If this was a custom, SmartList Builder SmartList, sure. Does this problem occur on all workstations? When you’re logged in as ’sa’? What is your GP version and service pack?
-Victoria
yes logged in as SA is does the same thing. I won’t display the account discription and it is not a custom smartlist. It is one that is out of the box. Its under financial and its called account summary. The version of GP is 10.00.1301
I will contact them. Thanks
Hi!
The company I work for uses GP 10 with the HR Module.
We recently got a new HR Manager and she is convinced we have got a problem with Employee records not being up to date. She wants to be able to check for the number of active employees during any given time period.
I am the network admin which where I work at means I’m the ‘computer guy’ but as will become painfully obvious, I don’t know squat about GP tables, especially any HR tables. She wants me to check historical time periods for her without using the GP user interface so she can compare the results using the GP interface.
I’m moderately familiar with SQL and think that is the way to go. I actually came across a script that shows employees but that info comes from upr00100 which is payroll? That script reads:
select
e.employid as ‘EMPLOYEE ID’,
rtrim(e.frstname) + ‘ ‘ + e.lastname as ‘EMPLOYEE NAME’,
p.jobtitle as ‘JOB TITLE CODE’,
p.dscriptn as ‘JOB TITLE DESCRIPTION’,
d.deprtmnt as ‘DEPT CODE’,
d.dscriptn as ‘DEPARTMENT TITLE’
from UPR00100 e
inner join UPR40301 p on p.jobtitle=e.jobtitle
inner join UPR40300 d on d.deprtmnt=e.deprtmnt
order by d.dscriptn, p.dscriptn
Looks good on query results but I can’t figure out how to limit by date. I found a strtdate field and an inactive field in there and have fooled around with that, but my results don’t look right to me as far as how many employees it shows we have active right now so I’m thinking I’m heading in the wrong direction here.
I’m thinking I need to pull from HR tables instead of Payroll tables to get accurate info for her. Is that correct?
If so, what are the HR tables that I need to select from?
Thanks In Advance for any hints, tips or directions!
Hi there,
Where to find the data you need will depend on how it is being entered and where (what screen, what module, etc.). Payroll and HR are not my specialties, so rather than give you partial advice, I would recommend one or both of the following:
-Victoria
thanks!
will do.
[...] & Events page. You can also see a list of all the Crystal Reports tips published so far on my GP Reports page under Working with Crystal [...]
Thanks for your time Victoria. would do just that.
that’s exactly what i want to do.
so you are saying asides doing this report using cross-tab there’s no other way i can resolve this problem?
This is because using cross-tab doesnt present the report the actual way i need it.
Funmi,
Besides using a cross-tab I don’t know how to suppress a column in the middle and recapture the space. I would recommend posting this on some Crystal specific forums to see if you can get people with more Crystal expertise to answer you. Two that I would recommend are: Tek-Tips and Experts-Exchange.
-Victoria
Hi victoria,
Please i need your help with a particular problem in crystal report.
I designed a new report using “Blank report” and everything is working fine. the only pain i have is suppressing the empty columns in the report.
That is, columns that have nothing in them as at the time of printing.
I also have lines in this report. Is there a way to fix this problem.
Please i need your help asap.
Thanks
Funmi,
Are you saying you want to ‘recapture’ the space caused by empty columns that are in the middle? If so, the only way I know of doing that would be to use a cross-tab, which can automatically suppress any blank columns. If that’s not what you are asking, can you please give more detail?
-Victoria
SQL View question. Being new to GP and SQL I can see the power of SQL views but do not know how to create one. Although there are several excellent examples on this site how do I go about creating a SQL view in SQL 2000 and SQL 2005.
THanks for any help,
Hi Mitch,
Do you mean how to take one of the views I have posted and create it on your server? If so, the first step of my How to use a SQL view in SmartList Builder post should help. Or do you mean creating your own view from scratch? For that, I would just follow the examples in my views and substitute your own names, tables, columns. (I typically start with a select statement, then when I have what I want add the view creation code at the top and the permissions code at the bottom.
-Victoria
Hello Victoria
Have you set up any web-based IS Report? If yes can I have a look of the same?
Regards
Sanjay.
Hi Sanjay,
I am not sure I understand. By IS, do you mean Income Statement? If so, I would use FRx for that. If not, can you please explain in more detail?
-Victoria
Hi Victoria,
Yes, I am talking about Income Statement only. I have also set up lot more IS Reports with Frx. But now we are planning to develop this report as Web Based Report with using ASP.net. It is bit difficult for us to show or combined two or More accounts in Single row Item. Its really require high level of calculations and other formulas as its a comparative Statement too, wherein we have to present CBR for %. It will better if I could any sample script which added two accounts in one row and while drilling that row it will present data of both Accounts.
Regards,
Sanjay
Sanjay,
Sorry, I do not have any samples that would do this outside of FRx.
-Victoria
Hi..Victoria,
I want to create auto Apply Payable Document from my customize window, but I was try some code but it’s not work. Have you experience in Auto Apply Payable Document?
Thanx
Deny,
I am sorry, I am not able to provide any help with that. I would recommend posting your questions to the Microsoft Dynamics GP Developer Newsgroup.
-Victoria
Anyone have any suggestions to help me achieve the following:
I am looking to create a Purchase Order template in Crystal Reports, as opposed to using the one in GPS, that will provide more information.
Our CFO likes to be able to see the GL coding breakdown of the PO, so I would like to be able to provide that information on my template and furthermore provide YTD budget and actual info for the GL accounts so that one report will give our CFO “one stop shopping” of information so she can make decisions about whether or not to approve the PO if the purchase will be under or over budget.
My question is which table to link to get at all of this information. I have already created the PO itself and have pulled the relevant field data from the POP tables. It’s just the GL tables (and maybe even Inventory tables) that have me baffled.
Currently we are using Excel PO templates and it’s a horror show as I am sure you can imagine.
Thanks in advance.
Regards,
Gordon
Gordon,
Take a look at the following pages to find the tables you want:
GL Tables – this will have the tables/views for GL actuals and budgets
POP Tables
-Victoria
Hi Victoria,
I have a question and was wondering if you had an answer off the top of your head.
Thanks to you, I am getting more proficient with SQL Views and it has created many possibilities.
I am creating a Crystal Report that pulls data from the CM tables and the detail pulls without any problems and I can see my cash in vs. my cash out.
The problem I am having is getting a beginning balance so that I can roll it forward.
Any ideas on how to make that happen? I didn’t see a field for Beginning Balance anywhere.
Thanks and regards,
Gordon
Gordon,
The beginning balance is not stored anywhere, you would have to calculate it either by adding up all the transactions through your beginning date or possibly using the information from the CM20500 table. I have not done this before, so I don’t have a query to share for this.
-Victoria
Thanks again for your prompt reply Victoria. I will take a look at CM20500 and see if that will help.
Regards,
Gordon
Victoria, good news! CM20500 does contain the data for beginning balance.
Now I am one step closer to my goal.
However, I have linked tables CM20500 and CM20200, both of which contain the data I need, but when I did, the results are duplicated in my report. I don’t recall why that happens or even how to fix that. Is there a command for show unique records only?
Thanks again!
Gordon
Victoria, I got it! It was an issue with the linking.
Thanks again for all of your help.
Gordon
Gordon,
Excellent, thanks for following up!
-Victoria
Thanks Victoria! I know I will be visiting this site often as we move forward with our implementation.
Hi Victoria-
I love your blog! Very useful for a newbie like me. My company is just beginning implementation on GP 10.0 and I’m starting to use Smartlist Builder. I think this must be a dumb question, but I can’t seem to find where you can use a restriction that matches two fields in the same table so that the Smartlist only returns records where those two fields match?
I see in Smarlist that you can use Search to do a field comparison, but I want the Smartlist to perform that comparison by itself. Suggestions?
Katrina,
Not a dumb question at all. You can add restrictions in SmartList Builder to accomplish something like this. Although if it starts getting complicated, you may be better off doing this kind of logic directly in SQL. Create a SQL view with the logic you need, then use SmartList Builder to create a SmartList based on your view.
-Victoria
Victoria,
Ok. I see.
Thank you very much.
-Jim
Thank you very much…..
No luck on SQL script for Cash forcast right?
-Jim
Jim,
I don’t have any scripts readily available for cash forecasts, sorry.
-Victoria
Hi Victoria or Nev,
Can you please help me with SQL Script to create Cash Forcast or to view the Budget in GP?
Thank you,
-Jim
Jim,
To get the budget data from GP you can use GL01201, a view that comes preinstalled with GP.
-Victoria
[...] with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports [...]
Victoria,
Once again thank you for the reply, I have managed to dig out some SQL that I can build on as a foundation from when a colleauge worked on something similar for Chelsea FC footbal club.
Thanks again though
Nev Browitt
Hi again,
You couldnt point me in the direction of some sql that I could amend to create a Cashflow statement via SSRS?
Many Thanks
Nev
Nev,
Since Cash Flow reports are typically created in FRx, I don’t have anything readily available for this in SQL. My company, Flexible Solutions, does offier custom report creation, please let me know if you’re interested in something like that.
-Victoria
[...] & Events page. You can also see a list of all the Crystal Reports tips published so far on my GP Reports page under Working with Crystal Reports. Posted in Crystal Reports, GP Reports Tagged: Crystal Reports, [...]
Hi Victoria,
I’m wondering if you have ever come across item allocated quantities consistently going negative, even though the item allocation inquiry window shows positive allocations.
For example, one of our sales inventory items is currently allocated on two Sales Orders for one each. But when I look in SmartList and Item Quantities Maintenance, it says negative 27, or (27)…instead of 2.
I have to consistently run reconciles to change the allocated qty back to where it should be. And it only happens in one of my 20 sites I have created in GP 9.0
I’m kind of at a loss for why this continues to happen every month.
Any insight would be awesome!
Thanks,
Mark
Mark,
Sorry, I have not come across this being a consistent issue. Once in a while, sure, but not constantly. Are there any 3rd party products or customizations involved? Are there a lot of system crashed on connectivity losses? (You can sometimes judge this be seeing how many stranded SQL sessions are in the DEX_SESSION table in tempdb.) Is there anything different about the one site this happens in?
-Victoria
Victoria,
I see two session IDs currently, 302(110) and 305(112).
The only thing I can think of is that the sales orders placed for this site need to have their Default Site ID changed because the site in question isn’t currently set as our default for my sales users.
My first thought was that maybe they were consistently abandoning sales documents after changing the header info, but unless I have a gremlin among them, the allocated quantities are just too high for a user to sit and meddle with.
Our GP support also came back with limited info on the subject. I’m waiting for the jump to 10.0 to see if the problem carries over.
Mark
Victoria,
Sorry I forgot to respond to your initial questions…
We have customizations, but they aren’t site specific. Small fixes like preventing users to changes sales batches based on terms, etc…
I never get system crashes or connectivity issues, unless someone is connected via wireless, but even then it’s minimal.
Thanks again,
Mark
Mark,
I can’t see how just abandoning a sales header record would have an impact on anything to do with item quantities. I would suspect crashing/losing connection while entering or transferring transactions instead. Also, if I recall correctly, I have seen a 3rd party product that doesn’t support the Copy functionality because it screws up the item allocations…that’s why I was asking about that. If you do find more detail on why this is happening, please post back, this would be good information to know.
-Victoria
Victoria,
I know we use SQL reporting quite extensively, and we have recently implemented a custom packing application that ties GP to our shipping program, but I don’t see how either of those would affect this site as it’s a separate physical facility that we ship out of…
Also, do you know of a master list of all GP tables and their contents? I’m looking for the inventory table that holds the GL Accounts by item. Item Account Maintenance.
Thanks again!
Mark
Mark,
If this is happening often enough, you should be able to trap it with a DEXSQL log or a SQL trace…but something sounds like it is going wrong, so I would try to track it down.
For the master list of GP tables, I typically go to Tools > Resource Descriptions > Tables in GP. I also recommend you check out the list under ‘More Online Resources for GP Reporting’ on this page.
The item accounts on the Item Account Maintenance window are stored in the IV00101 (Item Master) table. They are stored as account indexes, so look for the columns ending in IDX, INDX, etc. For example, the ‘Inventory’ account index is IVIVINDX (for a complete list I would look in Resource Descriptions). Once you have this, you can link on that index to the ACTINDX in the GL00105 table to get the account number and/or the GL00100 table to get the account name. Hope that helps.
-Victoria
[...] To start we need a SQL view. I have published a number of views for getting GP data, so I will use one of my favorites – Payables Apply Information. You can see the list of other SQL views I have published on my GP Reports page. [...]
Victoria,
We have many companies in our system and would like to create an application to control the opening and closing of our periods. I know that the SY40100 (SY_Period_SETP)table controls the opening and closing of periods. However, I am concerned that there are some updates that occur when the Fiscal Periods Setup Checkbox is Checked and UnChecked.
Any help or advice would be appreciated.
Thanks,
Arthur
Arthur,
We’ve not done a customization like this, but if you are talking about the checkboxes for the Periods/Series, I don’t believe there is any other process that is run when the checkboxes are checked or unchecked. Typically other processes check the status of this table, not the other way around. There are a couple of things I would suggest if you want to confirm this:
- Run some traces (DEXSQL.log, SQL Profiler) and capture what happens when the checkboxes are checked, unchecked.
- Confirm with GP Support that no other updates occur when checking/unchecking those boxes.
I would also recommend posting a question on the GP Newsgroups – you may find that someone has already done a customization like this and can let you know about their experience with it.
Please let me know how this project goes – I would be very curious to see what your findings are.
-Victoria
[...] other SQL code, please check out the GP SQL Scripts section my GP Reports page. Posted in Crystal Reports, Dynamics GP, GP 10.0, GP Reports, GP SQL scripts, SmartList Builder [...]
Thanks for everything, great information keep it going.
Victoria,
This is very good information and thank you very much for posting it.
John,
The company I am working for is using Nodus for their online store and it is pretty kool and easy. I don’t really deal with it everyday so I won’t be able to give you info on it much, but I know that its not complicated.
[...] any spaces in the column names. I maintain a list of all the SQL code I’ve published on my GP Reports page so if you’re looking for other scripts, take a look [...]
Thank you for your quick, helpful response. I will look into each reference.
Hello Miss Victoria,
Again, thank you for your help. I am very new to GP and I am a C# programmer looking to integrate my client’s estore and GP system (customers, products, orders). How can I pull data from GP using ADO.NET? a Web service? or console application written in C#? I don’t have any tools aside from my knowledge in programming and SQL. Any help or direction is greatly appreciated!
John,
There are a number of companies that have created products for this. Two that I know off the top of my head are Azox and Nodus. If you’re looking for additional GP development resources, you may want to check out my fellow bloggers, particularly Mariano Gomez, David Musgrave and Vaidy Mohan.
-Victoria
Hello Miss Victoria,
Thank you for this very helpful information. I appreciate your efforts very much! Thank you.
Lisa,
Thank you very much! I do not have as much information on PR, HR and Manufacturing as I do the other modules, but I will add them to the wish list and hopefully be able publish those soon. If there is any other information you’d like to see, let me know.
-Victoria
Victoria, I am familar with your posting on the newgroups and am a regular reader of the blogger in the Blogroll group.
THIS information is VERY informative and I really appreciate you posting it. Would I be pushing it if I asked if you were planning on continuing the posting of other modules, such as PR, HR and Manufacturing??
Thanks again for taking the time to posting all this information.