Count formula 1

[angel769]

Hi all newbie here...

I need to count accounts based on 5 different combinations of service types. My report is grouped on the account number and the services are listed in the details section.
The service type is one db field and the combinations would be:
WATER
RECYCLED
RAWATER
WATER,SEWERWA OR SEWERNWA
WATER, SEWERWA OR SEWERNWA, RECYCLED

I can't figure out how to combined the different combinations in a running total.

Please help

 

[lbass]

Create a separate running total per combination, where you do a count of account, evaluate using a formula, e.g.,:

{table.servicetype} in ["WATER","SEWERWA","SEWERNWA","RECYCLED"]

Change the formula in each running total. Reset never.

Place each running total in the report footer and add a text box to label each. Not sure whether you want a distinct count of accounts or a count. If the service type field can be in upper or lower case, then change the formula to:

ucase({table.servicetype}) in ["WATER","SEWERWA","SEWERNWA","RECYCLED"]


-LB

 

[angel769]

THANK YOU LB!!!

I tried this, BUT i forgot to change the count to the acct number and not the service. duh!!!

THANK YOU!!!!!

 

[angel769]

I spoke to soon. It is not distinctly counting accounts with Water and Sewerwa only. It's counting all accounts that have either..?

 

[lbass]

What do you mean? Are you saying that accounts should fall into only ONE of these categories?

-LB

 

[angel769]

Yes accounts should only fall into one of the combinations of services.

Thanks again.

 

[lbass]

First create a formula {@svctype}:

if {table.servicetype} startswith "SEWER" then
"SEWER" else
{table.servicetype}

Then in your running totals, in the evaluation section, use these formulas:

//water:
distinctcount({@svctype},{table.acct}) = 1 and
{table.servicetype} = "WATER"

//recycled:
distinctcount({@svctype},{table.acct}) = 1 and
{table.servicetype} = "RECYCLED"

//rawater:
distinctcount({@svctype},{table.acct}) = 1 and
{table.servicetype} = "RAWATER"

//water and sewer:
distinctcount({@svctype},{table.acct}) = 2

//water, sewer and recycled:
distinctcount({@svctype},{table.acct}) = 3

This assumes that these are the only POSSIBLE combinations.

-LB

 

[angel769]

LB ROCKS!!!! I would have never figured this one out.

THANK YOU

 

[angel769]

LB r u there? I need to added more combinations... and i can't figure it out.
Water, Sewer and Landscape
Water, Recycled
Water, Landscape

Please help

THANK YOU!

 

[lbass]

You could just modify the running total formulas like this:

//{@Water,Recycled}:
distinctcount({@svctype},{table.acct}) = 2 and
minimum({@svctype},{table.acct}) = "Recycled"

Repeat for Water,Landscape and Water, Sewer. You can also use the same concept for the accounts with three distinct values.

-LB

 

[angel769]

Thank you again LB!!!

one more (I hope) what if i had a combination of Sewer, recycled and Landscape? Since i have a combination of Water/Sewer/Recycled and Water/Sewer/Landscape. How would i pull it?

 

[lbass]

distinctcount({@svctype},{table.acct}) = 3 and
maximum({@svctype},{table.acct}) <> "Water"

If you keep adding on variations, you should probably take a different approach. You could concatenate the detail level entries at the group level, using a variable, and then use running totals to count the literal matches.

-LB

 

[angel769]

THANK YOU!!! lb

 

[angel769]

Good morning LB~

Can you give me an example of the concatenate,variable and literal match?

Thank you !

 

[lbass]

//{@Reset} for the account group header:
whileprintingrecords;
stringvar svctypes;
if not inrepeatedgroupheader then
svctypes := "";

Place a formula in the detail section:
//{@accum}:
whileprintingrecords;
stringvar svctypes := svctypes + {table.servicetype} + ", ";

//{@display} for acccount group footer:
whileprintingrecords;
stringvar svctypes;
stringvar y;
stringvar wat;
stringvar watsewrec; //add additional variables here
if len(svctypes) >= 2 then
y := left(svctypes, len(svctypes)-2);
if y = "WATER" then
wat := wat + 1;
if y = "WATER, SEWER, RECYCLED" then
watsewrec := watsewrec + 1; //add in additional type checks here
y //this is so you can see the group result

Then in the report footer, you can check each value with separate formulas like this:

whileprintingrecords;
stringvar wat;

whileprintingrecords;
stringvar watsewrec;

-LB