A new client recently wanted a grid in the client info center that shows yearly revenues and billings under the projects grid in the Clients infocenter.
This grid has in it:
number of projects
It’s useful if you’re looking at a client record and quickly want to understand how much business they’ve brought your firm, and how profitable this business has been.
In order to do this, all you need to do is create a custom grid with those fields in it, then run this procedure nightly to update the data in that grid for every client.
Insert into clients_yearlyRevenue
(clientid, seq, custYear, custtotalRevenue, custBillings, custUnits, custGrossMargin, custAR,custNumberofProjects )
select TOP 100 percent cl.clientid
, cast(left(prs.period,4) as int) as ‘year’
, sum(prs.revenue) as ‘revenue’
, sum(prs.billed) as ‘billings’
, sum(prs.billedunit) as ‘billunits’
, sum(prs.grossmargin) as ‘grossmargin’
, sum(prs.AR) as ‘AR’
, count(distinct(prs.wbs1)) as ‘numProjects’
from prsummarymain prs
inner join pr
on prs.wbs1 = pr.wbs1 and prs.wbs2 = pr.wbs2 and prs.wbs3 = pr.wbs3
inner join cl on pr.clientid = cl.clientid
group by cl.clientid, cl.name, cast(left(prs.period,4) as int)
order by cl.clientid, cast(left(prs.period,4) as int)