Trouble with dynamic cascading parametet-Crystal Reports 1

[abcraj75]

Hi,
can anyone help me with cascading dynamic parameter in Crystal Reports 2008. I am using a cascading parameter (2 prompts in one parameter screen) namely; param1 (string) for "name" field and param2 (datetime) for "date" field. Both the fields, name and date are from the same table "price". In the record selection formula I equated both parameters to the respective fields, i.e.

{?Param1- NAME}={PRICES.NAME}and
{?Param2-DATE}={PRICES._DATE};

The cascading prompt opens with both the prompts, but the problem is the first prompt (param1) displays only 5 record values instead of 20. I also tried to use the formula in it:
Local StringVar a := "";
Local NumberVar i;
For i := 1 To Ubound({?Param1-NAME}) Do
(a := a + ", " + {?Param1-NAME});
a

But now there is a error "invalid string function.

I cannot decipher what the problem is can someone suggest how I could have all the records displayed in the first cascading prompt? Thanks

 

[lbass]

The issue is that the combined number of values exceeds the limit, which I think is around 1000 values, i.e., the combo of name and dates is > 1000, so the first value is limited accordingly. There is a way you can change the registry key that limits this--try searching on this topic for more guidance. Also consider whether it really makes sense to use a parameter for the dates in this case, if there are so many.

-LB

 

[abcraj75]

Hi Ibass,
My assumption was that the first parameter (param1) limits thd records for the parameter (param2). Param1 has only 22 records (LOVs), and there is only one date associated with param2. Whereas for only one record in param1 there is over 1000 records associated in param2. So is this limiting the number of LOVs in param1? If so how could I unlimit this other than changing the registry key. Otherwise could you pls guide how to change the registry key, I could try that too.
Thanks

 

[abcraj75]

Hi could you please suggest how to increase the rowcount in registry key in Crystal Reports 2008, so that I could populate all the values in parameter.
thanks

 

[lbass]

Other than the changing the registry key, I think the only way you could handle this is to use a command as a basis for the dynamic parameters, where you limit the range of possible dates for parm#2. Otherwise, try searching regarding the registry key. I haven't done this myself.

-LB

 

[abcraj75]

Hi Ibass,
what do you mean by to use a command. Could you please explain me better.
thanks

 

[lbass]

First, are the some dates that you don't really need as parameter options? A command is a SQL query where you could add a "where" clause to limit the range of dates returned instead of accessing a database field where all values are represented.

-LB

 

[abcraj75]

Hi Ibass,

How could I use an "add command" to limit my records selected in the first parameter to be passed on to the second parameter. I ahve name field (parameter1) when user selects one name (no multiselect), the second field is price_date (parameter2), which should get filtered based on the parameter1. How and where could I use the add command to link both my parameter. Hope I am clear. Kindly help me clearly as I am new to developing reports.
Thanks in advance.

 

[abcraj75]

Hi Ibass,
I tried first with cascading parameter, because that did not help, I was wondering if there is any method where I could limit dynamically the second parameter records based on the first parameter (without using cascading parameter). Please suggest.
thank you in advance

 

[lbass]

Your problem is with the second parameter, which has too many options. You didn't really answer the question in my last post. It is a little unusual to want a pick list of dates specific to a name. What is the rationale for this? Why not just add a date range parameter as a separate static parameter?

-LB

 

[abcraj75]

Hi Ibass,
Little change in the approach, let me go over again: I have 2 parameters, name and date. Both need to be dynamic. the date parameter record is limited to past 3 year records only and depends on the name parameter.
I mean: Eg:
Name date
abc 01/01/2009 12:00:00AM
asd 01/01/2008 12:00:00AM
azs 01/01/2007 12:00:00AM
axs 01/01/2006 12:00:00AM

so when I select abc in the first parameter, the seond parameter needs to display '01/01/2009' only. because I had trouble with linking directly to table, I used a command prompt with following query:

select distinct tdate, name from
where tdate >add_months(sysdate, -36)

This works for me, but slows the system a lot. Could you help me on the performance. I linked the command to the fields in a table, that I added too. Is my approach correct? Pls suggest...
thanks

 

[lbass]

Do not link the command to the main report tables, and do not reference the command in the main report. ONLY use it to populate the picklists. Then your performance won't be affected in any signtificant way. Just ignore the message in the linking expert.

-LB