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
- October 2018: GP Reports Viewer now compatible with Dynamics GP 2018 R2
- January 2018: GP Reports Viewer now available for Dynamics GP 2018
- August 2017: GP Reports Viewer now works with Collections Management to email invoices
- November 2016: New mass email feature for GP Reports Viewer
- June 2016: GP Reports Viewer is available for Dynamics GP 2016
- November 2015: New functionality added to GP Reports Viewer to copy security settings for users and/or reports
- July 2015: GP Reports Viewer is now compatible with Dynamics GP 2015 R2
- February 2015: GP Reports Viewer is now available for Dynamics GP 2015
- November 2014: Excel reports functionality now available in GP Reports Viewer!
- June 2014: GP Reports Viewer is compatible with Dynamics GP 2013 R2
- February 2014: GP Reports Viewer adds Mass Customer Statement functionality
- 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 Yudin 
Great content Victoria, thanks!
A question, an auditor is requesting me all the operation a user has made yesterday. Can you please tell me how can I do that?
LikeLike
HI Jose,
GP does not track this kind of stuff. Unless you have something else in place beforehand to specifically track this, there is no way to provide this information.
-Victoria
LikeLike
Hi Victoria.
We have a request from our auditors to add 3 more aging columns (61 to 90, 91 to 120, and 120+) to the Purchasing HATB report. We have a Smartlist report and a SQL view report that shows the data matching but the auditors want a PDF type report to prove validity. Do you know of one that has been developed for this reason?
Thanks,
Steve
LikeLike
Hi Steve,
I always wonder when I hear a request like this, “Do auditors not think we can modify a PDF in 2019?” 🙂
Anyway, here is a link to instructions for adding aging buckets to an aging report. I think it should be pretty much the same for the HATB report and newer GP versions: https://support.microsoft.com/en-us/help/864847/how-to-add-a-new-bucket-to-the-aged-trial-balance-detail-with-options
Hope that helps,
-Victoria
LikeLike
You are the best! Thank you!
LikeLiked by 1 person
Hi Victoria, Do you have any SQL for Inventory turnover report?
LikeLike
Hi Jennifer,
No, sorry, I do not have one of those.
-Victoria
LikeLike
Hi Victoria,
Can I upgrade directly from GP 2010 SP3 into GP 2016 or GP 2018
LikeLike
Dana,
No, this is not possible. You can only skip one version.
-Victoria
LikeLike
Hi Victoria
I wasn’t sure in which section to post this question:
I have been looking in GP 2010 where I can amend the default Report Destination (Send to Printer or Screen View options) for the Asset Delete Utility. By default it selects both ‘Screen’ and ‘Printer’. I know that in Posting Setup you can change this for all other Ledger reports except for Fixed Assets.
Please advise. Many thanks.
LikeLike
Hi Guido,
For reports that are not part of the Posting Setup, I believe these defaults are controlled independently for each user by their User Preferences. Microsoft Dynamics GP | User Preferences.
Hope that helps.
-Victoria
LikeLike
Thank you Victoria! Much appreciated.
LikeLike
Hello, I’m fairly new to GP and SQL. We’ve recently upgraded to GP 2016R2 from 2010. I’m having an issue with a smartlist I created from a SQL view I wrote. The report works fine for what accounting needs it for but the rows in the lower left-hand corner count up to 34000 then start counting back until it reaches -0 then back up. It continues to do this until the report is complete. Wondering if anybody has seen this before?
LikeLike
Hi Dan,
I’ve never seen that happen before. Are you using SmartList Builder or SmartList Designer? When the SmartList is finally complete, does it actually have 34,000 rows?
-Victoria
LikeLike
I am using Smartlist designer. There are about 300,000 rows.
LikeLike
Dan,
The volume of the data may have something to do with it, it also may be something to do specifically with SmartList Designer – I don’t use it too often, as most of our customers using custom SmartLists have SmartList Builder. I would also suspect that it takes a lot of time for all of this data to display in SmartList. For what it’s worth – SmartList is really not designed for huge result sets like this. It’s best when you are looking for about 1000 or less results. For something like this you might want to consider a different type of report, maybe directly in Excel or using a different reporting tool.
-Victoria
LikeLike
I hope I’m doing this correctly…I am trying to run one of your SQL queries (Vendor Totals by Year) against the data in my company. I get this error message:Msg 262, Level 14, State 1, Procedure view_Vendor_Totals_by_Year, Line 3 [Batch Start Line 0]
CREATE VIEW permission denied in database ‘AHPN’.
Msg 15151, Level 16, State 1, Line 58
Cannot find the object ‘view_Vendor_Totals_by_Year’, because it does not exist or you do not have permission.
My IT dept doesn’t know how to fix. I’m at a total loss. Do you know what kind of access I need? I think I have Data Read access and have been told that the next step up is System Administrator.
Thank you so much for your help!
LikeLike
Hi Pat,
It sounds like you are logged into SQL without enough permissions to create a view. Hopefully that will help your IT dept fix this for you. The second error comes up because it assumed the view to already be there, and you were not able to create it so that is to be expected.
-Victoria
LikeLike
Victoria, Found your ‘very helpful’ view_Current_Payables_aging_Detail and was wondering if you were familiar with Rockton Multi Entity Management add-on. I want to add my facilities as a row sort but not sure if that is possible
LikeLike
Hi Sam,
Do you mean Binary Stream’s Multi Entity Management? If so, yes, this is possible and I have done this in the past for similar reports. I believe the MEM tables you need to add for this are B3920001 and B3920200.
-Victoria
LikeLike
awesome we use binary stream and have about 53 facilities / adding that ability to sort with facilities would make me HOTD ( hero of the day)
Sent from iPhone Outlook
_____________________________
LikeLike
I’m trying to add B3920001 to a GP cheque remittance so I can subtotal by entity when using Centralized AP, how do I add a table from a different module in RW?
LikeLike
Fab,
I don’t work with Report Writer much, but I don’t believe you can simply add a table from another dictionary, you will need some kind of customization to allow this.
-Victoria
LikeLike
Victoria,
I’m trying to create non-inventory invoices in GP. All invoices we currently make are for inventoried items but the need has now come up to invoice customers for services or fees that are not related to inventory. Is there an easy way to do this without making huge changes?
LikeLike
John,
Typically, I recommend doing this with items that are set up with Item Type Services (as opposed to Sales Inventory). But to really set this up right and not impact your other sales inadvertently, I would recommend discussing this with your GP partner.
-Victoria
LikeLike
I am trying to create a GP Payables check remittance report (not modify the existing report). Do you know the source for the fields on that report, is there a view, or a table? I want to be able to run the report after the checks have posted.
LikeLike
Lynda,
I think you should be able to use the code from this blog post to accomplish what you need.
-Victoria
LikeLike
I am going to develop a SSRS report by the way, not use Report Writer or Crystal.
LikeLike
Lynda,
99% of the time when I create reports I write the SQL code first. That way, the reporting tool is irrelevant and the same ode can be used with whatever reporting tool you choose.
-Victoria
LikeLike
Victoria:
I’ve been asked to modify the PM computer check edit list/posting journal to show the vendor invoice number instead of the payment number. Why they want this I don’t know. Anyway, I’ve been able to add the open/work apply table and the apply to document number. When I view the edit list I can see the new fields I’ve added, but when I post the payments and print the computer check posing journal all the fields from the apply table are blank. My thinking is that by the time the report prints the data has moved to the historical apply table and is no longer available to the report. Do you think this reasoning is correct? Have you ever added the apply table to the computer check edit list/posting journal successfully?
Thanks.
Ralph
LikeLike
Hi Ralph,
I try not to mess with the posting reports, so I cannot answer your question directly as I’ve not tried to do what you’re attempting. However, what I have done in the past is completely replace the Check Edit List with my own report that does not take up half a page per check and shows the information in a much nicer way. I have done this for a few customers using Crystal Reports and our GP Reports Viewer product (to launch the Crystal Report). Sorry not to have a more specific answer to your question.
-Victoria
LikeLike
Hi Victoria, similar request to Ralph… can I send you a sample and pay you to build us a report
thanks – Mike
LikeLike
Hi Mike,
Sure. I will email you directly.
-Victoria
LikeLike
Your blog has been helping me a lot. Thank you
LikeLike
Hi Victoria,
Have you ever generated an ssrs MAP from GP that plotted the top 10 customers by sales etc.
My goal is to generate a bunch of TOP X queries. Then be able to plot the resulting data on a MAP in SSRS that is then drillable to lower levels (layers) Country down to State, to City then ZIP etc.
My thought would be to add Latitude/Long to the RM00101 and 102 tables.
But I may need a table the points to the CUSTNMBR+ADRSCODE with lat/long.
Any help or thoughts you may have on this, would be wonderful to hear!
Thank you in advance,
Gene
LikeLike
Gene,
Sorry, I have not done anything like this. You might want to post this on the Dynamics GP community forum to see if anyone there has done something similar.
-Victoria
LikeLike
Victoria,
In Report Writer, would it be possible to add a subtotal by item class on an Invocing invoice? I have tried it and I can get the invoice to group by item class but no subtotal and it page breaks after each change in the item class.
Thank you for your time
LikeLike
HI Dan,
This is probably possible in Report Writer, but I cannot tell you how, sorry. I know that I have done this exact thing on invoices I have created in Crystal Reports and it’s worked great. For help with Report Writer, I recommend posting your question on the Dynamics GP Community Forum so that folks that work with Report Writer can help you out. I really try to stay away from Report Writer and instead prefer to use Crystal, SSRS, or even Excel.
LikeLike
Hi Victoria,
I have a quick question. There is a smartlist in GP under Purchasing – Payables Transactions. In that smartlist, I can add a field called Document Date. Would that be the same date as the Check date? If not, do you know which field I would be able to use?
Thanks so much!
LikeLike
Hi Chan,
For Payment transactions, the Document Date will be the date printed on the check. For all other transaction types, there is no such concept as ‘check date’. Hope that helps.
-Victoria
LikeLike
Chan,
Yes, the Document Date should be the check date for payments. One thing you can do to test this for yourself is put a different date for each date field you find when entering/printing a check. Then go and run the SmartList and see where what date ends up where.
-Victoria
LikeLiked by 1 person
Hi Victoria,
I’m relatively new to GP. I’ve been tasked with finding how we can use forms printer with multiple companies. Each is its own database. We use Crystal reports to design our templates. Each company will use the same reports. Any help you can give is greatly appreciated.
LikeLike
Hi Bill,
If you already have the Forms Printer product and are using it, you may be best served by contacting them with specific questions. However, if you are looking for a product that will help you use Crystal Reports with Dynamics GP, please take a look at out GP Reports Viewer: http://www.GPReportsViewer.com/GP-Reports-Viewer.
-Victoria
LikeLike
Hi Victoria,
Just wondering if you have a SQL view or smartlist XML code you could give me that replicates the Purchase->Received/Not Invoiced report that is run out of GP? I am trying to do this at the moment, but seem to be getting more data than this report generates, so having trouble identifying the various filters this report uses to exclude certain data.
Thanks
Matt
LikeLike
Hi Matt,
I think that SmartList is doing the following:
select * from POP10500 where QTYSHPPD > QTYMATCH and POPTYPE = 1
-Victoria
LikeLike
Need some assistance on getting data that’s located on the Billing Schedule Maintenance screen.
A user requested to develop a report and display the items that are located on the Billing Schedule Maintenance screen. For the particular customer, they have ten items listed.
I used one of your views and I was able to pull in six of the ten items. I used my own coding and I get the same result.
Not sure where else to go to get the remaining items that are located on the mentioned screen.
The tables I used are SOP30200, SOP30300 then UNION all with SOP10100 and SOP10200.
Hopefully my explanation was clear and understood.
LikeLike
Is this a custom window? I don’t recall a window with this name in GP…maybe it’s in a module I do not typically use. How do you get to it?
-Victoria
LikeLike
Is there an email address so I can send you a screenshot of the screen I mentioned?
LikeLike
You can sent the email to vyudin@outlook.com. It would also be helpful to know the navigation path to get to the window.
-Victoria
LikeLike
When GP is open, the left side pane, there’s an option called Adv Recurring Contract Billing. From what one of the GP ladies told me, it’s a third party called BinaryStream. When that’s selected, then under Cards, there’s a link called Billing Maintenance.
LikeLike
In that case, I would recommend emailing Binary Stream and asking them where to find the data you are looking for. 🙂
-Victoria
LikeLike
For anyone else that might be interested, I contacted Binary and they told me that these tables are used for the screen I mentioned and I have a majority of what I need.
PEER_Billing_Schedule_MSTR(B0800001)
PEER_Billing_Schedule_Detail(B0800002)
BSSI_Billing_Schedule(B0800015)
LikeLike
Hi Victoria, have you ever created or know of a report that provides for outstanding Purchase Orders but date sensitive. Meaning, passing a date received parameter so that balances of PO’s and their line items can be reviewed for prior month-end balances?
Thanks.
LikeLike
Hi Pete,
I have not yet created nor seen any reports like this. Sorry. 😦
-Victoria
LikeLike
Hi Victoria,
Where (in what table) can one find the text values of ProfitType 1-9? That is to ask, is there a lookup table? and if there is what is it’s name? If there is not and it is an enumeration what are the proper text values that correspond to the integer values 1-9? Thank you in advance.
LikeLike
Hi TK,
ProfitType is not a term that I am familiar with in Dynamics GP. Are you seeing it on a window somewhere? If so, what is the name of the window?
-Victoria
LikeLike
The window in question is “Budget Maintenance”
The Profit Types appear to express the way that the “Profit Amount” is to be interpreted by the viewer.
That is to say, for instance PUfix which is the text (code) value of one of the profit types, relative to the Profit Amount is on a “Per/Unit” of Unit of Measure (UofM) basis, whereas TProf is Profit Amount divided by Quantity.
The 9 Options for Profit Type are:
Billing Rate
Markup%
Profit/Unit-Fixed (PUFix)
Profit/Unit-Variable
Total Profit (TProf)
% of Baseline
% of Actual
None
Price Level
What I don’t know is what integers 1-9 correspond to their text values and I don’t want to assume that they are in correct sequential order based on their position in the dropdown select. To add to the intrigue they all appear to have abbreviations or Codes for shorthand. This suggests to me that there is a table somewhere which defines these values, but I have yet to find it in the more than 600 tables in the system database. Can you help?
Thank you in advance. 🙂
LikeLike
Hi TK,
Sorry, I do not have anything called Profit Type on the Budget Maintenance window in my GP. Are you sure this is not a customized window or a different product?
Worst case scenario, since there are only 9, put in each value one at a time on a window, save it and check the value in the table.
-Victoria
LikeLike
Hi Victoria,
Just wondering if you ever created any Views to track who created or modified Inventory In-Transit Transfers? As you will know, the userid field from the SVC00700 (work) and SVC30700 (History) only seems to show the last user who opened/edited the document and not the original creator.
LikeLike
Hi Matt,
I’ve not done any reports on inventory in-transit transfers. In general, if the information you want is not being stored in a GP table, you will need to first create a way to track/store it. Typically for something like this, that would involve creating a SQL trigger and an additional table to store the data. Once you have the data tracked somewhere, then you can create a report against it.
-Victoria
LikeLike
Thanks, i did this and it is working well.
LikeLike
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')
LikeLike
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
LikeLike
Thanks Victoria, i created something today which looks like it is doing the job. I did the same thing for the Exchange Rate Tables, as a few times people have entered an exchange rate incorrectly, so i have created a trigger that emails the finance team any time an exchange rate is changed.
LikeLike
Matt,
Excellent, thanks for the update.
-Victoria
LikeLike
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!
LikeLike
Addin,
Sounds like you may not have granted security to the modified report to your users.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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 ?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
Hi Patrick,
I just posted a new view to show this information: https://victoriayudin.com/2011/10/06/sql-view-for-bank-deposits-and-receipts-in-dynamics-gp/.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Thanks!
It is for items. Purchasing line items. This smartlist he made has things like order poit qty and last rcpt cost.
thanks
Vic
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Samer,
If you are looking to link on lot number, you may also need to look at IV30400.
-Victoria
LikeLike
Hi Victoria,
Do you happen to have a listing of all of GP standard reports for all modules?
thanks in advance,
LikeLike
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
LikeLike
Besides, I like to create my own reports. 🙂
-Victoria
LikeLike
ok. Thanks victoria 🙂
LikeLike
How about a SQL View for sales of items by year? Any chance you have one for that?
LikeLike
Hi Mark,
I just posted a new view for this: https://victoriayudin.com/2011/08/11/sql-view-for-sales-by-item-by-year/.
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
SOP30300.QTYCANCE is 0 for an order that I KNOW is cancelled. Where else can I find out about cancelled orders?
LikeLike
Robbie,
It’s possible the whole order was voided. In that case, you would see SOP30200.VOIDSTTS = 1.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Divya,
I am not aware of any way to do this in Report Writer.
-Victoria
LikeLike
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?
LikeLike
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.
LikeLike
Hi Teresa,
I am not aware of any way to enable the alignment section of the Print dialog box when printing from GP.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Tricia,
Check out my latest blog post to see if that will help with what you’re looking for: https://victoriayudin.com/2010/10/14/sql-view-for-dynamics-gp-checkbook-register/.
-Victoria
LikeLike
Hi Victoria,
Fantastic. I will have a look:)
Tricia
LikeLike
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
LikeLike
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 for 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
LikeLike
Did we ever get an answer to this? I have been banging my head against the wall. Would love to know and would be in heaven is it was created using SOP transactions.
Thanks – Eddie
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks for the prompt – was hoping someone here would know.
Regards
Stu
LikeLike
Just FYi and as follow up HTML tags appears to work in this scenario
Stu
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Thank you very much Victoria for your usual support. I guess we really should talk to GP Support about this.
herson
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
I will contact them. Thanks
LikeLike
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!
LikeLike
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
LikeLike
thanks!
will do.
LikeLike
Thanks for your time Victoria. would do just that.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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,
LikeLike
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
LikeLike
Hello Victoria
Have you set up any web-based IS Report? If yes can I have a look of the same?
Regards
Sanjay.
LikeLike
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
LikeLike
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
LikeLike
Sanjay,
Sorry, I do not have any samples that would do this outside of FRx.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks again for your prompt reply Victoria. I will take a look at CM20500 and see if that will help.
Regards,
Gordon
LikeLike
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
LikeLike
Victoria, I got it! It was an issue with the linking.
Thanks again for all of your help.
Gordon
LikeLike
Gordon,
Excellent, thanks for following up!
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Thanks Victoria! I know I will be visiting this site often as we move forward with our implementation.
LikeLike
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?
LikeLike
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
LikeLike
Victoria,
Ok. I see.
Thank you very much.
-Jim
LikeLike
Thank you very much…..
No luck on SQL script for Cash forcast right?
-Jim
LikeLike
Jim,
I don’t have any scripts readily available for cash forecasts, sorry.
-Victoria
LikeLike
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
LikeLike
Jim,
To get the budget data from GP you can use GL01201, a view that comes preinstalled with GP.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Victoria,
What about sister screen “Item Account Maintenance – Costing” This screen shows up when I access the “Item Account Maintenance” window, and it has the variance and wip accounts details. If this screen is empty, no purchasing costs will be captured. Which table hold the data from the “Item Account Maintenance – Costing”
LikeLike
Raouf,
I do not have this window in my GP, maybe it is for a module we do not have installed, or possibly a 3rd party product. I would recommend posting your questions on the Dynamics GP Community Forum.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Thanks for everything, great information keep it going.
LikeLike
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.
LikeLike
Thank you for your quick, helpful response. I will look into each reference.
LikeLike
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!
LikeLike
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
LikeLike
Hello Miss Victoria,
Thank you for this very helpful information. I appreciate your efforts very much! Thank you.
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike