The AR system odbc connection will not do any joins, this is by design. Here's the official announcement: http://support.bmc.com/KMSUSER/DocView_SHFrame.htm?p=&user=public&d=/kms public/KM-000000003462.htm&vg=selfhelp
The only workaround for this issue that we have come up with is to pull the individual tables into a standard sql environment and then use that set as the data source for reporting. There are some issues doing this, I have another thread on the issue. The issue is that I can't seem to get the odbc driver to pull any information when I'm doing it in ms sql 2005. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rod Harris Sent: Tuesday, June 19, 2007 05:36 To: [email protected] Subject: Re: ODBC Driver: AR System ODBC Driver version 7.x Hi Joe, This is actually a Crystal feature that has been around for a while now. I don't think it can be used to do joins (at least efficient ones anyway) against the AR system database as I'm pretty sure the control provided by the AR System ODBC driver just isn't up to it. This makes sense when you consider that at the database level a single AR table (form) uses data from several underlying tables. You are limited to doing a (very slow) join by combining multiple data sources referencing the same AR system server or you can use the AR created native database views instead. It has been a while since I've used the command feature in Crystal but I seem to recall it being used with an "order by" clause to enforce a particular order for gathering data into a report. I guess it could also be used in combination with a parameter to pass a query to a report. If the query is passed to the report then it could be displayed as data in the report. In short I guess the command feature does have its uses but probably won't help get around the issue with joins you were hoping it would. Rod > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza > Sent: Tuesday, 19 June 2007 5:06 AM > To: [email protected] > Subject: ODBC Driver: AR System ODBC Driver version 7.x > > After configuring the AR System ODBC Driver to connect to crystal, I see > an > option right on the top of the list of all available tables, called 'Add > Command'. > > Was this option available in the previous versions of the AR System ODBC > driver??? > > From the looks of it you can write an SQL statement to create a view to > report out of.. I'm beginning to think this might be useful to create a > join > view of tables to report out of without creating the join form within > Remedy.. > > Has anyone used this option before? And what have you'll done using this > option. I'd like to see what different reporting possibilities this option > makes available to a report developer. > > Joe ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
