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
796 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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