YTD report calculation

[Jeff Dionne]

I have a report that tracks quotes by month and by account manager. Grouping level is 'Month' and then 'Manager'. There is no detail shown. The 'Manager' footer lists each account manager with a total amount quoted. The 'Month' footer shows the total quoted for the month. A report footer shows the total quoted for the year.

I'd like to add a YTD field next to the monthly total shown for each account manager. I've tried several variations of the run_total function, all of them unsuccessful. Any suggestions?

[Stan Mathews]

Re: YTD report calculation

You've tried several variations of the run_total function but we have no clue what they were. A sample database would go a long way towards getting concrete help rather than guesses.

[Jeff Dionne]

Re: YTD report calculation

Stan,

I'm not sure how to include a sample database. Here are the calculated fields I've been using so far:

Month = cmonth(Quote_Date)
Month_Total = total(Hrs_Quoted,Grp->Manager)
Month_Grand = total(calc->Month_Total,Grp->Month)
Year_Total = total(calc->Month_Grand)
YTD_Manager = run_total(calc->Month_Total,GRP->Grand)
YTD_Total = run_total(calc->Month_Grand,GRP->Grand)

The last 2 fields are the ones that are giving me the trouble.

[Stan Mathews]

Re: YTD report calculation

View the control panel.
Use the Tools menu
Choose Zip database
Attach the result here

[Jeff Dionne]

Re: YTD report calculation

I'll give it a try - hopefully this works...

[Stan Mathews]

Re: YTD report calculation

OK. The problem is

RUN_TOTAL() is used in the Detail section of a report to return the running total

You are trying to use it in group footers.

I'll see if I can think of another solution.

[G Gabriel]

Re: YTD report calculation

Here you go.

[Jeff Dionne]

Re: YTD report calculation

The YTD results appear to be the same now in each month, based on the current YTD totals for each manager...


I'm not sure why it works, but you might have noticed a strange calculation I tried for the overall monthly YTD total. Without adding "calc->Month_Grand" part to the run_total, the initial month's YTD calculates to 0.

[Stan Mathews]

Re: YTD report calculation

Like my screenshot?

I added a field run_hrs and populated it via a script that needs to be run before the report is printed to update new records.

[Jeff Dionne]

Re: YTD report calculation

This is great! I don't pretend to understand the code, but the report works nicely. When you say the script needs to be run before the report, do you mean I should manually click on the code icon before running the report?

Since this is new to me, can you tell me the best way to add your changes to my working database without overwriting existing data? Do I only need to add certain files from the zip folder, or all of them? (I selected all of them and overwrote the existing files on my database copy, but I don't know if that was the right thing to do.) Even though I've worked with Alpha 5 for years, I'm a novice when it comes to more complex procedures...

Thanks for your help!

[Stan Mathews]

Re: YTD report calculation

the script needs to be run before the report

The script can be run as often as you like. If you run the report from a button, merely add an action to run the script before the report print action. The script process all the records in the table, one manager at a time, in quote_date order to populate the run_hrs field. As you enter new records the run_hrs field is blank until the script is run.

best way to add your changes to my working database

Open your live database and add the run_hrs field to the quotes table. I defined it as numeric, 7, 0. If you think there will be more than 9,999,999 hrs_quoted for a manager, increase the 7 to something else. I inserted it in a particular position but it is not necessary. Close your live database and open the example. Go to the code tab of the control panel, hilight the pop script, right click and choose Copy To.

Alternatively you can export the pop script, open your database, and import it. Alternatively you can cut and paste from here to your database after beginning a new xbasic script on your code tab.

managers = table.external_record_content_get("quotes","alltrim(manager)","manager",".t. .and. unique_key_value()")
mgrs = w_count(managers)
for i = 1 to mgrs
	tbl = table.open("quotes",FILE_RW_EXCLUSIVE)
	query.filter = "manager = "+quote(word(managers,i,crlf()))
	query.options = "I"
	query.order = "cdate(quote_date)"
	query.description = "Temporary Query"
	ix = tbl.query_create()
	tbl.fetch_first()
	hrs = 0
	while .not. tbl.fetch_eof()
		tbl.change_begin()
		tbl.Run_hrs = tbl.Hrs_quoted + hrs
		tbl.change_end(.t.)
		hrs = tbl.Run_hrs
		yr = year(tbl.Quote_date)
		tbl.fetch_next()
		if yr <> year(tbl.Quote_date)
			hrs = 0
		end if
	end while
	tbl.query_detach_all()
	tbl.close()
next i

Nice to have lots of options.

[Jeff Dionne]

Re: YTD report calculation

Thanks again for your help. The report works great now.

I copied and edited this script for another table, workordr.dbf, where I have a similar report showing estimated hours from work orders. The fields that are different are "Date" and "Run_Esthrs" instead of "Quote_Date" and "Run_Hrs". The manager field is the same as in the quotes table. For some reason, the YTD calculation works for only 2 of the 3 managers, which seems odd. I've run the script several times with the same result and can't figure out why it won't work for one manager. I can see that the "Run_Esthrs" field is populated for only 2 of 3 managers, so it doesn't appear to be a problem with the report.

Here is what the edited script looks like:


managers = table.external_record_content_get("workordr","alltrim(manager)","manager",".t. .and. unique_key_value()")
mgrs = w_count(managers)
for i = 1 to mgrs
tbl = table.open("workordr",FILE_RW_EXCLUSIVE)
query.filter = "manager = "+quote(word(managers,i,crlf()))
query.options = "I"
query.order = "cdate(date)"
query.description = "Temporary Query"
ix = tbl.query_create()
tbl.fetch_first()
hrs = 0
while .not. tbl.fetch_eof()
tbl.change_begin()
tbl.Run_Esthrs = tbl.Esthrs + hrs
tbl.change_end(.t.)
hrs = tbl.Run_Esthrs
yr = year(tbl.Date)
tbl.fetch_next()
if yr <> year(tbl.Date)
hrs = 0
end if
end while
tbl.query_detach_all()
tbl.close()
next i

[Stan Mathews]

Re: YTD report calculation

In the interactive editor, paste in this line

? table.external_record_content_get("workordr","alltrim(manager)","manager",".t. .and. unique_key_value()")

You should see a list of the manager names found in the workordr table.

[Jeff Dionne]

Re: YTD report calculation

This worked properly - it identified each of the 3 managers. However, when I review the new Run_Esthrs field in the table, only 2 managers show data after running the script.

[Jeff Dionne]

Re: YTD report calculation

Stan,

I figured out the problem. I initially needed to create a "manager" field in my work order table and populate it from another field. I only populated the field for work orders after 1/1/09. When I repopulated the field for all records, the script worked and populated the Run_Esthrs field for all managers.

I have over 25,000 work order records, so the script takes a couple minutes to run. Is there a line I can insert to filter it to records dated 1/1/09 or later?

Thanks.