Monthly birthday list 1

[bkast3]

I have searched and cannot find the answer and it has to be simple. Please help. I have a stored procedure that pulls my data in a SQL database. I am creating a crystal report in 8.5 and have a simlple name and birthdate report. I want to now create the report so that the end-user opens the report and is prompted to select a month. After the month is selected then only the names and days of the month are pulled to created a monthly birthday list. End result is:

MAY

Jim Smith 16
Tony Jones 23
Honey Wells 30

I would think that it is simple but I looked everywhere and I can not find how to pull the day out or select by month from the patient.DOB field which is formatted 05/06/1957.

Any help would be great. I was trained in the past and now asked to do the work. Any suggestions of great resource material would be helpful. I have several books but none give good examples of formula work.

 

[kskid]

given the month number or {?BirthMonth} from the user, then the record selection would look like

month({patient.DOB}) = {?BirthMonth}

 

[synapsevampire]

Since it's a stored procedure, it should already have a date parameter in it, and Crystal will prompt for it automatically if that's the case. SPs tend to be very atomic,, extracting data for a specific purpose as opposed to a generic function, although they can do the latter as well.

If it isn't prompting, then all processing will occur within the report, which could be quite slow.

You need to know the data type being returned by the SP for this date. The format is meaningless if it's a date type, if it's a string, then it matters.

If it's a date type, create a parameter of type date, then in the Report->Edit Selection Formula->Record use something like:

month({table.dob}) = month({?MyDateParm})

-k

 

[synapsevampire]

Witchita makes a good point, you can use a numeric parameter for the month if you'd like, but make sure that you follow my other suggestions to cover the other aspects.

-k

 

[bkast3]

I have been able to get the month to list like I want but I have not been able to have the report prompt me for which month I would like to report on. In access when I run the report a message pops up and asks for a month and then once entered it gives me the data for the month selected. Any help on this also.

 

[synapsevampire]

Create a numeric parameter and then go into Report->Edit Selection Formula->Record and place

month({table.date}) = {?MyMOnthParm}

-k