# Crystal Reports: Add if between two dates

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
``````
Report-Template.jpg
LVL 4
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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
Author Commented:
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
Commented:
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
Commented:
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
Author Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.