Skip to Content
Former Member
Jun 27, 2008 at 02:18 PM

Editing CR's base SQL query?


Is there a way to edit the SQL query that Crystal Reports is using to pull its original data? Here's my problem:

I'm tasked with writing a report on transactions from the GL. Datatel's Colleague, our newly implemented system, stores transaction data in a table called "GLA_2008 (or whatever FY)". The beginning balance, however, is stored in another table called "GLS_2008". A for "activity", S for "summary", right? Now, the GLS table is keyed with the GL account numbers. The GLA table, however, is keyed with numbers that are the GL account number with the transaction number appended to that with an asterisk delimiter (11010102340000*198). Yes, this is crappy database design. Not my fault. More importantly, it's unchangeable as that's how this system needs it to be.

So, what I need to do is get into the query that CR is using to pull this data and join the GL account number from the GLS table with LEFT(GLA_2008.GLA_ID, 15) rather than the actual key field, as those won't join. Is there any way to do this? It seems like it would be rather simple, but the "Show SQL Query" thing is read-only.

It's worth pointing out that without that join, CR appears to pull in all records from both tables, and that crashes CR, so doing a formula field with LEFT() in it and making the join in the report rather than the original query doesn't seem to work.