Formula Syntax for CR 8.5

[CrystalDuck]

I am working with Crystal Reports 8.5 and a SQL server 2000 data source though an ODBC connection.

One of my co workers sent me this formula text and is baffled as to why it's not working. It's a little over my head, (honestly to me, it doesn't look like it would work at all for a few reasons, but...he's a pretty experienced guru, so I'll trust that he knows what he is talking about. *ha* )
...so I thought I'd pick your brains out there...anyone see the problem with this?

[highlight #FF99FF]
select Sum ({@Dollars}, {VW_TB_ACCOUNT.CUSTOMER_ACCT_NO})

case (>00 and <=10000): "Accounts Under $10,000"

case >10000 and <=25000: "Accounts Between $10,000 and $25,000"

default: "All Others"
[/highlight]

Thanks in advance for any help you can provide,
CrystalDuck

 

[lbass]

Try:

select Sum ({@Dollars}, {VW_TB_ACCOUNT.CUSTOMER_ACCT_NO})
case 0 to 9999 : "Accounts Under $10,000"
case 10000 to 25000: "Accounts Between $10,000 and $25,000"
default: "All Others"

-LB

 

[CrystalDuck]

That worked like a champ, thank you so much!!

One more question...is there any way at all to group by this field. I know you can't right off the cuff...but is there a sneaky way around to pull it off?

Thanks a million again!

~CrystalDuck

 

[lbass]

What is the exact content of {@Dollars}?

-LB

 

[CrystalDuck]

({VW_TB_ACCOUNT.TOTAL_SHARE_AMT}*{VW_FUNDSTATION_NAV_POP.NET_ASSET_VALUE})

These are both long numerical values.

 

[lbass]

I was going to suggest converting the sum into a SQL expression because then you could group on it, but I just tested a similar expression, and even though it saved, it was slow and then crashed my report. So...

You could insert a sum on {@Dollars} at the Account group level and then do a topN/group sort on "sum of {@Dollars}". This will order the groups correctly. You could then insert another group header section_b on the Account group and place the select-case formula (let's call it {@Intervals}) in the GH_a section and the other group fields in the GH_b.
Change {@Intervals} to:

whileprintingrecords;
stringvar x;

if instr(x,
select Sum ({@Dollars}, {VW_TB_ACCOUNT.CUSTOMER_ACCT_NO})
case 0 to 9999 : "Accounts Under $10,000"
case 10000 to 25000: "Accounts Between $10,000 and $25,000"
default: "All Others";
) = 0 then
x :=
select Sum ({@Dollars}, {VW_TB_ACCOUNT.CUSTOMER_ACCT_NO})
case 0 to 9999 : "Accounts Under $10,000"
case 10000 to 25000: "Accounts Between $10,000 and $25,000"
default: "All Others"; else
""

Then go into the section expert->GH_a->suppress blank section. This will give you a faux group header by amount intervals.

-LB