Help with Formula and string of ID's

[cheerfulskeptic]

Hello,
I have a CR8 report, and am trying to create a running total to sum the count of ID's using an evaluation formula:

The idea is that the Id's will be counted only if the field CUSTID_32 is contained in a string of ID's, which are in the format "ID1","ID2","ID3", etc. (comma delimited string).
The strings are a parameter called {?accountids}
my existing formula is:

Month ({Invoice_Detail.INVDTE_32}) = 6 and Year ({Invoice_Detail.INVDTE_32}) = 2003
and
{Invoice_Detail.PRTNUM_32} startswith "XXXXX-XX"
and {Invoice_Detail.CUSTID_32} in [{?accountids}]

Can I do this? Is there a resolution?
Thanks!

 

[synapsevampire]

If you're entering the parms as "ID1" as opposed to ID1 then you might be ok if you use:

and {Invoice_Detail.CUSTID_32} like "*"+{?accountids}+"*"

You may even have to enter as *<id>*

The idea of saving data as a delimited string in a field is bad, the dba should be publicly flogged prior to being buried up to their earlobes in fire ants.

I'd probably normalize the data within a View first, then you could treat it as intelligent data.

-k

 

[cheerfulskeptic]

*sigh*. its not hte DBA's fault. We're connecting pervasive SQL data to groups in SQL Server, and we cant connect unless there's a subreport that feeds in the account ID's of a group (other solution is to have shared variables), to calculate group totals.

 

[synapsevampire]

The reason why I mentioned that you may need to enter the data with the quotes is that a search for ID1 would succeed if it found ID10...

A bit of a sticky wicket there...

You probably can do this though, just play with the above theories and make sure that you enter the parms including the quotes.

-k