Solved

Crystal Reports: Add if between two dates

Posted on 2009-07-14
6
1,712 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

15 Experts available now in Live!

Get 1:1 Help Now