| Author |
Message |
theotherdodge
Groupie
Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
|
   Topic: Record Selection Formula
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!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}
|
|
IP Logged |
|
theotherdodge
Groupie
Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
|
Posted: 03 Dec 2009 at 7:51am |
|
What happens if the user selects multiple companies such as 1,2,5 ? How is that handled?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
Edited by DBlank - 03 Dec 2009 at 7:55am
|
|
IP Logged |
|
theotherdodge
Groupie
Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}
|
|
IP Logged |
|
theotherdodge
Groupie
Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
Edited by DBlank - 03 Dec 2009 at 9:07am
|
|
IP Logged |
|
theotherdodge
Groupie
Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
|
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...
|
|
IP Logged |
|
|
|
