Re: Change query from application on the fly?

Dave,

Sorry I didn't respond earlier...

What happens when you use parameters in the where clause?

Not familiar with this. Where and how are the where clause parameters
passed at run time? this would certainly achieve what I am looking for, but
haven't seen how you would do that.

Thanks,
Mark


"drolaw" <drlaw@xxxxxxx> wrote in message
news:8f9fe37b-ebd0-4e57-add6-2c34ac0586be@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 4, 10:16 am, "mark gross" <mgr...@xxxxxxxxxxxxx> wrote:
I still have a deployed application which uses VB6 & CR4.6 to produce
custom
reports which extract data from a massive financial database to produce
detail which project managers use. the response is almost instantaneous,
because the search parameters are the indexes in the table. From a
front-end application, I build the SQL query and pass it to the Crystal
Reports object.

When I first converted this application to .NET 2003 and the newer version
of CR, I was thrilled and there were many things I liked, but what killed
it
was that you could not pass a query to CR. So the former instant report
because a wait for the download of 8 million records, which then were
filtered by the CR application. totally unacceptable.

It's been a couple years since I last looked at this, so I'd love to have
someone tell me that it can be done... and don't say "create a view" -
this
is not an option (no user schemas in the database).

mark

What happens when you use parameters in the where clause? Try using
parameters and then viewing the SQL query in crystal. See if they get
passed through at all. If not, try clicking the process on server
options in the the report options. You say a view is not available,
how about a stored procedure (I realize that is somewhat of a stupid
question given your schema comment, but I had to ask).

Crystal should only pull all the records if it cannot process the
Record Selection formula into a where clause in the query for you. If
you are unable to create datbase objects, then you should focus on
getting the RSF to work properly. Alternatively, you could try basing
your report on a command. I'm not sure if you can programatically
overwrite the command though.

Anyway,

1. Try to create a parametrized SP if you can.
2. If you can't investigate why Crystal is not turning the RSF into an
appropriate where clause.
3. Have a look at the ability to base your report off of a command.
You may be able to overwrite the SQL for that. Not great for existing
reports though.

Hope that helps,
Dave


.

Relevant Pages

  • Re: Report Parameters - no results
    ... Is it indicated in the correct spot of my WHERE clause? ... be able to use it for this particular report based on user needs. ... What else can I do to try to correct my sql? ... used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)
  • RE: Passing Qry as part of Rpt DoCmd.OpenReport
    ... You are, by your description, trying to filter the output of the report. ... If you create a string that is a Where Clause without the word Where, ... And yes my data query is plain vanillia with no where clause only a single ...
    (microsoft.public.access.reports)
  • Re: Report Parameters - no results
    ... The WHERE clause I posted earlier is the only suggestion I have if you want to continue with that approach. ... be able to use it for this particular report based on user needs. ... What else can I do to try to correct my sql? ... > used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)
  • Re: For Marshall Barton
    ... additional subreports into the same detail section of the main report. ... Control Source: ABegBal ... named the subreport control to make it look like a text box ... "The LEVEL clause includes a reserved word or argument that is misspelled or ...
    (microsoft.public.access.reports)
  • Re: Report Parameters - no results
    ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... be able to use it for this particular report based on user needs. ... the WHERE clause in your query. ... used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)