Record Selection Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8548
Printed Date: 02 May 2024 at 1:59am
Topic: Record Selection Formula
Posted By: theotherdodge
Subject: Record Selection Formula
Date Posted: 03 Dec 2009 at 7:24am
|
I have a report parameter that the user will enter a company number or multiple company numbers such as: 1,2,5 In the record selection formula, I can't quite get it to work. It works if the user enters only 1 company, but if they enter multiple companies, it returns no records. Here is what I have tried:
(if {?Company}<> 999 then ","+ToText({z_CustomerRankings.JCCo},0,'')+"," in ","+ToText({?Company},0,'')+"," else 1=1)
999 is the default in the parameter as there is no company 999. The field is a number.
Thanks!
|
Replies:
Posted By: DBlank
Date Posted: 03 Dec 2009 at 7:48am
|
I assume you want 999 to be all records and that the original db field is an int the same as your param is. If so the following should work:
{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo}
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 7:51am
| What happens if the user selects multiple companies such as 1,2,5 ? How is that handled? |
Posted By: DBlank
Date Posted: 03 Dec 2009 at 7:55am
|
It should find all records =1 or =2 or =5.
If the enter 999 it will not filter at all hence the full record selection or 'all'.
Test it to make sure both parts work.
Sometimes an array error happens
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 8:21am
|
Here is what I used:
(if {?Company}= 999 or {?Company}={z_CustomerRankings.JCCo} then ","+ToText({z_CustomerRankings.JCCo},0,'')+"," in ","+ToText({?Company},0,'')+"," else 1 = 1)
If I leave the parameter as 999 (which should be all companies) I dont get any results.
If I enter 1 company it gives the total of all companies.
If I enter multiple companies, it does not give me back any results
|
Posted By: DBlank
Date Posted: 03 Dec 2009 at 8:24am
|
Do not use any "if-then" or IN statements just use exactly:
{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo} |
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 8:54am
|
Here is my record formula:
{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo} and {z_CustomerRankings.Mth} >= {?BeginMonth} and {z_CustomerRankings.Mth} <={?EndMonth} for all companies (leaving the parameter to the default) it gives me for all date ranges even though I put in a range of dates for single companies, it works fine
for multiple companies (2,3) I dont get anything
|
Posted By: DBlank
Date Posted: 03 Dec 2009 at 9:04am
|
You have to parenth the OR statement so it applies it as a single condition then add your AND statements. If you don't it will stop evaluating as soon as it hits one OR statement that it can satisfy:
({?Company}= 999 or {?Company}={z_CustomerRankings.JCCo}) and
{z_CustomerRankings.Mth} >= {?BeginMonth} and
{z_CustomerRankings.Mth} <={?EndMonth} |
Posted By: DBlank
Date Posted: 03 Dec 2009 at 9:06am
|
I think there is a problem with your second statement part as well but can't tell as I am not familiar with your data set.
If you get no results using the parneth statement I gave you above then get rid of the date part to test and see if I am correct.
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 12:47pm
|
Ok, I changed the formula to reflect what you have.
For a single company, it works
For all companies (leaving the default 999) it works
For multiple companies (2,3) Nothing returns
Maybe Im not clear on how Im having the user input multiple companies... They actually enter each company followed by a comma just like above. Not sure if that matters...
The Month part is fine. I tried it without it to test the first part...
|
Posted By: DBlank
Date Posted: 03 Dec 2009 at 12:49pm
|
Yes that would be an issue.
Just change your paramter to allow for multiple values and drop the use of the comma.
Will be easier for your end users that way too.
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 12:50pm
| Just a note... I placed the parameter field on my report to see what it was seeing from the user input. If I put in "2,3" the parameter field shows 23 on the report..... |
Posted By: DBlank
Date Posted: 03 Dec 2009 at 1:01pm
|
I think that is because it is a numeric parameter and it will ignore an incorrectly placed comma so 2,3 is really 23.
Again, the easiest and IMO best solution is to NOT use a comma seperated input process but rather edit the parameter to allow for multiple values.
Select Edit on the param and in the Options box at the bottom of the screen there is a "Allow multiple values". Change that to TRUE.
That is how Crystal is designed to to accept multiple values in a parameter so use its features whenever you can.
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 1:08pm
|
Ya I changed that and it seems to work...
But......Now, how can I figure out a formula field to display the customer in the heading.
Here is what I have:
(if {?Company} = 999 then 'All Companies' else
if {?Company} = {z_CustomerRankings.JCCo} then ToText({HQCO.HQCo},0,'')+' - '+{HQCO.Name}) That works fine except it multiple companies are selected. I have no idea how to reference that!
|
Posted By: DBlank
Date Posted: 03 Dec 2009 at 1:22pm
|
Hmmm, not sure abou that.
Usually a Join() would work but I think it has to be a string and not numeric.
Try a new post on this (specifically state you have an numeric array that can have multiple values with the 999 caveat and how you want it dispalyed in your header) and see if someone can help you with it. I think it needs to be an array which I do not do.
|
Posted By: theotherdodge
Date Posted: 03 Dec 2009 at 1:30pm
Thanks for all of your help! 
|