George Jensen’s Blog

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

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 qualify records for the query I am working on in either SQL or Crystal. The problem is the Arrays are formatted differently in each environment and if you are like me, it can be difficult remembering the exact format for each reporting tool.  You can find many examples of Arrays for each environment using general web searches but I thought it would be nice to bring the two formats differences together in one post as future reference for me and in the hopes of helping someone else out in their reporting efforts. An Array in SQL is formatted like this:  TABLE.FIELD in ( ‘Value1′,’Value2′,’Value3′,’Value4’) In Crystal, the format is this:  TABLE.FIELD in [“Value”,”Value”,”Value”,”Value”] I hope this helps and as always, keep smiling! George...

Using a Test Plan

So you have approved the Statement of Work (SOW) for some modifications and are awaiting the delivery of the bundles from Customer FX. The next item you should be working on is a solid test plan. There are several ways to create a test plan and my intent is not to guide you onto any one method but to encourage you to create and use some type of plan. CFX has very limited insight as to how your organization uses Saleslogix. We create our scope documents based on the information you provide during the planning of the modifications. Our testers use those same documents to make sure the customization you requested are working as documented. Only your organization has a complete understanding on how the modification is intended to be used. A well written plan created soon after the SOW will help maintain the scope of what is trying to be accomplished. Often we see organizations go into beta testing like kids in a candy shop. The issues reported have little to do with the modification and more to do with “it would be nice if it also did this” or “shouldn’t we also be able to do that”. Reporting these types of issues will change the scope of the project. A good test plan helps your team stay on track with what needs to be tested and helps you evaluate what issues are to be reported. Also, using a test plan ensures the same tests are run for each bundle revision and provides an easy way of documenting your progress through testing. A planned approach will make your...

What Do We Do With Those Backup Database Files?

When working with the team at Customer FX, more often than not we are going to ask for a current back copy of your production system and I hope this blog will help you understand why. Using a recent version of the database lowers the risk of Customer FX overwriting any customizations that you have in the system. Also, when you report an issue to Customer FX it helps if we can log in using the credentials of the user and have access to the same records the issue is occurring with. Obviously, the newer the version we have the easier it is for us to help you.  Some things you should know – when we request this backup file we will provide you instructions with access to a secure folder up on customerfx.sharefile.com.   We are notified by the site when you have uploaded the file and our very first priority is to download and delete the file from the site.  Once we have the backup file we will create two separate environments, one development and one test.  These environments are meant to replicate, as close as possible, the production environment.  As we work to develop a solution you have requested, we do all of the work in the development environment.  Once we complete our development effort we will package up the deliverable and document any additional steps needed for installation.  We will use the test environment to prove the installation procedures are accurate and ensure the deliverable package is complete and working as requested.  Often, before we deliver a solution we will contact you and demonstrate what is...

SalesLogix Web Form Designer

New to the Sage SalesLogix v8.0 Web Client is the Web Form Designer. This tool will allow a user, with a proper role assignment, the ability to modify main entity forms in the the web client using existing database fields. Here is a quick introduction to the tool from Customer FX. For this demo I am using a Sage SalesLogix v8.0 RC3 Web Client with an evaluation database set up in the Cloud.  I found the information on this tool using the SalesLogix 8.0 Web Client Help Files with the key word search of  “Working with Forms”.  The number one listing in the results are will be “Working with Forms”. I found these files extremely helpful and accurate while I learned to use this tool. In order to use this tool you must be logged in as the Admin user or a User assigned to the role of “Administrator”. I set our user Lee Hogan up in my system with the Administrator Role.   There are two ways to bring up the Web Form Designer tool.  The first is to go through the Administration Navigation panel and find the Form Manager icon:    This icon opens the Web Form Manager with a list of all forms in the system that are editable:    Using this area, if you select the form you want to edit it will display in the Web Client ready for editing. The better option is to use the icon that you will find on the tool bar of the form that you want to edit.  This option opens the form in another browser window separate...

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 data.   For example you have pictures of products that you want to display in a report.  One way to accomplish this is to add an image with each product in a huge segmented report and have Crystal sort things out through suppression rules.  This is a valid solution but the report is going to become very unmanageable as your inventory grows and than changes.  The best case is to store the picture in a central folder and call the image into your report based on the data. With SalesLogix you have the ability to store attachments to records which can be be documents, spreadsheets, pdf’s, or pictures.  This provides a perfect area for managing images.  But – the standard Products area in SalesLogix does not have the attachment capability. I came up with an easy work around that will allow you to create a Product Report that displays the correct picture that you maybe able to relate to a report that you are working on. I am using the evaluation database for a SalesLogix version 7.5.4 system.  This database is owned by a company called “Phoenix Computers, Inc.”  and comes with product records we can use for our example. The first thing I need is some pictures of some of the products.  I searched on Google Images for photos of a some common products that are already in the database.  I found images for the  “BlackBerry”, the “Dell Latitude”, the “Dell Optiplex”, and the “MacBook Pro”.  The pictures are all stored as .jpg I went to the Phoenix Computers, Inc account record and added the pictures to...

