Here's a simplified version of my t 2

[SteveBell]

I'm using Crystal Reports v8.0, Crystal Enterprise v8.0, and the interface to Baan ERP on Oracle. I need some help figuring out the best way to massage my data into the proper report format.

Here's a simplified version of my table:

[tt]ProblemID Person1 Person2 Person3 Action1 Action2 Action3
--------- ------- ------- ------- ------- ------- -------
101 Joe Susan Bill Run Jump Play
102 Joe Joe Saw Hammer
103 Susan Lift[/tt]

I need a report that looks like this:
[tt] Bill
101 Play
Joe
101 Run
102 Saw
102 Hammer
Susan
101 Jump
103 Lift[/tt]

What's the best way to accomplish this? Here's what I've considered:
[ol][li]UNION in Crystal Reports -- I'll have to figure out how to do this. Once I monkey with the SQL statement, I'll have to maintain that forever. Lots of point-and-click functionality goes away in CR.[/li]
[li]View in Oracle -- This is a separate item that will have to be maintained. My replacement (someday) will have to know that it exists. From my reading, some or all fields will be converted to memo.[/li]
[li]Develop a formula scheme -- I'm familiar with formulas. The tables only have about 3000 rows now, so this is OK. The tables will probably grow by 1000 rows a year, so it won't be long before analyzing every row is too slow.[/li]

Is one of these the best answer? Are there other methods I don't know yet? I'd appreciate any help I can get.

Thanks,
Steve Bell

Off-the-topic rant: Why isn't there a dedicated place in Crystal Reports to store documentation?>:-<

 

[Naith]

Hey Steve,

I would be inclined to take this to a view, but I don't get the memo comment. Oracle doesn't support memo datatypes. Seems to me that your data, with the exception of the ProblemID, is all varchar2.

I'd be interested to see how you'd go about conglomerating the person data so you aren't trying to group over 3 columns. Most options here seem to indicate the view growing by 300%, so if performance is going to be an issue, you might want to consider making the view a materialised view - which runs a *lot* faster - but take up more disk storage space. It's also a static view.


Naith

 

[SteveBell]

Well, I simplified the environment significantly. I'm pulling data out of a Baan ERP database using a Crystal-supplied DLL (it has its own limitations); I'll use multiple tables; there are many date and number fields; the fields &quot;ProlemID&quot;, &quot;Action1&quot;, &quot;Action2&quot;, and &quot;Action3&quot; will be formulas that combine multiple fields into single strings; and &quot;Person&quot; is an employee ID that I'll use to look up a name in the personnel table. I want to keep the discussion on the root problem that I don't understand yet.

Of course I can't find it now, but I read somewhere in these discussions last night that views sometimes convert all your data to text strings. This would make it difficult to do data comparisons.

 

[Naith]

Oracle views don't do that.

 

[Ngolem]

As is well known, I am a fan of the use of formulas.

Analysis of 3000 rows of data is not hard at all...in fact 10 times that amount is not too bad either. My benchmark was to have my reports completed in less than one minute and I have analyzed over 30000 records in that time often.

But having said that, this is not an easy report for use of formulas...

it isn't the formulas per se....it is the grouping.

You really want to group on the distinct values of persons 1/2/3 and since not all are in Person1 this becomes a problem.

A union query using the SQL designer would solve the problem nicely and the report itself would be simple.

A view would also do the trick too and perhaps would be the method of choice....especially if this report is to be web enabled at a future time since the SQL designer will not work in this environment....

as far as future maintenance goes (and I am glad you think in those terms) what I do is create a formula called @ReadMe, which is nothing more than a series of comment statements describing unique features of a particular report. I place the formula in the page footer, with a red background and conditionally suppressed with 1 = 1 (this way it stays red in design mode and invisible in preview)
Jim Broadbent

 

[Naith]

The @ReadMe commentary is an excellent idea.

Naith