SQL view for rolling 12 months of sales by item by site in Dynamics GP

12 Responses to “SQL view for rolling 12 months of sales by item by site in Dynamics GP”

1. 

   Joanna Reply August 20, 2019 at 2:01 pm

   Victoria,
   I have a question. We are on GP2010 and I was wondering if there is a SQL or Smartlist for Deferred Revenue. What we want to do is confirm the “contract” = Quarterly, Yearly, etc. with the invoices that are generated via that contract. We have found some example of invoices that were created but were not being deferred which we then had to do manually to the GL.

   LikeLike

   • 

     Victoria Yudin Reply August 21, 2019 at 7:52 am

     Hi Joanna,

     Are you using the Revenue and Expense Deferrals module in GP? If so, unfortunately, there are no SmartLists for that data. There should be a report and inquiry window for Deferrals. I know they are in my GP 2016 and I am pretty certain they are also in GP 2010, but I don’t have any easy way of confirming that. Not sure if those will give you what you need, though.

     I’ve done some custom reporting on this in the past, but it’s typically pretty specific to the data and report needed, as people use this module in different ways. If that is what you need, let me know if you are interested in pursuing this as a consulting engagement.

     -Victoria

     LikeLike

     • 

       gooteejl Reply August 21, 2019 at 8:16 am

       Thanks Victoria for the quick response.

       I will look into the Deferred Expense Module and see if this will work for what we are looking for.

       LikeLike

       • 

         Victoria Yudin Reply August 21, 2019 at 8:25 am

         Hi Joanna,

         Sorry if I misunderstood your question – I thought you were already using that module. If you’re not and you need deferrals – it’s perfect for many situations. But since you’re on GP 2010 you might need to check into your GP licensing to see if you have it already and if not, what your options are.

         -Victoria

         LikeLike

2. 

   Tom Hamill Reply April 11, 2018 at 2:12 pm

   Victoria, First of all.. thank you for creating this site. I can’t tell you how many times I’ve come here to find translations for fields like RMDTYPL, DISTTYPE and the like.

   I’m working on a GP 2015 project. (My experience dates back to Dynamics 3.15… how old am I??) Anyway, I need to create a report the “reassembles” allocation entries from the GL20000 and 30000 tables. Unfortunately, I can’t find anything in the tables that clearly and unambiguously indicates that these were enter using a Fixed or Variable allocation account.

   Do you know of either any table that stores the original allocation entry OR any way to identify GL Entries that were entered using an allocation account?

   LikeLike

   • 

     Victoria Yudin Reply April 11, 2018 at 2:24 pm

     Hi Tom,

     Thank you for your kind words. I have worked with GP since it was on DOS, so I guess I am old, too. 🙂

     My understanding of how allocations work in GP is that it’s a temporary place holder. As soon as the transaction is posted, the allocation accounts get “translated” into the actual posting accounts and there is nothing stored anywhere to let you know that there was an allocation done.

     Why do you need to report on this? Perhaps if I knew what the ultimate goal was I could think of something else to recommend.

     -Victoria

     LikeLike

     • 

       Tom Hamill Reply April 11, 2018 at 2:33 pm

       One of our VP’s is trying to get a list of expenses for a particular office. The office expenses are posted to allocation accounts then split to various cost centers. Of course, the accounts in the cost centers also include expenses that have nothing to do with the office expenses. (This means a trial balance wouldn’t be sufficient.)

       This would be easy if the allocated expenses went through AP. PM30600 still has the allocation account’s DISTINDX. Trouble is than many of those AP vouchers debit a prepaid account (prepaid rent for example). We use a gl entry to credit prepaid and debit allocation. Those entries, like you say, seem to be temporary.

       LikeLike

       • 

         Victoria Yudin Reply April 11, 2018 at 2:37 pm

         Tom,

         The only thing I can think of is this is something that needs to be tracked is tracking it in some other way. For example on all transactions that belong to the office that you need to track, enter something else, maybe using MDA or Extender. Then report off that. However that would require additional entry for people that are entering the transactions and them remembering to do this. I don’t think it’s a lot of additional work, but more the change and the remembering that would be important. 😦

         -Victoria

         LikeLike

3. 

   Thomas Goodspeed Reply November 30, 2017 at 6:56 pm

   Victoria, you do such great work and your scripts have been helpful to me so often.
   I wanted to share a template I use for creating Views and such.

   — this first section checks to see if the view exists and if so drops it before creating the view

   IF EXISTS (SELECT name
   FROM sysobjects
   WHERE name = N’Your_View_Name_Here’
   AND type = ‘V’) — note the type is V for view, P for Stored Proc, U for Table
   DROP view Your_View_Name_Here
   GO

   create view Your_View_Name_Here
   as
   –…place body of the view here and end with a ‘Go’ statement……………

   go

   — this will grant select rights to the view.
   grant select on Your_View_Name_Here to DYNGRP
   Go

   LikeLike

4. 

   Peter McQuillan Reply April 20, 2017 at 4:47 pm

   Another awesome view! I just used it as part of a data set in Power BI worked great.
   Thank you.

   LikeLike

5. 

   Stephane Jodouin Reply April 20, 2017 at 3:58 pm

   It works perfectly, thank you so much for this update!
   This will drastically improve our forecast tracking

   LikeLike

6. 

   Blair Christensen Reply April 20, 2017 at 1:37 pm

   Just tried this out using SSRS and am going to post it for internal comments. Thanks!

   LikeLike