This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.
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?
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. 
I don't have a CR2008 installation readily available and I also don't have a customer list of any real size.
mlmcc
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
mlmcc, do you know what I'm talking about? (because clearly I don't :-)
James
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
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
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
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,
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
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
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
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
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
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..
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
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
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
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..
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
Unless there are more than 1000 in a particular letter you won't need to make changes to a registry.
mlmcc
Premium Content
You need an Expert Office subscription to comment.Start Free Trial