cascading parameters missing some contacts 1

[kims212]

I am attempting to create a cascading parameter that allows the user to select a name from a database and then print a transcript of the courses that person has taken. the courses are in a different table which I will eventually link but for now I'm keeping it very simple.

I use a command to select records from the db; it selects only the first initial of the last name, the first three letters of the last name, the student full name and the course code of the course taken.

with a cascading parameter using the data above, the user first selects the first initial of the last name and then the first three letters of the last name and then the student name from the list of students for that combination. the command specifies that the student must have taken courses with us since the beginning of 2010.

when I run a simple list of the students with the command to select records from the file, I get all of the records that I expect.

but, using the cascading parameter to select an individual student - first initial, then first three letters and finally student name - for which to print the transcript, some of the records are missing. the first three letters of the last name just aren't in the list.

I use the cascading parameter to overcome the problem of maximum 500 records in a select list which I understand to be a limitation.

I'm thinking that it may still have something to do with the 500 record limitation but I can't figure out what it might be or how to get around it.

As always, thank you for your time and assistance.

kim

 

[lbass]

I think you should post the command. The limit is 1000 elements in the final array resulting from the combination of parameters.

-LB

 

[kims212]

Thanks LB.

here's the command to select the records I need.

as I say, using just the command and listing the records, I get everything that I expect. it's when I set up a cascading parameter that I lose some of the records.

the table contsupp is a multi-purpose table and the fields are used for a lot of different data so the contsupp names aren't too meaningful.

I have a screen print of the list of fields in the select data source, the cascading parameter, the report select criteria and the parameter entry but don't know how to attach it.

select left(dbo.contact1.lastname,1) as first_initial, left(dbo.contact1.lastname,3) as first_three, dbo.contact1.contact, dbo.contsupp.country
FROM dbo.CONTACT1 INNER JOIN
dbo.CONTSUPP AS CONTSUPP_1 ON dbo.CONTACT1.ACCOUNTNO = CONTSUPP_1.ACCOUNTNO RIGHT OUTER JOIN
dbo.CONTACT1 AS COURSE INNER JOIN
dbo.CONTACT2 AS Course_Info ON COURSE.ACCOUNTNO = Course_Info.ACCOUNTNO RIGHT OUTER JOIN
dbo.CONTSUPP ON COURSE.KEY3 = dbo.CONTSUPP.CONTSUPREF ON dbo.CONTACT1.ACCOUNTNO = dbo.CONTSUPP.ACCOUNTNO
WHERE (dbo.CONTSUPP.RECTYPE = 'P') AND (dbo.CONTSUPP.CONTACT = 'Course Name') AND (COURSE.KEY1 = 'Course Info') AND
(CONTSUPP_1.CONTACT = 'E-mail Address') AND (CONTSUPP_1.ZIP LIKE '_1%') and dbo.CONTSUPP.CONTSUPREF like 'TRG%' AND (Course_Info.UCRSTART >= ' 01/01/2010')

thank you.
k.

 

[lbass]

I don't see a problem with this.

Are the parameters set to accept multiple values? What is the total set of possible names? If the result set would be more than 1000, the higher level parameters will be limited (e.g., some three-letter combos won't appear on the picklist).

-LB

 

[kims212]

Thanks LB

I have the multiple options set to false but I tried it with the multiple values option, and I realize that I don't want multiple values; I want them to pick one last name first initial, then one from the list of first three letters of the last name that match the last name first initial selected and then one student from the list of the students with last names begining with the first three letters that they picked. I don't want them to pick more than one student.

The result set in any one of the parameters wouldn't be more than 1000. First initial, maximum 25 - no last names beginning with X, first three letters of the last name for first initial selected - maximum 200, maximum students w/last name beg w/first three letters selected - 22.

I have attached a copy of the parameter setup screens just in case you can see something that I've done incorrectly there.

Thanks
kim

 

[lbass]

In retrospect, looking more closely at your query, I realize I don't actually understand your joins. I never have found occasion to use a right join, so wonder what your logic is here. Not sure how the usual rules for left joins would apply here, but if you had used left joins, I would have said that:

1) any selection on a right-hand table would undo the left join (make it act like an inner join)--so you might want to test to see whether removing the selection criteria affects the values that appear. If this does have an impact, then you could try moving the selection criteria from the "Where" clause to the "From" clause in order to maintain the desired joins;

2) Any joins to the right of a left join must also be left join (and you appear to have inner joined tables following a right joined table).

-LB

 

[kims212]

thanks again lb

truthfully, the select statement predated me and I just copied it into my report as I assumed it was working.

so, I rewrote it and put all of the selection criteria in the from statements; this worked fine but still gave me the same records from the file that the other select statement gave me.

all of the records are selected from the file and if I list the records without any report selection criteria, all of the records I want are there. It's when I use the report selection that I run into the problem, not all of the records show up in the pick list, even when I just set up a parameter for the first letter of the last name - of which there are only 25. which makes me think it must have something to do with the 1000 record limitation but don't know how to figure that out. Can that limit be increased?

