I am four posts into building a drill down report using Sage SalesLogix Visual Analyzer and Dashboard as a my guide.  This series of post starts with a post labeled “Updating the Managed Quota Area for Sage SalesLogix” , if you follow the links on each post you will eventually end up back on this post. In the last post we started building our report which gave us our performance numbers for comparison. Today, we are going to go back into SQL Server Management Studio and build a view that will allow us to level out all of the user related data.  This will allow us to display in one group the Accounts, Contacts, Opportunity, Ticket, History, and Activity data related to the user.

What we will use to bring all of this data together from six different tables is a well designed SQL view that uses a Union join.  This join allows you to combine data from several select statements into one view.  The key to a union join is each Select statement has the same number of columns labeled in the exact same order.  So what columns do we need in our view? 

• 
  
  An obvious answer is a way to relate each of the records from the previously mentioned tables to the user. 


• 
  
  There also should be a way to categorize the record to tell us what type of record we are creating. 


• 
  
  Thinking ahead, I want to include a column which will allow us to filter our report by a data range.


• 
  
  We need one column that will list each table primary id that will indicate a record does exist for the user and will allow us to group the records under the category.  


• 
  
  Finally, we will need a column for each of the table primary id, this will allow you to join to the table and show the detail from the record under the category group. 

So lets layout our columns:

• 
  
  Category


• 
  
  FilterDate


• 
  
  UserId


• 
  
  EntityId


• 
  
  AccountId


• 
  
  ContactId


• 
  
  OpportunityId


• 
  
  TicketId


• 
  
  HistoryId


• 
  
  ActivityId

The first step is to create the Account select Statement.  This is what I came up with:

Select ‘Account’ as Category, a.Filterdate, a.Userid, a.AccountId as EntityId, a.AccountId, ” as Contactid, ” as OpportunityId, ” as TicketId, ” as HistoryId, ” as ActivityId

(Select Accountid, AccountManagerId as UserId, CreateDate as Filterdate from sysdba.Account) as a

In the “Select” part of our statement you see the list of our columns.  “Account” is the Category of the record, this will change as we go.  If you look at the prefix to each of the other columns you see an “a“.  Go down and look at the “From” portion of the statement.  There you will see an embedded SQL statement that organizes our data from the Account table in a manner similar to a view.  We provide an alias to this set of data and call it “a“.  Back up in the “Select” portion of our statement, if we could not derive supporting data out of our embedded SQL we simply added a blank value ( like ” as Contactid).  Notice one other thing, we are using the AccountId twice once as the EntityId and once as the AccountId, this pattern will be followed through the process of creating our view.

Select ‘Contact’ as Category, c.FilterDate, c.userid, c.Contactid as EntityId, ” as AccountId, c.Contactid, ” as OpportunityId, ” as TicketId, ” as HistoryId, ” as ActivityId

(Select ContactId, AccountManagerId as userid, CreateDate as filterdate from sysdba.Contact) as c

I bolded the changes that differ from the Account set of data.  All of the column headings are the same but “Contact” is now the Category,  the prefix for each field is now “c“, the Contact is now our target table in the embedded SQL Statement where we are getting are data from, and ContactId now is the EntityId and is populating the ContactId column.  Also, notice that Accountid is pulling in blank value.

In the SQL view we are building, the word “Union” separates the two select statements.  See how this is done below and compare the two scripts.    

Select ‘Account’ as Category, a.Filterdate, a.Userid, a.AccountId as EntityId, a.AccountId, ” as Contactid, ” as OpportunityId, ” as TicketId, ” as HistoryId, ” as ActivityId

(Select Accountid, AccountManagerId as UserId, CreateDate as Filterdate from sysdba.Account) as a

UNION 

Select ‘Contact’ as Category, c.FilterDate, c.userid, c.Contactid as EntityId, ” as AccountId, c.Contactid, ” as OpportunityId, ” as TicketId, ” as HistoryId, ” as ActivityId

(Select ContactId, AccountManagerId as userid, CreateDate as filterdate from sysdba.Contact) as c

I am aware that the embedded SQL might be a  little over kill for my Account and Contact examples.  You could also have the “From” portion of the statement looking like this ” From sysdba.Account a”  The embedded SQL allows you to build and organize your data in support of the main select statement.  Take a look at the example below using the Opportunity portion of the view:

(Select TicketId, AssignedToId, ASSIGNEDDATE as filterdate from sysdba.Ticket) as t on u.seccodeid = t.AssignedToId

This one is a Little more difficult because I want to only bring in Tickets that are assigned to the user.  Since Tickets can be assigned to either a User or a Team we have to figure away of relating back to the user.  Instead of the UserInfo table we have to reference the SECCODE table.  If you did a query against the table you would discover that all users have a SECCODEID assigned to them and this is what is being stored as the AssignedToId.  The SECCODEDESC actually lists the user name in “LastName, FirstName” format.  I played around here a little bit and discovered that I could build a join using the UserInfo.Lastname and UserInfo.Firstname in a formula to match against the SECCODE.SECCODEDEC field.  The formula looks like this (ui.lastname + ‘, ‘+ui.firstname)  = sc.seccodedesc.  Doing this allowed me to then use the USERINFO.USERID to get the Assigned to user for a Ticket.

When I run the statement the data looks like this:

 I have attached the example I am using for my drill down report to this post.

What we have accomplished might still be a little confusing to some of you but you will see the benefit in the next post where I will add this view to our Crystal Report and start to build our drill down.  We will see this style of view building one more time as we build the bottom level of the drill down.

Thanks for Reading, Keep Smiling – Geo