Crystal 10 Formula Help

[hld24]

Adding a column on an already existing report and pulling the data from sheet 2 in Excel. Sheet 1 is where the rest of the information comes from.

Group 1 Name = "Reviewed" and "Not Reviewed" (Sheet 1)
Group 2 Name = State (Sheet 1)
Detail Section includes: City, Tax and No Tax. (Sheet 1)
Applnumb (number)(sheet 2)
Action (string) = "Y" or "N" (Sheet 2)

What I want to add to this report is a column that will total all the "Y"' and "N" and group them in the detail section by State, City. For instance in Texas and the city of Austin there were 12 applications of "Y" and 8 applications with "N". On sheet two of this excel spreadsheet I have all the application numbers listed individually by state, city and action classification.

Applnumb State City Action

123456 Texas Austin Y
234567 Texas Austin N
544412 Texas Austin Y
789456 Texas Austin N
894561 Texas Austin N
456852 Texas Austin Y
741852 Texas Austin Y
963574 Texas Austin Y
256984 Texas Austin Y
123995 Texas Austin N
226654 Texas Austin Y
954789 Texas Austin N
335577 Texas Austin Y
115599 Texas Austin N
998866 Texas Austin N
794613 Texas Austin Y
951654 Texas Austin Y
246813 Texas Austin Y
559701 Texas Austin Y
918725 Texas Austin N

I have created this formula:if {Sheet2_.Action}= "Y" then "1"
else if {Sheet2_.Action} = "N" then "1"

Then in the running total section I had the following:
Field to summarize: Sheet2_Applnumb - Summary type: Count
Evaluate: Formula - count({@tax}) where tax is the name of the formula created.
Reset: never
This returns a value of "0" (zero) and takes my report from 6 pages to 811 pages.
I have sheet 1 linked to sheet 2 by state and city. Outer join not enforced.

I am either very close or not even in the ball park. I hope someone can help me out. Sorry for the length of this post.

 

[lbass]

You don't need a running total, and you should have separate formulas for yes and no:

//{@yes}:
if {Sheet2_.Action}= "Y" then 1 //a number result

//{@no}:
if {Sheet2_.Action}= "N" then 1

Place these in the detail section and right click on each and insert a summary (sum, NOT count) at the city group level.

-LB

 

[hld24]

I have done exactly as you suggested yet now I have over 800 pages and the data is incorrect. IE: One city has eight(8) "Y"'s and (0) zero "N"'s. In the SUM of all funded loans I have (22) twenty two. All the city groups are the same as far as exaggerated amounts. It happens as soon as I add the formula's into the detail section.

 

[dgillz]

Are you sure you are summing and not counting the results?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[hld24]

I know, that's what it appears to have happened, but I doubled checked it after reading this. I even "mouse over" the entry in the group footer (city) and it reads
SUM of @funded (number).

 

[lbass]

Does your report involve multiple tables that are linked to each other? It sounds like you have record inflation. Try sorting your report by the sheet_2 applnumb field. Place the sheet two fields in the detail section and see if you are getting repeated values in the applnumb field. If you are, then instead of the formulas, insert a running total {#Yes} that does a count of {table.action}, evaluate using a formula:

(
onfirstrecord or
{sheet2.applnumb} <> previous({sheet2.applnumb})
) and
{table.action} = "Y"

Reset on change of city. Place this in the city group footer. Repeat for {#No}.

-LB

 

[lbass]

Note also that you can suppress the details section if you are only interested in the subtotals.

-LB