| Author |
Message |
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
   Topic: Running Total YTD
Posted: 16 Aug 2017 at 1:52am |
|
I need to create a year to date running total but cannot figure it out. I am trying to count entries to the field lwcase.cstatus. The problem is, I'm only running the report on a monthly basis - so, I can't tell it to just sum fields for each month on the report. I need it to pull data that is not actually on the report itself. How can I do this?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Aug 2017 at 3:55am |
|
The data needs to be part of your data set, either as larger data pull of the existing source, sub report data pull, command object, etc.
For example, you can make the system pull the full year, group by month and suppress all groups that are not the month you want to display.
Edited by DBlank - 16 Aug 2017 at 3:55am
|
|
IP Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
Posted: 16 Aug 2017 at 7:54am |
Originally posted by DBlank
The data needs to be part of your data set, either as larger data pull of the existing source, sub report data pull, command object, etc.
For example, you can make the system pull the full year, group by month and suppress all groups that are not the month you want to display.
So, I have a sub report built out that gives me the numbers that I need (field is #YTD in the sub report), but I don't understand how I get just that field to display for the corresponding person that my primary report is grouped by?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Aug 2017 at 8:00am |
|
not understanding how you have the entirety of the group this is a guess..
You will need to run the sub report (sr) per group, passing the person identifier from the main report to the sub report as a parameter to make sure each groups values are limited to that person.
From there you can either place the sr in the group header or footer location you want to display it or you can use shared variables to pass the values back from the sr to the main report to be used as a distinct main report field.
That said, I think you would be better off performance wise to just pull all the data into the main report and suppress it. Each group will be running the sub report which requires more overhead.
|
|
IP Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
Posted: 16 Aug 2017 at 10:39am |
Originally posted by DBlank
not understanding how you have the entirety of the group this is a guess..
You will need to run the sub report (sr) per group, passing the person identifier from the main report to the sub report as a parameter to make sure each groups values are limited to that person.
From there you can either place the sr in the group header or footer location you want to display it or you can use shared variables to pass the values back from the sr to the main report to be used as a distinct main report field.
That said, I think you would be better off performance wise to just pull all the data into the main report and suppress it. Each group will be running the sub report which requires more overhead.
I really have no idea which one I need to do, because I don't know how to do it in the first place. On my sub report, I have it broken down by group exactly like I need it to be for each person. I just need that total for each person (the result of the formula) to show up on my main report, but have no idea how to pass the value to the main report. I have also made a formula that gives me the correct numbers per group, if that is easier to pass along to the main report?
Edited by fuller31 - 16 Aug 2017 at 10:39am
|
|
IP Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
Posted: 16 Aug 2017 at 12:43pm |
|
I will try to be a little more clear on this, I know I'm not explaining it well. My main report is grouped by lwcase.asgnoff field, and I have the record selection set for 07/01-07/31. This is a monthly report, which is why that is not set for the entire year.
I have grouped the subreport the same way as the main is grouped, but the record selection is set for the whole year. I then have a formula to give me the year to date numbers for cases assigned to each person for the year. That formula is called @YTDformula - it basically counts the lwmain.number field evaluated on change of lwmain.asgnoff, which gives me the number of cases that person has for the year. So, that part is fine.....I have accurate data to pass on to the main report. There are four people, and it gives me four different correct numbers on the subreport as expected.
I need to get those values to go over to my main report in the Group Footer #1 section in the main report for each person. I've been tinkering with it for about 12 hours now and have Googled my brains out with no luck. Or, if there's an easier way to have my main report pull the data for the year but only show the numbers for a specified date range, except for the one field that I want to show a yearly total - doesn't matter to me.
Edited by fuller31 - 16 Aug 2017 at 12:47pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Aug 2017 at 5:00am |
|
Try this.
Ignore the subreport for now, you shouldn't need it.
Change your select criteria to use the current year.
If you always want the current year up to the prior month something like
Year(table.datefield)=year(today) and month(table.datefield)<month(today)
Keep your same grouping.
Add a suppression criteria in the section expert to hide rows not in the last month
month(table.datefield)<month(today)-1
create a Running total to get your sum for last month using an evaluate formula and resetting on the group change
month(table.datefield)=month(today)-1
If your total is including last month just sum the number field by the group if not create another rt with an evaluate formula resetting on the group change
month(table.datefield)<month(today)-1
Place any RT in the group footer
|
|
IP Logged |
|
|
|
