Solved

Parameters in Crystal Reports

Posted on 2014-02-24
20
438 Views
Last Modified: 2014-03-07
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?
0
Comment
Question by:TCHGirl
  • 8
  • 6
  • 3
  • +1
20 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 39883879
What is the structure of the data ?
0
 

Author Comment

by:TCHGirl
ID: 39883898
it is a database table.  Is this what you are asking?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39883913
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
 
LVL 18

Expert Comment

by:vasto
ID: 39883944
I meant the structure - showing the columns. What kind of datasource is this ? Access, Excell ... ?
0
 

Author Comment

by:TCHGirl
ID: 39883951
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
 

Author Comment

by:TCHGirl
ID: 39883954
The table are in SQL
0
 
LVL 18

Accepted Solution

by:
vasto earned 250 total points
ID: 39883983
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39884513
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39884518
Can you time frame cross enrollment periods?

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39907950
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:James0628
ID: 39908256
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
 

Author Comment

by:TCHGirl
ID: 39910259
So I pasted this formula into my report formula editor and then I got this message.  Date Formula
0
 
LVL 34

Expert Comment

by:James0628
ID: 39910414
I missed that.  The highlighted "then" should not be there.  Just remove it.

 James
0
 

Author Comment

by:TCHGirl
ID: 39910466
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39910504
The parameter ClassDate has to be a date or DateTime type.

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39910668
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39911010
WHat are you entering now?

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

mlmcc
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39911101
my class date is a listed as a date.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39912105
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now