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?
Example of what I want the report to look like.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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-exchang
James
Business Accounts
Answer for Membership
by: peter57rPosted on 2009-07-14 at 09:37:59ID: 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.