bzzyplayin
Programmer
Is anyone using Lawson's attribute table for reporting in Crystal?
My dilemma with this file is that the attributes are in multiple records per object ID. They allow 9,999 user-defined attributes. So, the data ends up in the following format (with multiple records per ObjectID):
ObectID AttributeName Value
12 SQFT 1200
12 StoreType Mall
12 State MI
15 SQFT 3000
15 StoreType Mall
15 State IN
We are unable to use this format to join to any other tables, since the above example would inflate the other records (by 2) if I join the tables by ObjectID. I need one record per ObjectID.
What we hope to do, is create a table in SQL that puts the information by ObjectID into 1 record, with each AttributeName as a field heading with the corresponding value for that field. Is this possible?
ObjectID SQFT StoreType State
12 1200 Mall MI
15 3000 Mall IN
The fields needs to be dynamic based upon additional attributes that are created.
We plan to (initially) build about 50 attributes, which needs to be turned into 1 record (per Object ID) with 50 fields.
Note: This new table would be used for reporting using Crystal Reports 8.5.
Note: I posted a similair question under the SQL group, but thought some of you (Crystal Guru's) may have run into this same issue.
Any help or advice will be more than appreciated.
My dilemma with this file is that the attributes are in multiple records per object ID. They allow 9,999 user-defined attributes. So, the data ends up in the following format (with multiple records per ObjectID):
ObectID AttributeName Value
12 SQFT 1200
12 StoreType Mall
12 State MI
15 SQFT 3000
15 StoreType Mall
15 State IN
We are unable to use this format to join to any other tables, since the above example would inflate the other records (by 2) if I join the tables by ObjectID. I need one record per ObjectID.
What we hope to do, is create a table in SQL that puts the information by ObjectID into 1 record, with each AttributeName as a field heading with the corresponding value for that field. Is this possible?
ObjectID SQFT StoreType State
12 1200 Mall MI
15 3000 Mall IN
The fields needs to be dynamic based upon additional attributes that are created.
We plan to (initially) build about 50 attributes, which needs to be turned into 1 record (per Object ID) with 50 fields.
Note: This new table would be used for reporting using Crystal Reports 8.5.
Note: I posted a similair question under the SQL group, but thought some of you (Crystal Guru's) may have run into this same issue.
Any help or advice will be more than appreciated.