Cross tab on a formula or Group on a formula

[bnsSteve]

Hi,

I have data that shows:
Account# - Type of Product

Any account# can have multiple product types in it, so my data will show repeated acct# with different products assigned to that acct#

I've grouped the data by acct# and did a Count on products per acct#.

I have then created a formula (case) that tells me if an acct# has
"less than 5 products", "5-10 prod" etc. Shown here:
select Count ({'Mar_Dec_11_customer_account_'.product_code}, {'Mar_Dec_11_customer_account_'.account#})

case(1) to (4) : "Less than 5"
case(5) to (10) :"Five to Ten"
case(11) to (20) :"Ten to 20"
case(21) to (10000) :"Greater than 20"

What I would like to do, is group this formula so that I can get a count of how many accounts have less than 5 prod, 5-10 prod, etc.

I did another report very similar to this one using age groups and I was able to group and count the age groups, however, I am unable to select the formula for grouping in this example.

Thank you help, its greatly appreciated.
bnssteve.

Running Crystal 14.0.2.364

 

[bnsSteve]

Sorry no need to reply, I made it work. I think I was performing the count on a wrong field in the cross tab.

Thanks,
bnsSteve

Running Crystal 14.0.2.364

 

[bnsSteve]

OK not sure how I did it, but sometimes it allows me to select the formula for grouping and at other times I cannot.

Any idea's on why this is?

Sorry for the back and forth post.

Thanks,
bnssteve

Running Crystal 14.0.2.364

 

[lbass]

Try replacing the summary with a SQL expression:

(
select Count (A.product_code)
from 'Mar_Dec_11_customer_account_' A
where A.account#='Mar_Dec_11_customer_account_'.account#
)

If you have selection criteria, they may need to be built into the expression.

-LB

 

[bnsSteve]

Thanks for the reply LB.

I seem to be getting an error "The ) is missing" highlighting the 'from'

The part after the from 'Mar_Dec_customer_account_' A, should this by my product field?

(
select Count (A.product_code)
from 'Mar_Dec_11_customer_account_' A
where A.account#='Mar_Dec_11_customer_account_'.account#
)






Running Crystal 14.0.2.364

 

[lbass]

No. Is 'Mar_Dec_11_customer_account_' your actual table name? ?What type of database? By CR 14, do you mean CR 2011?

Please copy your SQL query from Database->Show Sql Query into the thread.

-LB

 

[bnsSteve]

Hi LB,

'Mar_Dec_11_customer_account_' is the table name (excel file).
Yep running CR 2011.

Here's the SQL query, I'll paste the formula once more, I needed to change a field that its counting on but its essentially the same. You can think of CID as the same as accunt number.

------------------------------------------------------
Formula:

Select Count ({'Mar_Dec_11_customer_account_'.cid}, {'Mar_Dec_11_customer_account_'.cid})

case(0) to (1) : "Only 1 Product"
case(2) to (2) : "Two Products"
case(3) to (5) : "Three to Five"
case(6) to (10) :"Six to Ten"
case(11) to (20) :"Ten to 20"
case(21) to (10000) :"Greater than 20"
------------------------------------------------------

------------------------------------------------------
SQL Query:

SELECT `'Mar_Dec_11_customer_account_'`.`product_code`, `'Mar_Dec_11_customer_account_'`.`cid`, `'Mar_Dec_11_customer_account_'`.`relationship_code`, `'Mar_Dec_11_customer_account_'`.`ADF_CITY`, `'Mar_Dec_11_customer_account_'`.`ADF_PROV`, `'Mar_Dec_11_customer_account_'`.`ADF_P_CODE`, `'Mar_Dec_11_customer_account_'`.`Account_Type`
FROM `'Mar_Dec_11_customer account$'` `'Mar_Dec_11_customer_account_'`
ORDER BY `'Mar_Dec_11_customer_account_'`.`cid`
------------------------------------------------------

Thanks again for your help,
Steve.




Running Crystal 11 - 14.0.2.364

 

[lbass]

If this is XI, then you should leave out the A in the summary function. Try:

(
select Count (`product_code`)
from `'Mar_Dec_11_customer_account_'` A
where A.`cid`=`'Mar_Dec_11_customer_account_'`.`cid`
)

You should be able to copy the above directly into the field explorer->SQL expression->new with no changes.

-LB

 

[bnsSteve]

Hmm not sure why, keep getting an error saying it cannot locate the object 'Mar_Dec_11_customer_account_' - looks like something wrong on my end. I'll just leave it for now.

I seem to be able to sometimes get it if I undo then redo the
grouping. Not the greatest way but a bit of a work around.

Thanks for the help LB much appreciated.



Running Crystal 11 - 14.0.2.364