Solved

Crystal Reports: Add if between two dates

Posted on 2009-07-14
6
1,716 Views
Last Modified: 2012-05-07
I have a Crystal Report 2008 report that I can't seem to figure out the right way to work up a breakdown view. The data inthe report will be a list of people that I want to show how many hours they works for each month during a specified time frame.

Parameter Field: Start_Date
Parameter Field: End_Date (For only if they want to show less than 12 months of data.

I then take the user entered Start_Date and look out 12 months which make up the headers for each of the columns.
Formula Field: Month_01 through Month_11. (See Code)

There are two time fields (Billable Hours & NonBillableHours) that I need to add for each user and then total for the time range specified.

Anyone able to help me out?
numberVar i := 1; //Looking ahead x no of months

numberVar x1 := month({?Start Date}); //month requested

if x1 + i <= 12 then Date(year({?Start Date}),month({?Start Date}) + i,day({?Start Date})) else

 Date(year({?Start Date})+1,(month({?Start Date})+i)-12,day({?Start Date})) // month is greater than 12

Open in new window

Report-Template.jpg
0
Comment
Question by:tropez9
  • 3
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24851467
I think I must be missing the point somewhere because what you are doing seems overly complicated to me.

It seems to me that you need..
1)a selection rule which gets the records either  between the start date and the end date  or  between the start date and the (start date +1 year - 1 day)

2) a formula field to add Billable Hours & NonBillableHours for each record

3) a crosstab which has the employee in rows, the date in columns, grouped by month, and the formula field as the data

The only issue I can identify is that the column headings must include the year to force a correct sequence.
0
 
LVL 4

Author Comment

by:tropez9
ID: 24853100
Sound simple enough.

I know a bit more than the basics of Crystal Reporting + some formulas to get around in Crystal, but a crosstab is one of those areas I haven't worked on yet. I'll try it out and let you know as soon as I can.

Thank you.
0
 
LVL 4

Author Comment

by:tropez9
ID: 25023192
Do you know of anyway to force a 12-month view when doing the Cross-Tab? Everytime I run the report, it only shows those months that have data and I want it to show a 0 placeholder.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 77

Expert Comment

by:peter57r
ID: 25023348
CR cannot report on data that isn't there.
You woud need to include an extra table containing a record for each month you want to include in the report and do a left join from that table onto your current data source.

There is a further problem here in that if you try to do a select from the right side of the left join (which would be from your existing data source) that destroys the left join and you're back where you started.

So select your dates from the dates table, not the main data.
0
 
LVL 34

Expert Comment

by:James0628
ID: 25039242
Peter has given you the "standard" solution.  If that's a problem for you (maybe you can't create a new table with the months in it), you could check out the links posted by mlmcc in the following thread.  They point to a solution for charts, but it seems like it would work for a cross-tab too.  Basically, you store the values you need in variables, adding entries for the missing dates as you go, then call a subreport, which pulls the values from the variables and uses those to produce the chart (or, in your case, a cross-tab).  One basic limitation of this approach is that since it uses a subreport, you can't use this in a subreport (one subreport can not contain another subreport).  FWIW, there are two example reports with different variations on the solution.  "populate dates between 3d.rpt" is probably the better choice (it uses arrays to store the values).

 http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_24441879.html


 James
0
 
LVL 4

Author Closing Comment

by:tropez9
ID: 31603335
Sorry for the wait, but I did finally get in and was able to create a cross-tab that did the report I wanted!! Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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

22 Experts available now in Live!

Get 1:1 Help Now