Avatar of Brandon
BrandonFlag for United States of America

asked on 

Dealing with large customer lists as a parameter in Crystal

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!
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken
Avatar of Mike McCracken
Mike McCracken

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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.
Avatar of David11Norman
David11Norman

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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. User generated image
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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
SOLUTION
Avatar of James0628
James0628

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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
Avatar of James0628
James0628

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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.
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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
Avatar of James0628
James0628

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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
Avatar of James0628
James0628

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
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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..
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Brandon
Brandon
Flag of United States of America image

ASKER

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
Crystal Reports
Crystal Reports

Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.

36K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo