Parameters in Crystal Reports

Is there a way to use a date parameter with out using a date field in a table.
I hope that I can explain this correctly

For example: I have 158 students who enrolled into classes in January and I have 5 students who have enrolled into classes in February.

Now I want to know for the week of February 17 to February 21 how many of these students are still enrolled.

So when I do this report as it is set up right now my report does not have anybody enrolled for this week, but what I want to see is everybody who enrolled in January and February without having to put in the January and February dates.    I guess what I need is a snapshot.

Does this make sense?
TCHGirlAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vastoConnect With a Mentor Commented:
You will need to add a new column to distinct the records somehow. You need either a date or a period column. In SQLServer ( and in any other database ) you can add a date column and set the default value to the current date. Each time you add a new record the field will be filled with the current date. Later you can use this field. There are probably other ways to identify who is enrolled during the period . For example if you have consecutive ID field you can write somewhere the maximum ID number for the period so you will know that for January data was Min ID=150 - MaxID =250 and you can get just the records with this IDs. However this method is nothing different than the date column (you still need a separate column) and you will be very restricted on reporting + you will need to maintain additional table where the beginning and the end of the period will be saved.
0
 
vastoCommented:
What is the structure of the data ?
0
 
TCHGirlAuthor Commented:
it is a database table.  Is this what you are asking?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mlmccCommented:
It can be done but you would need to define what you want entered like 17 Feb 2014 and 25 Feb 2014 then define what records are returned.

In this instance I suspect you have classes that start in January and run for a specified period say 2 months and other classes that start in Feb that run for say 3 months.  You need to see who is in class during your time frame.

It can be done but the results will probably be slow since you would have to calculate the end date for each record.  

Can you change the database to include an end date?

mlmcc
0
 
vastoCommented:
I meant the structure - showing the columns. What kind of datasource is this ? Access, Excell ... ?
0
 
TCHGirlAuthor Commented:
Our enrollment goes from January to March, then April to June, Then July to September, October to December.  I don't want to add any more fields to the table. Right now I am the only one doing reports and I want use this formula no matter what time frame people are looking for.
0
 
TCHGirlAuthor Commented:
The table are in SQL
0
 
mlmccConnect With a Mentor Commented:
I assume you have a field that indicates the enrollment period.
What values do you have that indicate the time period the record is for?


Say you have
Jan14     January-March 2014
Apr14    April-June 2014
Jul14     July-September2014
Oct14   October-December14
etc

You could use a selection formula like
If Month({?DateParam} <=3 then
    {YourDateField = 'Jan14'
Else If Month({?DateParam} <=6 then
    {YourDateField = 'Apr14'
Else If Month({?DateParam} <=9 then
    {YourDateField = 'Jul14'
Else  then
    {YourDateField = 'Oct14'

Open in new window


This can be expanded to handle multiple years

If Month({?DateParam} <=3 then
    {YourDateField = 'Jan' & CStr(Year(?DateParam}, 'yy')
Else If Month({?DateParam} <=6 then
    {YourDateField = 'Apr' & CStr(Year(?DateParam}, 'yy')
Else If Month({?DateParam} <=9 then
    {YourDateField = 'Jul' & CStr(Year(?DateParam}, 'yy')
Else  then
    {YourDateField = 'Oct' & CStr(Year(?DateParam}, 'yy')

Open in new window


mlmcc
0
 
mlmccCommented:
Can you time frame cross enrollment periods?

mlmcc
0
 
TCHGirlAuthor Commented:
I finally got the chance to do the above formula and this is what I got.  Did I miss a step some where?
print of the above formula
0
 
James0628Commented:
mlmcc was actually missing a lot of "{", "}" and ")" characters.  You could try the following.  Put in your field name, of course.

If Month({?Date}) <=3 then
    {YourDateField} = 'Jan' & CStr(Year({?Date}), 'yy')
Else If Month({?Date}) <=6 then
    {YourDateField} = 'Apr' & CStr(Year({?Date}), 'yy')
Else If Month({?Date}) <=9 then
    {YourDateField} = 'Jul' & CStr(Year({?Date}), 'yy')
Else  then
    {YourDateField} = 'Oct' & CStr(Year({?Date}), 'yy')

Open in new window

0
 
TCHGirlAuthor Commented:
So I pasted this formula into my report formula editor and then I got this message.  Date Formula
0
 
James0628Commented:
I missed that.  The highlighted "then" should not be there.  Just remove it.

 James
0
 
TCHGirlAuthor Commented:
ok so I took out the "then" and now this is what I get.  Why does Crystal Reports have to be so hard.
Next Screen
0
 
mlmccCommented:
The parameter ClassDate has to be a date or DateTime type.

mlmcc
0
 
TCHGirlAuthor Commented:
So I have to put a real date in there like 01/01/2014?  I am sorry if these are stupid questions.  I am still learning this software.
0
 
mlmccCommented:
WHat are you entering now?

The datatype must be date.  You can then select from the calendar

mlmcc
0
 
mlmccCommented:
If you want to enter free text in a date format, it can be converted to a date type.

mlmcc
0
 
TCHGirlAuthor Commented:
my class date is a listed as a date.
0
 
James0628Commented:
I think you misunderstood.  You have a parameter named {?Class Date} and a field named ClassDate.  mlmcc was saying that the data type for the {?Class Date} parameter should be date or datetime.  He wasn't talking about the field named ClassDate.

 Your {?Class Date} parameter doesn't absolutely have to be a date or datetime type.  mlmcc's formula was just based on the assumption that it was.  If your parameter is a different data type (eg. string) and you want to keep it that way for some reason, it may be possible to change the formula so that it extracts the month from that value.  It depends on how the parameter values are formatted, and how consistent they are.

 Aside from that, if I was correct and you were saying that the ClassDate field is a date data type, then that's another problem.  If you go back to mlmcc's first post with that formula, he was assuming, for example purposes, that your date field contained strings like "Jan14" and "Apr14".  If your field is actually a date, the tests in the formula will need to be changed.

 It might save a lot of time if you could just u/l the report so that we can take a look at it and see things for ourselves.  You can attach the .RPT file to a post.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.