Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parameters in Crystal Reports

Posted on 2014-02-24
20
Medium Priority
?
463 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 101

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 750 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 101

Assisted Solution

by:mlmcc
mlmcc earned 750 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 101

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
 
LVL 35

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 35

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 101

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 101

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 101

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 35

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
While Plesk offers many potential benefits to website administrators, including compatibility with Windows Server and other leading technologies, the company has also been working to differentiate it from other control panels for content management…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

926 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