Crystal Reports Help: Record Selection Formula

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Help: Record Selection Formula

Author	Message
Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Topic: Help: Record Selection Formula Posted: 02 Jun 2010 at 4:46am
	Hello FOlks, I am trying to create a report based on 3 parameteres i.e. Clientid,parentclientid and Date. My requirement is if the user enters Null for all 3 parameters I should get all the combinations of Data. How should i do that. I have included this formula in the record selection but looks like it doesnt understand it. Can anyone throw some light on this. Thanks a million If isnull({?Date}) then {Query1_1.Arrivaldate}={@PreviousDay} else {Query1_1.Arrivaldate} = Datetime({?Date}) and If isnull({?MajorClientcode}) then {Query1.Parentclientid} = {Query1.Parentclientid} else {Query1.Parentclientid} = {?MajorClientcode} and If isnull({?Clientid}) then {Query1.Clientid} = {Query1.Clientid} else {Query1.Clientid} = {?Clientid} Edited by Nav522 - 02 Jun 2010 at 5:03am
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Jun 2010 at 5:26am
	First verify that you can run the report with no param value entered. I do not think you can. Usually you have to use an 'All' value to handle these. Here is an example of how you would do the select statement... ( ({?Date}= NULL_equivalent_here and {Query1_1.Arrivaldate}={@PreviousDay}) or {Query1_1.Arrivaldate} = Datetime({?Date}) ) and ({?MajorClientcode}=allvalue or {Query1.Parentclientid} = {?MajorClientcode}) and ({?Clientid}= allvalue or {Query1.Clientid} = {?Clientid}) if you can use NULLS try: ( (isnull({?Date}) and and {Query1_1.Arrivaldate}={@PreviousDay}) or {Query1_1.Arrivaldate} = Datetime({?Date}) ) and (isnull({?MajorClientcode}) or {Query1.Parentclientid} = {?MajorClientcode}) and (isnull({?Clientid}) or {Query1.Clientid} = {?Clientid}) Edited by DBlank - 02 Jun 2010 at 5:53am
	IP Logged

Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Posted: 02 Jun 2010 at 6:33am
	Hey thanks for getting back. Yes you are Correct,I cannot run the report without entering the parameter values. So Itried to use the ALL value logic in the Record Selection. But for some reason i was getting an error saying " Bad Date Format String" near {Query1_1.Arrivaldate} = Datetime({?Date}). Am sure that the Field {Query1_1.Arrivaldate} is in DateTime Format and parameter {?Date} is a String. Any ideas about this error Thanks Edited by Nav522 - 02 Jun 2010 at 6:34am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Jun 2010 at 6:37am
	The ALL is only for strings. You have to choose a date that acts as ALL, like 1-1-1900, for that portion of it. You could add in another param with 2 options that you work into your date logic (all Dates & Select Date) and then use that too but they still have to put something in the date param regardless. Edited by DBlank - 02 Jun 2010 at 6:38am
	IP Logged

Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Posted: 02 Jun 2010 at 7:49am
	I have tried to provide 1-1-1900 in the record selection which acts like ALL. When i run the report Its coming back with an error saying "A DateTime is required". {?Date} = 1-1-1900 and {Query1_1.Arrivaldate}={@PreviousDay} or {?Date} = {Query1_1.Arrivaldate}. And i have a small question. If the parameter is string(Example: {?MajorClientcode}) and the value passing to that parameter is Number.({Query1.Parentclientid}).How should we enter the values for that param while running the report? Should we enter the value within quotations or just the Number i.e 100. {?MajorClientcode} = 'ALL' or totext({Query1.Parentclientid}) = {?MajorClientcode} Appreciate your help. Edited by Nav522 - 02 Jun 2010 at 8:27am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Jun 2010 at 9:02am
	as for the date you have to make it in the correct format, I was just typing in an option to use... {?Date} = date(1900,1,1) and {Query1_1.Arrivaldate}={@PreviousDay} or {?Date} = {Query1_1.Arrivaldate}. I try to avoid converting in the select expert but that being said you do not have to use quotes in the param entry. If you do it will think that is part of the string and not match it. If you are not allowing for multiple clientcodes selection I would convert the other direction (if multiple selections are allowed it will not work due to an array error). You will get better matching results matching numbers thatn strings. If you go with a string a user typing in 1,200 will not match 1200 or you can tell users to use 0 (zero) for 'all' and avoid conversion... {?MajorClientcode} = 'ALL' or {Query1.Parentclientid} = tonumber({?MajorClientcode}) The zero as all option would be changing {?MajorClientcode} to a number type and then: {?MajorClientcode} = '0 or {Query1.Parentclientid} = {?MajorClientcode}
	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