The Crystal Reports Underground News

The Crystal Reports Underground News - Volume 2015.11

an independent source for Crystal Reports Information
by Ken Hamady, MS

Contents for November 2015:
** Crystal Reports formula function libraries (2015 update)
** Another free viewer, RptView
** My library of Crystal Reports materials
** Can Crystal Reports store passwords?
** Cross-tabs calculated before/after group selection
** Let me create your Crystal Reports
** Read a Google Sheets spreadsheet from Crystal
** SQL expression pulls values from a code table
** Open enrollment Crystal classes in Frederick, MD
** More efficient SQL for returning the last record
** Amazon joins the cloud based BI club
** Continued support for Crystal Reports

Gems from the Archives
Selecting an ODBC Connection at runtime (January 2005)
Getting reports from spreadsheet data (February 2005)

Crystal Reports formula function libraries (2015 update)

It is time for my annual comparison of formula function libraries. If you aren't familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today's report to tomorrow's report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is Cut Light, which now includes a handful of new functions:

    Search text using regular expressions
    Calculate the height of a growing text object
    Calculate the number of rows in a wrapping text object
    Trigger a web service to return a value
    Convert HTML/RTF into text

If you need help deploying one of these functions in a project let me know.

Another free viewer, RptView

RptView by Pursuit Technology was originally for sale. Then it was free with ads. Now it has now been released as a free viewer without ads. All that is required is that you register with the vendor. To see what it can and can't do you should read my annual comparison of viewer applications which I updated this week to include this new information.

If you don't have CR installed you can view any report simply by double-clicking the rpt file. Alternatively, you can launch RptView and then identify the folder that contains your reports. Keep in mind that RptView is no longer being actively maintained. If you do need support, you can hire the vendor to fix any problems you find.

My complete library of Crystal Reports materials:

Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.

    Expert's Guide to Formulas ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to SQL Expressions, Options and Commands ($26)
    Expert's Guide to Totals ($24)
    Expert's Guide to Cross-Tabs ($22)
    Expert Techniques Vol. 1 - 4 ($19 each)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site.

Can Crystal Reports store passwords?

I received an Email from a desperate customer this weekend. They are on the last few months out of a legacy system and had to improve security to the database by requiring a password for running Crystal reports. That isn't normally a problem because Crystal will open the User/PW dialogue. But they run their reports through the legacy application. When this application tries to run a report that requires a password dialogue, the report viewer crashes. I am told that they can't use integrated authentication and the also can't rewrite the legacy application. So they were looking for a way to store the passwords in the report so that there wasn't a password dialogue. I have been asked this before and thought there was a way to store the password in the connection, so I did some research on their options:

1) No, there is no way that an RPT will store a password. This is has always been true of Crystal, and it is by design.

2) It appears that in the past you could create an ODBC DSN that stored passwords. This is no longer allowed for security reasons. To be sure I created a file DSN and specified the user, password and database. At runtime the login dialogue showed the stored user and database, but ignored the password. Most of my customers people solve this problem with integrated authentication, where your DB login is tied to your network login.

Actually, this was the first time I have created a File DSN so I learned something new. A File DSN is a small text file with the extension DSN. You write it in any text editor. It has the same information that you use when you create a system DSN. The difference is that a normal DSN's info is stored in the registry, while a file DSN is kept in a text file. It can therefore be copied from one PC to another or stored on a central server to be read by many different workstations. It reminds me of a UDL (see below)

3) If you use OLEDB instead of ODBC you can create a UDL file. This is a small text file that has the extension UDL. You create it as a new text file and change the extension. When you right-click on a UDL file you see a "properties" option that opens a special dialogue where you can enter connection information. Using a UDL file is one option for this customer to get up and running. This is because a UDL can remember the password for the database, unlike a File DSN. But it isn't an ideal solution because the password is stored in plain text.

4) A last resort option is for this customer to deploy a client based viewer. The down side is that they would have to run the reports outside of the application. With this option they can either have the user enter the password, or they can use a viewer that can encrypt and remember the password.

This customer chose to start with a UDL, because it got them up and running immediately. If they later decide it is too much of a risk to store the passwords in the file they will move to a viewer.

Cross-tabs calculated before/after group selection

I just learned a neat trick for cross-tabs that allows you to decide if the cross-tab is calculated in the first pass or in the second pass. A cross-tab that uses only database fields or most formula fields will be calculated in the first pass. But if you use a specific type of formula in the cross-tab, then the cross-tab will be calculated on the second pass.

[ crickets chirping]

To see if this will help you I guess we have to answer the two obvious questions:

1) What difference does it make if a cross-tab happens in the first or second pass?
2) How do you create the special formula?

The answer to the first question has to do with Group Selection. Say you have 100 customers in your report but you only want to report on those who have spent more than $1000 in total. You would create a subtotal for each customer and use that subtotal in the Select Expert (Group) as a criteria. A filter that involves a subtotal has to be applied as Group Selection.

