Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Leveling out Users Data.

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

from

(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.

Here is what the Contact statement. 

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

from

(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

from

(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

from

(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 'Opportunity' as Category, o.Filterdate, o.userid, o.opportunityId as EntityId, '' as AccountId, '' as Contactid, o.opportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select OpportunityId, AccountManagerId as UserID, Case when sysdba.Opportunity.Status = 'Closed - Won' or sysdba.Opportunity.Status = 'Closed - Lost' then ActualClose else DateOpened end as Filterdate

from sysdba.Opportunity) as o

The bold here shows you the basic difference to what we have been doing so far.  Here I have used a Case statement to help me set the Filterdate based on Opportunity Status. 

Below you find the statement for Ticket:

Select 'Ticket' as Category, t.Filterdate, u.userid, t.TicketId as EntityId,'' as AccountId, '' as Contactid, '' as OpportunityId, t.TicketId, '' as HistoryId, '' as ActivityId

from

(Select ui.userid, sc.seccodeid from sysdba.userinfo ui inner join sysdba.Seccode sc on (ui.lastname + ', '+ui.firstname) = sc.seccodedesc) as u Inner join

(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


What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   SQL Stored Procedure to Create SalesLogix Table ID Values
Most of the SalesLogix systems I see now days no longer use remote users. Instead, all users VPN into a c
Posted on Dec 23, 2013 by Ryan Farley to Ryan Farley's Blog
 
   Working with the Picture Object in Crystal
We have had several inquires lately on how to dynamically change images in a report based on report
Posted on Aug 17, 2012 by George Jensen to George Jensen's Blog
 
   CFX Reports Are Updated
I am very happy to back after 19 months of Military leave.  One of my first tasks upon returning to
Posted on Jul 27, 2012 by George Jensen to George Jensen's Blog
 
   Updating text within Textblob fields in SalesLogix
I had a request to post on how you could replace characters within a blob field in the SalesLogix databas
Posted on Apr 19, 2012 by Jason Buss to Jason Buss' Blog
 
   Setting up a date range parameter and displaying it in the report
In a recent report, I needed to return data based on a date range selected. That's easy enought to p
Posted on Jul 06, 2011 by Jason Buss to George Jensen's Blog
 
Comments

 

Twitter Trackbacks for Leveling out Users Data. - George Jensen's Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Leveling out Users Data. - George Jensen's Blog         [customerfx.com]        on Topsy.com

December 1, 2010 8:54 AM
 

George Jensen's Blog said:

Over the last three posts we have updated the Quota's are of Sage SalesLogix , we have built SQL

December 1, 2010 10:31 AM
 

hallenmadona said:

I am comparing two processes on our web site and am having a hard time figuring out what distribution to use and how to calculate the confidence level that one process is perform better than the other.

www.articlesbase.com/.../xtreme-no-supplement-reviews-where-to-buy-2541278.html

December 2, 2010 12:07 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register