need help with eliminating records. 1

[musini]

Hi All,

I am getting the data like this in to the report.

accno statuscode arrears income agencycode
1 abc 30 y p
1 abc 30 a p
1 abc 30 b p
2 def 40 h q
2 def 40 j q
2 def 40 q
3 ghi 50 y p
3 ghi 50 p
3 ghi 50 k p
3 ghi 50 l p

but actually I do not want the repeatation of the account number in the report. the only field that changes for a account number is the income field.

My criteria is when ever there is a 'y' for a account just take that record and i do not need any other records.

When there is no 'y' or if there is a null for a particular account I need only one record for those accounts too but witha 'n' symbol.

to brief it. I need only one record for a particular accountin the report. I have groupings on the agencycode and statuscode and income fields. this is a summary report i do not display the account numbers in the report but only the sum of the arrears field according to the groups mentioned above. for one account the only thing changes is the income field.

I used formulas like the supress if previous(val) = (val) and nothing works because of the grouping.

Help would be greatly appreciated. I almost spent aroung 7 hrs on this but could not figure it out. I am new to crystal.

Thanks in advance. If this is not clear please let me know.

 

[dgillz]

What version of crystal? The correct answer to your question varies by version.

In crystal XI, click on report, selection formulas, record, and enter a formula:

{Income}="Y"

This will only return the records with a Y in the income field.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Write a formula {@inc} like this:

if isnull({table.income}) or
{table.income} <> "Y" then "N" else
"Y"

Use this instead of {table.income}.

For your calculations, it looks like the income column is irrelevant--you just want one value per account, so use running totals that calculate on change on group {table.account}.

-LB

 

[musini]

Thanks for the responses.

I am using crystal 9 version. Lb I got what you said I did not try this yet but. from the formula it looks fine but say in the above pattern I have mentioned. for account no 3. there will be four records like this.

3 ghi 50 y p
3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 N p

So three records for 'N' and one record for Y. In that case when I group on status code, income and agencycode and caliculate the arrears. I keep the summary in groupfotter3 this results in repetation of the same account in two different group combitations.

statuscode (group1)

agencycode (group2)

income (group3) (headers)

details (I will put the accno here & calc runntot on change of acc number)

income (fotters) ghi 50 y p
ghi 50 N p

agencycode

statuscoe.


Now even if I use running total with "caliculate on change of accnumber and reset when group3 changes". The account will be caliculated twice because it will be present in two different places, because of the grouping.

Thanks in advance.

 

[lbass]

Don't group on {@inc} then--just sort by it in descending order.

-LB

 

[musini]

LB,

If I do not group on {@inc} how can I have the field iinc in the grouop fotter. I have to display that in the report. I have to display the total number of accounts which have lowincome yes and no in a particular agencycode, status code and then sum the arrears accordingly. If I do not group on {@inc} and put it in the group fotter then how can I get the data for both yes and no. Because for a particular account there can one yes and one no and if there is an yes for particular account I should ignore the no for that account otherwise if an account does not have a yes at all then I should consider that as a no.

Thanks,
M

 

[lbass]

Not sure I'm following, but try changing the reset to on change of account. If that doesn't work, please post sample data that shows your running total accumulating, and identify the section you are displaying.

-LB

 

[musini]

The data what I have similar to what I posted in th first post. By using the formula you praposed. The data now looks like this. Changing all the income fields to 'N' except those with 'Y'.

accno statuscode arrears income(f1) agencycode
1 abc 30 y p
1 abc 30 N p
1 abc 30 N p
2 def 40 N q
2 def 40 N q
2 def 40 N q
3 ghi 50 y p
3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 N p
4 abc 60 N p
Now considering what you said after that, not grouping on the income(f1) and sort desc, but only on statuscode and agencycode. The data will be like this.

statuscode (group1)

agencycode (group2) (headers)



details (I will put the accno here & calc runntot on change of acc number)


