not conditional 1

[sandora]

CRV11, Access DB
I have an income statement with groups for Revenues, COS, etc. I have the account numbers summaried and the groups summaried and a grand total. I now need to divide the COS summary total by the revenue total for a percentage. I can get Revenues to work with the following formula, but the rest of the groups are seen as zero.

if {@AcctNames} = "Revenues" then Sum ({@Amount}, {@AcctNames}) else 0

How do I make this a value and not a conditional statement?

 

[lbass]

You really need to be more specific with your questions. How many total stores are we talking about? Are the store names in a table?

-LB

 

[sandora]

If you go up a couple of responses to the one where I've defined the parameters, it should show you what is in the parameter. The value is the letter (profitcenter) which is a selection criteria for the report. The description is where the store name is located. There are 4 stores.

 

[synapsevampire]

If you're referring to displaying parameters selected (assuming by the ? in the name), then that's a nuisance in Crystal, you can't display the description from the parameter, you'll need to build out a display formula or better yet create a table for the codes/descriptions.

-k

 

[lbass]

Please answer my questions.

-LB

 

[sandora]

I'm sorry, I thought I did. There are 4 stores and the only place the name of the store is located is in the description in the parameter {?store}.

 

[lbass]

If there are only four, then use the following formula:

numbervar i;
numbervar j := ubound({?Store});
stringvar stores;
stringvar title;

for i := 1 to j do(
stores := stores + (
if {?Store} = "All" then "Oval-S Combined" else
if {?Store} = "****-11" then
"OKC" else
if {?Store} = "****-12" then
"Tulsa" else
if {?Store} = "****-13" then
"Ohio" else
if {?Store} = "****-15" then
"Florida"
)+ ", ");
title := "Income Statement for "+ stores +" for "+
totext(minimum({?Period}),"MM/dd/yyyy")+" to "+
totext(maximum({?Period}),"MM/dd/yyyy")

Substitute whatever is your default for "All" and replace the values in the formula like "****-12", etc., with your exact parameter values.

-LB

 

[sandora]

I've put in the formula you gave me, the result works fine except when I choose all of the stores. The result is:

Income statement for OKC,OKC,OKC,OKC for 04/01/2005 to 05/01/2005

I wouldn't keep after this except I'm going to have to use this in many more reports. If it makes it any easier, I've created a table for the storenames/profit centers and linked it to my current report. I can get that to work again, except for multiple stores. It only picks up the first one.

 

[lbass]

I think you must have changed my formula. If you were using the parameter in the formula, only one instance would appear. Please copy the formula you actually used into this thread. Also, please explain what you are using in the parameter selection list to mean "All".

-LB

 

[sandora]

This is what I have, I replaced the parameter value to what is called for in my parameter value like you said. "All" is in the default field but I'm thinking you must have to define "all" somewhere, but I don't know where.

numbervar i;
numbervar j := ubound({?Store});
stringvar stores;
stringvar title;

for i := 1 to j do(
stores := stores + (
if {?Store} = "All" then "Oval-S Combined" else
if {?Store} = "B" then
"OKC" else
if {?Store} = "C" then
"Tulsa" else
if {?Store} = "D" then
"Ohio" else
if {?Store} = "E" then
"Florida"
)+ ", ");
title := "Income Statement for "+ stores +" for "+
totext(minimum({?Period}),"MM/dd/yyyy")+" to "+
totext(maximum({?Period}),"MM/dd/yyyy")

 

[lbass]

That formula should work (it belongs in the report header or page header), although the final line should be (my error):

title := "Income Statement for "+ left(stores,len(stores)-2) +" for "+
totext(minimum({?Period}),"MM/dd/yyyy")+" to "+
totext(maximum({?Period}),"MM/dd/yyyy")

What are you using in your record selection formula? Please go to report->edit selection formula-record and copy your statement and paste it here.

-LB

 

[sandora]

I have it in the RH, and I changed it to what you have now and the result is the same. Here is the selection criteria:

{GL_Account.GL Account Number} in "4020-11" to "8308-11" and
{GL_Transactions_Archive.Transaction Date} = {?Period} and
{GL_Account.Profit Center} = {?Store}

Do you have to do something else to get "All" in your parameter?

 

[lbass]

Try:

{GL_Account.GL Account Number} in "4020-11" to "8308-11" and
{GL_Transactions_Archive.Transaction Date} = {?Period} and
(
if {?Store} <> "All" then
{GL_Account.Profit Center} = {?Store} else
if {?Store} = "All" then true
)

-LB

 

[sandora]

I made the changes to the selection criteria with no change in the result.

I'll be the first to admit I'm not a programmer, and I'm very new to Crystal, but I think the problem lies in the way the parameter is set up. There is no value for 'All'. There is only the option to pick single or multiple stores.

?store
Name: store Type: String Static
Value field: Profit Center
Value: b Description: OKC
c Tulsa
d Ohio
e Florida
Description only = T
Default = All
Custom = T
Multiple = T
Discrete = T
Range = F

 

[lbass]

In the parameter setup screen, you can add "All" as one of the values. Select insert and type it in (with no quotes).

-LB

 

[sandora]

When I add All to the values and select it, the title works great but all of my data goes away.

 

[lbass]

You have to do that in combination with using the record selection formula I suggested.

-LB

 

[sandora]

Thank you so much for all your help. It works great.