Solved

Parameters in Crystal Reports

Posted on 2014-02-24
20
448 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

713 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