Looping & counting

[rekhatr10]

Version 7.0
Server 2000 sQL server

In the databsase field called Familyid where the family members are grouped togeterh
so that whent the reassignemtn are made they are all assigned to the same Residents.

Eg F01223401
F01223402
F01223403
F01223404

I have grouped them by Familyid by taking off the last 2 number 01,02 which represents the family. I have to count some might have only 01, 02 and some will have only 01,02,03,04. I have to break it down to family size. 1,2 3, 4 or 5. How can I count

 

[synapsevampire]

You were so close to fleshing out this spec., had you simply provided expected output based on the example data, it would have been clear, instead you chose to try to describe.

Are you saying that you need a subgroup count of the 01, 02, 03 per group?

If so, insert another group based on:

mid({table.field},len(trim({table.field}))-2)

Then you can right click fields in the details and select insert-summary->count

And what does family size have to do with this.

Again, example output based on the example data supplied will clarify, and try not to state things like "how can I count", state what you need to count and at which group level.

-k

 

[rekhatr10]

K,
I have to do this based on the Familyid which is e.g. 100701,1000801,100802, 100901,100902,100903
1007- FamSize 1
1008- 1

Example output
FamSize 1 2 3 4 5 6
60 70 35 25 15 10

I hope I have explained or shown you what I want to do . Please guide me as to how to do this.

Thank you
Rekha

 

[lbass]

If SQL expressions are available in CR 7.0, and if the length of the family portion of the code is always the same, you could create the following SQL expression {%countinfam}:

(select count(AKA.`FamilyID`) from Table AKA where
left(AKA.`FamilyID`,7) = left(Table.`FamilyID`,7))

You would substitute your exact table name for "Table" wherever it appears in the expression, and make sure that "FamilyID" is the exact field name. Leave "AKA" as is, since it is an alias table. Change the 7 to whatever number reflects the length of the family portion of the FamilyID.

Then insert a crosstab and use {%countinfam} as the column field. Use no row field. Create a formula {@left7}:

left({Table.FamilyID},7)

Use this as your summary field, and select distinctcount as the summary operation. Place the crosstab in your report header or footer.

-LB

 

[rekhatr10]

LB,


FamilyID will not always be the same length only the last 2 number will be 01 02 etc. Also I made a mistake her 1008 should be 2
e.g. 100701,1000801,100802, 100901,100902,100903
1007- FamSize 1
1008- 2
1009 3
I am sorry, but I don't understand how you are counting. Could you please explain some more.

Thank you
Rekha

 

[lbass]

Go to the field explorer to find the SQL Expression editor. Change the SQL expression to the following:

(select count(AKA.`FamilyID`) from Table AKA where
{fn substring(AKA.`FamilyID`,1,{fn length(AKA.`FamilyID`)}-2)} =
{fn substring(Table.`FamilyID`,1,{fn length(Table.`FamilyID`)}-2)})

Now follow the suggestions in my last post, using the SQL expression as your column field in the crosstab expert.

The SQL expression is a count of family members by family. With the count as a column field in the crosstab, you will be counting the number of families that have the number of family members indicated in a specific column. Let's say the crosstab looks like this:

5 6 8 Total
Total 3 2 4 9

Three families have five members, two have six members, and 4 have 8 members, and there are nine families in all.

-LB

 

[rekhatr10]

LB

Okay will give it a try.
Thank you very much
Rekha