Creating Conditional Formulas 1

[Guest_imported]

Hi All,

I may not be understanding how conditional formulas work in Crystal. Here's what I want to do.

I have a report that tracks sales of items by employees. The report is grouped by employee ID. Within the group footer of the employee ID, I want to sum the cost field for those items whose Item# is equal to a given value.

For example:
Sum ({Tbl Inventory.Cost},{Tbl Inventory.Item#},"1529&quot

It's not working. The logic in the above formula would work in Access, Excel, etc. Crystal is telling me:
"The summing/running total field could not be created"
&
"A group condition is not allowed here"

There has to be a way to accomplish this. Any Ideas?

 

[synapsevampire]

Hardcoding inventory numbers to sum sounds costly and high maintenance.

Perhaps you can just insert another group for the inventoryitem below the Employee and then just use a summary function in CR to do the work.

Now if you want to limit the item numbers, you can either parameterize the report to prompt for those that you want, or hardcode the item numbers into the record selection criteria to limit the rows returned to only those items, or do neither and get the sums for everything.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Guest_imported]

Actually, I really need to know how to use the conditional formulas.

 

[Ngolem]

Tyana

For this I use a 3 formula sum...I take it that there is no group under EmployeeID and you have a specific item# you want....I think that this is a simplified example but it will serve for instruction

Create first a Parameter for the Item# so we won't hard-code anything

{?ReportItem}

type: string
description: Enter the Item# for the sum


Now create the 3 formulas


@initialization (placed suppressed in the Group1 header)

WhilePrintingRecords;

//just in case there are more than 1 page of items listed
if not InRepeatedGroupHeader then
numberVar ItemTotal := 0;

*******************************

@SummingItem (placed suppressed in the detail section)

WhilePrintingRecords;
numberVar ItemTotal;

if {Tbl Inventory.Item#} = {?ReportItem} then
ItemTotal := ItemTotal + {Tbl Inventory.Cost};

*******************************

@DisplayItemCost (not suppressed placed in Group1 footer)

WhilePrintingRecords;
numberVar ItemTotal;

"The total cost of Item# " + {?ReportItem} + " is " +
totext(ItemTotal,2)


that should do it...this can easily be modified to work with a multiselect parameter too.

Hope this is what you are looking for Jim Broadbent

 

[dgillz]

Tyan,

Lets make this simpler.

What is the third argument to your formula, "1529" supposed to accomplish? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[IdoMillet]

Create a formula (@1529_Cost):
----------------------------------------------
IF {Tbl Inventory.Item#} = "1529" THEN {Tbl Inventory.Cost}
ELSE 0
----------------------------------------------

Now you can sum that formula at any level you want.

Cheers,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Guest_imported]

dgillz,

The "1529" is a specific inventory item that I want to find. It was the criteria for the second part of the formula. This may be the part that I am fuzzy on. Bottom line: I need to understand this particular formula.

 

[Naith]

Boy, it's getting a little crowded in here, but I'll just throw in my 2 pence and get the heck out.

From your formula

Sum ({Tbl Inventory.Cost},{Tbl Inventory.Item#},"1529&quot

it looks like you're only trying to see the total inventory cost where the item number is 1529. If this is not what you're trying to do, you might as well stop reading right here.

If it is, there's two approaches you can take - none of them being like the formula sum(cost,item,'1529'). Crystal just won't play dice with that format.

Instead, if you're happy with hard coding the item number, use:

If {Tbl Inventory.Item#} = '1529'
then {Tbl Inventory.Cost}
else 0

Place that in your detail section, and run a sum on it.

But if you don't want to know about any other Item Numbers, you should use an approach which only brings 1529 items back into the report in the first place, rather than bring everything to the report and then try and sift through the results.

In that case, in your Select Expert, type:

{Tbl Inventory.Item#} = '1529'

then, you can just sum on the database cost field without having to sweat any formulas.

Naith

 

[dgillz]

Tyana,

Summing in crystal is not typically carried out in a formula. You just right click on the field to be summed and insert summary, or insert grand total.

Does that help? Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Ngolem]

dgillz - that is a rather general statement. There are many ways to sum values....nothing wrong with using formulas.... especially where conditions apply Jim Broadbent

 

[dgillz]

Jim,

Its generally true.

Especially since with running total fields were introduced in version 7, you rarely have to write a formula to sum anything. Of course as you pointed out that is always an option.

I remember when I first picked up crystal 5 years ago, I was suprised that you didn't have to write a formula to sum or count something. This is the point I was trying to convey to Tyana.

Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Guest_imported]

Naith,

Allright, I am going to ask two things. First, I already tried what you suggested and it does work. The problem is that I might want to summarize many item numbers. That means a lot of formulas. While I can create group footers for all item numbers, that starts to be a pain when your report has grown by 6 pages to accomodate the extra space.

Access(and the rest of Microsoft) provides a simple formula to this called Dsum, by which you can plug a criteria into the Sum formula and summarize only what you want. Crystal appears to be limited here.

So here are my two questions.

First, how do you recommend accounting for multiple item numbers in the simplest manner.

Second, can you explain the workings of the
Sum(fld,condFld,cond) formula. Specifically, what does the "cond" part want. The help screen listed the following conditions. Are these the "only" variables it will take?

"Boolean Conditions"
any change
change to Yes
change to No
every Yes
every No
next is Yes
next is No

"Date Conditions"
daily
weekly
biweekly
semimonthly
monthly
quarterly
semiannually
annually

"Time Conditions"
by second
by minute
by hour
by AMPM

 

[dgillz]

Tyana,

Try this for me: Right click on the Cost Field, Select Insert Summary, and sum by Item. This will return a summary for each and every item. Is this what you are looking for? Or at least closer? Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

'kay Tyana,

It looks like you've solved your initial summing problem, so I guess what we want to do right here is illustrate the syntax differences between what you're used to, and what Crystal wants.

If I check out your initial statement:
Sum ({Tbl Inventory.Cost},{Tbl Inventory.Item#},"1529&quot
I suppose what you're saying is:
Sum ({This Field}, {Only Where This Other Field}, "equals this value&quot

But how Crystal would read this is:
Sum ({This Field}, {Grouped by This Field}, "reset it at this level&quot
So, for example:
Sum ({cash.amount},{payment.date},"weekly&quot
would return cash totals at weekly payment date levels.

It's not exactly the MS style of doing things, but this summing technique is standard in quite a few other products, like Oracle and Basic.

But having said all that, don't really worry about getting involved with 'cond', and 'condfld' in this instance. It isn't really what you want for conditional accumulations. It's more for grouping styles.

Where you're talking about multiple "1529" scenarios, you would use formulae for each scenario, or have Item# as a subgroup of Employee ID. To work these out with formluas, you can either use the case functionality (if your database supports it), or use If, Then, Else as above for each item.

If you weren't bothered with seperating the totals for the three items, you'd use something like:

If {ITEM#} in ['1529','1530','1531'] then a := a + {COST}

All in all, Crystal can pretty much do whatever you're used to MS doing. Crystal just uses sexier syntax.

Naith

 

[Guest_imported]

Dgillz, Naith,

Thank both of you for your help. I am understanding this a little more. Let's wrap this up then. Basically, despite the other wonderful features of the program, when it comes to conditional formulas Crystal seems extremely weak. They have no equivalent to a SQL aggregate function in Microsoft (a.k.a Dsum, Dcount, etc.) whereby you use a single formula to add up all items within a domain meeting a specified criteria.

I had to use two If statements per item number (one in the detail and one in the group footer) to break them out. That is a lot of work. I was trying to avoid the "Insert, Summary" feature because it didn't provide me the flexibility of indicating where I wanted the formula to appear (group footer, report footer), and because it added to much extra paper to the final product due to the increased number of groups. The answer, I suppose, is that I have to live with it.

As for the conditional formulas in Crystal, am I correct in my assumption that they only work with predefined variables such as "Weekly","by hour", "Change to Yes", etc? And that I must already have the necessary groups in place.

 

[Naith]

You gotta have your group specified as reflected by the sum (daily/weekly/...etc), yeah.

Naith