Crystal 2008 Set datasource location for SQL command reports

To view SQL Command,
Database > Show SQL Query

Update Data source of SQL Command is same as Procedure...

For musalman and mlmcc, Lawson queries are treated as "commands" in Crystal Reports, but aren't really the same thing. They're actually create outside of Crystal entirely.

@jrbledsoe001 - do your reports use Lawson Queries or actual SQL Commands? Also, if you're using tables combined with Lawson Queries, you're going to get horrible performance.  The queries either need to be complete, including parameters, or you should rewrite the reports to use SQL Commands or tables entirely. Also, no, you cannot view the contents of a SQL Command or Lawson Query without first connecting to it's data source.

~Kurt

Hi Kurt,  

You must work on Lawson to know about dme queries (Lawson OLEDB connector).  These specific reports are not using the Lawson OLEDB connector they are written in MS SQL using SQL command parameters.  My goal is to connect the reports using SQL command statements to an Oracle database.  I'll try the suggestion from mlmcc and post an update.  

A couple of the reports are using tables and SQL commands - this was a short cut due to a time pressure.  Thanks for the advice to rewrite these as SQL statements.  The oracle db is gargantuan and the last thing I need are poor performing reports.  Thanks again!!!

jrbledsoe001 (aka Joanna_Bledsoe@dell.com)  

Hi Joanna,

Yes, I've had a few clients with Lawons and LBI. In fact I recently had a project where we had to convert DME queries to use either simply tables (where possible) or SQL commands.  So many of the reports performed so badly prior to the conversion, because they had parameters outside the queries, joins to other queries, joins to tables, etc..  It's great seeing a report that takes 10+ minutes running in seconds now!

In general, the reason you shouldn't have tables connected to a data source like a command or query is because the command is treated as an external data source. It must execute in entirety before any joins, Crystal Reports record selection criteria or parameters that exist outside the command can be processed.  When using a command, it's best practice to have everything you need, including parameters and selection criteria, inside the command.

~Kurt

mlmcc - your solution worked perfectly to update my sql command statements in crystal

kurt - I would like to network with you since i do lbi too.  would you send me your e-mail address?    

Joanna - In general posting of email addresses in question is not allowed.  You can post it in your profile.
Please post it there.  This is mainly for your protection since there are people who scrape these pages looking for email addresses and then start sending spam.

mlmcc

My email is in my profile as is my linkedin.com profile. I'd be happy to network with you.

~Kurt

mlmcc  -  thank you for pointing out the profile section for me.  i am a newbie to the forum.

No problem.  You post most anything you want there so long as it is moral and legal.

Most of us who post our addresses put a twist on it like spelling out AT or DOT.

mlmcc

Dear All

I have advised the same thing , What was wrong in my suggestion ???

@musalman - Your reply instructed the OP to use Show SQL Query, which ins't where you set the location, nor is it necessary in this case. You also didn't explicitly tell her how to set the location, as mlmcc did. mlmcc's answer was the most technically correct.

~Kurt

If you read Part of the question
"Does anyone know of a way to view the SQL command on a report without connecting to the orginal data source?"

I answered this ...

Secondly, He wrote
"I can use the Set Datasource Location to easily map the tables. "

that's why I suggested her that Update Sql Command is similar to update Table Datasource...

As far as I know you cannot show a SQL command without connecting to the database.
I tried in the Database Expert by trying to edit the command.  It wanted to connect to the database
I tried the SHOW SQL QUERY - I got prompted for the parameters then it wanted the database.
Perhaps if there is data stored with the report it would work.

As to the second part, since she knew how to do that it either didn't work or she was doing something wrong

mlmcc

see - I just created a report by using SQL, and just saw this Sql As i explained, Perhaps i am wrong ...

In 2nd process, if it did not work is another thing,,,but my answer was enough to understand... Perhaps I am wrong...

You saw the SQL because you were logged in to the database.  You had to be in order to create it.  You cannot see the SQL in Show SQL Command without connecting to the database. If you completely close Crystal Reports, open it again and attempt to view the SQL you'll be prompted to login again (assuming you aren't using some sort of trusted pass through authentication).

In the second, you just weren't explicit enough.  

Update Data source of SQL Command is same as Procedure...

Select allOpen in new window

doesn't mean anything to anybody who hasn't ever set location before or used a stored procedure as a data source.  It doesn't even mean "set location", since you might be talking about changing the code within the actual SQL itself.

You need to be explicit with your answers.

~Kurt