Solved

Profit and Loss Statement in Crystal Reports

Posted on 2012-03-12
44
1,558 Views
Last Modified: 2012-03-21
I'm trying to create a P&L statement in CR.  We have approximately 40 GL accounts.  Sometimes multiple GL accounts will be used to give a total for an account description on the P&L.  For instance "Materials" may consist of "Purchases", "Inventory Variance" and "Raw Material"  I created formulas that will total the account descriptions but now I'm not sure how do I display it on the report.  If I put @Materials on the Details section and refresh the report, it will show zeros or empty spaces if I select to 'suppress if zero' on the report for each of the 40 GL accounts.  All I want to see is just one line of information.
0
Comment
Question by:ITworks
  • 19
  • 18
  • 7
44 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are you dealing with multiple records for the data?

Can you show a picture of what you are getting and what you want?

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I've attached a screen shot.  Maybe I'm not going about this the right way.  I'm open to suggestions.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I don't see the attachment

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Sorry....here it is.
ScreenShot1.docx
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Have you tried the combination of
Suppress the field  IF 0
Suppress Blank Section in the SECTION EXPERT

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I had not. That worked fine for the first item (formula field) but not when I have a formula for an item that has multiple GL accounts.  For instance, "Materials" has one GL account that shows 516,829 and a second GL several rows down that shows -2547, and a 3rd row that shows 3285, etc.   I want it to show just one row with the sum of all or in this case 517027
ScreenShot2.docx
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try this
Add a group on the Materials field
RIght click the field
Click INSERT --> SUMMARY
Set for SUM
Put it in the group footer
CLick OK
Drag the summary to the group header
You can then suppress the details

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Grouping might be one way to handle it.  If you actually have a description/name field for those accounts with "Materials", "Freight In", etc. and can just group by that, great.  If not, can you define a group of one or more accounts for each line?  For example, "Sales" includes accounts A, B, and C; "Materials" includes accounts D, E and F; "Freight In" includes accounts G, H and I; and so on.  If so, then you can create a formula and group on that.

 If you just group on those names (whether they come from a field or a formula), you're probably not going to get them in the order shown in your example.  If that's a problem, I think you could handle it by assigning numbers or letters to each group (instead of just using the name), to get them to sort in the desired order.  One potential drawback to that would be that the group tree would show those number/letter names, instead of the longer names, but I doubt that you'd really be using the group tree for a report like this anyway (since it looks like it's all going to fit on one page).

 If you're using the account number to define the groups, do any of the groups overlap?  For example, the "Sales" line includes accounts that are also included in the "Packaging" line.  Probably not, but I wanted to check.

 For your total sales, cost and profit, you may be able to use summaries, or you may have to use formulas.

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
mlcc-
I'm sorry, but I don't understand the first step..."add a group on the materials field"  Could you please elaborate on this.

Thanks,

Sue
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you have the report grouped on any field?

My comment was to add a group to the report using the field tat hjas MATERIALS as a value

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I don't have any group yet.  I don't really have much of anything.  The database has the Account number field and balance field.  I then created formulas like:  if{account.number} like ["Raw Material", "WIP Material"] then {balances} else 0  
I thought I would then be able to enter the formulas into the detail section of the report but that didn't work.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
WHat didn't work?

In the above formula I would use IN in place of LIKE

