Crystal Reports Record Selection Formula Select

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Record Selection Formula Select

Author	Message
cruser2 Newbie Joined: 07 Feb 2012 Online Status: Offline Posts: 14	Topic: Record Selection Formula Select Posted: 07 Feb 2012 at 3:41am
	In the Formula Workshop – Record Selection Formula Editor I am trying to include a SQL Select statement to exclude certain customer ids. I’m getting errors when I click the Check formula button. The statement is below, any suggestions? not in (select { STATISTICS.custid} from { STATISTICS} where { STATISTICS.date_reg} >= '27-may-2011' and { STATISTICS.date_reg} < '30-sep-2011')
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 07 Feb 2012 at 4:08am
	you cannot use SQL in the select expert you would use this in a command object as your source.
	IP Logged

cruser2 Newbie Joined: 07 Feb 2012 Online Status: Offline Posts: 14	Posted: 07 Feb 2012 at 4:31am
	OK, thanks. Do you know how I could write this Select in Crystal Reports formula language? thanks.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 07 Feb 2012 at 4:50am
	you would have to use report grouping and group select statement to accomplish it. group on STATISTICS.custid create a formula to flag your date rows called 'flag' (or whatever) if STATISTICS.date_reg in date(2011,5,27) to date(2011,9,30) then 1 sum this per group sum(@flag,statistics.custid) now any group that had a row with any date in that range has a sum>0 Now in the select expert you can change the toggle to use 'group selection' and insert your criteria sum(@flag,statistics.custid)=0 Edited by DBlank - 07 Feb 2012 at 4:51am
	IP Logged

cruser2 Newbie Joined: 07 Feb 2012 Online Status: Offline Posts: 14	Posted: 08 Feb 2012 at 2:12am
	Thanks for the reply, I see what you mean. However I think I will rewrite the report just using an SQL command instead as will be faster to run on the server.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum