SQL Command not showing all available data

[peterb58]

I have a requirement to allow users to have an ..ALL option in a parameter. I simply select the column from the table and Union it with appropriate text. e.g

Select LW_Name from LW_Workers
Union
Select '..All LW_Workers'

This then puts the '..All' at the top of the list. This has worked well up to now. I then use an If - Then in the Selection formula to pull out the date for ..All or just the selected item.

It seems there is a limitation in the amount of items that can be shown in the resultant parameter list. There are 1365 entries in the table, and I am only being shown 1005.
Is there a way to increase the results shown by the Command.

Previous usage of this has been on smaller tables, so never encountered the problem.

CRXI on Sybase 11.
Running on Windows 7.

thanks

Pete

 

[lbass]

This isn't a limitation of the command (if you run the command separately, you should get all values), but of the picklist. There is a registry key that can be changed to increase this number--try advanced search to find a similar thread.

-LB

 

[peterb58]

Lbass

Thanks for that, found the registry setting and now see all of the list.

 

[peterb58]

Update on this.

I can see everything when I browse the list ( i.e right click on the entry in Field Explorer )

I can only see 1000 entries when I get to the Parameter list.
I have set the MaxNBrowseValues to 1500, but the list is still truncated.

1500 seems to be the max. If I try and set it higher, it seems to reset itself back to 1500.

thanks

 

[lbass]

I'm not sure about this, but I'm wondering whether there are two different registry keys--one for browsing, which you changed, and one for setting the limit on the size of the parameter pick list.

-LB

 

[peterb58]

Lbass

A bit more googling found an instructions to add a new registry key to Database Options.

Adding 'Crystal Reports\DatabaseOptions\LOV' ( List of Values?? ) then creating a Dword - MaxRowSetRecords has cured the problem. At least in CR design mode.
I have posed the question to Origin Software to get ViewerFX to do the same.

My colleagues favorite website StackOverFlow was the first hit.
I do find it incredible that there is any sort of limit of results on a parameter etc and that it is still there after what must be eyars of people finding the limitation and grumbling.


Thank for all the thinking that went on out there.