So, lets assume that using Group Selection we reduced the report form 100 customers to 30. Now you add a cross-tab to the report without using a special formula. This cross-tab will still include all 100 customers even though the report only shows the 30 customers that are over $1000. This is because a normal cross-tab is calculated before Group Selection – in the first pass. But if you use a special formula in the cross-tab, then it will be calculated after Group Selection – in the second pass. This cross-tab will be based on the 30 customers.

So how do we create a special formula? All you have to do is start the formula with "WhilePrintingRecords". So lets say that the cross-tab mentioned above has a column for each Ship Date. You could write a formula that says:

WhilePrintingRecords;
{Orders.ShipDate}

If you use this in place of the original Ship Date field in the cross-tab, that pushes the Cross-tab to the second pass. You can do this with any field used by the cross-tab. Also, any formula that involves a grand total or subtotal will have the same effect when used as part of the cross-tab, even without the phrase WhilePrintingRecords.

This blog article includes a diagram of the passes and even shows the two places where cross-tabs can be calculated.

Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.

Read a Google Sheets spreadsheet from Crystal

A customer asked if there was a way to read a Google Sheets spreadsheet from Crystal so I did some research for her. If you do a web search for "ODBC Google Sheets" you might be fooled into thinking that there are several options. However if you follow them they all point to the same ODBC driver from CDATA. This has a free trial download and costs $200 for a perpetual license or $79 per year for a subscription license.

We downloaded the trial and found that setting it up was pretty straightforward. There was only one thing that wasn't very clear at first. Google says to register your "application" and get a security token for it. I assumed that this was only if we were coding an application wanted do authentication in the code, so we skipped that step. From Crystal we were able to connect successfully to Google, but we could not see the sheets. All we could see was a list of actions.

After a few more experiments we went back and created an application entry called Crystal in the customer's Google account. That allowed us to generate a token and a 'secret' (their name for a password). Once those were entered in the local ODBC config, Crystal was able to see the tables and things worked normally.

SQL expression pulls values from a code table

SQL expressions don't get used very often. After all, if you know how to write a SQL expression, why not write the whole query as a command? But this week a customer asked me if using a SQL expression could help them look up values in a master code table. It was a great idea and we developed a handy SQL expression that they can use in many different areas.

This customer's database stores all of their code descriptions in a single master lookup table. I see this quite often. The code table will have different groups of records for different code fields. There might be one set of records to decode the product category, another set to decode the customer type, and maybe even a set to decode the state field for addresses. When you have multiple code sets in the same table you will usually have three fields:

    {Code} – the code value
    {Desc} – the description that goes with that code
    {Field} – the name of the field that is being decoded (e.g. State, ProdCat , CusType)

The last field is necessary when the codes are not unique. For instance a code of 007 might represent Ohio in the "State" list, while further down code 007 might represent 'filters' in the ProdCat list. So once you link on the code you also have to add a record selection filter to specify the field. Things get complicated if the code field is optional because Crystal doesn't support filters on outer join fields. But even when the code field is a required field, retrieving a description involves five steps for each code:

1) add a new instance of a table
2) link the new instance to the correct code field
3) change the alias of the table to make it clear which field is being decoded
4) add the appropriate filter for that field in the selection formula
5) place that field on the report.

With a SQL expression all of the above is contained within the expression. Here is an example:

(SELECT max(code.desc)
FROM code
WHERE code.field = 'ProdCat'
and po.ProdCat = code.code)

Once you verify that it works you can simply copy the object from the layout of one report and paste it in another report and you are done. All the steps above are accomplished in that one step. And if you need another field decoded you just duplicate the field and change the value 'ProdCat' to another code. Best of all you can use it with optional codes, because the filter is in the expression, not in the overall report query.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for initiating this idea.

Note – in certain reports and in certain versions of Crystal a SQL Expression will error with:

"The multi-part identifier [your field] could not be bound."

This is a Crystal bug and here is the work around.

Open enrollment Crystal classes in Frederick, MD

Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my January classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.

So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.

Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.

More efficient SQL for returning the last record

I wrote up a method I called the "wormhole" years ago. Normally when you calculate the maximum of a column you can refer to that maximum in any formula in the report. The wormhole technique takes advantage of this behavior to transport other values from the same row as the maximum value. For instance, I can move several values from the last record of the report and show them at the top of the report. To do this I append the other fields to the original, take the maximum of the combined string and then split the maximum value back into pieces. Recently I realized that this method can also be used in SQL expressions to return multiple values, and in SQL commands to simplify certain queries.

Say you have a table of transactions by customer. You want to generate a list that shows the customer name and three fields from their last order (date, order ID and amount). This is simple to do in Crystal without resorting to SQL, but it requires that Crystal bring back ALL of the orders. You then either sort and display only the group footer, or you do a group selection to select the maximum order date for each group.