just an fyi, here is my new select statement:

select distinct left(dbo.contact1.lastname,1) as first_initial, left(dbo.contact1.lastname,3), dbo.contact1.contact
FROM dbo.CONTACT1
JOIN dbo.CONTSUPP AS CONTSUPP_1 ON dbo.CONTACT1.ACCOUNTNO = CONTSUPP_1.ACCOUNTNO and
CONTSUPP_1.CONTACT = 'E-mail Address' AND (CONTSUPP_1.ZIP LIKE '_1%')
JOIN dbo.CONTACT1 AS COURSE on course.key1='Course Info'
join dbo.CONTACT2 AS Course_Info ON COURSE.ACCOUNTNO = Course_Info.ACCOUNTNO AND (Course_Info.UCRSTART >= ' 01/01/2010')
join dbo.CONTSUPP ON COURSE.KEY3 = dbo.CONTSUPP.CONTSUPREF and dbo.CONTACT1.ACCOUNTNO = dbo.CONTSUPP.ACCOUNTNO
and dbo.CONTSUPP.CONTSUPREF like 'TRG%'

thanks
kim

 

[lbass]

I think it has to do with joins and nulls--either a linking field is null or a field that you are selecting on is sometimes null and an equal join is then limiting fields in the lead table. Try changing all joins to left outer joins FROM contact1 and see if that makes a difference.

-LB

 

[kims212]

I don't think that I can do that as not all tables link to contact1; some of the tables link to a table that links to contact1.
i.e. contact1 contains a contact name and an account number which can be linked to the registration file by account number, but the course file containing the course name and number can't be linked to the contact1 file, it can be linked to only the registration file.

I tried a left outer join for the contsupp_1 file but got all of the records in the database.

I don't think that the linking fields being null is a problem, because I get all of the records that I want when there's no select parameter set up.

thanks
kim

 

[lbass]

I'm sorry--I think I've confused things. I did some testing and the issue is that for the first prompt (options A to Z), the potential results exceed the limit. I did get the cascading prompt to work using the first3 letters and then the name (two levels of prompt), but your database sounds a lot larger.

One way you might get this to work is to use a separate command (unlinked) to populate the pick list, and limit the names in the command by using a parameter in the Where clause:

where Left(dbo.contact1.lastname,3) = '{?FirstName?}'

You would create {?FirstName} right in the command (on the right hand side). Then in the main report, use the dynamic parameter for the full name only and populate the picklist with the names from the command (now limited to names that start with the selected three letters). In the record selection formula you woul d just set the name equal to the new dynamic parameter. This approach would require the user to manually enter the first three letters of the name, and then to select from the resulting list of names.

-LB

 

[kims212]

thanks LB

I confirmed this morning using lists of the contact names selected from the file that it was the 1000 record limit in the drop down list that was causing the problem so at least we do know what the problem is.

Today I thought that I'd try a report to select the last name initial and then a sub report to select the first three of the last name and from that, the contact.

I've worked with it almost all day and can't quite get it working - passing the initial to the subreport and then getting the subreport to list the selected contact - but hopefully taking a break from it for a while I'm sure that it will all make sense.

thanks again
Kim

 

[lbass]

Well, you can increase the limit by making changes in the registry--you just have to feel very comfortable with that, have it backed up, etc. If you do a search, you should find instructions in one of the CR forums for how to go about this.

-LB

 

[kims212]

Thanks LB

I don't think that I'll start playing with the registry, I know very little about it and would be quite anxious at just the thought of doing some real damage.

a question I have, is: is it possible to set up a parameter in a subreport, other than the one or ones that come from the containing report? I've been trying to do this to set up a parameter for selecting the last name from the list of contacts; I would have a parameter in the container report where the user selects the last name initial which it would pass to the subreport which it would use in the command to select only those contacts with that last name initial which I could use to list the names of all of the contacts in the subreport.. I realized that once I have the initial of the last name, I really don't need a parameter for the first three letters of the last name as I don't think that we'd ever have more than 1000 people in the database with the same last name initial.

if it can't be done, then I'm just spinning my wheels but if it can be done, I guess that I'll just have to keep at it cuz I'm not yet quite getting it to work - strange things keep happening and it seems, not all that consistently. I can't seem to find it clearly stated anywhere if you can or can't.

thanks
kim

 

[lbass]

You can accomplish this by setting up a command to use just for the dynamic name prompt (use your other query) for the body of the report. After creating the command, do not link it and do not reference it in the main report--use it only to populate the dynamic parameter picklist:

select dbo.contact1.contact
from dbo.contact1
where left(dbo.contact1.lastname,1)='{?FirstLetter}'

Be sure to create {?FirstLetter} in the command on the right, and enclose it in single quotes in the body of the command.

In the main report, use {command.contact} to populate the parameter list. You will then get an initial prompt where you have to manually enter a letter. Then this will trigger a second prompt that will contain the list of names.

No need for a subreport then.

-LB