Simple Formula fail if no records 1

[mr2tho]

I have a very simple function for a label.
My parameter: {?LocationCode}

1)
if parameter is seleted to be All it should show "All locations"
(This always works)
2) if a parameter is selected I want to show the description as well for the selected one.
Sometimes there's no records for the selected parameter than the formula shows nothing for some reason. I want to at least show the selected parameter...
Here's what I have:
-------
if {?LocationCode} ="" then
"All Locations"
else if Trim({vwrptAssetByLocation1.LOC_CODE_1})<>"" then
{vwrptAssetByLocation1.LOC_CODE_1} & " - " & {vwrptAssetByLocation1.LOC_DESC}
else
{?LocationCode}
-------

I guess instead of making a comparision with view.field, should I be making a comparision with record Num? i.e. if the report doesn't have any records then show parameter. Is there a formula that shows if the report has any records? Maybe there's something else...

 

[lbass]

Is the formula intended only to display the selected the parameter and the corresponding description? Is the parameter set to allow multiple selections? How many location codes are there? There are two approaches to this, that I know of.

-LB

 

[mr2tho]

I am just using the formula only to show what parameters were selected. It's a simple label.
The parameter allows only one code to be entered.

 

[lbass]

I asked about the number of location codes in order to tell which solution would work best.

If there are only a few possible codes, then you could create the formula like this, where you are hardcoding the results:

if {?LocationCode} = "" then
"All Locations" else
{?LocationCode} + (
if {?LocationCode} = "value1" then " - " + "description1" else
if {?LocationCode} = "value2" then " - " + "description2" else //etc.
)

If there are many location codes to select from, then you should insert a subreport that uses {vwrptAssetByLocation1.LOC_CODE_1} and vwrptAssetByLocation1.LOC_DESC} as detail fields. Create a parameter {?LocationCode} within the subreport and use a record selection formula in the subreport like:

if {?LocationCode} <> "" then
{vwrptAssetByLocation1.LOC_CODE_1} = {?LocationCode} else
if {?LocationCode} <> "" then true

Then link the subreport to the main report on {?LocationCode}. Be sure to use the dropdown to select {?LocationCode} in the lower left for the subreport field, not the default {?pm-?LocationCode}.

Either solution should always populate regardless of whether there are any records in the main report.

-LB

 

[mr2tho]

Thank you very much Lbass and I appologize not giving you the number of locations, too. But I appreciated your answer more this way because now I learned more. What you said makes sense.

I am a little disappointed from Crystal that I have to make a subreport just to show the selected parameter and little information if there's a record. Oh well, I guess this is why I am getting paid, not everything is straight forward.

Thanks very much again and have a good day.

 

[synapsevampire]

Don't be disappointed with Crystal, there are alternatives to this apporach.

Were we to know the database type, your connectivity,your expertise with database programming, and the version of Crystal it would be easier to assist you.

This is a good example of a need for a Stored Procedure, or advanced SQL using a Command object.

Your issue was that Crystal doesn't display data when there isn't any. Not ajn unusual complaint, except with database programmers, they understand that nothing was returned.

I also don't understand WHERE in the report you are attempting to use your formula.

Anyway, if you check with your dba, they can probably work out some simple code to return precisely what you want without embedding the rules in a subreport.

-k