CFX Reports Are Updated

I am very happy to back after 19 months of Military leave.  One of my first tasks upon returning to my desk was to clean up my Customer FX email inbox that was full with a about 4,000 new emails, mostly automatic notifications from various resources.  What surprised me the most was the number of download notifications from Box.net which is where we store all of the SalesLogix reports that were refreshed through this blog.  Seeing that these reports are still being downloaded and with Sage SalesLogix (SLX) currently at 7.5.4 with the release of 8.0 just around the corner, I thought I should take a look at all of the reports and ensure they are still working.  I set up a SLX 7.5.4 test environment and reviewed each report one by one.  Most of them worked just fine but I did find somethings to adjust on 9 of the reports.  Use this link as a guide to all of our updated reports.  The ones that were updated are marked by the words Updated 07/20/2012. I will share some of the things I learned while doing this review during the next few weeks. Thanks...

Supressing the blank page after “New Page After” Group Footer setting

I've worked with Crystal for some time, but there's always been one little thing that sort of bothered me, and I had never took the time to figure out how to deal with it.

If you have a group in your report, and want to start a new page after each grouping, you end up with a blank page at the end of the report. To get rid of this blank page, all you need to do is to add a simple script to the New Page After property of the group footer:

Setting custom date ranges in Crystal

Recently, I had to develop a report which was to return data within a particular date range. Specifically, a user would need to select a date and the report would then return data from a range of Monday of the previous week to Friday of the current week. I was able to accomplish this fairly easily using Global and Local variables and a couple of different date functions.

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

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

Crystal Connection through the SalesLogix OLE DB Provider

The first step in creating a report in Business Object Crystal Reports is always the connection to the database.  In a post labeled “Report Database Connections”, I discuss one method of making a connection to a SQL database.  That post will lead you through how to make the connection using the Microsoft OLE DB Provider for SQL Server using the sysdba login and password.   There is also a SalesLogix OLE DB Provider and since this is the primary way of a making a connection to SalesLogix data, I thought I would take the time to talk you through making this connection  for Crystal Reports.  What is required to make this connection is the SalesLogix Admin Password, the location of the SalesLogix Connection Manager, and the SalesLogix Connection name.  If you know or can obtain the password the rest is easily accessible by logging into the SalesLogix Client. Instead of logging in, click on the button located to the right of the “Log on to:” field.  This opens the Data Link Manager.  Usually you will just see one or two database connection listed here.  Select the database that you want to report against out of the list and then click on the “Edit” button.     This action will open the Data Link Properties window. Now you will see the SalesLogix Server Name where the SalesLogix Connection Manager is located  for your system ( for me it is Test 82), and the connection name given to the database you want to connect to (CFX_Reporting Class).   The password may be visible but it will be encrypted. Armed with this information, let’s open Crystal Reports.  When Crystal opens, select the New Report icon located in the upper right hand...

Gathering the Actual Numbers for Reporting Comparison

I have just started writing a series of posts that will lead the reader of this blog through the creation of a report with the same kind of data break down as you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board.  In the first week I provided a method to effectively establishing quotas ( or goals) for your users by updating the Manage Quota area of the Sage SalesLogix LAN.  I also took the time creating the functionality for the SalesLogix 7.5.3 Web.  Last week I showed you a method of using SQL Views to organize that quota data for use in reporting.  This week my goal is for us to gather the Actual Numbers from our system for comparison against the quotas.  Once again we are going to use a SQL View to organize the data.  I am going to go a little slower today because this is going to be a little different for everyone.  The first thing we need to do is create a list of users. The UserInfo table holds that data in SalesLogix. The following SQL statement provides that list.  SELECT USERID FROM sysdba.USERINFO To prepare to add the different totals from each of your categories we need to write this statement a different way: SELECT t0.USERID FROM (SELECT USERID FROM sysdba.USERINFO) as t0 Here we are a using an embedded SQL Statement labeled “t0” to provide the user data for our SQL View.  Now to add on a count of anything in the database we need to go create another SQL statement.  Let’s say we want to get a count of open Activities, for the current year, for each of our users.  I believe I will need three...

