Parameter for Sorting

[wanzek]

I have a very strange thing occurring on my report. I have a parameter formula that is supposed to allow you to choose what you want to sort by:

if {?Sort}='N' then
{udDriverDatabase.FullName}
else
if {?Sort}='D' then
{udDriverDatabase.DriverStatus}
else
if {?Sort}='L' then
ToText({udDriverDatabase.LicExpDate},"yyyy-MM-dd")
else
if {?Sort}='P' then
ToText({HRRM.PhysExpireDate},"yyyy-MM-dd")
else
if {?Sort}='R' then
ToText({udDriverDatabase.HRRef},"######")
else
if {?Sort}='J' then
{%Job}
else
''

Here is my select statement:
(if {?Job}<> ' ' then {HRRM.udJobLocation} = {?Job} else 1=1) and
(if {?Include Inactive} = "N" then
{HRRM.ActiveYN} = "Y"
Else 1=1) and
(IF {?Company} <> '' then
(if ","+Totext({HRRM.HRCo},0,'')+"," in ","+{?Company}+","
then 1=1 else 0=1)
Else
1=1) and
(if {?Department}<> ' ' then {HRRM.udGLDepartment1} = {?Department} else 1=1)

When I run the report by some of the sort groups it narrows down my results versus sorting the data. For example if I sort by "D" for Driver Status I get 4 records when I sort by "N" for full name I get 44 pages of results. Why is my sort formula filtering down my report results?

 

[lbass]

First thing I would do is check the records referenced in the sort formula at the detail level to determine whether any of them can be null. If so, add null checks before the field is referenced in the sort formula.

I would also take a look at your tables. If you have a field from a table that is referenced in the sort formula but is not otherwise referenced in the report, it could activate the link and could limit the records returned.

-LB

 

[wanzek]

Thank you for the suggestion. I tried that on one of the fields with this formula:
If IsNull({HRRM.udJobLocation}) or {HRRM.udJobLocation} = "" then
'999' else {HRRM.udJobLocation}

I think updated my sort formula to have:
if {?Sort}='N' then
{udDriverDatabase.FullName}
else
if {?Sort}='D' then
{udDriverDatabase.DriverStatus}

else
if {?Sort}='L' then
ToText({udDriverDatabase.LicExpDate},"yyyy-MM-dd")

else
if {?Sort}='P' then
ToText({HRRM.PhysExpireDate},"yyyy-MM-dd")

else
if {?Sort}='R' then
ToText({udDriverDatabase.HRRef},"######")
else
if {?Sort}='J' then
({@Job})
else
{udDriverDatabase.FullName}

That didn't help at all. Still narrows it down and only 3 records were blank.

Any other ideas for me to try?

 

[lbass]

Please put all the sort fields in the detail section and check to see if any have nulls and then report back.

Or, you could instead check "convert nulls to default values" in the report options. Then see if the problem recurs.

Does the problem occur only with specific fields? Which ones?

Also, you didn't explain whether any of the fields in the sort formula were the only reference to a particular table in the report.

Also, you should always show the content of nested formula ({@job} in your last formula}) or {%job} in your first--especially if the resulting sort is a limiting one.

This is hard to assess without being able to see the report design and run the report.

-LB