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
779 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

17 Experts available now in Live!

Get 1:1 Help Now