Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Analyze with SELECT INTO

Use SELECT INTO to help analyze your data.

In another article, we showed how to speed up your Crystal Report processing using SELECT INTO.

Paul Briggs of South Auckland Health tells us of another use of this SQL function.

Paul uses a SELECT INTO to create a temporary summary table of all the patients who had a certain procedure, and then joins this table to find all the other medical procedures the patients incurred.

If you think about it for a moment, you can see this sort of analysis can be extremely useful. You can look at correlations, possible causations, and other logical relationships.

Suppose, for example, 80% of patients who had an appendectomy later had a hernia operation. But for all other procedures, patients later having a hernia operation was less than 1% this would point to some problem with the appendectomies. This is only a fictional example; it in no way implies South Auckland Health had any such problem.

Now, here's another example. Your company makes about 100 different widgets. Many of them use the same components. For example, some use the same screen. Some use the same li-poly battery. Some use the same keyboard.

Suppose there's a high failure rate of a given component. In which widgets is this being used? There's no easy way to answer that, or is there? Using the SQL function, there is an easy way!

It's unlikely that a Crystal Reports administrator will know which correlations to look for, using this SQL function (or via any other method). But it's equally unlikely your users will know that you can find such correlations.

So how can you them aware of your (awesome) ability? First, make a little demo. Pick something at random. Do the join, and make sure this SQL function pulls up something that you can show. Make sure this works on your laptop.

Next, schedule an appointment with one of your manager/users--preferably in a conference room. Now demonstrate this on your laptop.

The user will probably wonder aloud, "So, what good is this?" The answer is this particular correlation isn't useful. But the same sort of correlation between, say, sales of product X and all items bought by customers who bought product X might be.

Ask the user to name some possible correlations. Ask, "What kinds of causal relationships might you be interested in?" Get some discussion going. Take notes on what comes up.

Then, go back to your office and see if the database can support correlating A with B for the named correlations. Probably, something will work. Then you have a good working demonstration. Provide this to that user, then you have a new working demo to show a few other users.

After you get a few users going with this system, let your boss know how it's working and talk about getting resources to scale it up. Your boss now has an employee who has done something very useful for the company, so your work reflects well on your boss and your department.

More importantly, you have yet another resume bullet point plus some good reputation management going on.

Report Analyzer

You can more effectively analyze your Crystal Reports by using Report Analyzer:

http://www.crystalkeen.com/tools/analyze.html

 

 

 

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.

 

 

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.