I need to store Total Sales into a variable, to further aggregate the Gross Profit, and Net Profit

Hello pros,
I have made a manual crosstab in CRXI, but have run into a road block or two. I've included the report and the screenshot with red sketches of my goals.
I need to subtract the Cost Totals from the Sales Totals, to get Gross Profit.
Then, use Gross Profit to subtract the Total Expenses to get an added Total: Net Profit Of Loss.
It's kind of a trickle down effect.
I'm not sure if I have it grouped incorrectly, or it just needs to be done in formulas.
Please note: I have two (102) columns...the left (102) is using the 'Insert Summary' dialog box to create the totals, then the right (102) is creating the summaries with three formulas. This was just for testing which works better. Only the best one will stay.

On another note, I have the percentage column that each row should show the percent of the Total Sales of all Divisions (102-802)...

Background:
The group titles "TypeSales" and "TypeExpenses" are grouped based on a formula using a CASE statement. Thats why Sales and Cost are together.

Thank you for your help!
Luke

SOI2-ManualCrossTab-Add-GrandTot.rpt
ph3.1.PNG
leason2Asked:
Who is Participating?
 
Abdulmalek_HamshoConnect With a Mentor Commented:
I'm not sure if I can do it in the report without having the database at my side, but I don't have Lawson at my side. But I'm gonna explain how to do it:

1- Create Gross Profit formula Fields (GP102, GP402, GP602, GP702, GP802).

2- Put this code inside each of them (modify the code according to the SDIVI:

//This is for GP102
if {Command.CODE} = "Sales" then
  GP102 + {Command.AMT};
else
  GP102 - {Command.AMT};

I assumed that you have only (Sales, Cost, and Expenses), but if you have more codes, then you need to include it in the If...Then statement.

3- Place each of the fields in the Details section, and create summaries at the Group Footers as required.

Now for the percentage, create a formula fields SalesExpPerc. Here's the code:

({@DescrTotalSales}*100)/{@TotalSales}

Note: You can add a summary of a field by right-click it and select Insert -> Summary, and where to place the summary, rather than doing it manually.
P.S: There might be some typos in my code, because I didn't tested it in Crystal.
0
 
Abdulmalek_HamshoCommented:
You want a way to calculate Gross Profit, Net Profit/Loss, Line percentage of total Sales/Expenses?

You want me to do it in the report file and send it to you, or tell you how to do it?

What's the (ERP/Accounting) system you're getting data from?
0
 
leason2Author Commented:
If you're willing to do it in the report file and send it that would be helpful, but please instruct me as well yes. THank you so much!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
leason2Author Commented:
We are using the IBM DB2 iSeries 7R1. The Software on top of that is LAWSON M3.
Although, this is pulling straight from the DB2.
Thank you.
0
 
leason2Author Commented:
Very good, considering he couldn't test being that he couldn't access our database.
0
 
leason2Author Commented:
Thank you very much Abdulmalek_Hamsho!
Nice.
0
 
Abdulmalek_HamshoCommented:
You fast.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.