Gathering User Quotas for Reporting

If you read last weeks post, you will know I am setting up to build a user based report that will give the reader a lot of the same data you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board.  Last week I provided you the ability to effectively set goals for your users by updating the Manage Quota area of the SalesLogix LAN and actually creating the functionality for the SalesLogix 7.5.3 Web.  This is available to you via the bundles attached to the post.  This week I am providing you a means of grabbing the quota’s for each user out of the SalesLogix database by providing you an example of a SQL view. 


But first, I have added some text to the “About This Blog” section that I will reference from time to time just to remind everyone of my goals in delivering information to you through this blog, please feel free to click on this link to read. 


So with the bundles from last week in place I have added some Quotas for some of the users in my eval system.  The below screen shot gives you the examples that I have established.



Lets look at the data in the Quota Table. 


  


As you can see, for every user and for each type of Quota there is a row of data, this means multiple rows of data per user. Now, if all I was asking Crystal to do was organize this data it could handle it with no problem simply by building a group based on the UserID and pulling in the fields I want displayed.  Our report is going to be much more complex and this just an element of the data we are going to pull to roll up under a user.  We need this data to be laid out so each user has one row of data, with a column for each Type of Quota that displays the  value enterd for that Type.  Below you will find an example of a SQL view that can be added to your system to draw out and bring the data together the way I described above:


Create View sysdba.CFX_UserPeriodReview_Goal
as


SELECT DISTINCT t1.UserID,
IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,

IsNull(t3.NumbOpenActivities,0) as NumbOpenActivities,
IsNull(t4.NumbOpenTickets,0) as NumbOpenTickets,
IsNull(t5.TotalYearlySales,0) as TotalYearlySales,
IsNull(t6.TotalWeightedPipeline,0) as TotalWeightedPipeline


FROM (SELECT USERID AS UserID  FROM sysdba.USERINFO) AS t1 INNER JOIN
    
(SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
 WHERE (QUOTATYPE = ‘Numb Open Opportunites’) GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN

    
(SELECT USERID, SUM(QUANTITY) AS NumbOpenActivities FROM sysdba.QUOTA
 WHERE (QUOTATYPE = ‘Numb Open Activities’) GROUP BY USERID) AS t3 ON t1.UserID = t3.USERID INNER JOIN

    
(SELECT USERID, SUM(QUANTITY) AS NumbOpenTickets FROM  sysdba.QUOTA
        WHERE (QUOTATYPE = ‘Numb Open Tickets’) GROUP BY USERID) AS t4 ON t1.UserID = t4.USERID INNER JOIN
    
(SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
        WHERE (QUOTATYPE = ‘Total Yearly Sales’) GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN

    
(SELECT USERID, SUM(AMOUNT) AS TotalWeightedPipeline FROM sysdba.QUOTA
        WHERE      (QUOTATYPE = ‘Total Weighted Pipeline’) GROUP BY USERID) AS t6 ON t1.UserID = t6.USERID


 


This is not plug and play SQL view, you have to customize this script to match the quota types that are set in your system.  There is a lot going on here and I will try to explain this a bit so you understand. 


We have two major components to this view: 


A “Select” portion…….a


            SELECT DISTINCT t1.UserID,
             IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,
             IsNull(t3.NumbOpenActivities,0) as NumbOpenActivities,

             IsNull(t4.NumbOpenTickets,0) as NumbOpenTickets,
             IsNull(t5.TotalYearlySales,0) as TotalYearlySales, 
             IsNull(t6.TotalWeightedPipeline,0) as TotalWeightedPipeline


 and a “From” portion.


            FROM (SELECT USERID AS UserID  FROM sysdba.USERINFO) AS t1 INNER JOIN
    
             (SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
             WHERE (QUOTATYPE = ‘Numb Open Opportunites’) GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN

    
             (SELECT USERID, SUM(QUANTITY) AS NumbOpenActivities FROM sysdba.QUOTA
             WHERE (QUOTATYPE = ‘Numb Open Activities’) GROUP BY USERID) AS t3 ON t1.UserID = t3.USERID INNER JOIN

    
             (SELECT USERID, SUM(QUANTITY) AS NumbOpenTickets FROM  sysdba.QUOTA
             WHERE (QUOTATYPE =
‘Numb Open Tickets’) GROUP BY USERID) AS t4 ON t1.UserID = t4.USERID INNER JOIN
    
             (SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
             WHERE (QUOTATYPE = ‘Total Yearly Sales’) GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN

    
             (SELECT USERID, SUM(AMOUNT) AS TotalWeightedPipeline FROM sysdba.QUOTA
             WHERE      (QUOTATYPE = ‘Total Weighted Pipeline’) GROUP BY USERID) AS t6 ON t1.UserID = t6.USERID


 


Look at the 1st Column of the “Select” portion:


t1.UserID,


Now the first part of the “From” portion of the view statement supports the first columns by providing SQL a list of all usersid in the system. 


FROM (SELECT USERID AS UserID  FROM sysdba.USERINFO) AS t1 INNER JOIN 


 


Now, look at the 2nd column in the “Select” portion of the statement:


IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,


This is saying give me a row of data with a label of NumbOpenOpportunites.  This part of the select statement is matched to an embedded SQL statement in the “From” portion of the view statement to draw just that value out of the quota table for the user.  Looking at the embedded SQL for this column it reads:


(SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
 WHERE (QUOTATYPE = ‘Numb Open Opportunites’) GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN


This is drawing data out of the Quantity field in the Quota Table where we set the Type for that row as ‘Numb Open Opportunites’.  As you can see this statement organizes the data just the way we want it, a User with a named column displaying a value



To make it easy, think of this as a table of data for SQL to use while it is building the view, this particular table was named “t2”. 


Look at the very last part of the embedded SQL:


t1.UserID = t2.USERID INNER JOIN


This matches the data to the established UserId in the 1st column, The “Inner Join” cleans out our view a bit because only records from the user table that have records in the t2 table will display records in our SQL view. 


 


The rest of the our SQL view is adding on the different columns of data based on the Quota Types.  For each column in the “Select” portion there is a supporting embedded SQL in the “From” portion.  Look at the 4th column from the “Select” portion of  SQL View Statement :


 IsNull(t5.TotalYearlySales,0) as TotalYearlySales,


This columns uses “Amount” field and provide us our currency values.  The matching Embedded SQL looks likes this:


(SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
        WHERE (QUOTATYPE = ‘Total Yearly Sales’) GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN


The only real difference is instead of “Quantity” field we are using “Amount” field.  The rest of the embedded SQL is basically the same.


Ok, to wrap this up the SQL view statement provided today is an example of how to prepare the data in the Quota table for use in a SQL report.  I will use the above element of the SQL View Statement to help me explain what you need to modify in the view to make it work for you and your custom quota Types.  Colors should match up to the section you need to modify




  • You will need to customize the embedded SQL to your custom quota types


  • Select whether the value you want is currecny (Use the Amount Field) or a number (Use the Quantity field). 


  • The label for the Column in the view must match in both the Select Portion and the embedded SQL in the From portion of the SQL view.

Take your time while doing this.  Remember your are building a view, you are not effecting your data or hurting your system so do not feel like you are breaking anything.  SQL is one great tool and I have a lot of fun playing around and seeing what I can do.  One other thing, fight for the right to have access to your SQL data.  You are the report writer, you cannot really write great reports without access to the wonderful world of SQL.


Thanks for reading.  Next time we pull together the data to compare these quotas against. Click Here


Keep Smiling,  Geo


 


 


 


 


 

Submit a Comment

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

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!