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

x
?
Solved

Crystal Reports: Add if between two dates

Posted on 2009-07-14
6
Medium Priority
?
1,767 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
[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
  • 3
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 35

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

722 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