Home > Crystal Reports, Visual Basic 6 > Old Crystal Tricks

Old Crystal Tricks

2012/07/08

Many years ago i developed a software for transports by truck (that i should rewrite, i’m thinking to a Metro App) , in this blog i already wrote a post about.

In these days the problem: for another customer i have prepared a near identical pc, with the same Windows 2003 standard 32 bit , and surprise , the reports using UFL functions are not working : trying to launch them = crash , the program exits without errors ; in the event viewer only a generic message that my program crashed.

What is a UFL function ? In old Crystal Reports (i don’t know for the latest release, my program uses reports that was developed with Crystal 9 , Service pack 7) you can use custom function implemented in Visual Basic DLL; these functions are visible in Crystal if the name begins with “CRUFL” , so i have developed a dll named CRUFLWtk


That gives the cities reached from a truck in a trip : my program has a Trips archives where for every Trip there is a list of Cities reached in the Trip .

Compiling the DLL you can see in Crystal the new functions available for a formula, in my case


These functions does nothing else to give a list of cities separated from a comma, for example “Torino, Genova,Milano”

After some attempt (also the install of Crystal Reports on the server…) there were 2 options:

  1. Make a image of the working server done with Vmware vCenter Converter, then launch on the other customer server a VmWare Player using this vm
  2. Search an alternative to UFL functions, because these functions in this case does nothing of very special

The UFL reports (launched from the vb6 program) are perfectly working on every Windows XP on the earth i have tried, so i assume that there are problems with Windows 2003, and the fact that for a customer all is working under Windows 2003 is a strange miracle that i have no time to investigate; finally for this software there are no future developments (as vb6 version).

I have decided for the second option thinking to the old good WhilePrintingRecords that i have used a lot in the past.

The first step was to insert a subreport , with a ADO source, but i was having the problem that also in Crystal there was authentication troubles, the database password was not recognized.

Here the strange discovery : in order to pass the database password of a Microsoft Access file to a subreport (the Access file is password protected, ok is a weak protection but best than nothing) there is a curious procedure (discovered after a last desperate attempt………):

  1. Insert a subreport in Crystal


  1. Choose every time (do not reuse previous connections: apparently does not work !) to create a new ADODB connection


  1. Choose the OLE DB 4 provider, BUT click on “use a connection link file” and select a udl file prepared first (sorry for the Italian language..the XP vm is in Italian because the customers are tipically Italians)


  1. For to create a udl file right clic somewhere (better if in the program folder where are the reports) and create a file with .udl extension, open with double click
  2. Compile for a connection to a Access file (in my case) and make sure to write the db password, and save the udl with the password enclosed


 
 


Doing this , finally when i have defined the link between the main and the subreport no password problem when i was trying the report preview.


At this point how to obtain all the cities touched from our truck drivers, or the start and last cities ?

In my program we have an archive of steps, with a start and a end for every step:


Well, we can define our subreport with these formulas in order to obtain a list of all cities

INIT

WhilePrintingRecords;
StringVar ConcatenatedID := "";

RUNNING

WhilePrintingRecords;
StringVar ConcatenatedID := ConcatenatedID + {GetSintesiPercorsoViaggioCrystal.SiglaPartenza} + "," + {GetSintesiPercorsoViaggioCrystal.SiglaArrivo} + ",";

SHOW

WhilePrintingRecords;
StringVar ConcatenatedID;

GetSintesiPercorsoViaggioCrystal in my sample is a view without parameters that gives a table as


With the subreport defined as


We have a result as (not the illustrated case)


 
 

The subreport in enclosed without borders, with a height equal to the one of the other normal fields.

For the other case where we need the start city of first trip and the end city of the last trip we must change our formulas:

INIT

>WhilePrintingRecords;
StringVar strStart := {GetSintesiPercorsoViaggioCrystal.SiglaPartenza};
StringVar strEnd := "";

RUNNING

WhilePrintingRecords;

SHOW

WhilePrintingRecords;
StringVar strStart ;
StringVar strEnd := {GetSintesiPercorsoViaggioCrystal.SiglaArrivo};
strStart + " --- " + strEnd

The result:


The only problem is that specifying”Can grow” in the subreport and in the main report the textbox does not grow if the text field is larger than the subreport: so beware of this.

For now is fine, i’m out of the Windows 2003 UFL reports nightmare.

Advertisements
%d bloggers like this: