Image of Navigational Map linked to Home / Contents / Search Crystal Connection Confusion

by Ross Mack - GUI Computing
Image of Line Break

Some tricks when using Crystal and time is short.

In a recent development I had an extremely limited time frame (partly my own fault, actually) and was required to get an app that had a serious amount of data access work working against both an Access97 database and SQL Server 6.5. That's all well and good. However, the trick came when doing reporting.

I have often said that reporting is the bane of software development. Of course I have also said that about date handling, multiple platforms, scope creep, and users. But let's not get tied up with technicalities.

As it turned out I had almost a whole day to do reporting and a limited supply of third party tools to build reports with. The limited supply being whatever ships with VB. OK, so I had a choice of using Crystal Reports or the VB Printer object.

The user's requirement was that the reports had to look sexy and be very flexible in criteria, sorting, and grouping. Of course when targeting two back ends I had to make sure whatever I did worked on both Access97 and SQL Server. Crystal was really the only viable choice, but I had a few hurdles to get everything working in time.

To implement variable criteria in Crystal is fortunately pretty easy. In fact the most difficult thing about this is building an interface that allows the user to express what criteria they want. Essentially all you need to do is translate what the user wants into a valid Crystal Expression and assign it to the RecordSelectionFormula property of the Crystal Custom Control before running the report (yes, this is Crystal 101 but bear with me). This may end up looking something like this:

cryReport.RecordSelectionFormula = "{Orders.OrderStatus} = 1"

The above statement would restrict records from the order table returned to those with a specific status (1). Or it may look more like this:

cryReport.RecordSelectionFormula = "({Orders.OrderStatus} = 5) and ({Orders.OrderTotal} > 500)"

OK, so we have a grip on all that ? Good because we now get to the interesting stuff. Grouping and Sorting was going to be more of a problem. Fortunately the reporting for this version of the app really only to report a couple of different sets of data. As I have mentioned, the reports may have to group and restrict that data differently, but the details reported only had a couple of variations. So, the first thing I did was create a couple of simple reports that simply had the required detail data displayed in an appropriate layout. Then I added a Formula field to the report. These formulas are usually used for some form of line by line calculation - total costs for line items and such.

However, I have been known to use report formulas for everything from calculating national debt to filtering coffee.

In this case I just set the formula to equal the contents of a field being reported. Let's say for example that one of the fields being reported is PostCode. I create a formula (we will call it MyFormula in a stroke of originality) and set MyFormula to return whatever is in PostCode by setting the formula's text to:

{Orders.PostCode}

