Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Next issue - touchy record selection problem 2

Status
Not open for further replies.

Cort

MIS
Apr 16, 2002
154
US
Since my records wont let me group the way I need to I need to exclude a very select set of records.

I'm sure there is a very simple solution to this question but it escapes me at the moment. Been a bad week :)

I need to include all records where [Closed Date] is YTD except those records where ([Payment Type] = "XP" and [Payment Date] = [Reopen Date])

Basically I need to exclude XP payments when the payment date = the reopen date. We has an issue where payments had to be recoded but when that was done all the payment dates were moved into this year so I need to exclude those specific payments to get an accurate count for the year.

Again, thanks in advance
 
You pretty much answered this yourself, add something like this to your record selection criteria:

[Closed Date] in Yeartodate
and
([Payment Type] <> &quot;XP&quot;
and
[Payment Date] <> [Reopen Date])

-k

Sorry if I posted multiple times, I keep getting:

Error Diagnostic Information
An error occurred while attempting to establish a connection to the service.

The most likely cause of this problem is that the service is not currently running. You can use the 'Services' Control Panel to verify that the service is running and to restart it if necessary.

Windows NT error number 2 occurred.

Dontcha just loathe Cold Fusion?

-k kai@informeddatadecisions.com
 
Same problem is causing me to post resolutions after people have resolved them themselves... [ponder] Most annoying.

Anyway, I'm pretty sure that formula is going to dump 'XP' and dates which match, rather than XP records where the dates match.

I think you should probably go with something like:

not({Payment Type} = 'XP' and {Payment Date} = {Reopen Date})
and {Closed Date} in YearToDate

Naith
 
I should probably clarify - there's nothing wrong with the syntax of Synapsevampire's formula, it's just that I tend to find when I utilise parentheses in this context, Crystal thinks it's a good idea to just take them out when you're not looking.

If you take the parentheses out, the report will ignore both matching dates and 'XP' products.

Naith
 
Thanks again for the very prompt assistance. I knew exactly what I wanted to do but getting the syntax right is where I was failing.

My company sent me to an initial CR class and it just scratched the surface of the program. Everything else is OJT and I'm the company &quot;expert&quot; on the program. :)

I only try to ask a question once though and search through the FAQ's first.
 
Naith: I've never had Crystal remove parens, do you have an example of how to reproduce this???

The use of NOT() tends to overcomplicate I think, you're using it to reverse the logic of &quot;=&quot;, why not just use <>?

And your NOT has parens wrapped around what it's checking anyway, will Crystal remove these?

And I appreciate the &quot;clarification&quot;, your initial post suggested that my logic was faulty (not unheard of...), and I hate wasting time verfiying sound code.

-k kai@informeddatadecisions.com
 
If I qualify brackets with an operator that is bracket dependent (like 'not'), Crystal won't remove them, because the formula will fall over.

I just put in your formula in my Select Expert just now, exited the Expert, went back in - and sure enough...disappearing brackets.

Crystal tries to act smarter than it is in the Selection Expert and removes stuff which it thinks are unnecessary - like comments, or allegedly excessive brackets.

I have seen this bug on pretty much all versions up to, and including 8.5.

Naith
 
Are you speaking of brackets or parens?

The originators example used brackets when it should have used braces to enclose the fields (I didn't change it because if the coder is going to just freehand type the code sample rather than test, I'll take the same liberties), and it already used parens to wrap the code.

Anyway, it doesn't remove them here, are you sure you don't have the dreaded Crystal Reports parens virus? ;)

If this can happen here, that would invalidate 90% of my logic.

Please email the report that has this problem, I'd like to see if it fails here.

-k kai@informeddatadecisions.com
 
Ahhh, I think I get it, you're using the select expert, I'm not, nor did I suggest doing so.

It's not very expertly crafted, I never use it.

I'm more concerned than most about what gets passed to the database, so I always hand code the record selection criteria in a VERY specific way.

Occasionally I'll see some anomalies with what's in the record selection and what's in the SQL, but I can always work around it.

Hopefully I'll be back to a Stored Procedure contract soon and not have to worry over these types of buglets...

-k kai@informeddatadecisions.com
 
Yes, I was referring to the Select Expert - like I said in the post.

Regardless, if you've looked at the SQL generated by a report using bracketed subconditions from the record OR select formulas, you'll see that Crystal will not return the XP PaymentType, as it will instinctively pass that to the database, but won't pass the parenthesised date matching criteria.

Naith
 
Yeah, I looked at the SQL, pretty annoying...

I just tested the NOT with a surprising behavior:

Record Selection:

{SOLCONTACT.UTCSTARTTIME} = currentdate
and
not({SOLCONTACT.MENU1} = 'Bob'
and
{SOLCONTACT.LOCALSTARTTIME} >= currentdate)

Builds this SQL where clause (Oracle):

&quot;SOLCONTACT&quot;.&quot;UTCSTARTTIME&quot; >= TO_DATE ('11-07-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
&quot;SOLCONTACT&quot;.&quot;UTCSTARTTIME&quot; <= TO_DATE ('11-07-2002 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
(&quot;SOLCONTACT&quot;.&quot;MENU1&quot; <> 'Bob' OR
&quot;SOLCONTACT&quot;.&quot;LOCALSTARTTIME&quot; < TO_DATE ('11-07-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Note that it does not pass a NOT, rather it reverses the operators... In database programming, you wouldn't approach logic in this way because it adds a construct and hence, will likely degrade performance.

Anyway, it works, and you're right, a NOT is absolutely required in Crystal. I'm slowly learning the table/view way of reporting, lotsa gotchas in Crystal... Can't wait to get back to a real contract that uses Stored Procedures.

Thanks dgillz!

-k kai@informeddatadecisions.com
 
Dgillz? Where? ;-)

Actually, Crystal's assumed the best case SQL there, I think.

The alternative would have been to generate a 'not in' clause instead of the '<> Bob/< date' combination. A 'not in' definitely would have crapped out on performance, because the indices go out the window. ('not exists', which does respect indices, wouldn't be an option for Crystal because of the subquery dependency).

Crystal isn't so bad with tables and views in Oracle - you just need to watch it like a hawk at runtime.

Naith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top