Many routines can either be done nightly using the SQL Agent or in Vision using a Workflow. The biggest advantage of the Vision workflow is that it can be done immediately after an InfoCenter is created or a particular field is updated, the downside is that the extra workflows can slow down Vision and take extra time for saves. For fields that don’t need to be viewed immediately after an InfoCenter item is created, then a nightly update can be a good solution. Besides reducing the impact to Vision, nightly routines can also be more consistent in completing fields, since they don’t need to rely on a particular action being performed in Vision. For this example the number of projects and amount of revenue that’s been generated for a given client will be updated for an Opportunity, which was described in the Opportunity Risk Calculator.

To begin using nightly updates the SQL Server Agent will need to be active on your sql server. Once it’s active simply right click on Jobs and create a New Job.

 

Setting up a job just requires 3 things, a name, a schedule and steps to perform, just give the Job a name a then Click Schedules.

On the Schedule tab click new to create a schedule, note you could have multiple schedule for a job.

For this example the Job will run nightly, so first give it a name, and then from Frequency under Occurs choose Daily and choose Occurs once at, and select a time for the job to run and then click OK. So this job would run every night at 1am. If you just wanted a job to run once a week, say on a weekend, you could choose Occurs: Weekly and then check the day or days for the job to run.

Now click on Steps and choose new.

 

Now in the new Job Step all you need to do is give the step a name, choose the database you want the run the sql code on and then enter the sql code in the command blank. In this example the code will update opportunity project counts and revenue for the client.

 

To total up the number of projects that have been performed for a client, a sub select statement is run to total up all the projects using count(wbs1) and then filtering on the chargetype as being regular/billable. That is then it used to join the opportunity info center on client and then then where is used to join on the opportunity using OpportunityCustomTabFields.opportunityid = op.opportunityid.

 

update
OpportunityCustomTabFields

set
custTotalofProjectsPerformedforclient
=
projcount

From

(

select
cl.clientid,
count(pr.wbs1)
as projcount

from
cl

inner
join
pr
on
pr.clientid= cl.clientid and pr.wbs2= and pr.chargetype =
‘R’

group
by
cl.clientid)
as x

inner
join
opportunity
op
on
x.clientid = op.clientid

where
OpportunityCustomTabFields.opportunityid = op.opportunityid

 

The total revenue is updated again using a sub select to sum up the revenue using the PRSummaryMain, this is updated using the Advanced Utility, Refresh PRSummary Table, so make sure you have that scheduled in Vision to run nightly. This table has lots of useful summary information including revenue.


 

After the sub select is run, it’s again joined to the opportunity on clientid and then from the opportunity to the customtabfield table using the where on opportunityid.

 

update
OpportunityCustomTabFields

set
custTotalPriorRevenue
=
revjtd

From


(

select
clientid,
sum(Revenue) as revjtd
from PRSummaryMain

inner
join
pr
on
PRSummaryMain.wbs1= pr.wbs1

where
pr.wbs2=

group
by
clientid

)
as
X

inner
join
opportunity
op
on
x.clientid = op.clientid

where
OpportunityCustomTabFields.opportunityid = op.opportunityid

 

The job can be saved now. One thing to note on the Advanced tab is the next step actions on the job. If you’re running multiple steps in one job, you may wish the se the failure action to continue, rather than quite.

 

 

update OpportunityCustomTabFields

set custCurrentPSpent = cost

from (

select wbs1,
sum(cost)
as cost from

(

select pr.wbs1,
Sum((RegHrs*Rate)+(OvtHrs*OvtRate))
as cost

from pr

inner
join LD on ld.wbs1 = pr.wbs1

where pr.wbs2 =

and pr.chargetype =
‘P’

group
by pr.wbs1

union

select pr.wbs1,
isnull(sum(Amount),0)

from pr

inner
join ledgerAP on ledgerAP.wbs1 = pr.wbs1

where pr.wbs2 =
and pr.chargetype = ‘P’

group
by pr.wbs1

union

select pr.wbs1,
isnull(sum(Amount),0)

from pr

inner
join ledgerEX on ledgerEX.wbs1 = pr.wbs1

where pr.wbs2 =
and pr.chargetype = ‘P’

group
by pr.wbs1

)

as y

group
by wbs1

)
as x, Opportunity op

where op.PRProposalWBS1 = x.wbs1 and op.OpportunityID = OpportunityCustomTabFields.OpportunityID

 

 

update OpportunityCustomTabFields

set custCurrentPHours =
hours

from (

select wbs1,
sum(hours) as
hours
from

(

select pr.wbs1,
Sum((RegHrs+OvtHrs))
as
Hours

from pr

inner
join LD on ld.wbs1 = pr.wbs1

where pr.wbs2 =

and pr.chargetype =
‘P’

group
by pr.wbs1

)

as y

group
by wbs1

)
as x, Opportunity op

where op.PRProposalWBS1 = x.wbs1 and op.OpportunityID = OpportunityCustomTabFields.OpportunityID


Leave a Reply

Your email address will not be published. Required fields are marked *