dropdowns in parameters do not show all table content

[Former Member]

I want to set up a multiple parameter (on three fields). but one of the fields is behaving weardly. it does not show all the data in the table behind it.

do you know about such behavior?

the values not shown are values starting with a character. the values starting with a number (but that perhaps contain a character further on), are shown.

thanks in advance for your ideas

[Former Member · Accepted solution]

Greetings,

Instead of selecting the tables write a query for the fields. For the problematic field cast the datatype to nvarchar, then you can create the parameter using this field (which you got through writing the command).

ForEx: - I want to select fields u201Ccardcodeu201D(consider this as a problematic field) and u201Ccardnameu201D from table u201Coitmu201D then write the query as u201Cselect cast(cardcode as nvarchar), cardname from oitmu201D, now these two will appear as fields in the report. You can use these for creating parameters as well as report field.If this doesent work then use trim function

Kind regards

Pradeep

Edited by: pradeep hebballi on Sep 26, 2008 12:11 PM

[Former Member · Accepted solution]

My experience is not that it only displays the first 1000 values but that it only examines the first 1000 rows and then displays the unique values from that subset.

Fuskie

Who has had dynamic parameters show only a dozen options from what should have been a couple dozen unique values among thousands of rows...

[Former Member · Accepted solution]

I have modified the registry to make sure that more than 1000 entries are taken into account

also I have tried using the CAST function. My statement is:

select cast(Company as nvarchar), cast(Organization as nvarchar),cast(LeaseContractID as nvarchar)

from SIS_AST_CI_LeaseInfo

this works perfectly within SQL Server, but with Crystal Reports, connected to the database with the Remedy ODBC driver, it produces the following error:

3700:[AR System ODBC Driver]Expected lexical element not found: FROM

[Former Member]

Hi,

I found the issue, but not yet the complete solution.

It seems to be an issue with NULL values.

when I put a value (f.i. x) in the empty fields, then the dropdowns behave almost normal.

one remaining issue that - if i have only one value besides the empty ones - the dropdow shows me three values: Myvalue - x - Myvalue

where i would expect only Myvalue - x

is there a decent workaround for these empty values?

thanks!

Werner

[Former Member]

Hi Werner,

In case there are large number of values, only first 1000 values will be displayed.

For performance reasons, in Crystal Reports XI Release 1 and Release 2 the maximum number of values that are returned for a list of values is set to 1000. If you have a cascading List of Values (for example Country > Region > City), the lowest level (in this case City) will only display a maximum of 1000 values. This means that the higher-level prompts may display far fewer values than you expect. The list of values provides the data for the dynamic parameter list.

If this is the reason check out SAP note no 1213724 and 1218588. These notes explains how to increase the number of values for a parameter.

Hope this helps.

Regards

[Former Member]

Hi Werner

If you define the parameter data type as String, you would be able to get both the numeric and character values in the parameter.

Hope this helps!!

Regards

Sourashree

[Former Member]

Thanks Sourashree, for the quick response.

The parameter was already defined as character. the system does not show the entries beginning with a character, but does show the entries having a character elsewhere.