Skip to Content
author's profile photo Former Member
Former Member

Auto-fill UDF with calculation

Hello,

I have added a UDF to record the Gross Profit on a project. We manually assign the projects the same number as the Sales Order number. The UDF is on the sales order.

I have written a query using Query Generator to pull together the data I need to calculate the Gross profit, but now I need to subtotal the data by project number (this is the same number as the sales order).

I think I need to link the query to a FMS, but I am struggling getting the query to deliver the actual number I need. Here is what I have so far:

SELECT SUM(T1.Debit) AS 'Amount Debit Amount', SUM(T1.Credit) AS 'Amount Credit Amount', T0.ActType AS 'Account Category', T1.Project AS 'Project Code', SUM(T1.Debit - T1.Credit) AS 'Amount Net Amount' FROM [dbo].[OACT] T0 INNER JOIN [dbo].[JDT1] T1 ON T1.Account = T0.AcctCode AND T1.Account = T0.AcctCode WHERE T1.Project > N'0' AND T0.ActType <> N'N' GROUP BY T0.ActType, T1.Project

Is it possible to Group by the calculated column which is T1.Debit-T1.Credit?

Thanks in advance for any help.

Jennifer

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

11 Answers

  • Best Answer
    Posted on Nov 29, 2007 at 04:16 PM

    Hurray Jennifer - good going!

    Now you have the first part of your question answered to get the dollars.

    You might want to search the forum on "UDF" as I know there are several other postings on this and I am just not the expert on auto-fill with calcuations! Shift+F2 and such...

    Please, if you do get the resolution somewhere else, post where you found it because it seems quite a few folks are interested...

    Thanks - Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 03:22 PM

    Hi, Jennifer!

    A quick note on this query is that it could return multiple lines, in which case only the first one will come up on your UDF.

    It does not have a restriction by the sales order/project. You can do this by using the $[$...] format to get T1.Project = $[$8.1.0]

    Here is a quick re-write of the query:

    SELECT SUM(T1.Debit) AS 'Amount Debit Amount', SUM(T1.Credit) AS 'Amount Credit Amount', T0.ActType AS 'Account Category',

    T1.Project AS 'Project Code', SUM(T1.Debit - T1.Credit) AS 'Amount Net Amount'

    FROM [dbo].[OACT] T0 INNER JOIN [dbo].[JDT1] T1 ON T1.Account = T0.AcctCode

    WHERE T1.Project = $[$8.1.0] AND T0.ActType <> N'N'

    GROUP BY T0.ActType, T1.Project

    As far as grouping, yes, you can group by the debit - credit, you have to put them in paranthesis, but that would not help too much. It will only bring up all the possible differences and this will only clutter your results. It might not be useful even if possible.

    Save the query, open the Order screen, go to Tools, Queries, User Queries, find the category your query resides in, and run your query from there. What do you get on the screen?

    Liviu

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      It works ok on my side when I run it on a sales order. What is the error?

      Regardles, use your query, remove the T0.ActType AS 'Account Category', from the select part, remove T0.ActType, from the group by part and you should be good to go with Zal's suggestion. You might need to play with the signs.

      SELECT SUM(T1.Debit) AS 'Amount Debit Amount', SUM(T1.Credit) AS 'Amount Credit Amount', T1.Project AS 'Project Code', SUM(T1.Debit - T1.Credit) AS 'Amount Net Amount' FROM [dbo].[OACT] T0 INNER JOIN [dbo].[JDT1] T1 ON T1.Account = T0.AcctCode WHERE T1.Project > N'0' AND T0.ActType <> N'N' GROUP BY T1.Project

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 03:16 PM

    Hi,

    I'm not sure to understand exactly what you want to do...

    What you want, is to determine on a project basis, how much money you earned or loosed. Is it correct?

    If yes, you must know that at each marketing document, you can assign a project. This "common" project, can be modified on line basis (changed directly on each line item of the document).

    After this, the only "problem" becomes to write a query (report) to know your Gross Profit.

    Regards,

    Eric

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Eric,

      Yes, we do exactly what you suggested. We assign all revenue and expenses with a project number. In marketing documents, journal entries, everywhere.

      Now I need to calculate the gross profit and have it automatically populate in a UDF. The UDF can be anywhere.

      Thanks,

      Jennifer

  • Posted on Nov 29, 2007 at 03:33 PM

    Jennifer - instead of GROUP BY, do you mean ORDER BY?

    Basically - are you looking to arrange the results from the SQL in lowest dollar amount to highest or vice versa?

    Something like this?

    GROUP BY T0.ActType, T1.Project

    ORDER BY (sum(T1.Debit) - sum(T1.Credit)) DESC

    NOTE:

    If you add (T1.Debit-T1.Credit) to your GROUP BY statement then the whole purpose of grouping by account type and project is lost (because each calculation is displaye) - but you can certainly use calcs - just give it a try!

    GROUP BY T0.ActType, T1.Project, (T1.Debit-T1.Credit)

    [you need the parenthesis]

    I notice you have T1.Account = T0.AcctCode twice in your WHERE statement?

    Good luck - Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 29, 2007 at 03:38 PM

    yikes - well my message certainly did not help after seeing all the further explanation - sorry Jennifer

    Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 29, 2007 at 03:44 PM

    if you just GROUP BY Project alone and nothing else the system will give you that dollar figure...

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      I am getting an error message when I delete the Group By T0.ActType. But I added that because I only want to include accounts that have been categorized as an income (I) and expense (E) account, not an other (N) account. Before I added the ActType referernce, I was getting balance sheet accounts, like A/R and Inventory that had a project number associated with it.

      Any other thoughts?

      Thanks,

      Jennifer

  • Posted on Nov 29, 2007 at 04:02 PM

    You removed ActType from the SELECT section and removed it also from the GROUP BY and you are getting an error message???

    You would not pick up any 'N' with the way your SQL runs right now, because you are eliminating them with the statement - AND ActType <> N

    Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 29, 2007 at 05:21 PM

    Jennifer

    When you created the UDF did you make it as a "TYPE" of "Unit and Totals" and make it a "STRUCTURE" of "Amount"?

    My UDF works great when I set it like that...

    Zal

    PS - working right along with you to learn also...

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I forgot, the UDF is on the additional window on the right, in which case you will have to use a - infront of 8. This will tell SAP to select field # 8 from the main form.

      Replace $[$8.1.0] with $[$-8.1.0] and it should work.

      Your 2,215,319 is the sum of all journal entries that are not N and have T1.Project = '' (ie no project).

      Liviu

  • Posted on Nov 29, 2007 at 07:32 PM

    WOW - I just substituted...

    WHERE T1.Project = $[$-157.1.0]

    NOTE: 157 is the field where the BP Project Code is located at on the Accounting Tab on the Sales Order.

    And now the system updates the UDF with the dollar amount when I press Shift + F2 in the UDF...

    Are we getting closer???

    LOL - talking about people putting their heads together!!!

    Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 07:59 PM

    wanted to give credit to 2 people who helped answer the question

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.