The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniPlan > OmniPlan Extras
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Cash-flow reports Thread Tools Search this Thread Display Modes
Hi everyone,

My company works with very big projects (>2500) and we needed to export the project costs grouped by year and month to be used as a expenditure-flow (or "cash-flow") data within MS Excel so we could negotiate with our clients easily...

I was doing this using CSV, TextMate and MySQL to export, transform and process this data. But after asking the wonderful folks at omnigroup and doing some research on the net, I came up with the following AppleScript:

Code:
tell application "OmniPlan"
	set CurrentCashFlowDates to {}
	set CurrentCashFlowValues to {}
	repeat with CurrentTask in tasks of front document
		if task type of CurrentTask is standard task and type is not "group task" then
			set CurrentTaskEndDate to (ending date of CurrentTask)
			set CurrentTaskTotalCost to (total cost of CurrentTask)
			set CurrentCashFlowDate to (year of CurrentTaskEndDate as integer) & "-" & (month of CurrentTaskEndDate as integer) as string
			set CurrentCashFlowValue to CurrentTaskTotalCost as real
			if CurrentCashFlowDates contains CurrentCashFlowDate then
				repeat with id from 1 to the count of CurrentCashFlowDates
					if item (id) of CurrentCashFlowDates is equal to CurrentCashFlowDate then
						set item (id) of CurrentCashFlowValues to (item (id) of CurrentCashFlowValues as real) + CurrentCashFlowValue as real
					end if
				end repeat
			else
				set the end of CurrentCashFlowDates to CurrentCashFlowDate as string
				set the end of CurrentCashFlowValues to CurrentCashFlowValue as real
			end if
		end if
	end repeat
	set CurrentCashFlowFileContents to "" as string
	repeat with id from 1 to the count of CurrentCashFlowDates
		set CurrentCashFlowFileContents to CurrentCashFlowFileContents & (item (id) of CurrentCashFlowDates) & "	" & (item (id) of CurrentCashFlowValues) & "
"
	end repeat
	set CurrentCashFlowFile to open for access file "~/Desktop/cashflow.csv" with write permission
	write CurrentCashFlowFileContents to CurrentCashFlowFile starting at eof
	close access CurrentCashFlowFile
end tell
I hope it can help someone searching for a solution like this...

Best Regards,
Bruno B B Magalhaes
 
Just what I was looking for. Thanks for the code.

The path name wasn't working for me, so I made a minor addition to use the Finder and the project name:

Code:
tell application "Finder"
	get path to desktop folder from user domain
	set theResultsFile to (result as string)
end tell

tell application "OmniPlan"
	set CurrentCashFlowDates to {}
	set CurrentCashFlowValues to {}
	set theResultsFile to the theResultsFile & (title of front document) & "-CashFlow.csv"
....
 
Great, but completely inaccurate results :-)
Back to MS Project...
 
I know this is an old post but does anyone have an updated script that works?
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes



All times are GMT -8. The time now is 08:59 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.