I have just started writing a series of posts that will lead the reader of this blog through the creation of a report with the same kind of data break down as you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board. In the first week I provided a method to effectively establishing quotas ( or goals) for your users by updating the Manage Quota area of the Sage SalesLogix LAN. I also took the time creating the functionality for the SalesLogix 7.5.3 Web. Last week I showed you a method of using SQL Views to organize that quota data for use in reporting. This week my goal is for us to gather the Actual Numbers from our system for comparison against the quotas.
Once again we are going to use a SQL View to organize the data. I am going to go a little slower today because this is going to be a little different for everyone.
The first thing we need to do is create a list of users. The UserInfo table holds that data in SalesLogix. The following SQL statement provides that list.
SELECT USERID FROM sysdba.USERINFO
To prepare to add the different totals from each of your categories we need to write this statement a different way:
SELECT
t0.USERIDFROM (SELECT USERID FROM sysdba.USERINFO) as t0
Here we are a using an embedded SQL Statement labeled “t0” to provide the user data for our SQL View. Now to add on a count of anything in the database we need to go create another SQL statement. Let’s say we want to get a count of open Activities, for the current year, for each of our users. I believe I will need three fields, the UserId (to Group By), ActivityId (gives something to count), StartDate (Qualifies record for the current year). My SQL Statement looks something like this.
SELECT COUNT(sysdba.ACTIVITY.ACTIVITYID) AS OpenActivites
FROM sysdba.ACTIVITY
where Year (sysdba.ACTIVITY.STARTDATE) = Year(GetDate())
GROUP BY sysdba.ACTIVITY.USERID
Now lets work the above statment into our SQL view to match the data to the users in the system. I will try to use colors to direct you to the portion of the statement.
Black is the original statment form above
- Add the words left join to the end fo the user embeded SQL
- Copy the entire SQL Statement for Activities in to the “From” portion of our SQL View.
- Add Open and Closed parenthesis
- Alias the SQL ( as t1 )
- Match the data to the established user. (on t0.UserId = t1.Userid)
- Add the data collected to the SQL View select portion
SELECT t0.USERID, t1.OpenActivities
FROM (SELECT USERID FROM sysdba.USERINFO) as t0 left join
(SELECT sysdba.ACTIVITY.USERID, COUNT(sysdba.ACTIVITY.ACTIVITYID) AS OpenActivites
FROM sysdba.ACTIVITY
where Year (sysdba.ACTIVITY.STARTDATE) = Year(GetDate())
GROUP BY sysdba.ACTIVITY.USERID) as t1 on t0.UserId = t1.Userid
SELECT t0.USERID, t1.OpenActivites, t2.ClosedLostOpportunities, t2.LostSalesPotential
FROM
(SELECT USERID FROM sysdba.USERINFO) as t0 left join
(SELECT sysdba.ACTIVITY.USERID, COUNT(sysdba.ACTIVITY.ACTIVITYID) AS OpenActivites
FROM sysdba.ACTIVITY
where Year (sysdba.ACTIVITY.STARTDATE) = Year(GetDate())
GROUP BY sysdba.ACTIVITY.USERID) as t1 on t0.UserId = t1.Userid left join
(SELECT ACCOUNTMANAGERID AS UserID, COUNT(OPPORTUNITYID) AS ClosedLostOpportunities, SUM(SALESPOTENTIAL) AS LostSalesPotential
FROM sysdba.OPPORTUNITY
WHERE (STATUS LIKE ‘%Closed – Lost%’) AND (YEAR(ACTUALCLOSE) = YEAR(GETDATE()))
GROUP BY ACCOUNTMANAGERID) AS t2 ON t0.USERID = t2.UserID
Here I collected a count of closed lost Opportunities, and provided a sum displaying what those Opportunities were worth. Notice in the select portion how I called in the data from the embedded SQL.
If I run this SQL View right now my data looks like this.
I do not like the null values I am seeing here. We can write a formula in Crystal to make the correction but it is much easier here using the IsNull function. IsNull is a function that is standard in SQL that provides the user the ability to handle null values like we are seeing here. This is added to the select portion of my SQL View. The syntax looks like this:
IsNull (TargetField, Value if Null) as FieldName
Here is my new Select portion of SQL view:
SELECT t0.USERID
, IsNull(t1.OpenActivites,0) as OpenActivites
, IsNull(t2.ClosedLostOpportunities,0) as ClosedLostOpportunities
, IsNull(t2.LostSalesPotential,0) as LostSalesPotential
So for us if the value returned is null then we want to see a zero and we still want the field called by it’s original name. Here are results
I hope you are seeing the value of preparing the data in SQL and why I say if you writing reports you simply need to have access to your SQL database. It makes things a whole lot easier in Crystal if you can prep your data before writing the report. I have attached the SQL View we will be using for our new report to this post for download. The challange on your end is to come up with some quotas in your system and then try and build a SQL view to pull the comparisons.
Next step, we will be bringing all of this data together and display it against our quotas in a Crystal Report. Have a great week, thanks for reading and of course “Keep Smiling”.
See how this rolled up into a Crystal Report by following this link.
Geo
