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
803 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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 …
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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