Gathering User Quotas for Reporting

If you read last weeks post, you will know I am setting up to build a user based report that will give the reader a lot of the same data you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board.  Last week I provided you the ability to effectively set goals for your users by updating the Manage Quota area of the SalesLogix LAN and actually creating the functionality for the SalesLogix 7.5.3 Web.  This is available to you via the bundles attached to the post.  This week I am providing you a means of grabbing the quota’s for each user out of the SalesLogix database by providing you an example of a SQL view.  But first, I have added some text to the “About This Blog” section that I will reference from time to time just to remind everyone of my goals in delivering information to you through this blog, please feel free to click on this link to read.  So with the bundles from last week in place I have added some Quotas for some of the users in my eval system.  The below screen shot gives you the examples that I have established. Lets look at the data in the Quota Table.     As you can see, for every user and for each type of Quota there is a row of data, this means multiple rows of data per user. Now, if all I was asking Crystal to do was organize this data it could handle it with no problem simply by building a group based on the UserID and pulling in the fields I want displayed.  Our report is going to be much more complex and this just an element of...

Updating the Managed Quota Area for Sage SalesLogix

Often, when discussing reporting issues with clients I will bring up the Quota’s area of Sage SalesLogix.  If you do not know where this functionality is located, go to your Tools/Manage/Quota menu and the following screen will appear. On the upper pane you find a place to list the users you want to set a quota for.  As each user is highlighted the list below changes to display the quota’s that have been established for that user.  Usually I bring up this area of SalesLogix when a conversation has to do with a way of tracking goals for the client’s user base.  This area has been around in SalesLogix since pre version 6.X.  I believe this area is over looked or unused because of two basic issues.  First, there is no place to define what the Quota being set is for.  This limits the use of a quota set for a user to only one because it could only have one meaning.  Second, the Quota section is set up for currency only.  If we look at a tool like Visual Analyzer you are seeing a trend of knowing the counts of items like Activities or History records.  There is a third issue, I think the entire set of functionality should be labeled “User Goals”. This would provide a better understanding what the area is for.  Oh yeah, one other thing, for you web users, the Quota functionality has been left out completely. Today, I decided to fix at least 2 of these issues and bring this functionality to the web.  I am making this available to the readers of this blog as I start down the trail of leading you through the building of a...

Looking at Stonefield Query for Sage SalesLogix (Final Thoughts)

For the last several posts I have been writing about my first hand experience using the reporting tool called Stonefield Query for Sage SalesLogix.  I thought it might be time to wrap it up with some overall final thoughts.  For those just joining the experience the series of post starts here.  My blog series was meant to provide, at a high level, the ease of use and how I was able to accomplish what I think are some primary tasks for a reporting tool.   Overall, I really do like the tool.  I think it is fairly easy to use, the documentation is well written and easy to follow which for a new user is very important.  The people at Stonefield Query are very responsive to questions and are easy to work with.  Stonefield Query would be great for someone who wants quick on the fly reporting with very little training.  The more the user works with the tool the more they will be able to write more complex report.  After the install there has not been any need for additional support from my system administrator which adds to the simplicity. As with all tools like this there have been some little quirky things within my experience like the interface locking up or messages stating the connection to the database has been lost.  All were self correcting by simply closing and reopening the tool.  These things did not happen that often and in a system as complicated as ours could have been caused by issues outside the tool.  The only suggestion for improving this tool would be to limit the number of dialog boxes used to accomplish tasks.  For example filtering:   As you...

Looking at Stonefield Query for Sage SalesLogix (Part 6)

