Problem with record selection and left joins

Many report writers begin to explore table joins in Crystal Reports when they aren't getting the results they expect. While changing a join from an inner to an outer is sometimes the easy solution, it may not always yield the results you expect. Imagine you want all your customers in a report, whether they had transactions or not. Where they do have transactions, you are only interested in a selection of transactions

To illustrate with a simple example, let's look at a customer table and a transaction table of orders.

This one-to-many table relationship is common to many reports. This is a problem we have seen on many occasions. There are several solutions you might like to consider if your report has this problem. The default Inner Join will give us only those customers who have orders. To get ALL customers and their orders if they have any, you would use a Left Outer join. Under normal circumstances, the Order table fields will be NULL for those customers without any orders. And that is the foundation of the problem we are about to describe.

Usually, you don't want all the records in the transaction table. At a client site recently, we only wanted the transactions from a selected date range. But we still wanted to see all the customers on the reports. Because we are doing a selection on the date field in the orders table, we restricted our customer selection to only those customers who had orders in the selected dates. You might suggest that those customers without any orders will have NULL in the fields in the order table, so select the NULL values as well. Sure, that will pick up customers who have NEVER had any orders. But customers who have orders, but not in the date selection are still missing from the report. This is because the LEFT join is done in the database prior to record selection. We need a solution that shows all customers, and their orders in the date selection (if they have any). There are several solutions to this. Thanks to Tim Wilson for his help investigating these.

Brute Force Solution

Use a formula on all records After examining and trying some of the other methods, this was the solution we implemented. We expected a performance hit, but the report was fast and gave us the results we wanted.

As faster and faster processors are delivered it is time we used some of that extra power for more productive purposes than 3D helicopter games. We joined the two tables with a left join, but with no selection from the orders table.

Yes, all the transactions were loaded into the report and had to be processed. We calculated our “This Month” total using a summary of an If .. Then .. Else formula.

A Running Total Field would also work fine here. You might try and avoid a report that loads millions of records into one report. But Crystal can handle the volume without any problems. This solution performed well and worked with our CR 8.5 user.

Stored Procedure Solution

We looked at the previous problem from the database side. If Crystal wasn't part of the solution, how would we do this using SQL? The raw data tables won't do what we require, so we need an SQL stored procedure.

* We looked into using SQL to create the left join we needed, and it just didn't happen. If any of you SQL experts out there have a pure SQL solution for this, then let us know.
* If only summary data are required, a sub SELECT would work. But if you need the transactions as well, it is not enough.
* A UNION join also looked like it might provide a solution. With a stored procedure we could select the required transactions from the orders table. Then we could use Crystal Reports 9 or 10 to left join the stored procedure to the master table. But this solution is not available for CR8.5 or earlier and has a big performance hit.

Business View Solution

Business Views are a new feature in Crystal Reports 10. We created a SQL command with the date selection parameters inside a Business Foundation. Left join that to the customer table, and create the required Business Elements and Business Views.

This worked fine in our Crystal Reports 10 test report.

SELECT INTO Solution

One stored procedure solution we thought would work was to use a stored procedure with several steps. The first step did a SELECT INTO to create a temporary table with the only the records we needed, and then it left joined the temporary table to the master table. This would work with any version of Crystal Reports.

The negative side of this solution is that the report can only be run by one user at a time. A second user would reset the temporary table while the first was still using it.

Subreport Solution

Back into Crystal Reports with a main report from the master table, and a subreport for each transaction record to get the data we required. This did work correctly.

But the performance and selection of the transactions for each master record made this the slowest performing solution of any we implemented. Also, you could not do any grouping or primary record selection on fields in the transaction table.

Conclusion

Use the solution that best suits your needs. Each of the above has specific technology and performance limits. But we now have several ways to get that report with the data we want on it.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Retrieving random records with a query

I've had several students in the past ask about getting random results from a query, so they could review/analyze random data from there tables. For instance, one student was working with a customer service database - tracking calls and solutions provided to customers. Once a week, one person was in charge of calling random customers to get feedback on how the customer service was handled and if the customer was satisfied with the service. The employee would open the database and manually look for random customers to call. This solution provides an easy way to build a query - which could then be formatted with a report - to automatically build a list of any number of customers randomly.

