GP Reports


Over the last 10+ years we have done a huge amount of reporting on GP data. Most of the time we use Crystal Reports, SmartList Builder, Excel and SRS (probably in that order) to deliver the reports. However, no matter how the report is ultimately delivered, one of the critical steps is knowing where to find the data in the GP tables. I am going to publish some information that we get asked for frequently during reporting. I have a lot of data in various places, so bear with me while I keep adding to this. If there is something you’d like to see, or have additional information you’d like to contribute, please e-mail me.

GP Reports Viewer News


Dynamics GP SQL Scripts


Other SQL Scripts and Links

176 Responses to “GP Reports”

  1. 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!

    Like

    • 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

      Like

    • 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

      Like

  2. 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.

    Like

  3. 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

    Like

  4. 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.

    Like

  5. 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.

    Like

  6. 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.

    Like

    • 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

      Like

      • 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. :)

        Like

        • 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

          Like

  7. 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.

    Like

    • 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

      Like

  8. 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')

    Like

    • Alex,

      If you want to calculate 7 days ago, you can use the following:

      dateadd(d,-7,getdate())
      

      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:

      select dateadd(d,-7,getdate())
      

      -Victoria

      Like

    • 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.

      Like

  9. Addin Adhikacaksana Reply July 17, 2012 at 4:46 am

    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!

    Like

  10. 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

    Like

    • 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

      Like

  11. 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 ?

    Like

  12. 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?

    Like

  13. 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

    Like

    • 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

      Like

  14. 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

    Like

    • 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

      Like

  15. 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

    Like

    • 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

      Like

  16. 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

    Like

    • 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

      Like

      • 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

        Like

        • 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:

          1. Log into SSMS (SQL Server Management Studio) with SQL authentication (as ‘sa’)
          2. Create a new view inside one of the GP databases to point to your CRM view – it should look something like:
            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            create view YourViewName
            as
            select * from CRMdbName..CRMViewName
            GO
            grant select on YourViewName to DYNGRP
          3. Still in SSMS, run the following query:
            select * from YourViewName

          If that returns data, now try to create a new SmartList using SLB and see if it returns any data.

          -Victoria

          Like

  17. 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!

    Like

    • 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

      Like

  18. 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.

    Like

  19. 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

    Like

  20. 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.

    Like

    • 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

      Like

      • Thanks!

        It is for items. Purchasing line items. This smartlist he made has things like order poit qty and last rcpt cost.

        thanks
        Vic

        Like

        • 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

          Like

          • 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

            Like

  21. 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

    Like

  22. Hi Victoria,

    Do you happen to have a listing of all of GP standard reports for all modules?

    thanks in advance,

    Like

  23. How about a SQL View for sales of items by year? Any chance you have one for that?

    Like

  24. 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!

    Like

    • 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

      Like

  25. 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?

    Like

  26. 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.

    Like

    • 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

      Like

  27. 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.

    Like

  28. 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?

    Like

  29. 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.

    Like

  30. 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

    Like

  31. 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

    Like

  32. 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

    Like

    • 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

      Like

  33. 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

    Like

    • 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

      Like

  34. 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

    Like

  35. 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.

    Like

    • 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

      Like

  36. 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.

    Like

    • 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

      Like

  37. 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

    Like

    • 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

      Like

      • 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

        Like

        • 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

          Like

          • Hi Victoria,

            Thank you very much Victoria for your usual support. I guess we really should talk to GP Support about this.

            herson

            Like

  38. 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

    Like

    • 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

      Like

  39. 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

    Like

    • 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

      Like

      • 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

        Like

  40. 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.

    Like

    • 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

      Like

  41. 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?

    Like

    • 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

      Like

      • 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

        Like

    • I will contact them. Thanks

      Like

  42. 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!

    Like

    • 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:

      1. Talk to your GP partner about whether data is being entered properly in the HR module and where to get the reporting you are looking for based on that – this will be much more specific advice for your situation, as opposed to a general answer.
      2. Post a question on one of the GP newsgroups (listed on the right) to reach a broader audience that may be better versed in the HR and payroll modules and tables.

      -Victoria

      Like

    • thanks!

      will do.

      Like

  43. Thanks for your time Victoria. would do just that.

    Like

  44. 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.

    Like

    • 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

      Like

  45. 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

    Like

    • 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

      Like

  46. 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,

    Like

    • 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

      Like

  47. Hello Victoria

    Have you set up any web-based IS Report? If yes can I have a look of the same?

    Regards

    Sanjay.

    Like

    • 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

      Like

      • 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

        Like

  48. 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

    Like

  49. 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

    Like

  50. 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

    Like

    • 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

      Like

      • Thanks again for your prompt reply Victoria. I will take a look at CM20500 and see if that will help.

        Regards,

        Gordon

        Like

        • 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

          Like

          • Victoria, I got it! It was an issue with the linking.

            Thanks again for all of your help.

            Gordon

            Like

            • Gordon,

              Excellent, thanks for following up!

              -Victoria

              Like

              • 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.

                Like

  51. Thanks Victoria! I know I will be visiting this site often as we move forward with our implementation.

    Like

  52. 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?

    Like

    • 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

      Like

  53. Victoria,

    Ok. I see.

    Thank you very much.

    -Jim

    Like

  54. Thank you very much…..

    No luck on SQL script for Cash forcast right?

    -Jim

    Like

  55. 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

    Like

  56. 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

    Like

  57. 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

    Like

  58. 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

    Like

    • 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

      Like

      • 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

        Like

        • 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

          Like

        • 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

          Like

          • 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

            Like

            • 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

              Like

              • 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”

                Like

  59. 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

    Like

    • 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

      Like

  60. Thanks for everything, great information keep it going.

    Like

  61. 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.

    Like

  62. Thank you for your quick, helpful response. I will look into each reference.

    Like

  63. 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!

    Like

  64. Hello Miss Victoria,

    Thank you for this very helpful information. I appreciate your efforts very much! Thank you.

    Like

  65. 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

    Like

  66. 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.

    Like

    • 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?

      Like

      • 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

        Like

