How to Join Remedy tables using Crystal 1

[james110]

Hi,
Does anyone know if you can join Remedy 5.0 tables?
I'm using the ODBC that came with the AR Remedy system.
I'm trying to join the Help_desk and the help desk audit log on incident_number using crystal reports 10. I get an error that says column found in multiple tables. (of course that's why I want to join)

Thanks
James

 

[ddrake]

You cannot join Remedy tables together in Crystal and have it pull data. You'll need to create join forms in Remedy between the sources you need combined and run reports from those join forms.

 

[MVisconte]

Actually, you CAN join tables, but the results are hella slow.

The way to do it is to define more than one ODBC to the Remedy system. You can add/join tables from different data sources in Crystal, and let Crystal handle the data merge.

Just be aware that. it. is. NOT. fast.

Getting a good choke hold on the data helps... This is not too bad if you're pulling a small set of records. I wound up adding a parameter that will let me pick developer names and that is the main constraint we use against the Remedy tables. Well, that and status.

This will NOT work (or work well at all) through the user tool. I _did_ get it to work on MY machine through this kind of join, but when I tried to make the report available system wide, nobody else could get it to run even after replicating my multiple ODBC DSN trick.

Be aware, you can probably get a MUCH better response using the correct ODBC driver for your database. You would have to do time conversion, but it's not hard. I can float you those formulas, if you are interested. Again, the best trick to speed reports up is to control the data / what records you bring back. You should be able to construct a query that brings back ONLY the records you want -- in 99% of the cases.

Yell if you want to try the other ODBC method.


--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[willyd61]

I do it 2 ways, one just creating a join form and report off that, which is how Remedy would tell you to do it. But thats a
pain. So what I have done like stated above is just using the native odbc driver to my database and query the view directly. You will end up having to do some leg work converting integers to strings, but you can start to post these to the server for other developers to use.

There is no speed issues with hitting the DB directly. Just get with your DBA and configure odbc and off you go, the view names are pretty darn close to the form names. One not that yo u will have to do some joining in order to get status history information to the records you are pulling, if that is what you need.

-Willy Lipford