Yesterday we start talking about the installation of Sage SalesLogix Visual Analyzer. As stated in our post Visual Analyzer Set Up (Part 1) we are focusing on establishing the supporting data behind the KPI tab of the Visual Analyzer.
In part one we created a list of Users who are targets for our KPI’s. We also discussed determining how far back in history do you want be able to make your comparisons. With these two items in hand you are ready to start modifying your KPI spread sheets. I will continue to use the eval database names and data but any examples are from the installation of Sage SalesLogix (SLX) Visual Analyzer (VA) against an actual clients custom database.
Today we are focusing on the Sales KPI.
There are two parts to the Sales KPI, “Actual” is the sum of all Closed – Won Opportunities to date, “Goal” is the to date yearly Goal amount as entered in the KPI Spread Sheet. In the example above I have closed one of Lee’s Opportunities worth $800.00. The Eval KPI Spread Sheet has a Goal established for Lee in the year 2008 of $1,200,000. Since this is June 2008, VA did the math and detimined Lee’s Goal to date is $600,000.
My clients were actually using the SLX standard functionality of Manage Goals. The difference was that my Client had unique goals for every month for each of thier users. I used the following SQL Statement to sum up the Goals entered in SLX so I could use them in the KPI spread sheet.
SELECT TOP 100 PERCENT SYSDBA.USERINFO.USERNAME AS [User],
SUM(SYSDBA.QUOTA.AMOUNT) AS [Yearly Goal], MIN(SYSDBA.QUOTA.BEGINDATE)
AS [Begin Date], MAX(SYSDBA.QUOTA.ENDDATE) AS [End Date]
FROM SYSDBA.USERINFO INNER JOIN SYSDBA.QUOTA
ON SYSDBA.USERINFO.USERID = SYSDBA.QUOTA.USERID
GROUP BY SYSDBA.USERINFO.USERNAME, YEAR(SYSDBA.QUOTA.BEGINDATE)
ORDER BY YEAR(SYSDBA.QUOTA.BEGINDATE), SYSDBA.USERINFO.USERNAME
This gave me the goals for the year and for 3 years previous for this client.
To update the KPI spread sheet with data with this data I followed these steps:
Log into the SLX Database as Admin.
Navigate to the VA interface by the NAV Bar button.
Along the top center of the view you will see a tool bar box with a number of icons, The second icon from the right has a drop down, select the “Manage Configuration” from the list.
The Manage Configuration view will appear. Select the “Open in Excel” button and select the KPIs.xls file from the windows open view. The KPI spread sheet should open.
Navigate to the KPIGoals(1) sheet.
The first thing I did was delete all of the data in the coulmns to the right of colum A. I then copied in the data from the SQL query into the KPIGoals (1) Spread Sheet.
I then deleted or added the value of “Sales” in column A as necessary.
Save and close the KPIs.xls file.
On the Manage Configurations view, select the “Load From Excel” button
Again the select the KPIs.xls file from the windows open view. The data sheet should now be updated with your data.
As an example I updated the evaluation KPI data under Lee for the year 2008 to $2,400,000.
The KPI dial for Sales shows the difference.
Old Lee better get to work 🙂
Next post covers the Opportunity KPI.