Again, I have to apologize for the rather long break between posts.  The Minnesota National Guard and I have been seeing quite a lot of each other over the past few months.  Reminds of a post I wrote over a year ago called “Supportive Commitment“, what was written in that post is still true today.  This is a great company to work for and they are very supportive of the efforts of U.S. Military Forces.  Today I want to look at creating a Formula field in Stonefield Query for Sage SalesLogix.  In the Report wizard you find the controls that allow you to create a formula field in Step 2 – Data Selection.  I added the Account Name to the list of fields selected for this report and then changed the table to the Address. The formula that I am going to write for me demonstration will have address 1 display on the first line and the have the City/State/Zip field display on the second line all with in same formula field.  Under the list of available fields along the left, you will see the “Formula…” button .  Selecting this opens the “Formula Editor” below.  I added the name for my formula as “Custom Address”.  When I left the “Name” field the “Heading” auto populated, the value was still editable so I put a space between the two words. I then selected the button to the left of the formula space which brought up the “Expression Builder” dialog.  To build my formula I selected the my two fields from the list of available fields for this table.  I then added some VB scripting that inserted a carriage return...

Looking at Stonefield Query for Sage SalesLogix (Part 5)

Sorry for the long absence, I went away on some training with the Minnesota National Guard.  This is the fifth post in series that is covering the use of a reporting tool called Stonefield Query for Sage SalesLogix.  If you would like to take a look at the previous posts here are the links Post 1, Post 2, Post 3, and Post 4. In todays post we will cover the use of SQL Views in this reporting tool.  First of all, I like using SQL Views to organize my data for reporting. I have written a set of SQL views that provides counts of Activities and History Records by user for the current day.  This is a set of views so I cannot just paste the statement in this post but I should be able to show you that the use of SQL views is possible.  The first step of course is creating the SQL View.  Here is a link to a post I wrote back on May 27th 2008 which talks about using SQL Views in Reporting and Grouping and bringing together data from two independent databases.  As discussed in the post, once you have the SQL View ready for use you may need to enable it for use in other systems like Stonefield Query. To enable a SQL view go into the SLX Architect and open the Database Manager. Find your new view in the list of database tables Right Click and select Properties from the list. When the “Properties” window opens select the “Enable” button. The view is now available for use in Stonefield Query reporting.  With this step done open Stonefield Query and follow Stonefield’s wizard to build your...

Looking at Stonefield Query for Sage SalesLogix (Part 4)

The last couple of weeks I have been working on a series of posts introducing Stonefield Query for Sage SalesLogix.  The first two posts have been focused on set up and interface layout. Last week we created our first report.  This week we are going to see if our report can handle custom tables.  I have created an add on module we call Power2Project for the Sage SalesLogix Web.  We will use this module to test out how Stonefield Query can include custom tables. My report layout will be simple, I will list the Account, Project Name, and some of the project numbers.  I think my previous post explained well enough how to start a new report so we will not go into those details. Here is the first screen of my wizard, notice that I selected the Data group of SalesLogix.  I learned from trial and error last week that this is one way of filtering down the list of available tables.  Here on step 2 we see the custom tables already available for us to select from.  Again, find the tables that you need for your report and then select the fields for the report by moving them from the list on the left to the list on the right.  Once the fields are on the left you can start setting their properties by highlighting and selecting the “Properties” button. Here I am setting the properties of the EstimatedBudget field. This is a currency field. I went to the format tab and unselected the “Use Default Format” check box which then enabled the rest of the the fields for selection.  “Display $”...

Looking at Stonefield Query for Sage SalesLogix (Part 3)

The last couple of weeks I have been working on a series of posts introducing Stonefield Query for Sage SalesLogix.  The first two posts have been focused on set up and interface layout. This week we are going to actually dig in and create our first report.  I always try to stress in classes that I have taught the importance of sitting down and creating a report plan.  It can be as simple a drawing the report out and answering questions like these: What are the fields I want displayed. How do you want to group the report. Are there any records you do not want displayed, what are the rules.  Are there any formula fields. You can read more about my thoughts on planning reports here. Answering these questions gets your mind focused on your task.  My plan is to build an Account level report listing the Account, Account Manager, Status, Main Phone Number, along with the City, State, and Postal code displayed in one field.  Under the Account I will list the Contacts using a formula field to format the FullName of the Contact, their Status, work number, and email.  From my experience with other reporting writing tools I think I may have to create formula fields for formating the phone numbers, the contacts full name, and a combo City, State and Zip field.  I will not filter this report at this point.  I want you to know that prior to writing todays post I have not built one report in Stonefield Query.  This will be my first of many I am sure. I started writing the report at 8:56am  – I completed writing...

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!