Type the following expression into an empty column in the field row of the query design view:

RandomValue: Rnd([field])

where [field] is any field in the table that contains numeric values - the field can be an AutoNumber, Number, or Date/Time field. Apply a sort order to the RandomValue field, ascending or descending. Use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.

Outlook reminders sent to your mobile phone

I'm always forgetting stuff. Meetings, appointments, tasks, etc. I forget them all. I don't want to look like batman with a utility belt so instead of carrying a cell phone and pda, I wish Outlook could send a text message to my phone as a reminder if I'm away from my desk. This great little utility - although it's not free - makes a great add-on for Outlook.

OutlookSMS

Add bullets to Access reports

Bullets are useful to draw the reader's attention to a particular item, displaying lists, or even separating address parts in the footer of a report (similar to most company letter head). You can add a bullet to a report in Access.

Open your report in design view.

Turn on the Number Lock.

Insert a label and type the text you want in the Caption property. When you want to insert a bullet:

Hold the alt key down and--

For a round bullet, type:

0149

For a hyphen bullet, type:

0150

Release the alt key.

Crystal Reports XI Released

Business Objects has just released the next version of Crystal Reports 11 and Crystal Enterprise 11. Unlike the upgrade to version 10, the upgrade to version 11 has some significant improvements for report designers. Here are the ones I feel are most significant (follow this link to see all new features):

Dynamic Parameters:
Crystal parameters can finally generate the list of default values at runtime. The 'pick list' is no longer static.

Crystal Reports allows you to generate the 'pick list' directly from your data. You can specify a field in the report and Crystal will query the database at runtime for current values. You can even create a command (SQL statement) to generate the list.

Cascading Parameters:
You can now have one parameter prompt determine the list in another prompt. For instance, when trying to select an employee you could first select a department. Then Crystal would show you a list that only included employees from that department.

I think Business Objects was finally feeling the pressure from Microsoft's SQL Server Reporting Services. I expect this new rivalry to really benefit developers as the business intelligence tools gain more and more features.

Microsoft Access Limitations

Many times in class students will ask when they should upgrade to a server product like SQL Server for their databases. Access does have limitations; this list can be useful when deciding whether to use Access or some other database.

Access table limitations:

Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255

General Access limitations:

Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255

Technology News: Security: Microsoft Beta Seeks and Destroys Spyware

Technology News: Security: Microsoft Beta Seeks and Destroys Spyware

Looks like we can expect even more software from Microsoft in the near future to be dealing with malicious software. I expect this to be a huge step forward for Microsoft, simply because of the integration with the operating system.

Microsoft SpyWare Beta

Microsoft has entered the SpyWare removal business! If you have been looking for a good source for reliable spyware removal, I would suggest checking this out. I just tried it and was very impressed with the speed, but I haven't really had a chance to compare what it found/didn't find to other leading removal programs.

Microsoft Spyware Beta

LogMeIn - Remote Access Software

LogMeIn - Remote Access Software and Remote Control Software

I LOVE free stuff. As an MCT, I've had a free 1 year trial to GoToMyPC - a really cool and easy to use remote access software. I've had it installed on my home computer and can easily access it from ANY java enabled web browser. Unfortunately, my year is almost up. So, I've been looking for reasonable alternatives (GoToMyPC is 20 bucks a month!!!). I think I've found it ...

LogMeIn took me all of 5 minutes to install and get configured ... and it seems to work very well - especially for free. If you have the need to access your home computer from work or vice versa you should definitely check this out!

Threat profiliing Microsoft SQL Server

Threat profiliing Microsoft SQL Server

This document is an excellent overview of the security vulnerabilities with SQL Server. This is a must read for any dba out there that is trying to decide which authentication mode to use.

Personally, I rarely recommend using SQL Server Authentication. I've seen developer's tell their dba they MUST use SQL Server Authentication for programming - only because all the examples you see in books and online use "sa" with no password. I am shocked at the number of systems I have seen setup this way. Don't fall into this trap. Take the extra time to learn how to use Windows Authentication via code. This article should open your eyes to the real threats.