Xi and Dynamic Parameters

[befine]

I've been working with the new dynamic parameter/prompts in CRXi. It works pretty good, but have run into a block and I hope someone can steer me in the right direction.

Access db with 3 fields: ID(autonum), CodeNum, CodeName

I created a dynamic parameter using CodeNum. When new values are entered into the db and then I run the report, the values show up. This is the great part. Finally.

The block comes when the db has more than 1,000 values. (Before you jump in. Yes 1,000+ values is too many to have in a parameter, but it's what the client wants).
- I modified the db to have 2,000 values.
- Then created a sql command (sqlPrompt):
Select ID, CodeNum, CodeName From tblName
- The command could be linked or not to the tbl. CR help says it doesn't have to be linked as long no fields from the command are used in the rpt.
- Created a new dynamic parameter using the command.
- Select expert: tblName.CodeNum = {?sqlPrompt}
- Drop tblName.CodeNum into Detail section
- Refresh
- The prompt dialog that comes only has the first 1,000 values listed.

Is there a way to display ALL the values in the table? Am I missing something here?

Thanks

PS The resulting rpt sql statement has: tblName.CodeNum = 1 or tblName.CodeNum = 2 or... etc.

 

[lyu]

Sorry I cannot answer ur question here. But can I have a question for you? I have been playing with CRXI these days with dynamic parameter prompts and it really makes me sick. My database is SQL Server 2000. I have two data sources in my report:
one is a stored procedure which takes a parameter A. I want to provide a dynamic pickup list for the parameter A, so I added the 2nd datasource, a SQL command thru database expert. "select ID, Name from tblName" and then I set parameterA to be dynamic, and its values as tblName.ID, and description as tblName.Name. So, the result should be a dropdown list which contains Name for user to choose.

Now whenever I refresh my report, it always prompts the parameterA twice. The first time it doesn't contain the dropdown list, i.e. I still need to input ID(number), but the 2nd time it contains the dropdown list.

Do you or any guys here have any ideas of that? I desperatedly need to make this work....

Thanks