To add a group to the report
(standalone versions)
Open the report
Click INSERT --> GROUP
Choose field (in this case the field {account.number}
Click OK
This will group all the records with the same value into one area of the report allowing you to do summaries.

If you need several values to group together you can write a formula like

if {account.number} IN ["Raw Material", "WIP Material"] then
    "Materials"
Else if {account.number} = "Final Product" then
     "Product"
Else
    "Others"

That formula can be used to group similar records together

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
When I try to enter the forumula you suggested...

  if {account.number} IN ["Raw Material", "WIP Material"] then
    "Materials"
Else if {account.number} = "Final Product" then
     "Product"
Else
    "Others"



......I get an error A number, currency amount, boolean, date, time, date-time, or string is expected here.  The cursor stops right at the quotation mark for the second statement.  My actual formula is:

if{balances.account_no} in ["40200601","40600601","41000601", "50100601", "51600601"] then
"Materials"


Else if{balances.account_no} in ["60300601", "62100601", "62200601"] then
 “Auto Expense Mileage”


Else
“Others”
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Where are you entering the code?

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
In formula workshop - formula editor
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are the account numbers strings or numbers?
If  numbers delete the " " in the set

Can you provide a screen shot of the error?

Are you using Crystal or Basic syntax?
The formula is in Crystal syntax

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I figured it out by accident.  I had copied and pasted this from word into the formula field and this sounds strange but my quotation marks were different looking (more on a slant) that the first argument in the formula.  After I changed that, it worked.  Strange!  I sent a screen shot just so you could see it.
Screenshot-3.xlsx
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Yes, WOrd uses a LEFT quote and RIGHT quote.  I have seen that cause issues but generally don't think of it beause in most systems they look the same.

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
OK....I have all of that working now I want to take it a step further.  I want to be able to group the new formulas into another group.  I want to take @Materials, @Freight In, @Packaging, etc and put them in a group called "COST OF GOODS SOLD".  Then @Sales, @Sales Discounts, etc and call them "INCOME".   Then I want to be abe to put them in a particular order.  "INCOME", "COST OF GOODS SOLD", etc.  

What is the best approach for that?


Thanks,

Sue
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Use a similar formula

If  {balances.account_no} in ["40200601","40600601","41000601", "50100601", "51600601"] then
     "Cost of Goods Sold"
Else If   etc


You can then insert a group based on the formula and set the sort order to SPECIFIED ORDER
WHen you select that you can put the values in any order you want.

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Acccount No. 40200601 is called Cost of Sales in the database, 50100601 is called Purchases in the database, but both of those are part of  "Materials" .  And Materials is part of Cost of Goods Sold.  So how do I get it to be labeled "Materials" and be under the "Cost of Goods Sold" group?

How do I change the number in Sales to a positive number?  


Thanks,

Sue
Screenshot-4.docx
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Use 2 formulas
Group1 formula
If  {balances.account_no} in ['40100601', '40500601'] then
    'Income'

Else If  {balances.account_no} in ['40200601', ... '50800601'] then
    'Cost of Goods Sold'

Group2 formula
If  {balances.account_no} = 40100601'  then
    'Sales'
Else If {balances.account_no} = 40500601'  then
    'Sales Discounts'
Else If  {balances.account_no} = '40200601'  then
    'Materials'
Else ETC

mlmcc
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:James0628
Comment Utility
To make the sales positive, create a formula and use that in the report instead of your amount field.  For example:

If  {balances.account_no} = '40100601'  then
  0 - {amount field}
else
  {amount field}


 If you've got a formula that identifies the "Sales" accounts, you could check that formula instead of the account number.  For example:

If  {@account_type} = "Sales" then
  0 - {amount field}
else
  {amount field}


 The advantage to that is that if the accounts that are included in "Sales" change, you don't need to make any changes to this formula.  Just change @account_type.

 If you want the sales to _always_ be positive, you could use Abs ({amount field}) instead of   0 - {amount field}, but you probably don't want that.


 Also, in an earlier post, you mentioned @Materials, @Freight In, @Packaging, @Sales and @Sales Discounts, which seems to indicate a separate formula for each account type.  The idea was to create a single formula that would produce the type ("Sales", "Materials", etc.), and group the report on that, to create a summary line for each type.  And then for the higher level group (eg. "Cost of Goods Sold"), create a second formula that groups those accounts together.

 FWIW, you could use Specified Order to sort the higher level group, but I always found it a pain.  Any time I wanted to view/change the order, CR would "lock up" for a short time (presumably accessing the db to get the values for the field(s) used to define the group).  So, I would handle the sorting some other way.

 One option would be to have your group formula just produce simple numbers or letters instead (as I described in an earlier post).  For example, if "Cost of Goods Sold" is the third major group, have the formula produce 3.  And then you could have a formula that produced the group name on the report, based on that number.

 But I think I'd actually just put spaces in front of the group names to sort them.  Let's say that you had 4 groups, A, B, C and D, but you wanted them to be listed as C, B, A, D.  I'd make the group names something like "   C" (3 spaces), "  B" (2 spaces), " A" (1 space) and "D" (no spaces).  Those spaces might not even be really noticeable on the report, but if they were a problem, you could just create a formula that used TrimLeft on the group name to remove the spaces, and put that formula on the report, instead of the original group formula.

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
THis is going to sound like a really stupid question...but....where do I put the formula to change the sales to a positive number?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You create a new formula that changes it to a positive as above.  YOu then use the formula instead of the sales field

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Doh!   That was stupid of me.

OK.....so is there a way to write a formula to do some basic math using the "NAMES" that we created in a formula earlier?  

For instance, what's the easiest way to subtract the "Cogs of Goods Sold" from the "Sales"?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
There are different ways you could handle it.  One example:

 Say you've got a formula that produces the string "Costs of Goods Sold" for the appropriate accounts, and a formula (it could be the same formula or a different one) that produces "Sales" for those accounts.

 Create a formula (let's call it cogs_amount) like this:

if {@first formula above) = "Cost of Good Sold" then
  {amount field}

 A Sum of that formula should give you the total from the CoGS accounts.

 Create a formula (let's call it sales_amount) like this:

if {@second formula above) = "Sales" then
  0 - {amount field}

 A Sum of that formula should give you the total from the Sales accounts.

 Then create a formula (call it whatever you like) like this:

Sum ({@sales_amount}) - Sum ({@cogs_amount})


 Like I said, there are other ways you could handle it, but that seems pretty straightforward (and I'm pretty sure it will work :-).

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I thought that I had the sorting part working but when I looked at it in detail it is not.  My first group is called @Header Names.  I have it sorted by a specified order.  They are: Income, Cost of Goods Sold, Selling Expenses, Operating Expenses, and Other Income.   For the others, I have tried to Discard them, Leave in their own group and currently I have them put together with the name:  Where Do I Belong?  My second Group is Called @Named Accounts.  Currently, I have it sorted by original order.  I have a formula called Header Names, which I will attach a copy of.  I also have a formula called Named Accounts, which I will attach a copy of.  When I refresh my report, I'm getting the first two header names "Income" and "Cost of Goods Sold"  Then it goes haywire.  It shows "Other Income" and then a bunch of accounts under the "Where Do I Belong" even though many of them belong in some other upper group like "Selling Expenses" or "Operating Expenses"  I've looked over and over at my formulas and can't find an error.  Any suggestions?
Header-Names-Formula.docx
Named-Accounts-Formula.docx
Header-Names-Specified-Order.docx
Export-of-Report.pdf
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
This would be a lot easier if you could u/l the report.  Then we could see the formulas, the group settings, the report structure, etc., instead of having to look at several files and still not get the complete picture.  It would be even better if you could save the report with some test data and u/l that.

 I've never used "original order", because it generally means that you can't rely on the records being in any specific order, but from a little looking I just did, it seems that "original order" basically means that the records aren't really grouped together.  If you have some records for "Sales" accounts, and then some records for "Materials" accounts, and then some more records for "Sales" accounts, you will apparently get a "Sales" group with the first records, then a "Materials" group, and then another "Sales" group for the second set of "Sales" records.  Try changing the {@Named Accounts} group to Ascending or Descending and see if the accounts fall into groups correctly.  They won't be in the order that you want, but you can worry about that once you get the groups working.

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I attached the report.  I also attached an Excel spreadsheet of what I want the report to resemble.

I didn't have the second group sorted by "Original Order" in the beginning.  I tried all the options and still didn't get what I wanted.

I tried using ascending and descending order and still not working.


Thanks,

Sue
Balances.rpt
Copy-of-Sue--Jan-2012-PL.xls
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
In the Specified Order you  have OPERATING EXPENSES linked to OPERATING EXPENSE but the formula for the group name returns OPERATING EXPENSES

Similarly for OTHER INCOME you have it linked to OTHER INCOME but the formula returns Other Income

I think if you change the first one to add the S and the second either change the formula to return Other Income in uppercae or use the value it returns you will see what you want.

You probably also want to use specified order for the second group

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Ohhhhhh.......I didn't realize that the case mattered (upper case, lower case)  

If in the period that I'm running the report it doesn't have anything for a particular account, will it throw everything off kilter?  Because everything it looking good until I get to an account, in this case, PLATING, that doesn't have anything recorded for this month.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
If there is no data for a given type/group then the report won't show it.

It is possible with some tricks to get it to show.  Do you need it to show?
How often will you change the categories?

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Yes, I would prefer the ones without data to show.  I don't forsee changing the categories once the report is written and working correctly.

I still am having one minor mix up on the sorting.  I'm going to review my formulas again, but currently there are a few of the second group catagories (Named Accounts) that aren't showing up in the proper first group catagory (Header Names)  I just noticed that all of the first groups started on the top of a new page.  Could that have anything to do with my issue?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It shouldn't

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I don't think the case ("OTHER INCOME" vs "Other Income") is an issue in the report that you posted, because you have "Database Server is Case-Insensitive" checked.  That seems to affect any string comparison.  If I create an empty report with that option checked, "ABC" = "abc" returns True.  If I uncheck that option, it's False.

 But, as mlmcc mentioned, in the Specified Order, you did leave off the "S" in "OPERATING EXPENSES".  Same thing for "SELLING EXPENSES".

 Also, you used the same account twice in {@Named Accounts}.  You used 60900601 for both "Freight Out" and "Plating", and since "Freight Out" is first, you'll get that.  Perhaps that's why you weren't showing anything for Plating?

 Of course if you really don't have any records with any of the account numbers for a given category, then you won't see that category on the report (unless you do something to add it), so that could still be an issue.

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I think I corrected all of the mistakes I had but I still am having one issue with the sorting.  "Materials, Freight In, Packaging, and Tooling all are displayed under the "Header" INCOME, but it should have been under COST OF GOODS SOLD.   Then on Entertainment & Promotion and Uniform Rental they should have been under SELLING EXPENSES.  On Administration Fees, Dues and Subscriptions, Employee Lease Expesne, Equipment Rental, Equipment Repair Expsne, Insurance - Group, Insurance - Other, and Legal and Accounting, they should have all been under OPERATING EXPENSES.  When I look at the report, I notice that all of the HEADINGS are on the top of a page.  Also, you can see a little space in between the end of one group to the next group. (Space between Sales Discounts and Materials where Materials should fall under the next HEADER.
Balances.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You seem to have it setup correctly.  Try changing the IN in the formulas to =

Crystal will do it correctly.  I am thinking IN might do some kind of range check

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
Comment Utility
I think I can explain at least part of it.  You have the group 1 name in the page header, and not in the group 1 header, so the name (eg. "INCOME") is only going to be shown at the top of each page.  So, the heading on the first page will be for the first account in the report (an "INCOME" account).  Everything on the second page (if there is a second page) will be under the heading for the first account on the second page.

 Try moving the group 1 name into the group 1 header.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Good catch James.  I didn't notice that.  It makes sense since the ones that he says are in the "wrong" group are the first in the sort order.

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
I tried changing to the = sign and still no good.


That was it, James.


I'm sure I will have additional questions but you two have helped a lot.  I'm going to close this post so you guys can at least get your points.  You've answered many questions on this single post and I greatly appreciate it.

Thanks,

Sue
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
Comment Utility
I didn't expect the IN/= to make a difference.  I just threw it out there in case it did.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.

 FWIW, some of the other posts probably should have been included as part of the solution, although trying to pick the posts, not to mention dividing the points between them, may be a bit of a chore.  And, as mlmcc will no doubt agree, his last post really shouldn't have been included, since it certainly isn't part of the solution.

 James
0
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Yea....I was just trying to divvy up some points and get it closed.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

12 Experts available now in Live!

Get 1:1 Help Now