Using a 'compound' field parameter

[kims212]

I have a table that contains course information and another that contains registration details - contact and courses in which she/he is registered.

The courses are distinct by course code by the name may be the same for different course codes; i.e. Course A will be offered at three different times during the year; there will be a course record for each instance differentiated by the course code and the course record contains the date the course will be beheld.

For the class list, I need to be able to select on the course name and date but don't know how.

i.e. the first parameter: course name and the second parameter to be the dates that the course name selected is offered - and only the dates that the course is offered.

If it's necessary to create a field that is a concatenation of the course name and date and select on both at once, that's fine.

Any help would be appreciated.

 

[lbass]

If you are using CR XI or above, you could use a cascading dynamic parameter that is based on the name as the first parameter and the date as the second.

-LB

 

[kims212]

Thank you lbass.

I think that I've got that working now - except that the documentation in the help file seems to be different than what happens in the actual system. I have to investigate further to make sure that it's including all of the dates for a course but so far it looks good.

I have a couple of other questions however:

i) is there a way to format the display of the date when it is shown as a parameter and to display the date portion only; currently it displays the date and time and shows the date as YYYY-MM-DD; I'd prefer December 12, 2009

ii) I do want to list the dates for the courses but is there a way to select only current and future courses and not ones that are in the past?

Thank you so much for your help.

Kim

 

[lbass]

1) If you are talking about the parameter selection screen, there isn't really a way to change that display. You could change the parameter to a date parameter, instead of a datetime parameter, at least to eliminate the time.

2) You would have to use a command as your source for the parameter pick list. I think you should create a command to encompass both parameters, something like this:

select table.`course`, table.`date`
from table
where table.`date` >= {fn now()}

Then use this to populate both elements of the dynamic cascading parameter.

The punctuation and functions will be specific to your datasource, so adapt the above as necessary.

-LB

 

[kims212]

Thanks again lbass.

when I opt for a Dynamic parameter value, the select for Type gets greyed out and doesn't allow me to change it. I guess it gets the type from the field in the table so I can't specify it. Not a huge deal, but my users are a bit particular.

I'm quite new to Crystal reports so I'm not sure just where to set up the command you've indicated or even what that means; could you tell me where I would have to go to set up the sql for the command.

Thanks again.

 

[lbass]

What kind of database are you using?

Go to database->database expert->your datasource->add command (just above the list of tables), and enter the query there. If you are unsure how to punctuate, go to database->show SQL query and observe how the punctuation appears there.

Once you have a working command, the linking expert will pop up. Do not link the command to any tables, regardless of the message you get. Only reference the command in your dynamic parameter, not in the body of the report.

-LB

 

[kims212]

Dear LB

This is a very belated thank you for your reply back in December; the last week before the Christmas break I got swamped with a whole bunch of things that had to be done before I left for two weeks, much like I did last week after I got back and didn't come back here until today. In fact I had forgotten about the report until I was looking for the same process for a different report today.

Your suggestion of course worked perfectly and the dynamic list now gives me only current and future courses. it took me a while to figure out how to do what you were saying but once I did and it worked it was a very nice feeling.

Many, many! thanks.
Kim