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:
year
number of projects
revenue
gross margin
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.
create procedure pr_refresh_clients_yearlrevenue
as
BEGIN
truncate table clients_yearlyRevenue
Insert into clients_yearlyRevenue
(clientid, seq, custYear, custtotalRevenue, custBillings, custUnits, custGrossMargin, custAR,custNumberofProjects )
select TOP 100 percent cl.clientid
, replace(newid(),’-‘,”)
, 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)
END
In order for this to work, you have to make sure that you’re using the “refresh summary tables” in Vision, which refreshes the project summary tables.
It only takes an hour or less to implement and is a useful tool for management or sales to view how much business a particular client has brought in without running a report.