I then tell Crystal to Group the report on the contents of MyFormula. This means that the way it is set up now it will group the report on any change of PostCode (almost sounds useful, doesn't it?). However, this doesn't really address the great amount of flexibility I have already explained was required.

Here is the trick : when you run the report from VB, you have the option of changing the report formulas before the report is run.

In this case I showed the available Grouping options as an array of radio buttons. The code which achieved the grouping looked much like this:

Dim nNum As Integer
For nNum = 0 to 3
   If optGroup(nNum).Value <> 0 Then
      Select Case nNum
         Case 0
            cryReport.Formulas(0) = "MyFormula={Orders.PostCode}"
         Case 1
            cryReport.Formulas(0) = "MyFormula={Orders.CustomerAccountID}"
         Case 2
            cryReport.Formulas(0) = "MyFormula={Orders.State}"
         Case 3
            cryReport.Formulas(0) = "MyFormula={Orders.BillingType}"
      End Select  
   End If
Next nNum

Because I changed the value returned by that formula to different fields (and they could also be from different tables in the report) when the report does the grouping it is grouped on different data as it is based on what is returned by that formula. You can actually dynamically add and change grouping options for Crystal reports directly through another set of properties. However, to get this working quickly and as simply as possible I found that this quick trick was easiest.

The other factor that I needed to deal with was what was displayed as a heading at the start of each group. Normally this would be some database field that represented what each group was. In this case as I was changing groups dynamically I used the same method to dynamically change what was displayed in the group headings. I simply placed another Formula object in the Group heading section and called it 'MyGroupHeading'. I then changed the above code to also intelligently set that Formula.

Dim nNum As Integer
For nNum = 0 to 3
   If optGroup(nNum).Value <> 0 Then
      Select Case nNum
         Case 0
            cryReport.Formulas(0) = "MyFormula={Orders.PostCode}"
            cryReport.Formulas(0) = "MyGroupHeading={Orders.PostCode}"
         Case 1
            cryReport.Formulas(0) = "MyFormula={Orders.CustomerAccountID}"
            cryReport.Formulas(0) = "MyGroupHeading={Customer.Name}"
         Case 2
            cryReport.Formulas(0) = "MyFormula={Orders.State}"
            cryReport.Formulas(0) = "MyFormula={Orders.State} + ' - ' + {States.StateName}"
         Case 3
            cryReport.Formulas(0) = "MyFormula={Orders.BillingType}"
            cryReport.Formulas(0) = "MyFormula={BillingType.Description}"
      End Select  
   End If
Next nNum

As you can see in the code, in some cases it was appropriate to simply use the same field and in others I referred to other tables or even built up a string of a couple of fields. If the relationships in your report are correctly arranged, using reference data will work quite neatly.

Of course one of the simplest options to implement was allowing the user to request different sort orders. This was again a number of radio buttons with appropriate fields listed. I also added a checkbox to indicate if the sort should be ascending or descending. Crystal's SortFields property supports this very neatly. The code should largely speak for itself:

Dim nNum As Integer
Dim sPrefix as String

If chkDescending.Value <> 0 then
    sPrefix = "-"
Else
    sPrefix = "+"
End If
For nNum = 0 to 3
   If optSort(nNum).Value <> 0 Then
      Select Case nNum
         Case 0
            cryReport.SortFields(0) = sPrefix & "{Orders.TotalAmount}"
         Case 1
            cryReport.SortFields(0) = sPrefix & "{Orders.OrderDate}"
         Case 2
            cryReport.SortFields(0) = sPrefix & "{Orders.ItemCount}"
         Case 3
            cryReport.SortFields(0) = sPrefix & "{Orders.TaxAmount}"
      End Select  
   End If
Next nNum

Again, the object here was to implement the functionality as simply as possible. For more information on the Crystal properties I am using see the Crystal Developer's Help. The last trick I discovered has to do with deploying the same reports when the application was running against SQL Server as when it was running against Access - I definitely didn't want to create two copies! The simplest option seemed to be to always report on an Access database. In the cases where the back end was SQL Server I would have the app create a dummy MDB with attached tables and the report should work just the same, right ? Well, no.

The way I supported both platforms was to use normal DAO when access the Access database and to use DAO's ODBCDirect functionality when accessing the SQL Server. This meant most of the application worked just the same except where I wanted to use Asynchronous queries or other ODBCDirect functionality.

The problem was that when I attempted to run the Reports against the Access database with the attached tables it would not work. I got a dialog asking for user names and passwords, then the report would throw an error saying it was unable to connect. This would happen no matter what combination of usernames and passwords I entered or whether or not I stored the password within the attached tables or not.

I knew the reports would work this way as I had tested them against the attached table database from the Crystal design environment. But, from VB they would not work. It was only after much experimentation, some pulling out of hair, frenzied phone calls to a couple of other developers, and with my deadline looming larger with the passing of every minute that I discovered something interesting.

The Crystal Reports custom control uses the first WorkSpace object created by your application to run its reports in. There appears to be no way to override this and it is not clearly documented anywhere I could find, but that is what it does. Normally this is not an issue as you would normally only have one workspace and it would be reasonable for your report to use it. In this case the first workspace I created would either be a JET workspace to open the Access database or it would be an ODBCDirect workspace to connect to the SQL Server. In the second case I still wanted the Crystal Report running off an Access database, and so the workspace it was using was entirely inappropriate.

Fortunately, once I figured out what the cause of the mysterious, always failing logon was it became easy to fix. I simply made sure that the application always created a default JET workspace as the default first, and then created a second ODBCDirect Workspace if it was going to be using SQL Server. That way Crystal would always pick up the first JET Workspace and would work quite happily.

So, there you are. A few tricks I learnt about getting Crystal to work the way I wanted on time (well, give or take an hour or so). Hopefully the next time your deadline is fast approaching and you realise you hadn't even thought about reporting yet these tricks may help you, or at least point you in the right direction.



Written by: Ross Mack
April '98

Image of Arrow linked to Previous Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]