Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parameters in Crystal Reports

Posted on 2014-02-24
20
Medium Priority
?
460 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

671 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