Crystal Reports 2013
Tasked to create a Crystal reports report that displays the current month's amount and # of hours of each group of items and also displays the cumulative total year to date values for each group for the entire fiscal year. So report would look like
Current Month Year to date
Group I
Amount Hours Amount Hours
xxxxx xxxxx
xxxxx xxxxx
xxxxx xxxxx
subtotal xxxxx xxxxx XXXXX XXXX
Group II
Amount Hours Amount Hours
xxxxx xxxxx
xxxxx xxxxx
xxxxx xxxxx
subtotal xxxxx xxxxx XXXXX XXXX
Current Month Year to date
Group I
Amount Hours Amount Hours
xxxxx xxxxx
xxxxx xxxxx
xxxxx xxxxx
subtotal xxxxx xxxxx XXXXX XXXX
Group II
Amount Hours Amount Hours
xxxxx xxxxx
xxxxx xxxxx
xxxxx xxxxx
subtotal xxxxx xxxxx XXXXX XXXX
vasto
.... and the question is ?
What is the fiscal year?
I assume there is a date field
Selection formula will be
{DateFIeld} > Date(yyyy, mm, dd) // dd-mm-yyyy is the start of the fiscal year
Create a formula - CurrentMonthAmount
Group on the field you want
Put the amount field on the report and the above formula on the report
Use summaries to get the totals and subtotals
mlmcc
I assume there is a date field
Selection formula will be
{DateFIeld} > Date(yyyy, mm, dd) // dd-mm-yyyy is the start of the fiscal year
Create a formula - CurrentMonthAmount
If Year({DateField}) = Year(CurrentDate) and Month({DateField}) = Month(CurrentDate) then
{AmountField}
Else
0Group on the field you want
Put the amount field on the report and the above formula on the report
Use summaries to get the totals and subtotals
mlmcc
ASKER
mimcc,
I probably need to give you a little more information. I can easily sum the monthly amount of the two fields (amount and hours) but how do I get another sum field of all the months (cumulative amount) alongside of the sum? Another twist is the fiscal year is the government fiscal year of October 2016 to September 2017. One other thing: the amount field is a consolidation of 6 value fields:LABOR_HOURS.BASIC_P
+ {LABOR_HOURS.INDIRECT_ADD_
I am way behind so I'm doing December 2016 report. The date field name is labor_hours.wrk_date, the hour field is named labor_hours.hrs and the amount field is the consolidation of the fields cited above. To summarize, I can create the groups and obtain the group sums for the month but my question is how can I get a summary field for the whole year (in this case, the whole year is Oct - Dec so far).
Hope that helps.
Howard
I probably need to give you a little more information. I can easily sum the monthly amount of the two fields (amount and hours) but how do I get another sum field of all the months (cumulative amount) alongside of the sum? Another twist is the fiscal year is the government fiscal year of October 2016 to September 2017. One other thing: the amount field is a consolidation of 6 value fields:LABOR_HOURS.BASIC_P
+ {LABOR_HOURS.INDIRECT_ADD_
I am way behind so I'm doing December 2016 report. The date field name is labor_hours.wrk_date, the hour field is named labor_hours.hrs and the amount field is the consolidation of the fields cited above. To summarize, I can create the groups and obtain the group sums for the month but my question is how can I get a summary field for the whole year (in this case, the whole year is Oct - Dec so far).
Hope that helps.
Howard
The idea behind mlmcc's suggestion is that you have the report include the data for the entire year, and then create formulas like the one that he posted to get the figures for just the current month. So, for example, a summary on labor_hours.hrs would give you the total for the year, and a summary on the formula would give you the total for the month.
Since you're talking about running the report for an earlier month (December), I assume that the report has, or will have, a date parameter? You could use that parameter to calculate the starting and ending dates for the corresponding fiscal year, and use that in the record selection formula. For example:
If your parameter is a datetime, Date ({?date parameter}) will remove the time. If the parameter is just a date, you don't need the Date function.
The Day function returns the day of the month for the given date, so subtracting that from the date gives you the last day of the previous month. Add 1 to get the first day of the month. For example, 1/23 - 23 gives you 12/31, and + 1 gives you 1/1.
The Month function returns the month for the given date. DateAdd is used to subtract that many months, plus 2 more, from the first day of the specified month. So, for example, 01/23/17 becomes 01/01/17, and then 3 (1 + 2) months are subtracted from that, giving you 10/01/16, the start of the fiscal year.
For the fiscal year ending date, DateAdd is simply used to add 12 months to the starting date, and we look for dates before (<) that. For example, 10/01/16 becomes 10/01/17, so you're looking for dates >= 10/01/16 and < 10/01/17.
Also, if you do use a date parameter (instead of basing the report on today's date), then you would change the formula that mlmcc posted to use that parameter, instead of CurrentDate:
FWIW, there are other ways that you could try to handle this, but including the data for the full year in the report seems the simplest, and the other approaches probably wouldn't gain you much, if anything, unless including the full year in the report causes some kind of problem (which may depend on the structure of your report).
James
Since you're talking about running the report for an earlier month (December), I assume that the report has, or will have, a date parameter? You could use that parameter to calculate the starting and ending dates for the corresponding fiscal year, and use that in the record selection formula. For example:
{labor_hours.wrk_date} >=
DateAdd ("m", - (Month({?date parameter}) + 2), Date ({?date parameter}) - Day ({?date parameter}) + 1) and
{labor_hours.wrk_date} <
DateAdd ("m", 12, DateAdd ("m", - (Month({?date parameter}) + 2), Date ({?date parameter}) - Day ({?date parameter}) + 1))If your parameter is a datetime, Date ({?date parameter}) will remove the time. If the parameter is just a date, you don't need the Date function.
The Day function returns the day of the month for the given date, so subtracting that from the date gives you the last day of the previous month. Add 1 to get the first day of the month. For example, 1/23 - 23 gives you 12/31, and + 1 gives you 1/1.
The Month function returns the month for the given date. DateAdd is used to subtract that many months, plus 2 more, from the first day of the specified month. So, for example, 01/23/17 becomes 01/01/17, and then 3 (1 + 2) months are subtracted from that, giving you 10/01/16, the start of the fiscal year.
For the fiscal year ending date, DateAdd is simply used to add 12 months to the starting date, and we look for dates before (<) that. For example, 10/01/16 becomes 10/01/17, so you're looking for dates >= 10/01/16 and < 10/01/17.
Also, if you do use a date parameter (instead of basing the report on today's date), then you would change the formula that mlmcc posted to use that parameter, instead of CurrentDate:
If Year({DateField}) = Year({?date parameter}) and Month({DateField}) = Month({?date parameter}) then
{AmountField}
Else
0FWIW, there are other ways that you could try to handle this, but including the data for the full year in the report seems the simplest, and the other approaches probably wouldn't gain you much, if anything, unless including the full year in the report causes some kind of problem (which may depend on the structure of your report).
James
ASKER
mimcc/james, Sounds good! I'll give it a shot first thing in the morning. Will let you know what happens. BTW: I'm assuming that you mean so far for the year when you say entire year? For instance, I only know the figures from October - December. So in my world, as of December would be the entire year, which would change to include January when those figures are obtained, correct?
Thanks,
Appreciate both of your prompt responses.
Thanks,
Appreciate both of your prompt responses.
Yeah, I guess I went too far on the ending date. :-) I was trying to get the entire fiscal year, but I guess whatever month you ask for is the last month that you want to include (December, in your example). However, if your data doesn't actually include anything after the month in question, then having the date range include months after that doesn't matter, since there is no data for those months. IAC, if you want the ending date to be end of the specified month, the tests below should include dates from the October before the date parameter, through the end of the month in that parameter:
James
{labor_hours.wrk_date} >=
DateAdd ("m", - (Month({?date parameter}) + 2), Date ({?date parameter}) - Day ({?date parameter}) + 1) and
{labor_hours.wrk_date} <
DateAdd ("m", 1, Date ({?date parameter}) - Day ({?date parameter}) + 1)James
ASKER
Getting a little confused and backwards it seems, so I just hard-coded the fields to test. Note, page 1 is grouped by org code (N2H0000) and then by charge code (ignore 2C17B9 as it is a continuation of the previous page). Note the work date (wrk_date) has various months of the fiscal year (Oct 16 - Sept 17). As an example. I need only December displayed as the detailed current month. I don't need to see any of the other month details...just the subtotal for the whole year for each group. Additionally, don't need January displayed as it is not a complete month. But what I have so far shows the detail in the year to date field while the subtotal for detail appears to be the only field displayed for my current month. Somehow have it backwards?? Attached is the hard-code and design view
Report.pdf
Report.pdf
Can you upload the report file?
mlmcc
mlmcc
ASKER
Sorry, can't upload file..,data is too sensitive. I'll just wade through it and close post soon if I can't figure it out.
Thanks for your efforts.
howard
Thanks for your efforts.
howard
We could look at it without data.
mlmcc
mlmcc
If it's just the actual data that's sensitive, not the data structure (tables, etc.) or report structure, then, as mlmcc said, you can just post the report without saved data.
I see some January dates in that report output, so you have data that's dated after December, and you're apparently not using the record selection to limit which dates are included in the report. How are you selecting the month for the report (eg. December)? Are you using a parameter? If so, what type of parameter (date, datetime, etc.)?
For the months prior to the selected month, like October, you can use suppression on the detail section, so those records are hidden. Go to the Section Expert for the detail section, click the formula (X+2) button beside Suppress and enter a formula to suppress any dates before the start of the desired month. Assuming that you were using a datetime parameter, as in my earlier examples, you could use:
{labor_hours.wrk_date} < (Date ({?date parameter}) - Day ({?date parameter}) + 1)
That should suppress the detail section for any date that is before the first day of the specified month.
As I mentioned earlier, if the parameter is just a date (not datetime), then you don't need the Date function (and you might get an error if you try to use it on a date).
James
I see some January dates in that report output, so you have data that's dated after December, and you're apparently not using the record selection to limit which dates are included in the report. How are you selecting the month for the report (eg. December)? Are you using a parameter? If so, what type of parameter (date, datetime, etc.)?
For the months prior to the selected month, like October, you can use suppression on the detail section, so those records are hidden. Go to the Section Expert for the detail section, click the formula (X+2) button beside Suppress and enter a formula to suppress any dates before the start of the desired month. Assuming that you were using a datetime parameter, as in my earlier examples, you could use:
{labor_hours.wrk_date} < (Date ({?date parameter}) - Day ({?date parameter}) + 1)
That should suppress the detail section for any date that is before the first day of the specified month.
As I mentioned earlier, if the parameter is just a date (not datetime), then you don't need the Date function (and you might get an error if you try to use it on a date).
James
ASKER
OK. I cleaned this up a bit and reduced the number of pages to only a few. Also reduced number of organizations to one (left the "in" in place instead of =)
Now, this is suppose to be a December report. But I can't get any detailed costs at all under the December cost heading except for zeroes when the subtotal of a group sums to zero (first 3 charge codes).Just need to see group total (2,269.27,1,368.05 and 855.03) along with the identifying group heading. Charge code 2A84D8 continues onto page 2 with transactions from Oct - jan 2017. Just need December detailed costs with subtotal.
Now, this is suppose to be a December report. But I can't get any detailed costs at all under the December cost heading except for zeroes when the subtotal of a group sums to zero (first 3 charge codes).Just need to see group total (2,269.27,1,368.05 and 855.03) along with the identifying group heading. Charge code 2A84D8 continues onto page 2 with transactions from Oct - jan 2017. Just need December detailed costs with subtotal.
Did you mean to include the report or something with that last post? I don't see anything attached, and it's hard to tell what's going on with just the info in that post.
Once again, what makes this a "December report"? Do you have a parameter or ...?
James
Once again, what makes this a "December report"? Do you have a parameter or ...?
James
ASKER
Sorry. Thought I included it. Took out the parameters and hard coded. Attached
LaborDistribution24Jan2017_5_EE.rpt
LaborDistribution24Jan2017_5_EE.rpt
I'm not sure that I'm following everything that you said, but if you want to see the figures for December in the details, just put EE_TESTS in the detail section.
If you want to see the details for December, but not for the earlier months, you can use a suppression formula on the detail section, as I described earlier. And I assume that you want to change the record selection formula to exclude any dates after December.
If you don't want to see any details at all (just the group totals), then you can suppress the detail section completely.
FWIW, since Monthly_Costs and EE_TESTS seem to be adding the same fields together, you could use Monthly_Costs in EE_TESTS. That makes EE_TESTS a bit simpler, but will also be convenient if the fields that you are combining ever change, because you'll only need to change one formula (Monthly_Costs).
if year ({LABOR_HOURS.WRK_DATE})= YEAR (#12/31/2016#) AND MONTH ({LABOR_HOURS.WRK_DATE})= MONTH (#12/31/2016#) THEN
{@Monthly_Costs}
ELSE
0
James
If you want to see the details for December, but not for the earlier months, you can use a suppression formula on the detail section, as I described earlier. And I assume that you want to change the record selection formula to exclude any dates after December.
If you don't want to see any details at all (just the group totals), then you can suppress the detail section completely.
FWIW, since Monthly_Costs and EE_TESTS seem to be adding the same fields together, you could use Monthly_Costs in EE_TESTS. That makes EE_TESTS a bit simpler, but will also be convenient if the fields that you are combining ever change, because you'll only need to change one formula (Monthly_Costs).
if year ({LABOR_HOURS.WRK_DATE})= YEAR (#12/31/2016#) AND MONTH ({LABOR_HOURS.WRK_DATE})= MONTH (#12/31/2016#) THEN
{@Monthly_Costs}
ELSE
0
James
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mimcc,
Basically. But I pasted your formula in the format formula editor-suppress(no drill-down) and get an error " A Boolean is required here" (cursor moves to the beginning of the formula before the "if".
Basically. But I pasted your formula in the format formula editor-suppress(no drill-down) and get an error " A Boolean is required here" (cursor moves to the beginning of the formula before the "if".
ASKER
Mimcc, Wait! My error!! I don't want to put that formula in the suppression field. I want to suppress everything BUT December transactions! Where do I do that??
ASKER
MIMCC, IT must somehow go into the record selection formula editor. Here is what mine currently looks like:{EMPLOYEE_MSTR_VIEW.O
{LABOR_HOURS.WRK_DATE} >= #10 01 2016# AND
{LABOR_HOURS.SOURCE_FOA} = "N2";
{LABOR_HOURS.WRK_DATE} >= #10 01 2016# AND
{LABOR_HOURS.SOURCE_FOA} = "N2";
If you change the record selection formula to only include December, the report won't include the previous months at all, so you won't be able to get YTD totals. My assumption is that you want the report to include everything from 10/01 through 12/31, and nothing after that, and then suppress the details for the months before December.
If so, then, as I've mentioned before, you need to change the record selection formula to exclude the later months, like January:
To suppress the earlier months (so the report includes them, but you can't see them), go to the Section Expert for the detail section, click the formula (X+2) button beside Suppress and enter a formula like the following:
{labor_hours.wrk_date} < #12 01 2016#
James
If so, then, as I've mentioned before, you need to change the record selection formula to exclude the later months, like January:
{EMPLOYEE_MSTR_VIEW.ORG_CODE} in ["N2H0000", "N2L1000", "N2L1BAO", "N2L1KAO", "N2L1SAO", "N2P0000"] and
{LABOR_HOURS.WRK_DATE} >= #10 01 2016# AND
{LABOR_HOURS.WRK_DATE} <= #12 31 2016# AND
{LABOR_HOURS.SOURCE_FOA} = "N2"To suppress the earlier months (so the report includes them, but you can't see them), go to the Section Expert for the detail section, click the formula (X+2) button beside Suppress and enter a formula like the following:
{labor_hours.wrk_date} < #12 01 2016#
James
The suppression is in the GROUP SELECTION.
I changed the selection formula to use the date parameter as the end date.
mlmcc
I changed the selection formula to use the date parameter as the end date.
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok. Think I was making simple things too hard. Seems as though my main problem was a grouping issue. I was trying to use variations (monthly, cumulative) of the sum of the same fields within the same group. When I created a new group and placed one of the entries, it worked like a charm. Duh! Not finished with the project but closing this post as successfully completed James and Mimcc, appreciate your expertise and your patience!!
ASKER
Outstanding assistance folks! I'm new to Crystal Reports and your expertise and patience was greatly appreciated. I am not finished with this project so I'm sure you'll be hearing from me on future questions.
Thanks Again,
Howard
Thanks Again,
Howard
I'm glad that you got it sorted out. :-)
I assume that the final report is going to use a parameter to select the month, rather than hard-coded dates, like #10 01 2016#. If you need any help with that, some of the things that I posted earlier might help.
James
I assume that the final report is going to use a parameter to select the month, rather than hard-coded dates, like #10 01 2016#. If you need any help with that, some of the things that I posted earlier might help.
James