GP Reports
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.
GP Reports Viewer News
- June 2010 Annoucement: GP Reports Viewer now available for Dynamics GP 2010.
- April 2010 Annoucement: GP Reports Viewer now offers SSRS support. You can have Crystal and SSRS reports co-exist happily and launch them all from within Dynamocs GP.
- 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 or SSRS Reports and would like to launch them from Dynamics 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
- SQL Coding: How to find all SQL tables with a column name
- SQL Coding: Examples of how to calculate different dates from Database Journal
- Fixed Assets: Depreciation
- Fixed Assets: Account Numbers (New August 10, 2010)
- General Ledger: All Posted GL Transactions
- Inventory: Page with Dynamics GP Inventory SQL views
- Payables: Page with Dynamics GP Payables SQL views
- POP: Items with SOP-POP Link
- POP: Payables invoices originating from POP (New September 1, 2010)
- Receivables: Page with Dynamics GP Receivables SQL views
- SOP: All Line Items
- SOP: Items with SOP-POP Link
- System/Setup: Security Details in GP 10
- System/Setup: Security Roles and Tasks in GP 10
- System/Setup: Security and SmartList Details in GP 10 and 2010
- Useful SQL Scripts series on the Developing for Dynamics GP blog
- How to use a SQL view in SmartList Builder
- SQL Script listing created by Mark Polino
Hi we are new to gp (1 month) . When i run a smart list and pick qty on hand it is always 0 for every item even though we have stock. Is this normal??
thanks
Vic
Vic,
Just to rule out this being a problem with SmartList, if you go to Cards > Inventory > Item and bring up an item that should be in stock, what does it show for the Quantity on Hand at the bottom?
If everything is set up and entered properly and you have stock, Qty on Hand should not be 0. There are many possible causes for why this would not be working properly. The most common three are: (1) beginning inventory was not populated properly, (2) history is not being tracked for inventory items, or (3) the items are not set up as ‘Sales Inventory’ items.
Since it sounds like you just implemented GP, I would talk to your GP partner (or whoever set up GP for you) and see if they can figure out what the problem is.
-Victoria
Hi all – SQL 2005 SSRS
Is there a way to line feed where necessary the body (‘Comments’ section) of the resulting email in a SSRS generated report subscription? I’ve tried the vb CRLF and CHR equivalents to no avail.
Thanks
Stu
Stu,
This is probably not the best place to ask your question. I would recommend an SSRS forum, like this one: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads/?lc=1033.
-Victoria
Thanks for the prompt – was hoping someone here would know.
Regards
Stu
Just FYi and as follow up HTML tags appears to work in this scenario
Stu
Hi Victoria,
I’ve encountered this error when trying to print purchase order transactions with UNAPPROVE approval status>>
” You cannot print an unapproved purchase transaction”
This is the first time I have encountered this error and I’ve checked other forums and threads for similar issues but unluckily for me found no answers.
I would greatly appreciate if you could extend any help.
Thanks a lot.
PJ,
If you have Purchase Order Approvals turned on, GP will not allow printing of an unapproved PO. This is by design. You must either approve the PO, turn off Purchase Order Approvals if you are not using them, or, if you need to print unapproved PO’s, print them from a different tool.
-Victoria
hi victoria,
In Performa invoice reports, the unit price is having 4 decimal places. I want to change to 2. I created a format field for 2 decimal places which worked fine.
Scenario where I enter currency other than functional the number of decimal places is 2 but the currency displays same as the functional.
Can you please let me know if in GP report writer its possible to print functional and originating currency with two decimal places.
Note: I have sales order in 4 different currencies.
Regards,
Nisha.
Hi Nisha,
I prefer to use Crystal Reports and our GP Reports Viewer add-in for reports with these kinds of requirements. In my experience anything except the simplest change in Report Writer is quite cumbersome to accomplish and maintain.
That said, if you need to do this in Report Writer, I would recommend posting your question in one of the Dynamics GP forums to see if anyone there can help you.
-Victoria
Hi Victoria,
A client on GP9 recently upgraded their SQL to 2008 from 2000. They now have to run the GP Utilities because they will create a new company. GP Utility however would not continue because of SQL version issue. Do you have any idea how will they now create a new company? Thanks Victoria.
herson
Hi Herson,
While GP 9.0 is not ‘officially’ supported on SQL Server 2008, I have not really seen any reported problems yet. Hard to say how many people are actually using GP 9.0 with SQL 2008, but I am sure there are some out there. Can you elaborate on what exactly the ‘SQL version issue’ they are experiencing is? Are they getting an error that says it’s a SQL version error? Have you talked to GP Support about this?
-Victoria
Hi Victoria,
Thank you very much for your reply. The window name is ‘Verfy SQL Server’ and it says – “Your current SQL Server is not a supported version. Req: Microsoft SQL Server 8.0 Act: Microsoft SQL Server 2008(SP1) – 10.0.2531.0 (Int You need to upgrade to SQL Server 8.0 before continuing.” No we have not elavated this issue to GP Support yet. We hope we could find a way to make SQL 2008 work with GP9 because reverting to SQL 2000 will never be our option. Thanks again Victoria.
herson
Herson,
I have not come across this before, so not sure what to tell you. Is upgrading to GP 10.0 out of the question? If so, I would say you should talk to GP Support at this point to see what they suggest.
-Victoria
Hi Victoria,
Thank you very much Victoria for your usual support. I guess we really should talk to GP Support about this.
herson
Hi Victoria,
Do we have any BI tools available for GP which the Business stake holders can use? or anything that you have come across? if yes then please let me know.
Any Articles on Management Reporter
Thanks,
Farooq
Farooq,
Any reporting package can be considered ‘BI’, so I am not quite sure what you’re looking for here. Is there a particular business need that your company has?
For Management Reporter, I have already seen a slew of articles on various blogs – are you having trouble finding them?
-Victoria
Pingback: SQL view with AR apply detail - Victoria Yudin
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
Hi Victoria,
Thanks for your reply. CreditMemo, Departments are just an example. The main thing is to add a subgroup for the document numbers in the HATB report. I will ask in the Dynamics GP News groups.
Thanks,
Prakash
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.
Pingback: Adding watermarks to Crystal Reports « Sochinda's Blog
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
Pingback: SQL view with all SOP line items - Victoria Yudin
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
Pingback: Adding watermarks to Crystal Reports - Victoria Yudin
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
Pingback: How to use a SQL view in SmartList Builder - Victoria Yudin
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
Pingback: SQL view with security resource details for Dynamics GP 10 - Victoria Yudin
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.
Pingback: SQL view to show items with SOP POP link - Victoria Yudin
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.