Link to data in a vertical lookup table

[Andrew111]

I am reporting from an Oracle system called Maximo using Crystal Reports 11.

In the Crystal report that I’m building, I have a primary table called WORKORDER with other tables related to it. I have to relate a new table called LOCATIONSPEC. The WORKORDER table and LOCATIONSPEC table have a common field called LOCATION on which to make a relationship. But I cannot simply link it in and pull data from the appropriate field like most tables, because it is set up as a vertical lookup table that does not keep data in separate fields. Different types of values are kept in the NUMVALUE field and the type of data is indicated by the ASSETATTRID field. Like this:


LOCATION NUMVALUE ASSETATTRID
11207 0.00000 DOH
11207 0.66000 FCI
11207 0.44000 BLDGDIM
11208 0.00000 DOH
11208 0.64000 FCI
11208 0.42000 BLDGDIM

In other words, there are no fields for DOH, FCI, BLDGDIM, etc. Instead, I have link in on location, then find the appropriate types in the ASSETATTRID fields, then look for the value in NUMVALUE.

I am assuming Oracle accomplishes this with subselects, which is something I’ve heard of but don’t know how to do. And with the report writer I used to use, I could simply create calculated fields to link to. But I cannot figure out a solution in Crystal Reports 11.

 

[synapsevampire]

You can take a query and paste it in as the datasource in CR XI using the add command, so if you have the proper query created (use Toad or PL/SQL Developer to create it), you can use subqueries.

Crystal uses subreports for this, which are slower.

You can also use Views or Stored Procedeures.

-k

 

[Andrew111]

Thanks for the quick response! I tried using the Add Command feature you suggested, using a select statement. Like this:

SELECT locationspec.location, locationspec.assetattrid, locationspec.numvalue FROM maximo.locationspec WHERE locationspec.assetattrid = 'fci'

This gave me a simulated table that seems to be working. This is a big performance hit compared to a routine query, but maybe that's just part of doing this.