Login / Register  search  syndication  about

          George Jensen's Blog

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

Writing the User - Quota vs Actual Crystal Report.

Over the last three posts we have updated the Quota's are of Sage SalesLogix, we have built SQL views to collect both the Quota data and the Actual Numbers for comparison.  Now we will start writing our report.  I am not going into to much detail on layout.  My goal is to walk you through the common steps:

  • connecting to the database
  • selecting our tables and views
  • establishing the group
  • adding the fields to the report.

Since we know this report is going to grow we need to accomplish all of this in a manner that will support expansion.

The first step in any report is making the connection.  When you select the "New Report" icon (  ) located on the upper left hand corner of the Crystal interface, the Standard Report Creation Wizard will appear. I have two established posts that will talk you through the creation of connection to a SalesLogix SQL database.  One labeled Report Database Connection talks you through using the "Microsoft OLE DB Provider for SQL Server" and recently published post labeled Crystal Connection through the SalesLogix OLE DB Provider talks you through using the SalesLogix OLE DB Provider.  

With your connection to your database made and selected we are ready to select the tables and views we will need to support our report.  In the "Available Data Sources" tree control on the left you should find your connection.  Clicking on the plus (+) sign to the right of your connection will allow you drill down and find a value of "sysdba". Drilling into "sysdba" are two more listings, one each for Tables and for Views.  Under "Tables" find and select the UserInfo table.  Under "Views" you should find your custom SQL views that we built for this report in the previous posts (Quota data and the Actual Numbers ).  My views are labeled CFX_UserPeriodReview_Goal and CFX_UserPeriodReview_Actual.

With your tables and view lets move on to linking the tables so our data flows correctly.   Select the "Next" Button.

When the Link portion of the Standard Report Wizard opens, I usually just go ahead and click the "Clear Links" button right away.  This proved true with this report also, crystal through a SQL error when I tried using the default links.  With the links deleated and since the USERINFO table is the base table for this report, I moved it all the way to the left side of the screen.  After moving the views to the right side of work area, I linked the USERINFO table to the CFX_USERPERIODREVIEW_GOAL and CFX_USERPERIODREVIEW_ACTUAL views, using the UserId field as my link between the entities.

Usually I change the default join of inner to left to ensure all records display even if a related table does not have data,.  Because we only want those users who have Quotas entered in the system we will leave our join types set to the default.

With my tables and views selected and joined let's finish the wizard by selecting USERINFO.USERNAME in each of the following two screens and select finish.  What this did was establish our first group, we selected the same field twice just to get through the field selection section of the wizard and were able to create that first group.   When I selected "Finished" this is what I was presented with. 

I know it looks pretty ugly right now and like I said, I am not going to get into to much detail on how to make it look better.  Here is basically what I did to bring this report around. 

  • I suppressed the Detail and Group1Footer Sections of the report. 
  • I added another section in GroupHeader1. 
  • In GroupHeader1a, I moved the username all the way to the upper left hand corner and collapsed in the rest of the section leaving just enough room to display the field. 
  • In GroupHeader1b, I placed all of the fields from the views. 
  • I combined each of the fields with labels to help me make sense of what the field is displaying. Make sure to format the dollar values before combining the field with the label. 

This is a screen shot of what I came up with:

The first part of our Drill Down Report is complete. 

Next, we have to come up with away of displaying all of the primary entities (Account, Contact, Opportunities, History, Activities, and Tickets) in one group.  I can hear you scratching your head.  If you remember, the overall idea of the report we are building is to be able to drill down into our data.  The idea is to display just enough information to let you know what is going on for a user.  If something peaks your interest, you will be able click on a record and be provided with more detail.  A user of your system has references in each of those primary entities.  So how do we display that information so it will all roll up under each of our user individually, but all in the same group.  We will dig into this next time.  See how here!

I have zipped up and attached the report displayed above.  The report should install into any system where the untouched views provided in previous posts (Quota data and the Actual Numbers ) have been installed.  Remember, if you are running this report outside of SalesLogix, to re-set the database location through the Database\Set Datasource Locations menu item. 

Thanks for reading, have a Happy Thanksgiving, of course please travel safely,  and most of all keep smiling.





What's This?
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.

Related Content
   Using an Array - SQL vs Crystal.
In data, an Array is used to store multiple values in a single variable.  I often use Arrays to qual
Posted on Jul 17, 2014 by George Jensen to George Jensen's Blog
   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


Twitter Trackbacks for Writing the User - Quota vs Actual Crystal Report. - George Jensen's Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Writing the User - Quota vs Actual Crystal Report. - George Jensen's Blog         [customerfx.com]        on Topsy.com

November 23, 2010 11:59 AM

Gathering the Actual Numbers for Reporting Comparison - George Jensen's Blog said:

Pingback from  Gathering the Actual Numbers for Reporting Comparison - George Jensen's Blog

November 23, 2010 3:24 PM

Leave a Comment

All contents Copyright © 2015 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