But if you only want the maximum record to come back from the database then you need to do the work in SQL. There are several methods to return just the last record. The most common involves running two queries and joining them. The first query would pull a list of ALL of the orders for each customer and would return the four fields. The second query would use a "Group By" on the customer and return only the last or Max() date, for each customer. Then you would inner join these two results on both the Customer ID and the Order Date to have all the fields from the first query but limited to the date from the second query. Here are is an example of this query that can be run as a SQL command in the Xtreme sample database:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`,
`Orders`.`Order ID`, `Orders`.`Order Amount`, `Orders`.`Order Date`
FROM (`Customer` `Customer`
INNER JOIN `Orders` `Orders`
ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN (
   SELECT `Customer`.`Customer ID`, max(`Orders`.`Order ID`) as LastOrder
   FROM `Customer` `Customer` INNER JOIN `Orders` `Orders`
   ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
   Group By `Customer`.`Customer ID`) LastOrd
ON `Orders`.`Order ID`=`LastOrd`.`LastOrder`
and `Customer`.`Customer ID`=`LastOrd`.`Customer ID`

This works fine, but it has to do two separate queries. This can slow things down, especially if you have to do this several different times. The alternate approach would be:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`,
max (
Format (`Orders`.`Order Date`, 'yyyy/mm/dd') & '-' &
format (str(`Orders`.`Order ID`) , '00000') & '-' &
format (str(`Orders`.`Order Amount`), '000000.00')
) as MaxStr

FROM (`Customer` `Customer`
INNER JOIN `Orders` `Orders`
ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
Group by `Customer`.`Customer ID`, `Customer`.`Customer Name`

Lines 2 to 6 calculate the maximum of a combined string for each customer. The string includes the Date, the ID and the Amount of the transaction. The string starts with the date formatted as Year-Month-Day. The maximum of that string will grab the last order, and will also include the values of the other three fields. It returns the same values as the fist query and only takes one pass. The only downside is that you get your values inside a combined string. To split them apart will take a few extra formulas in Crystal, but you might find this worth the extra effort because the query should run faster.

This technique is especially useful when you do a subquery inside a SQL Expression field. SQL Expression fields can only ever return a single "value", but with this method that one value can include a combination of several fields from the same row of data.

Note - Adam Butt of APB Reports in Trondheim, Norway just reminded me that some databases can use the Row_Number() function to accomplish this (Oracle/SQL Server). See the original blog post for his example.

Amazon joins the cloud based BI club

It seems just about any time I am shopping for something I find a larger selection and lower prices through Amazon. Competing with Amazon is an obvious challenge for any business. So when Amazon announced their own cloud based BI product called QuickSight it got my attention. QuickSight will allow users to visualize their Amazon Web Service (AWS) data as well as data from other sources. Amazon claims that their price is 1/10th the cost of local BI options. Their Standard Edition is $108 per user per year, with the Enterprise Edition being twice that.

QuickSight is not limited to reading AWS cloud data. They are providing outbound connectivity so you can also analyze data that you store locally and data in other cloud services like SalesForce. All of the data is then processed by a new calculation engine they have created named Spice. QuickSight has it's own visualization designer, but Amazon also provides inbound connectivity so users can tap into the Spice engine from partner tools like Tableau and Qlik.

According to an article on VentureBeat.com, cloud based BI is on the upswing from many vendors:

The rollout of [QuickSight] comes a couple of months after Microsoft's cloud-based business intelligence service, Power BI, became generally available. And last year IBM brought its Cognos business intelligence software to its SoftLayer public cloud. Salesforce came out with its comparable Analytics Cloud last year, while startups like BIME, Birst, Domo, and GoodData offer standalone cloud BI tools.

They don't mention that SAP has put their analysis engine, Lumira, in the cloud. You can get a free Lumira account just by registering (1 GB of storage). So it sounds like the future of both large scale data storage AND large scale data analysis may be in the cloud, with the biggest cloud provider of them all taking the lead.

Continued support for Crystal Reports

When CR 2011 was released SAP also introduced Crystal Reports for Enterprise. SAP stated at that time that they were going to focus their future development efforts on the newer product. They also stated that they would continue to support the standalone product, but the wording left some of us wondering how long that support would last.

Recently one of my newsletter subscribers sent me a link to an SAP wiki page. The page explains the difference between Crystal Reports for Enterprise and "classic" Crystal Reports. He found this statement in the overview:

"We will continue to support the Crystal Reports 2011/2013 line of products after Crystal Reports for Enterprise has caught up [with the features in CR]." It isn't a promise of indefinite support but perhaps they won't discontinue the stand alone version of Crystal reports any time soon. I certainly expect to be supporting Crystal for many more years.

And thanks to Joe Gaietto of Ohio MHAS for sending the updated link.

Gems from the Archives
Selecting an ODBC Connection at runtime (January 2005)
Getting reports from spreadsheet data (February 2005)

Removal instructions:

I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.

Contact Information

Follow Me on BlueSky