Parameters in SQL Query 1

[marsg]

Hi,

I'm using CR9 and I'm trying to get a parameter into the SQL Query of a subreport. I couldn`t make it directly within CR9 so I add to make a SQL query into SQL Designer (not shipped with CR9) and change a lot of thing in my report to make it use that new Query.

Is there a way to modify the command of a sub-report without using SQLDesigner and then have to change everything?

We have some reports which actually filter the data inside CR instead of in the db server and we`ll have millions of records in our db. I don't want to modify every report we have but I have to make sure the filtering happens on the server.

Any help would be appreciated

Marcel

 

[synapsevampire]

I don't think that CR even suggests using their query product these days, I'd suggest eliminating it.

I'm not sure what modify the command means (you mean the add command?), if you're using CR 9, there's no need for a 3rd party SQL product, real SQL works.

-k

 

[marsg]

Hi,

Thanks for your response

> I don't think that CR even suggests using their query product these days, I'd suggest eliminating it.
I want to eliminate this SQLDesigner thing from my reports... if I can find another solution.

> I'm not sure what modify the command means (you mean the add command?), if you're using CR 9, there's no need for a 3rd party SQL product, real SQL works.
I want to use real SQL but I need to insert parameters into the query. I'm not creating new reports, only modifying existing ones.

I can see the ODBC connection in the Database expert and the table on which the query is executed underneath. When I say modify the command, I mean right-cliqking on this table_name and choosing "Edit command". I guess an "Add Command" has been made on this table though I'm not sure.

I added a parameter field on my report which is linked to my sub-report ant that works fine. But I can`t use that parameter inside my SQL query though I've tried in a lot of ways. Every time there is something not working and it`s not always the same.

I hope I gave you enough precisions so that you can help me

 

[synapsevampire]

> I'm not sure what modify the command means (you mean the add command?), if you're using CR 9, there's no need for a 3rd party SQL product, real SQL works.
I want to use real SQL but I need to insert parameters into the query. I'm not creating new reports, only modifying existing ones.

Why would you insert a parameter into the query?

Use the record selection formula and Crystal will handle the parameter side for you.

>I added a parameter field on my report which is linked to my sub-report ant that works fine. But I can`t use that parameter inside my SQL query though I've tried in a lot of ways. Every time there is something not working and it`s not always the same.

What does something not working and it's not always the same mean?

Do you get errors? Bad data?

In all of my Crystal implementations I create Views or SP's, creating SQL within the reports does not facilitate reusability nor maintenance.

What database are you using? There are lots of Query building tools out there, some of which are free. Build the queries using one of those, and then create a View from it. Now use the View in the report and the record selection formula to leverage your parameters in the View, Crystal will pass the parameterized SQL for you.

-k

 

[marsg]

The reason I want to put a parameter into the query is that when I use the SelectExpert, Crystal retrieves all the records from the tables and THEN filters the data. I'll have millions of records in my database so that something I can't handle, filtering must be made on the server-side.

Everywhere I read that Crystal will use what is in the Select Expert to modify the SQL query before sending it to the server but that`s not my case. There was an already existing WHERE clause which I removed just to make sure it didn`t stand in the way but that didn't help.

I'm using an old MySQL, version 3.23.39. Unfortunately, it doesn't support stored procedures so I can`t use that.

> What does something not working and it's not always the same mean?
Sometimes I've been prompted twice for the same parameter name(I guess I had not linked them at the time), most of the time it's just that the server does not do the filtering (either he receives no WHERE clause or he receives textually {?ParameterName} in it). I had another behaviour but it seems that I can't remember it this morning.

Maybe my initial question should have been:
What is the practical way to retrieve from the server only the needed records using parameters entered by a user?

 

[rahulmoray]

How do I create a query file (.qry) using Crystal Reports 9.0 and use the same to design a new report.

i tried to create .qry file in Crystal SQL Designer of crystal reports 7.0 version.it is working in cr9.0 well.but if i create a .qry file using notepad or TOAD(oracle),Crystal reports 9.0 not recognising.if any other way is there to create .qry file with out using Crystal SQL Designer of crystal reports 7.0 version

 

[dgillz]

If you want the selection done on the server, create a view as SV suggested, then base your crystal report off of the view.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[marsg]

I forgot to mention on this thread that I resolved my problem. The Record selection of Crystal is now pushing-down the selection to the database server. The problem was that an "Add Command" has been done on the table when the whole table should have been used.

If you really want to use .qry files, I had done mine using the SQL Designer from CR9 (you must download it separately).

Good luck

Marcel

 

[synapsevampire]

Again, the qry files are a bad, bad idea...

Did you contemplate why Crystal no longer includes it? It's just an extra layer at best.

-k

 

[mahmed]

One benefit with the SQL designer is that you could format the MEMO field so that 254 characters from the MEMO can be extracted into one of the field in the SQL query and when the report is based on this query then you can use that field in the formula editor.
The second benefit is that when you need a sub-select statement then you can type in SQL Designer easily

Mansoor

 

[synapsevampire]

Mansoor: contact Crystal, they'll suggest that you NOT use it, it doesn't even ship with CR 9.

Whatever you're doing in SQL Designer can be more readily done on the database side.

BTW, you can use real SQL directly in CR 9, and you can paste SQL into an ADO select in CR 8.5.

I appreciate your intent, and it was a useful tool in it's day, but thankfully that technology is dead.

-k

 

[Smcmanus2001]

He there Marsg (Programmer),

I can definitely help you on this one, I have used the SQL designer on may occations when I needed speed, on some really Funky SQL querys that Crystal would choke on, and can understand. You need to manually put in the SQL Query {?Custname} (or whatever the Field is called). I create my SQL Querys in a SQL Tool, outside of Crystal to optimise the Speed, because SQL Query Tool Sucks for this. I use PowerBuilder, but whatever you feel comfortable with, Use the manually enter SQL Query, and then paste it inside the Text box, and continue to get some data. And then add the Parameters from the menu Custname in my case, then add the fields {?Custname}, I'm not sure of the exact order this has to be done. But this should help you.