Crystal Reports Formula

[chomzky]

Hello,
I am preparing a bank discrepancy report that will group the discrancy amounts into groups based on 10.00 dollar increments. It would start at 0 - 10.00 and work its was up to 90.00 - 100.00. I would like to project it over a calendar year and hoped to use a crosstab with the dates(by month) as column headers and the increments as the rows. I want to count teh number of discrepancies.

I am having problems with a formula that will group the discrepancies by dollar value?

Any help is greatly appreciated.

Regards,

C

 

[lbass]

You need to explain what fields you are working with. Do you have a date field for the column? How is the discrepancy determined--is this already a database field or is it a formula? If a formula, please show the contents of the formula (and of any nested formulas).

You also must identify your CR version.

-LB

 

[chomzky]

Hello,

I do have a date field for the columns.

The discrepancy total is made up of two fields. One is called "Over" and one is called "Short." I was hoping to calculate the value and then use it to count the number of dicrepancies for each month in the report.

Thanks again,

C

 

[lbass]

Please show the contents of the discrepancy formula.

-LB

 

[chomzky]

The trouble is I don't know where to start.

I was thinking an "If Then" Statment would work but I am unsure of how to apply a value if the stament is true? If the value is boolean how can you count it afterwords?

 

[lbass]

You have to provide more explanation and show a sample of your data. Would you be looking at discrepancies per accountID for example? Would the discrepancy you want to evaluate be the sum of over and short per acctID per month? Over and short compared to what? We don't have enough info to help yet.

-LB

 

[chomzky]

I think this may help. I am reporting on an Access Database that uses the following fields in one table:
1. EntryID
2. Date
3. Over
4. Short
5. Remarks

I would like to report the number(count)of discrepancies that fall into the following categories.
0-10.00
10.00 - 20.00
20.00 to 30.00
30.00 - 40.00
40.00 - 50.00
50.00 - 100.00
100.00 - 250.00

I wopuld also like to project the count over a calendar year, grouped by month. The months would be used as column headers and the discrepancy groups as rows.

Thanks again for teh assistance,

C

 

[lbass]

I'm guessing that in each row either over or short is null. I would start by setting the report options to convert nulls to default values. Then create a formula:

select round({table.over}+{table.short})
case 0 : "0"
case 1 to 10 : "1 to 10"
case 11 to 20: "11 to 20" //etc.

Insert a crosstab and add this formula as your row field. Click on group options and use specified order to order the rows. Add the date field as the column and click on group options->print on change of month. Then add
distinctcount of entryID as your summary field.

-LB

 

[chomzky]

The formula is working great. Thanks so much for your help.

C