Dealing with large customer lists as a parameter in Crystal

Brandon
Brandon used Ask the Experts™
on
Hello,
Im trying edit a report that uses dynamic prompts for the selection criteria..
I want to be able to select one customer at a time and be able to show all there orders over time. Seems relatively simple but the issue is that the client list is huge, say a couple thousand..

So what happens is crystal builds the prompt and shows 5 sections , all of the A;s and a few B's

I think I could do a wildcard type situation but im stumped on the syntax..

So if a user searched for a customer smith maybe they could enter a smi* or sm*?  only issue is Im not sure how to do this..

Name field is a string and the DB is mssql..


Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Crystal allows the user to type and it will move to that letter.

Is the issue that not all values are displayed in the list?

mlmcc
BrandonCEO - Janitor

Author

Commented:
Well IM  using a dynamic prompt, which is basically showing just the a s and some b's.. it doesnt give me an option to go any other letter

It gives me an option to go to the next batch 1 thru 5 but those only go to B.. strange.
Have you tried using a cascading prompt, with the top level selecting a letter and the bottom level returning all clients starting with that letter?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't follow what you mean about Crystal building the prompt and showing 5 sections.  

mlmcc
BrandonCEO - Janitor

Author

Commented:
Maybe a screen shot will help.. this is functionality ive never seen in Crystal before.. using 2008 which is a newer version to me. I know dealing with large numbers of customer names is cumbersome and have always avoided it if I could, I was just hoping there was a way to deal with this to give the report users what they want. screen shot prompting
BrandonCEO - Janitor

Author

Commented:
any ideas? Im stumped on this one
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't have a CR2008 installation readily available and I also don't have a customer list of any real size.
mlmcc
I haven't used CR 2008, but I seem to recall reading about a limit on the number of rows returned by some queries in some version of CR - Maybe CR XI.  I think the limit was on either something like the "Browse Data" function, or _maybe_ a dynamic prompt.  What I remember was that there was a registry entry that could be changed to increase that number.  It's a longshot, since I'm not sure if it was a dynamic prompt or something else, or which version of CR it was, but maybe that will help.

 mlmcc, do you know what I'm talking about?  (because clearly I don't :-)

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There is a limit on the number of rows returned for a dynamic parameter.  It is controlled by a registry setting so it can be changed.  The default limit is 1000.

Personally I wouldn't want my clients to have to choose from a list of even 1000 elements unless the prompted supported type ahead for selection.  Just think of the fun if you only knew the last name and the list was of full names.

Shockacon - What is the purpose of the sections?

mlmcc
If you mean the 5 sections that he referred to in the dropdown list, I got the impression that CR 2008 was just doing that on its own.  It seemed that it was getting a list of values for the dynamic prompt and broke that up into 5 smaller lists, which are referenced by choosing the different "sections".

 James
BrandonCEO - Janitor

Author

Commented:
Right. The sections are the A's of the customer list. Which there are over a 1000. Need to be able to select one at a time n view orders per last name. Either drop down or entering name.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
BrandonCEO - Janitor

Author

Commented:
Well this works to a point, but takes way to long to load and is not practical if I had to do the same thing on other machine registries.

What about creating a cascading prompt, that say for example they entered a letter of the last name say

H - would start the H last names
Z - would start the Z last names

etc etc..

then they could drill into the H's and find Henry and use that as the param?

thanks again,

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You could try adding a formula to the report

Left({NameFIeld},1)

Base the parameter on that then add the second selection on the namefield.

mlmcc
BrandonCEO - Janitor

Author

Commented:
OK I used the above and went out and created a command that brings back the customer ID (Key) and the first letter of there last name (Letter_Command), with the intention of using this as a cascading parameter.

So for example.. when Z  is selected all the customer names with Z should pop up for selection(on the lower cascade).. ZING, ZEN, etc etc.

So I want to ultimately create a dynamic cascading prompt and I believe it to now be possible.
Which would be as follows

letter_command.letter -  upper level cascade
       customer_table.name  - lower level cascade


In theory this will break up the customers in 26 groups A - Z and would be able to select per individual  customer.. Am I making any sense here? lol
 


Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What kind of COMMAND?
If you mean a Crystal command, then dynamic parameters are out.  You cannot use dynamic parameters with a command as the data source.

mlmcc
I don't know.  Does the first level (using Left({NameFIeld},1)) really need to by dynamic?  Why not just create a parameter with preset values, A - Z?  That would save you a query.  If someone selected a letter that you didn't actually have any names for, they'd presumably just get nothing.

 Of course if you could have names that start with something else, like 0 - 9, or some other character, including a space, that could be a problem.  One thought there is maybe have an "Anything else" option for the first parameter, and the cascading second parameter would look for any names that did not start with A - Z if you selected that.  I haven't used dynamic or cascading parameters (I'm using CR 10 and it doesn't have those), so I don't know if that's possible.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The first letter has to be related to the record.

For instance if you had an address book database and wanted to be able to select say state then city the fields have to coe from the same recordset or a formula that can be used to relate the 2.  So if you had a states table and a cities table you couldn't relate the city to the state and there wouldn't be the cascading effect.

My method of a formula gets the first letter of the name for each record.

mlmcc
BrandonCEO - Janitor

Author

Commented:
I agree doesn't need to be dynamic. BUT I havent been able to create a static or dynamic cascading prompt out of a formula.

 Hence the sql command route where I did -

select * from customer table select IEN, Left(va_cust.name,1) as' letter'
from va_cust

this seemed to start working but mlmcc said this isnt possible to use a command so im doubting myself on it a bit.


Im looking for the easiest or best way to do it and im open to ideas.

Like I said I wasnt able to create a param out of a formula so im not sure how to proceeed at this point..
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
That is what I have read here.  I haven't used dyanamic parameters much.  Most of the reports I write for clients are for use with pre CR XI systems.  I can author the in CR XI and CR9 and later can read them.

I just tried.  You seem to be correct that you can't use the formula in the parameter which makes sense since when the parameters are prompted for, the report really isn't open so the formulas aren't available

I was able to build one using a SQL command.  Mine was

SELECT *, Left(va_cust.name,1) as FirstLetter FROM va_cust

I then created a new dynamic parameter in Crystal

mlmcc
Thanks for the explanation mlmcc.  I thought you just created a query that returned some values for the first parameter, and somehow used the selected value (like a parameter) in a separate query for the second parameter.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There are really 2 kinds of dynamic parameters
1.  A single parameter that draws its values from a field in the query
  Theses are much like the static in CR 10 and before and you simply assign the field to them

2.  Dynamic that can cascade so they really have a sub parameter
   These are assigned the values in the list that would hold values for static parameters
    The first field chosen is assigned to the top level
    To get a dependent parameter another field from the query is chosen

mlmcc
BrandonCEO - Janitor

Author

Commented:
I dont think this is possible without making registry changes,, which I believe would need to be done on each individual work station.. thinking I should close this out..
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You are correct that you will need to make changes to the registry of any machine this runs on to get more than 1000 in a parameter list.
Unless there are more than 1000 in a particular letter you won't need to make changes to a registry.

mlmcc
BrandonCEO - Janitor

Author

Commented:
ok well how would I assign point here? it was solved by finding it wasnt really possible.. split points sound fair?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What makes you feel it isn't really possible?

How many names for each letter?
Would it be possible if they were limited to say A - AL?

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial