Customer by Contract Report

Today I have a report complementary to the previous Item on Contract Report, a report of Contract by Customer. That is, enter a given contract code, and it will return all customers using that contract, along with reporting the full pricing setup for each of those customers. In addition, is shows the sales rep assigned to each account, both by their ID and their name.

Doing this is a bit trickier than anything we’ve yet done. However, you can get the files you need, a modified contractreports.php, a modified contractreports.js, and the customer_by_contract.php file itself from bendustries.co/files/contracts/customer_by_contract.zip. contractreports.php goes to the root, while contractreports.js will go into the js directory, and customer_by_contract.php will go into /includes/contracts.

The heart of this is, of course, the query we use to retrieve the data from the SQL tables. As I mentioned, this is a little more complex than anything we’ve done thus far. The query itself looks like so:

select Customer.Account, Customer.Department, Customer.Name, CustomerPricing.BestPricing, CustomerPricing.DiscountType,
CustomerPricing.DiscountPercent, CustomerPricing.HoldDiscount, CustomerPricing.HoldDiscountDays, CustomerPricing.PricePlan1,
CustomerPricing.PricePlan2, CustomerPricing.PricePlan3, CustomerPricing.PricePlan4,
convert(varchar(10),CustomerTotal.LastOrderDate,111), CustomerSalesPerson.UserId, [User].Name
from (Customer left join (
CustomerPricing left join CustomerTotal on CustomerPricing.CustomerId = CustomerTotal.CustomerId
) on Customer.Id = CustomerPricing.CustomerId
) left join (
[User] left join CustomerSalesPerson on [User].Id = CustomerSalesPerson.UserId
) on CustomerSalesPerson.CustomerId = Customer.Id
where ((CustomerPricing.PricePlan1 = (?)) or (CustomerPricing.PricePlan2 = (?)) or
(CustomerPricing.PricePlan3 = (?)) or (CustomerPricing.PricePlan4 = (?)))
and CustomerPricing.OEExempt = ''
and CustomerSalesPerson.UserType = 'PRIMARY'
order by Customer.Account asc, Customer.Department asc

We start by left joining CustomerPricing to CustomerTotal on CustomerId and left join Customer to that pair, again on CustomerId. Finally, we left join User to CustomerSalesPerson on UserId and left join the whole set together using CustomerId again.

There are a couple other things to see here. First are the square brackets around User. This is because ‘User’ is a keyword for a SQL System Function. To make SSMS understand what we’re asking for, we surround the table name in square brackets. There are those that surround all table names in square brackets as a matter of habit, and there’s nothing wrong with that. For me, I prefer a more streamlined approach, and only use them when I have to.

The other thing to see are my where clauses. First is an exclusive check if our contract code resides in one of the four slots. Surrounding them with parentheses the way we have will return a match in any of the four columns. Next we’re checking that CustomerPricing.OEExempt is empty. This returns results only on account that aren’t closed. You could pull this condition to return all customers, but in that case it wouldn’t be a bad idea to add this column to your results. That way you can see at a glance if the account is open. The UserType clause forces the query to only return the Primary Salesperson on the account. If you have two reps assigned to an account, it will double up the results for each record, returning both the Primary and Secondary salespeople. As I’m only interested in the Primary salesperson, I exclude the other results.

Pick a contract and hit the View button. Your results should look something like this:

.
Click for full size

It shows account number, dept code, customer name, pricing structure of discount, minimum margin, and all contracts, last purchase date, and the assigned sales rep. You could easily add or remove columns, of course. If you add much to it, though, the columns squeeze down a bit far, making the report hard to read. for reports much larger than this, or with larger columns, a post to come a bit later will show a better way for larger reports.

So here you have Customer by Contract. It provides you an easy means to see all the customers using a particular contract, along with the rest of their pricing and their assigned sales rep. I hope this provides useful information to you. If you need my assistance, you can find me at .