date ranges by group formulas

[ginaburg]

I am using crystal 11.5. I have a group that runs off of this formula which gives me hours by month for 2014
if {PRJC.PREndDate} in cdate(2014,01,04) to cdate(2014,01,25) then 'January' else
if {PRJC.PREndDate} in cdate(2014,02,01) to cdate(2014,02,22) then 'February' else
if {PRJC.PREndDate} in cdate(2014,03,01) to cdate(2014,03,29) then 'March' else
if {PRJC.PREndDate} in cdate(2014,04,05) to cdate(2014,04,26) then 'April' else
if {PRJC.PREndDate} in cdate(2014,05,03) to cdate(2014,05,31) then 'May' else
if {PRJC.PREndDate} in cdate(2014,06,07) to cdate(2014,06,28) then 'June' else
if {PRJC.PREndDate} in cdate(2014,07,07) to cdate(2014,07,26) then 'July' else
if {PRJC.PREndDate} in cdate(2014,08,02) to cdate(2014,08,30) then 'August' else
if {PRJC.PREndDate} in cdate(2014,09,06) to cdate(2014,09,27) then 'September' else
if {PRJC.PREndDate} in cdate(2014,10,04) to cdate(2014,10,25) then 'October' else
if {PRJC.PREndDate} in cdate(2014,11,01) to cdate(2014,11,29) then 'November' else
if {PRJC.PREndDate} in cdate(2014,12,06) to cdate(2014,12,27) then 'December'

I inserted a group below that one that uses the following formula that I wan to give me hours for all of 2013
if {PRJC.PREndDate} in cdate(2013,01,05) to cdate(2013,12,28) then '2013'

When I run the report, it gives me January hours in the first group and only January hours in the second group. How can I get the second group to give me hours for the year.
There are no date range parameters in the report that should be restricting the data.

What am I missing?

 

[pmax9999]

The only way this can work is if you change the group order to YEAR first and then MONTH. Otherwise, the MONTH group will be broken down by YEAR, so you get the data for the MONTH of January broken down by the YEAR 2013, which, assuming the Record Selection returns only 1 year of data is the same result as you would receive without using the second group.

Hope this makes sense and helps.

Cheers
Pete

 

[ginaburg]

I changed my grouping to have the year group first but it still does not work.
For the year group, it's giving me January 2014 data and the month group is giving me January 2014 data.
I'm not getting any data for 2013.

 

[pmax9999]

Actually, if you want monthly grouping for 2014 but all of 2013 in a single group, get rid of the YEAR group and amend the MONTH group formula by adding a final line of:

...
Else '2013'

For this to work, your Record Selection formula will need to exclude any data where the {PRJC.PREndDate} is prior to 2013.

Hope this helps.

Cheers
Pete

 

[ginaburg]

When I do that, I only get January 2014 data. So I tried something and put both years in one grouping like this.
if {PRJC.PREndDate} in cdate(2013,01,05) to cdate(2013,01,26) then 'January' else
if {PRJC.PREndDate} in cdate(2013,02,02) to cdate(2013,02,23) then 'February' else
if {PRJC.PREndDate} in cdate(2013,03,02) to cdate(2013,03,30) then 'March' else
if {PRJC.PREndDate} in cdate(2013,04,06) to cdate(2013,04,27) then 'April' else
if {PRJC.PREndDate} in cdate(2013,05,04) to cdate(2013,05,25) then 'May' else
if {PRJC.PREndDate} in cdate(2013,06,01) to cdate(2013,06,29) then 'June' else
if {PRJC.PREndDate} in cdate(2013,07,06) to cdate(2013,07,27) then 'July' else
if {PRJC.PREndDate} in cdate(2013,08,03) to cdate(2013,08,31) then 'August' else
if {PRJC.PREndDate} in cdate(2013,09,07) to cdate(2013,09,28) then 'September' else
if {PRJC.PREndDate} in cdate(2013,10,05) to cdate(2013,10,26) then 'October' else
if {PRJC.PREndDate} in cdate(2013,11,02) to cdate(2013,11,30) then 'November' else
if {PRJC.PREndDate} in cdate(2013,12,07) to cdate(2013,12,21) then 'December' else
if {PRJC.PREndDate} in cdate(2014,01,04) to cdate(2014,01,25) then 'January 2014' else
if {PRJC.PREndDate} in cdate(2014,02,01) to cdate(2014,02,22) then 'February 2014' else
if {PRJC.PREndDate} in cdate(2014,03,01) to cdate(2014,03,29) then 'March 2014' else
if {PRJC.PREndDate} in cdate(2014,04,05) to cdate(2014,04,26) then 'April 2014' else
if {PRJC.PREndDate} in cdate(2014,05,03) to cdate(2014,05,31) then 'May 2014' else
if {PRJC.PREndDate} in cdate(2014,06,07) to cdate(2014,06,28) then 'June 2014' else
if {PRJC.PREndDate} in cdate(2014,07,07) to cdate(2014,07,26) then 'July 2014' else
if {PRJC.PREndDate} in cdate(2014,08,02) to cdate(2014,08,30) then 'August 2014' else
if {PRJC.PREndDate} in cdate(2014,09,06) to cdate(2014,09,27) then 'September 2014' else
if {PRJC.PREndDate} in cdate(2014,10,04) to cdate(2014,10,25) then 'October 2014' else
if {PRJC.PREndDate} in cdate(2014,11,01) to cdate(2014,11,29) then 'November 2014' else
if {PRJC.PREndDate} in cdate(2014,12,06) to cdate(2014,12,27) then 'December 2014'

So Now, I thought I would get January thru December 2013 and January 2014 but I only get January thru December 2013. It won't pull both years no matter how I do the groups or formulas. I have no date range restrictions on my report.

 

[pmax9999]

A few questions for you:
[ol 1]
[li]If you only want data for 2013 and 2014, why are you not limiting the data to those years via the Record Selection formula? If nothing else, it makes the report very inefficient when you return records that are not required.[/li]
[li]Where are you putting the above code? It needs to be in a formula, that is then used as the basis for the group. Is that how you are doing it?[/li]
[li]Do you have anything in your Group Selection formula that is limiting the groups being displayed?[/li]
[li]Have you verified that the report is even returning the required data? For troubleshooting I would start without any grouping, put sufficient fields on the report to confirm that you are getting records for the years you want, and then put the formula in the details section to make sure it is returning the result you require. This will confirm that there is nothing in your record selection that is excluding records you need, and confirm the formula is doing what is expected.
[/li]
[/ol]

Cheers
Pete