Profit and Loss Statement in Crystal Reports

Are you dealing with multiple records for the data?

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

mlmcc

I've attached a screen shot.  Maybe I'm not going about this the right way.  I'm open to suggestions.

I don't see the attachment

mlmcc

Sorry....here it is.
ScreenShot1.docx

Have you tried the combination of
Suppress the field  IF 0
Suppress Blank Section in the SECTION EXPERT

mlmcc

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

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

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

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

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

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.

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

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”

Where are you entering the code?

mlmcc

In formula workshop - formula editor

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

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

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

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

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

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

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

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

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?

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

mlmcc

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"?

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

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

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

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

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

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.

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

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?

It shouldn't

mlmcc

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

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

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

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

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

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

Yea....I was just trying to divvy up some points and get it closed.