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.