Auto-fill UDF with calculation

[Former Member]

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

[zal_parchem2 · Accepted solution]

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

[Former Member · Accepted solution]

Well, you now need to lock on to the order number on the screen so that you would not return every single project out there, but only the one related to the order you are working with.

Follow these steps:

Create a new query with this code and save it:

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

T1.Project AS 'Project Code'

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

Open the sales order screen, find an order that had some project activity, like SO 1, assuming it is still open.

For testing purposes, while on this order, go to the Tools menu, Queries, User Queries, find this new query and launch it from there.

You should get back only one line corresponding to your order number, 1 in this case.

Once you prove the concept, associate the above query with your UDF on the sales orders. Now run Shift F2 on this UDF. You will only see the net amount in your UDF.

Liviu

[Former Member · Accepted solution]

Liviu,

Ok, that worked perfectly until I assigned it to a UDF. When I did, it delivered the "2,215,280.4". I am not sure what this represents.

Any ideas?

You have been a great help.

jennifer

[zal_parchem2]

Well thank you Jennifer - when I make it to Cinncy again, I will do that!

This is a great example of how this forum works when folks put their experience, skills, and heads together!

Good luck - Zal

[Former Member]

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

[Former Member]

It works...thank you Liviu and Zal so much!

Next time you find yourself in Cincinnati, let meknow and I will buy you lunch!

Thanks again

Jennifer

[Former Member]

Cool. Thank you for the points! Good luck with your queries!

Liviu

[zal_parchem2]

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

[zal_parchem2]

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

[Former Member]

Zal,

I did as you suggested and it works great...except that the number 2,215,319.004 is being delivered to the UDF, regardless of the project I am querying.

Thanks to you and Liviu for all your help today.

Any other thoughts? I am working on it as well, but haven't figured it out yet.

Thanks,

Jennifer

[Former Member]

Hmmm, what is the sales order number you are wokring with?

Are you in Add mode?

On the sales order you are getting this number on, run the query from the Tools/Queries/User Queries menu. What do you see filled in instead of the $[$8.1.0]?

Liviu

[Former Member]

Ok, I just noticed that if I put the mouse on the UDF and highlight it and then run the query throught tools-queries-user queries... the 2,215,319.004 number is the result. If the mouse is somewhere else in the sales order screen, the correct number is the query result. If I run the FMS from the UDF, I get the 2,215,319.004.

So the problem is not the FMS, but something about the UDF.

I'll keep trying!

Jennifer

[Former Member]

I should also mention that the 2,215,319 number is probably close to the total for all projects, but only if you include account type I, E and N. I haven't tested it, just a theory

[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

[zal_parchem2]

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

[Former Member]

Zal,

Ok, that worked. Now I need to auto populate some field that I can use in reports or a UDF that I can attach to a marketing document.

Thanks,

Jennifer

[zal_parchem2]

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

[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

[zal_parchem2]

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

Zal

[zal_parchem2]

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

[Former Member]

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

[Former Member]

Hi,

Thanks for your answer.

When do you want the calculation of the gross profit done:

- During the creation of the marketing document?

- When you review the project?

- On a different timeframe (i.e. at the end of the month)?

Regards,

Eric

[Former Member]

When I run the query as I wrote it, it delivers 170 rows -

Debit Amount Credit Amount Account Category Project Code Net Amount

72,288.10 144,392.10 I 1 -72,104.00

22,707.87 17,143.96 E 1 5,563.91

38,784.52 4,375.00 E 3 34,409.52

5,760.77 2,162.14 E 4 3,598.63

4,023.81 2,165.00 E 5 1,858.81

39,064.99 4,620.00 E 6 34,444.99

0 20,643.12 I 6 -20,643.12

1,395.92 259.96 E 7 1,135.96

9,608.75 9,608.75 I 7 0

This goes on for 170 rows. In the case of project 1, I would like to deliver the net between -$72,104.00 and $5,5563.91, which is -$66,540.09, change the sign in front of it to a positive number and have that number auto populate in a UDF.

I tried to copy and paste the changes you suggested and I am getting an error message.

Any other thoughts?

Thanks for your assistance,

Jennifer

[Former Member]

Ideally, I would like it run it anytime to review the progress of the project (then it is really only a summary of the to-date project costs) and after we have produced an A/R Invoice, which is when it will report Gross Profit.

Thanks!

Jennifer

[Former Member]

OK

That's what I suspected. In this case, the UDF and the associated FMS won't be really usefull.

I would better suggest an external query with parameters.

I your original query you could modify the FROM clause to add a link to the project table (from memory: OPRJ) and the WHERE clause to add a parameter. Something like T1.Project = [%0]. I'll do it better later (no time now...)

By doing this you could execute it at any time and have a correct result.

Regards,

Eric

[Former Member]

Eric,

I will try this. Can I then get the result to auto populate a UDF somewhere? This is all because some members of management would like to see just a number and aren't capable of running something outside the system. Maybe what you are suggesting still accomplishes this.

Thanks,

Jennifer

[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

[Former Member]

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

[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