Solved

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

Posted on 2011-02-28
7
787 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:leason2
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 35000561
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
 

Author Comment

by:leason2
ID: 35000723
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
 

Author Comment

by:leason2
ID: 35000744
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Accepted Solution

by:
Abdulmalek_Hamsho earned 500 total points
ID: 35003034
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
 

Author Closing Comment

by:leason2
ID: 35003051
Very good, considering he couldn't test being that he couldn't access our database.
0
 

Author Comment

by:leason2
ID: 35003053
Thank you very much Abdulmalek_Hamsho!
Nice.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 35003093
You fast.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now