Populating dynamic parameters

[Clothahump]

I have a SQL stored procedure which I am basing a Crystal XI r2 report on, using an ODBC connection. The stored procedure returns around 250,000 records for a 3 month period. I'd like to have a dynamic cascading parameter for customer group > customer. In those 250,000 records, I have approximately 8 customer groups and 40 customers, so the final lists are not long.

However, the customer group and customer parameters are not fully populating. How many do populate appears to be determined by how I sort the table in SQL. If I sort by customer, I'll only see one customer group and customer. If I change it to sort by purchase date, I'll see many more, but not those that have only recently purchased. So my conclusion is that Crystal is not searching the entire table to find all possible customer groups and customers.

Is this possible? How many records will Crystal accept to populate the cascading parameters? How do I increase this number? Is there another way to do this?

FYI. I do have a customer table, but there are many inactive customers in this table that may have had purchases during this time frame. If I only include active customers, I'll miss these customers. And if I include all customers, I'll get a much longer list than I'd like for the users to sort through. I really need to pull the customer groups and customers out of the purchase data.

Suggestions?

 

[briangriffin]

This article explains the registry setting to increase the number of values in a dynamic paraemeter. The default is 1000, which would seem to be enough for your scenario.

http://www.sdn.sap.com/irj/scn/go/p...43330333033303331333233313338333533383338}.do

For performance purposes I would suggest creating a command object and basing your parameter on it. You can do something like this:

select customergroup, customerid
from customertable
where customerstatus = 'Active'
or customerid in
(select customerid from salestable where saledate > getdate() - 90)

Don't link the command to any other objects.

This in itself may solve your missing values issue without having to modify the registry.