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
- January 2013: GP Reports Viewer is available for Dynamics GP 2013 Desktop Client.
- November 2012: Default Report Destinations added to GP Reports Viewer.
- July 2012: Global Parameters added to GP Reports Viewer.
- April 2012: New emailing functionality for GP Reports Viewer and Dynamics GP 2010.
- November 2011: Now you can e-mail SOP and POP reports using GP Reports Viewer and Dynamics GP 2010!
- January 2011: GP Reports Viewer can now seamlessly replace GP’s Purchase Order reports with Crystal or SRS reports.
- June 2010: GP Reports Viewer now available for Dynamics GP 2010.
- April 2010: GP Reports Viewer now offers SSRS support. You can have Crystal and SSRS reports co-exist happily and launch them all from within Dynamics GP. Take a look at this demo video to see more.
- May 2009: GP Reports Viewer can now seamlessly replace GP’s SOP Reports with Crystal or SRS 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.
Dynamics GP SQL Scripts
- Bank Rec: Bank Deposits and Receipts
- Bank Rec: Checkbook Register
- Fixed Assets: Account Numbers
- Fixed Assets: Depreciation
- Fixed Assets: Depreciation – Yearly
- General Ledger SQL views
- Inventory SQL views
- Payables SQL views
- Purchase Order Processing (POP): Items with SOP-POP Link
- Purchase Order Processing (POP): Payables invoices originating from POP
- Receivables SQL views
- Sales Order Processing (SOP) SQL views
- System/Setup SQL Code
Other SQL Scripts and Links
- How to use a SQL view in SmartList Builder
- SQL Script listing created by Mark Polino
- SQL Server Coding Tips
- Useful SQL Scripts series on the Developing for Dynamics GP blog
Victoria,
I am trying to grab all of the friend of the court (FOC) transactions from the week prior. I am quite sure I am not correctly understanding the date function in SQL. Can you tell me what I am doing wrong?
SELECT UPR30300.EMPLOYID, UPR30100.EMPLNAME, UPR30300.CHEKDATE, UPR30300.PAYROLCD, UPR30300.TRXBEGDT, UPR30300.TRXENDDT,
UPR30300.UPRTRXAM, UPR30100.SOCSCNUM
FROM UPR30300 INNER JOIN
UPR30100 ON UPR30300.CHEKNMBR = UPR30100.CHEKNMBR
WHERE (UPR30300.TRXBEGDT >= DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)) AND (UPR30300.TRXENDDT = DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)) AND (UPR30300.TRXENDDT <= DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6)) AND
(UPR30300.PAYROLCD = 'FOC' OR
UPR30300.PAYROLCD = 'FOC2' OR
UPR30300.PAYROLCD = 'FOC3' OR
UPR30300.PAYROLCD = 'FOC4' OR
UPR30300.PAYROLCD = 'FOC5')
Alex,
If you want to calculate 7 days ago, you can use the following:
However, I am not sure that is what you mean by ‘from the week prior’?
To test what you’re coding, you can always put it into a select statement. For example, the statement above would be:
-Victoria
Hi Victoria,
how to deploy a modified report to GP? So, we have a Terminal Server that handles GP for users, and i install GP on my local computer. I modified a report based on user request. When i update the Reports.dic from my local computer to the Terminal Server, users dont see the updated reports. some step i missed?
Thanks!
Addin,
Sounds like you may not have granted security to the modified report to your users.
-Victoria
Hi Victoria,
Seems like you are the expert I’m looking for! Our company switched from Peachtree to GP. I am looking for a way to run one of our favorite reports from Peachtree in GP. I’m in sales, and NOT AT ALL technical. I see that we have Smartlist in the options in GP.
The report I am looking to run is to show customer sales by month. In Peachtree, it had customer names going down the left, and months going across the top to the right. Each column has just the total that the customer has spent in each month. I’m not interested in who the salesperson was, or what the customer ordered, just a sales total history by month… Please help make my sales job a little easier! THANKSS
Hi DeeDee,
Out-of-the-box GP does not offer a report like this. That said, this can certainly be done. How it is done and where you can access it would depend on a number of things, including what modules you are using to enter sales transactions and what reporting tools you own/have set up. If this is something your company is interested in, you could talk to you GP Partner about this, or if you prefer, this is something I can help with as a consulting project. Please let me know if you would like to talk about this option in more detail.
-Victoria
Hi Victoria..Thank you for sharing your knowledge with all others.
I have a question regarding customer table in GP 10, I would like to find out who created the record in rm00101 table. I cannot find any column related to that in that table. Is there anyway I can get the person who logged into GP ?
Suresh,
Since GP out-of-the-box does not track this, you would need to set up something upfront to be able to track this information for future customer adds.
-Victoria
Hi Victoria, great site! My Sales users want an invoice report that is pretty much identical to the SOP Short Invoice Form, with a few minor changes. They want the Short form to stay though. The SOP Other Invoice Form is unused, so i wanted to change that one, but i really don’t want to have to completely rebuild the short report. is there any way to copy a report form as the alternate version of another report?
Hi Mike,
Thanks for your kind words. This post by Leslie Vail may help answer your question: http://dynamicsgpland.blogspot.com/2011/09/little-tricks-with-report-writer.html.
-Victoria
Hi Victoria,
how can I tell who is the original writer of a Sales Order? The field “user to enter” changed after other people access the order.
Thanks a lot,
Steve
Steve,
I just tested this and do not see the USER2ENT changing when I add or change line items on existing SOP transactions. What version of GP are you using where you see this?
If you have confirmed that USER2ENT will not work for what you need, then you would need to either create a customer SQL trigger and table to track this or use a product like Audit Trails.
-Victoria
Hi Victoria.
Is there a way to use the Excel Refreshable reports and just return account trans or balances for or through specific periods? Would be useful in building financials in Excel vs FRx for instance.
Thanks,
Rose
Rose,
You can do this with Excel, however, if you need to enter parameters (like a data range), then your report will most likely need to be based on a stored procedure in SQL. Also, since GP does not store ending balances for GL accounts, you will need to do some coding to accomplish that.
-Victoria
Hi Victoria,
Below is my simple CRM view that won’t return any data in SLB, even when logged in as sa.
I’m only accessing 1 CRM table-very simple. I had already tried what you said but didn’t work. Now I didn’t use SetANSI nulls and SET quoted identifier statements but that shouldn’t matter? I can’t get any data in my views logging into SSMS as ‘sa’, as I said b4 it appears with MS-CRM you have to use windows authentication. Do you have any other ideas to get this to work? I would really appreciate it.
Dave from Cranford, NJ
CREATE VIEW view_PGM_CRM_UNAPPROVEDORDERS
AS
select
SOV.ordernumber ‘Order Number’,
SOV.name ‘Campaign Name’,
Status = CASE SOV.new_approve
WHEN 1 THEN ‘Request Approval’
WHEN 2 THEN ‘Approved’
ELSE ‘Entered Only’
END ,
sov.new_primarybrand ‘Primary Brand’,
sov.accountidname ‘Advertiser’,
sov.new_flightstartdate ‘Flight Start Date’,
sov.new_flightenddate ‘Flight End Date’,
sov.new_orderdocumentidname ‘Order Document ID’,
sov.new_billingoptionsname ‘Billing Options’,
SOV.new_ordernettotal ‘OrderNetTotal’,
SOV.new_ordertotal ‘OrderTotal’,
CreatedIn_DYNAD = CASE SOV.New_CreatedInDynAd
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END
from PGMEDIA_MSCRM.dbo.FilteredSalesOrder SOV
where SOV.New_Approve=1 or SOV.New_Approve Is Null
GO
GRANT SELECT ON view_PGM_CRM_UNAPPROVEDORDERS TO DYNGRP
Dave,
If this is something specific to CRM, then I am not sure I can help, however, did you take a look at the other links I provided for you? If those do not help either, you may need to talk to a SQL consultant that can take a look at your setup to see what’s going on in there. The fact that you cannot use ‘sa’ to access the data directly in SSMS does not sound right to me.
It also may end up being that the only way to enable seeing this data in a SmartList would be to replicate the data into a table/view inside one of the GP databases. Another option to consider is creating an Excel report against the CRM data instead of doing this in SmartList Builder.
-Victoria
Hi Victoria
Question about SmartList Builder. I created a sql view in another one of my databases (not GP). How do I grant access to DYNGROUP because it’s a completely different sql database that has nothing to do with GP? I want to give my users access in SLB to this view. pretty good with views in SLB, done many b4. I think this is far more complex b/c I’m trying to connect to a FilteredView in my MS-CRM database. Not sure if you know MSCRM or tried to have SLB access a view from there. I think it’s a permissions issue and could be very complicated to figure out.
Dave from Cranford, NJ
Hi Dave,
I don’t think you’re going to be able to point SLB, within GP, to a view in a non-GP database. If your CRM database is inside the same SQL Server instance as the GP databases, you should be able to create a new view inside your GP company database (or DYNAMICS if you have multiple companies that need to access this) to point to the CRM view. Then SLB can just point to the new view inside one of the GP databases.
If your CRM database is in a different SQL Server instance, then you may have a bit more trouble getting this to work. I have not tried these methods myself, but here are some links to postings by others that may provide a resolution:
Please let me know how this works out for you.
-Victoria
Thanks Victoria,
I did try what said. And my CRM db is in the same instance as my GP db. I login as ‘sa’ in Smartlist and having same problem where it returns no data. I have a simple select view to 1 table in CRM-FilterSalesOrder. I created the view in Mngt Studio logged in as windows authentication. The problem here is windows authentication and MS-CRM views being accessed in SmartListBuilder. So if I login to Mngt Studio and rt click my view and select Top 1000 rows I do get data (only when logged in as Windows auth in Mngt Studio). However, when I connect to this same view in SmartListBuilder I get no data. It just doesn’t work. Any ideas?
Dave from Cranford, NJ
Dave,
I don’t have any easy way to test this, as I do not have the same scenario set up, but I would try the following:
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
create view YourViewName
as
select * from CRMdbName..CRMViewName
GO
grant select on YourViewName to DYNGRP
select * from YourViewNameIf that returns data, now try to create a new SmartList using SLB and see if it returns any data.
-Victoria
Victoria,
We have been using GP for about 4 years, but I have avoided digging into the databases because it is so cryptic and confusing. Now we are in the cycle of “Build a Smartlist, export it to Excel, load the excel tables into another database, process, put the resulting data in a report!” Argh!!!!
Faced with trying to eliminate all of those middle steps, I started looking for help to decrypt those GP databases and found this site. YOU ARE MY ANGEL! Thank you for not only taking the time to figure this stuff out, but with sharing your brilliance with the rest of us poor souls!
Don,
Thank you so much for the kind words! I started keeping all these lists and bits of code to help with my own reporting projects, and it has grown into the blog that you see now. It’s really great to know that this information is helping others out there.
-Victoria
Hi Victoria, I need your help to create a report in SmartList.
I require it shows me the following information: Customer Number / Number of Document (Invoice) / Date docm. / Product Number / Lot / Serial Item / Description of Item / amounts billed / Location of the transaction / document status
Thanks for your help.
Roberto,
I do not believe you will be able to do this without SmartList Builder. Do you have SmartList Builder? If so, you can start with my view for all SOP line items and then link to the SOP10201 table for the lot and serial numbers.
-Victoria
Victoria,
New to your site, I am in my 1st year with GP 2010 and am just getting really into the good stuff with Smartlist and Excel report builders. I have a need to create a smartlist that will show Bank Deposit and deposit date (the date it hits the checkbook) along with receipt and receipt date (the GL posted date). I see that in the Bank Deposit Entry Zoom window shows both receipt date and deposit date in the checkbook. Just not sure what tables to hit. Can you help?
I have seen a lot of questions relating to GL & Checkbook out of balance and I have found in my case, it has been items posted to GL but not in the Checkbook or items deposited in checkbook and not in GL. Always a timeing difference based on dates posted GL & Ck book.
It sees a simple Smartlist with both Deposit date and Receipt date would show me what I need. Thanks for your help.
Patrick
Hi Patrick,
I just posted a new view to show this information: http://victoriayudin.com/2011/10/06/sql-view-for-bank-deposits-and-receipts-in-dynamics-gp/.
-Victoria
Hi Victoria
Is there a way to tell what tables make up the data available in smratlists specifically the purchasing item data. We do not have Builder and my cfo has a smartlist he wants turned into a view.
Vic,
There is an Integration Guide in the documentation that lists out the tables used by out-of-the-box SmartLists. I usually can’t remember where it is, so I have to search for it…if there is a specific SmartList you want to know about, let me know which one.
-Victoria
Thanks!
It is for items. Purchasing line items. This smartlist he made has things like order poit qty and last rcpt cost.
thanks
Vic
Vic,
Here is the listing for the Purchasing Line Items SmartList:
Tables
POP10110 – POP_POLine
POP10100 – POP_PO
IV00101 – IV_Item_MSTR
PM00200 – PM_Vendor_MSTR
PM00201 – PM_Vendor_MSTR_SUM
RM00101 – RM_Customer_MSTR
RM00103 – RM_Customer_MSTR_SUM
POP30110 – POP_POLineHist
POP30100 – POP_POHist
IV40201 – IV_UofM_SETP_HDR
Joins
POP10110 left-outer join to POP10100
POP10110 left-outer join to IV00101
POP10110 left-outer join to PM00200
POP10110 left-outer join to PM00201
POP10100 left-outer join to RM00101
POP10100 left-outer join to RM00103
POP30110 left-outer join to POP30100
POP30110 left-outer join to IV00101
POP30110 left-outer join to PM00200
POP30110 left-outer join to PM00201
POP30100 left-outer join to RM00101
POP30100 left-outer join to RM00103
IV00101 left-outer join to IV40201
-Victoria
wow…thanks again. you are terriffic!!!
This will give me a startintg point.; I have to try and find fields like the order point qty and min order qtu and last originating cost etc etc
thanks
Vic
Hi,
I have been trying to link SOP and POP on lot level for auditing reason. For example, you start from SOP line item where you have lot details, where you have three or four lots each with a difference price some witht the same lot number (this is when you have fractions and GP split the lot into two lines with the same lot number but different cost), and then you want to trace that back to which Receipt number to match the cost in the SOP with the cost in POP. Tried the following tables but could not link to Lot Number:
IV30300 & IV30301.
Please let me know if you have an idea on how to do it.
Thanks & Regards,
Samer
Samer,
If you are looking to link on lot number, you may also need to look at IV30400.
-Victoria
Hi Victoria,
Do you happen to have a listing of all of GP standard reports for all modules?
thanks in advance,
Sorry Jeff, I do not have anything like this. There used to be manuals with report listings, but I think that stopped a few versions ago.
-Victoria
Besides, I like to create my own reports.
-Victoria
ok. Thanks victoria
How about a SQL View for sales of items by year? Any chance you have one for that?
Hi Mark,
I just posted a new view for this: http://victoriayudin.com/2011/08/11/sql-view-for-sales-by-item-by-year/.
-Victoria
Victoria – love your site, I use it all the time.
I have been asked to write a report for cancelled orders in Great Plains. I was thinking of using the QTYCANCE column in SOP30300, but in our system I see a lot of instances where this number is greater than zero but less than the QUANTITY for a line item; I was also thinking of using the VOIDSTTS column on SOP30200, but am concerned that some of those may not be truly “cancelled” orders but rather something that was just entered incorrectly and then voided.
Any ideas on how I could do this?
Thanks!
Hi Craig,
Thanks for the kind words. What you are seeing is what I would expect. QUANTITY is what was originally ordered. QTYCANCE is what was canceled out of what was ordered. So QTYCANCE should always be less than or equal to QUANTITY. Partially canceled quantities are not an issue, unless you are not expecting to see that in your data?
If I were asked to write a report for canceled orders, I would first get the definition of what a canceled order is. How do your users cancel orders in GP? Have someone sit with you and show you all the different ways they do this and also show you some instances of canceled orders in the system so you can see how they appear in the tables. Hope that helps.
-Victoria
SOP30300.QTYCANCE is 0 for an order that I KNOW is cancelled. Where else can I find out about cancelled orders?
Robbie,
It’s possible the whole order was voided. In that case, you would see SOP30200.VOIDSTTS = 1.
-Victoria
hi victoria,
i’m working with job order of purchasing in GP 10. my problem is the user need to enter comments for the item, once he print it not all the text he wrote it will shows and it doesn’t fill the whole space in the print out. i tried to maximise the textbox size for that comment but still nothing change. can u help me please?
Hi Noorsean,
This is where I typically prefer to use Crystal Reports, as working with the comments fields in a breeze in there. This blog post by David Musgrave might help you with some ideas on additional things you can do in Report Writer with the comments: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/02/02/adding-more-comments-to-sop-documents.aspx.
-Victoria
I am working with a customer who is converting to Dynamics GP 2010 from another accounting system. We are learning as we go. Current problem: we need to apply deposits to several Sales Orders for one customer. (The amount being applied to each sales order will be 1/2 the amount of the Sales Order.) ie a customer gives us $5,000 up front. A week or more later, we will generate a Quote then Sales Order. The customer may have several Open Sales Orders. We need to take part of the $5,000 deposit and apply it to the sales orders. We will not convert the sales orders into invoices until the customer has received the items. At that time we will take the rest of the deposit and pay off the invoices. How do we take the deposit once it is posted and apply it to several Sales Orders. Thanks in advance for any help you can give us.
Pamela,
This is not a scenario that GP can handle out-of-the-box. There is no way to apply a customer payment (from the Receivables module) to something in the SOP module. There is also no way to enter one payment in SOP and split it out to multiple SOP orders.
You will either have to come up with some manual workarounds or a customization. Nothing easy I can recommend and no one answer…this would require a much more in depth understanding of the transaction flow of this specific customer.
-Victoria
Hi Victoria,
In Report writer,is there any option to have a textbox whose height gets altered dynamically based on the text’s content??
Thanks a lot for your valuable information and support.
Divya,
I am not aware of any way to do this in Report Writer.
-Victoria
Hi Victoria,
We are currently using version 9 and have multiple companies with multiple brands in each company. Each of the brands use a different Invoice ID (from Sales Invoice Setup window) to determine which invoice layout we should use and which invoice number sequence it should generate the invoice number from. We use the Electronic Document Delivery functionality in the Business Portal to send invoices via email to customers with a crystal report that has conditionally visible sections for each brand based on the invoice type.
Now in GP 2010 the Electronic Document Delivery functionality has been removed (presumably to get rid of the non Microsoft stuff) and replaced with Word templates. It seems you can use different templates for specific customers but not for different invoice types.
Is that correct and are you aware of any way to get around this because a customer may buy services from multiple brands and therefore they need to use different layouts because there is different information on the invoices?
Hi Victoria: Not sure if this is the right forum for this question but here goes. I know that in the DEX.INI file by setting the “NoPrintDialogs = FALSE” will enable the print dialog box. My question is does this enable the “alignment” section as well? I have an issue where the “alignment” section of the print dialog box is “grayed-out” and therfore inaccessible. Any thoughts? I was unable to find anyhthing in the KB. Thanks in advance.
Hi Teresa,
I am not aware of any way to enable the alignment section of the Print dialog box when printing from GP.
-Victoria
Hi Victoria,
I would like to know if its possible to prepare a letter accessing data from different table.
For example,
I have a requirement to create a letter with customer name as one of the field which is available by default. Also add someother fields which is in another table. I created a merge field by adding a view but no luck.
Do you have any idea.
Thanks,
Angelina
Angelina,
Are you referring to the Letter Writing Assistant in GP? I don’t usually work much with that, perhaps a better way to get this question answered would be to post your question on the GP Community forum.
-Victoria
Hi Victoria,
Your reports are so great and soo helpful. Have you ever done a checkbook register showing the breakdown of each bank deposit transactions that make up each checkbook deposit?
Regards
Tricia
Hi Tricia,
I have created a checkbook register report in Crystal before…but not one in SQL or with the bank deposit details…I will put that on the list of code requests for future blog posts. Thanks for the idea.
-Victoria
Hi Tricia,
Check out my latest blog post to see if that will help with what you’re looking for: http://victoriayudin.com/2010/10/14/sql-view-for-dynamics-gp-checkbook-register/.
-Victoria
Hi Victoria,
Fantastic. I will have a look:)
Tricia
Hi Victoria
Have you evere made a view that has this data in it?
Customer… Current month sales/ Same month last year / % diff + or -…Current YTD/Last YTD/% diff + or -…and total sales last year???
Love the SQL Date ref!!!! I am always looking that up but not anymore thanks to you!!!!!!!
Vic
Hi Vic,
Glad you’re finding the SQL date stuff useful…some of that I really don’t like doing without a full pot of coffee to keep me going.
For the sales query question, I think I can put together some code that, it’s a good idea and I am sure many people would find that useful. Question for you – are you looking to use the sales amounts from SOP transactions? Or from the customer summaries in Receivables? Or something else? Just want to have a starting point that would be helpful to you.
-Victoria
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
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.
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
Hi Victoria,
I am creating a report using Excel Report Builder to show AP againg by Location (Using the PO Location Code). The report links a SQL View to a GP table. When it links the GP table (Purchase Order Line) I get a record in the report for each PO Line Item. I attempted to filter to only show LineNumber 1 but that is not working how I had hoped and I still see duplicates.
Any advice? Basically trying to show unique records only as Gordon mentioned above.
Hi Mitch,
My advice would be to get everything you need into your SQL view, don’t link to the GP table and have additional ‘code’ inside any of the builders.
-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
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
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
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
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.
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.
Victoria,
Great website – I always find something useful here…
I have been asked by my accounting team to replicate a Smart List report for At Risk orders; basically the criteria are: SOP Type=Order, Document Status=Unposted, Batch Number=AT RISK
However, I am having some trouble finding some amounts… specifically, I need to show the Document Amount and the Remaining Subtotal for each order that it finds. I thought I could get the Document Amount from SOP10200.XTNDPRCE and the Remaining Amount from RM20101.OPEN_REMAINING_AMT but those aren’t matching what I see when I run the Smart List report in Great Plains.
Any advice?
Hi Craig,
No unposted SOP orders will ever show up in RM tables – only posted SOP invoices and returns will be in RM tables. There is also no field called OPEN_REMAINING_AMT in the RM20101 table, so I am a little confused as to what you’re looking at.
However, if all you want is the Remaining Subtotal for open SOP orders, that is stored in field REMSUBTO in the SOP10100 table. Hope that helps.
-Victoria