Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


using a complete sql-query as parameter?

Posted on 2004-03-22
Medium Priority
Last Modified: 2008-03-03
i've got a client (c# winform, crystal reports .net2003-studio) that has complete informations to filter data provided by database. filter consists of parts of a complete sql-statement. now i want to give my filter to a crystal report that should provide me a list of entries with corresponding data. the entries should be filtered to show the same result like my client. my filter-criteria could change several times in future.

i have seen two ways to pass information into a report:
1.) give him param-fields
2.) set a formula (only cr-syntax?)

both versions do not seem to fit my needs, because
1.) i do not want to cut my filter into little pieces (if my client filter changes, my cr-params change, formulas change, ... - it's more than double-work to pass all parameters)
2.) i do not really like translating my sql into cr-formulas (same as above)

is there a way to pass the report my complete sql-statement or for example the WHERE-clause to minimize later following administrative work? or is there a possibility to cut the sql into pieces in cr so i have it separated from my code and do not have to care?
Question by:kolpdc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 101

Accepted Solution

mlmcc earned 320 total points
ID: 10649594
From VB with CR8 you can.  I suspect it is possible in C# with CR.Net

In VB there is a RecordSelectionFormula property.  Simply set it to the desired "WHERE" clause.

crRpt.RecordSelectiionFromula = "{ViewName.Field} = Value"

for example to find all records for user Smith

crRpt.RecordSelectiionFromula = "{MyView.UserName} = 'Smith'"

These samples should provide more info


Author Comment

ID: 10650084
mlmcc,  >> = "{MyView.UserName} = 'Smith'" << -> it's the reason for my question.

i have several modules. all of them using own filter-classes. easy filters just consist of an sql-where-clause with 5-20 attributes and order by. complex ones have extra joins and group by. if i have to cut my values of and translate to cr-syntax i'll have much work in front of my feet. having the possibility to only pass my sql would prevent much work now and for the future.

there is the possibility to pass a cr-selectionformula to the report. but sql?

Assisted Solution

TeddyZero earned 80 total points
ID: 10651011
If you use a DataSet to show the data retrieved by your query to the user, you can set this on your report to use the same DataSet, all you need is to add your Report to the project, create a new var from it and set the Property ReportSource of your CrystalReportViewer object.

With sample code is more easy to understanding. :-)

    Private Sub SetMyReportWithMyDS(ByVal myDS As DataSet, ByVal CRV As CrystalDecisions.Windows.Forms.CrystalReportViewer)
        Dim xReport As New rptTest ' This is my report(rptTest.rpt) included in the project
            ' Try to set the DataSource
        Catch ex As Exception
            MessageBox.Show("Unable to set the DataSource", "Error", MessageBoxButtons.OK)
        End Try
        ' OK, let's rock.
        CRV.ReportSource = xReport
    End Sub

Simple call this sub with your DataSet and your ReportViewer.
But remember, your report and your DataSet must have the same fields name or your report will not show anything or will throw an error to you.

I Hope this help's you.
Enjoy it. :-)
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 10655881
TeddyZero, it's a nice way. unfortunately not fitting my needs. i have got a business-layer that separates everything from database. datasets i do not use (readers are faster, less memoryconsuming, ...).

perhaps there is no other way than translating my sql-clauses into cr-code?

LVL 101

Expert Comment

ID: 10664135
To the best of my knowledge Crystal doesn't handle the type of where clauses you are referring to very well.  It is also my understanding that you cannot change the select part of the SQL which I believe you are implying by the need to add new joins.

Group by, Order by, where can all be changed quite easily from the application.  For my reports I write the SQL in the database without any where clause unless it will always be needed and no groups again unless they will always be used.

I generally put the grouping and sorting in the report and leave filtering to the application.


Author Comment

ID: 10675711
looking around i found out that somehow it seems possible to use sql. i will explain in following lines and increase points, too:

seems like crystal retrieves a special amount of entries from database. a bigger amount then required. then it takes this set of data and processes client-side-filtering (which i think takes much more time then done by db).

there is the possibility to enter a sql-command directly in the section where someone may select the tables and columns to be retrieved from database. it is stored there as a command which can later be used like a table.

my slightly changed question: it looks as if a parameter-field could be inserted in such an sql-statement. is there a way to
1.) put intelligence directly into sql-command (if paramfield-length, insert where-clause...)
2.) or else modify the sql-statement from somewhere within the formula-editor?

how would such a formula have to look like?

...FROM table1, ....
************ formula part begin
if Length({?paramfield}) > 0 then
    ECHO?? {?paramfield}
************ formula part end
...ORDER BY...

is there a way to do this? directly in sql-command?
or else get the sql-command-object in formula-editor and modify it from there?
LVL 101

Expert Comment

ID: 10683541
You can do what you are trying in a stored procedure but I don't think SQL directly supports it.

You could so that in a CR formula though that would be after the data is retrieved.

I believe if you specify server side grouping CR does all the filtering on the server rather than the client.  I forget where it is but I believe it is under FILE --> OPTIONS

LVL 101

Expert Comment

ID: 10710495
Glad I could help


Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question