Dealing with large customer lists as a parameter in Crystal

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

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?

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

mlmcc

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.

any ideas? Im stumped on this one

I don't have a CR2008 installation readily available and I also don't have a customer list of any real size.
mlmcc

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

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.

Here is the link for CR XI on changing the number of values
http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333233313338333533383338%7D.do

It is a similar registry key for CR2008

mlmcc

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,

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

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
 

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

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

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..

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

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

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..

ok well how would I assign point here? it was solved by finding it wasnt really possible.. split points sound fair?

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