agencycode (footers)
abc 90(sum of Arers) y p 2(No. of acts field[accno's 1,4])
def 40 N q 1([acctno's 2])
ghi 50 N p 1([acctno's 3])

statuscode

If you observe it the 'Y' and 'N' are taken randomly for a record in the report since there is no grouping on that field and we are plaving it in the group fotter agencycode. But what I want is something like this.


statuscode (group1)

agencycode (group2) (headers)



details (I will put the accno here & calc runntot on change of acc number)


agencycode (footer)
abc 30(sum of Arers) y p 2 (No. of acts field[accno's 1])
abc 60 N p 1 ([acctno's 4])
def 40 N q 1 ([acctno's 2])
ghi 50 y p 1 ([acctno's 3])

statuscode (fotter)

Since acc no 3 has one y in it, it should be displayed only under y. and accno 4 has no y in it, it should be displayed under N. That is if acc has a y consider only that and do not care about anything and display only under y but if a account does not have a y in all the records present for it then it should be displayes under N.

Thanks.

 

[lbass]

If you want to show the data in the group footer you need to sort ascending on {@inc}. Here is your data reorganized (detail view) after inserting a group on status code, then agency code, then accno. All you need to do is insert a running total that does a sum of arrears, evaluate on change of group accno, reset on change of group accno, and place it and all of your other fields in the accno group footer.

accno statuscode arrears income(f1) agencycode
1 abc 30 N p
1 abc 30 N p
1 abc 30 y p

4 abc 60 N p

2 def 40 N q
2 def 40 N q
2 def 40 N q

3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 y p

-LB

 

[musini]

Lb,

Thanks for the response but my report is not a detail report. I am not showing the account level data in the report. I have to show the number of accounts(the count) in a particular status code, particular agency code, which have low income yes and which have low income no. So when I have to do that. I will group only on status code and agency code and show the total number of accounts with yes and with no and I do not want an account repeated in both yes and no when it has an yes jsut consider only that.

I am sorry but this is what I need and not sure if that is possible.

Thanks,
M

 

[lbass]

I KNOW your report is not a detail report. I was simply showing you how the data would look at the detail level. Please do what I suggested and suppress the detail.

-LB

 

[musini]

LB,

I tried that, I did the same thing what you suggested and everything is fine but there is some problem with the income field. I think we are different tracks. Ok what I want to know is. Is there a way to write a formula making the income field yes in all the records of a particular account when once it sees the yes in any of the records of that account. I saw this statement whilereadingrecords will this help in doing that.

Thanks,
M

 

[lbass]

What is the current problem then?

Anyway, write another formula to display instead of {@inc}:

if maximum({@inc},{table.accno}) = "Y" then "Y" else "N"

-LB

 

[musini]

accno statuscode arrears income(f1) agencycode
1 abc 30 N p
1 abc 30 N p
1 abc 30 y p

4 abc 60 N p

2 abc 40 N p
2 abc 40 N p
2 abc 40 N p

3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 N p
3 ghi 50 y p

LB, You said this is detailed view right, I made small change to the staus code of account 3. Now in the summary it should show like this.

statuscode sum(arrears) income agencycode count
abc 100 N p 2
abc 30 Y p 1
ghi 50 y p 1

Please look at it. I need the number of accounts according to the income too. I did not understand how I could do this with out group on income field and that is the reason why asked for a formula to change all n's to y's for a particular account if it has atleast one y. I thaught of grouping on that formula field but it does not allow me to group on the formula field. Please look at the count field how want under y and N of income for status code abc.

Thanks for all the help but I am sorry I could not get it.

I want the count of number of accounts in a particular status code, agency code and in a particular income that is no or yes.

Thanks

 

[lbass]

Okay, I think I see. Create a SQL expression {%inc} in the field explorer like this:

(
select max(`income`)
from table A
where A.`statuscode` = table.`statuscode` and
A.`agencycode` = table.`agencycode` and
A.`acctno` = table.`acctno`
)

Insert groups on status code, agency code, and {%inc}. Place the fields in the {%inc} group footer and then in the running total, use sum of arrears, evaluate on change of account no, reset on change of {%inc}. For the count of accounts, you should be able to just insert a distinctcount at the {%inc} level.

The punctuation of the SQL expression will depend upon your datasource. If you are unsure, go to database->show SQL query and check out how the tables and fields are punctuated there.

-LB

 

[musini]

LB,

Thanks it looks like this will solve my problem. I am using oracle DB and when I used the formula it says missing leftperanthesis or error in expression. I never wrote an sal expression in crystal. Can you help me with it. In the expression I did not understant 'table' and 'A'.

my showsql query under database is like this.

SELECT "TABLE"."ACCTNO", "TABLE"."STATUSCODE", "TABLE"."ARREARS", "TABLE"."INCOME", , "TABLE"."AGENCYCODE"
FROM "SCHEMA"."TABLE" "TABLE"
ORDER BY "TABLE"."ACCTNO".

Thanks for all your help.
M

 

[lbass]

What is the name of your table? Substitute that for table (this assumes you only are using one table). Then the sql expression would look like this. Be sure to add the parens.

(
select max("INCOME")
from "SCHEMA"."TABLE" A
where A."STATUSCODE" = "TABLE"."STATUSCODE" and
A."AGENCYCODE" = "TABLE"."AGENCYCODE" and
A."ACCTNO" = "TABLE"."ACCTNO"
)

The A is an alias table--leave that as is. Just substitute your actual schema, table, and field names.

-LB

 

[musini]

Thank You Very Much LB. It did work.

Thanks a Lot.

M.