Trackbacks/Pingbacks

  1. SQL view with AR apply detail - Victoria Yudin - February 15, 2010

    [...] more views like this, check out my GP Reports [...]

    Like

  2. Adding watermarks to Crystal Reports « Sochinda's Blog - November 27, 2009

    [...] & Events page. You can also see a list of all the Crystal Reports tips published so far on my GP Reports page under Working with Crystal [...]

    Like

  3. SQL view with all SOP line items - Victoria Yudin - May 17, 2009

    [...] with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports [...]

    Like

  4. Adding watermarks to Crystal Reports - Victoria Yudin - May 5, 2009

    [...] & Events page. You can also see a list of all the Crystal Reports tips published so far on my GP Reports page under Working with Crystal Reports. Posted in Crystal Reports, GP Reports Tagged: Crystal Reports, [...]

    Like

  5. How to use a SQL view in SmartList Builder - Victoria Yudin - April 20, 2009

    [...] To start we need a  SQL view.  I have published a number of views for getting GP data, so I will use one of my favorites – Payables Apply Information.  You can see the list of other SQL views I have published on my GP Reports page. [...]

    Like

  6. SQL view with security resource details for Dynamics GP 10 - Victoria Yudin - March 20, 2009

    [...] other SQL code, please check out the GP SQL Scripts section my GP Reports page. Posted in Crystal Reports, Dynamics GP, GP 10.0, GP Reports, GP SQL scripts, SmartList Builder [...]

    Like

  7. SQL view to show items with SOP POP link - Victoria Yudin - February 4, 2009

    [...] any spaces in the column names.  I maintain a list of all the SQL code I’ve published on my GP Reports page so if you’re looking for other scripts, take a look [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,486 other followers

%d bloggers like this: