Splitting data from one database column to multiple crystal columns 2

[Chats]

Hi,

I have a database table which lists data about equipment stored at different locations. The table looks something like this-

EQID LOCATION DATATYPE VALUE
0001 001234 MANUFACTURER Sharp
0002 001234 MODEL SK13h
0003 001234 SERIAL NUM TYHH8373788
0004 019983 MANUFACTURER Sony
0005 019983 MODEL SJHD200
etc

What I need to do in my Crystal report is to have different columns for MANUFACTURER, MODEL and SERIAL NUM for each location - i.e.

LOCATION MANUFACTURER MODEL SERIAL NUM
001234 SHARP SK13h TYHH8373788
019983 SONY SJHD200

How do I achieve this in Crystal reports?

In case it is important, I am using an Oracle database and Crystal 8.5. The report is a lot more complex than above with around 7 tables joined together, but this problem is stopping me from doing a lot of similar work.

Thanks for any advice.

Ade.

 

[sbdt]

If your data is lined up as neatly as you show above the Mid function will do.

Mid(datavalue,1,4) will return 0001
Mid(datavalue,6,6) will return 001234 etc.

Where datavalue is your Oracle column name.

Make a formula for each field you want.

 

[Chats]

Thanks, but I'm not quite sure this will do what I need - EQID, LOCATION, DATATYPE and VALUE are seperate fields in the EQUIPMENT table.

I tried using groups - a group for location and then putting the fields I wanted in the group footer. The problem was that I seem to get a seperate row in Crystal for each row in the equipment table - so the first row gives me MANUFACTURER for location 001234, the second row gives me MODEL for location 001234. I then need to collate all of this together onto a single row in Crystal.

Any ideas? - or am I misunderstanding your suggestion?

Ade

 

[Naith]

That's an interesting outcome to your attempted fix, because what you suggest you tried in your 2nd post should really have gone some way into solving your problem.

Are you sure that you grouped just by Location, and not by EQID and then Location?

You would have to group by Location, and place the information in the Location footer. The trouble is that if all your datatype information is coming out of one field, then you would need to set up variables which captured the values at the Detail level, and then call the variables in the group footer.

The formula in your detail section would depend on your identifying all the possible datatypes, and then producing something like the following:

WhilePrintingRecords;
StringVar Manufacturer;
StringVar Model;
StringVar SerialNum;

If {DataType} = 'Manufacturer'
Then Manufacturer := {Value}
Else
If {DataType} = 'Model'
Then Model := {Value}
Else
If {DataType} = 'SerialNum'
Then SerialNum := {Value}

Then in your group footer, call the three variables. Not an ideal way to report, but then that's what you get for hiring a DBA with a twisted sense of reality.

Naith

 

[kenhamady]

I would create a separate formula for each desired column:

If {DataType} = 'Manufacturer'
Then {Value}
Else ''

Then group the records for one item together, and take the maximum of each formula for that group. You can then hide the details and see just one line for each item. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Chats]

Thanks to both Naith and Ken, my report now works a treat - this also solves a problem I have have with other reports - as ever it is easy when you know how.

Cheers

Ade