Remedy AR System and Crystal Reporting

[bielet]

Banged my head to the wall with this one, just a reminder for all you trying to accomplish the similar:

"Crystal Reports allows users to generate reports from multiple tables by joining the tables together. AR System ODBC Driver does not support this capability; however, you can achieve the same goal by creating an AR System join form. After creating the join form, generate a report from it."

-Ville

 

[gortuk]

Also, there are problems with any version of crdb_odbc.dll *after* the one that ships on the CR 9 install CD. So if you use CR 10 or XI, or apply any service packs for CR 9 (last time I checked), you will get errors when attempting to view some tables using Remedy via ODBC connection. Solution is to revert to the version of crdb_odbc.dll that ships with CR 9, or use OLE connection for ODBC, which seems to work better.

Anyone else have Remedy reporting tips?

 

[MVisconte]

The only Remedy reporting tip I have is... "don't"? (just kidding)

Actually,
When we use the Remedy ODBC driver, we use the one that comes with the Remedy user tool. I personally don't like the Remedy driver, mainly because of the inability to do joins. The majority of my reports are written outside of Remedy, using the standard ODBC to MS SQL.

What does the Remedy ODBC driver buy you?
1) You can run reports "easily" from w/in Remedy, using Remedy's in-app reporting links.
2) Remedy takes care of user authentication, and because of that, handles record and field security.
3) Remedy uses the user's query (inside the user tool) for record selection.
4) Remedy ODBC does date conversion (epoch time conversion) for you.
5) Remedy ODBC does Action Log (or other memo) field handling for you.

What does it cost you?
a) Speed
b) Joins (see item "a)")
c) Limits complexity of reports

Since we run enterprise reports (outside of the user tool), across the while database, we don't care about #1, #2, or #3. Because we run reports against 100's of 1,000's of records, we NEED "a", and because of the complexity, "b" and "c". We use built in functions to handle #4. We seldom need to do anything w/ #5. So for us, enterprise-level, we use ODBC for SQL.

For folks that can't dictate to the SAs that they want/need a join form, that would be the way to go. Except for #2 and #5, I can't see reasons for using the Remedy ODBC. I believe (believe) you can run external processes w/ command line arguments from w/in the Remedy User Tool, meaning you can run a Crystal Reports viewer against an external Crystal Report and pass fields as parameters. Oops, I just thought of one more down-side to using external reports... you have to set up a seperate (read-only) user account into the Remedy database, and set up/share account credentials, and that may be considered a security/confidentiality/propriety data issue for some companies.

I actually worked with someone to overcome the no-join issue from w/in Remedy, but it wasn't pretty, and it wasn't quick. The result was a single report based off of multiple datasources -- all to the same datasource -- with joins between them. We have three defined datasources, say Remedy1, Remedy2, Remedy3, to the same server. While it worked, it ran like a wounded dog. The same report, run outside of Remedy was "split second", but they wanted it lauched from w/in Remedy. They wound up using a join form.

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[bielet]

Thanks for the tips gortuk and MVisconte! For the driver thing, we're (i think) using ODBC driver provided with Remedy User, not with Crystal. So far i've had no trouble querying the tables..

And yep, they want to run the reports from Remedy User, so direct connection to DB isn't an option. If the reports turn out to be veeery slow, maybe i can convince them to use the direct conn with the expense of not being able to run them through RU.

For the reporting needs, most of them can be achieved with join forms consisting of two tables, for one report i might have to join three tables. We'll see how that works speedwise.

thanks again!

-Ville

 

[bielet]

Trying to join more than two forms:

"To join three forms, you must first join two forms and then join the resulting form to the third, creating a hierarchy of joins. Joining multiple forms in a hierarchical order makes it easier to provide a consistent workflow. If there is a need to combine data from three or more forms, it can be done by creating a series of two-way joins."

and the limitation:

"Note: Hierarchical or "nested" joins are not supported where the underlying database does not support them. For example, Microsoft SQL Server does not support nested joins in AR System."

And of course we're using MS SQL Server.. Do you know if this applies to SQL Server 2000 or is this information out of date?

Cheers, Ville